原 Oracle 10g和11g查询表空间大小（数据库大小）
Tags： Oracle原创脚本小麦苗常用数据库大小表空间大小11gR2
👉 本文共约297个字，系统预计阅读时间或需2分钟。
其它
1、查询表空间大小：
- 10g和11g：https://www.dbaup.com/oracle-11gchaxunbiaokongjiandaxiao.html
- 12c：https://www.dbaup.com/oracle-12cchaxunbiaokongjiandaxiao.html
2、查询数据文件（含临时和Undo）详情
https://www.dbaup.com/oraclechaxunshujuwenjianhanlinshiheundowenjianxiangqing.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 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 | WITH wt1 AS (SELECT ts.TABLESPACE_NAME, df.all_bytes, decode(df.TYPE, 'D', nvl(fs.FREESIZ, 0), 'T', df.all_bytes - nvl(fs.FREESIZ, 0)) 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, ts.bigfile, df.ts_df_count FROM dba_tablespaces ts, (SELECT 'D' TYPE, 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 UNION ALL SELECT 'T', TABLESPACE_NAME, COUNT(*) ts_df_count, SUM(BYTES) all_bytes, SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) FROM dba_temp_files d GROUP BY TABLESPACE_NAME) df, (SELECT TABLESPACE_NAME, SUM(BYTES) FREESIZ FROM dba_free_space GROUP BY TABLESPACE_NAME UNION ALL SELECT tablespace_name, SUM(d.BLOCK_SIZE * a.BLOCKS) bytes FROM gv$sort_usage a, dba_tablespaces d WHERE a.tablespace = d.tablespace_name GROUP BY tablespace_name) fs WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+)) SELECT (SELECT A.TS# FROM V$TABLESPACE A WHERE A.NAME = UPPER(t.TABLESPACE_NAME)) TS#, t.TABLESPACE_NAME TS_Name, 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, 2) Used_per, round(MAXSIZ / 1024 / 1024 / 1024, 2) MAX_Size_g, round(decode(MAXSIZ, 0, to_number(NULL), (t.all_bytes - FREESIZ)) * 100 / MAXSIZ, 2) USED_per_MAX, round(t.BLOCK_SIZE) BLOCK_SIZE, t.LOGGING, t.FORCE_LOGGING, t.CONTENTS, t.EXTENT_MANAGEMENT, t.SEGMENT_SPACE_MANAGEMENT, t.RETENTION, t.DEF_TAB_COMPRESSION, t.bigfile, t.ts_df_count FROM wt1 t UNION ALL SELECT to_number('') TS#, 'ALL TS:' TS_Name, round(SUM(t.all_bytes) / 1024 / 1024, 2) 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), 2) Used_per, round(SUM(MAXSIZ) / 1024 / 1024 / 1024) MAX_Size, to_number('') "USED,% of MAX Size", to_number('') BLOCK_SIZE, '' LOGGING, '' FORCE_LOGGING, '' CONTENTS, '' EXTENT_MANAGEMENT, '' SEGMENT_SPACE_MANAGEMENT, '' RETENTION, '' DEF_TAB_COMPRESSION, '' bigfile, to_number('') ts_df_count FROM wt1 t order by CONTENTS,TS# ; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
