--创建表空间 create tablespace test logging datafile 'D:\oracle\oradata\ypcost\test01.dbf' size 10M autoextend on next 1M maxsize 20M extent management local--删除表空间 包括所有的数据对象和数据文件 drop tablespace tablesapcename including contents and datafiles;--增加数据文件 alter tablespace tablespacename add datafile 'D:\oracle\oradata\ypcost\test01.dbf' size 10M autoextend on next 1M maxsize 20M--修改表空间为自动增长 alter database datafile 'D:\oracle\oradata\aa\test01.dbf' autoextend on; --各个表空间的使用率、select Total.Tname "表空间名称", Total.Total_Size "表空间大小", Total.Total_Size - Used.free_size as "已使用大小", Used.Free_size as 表空间剩余大小, Round((Total.Total_Size - Used.free_size) / Total.Total_Size,4)* 100 || '%' as 表空间使用率 from ( -- 表空间数据文件的大小 select tablespace_name as TName, round(sum(user_bytes)/(1024*1024),1) as Total_size from dba_data_files group by tablespace_name ) Total, ( -- 表空间剩余的大小 select tablespace_name as TName, round(sum(bytes)/(1024*1024),1) as Free_size from dba_free_space group by tablespace_name ) Used where Total.TName = Used.TName(+)
alter database datafile 'D:\oracle\oradata\aa\test01.dbf' autoextend on next 100M maxsize unlimited;
--创建表空间
create tablespace test
logging
datafile 'D:\oracle\oradata\ypcost\test01.dbf'
size 10M
autoextend on
next 1M maxsize 20M
extent management local--删除表空间 包括所有的数据对象和数据文件
drop tablespace tablesapcename including contents and datafiles;--增加数据文件
alter tablespace tablespacename add
datafile 'D:\oracle\oradata\ypcost\test01.dbf'
size 10M
autoextend on
next 1M maxsize 20M--修改表空间为自动增长
alter database datafile 'D:\oracle\oradata\aa\test01.dbf' autoextend on;
--各个表空间的使用率、select Total.Tname "表空间名称",
Total.Total_Size "表空间大小",
Total.Total_Size - Used.free_size as "已使用大小",
Used.Free_size as 表空间剩余大小,
Round((Total.Total_Size - Used.free_size) / Total.Total_Size,4)* 100 || '%' as 表空间使用率
from
(
-- 表空间数据文件的大小
select tablespace_name as TName,
round(sum(user_bytes)/(1024*1024),1) as Total_size
from dba_data_files
group by tablespace_name
) Total,
(
-- 表空间剩余的大小
select tablespace_name as TName,
round(sum(bytes)/(1024*1024),1) as Free_size
from dba_free_space
group by tablespace_name
) Used
where Total.TName = Used.TName(+)
alter database
datafile 'D:\oracle\oradata\aa\test01.dbf'
autoextend on next 100M maxsize unlimited;