oracle中查看表空间使用情况

时间：2011-04-18 来源：yaya's Space

--sql

select dbf.tablespace_name,

dbf.totalspace "总量(M)", dbf.totalblocks as 总块数, dfs.freespace "剩余总量(M)", dfs.freeblocks "剩余块数", (dfs.freespace / dbf.totalspace) * 100 "空闲比例" from (select t.tablespace_name, sum(t.bytes) / 1024 / 1024 totalspace, sum(t.blocks) totalblocks from dba_data_files t group by t.tablespace_name) dbf, (select tt.tablespace_name, sum(tt.bytes) / 1024 / 1024 freespace, sum(tt.blocks) freeblocks from dba_free_space tt group by tt.tablespace_name) dfs where trim(dbf.tablespace_name) = trim(dfs.tablespace_name) ----------------------------------- SELECT Total.name "Tablespace Name", Free_space, (total_space-Free_space) Used_space, total_space FROM (select tablespace_name, sum(bytes/1024/1024) Free_Space from sys.dba_free_space group by tablespace_name ) Free, (select b.name, sum(bytes/1024/1024) TOTAL_SPACE from sys.v_$datafile a, sys.v_$tablespace B where a.ts# = b.ts# group by b.name ) Total WHERE Free.Tablespace_name = Total.name

-----------------------------

增加表空间大小 1：找出该表空间对应的数据文件及路径 select * from dba_data_files t where t.tablespace_name = 'ARD' 2：增大数据文件 alter database datafile '全路径的数据文件名称' resize ***M 3：增加数据文件 alter tablespace 表空间名称 add datafile '全路径的数据文件名称' size ***M 注解：表空间尽量让free百分比保持在10%以上，如果低于10%就增加datafile或者resizedatafile，一般数据文件不要超过2G 4:检查tablespace的free空间能不能满足最大的next_extent，不能则扩展tablespace select s.owner,s.segment_name,s.segment_type,s.tablespace_name, s.next_extent from dba_segments s where s.next_extent > (select MAX(f.bytes) from dba_free_space f where f.tablespace_name = s.tablespace_name) order by s.tablespace_name

设置自动扩展：

alter database datafile 'D:\oradata\orcl\PERFSTAT.DBF' autoextend on;

相关阅读更多 +