原 达梦数据库查询表空间大小和数据文件情况（数据库大小）
Tags： 原创小麦苗常用达梦数据库数据文件数据库大小表空间大小
👉 本文共约43个字，系统预计阅读时间或需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. |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
