oracle 查询表大小脚本,脚本查询oracle数据库表空间使用率等信息

写了一个统计查询表空间使用记录等信息的sql，做个记录

/*

CREATED BY DYD@20160914

dydzdc@163.com

CHECK THE TABLESPACE USED_INFO AND CREATATION_TIME

*/

SET PAGESIZE 500; --定义页面大小500行

SET LINESIZE 200; --定义输出屏幕宽度200字符

SET TIMING ON; --语句执行时间开关为开

COL TBS_NAME FOR A30; --定义列宽为字符串宽度30个字符

COL CREATION_TIME FOR A20;

COL FILE_NAME FOR A70;

COL MAX_SIZE FOR A10;

COL TOTAL_GB FOR A10;

COL USED_GB FOR A10;

COL FREE_MB FOR A10;

COL PCT_FREE FOR A10;

DEFINE_EDITOR='vi'; --定义sql内嵌编辑器为‘vi’

SELECT

DD.TABLESPACE_NAME AS TBS_NAME, --查询表空间名 并别名

VD.STATUS AS STATUS , --查询状态 并别名

VD.CREATION_TIME AS CREATION_TIME, --查询数据文件创建时间 并别名

ROUND(DD.MAXBYTES/1024/1024/1024,0)||'GB' AS MAX_SIZE, --查询数据文件最大大小 并别名

ROUND(VD.BYTES/1024/1024,2)||'MB' AS TOTAL_MB, --查询数据文件当前大小 并别名

ROUND((DD.BYTES-DF.FREE_BYTES)/1024/1024,0)||'MB' AS USED_MB, --查询已使用的大小并别名，用总大小减去dba_free_space查出的空闲空间=已使用的空间

ROUND(DF.FREE_BYTES/1024/1024,0)||'MB' AS FREE_MB, --查询空闲空间

ROUND((DF.FREE_BYTES/DD.BYTES)*100)||'%' AS PCT_FREE, --用空闲空间/当前大小=空闲百分比

DD.AUTOEXTENSIBLE AS AUTOEXTEND, --查询数据文件是否允许自动扩展

DD.FILE_NAME AS FILE_NAME --查询数据文件路径和名称

FROM

(SELECT F.TABLESPACE_NAME,F.FILE_ID,SUM(F.BYTES) FREE_BYTES FROM DBA_FREE_SPACE F GROUP BY TABLESPACE_NAME,FILE_ID ORDER BY 2) DF, --因为dba_free_space 表空间列出的内容，如果一个表空间存在碎片，则同一个表空间有多条记录，所以只能先求和并把结果 生成DF表

DBA_DATA_FILES DD, --DBA_DATA_FILES表别名DD

V$DATAFILE VD --V$DATAFILE 表别名 VD

WHERE

DD.FILE_ID=VD.FILE# AND DD.FILE_ID=DF.FILE_ID --关联信息，DBA_DATA_FILES表的FILE_ID列和V$DATAFILE的FILE#列以及DBA_FREE_SPACE表的列FILE_ID是相同的

AND

VD.CREATION_TIME>=SYSDATE-30 --仅查询距离当前日期30天以内新添加的数据文件，可以自行修改

ORDER BY

VD.CREATION_TIME； --最后根据数据文件创建时间排序

这个是脚本的执行结果，如果想查询范围更早的数据文件可以在where子句后面修改相关参数条件

TBS_NAME STATUS CREATION_ MAX_SIZE TOTAL_MB USED_MB FREE_MB PCT_FREE AUT FILE_NAME

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

EXAMPLE ONLINE 29-AUG-16 32GB 330MB 310MB 20MB 6% YES +DATA/racdb/datafile/example.264.921191975

UNDOTBS2 ONLINE 29-AUG-16 32GB 100MB 21MB 79MB 79% YES +DATA/racdb/datafile/undotbs2.259.921192243

SPOTLIGHT ONLINE 01-SEP-16 32GB 120MB 114MB 6MB 5% YES +DATA/racdb/datafile/spotlight.269.921452567

TEST ONLINE 14-SEP-16 32GB 50MB 1MB 49MB 98% YES +DATA/racdb/datafile/test.270.922549171

TEST ONLINE 14-SEP-16 32GB 10MB 1MB 9MB 90% YES +DATA/racdb/datafile/test.274.922549285

仅供参考，如果有错误麻烦指正。