--合并表空间大小

alter tablespace users coalesce

?

--查询数据文件

select file_name,file_id from dba_data_files

?

--查询数据库对象占空间大小

select owner,segment_name,sum(bytes) from dba_extents where file_id=4 group by owner,segment_name order by sum(bytes) desc

?

select sum(bytes),tablespace_name from dba_free_space group by tablespace_name order by 1 desc

?

--修改数据文件大小

alter database datafile 'D:\ORACLE11GXE\APP\ORACLE\ORADATA\XE\USERS.DBF' resize 7000M;

?

--查询表空间利用率

select ?s.tablespace_name,

? ? ? ?round(sum(s.bytes / 1024 / 1024), 2) as Space,

? ? ? ?round((sum(s.bytes / 1024 / 1024) - nvl(d.fr,0)), 2) used,

? ? ? ?nvl(round(d.fr, 2),0) free,

? ? ? ?decode(round(sum(s.maxbytes / 1024 / 1024 / 1024), 2),0,'NO','YES') auto_extend,

? ? ? ?round(sum(s.maxbytes / 1024 / 1024 / 1024), 2) MaximumGB

? from dba_data_files s,

? ? ? ?(select c.tablespace_name,

? ? ? ? ? ? ? ?round(sum(c.bytes / 1024 / 1024), 2) fr

? ? ? ? ? from dba_free_space c

? ? ? ? ?group by c.tablespace_name) d

?where s.tablespace_name = d.tablespace_name(+)

?group by s.tablespace_name,d.fr

?order by s.tablespace_name

?

?--查询表空间状态

?select file_id,file_name,tablespace_name,AUTOEXTENSIBLE ?from dba_data_files;