Oracle查询数据文件（含临时和Undo文件）详情
👉 本文共约183个字，系统预计阅读时间或需1分钟。
表空间大小请参考：
- 10g和11g：https://www.xmmup.com/oracle-11gchaxunbiaokongjiandaxiao.html
- 12c：https://www.xmmup.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; |
12c环境
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 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 | col PDBNAME format a15 col FILE_NAME format a80 col CONTENTS format a15 col TABLESPACE_NAME format a15 SELECT FILE_ID, CON_ID, (CASE WHEN T.PDBNAME = LAG(T.PDBNAME, 1) OVER(PARTITION BY T.PDBNAME ORDER BY TS#) THEN NULL ELSE T.PDBNAME END) PDBNAME, TS#, TABLESPACE_NAME, CONTENTS, TS_SIZE_M, FILE_NAME, FILE_SIZE_M, FILE_MAX_SIZE_G, AUTOEXTENSIBLE, INCREMENT_M, AUTOEXTEND_RATIO, CREATION_TIME, INCREMENT_BY_BLOCK, BYTES, BLOCKS, MAXBYTES, MAXBLOCKS, USER_BYTES, USER_BLOCKS FROM (SELECT D.FILE_ID, D.CON_ID, (SELECT NP.NAME FROM V$CONTAINERS NP WHERE NP.CON_ID = D.CON_ID) PDBNAME, (SELECT A.TS# FROM V$TABLESPACE A WHERE A.NAME = UPPER(D.TABLESPACE_NAME) AND A.CON_ID = D.CON_ID) TS#, D.TABLESPACE_NAME, (select CONTENTS from cdb_tablespaces where TABLESPACE_NAME=d.TABLESPACE_NAME and CON_ID = D.CON_ID) CONTENTS, (SELECT ROUND(SUM(NB.BYTES) / 1024 / 1024, 2) FROM CDB_DATA_FILES NB WHERE NB.TABLESPACE_NAME = D.TABLESPACE_NAME AND NB.CON_ID = D.CON_ID) 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 AND B.CON_ID = D.CON_ID) CREATION_TIME, D.INCREMENT_BY INCREMENT_BY_BLOCK, D.BYTES, D.BLOCKS, D.MAXBYTES, D.MAXBLOCKS, D.USER_BYTES, D.USER_BLOCKS FROM CDB_DATA_FILES D UNION ALL SELECT D.FILE_ID, D.CON_ID, (SELECT NP.NAME FROM V$CONTAINERS NP WHERE NP.CON_ID = D.CON_ID) PDBNAME, (SELECT A.TS# FROM V$TABLESPACE A WHERE A.NAME = UPPER(D.TABLESPACE_NAME) AND A.CON_ID = D.CON_ID) TS#, D.TABLESPACE_NAME, (select CONTENTS from cdb_tablespaces where TABLESPACE_NAME=d.TABLESPACE_NAME and CON_ID = D.CON_ID) CONTENTS, (SELECT ROUND(SUM(NB.BYTES) / 1024 / 1024, 2) FROM V$TEMPFILE NB WHERE NB.NAME = D.FILE_NAME AND NB.CON_ID = D.CON_ID) 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 AND B.CON_ID = D.CON_ID) CREATION_TIME, D.INCREMENT_BY INCREMENT_BY_BLOCK, D.BYTES, D.BLOCKS, D.MAXBYTES, D.MAXBLOCKS, D.USER_BYTES, D.USER_BLOCKS FROM CDB_TEMP_FILES D) T ORDER BY CON_ID,CONTENTS,FILE_ID,TS#; |
