合 Oracle查询数据文件（含临时和Undo文件）详情
👉 本文共约177个字，系统预计阅读时间或需1分钟。
表空间大小请参考：
- 10g和11g：https://www.dbaup.com/oracle-11gchaxunbiaokongjiandaxiao.html
- 12c：https://www.dbaup.com/oracle-12cchaxunbiaokongjiandaxiao.html
10g和11g环境
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | col FILE_NAME format a80 SELECT d.FILE_ID, d.TABLESPACE_NAME, (select CONTENTS from dba_tablespaces where TABLESPACE_NAME=d.TABLESPACE_NAME) CONTENTS, (SELECT round(SUM(nb.BYTES) / 1024 / 1024, 2) FROM dba_data_files nb WHERE nb.TABLESPACE_NAME = d.TABLESPACE_NAME) ts_size_m, d.FILE_NAME, round(d.BYTES / 1024 / 1024, 2) file_size_m, round(d.MAXBYTES / 1024 / 1024 / 1024, 2) file_max_size_G, d.AUTOEXTENSIBLE, round(d.INCREMENT_BY * 8 * 1024 / 1024 / 1024, 2) INCREMENT_m, round(d.BYTES * 100 / decode(d.MAXBYTES, 0, BYTES, d.MAXBYTES), 2) autoextend_ratio, (SELECT b.CREATION_TIME FROM sys.v_$datafile b where b.FILE# = d.FILE_ID) CREATION_TIME, d.INCREMENT_BY INCREMENT_BY_block, d.BYTES, d.blocks, d.MAXBYTES, d.MAXBLOCKS, d.USER_BYTES, d.USER_BLOCKS FROM dba_data_files d UNION ALL SELECT d.FILE_ID, d.TABLESPACE_NAME, (select CONTENTS from dba_tablespaces where TABLESPACE_NAME=d.TABLESPACE_NAME) CONTENTS, (SELECT round(SUM(nb.BYTES) / 1024 / 1024, 2) FROM v$tempfile nb WHERE nb.name = d.FILE_NAME) ts_size, d.FILE_NAME, round(d.BYTES / 1024 / 1024, 2) file_size_m, round(d.MAXBYTES / 1024 / 1024 / 1024, 2) file_max_size_G, d.AUTOEXTENSIBLE, round(d.INCREMENT_BY * 8 * 1024 / 1024 / 1024, 2) INCREMENT_m, round(d.BYTES * 100 / decode(d.MAXBYTES, 0, BYTES, d.MAXBYTES), 2) autoextend_ratio, (SELECT b.CREATION_TIME FROM sys.v_$datafile b where b.FILE# = d.FILE_ID) CREATION_TIME, d.INCREMENT_BY INCREMENT_BY_block, d.BYTES, d.blocks, d.MAXBYTES, d.MAXBLOCKS, d.USER_BYTES, d.USER_BLOCKS FROM dba_temp_files d ORDER BY CONTENTS,file_id,TABLESPACE_NAME; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
