达梦数据库查询表空间大小和数据文件情况（数据库大小）
👉 本文共约53个字，系统预计阅读时间或需1分钟。
表空间查询
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 | WITH wt1 AS (SELECT ts.TABLESPACE_NAME, df.all_bytes, fs.FREESIZ FREESIZ, df.MAXSIZ, ts.BLOCK_SIZE, ts.LOGGING, ts.FORCE_LOGGING, ts.CONTENTS, ts.EXTENT_MANAGEMENT, ts.SEGMENT_SPACE_MANAGEMENT, ts.RETENTION, ts.DEF_TAB_COMPRESSION, df.ts_df_count FROM dba_tablespaces ts, (SELECT TABLESPACE_NAME, count(*) ts_df_count, SUM(BYTES) all_bytes, SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ FROM dba_data_files d GROUP BY TABLESPACE_NAME) df, (SELECT TABLESPACE_NAME, SUM(BYTES) FREESIZ FROM dba_free_space GROUP BY TABLESPACE_NAME) fs WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME) SELECT (SELECT A.ID FROM V$TABLESPACE A WHERE A.NAME = UPPER(t.TABLESPACE_NAME)) TS#, t.TABLESPACE_NAME TS_Name, CONTENTS, round(t.all_bytes / 1024 / 1024) ts_size_M, round(t.freesiz / 1024 / 1024) Free_Size_M, round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M, round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 3) Used_per, round(MAXSIZ / 1024 / 1024/1024, 3) MAX_Size_g, round(decode(MAXSIZ, 0, to_number(NULL), (t.all_bytes - FREESIZ)) * 100 / MAXSIZ, 3) USED_per_MAX, round(t.BLOCK_SIZE) BLOCK_SIZE, t.LOGGING, t.ts_df_count FROM wt1 t UNION ALL SELECT to_number('') TS#, 'ALL TS:' TS_Name, null, round(SUM(t.all_bytes) / 1024 / 1024, 3) ts_size_M, round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m, round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M, round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes), 3) Used_per, round(SUM(MAXSIZ) / 1024 / 1024/1024) MAX_Size, to_number('') "USED,% of MAX Size", to_number('') BLOCK_SIZE, '' LOGGING, to_number('') ts_df_count FROM wt1 t order by CONTENTS NULLS LAST,TS# NULLS LAST TS# TS_Name CONTENTS ts_size_M Free_Size_M Used_Size_M Used_per MAX_Size_g USED_per_MAX BLOCK_SIZE LOGGING ts_df_count --- ------- --------- --------- ----------- ----------- -------- ---------- ------------ ---------- ------- ----------- 0 SYSTEM PERMANENT 44 35 9 21.058 32767.999 0 2816 1 4 MAIN PERMANENT 2176 2069 107 4.921 65535.998 0 139264 2 5 SYSAUX PERMANENT 379 33 346 91.173 10 3.374 24256 1 3 TEMP TEMPORARY 26 26 0 0.541 32767.999 0 1664 1 1 ROLL UNDO 128 110 18 14.246 32767.999 0 8192 1 ALL TS: 2753 2273 480 17.445 163850 6 rows got 已用时间: 69.247(毫秒). 执行号:28601. |
数据文件查询
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 | SELECT 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.CREATE_TIME FROM v$datafile b where b.path = file_name) 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; 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 --------------- --------- --------- ------------------------- ----------- --------------- -------------- ----------- ---------------- ------------------- ------------------ -------------------- -------------------- -------------- ----------- -------------------- -------------------- SYSTEM PERMANENT 44 /dm8/data/PROD/SYSTEM.DBF 44 32768 YES 0 0 2021-09-08 03:51:53 0 46137344 2816 35184371040256 33554431 36421632 2223 SYSAUX PERMANENT 379 /dm8/data/PROD/SYSAWR.DBF 379 10 YES 0 3.7 2021-10-11 09:27:06 0 397410304 24256 10737418240 10240 35078144 2141 MAIN PERMANENT 2176 /dm8/data/PROD/MAIN01.DBF 2048 32768 YES 0 0.01 2021-10-12 10:07:06 0 2147483648 131072 35184371040256 33554431 2117320704 129231 MAIN PERMANENT 2176 /dm8/data/PROD/MAIN.DBF 128 32768 YES 0 0 2021-09-08 03:51:53 0 134217728 8192 35184371040256 33554431 52101120 3180 TEMP TEMPORARY 26 /dm8/data/PROD/TEMP.DBF 26 32768 YES 0 0 2021-10-11 07:38:09 0 27262976 1664 35184371040256 33554431 27115520 1655 ROLL UNDO 128 /dm8/data/PROD/ROLL.DBF 128 32768 YES 0 0 2021-09-08 03:51:53 0 134217728 8192 35184371040256 33554431 115097600 7025 6 rows got |
