查询表空间总大小(dba_data_files和dba_segments,dba_free_space区别)

1, dba_data_files,dba_segments,dba_free_space
得出结论:一般情况下(没有drop表的时候):dba_data_files bytes = (dba_segments bytes + dba_free_space bytes)

实验:

SQL> Select sum(bytes)/1024/1024/1024 from dba_segments;

SUM(BYTES)/1024/1024/1024
-------------------------
           1.58441162

SQL> select sum(bytes)/1024/1024/1024 from dba_free_space;

SUM(BYTES)/1024/1024/1024
-------------------------
           .323669434

SQL> select sum(bytes)/1024/1024/1024 from dba_data_files;

SUM(BYTES)/1024/1024/1024
-------------------------
        1.9140625
2, delete,truncate,drop对空间的变化影响
delete表数据,对上述3个视图查询没有任何变化,满足上述相等原则.
truncate表数据,表空间一次性回收,dba_free_space增加,dba_segments减少,让满足上述相等原则.
drop表,表空间一次性回收,但dba_segment会多出个回收站,但实际上dba_free_segment也把回收站的那部分空间计算在内了,所以回收站的内容实际也是可以使用的部分,表面上看这个等式不成立了,但实质上做了些改变,改为(有drop时):dba_data_files bytes = (dba_segments bytes + dba_free_space bytes- dba_segment回收站部分)

 

3.还可以直接去数据文件所在目录直接 du -sh查看数据文件大小

转载于:https://www.cnblogs.com/gw666/p/8194994.html