-- Tablespace space reportset linesize 300 pagesize 300 col tablespace_name for a25 col force_logging for a12 col bigfile for a8 col encrypted for a10 col allocation_type for a15 col PDB_NAME for a15 col "TABLESPACE_SIZE(GB)" for a20 select a.tablespace_name,a.CON_ID,nvl(PDB_NAME,'CDB$ROOT' ) PDB_NAME, round(a.used_percent, 2) as "USED _%",b.block_size,b.force_logging, bigfile,encrypted,allocation_type,b.status,contents,retention,extent_management, to_char(round((a.tablespace_size * b.block_size)/1024/1024/1024, 2)) as "TABLESPACE_SIZE(GB)", round((a.used_space * b.block_size)/1024/1024/1024, 2) as "USED_SPACE(GB)", round((a.tablespace_size * b.block_size)/1024/1024/1024, 2) - round((a.used_space * b.block_size)/1024/1024/1024, 2) Free_GB, round(a.used_percent, 2) as "USED_%" from cdb_tablespace_usage_metrics a , cdb_tablespaces b ,dba_pdbs c where a.tablespace_name = b.tablespace_name -- and a.tablespace_name like '%' -- and CONTENTS='TEMPORARY' -- and CONTENTS='UNDO' -- and CONTENTS='PERMANENT' and a.con_id=b.con_id and a.con_id=c.con_id(+) order by "USED_%" desc ;
DBA_TABLESPACE_USAGE_METRICS Returns Incorrect Information After applying 12.1.0.2.170418 (25397136) Bundle patch (Doc ID 2289448.1)
set pagesize 10000 linesize 300 tab off col tablespace_name format A22 heading "Tablespace" col ts_type format A13 heading "TS Type" col segments format 999999 heading "Segments" col files format 9999 col allocated_mb format 999,999,990.00 heading "Allocated Size|(Mb)" col used_mb format 999,999,990.00 heading "Used Space|(Mb)" col Free_mb format 999,999,990.00 heading "Free Space|(Mb)" col used_pct format 999 heading "Used|%" col max_ext_mb format 999,999,990.00 heading "Max Size|(Mb)" col max_free_mb format 999,999,990.00 heading "Max Free|(Mb)" col max_used_pct format 999 heading "Max Used|(%)*" col max_used_pct1 format 999 heading "Max Used1|(%)****" col Max_Free for 999,999,990.00 heading "Max_Free|(MB)" BREAK ON REPORT COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE ==========>" OF segments files allocated_mb used_mb Free_MB max_ext_mb Max_Free ON REPORT select tablespace_name, status, ts_type, files, segments, Allocated_MB, Used_MB, Free_MB, Used_pct, max_ext_mb, Max_Free, Max_used_pct, --- <<<<<<<<<from dba_tablespace_usage_metrics Sometime this may be wrong due to bug (Used_MB/max_ext_mb)*100 Max_used_pct1 from ( WITH df AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_data_files GROUP BY tablespace_name), tf AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_temp_files GROUP BY tablespace_name), tm AS (SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics), ts AS (SELECT tablespace_name, COUNT(*) segcnt FROM dba_segments GROUP BY tablespace_name) SELECT d.tablespace_name, d.status, DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, a.cnt files, NVL(s.segcnt,0) segments, ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB, ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,3) Used_MB, ROUND(NVL(f.bytes, 0) / 1024 / 1024, 3) Free_MB, ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) Used_pct, ROUND(a.maxbytes / 1024 / 1024, 3) max_ext_mb, ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,3) Max_Free, ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND d.tablespace_name = m.tablespace_name(+) AND d.tablespace_name = s.tablespace_name(+) AND NOT d.contents = 'UNDO' AND NOT ( d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' ) UNION ALL -- TEMP TS SELECT d.tablespace_name, d.status, DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, a.cnt, 0, ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB, ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Used_MB, ROUND((NVL(a.bytes ,0)/1024/1024 - NVL((t.ub*d.block_size), 0)/1024/1024), 3) Free_MB, ROUND(NVL((t.ub*d.block_size) / a.bytes * 100, 0), 2) Used_pct, ROUND(a.maxbytes / 1024 / 1024, 3) max_size_mb, ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Max_Free, ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM dba_tablespaces d, tf a, tm m, (SELECT ss.tablespace_name , sum(ss.used_blocks) ub FROM gv$sort_segment ss GROUP BY ss.tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.tablespace_name = m.tablespace_name(+) AND d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' UNION ALL -- UNDO TS SELECT d.tablespace_name, d.status, DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, a.cnt, NVL(s.segcnt,0) segments, ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB, ROUND(NVL(u.bytes, 0) / 1024 / 1024, 3) Used_MB, ROUND(NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024, 3) Free_MB, ROUND(NVL(u.bytes / a.bytes * 100, 0), 2) Used_pct, ROUND(a.maxbytes / 1024 / 1024, 3) max_size_mb, ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(u.bytes, 0) / 1024 / 1024, 3) Max_Free , ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_undo_extents where status in ('ACTIVE','UNEXPIRED') GROUP BY tablespace_name) u WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = u.tablespace_name(+) AND d.tablespace_name = m.tablespace_name(+) AND d.tablespace_name = s.tablespace_name(+) AND d.contents = 'UNDO' ORDER BY 12 desc )
Tablespace report prompt - LM/DM - Local/Dictionary Managed |- SYS/UNI - SYStem/UNIform Extent Management (LM only) |ASSM/MSSM - Automatic/Manual Segment Space Management (ASSM -> LM only) set pagesize 10000 linesize 300 tab off col tablespace_name format A22 heading "Tablespace" col ts_type format A13 heading "TS Type" col segments format 999999 heading "Segments" col files format 9999 col allocated_mb format 999,999,990.00 heading "Allocated Size|(Mb)" col used_mb format 999,999,990.00 heading "Used Space|(Mb)" col Free_mb format 999,999,990.00 heading "Free Space|(Mb)" col used_pct format 999 heading "Used|%" col max_ext_mb format 999,999,990.00 heading "Max Size|(Mb)" col max_free_mb format 999,999,990.00 heading "Max Free|(Mb)" col max_used_pct format 999 heading "Max Used|(%)*" col Max_Free for 999,999,990.00 heading "Max_Free|(MB)" BREAK ON REPORT COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE ==========>" OF segments files allocated_mb used_mb Free_MB max_ext_mb Max_Free ON REPORT WITH df AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_data_files GROUP BY tablespace_name), tf AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_temp_files GROUP BY tablespace_name), tm AS (SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics), ts AS (SELECT tablespace_name, COUNT(*) segcnt FROM dba_segments GROUP BY tablespace_name) SELECT d.tablespace_name, d.status, DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, a.cnt files, NVL(s.segcnt,0) segments, ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB, ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,3) Used_MB, ROUND(NVL(f.bytes, 0) / 1024 / 1024, 3) Free_MB, ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) Used_pct, ROUND(a.maxbytes / 1024 / 1024, 3) max_ext_mb, ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,3) Max_Free, ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND d.tablespace_name = m.tablespace_name(+) AND d.tablespace_name = s.tablespace_name(+) AND NOT d.contents = 'UNDO' AND NOT ( d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' ) UNION ALL -- TEMP TS SELECT d.tablespace_name, d.status, DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, a.cnt, 0, ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB, ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Used_MB, ROUND((NVL(a.bytes ,0)/1024/1024 - NVL((t.ub*d.block_size), 0)/1024/1024), 3) Free_MB, ROUND(NVL((t.ub*d.block_size) / a.bytes * 100, 0), 2) Used_pct, ROUND(a.maxbytes / 1024 / 1024, 3) max_size_mb, ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Max_Free, ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM dba_tablespaces d, tf a, tm m, (SELECT ss.tablespace_name , sum(ss.used_blocks) ub FROM gv$sort_segment ss GROUP BY ss.tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.tablespace_name = m.tablespace_name(+) AND d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' UNION ALL -- UNDO TS SELECT d.tablespace_name, d.status, DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, a.cnt, NVL(s.segcnt,0) segments, ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB, ROUND(NVL(u.bytes, 0) / 1024 / 1024, 3) Used_MB, ROUND(NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024, 3) Free_MB, ROUND(NVL(u.bytes / a.bytes * 100, 0), 2) Used_pct, ROUND(a.maxbytes / 1024 / 1024, 3) max_size_mb, ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(u.bytes, 0) / 1024 / 1024, 3) Max_Free , ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_undo_extents where status in ('ACTIVE','UNEXPIRED') GROUP BY tablespace_name) u WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = u.tablespace_name(+) AND d.tablespace_name = m.tablespace_name(+) AND d.tablespace_name = s.tablespace_name(+) AND d.contents = 'UNDO' ORDER BY 12 desc / Allocated Size Used Space Free Space Used Max Size Max_Free Max Used Tablespace STATUS TS Type FILES Segments (Mb) (Mb) (Mb) % (Mb) (MB) (%)* ---------------------- --------- ------------- ----- -------- --------------- --------------- --------------- ---- --------------- --------------- -------- UNDOTBS1 ONLINE UNDO-SYS-MSSM 9 307 214,771.94 212,140.88 2,631.06 99 294,911.86 82,770.98 73 R80DBFS_TS ONLINE LM-SYS-ASSM 1 6 131,072.00 130,018.19 1,053.81 99 307,200.00 177,181.81 42 R80DATA ONLINE LM-SYS-ASSM 1 44210 12,001,380.00 11,867,990.56 133,389.44 99 33,554,431.98 21,686,441.41 35 OOOETL_DATA ONLINE LM-SYS-ASSM 1 58 2,068,580.00 2,038,569.44 30,010.56 99 33,554,431.98 31,515,862.54 6 SYSAUX ONLINE LM-SYS-ASSM 1 5053 2,470.00 1,745.63 724.38 71 32,767.98 31,022.36 5 R80DATA3 ONLINE LM-SYS-ASSM 70 5136 1,570,025.78 95,078.53 1,474,947.25 6 2,293,758.91 2,198,680.38 4 SYSTEM ONLINE LM-SYS-MSSM 1 1688 32,750.00 885.25 31,864.75 3 32,767.98 31,882.73 3 R80INDEX ONLINE LM-SYS-ASSM 1 15655 338,020.00 332,294.94 5,725.06 98 33,554,431.98 33,222,137.04 1 R80ARC ONLINE LM-SYS-ASSM 4 333 2,524.00 325.13 2,198.88 13 131,071.94 130,746.81 0 USERS ONLINE LM-SYS-ASSM 1 59 81.25 4.69 76.56 6 32,767.98 32,763.30 0 TEMP_BIGFILE ONLINE TEMP-UNI-MSSM 1 0 7,605,500.00 209.00 7,605,291.00 0 33,554,431.98 33,554,222.98 0 TEMP ONLINE TEMP-UNI-MSSM 1 0 32,767.00 0.00 32,767.00 0 32,767.00 32,767.00 0 ----- -------- --------------- --------------- --------------- --------------- --------------- AVERAGE ==========> 8 6042 1,999,995.16 1,223,271.85 776,723.31 11,447,978.46 10,224,706.61 TOTAL SUM ==========> 92 72505 23,999,941.97 14,679,262.22 9,320,679.75 137,375,741.56 122,696,479.34 12 rows selected. ========================= set pause off set feed off set verify off set pagesize 55 set linesize 255 clear screen col tn format a10 heading 'Name' trunc col fn format a50 heading 'Located in file_ID + File Name' col bts format a15 heading 'Size' -- col bts format 999999999999 heading 'Size' col used format a10 heading 'Used' col ex format 9999 heading 'NrExt' col rs format 999,999,999 heading 'RBSsize' col init format 999,999 heading 'Init' col next format 999,999 heading 'Next' col mi format 999 heading 'Min' col ma format 9999999999 heading 'Max' col pct format 990 heading '%Inc' col st format a4 heading 'Stat' col sn format a15 heading 'Segm Name' col ts format a15 heading 'In Table Space' create or replace view free_view as select file_id, sum(bytes) free_bytes from sys.dba_free_space group by file_id; clear screen prompt Tablespace Datafiles select d.tablespace_name tn, f.file_id||' '||file_name fn, to_char(f.bytes/1024,'999999,999')||'K' bts, to_char( (f.bytes - s.free_bytes)/1024,'999,999')||'K' used from sys.dba_tablespaces d, sys.dba_data_files f, free_view s where d.tablespace_name = f.tablespace_name and f.file_id = s.file_id(+) order by d.tablespace_name; prompt prompt Tablespace definitions define part1="rpad('|',29*(f.bytes-s.free_bytes)/f.bytes,'*')" col gr format a30 heading 'Percent full' select d.tablespace_name tn, d.initial_extent init, d.next_extent next, d.pct_increase pct, d.min_extents mi, max_extents ma, decode(d.status,'ONLINE','OnL','OFFLINE','OffL') st, rpad(&part1,29,' ')||'|' gr from sys.dba_tablespaces d, sys.dba_data_files f, free_view s where d.tablespace_name = f.tablespace_name and f.file_id = s.file_id order by d.tablespace_name; drop view free_view; set feed on =================================== Tablespace Datafiles Name Located in file_ID + File Name Size Used ---------- -------------------------------------------------- --------------- ---------- ANUJTEST 6 /opt/app/oracle/oradata/orcl/anujtest.dbf 10,240K 8,192K DROP1 11 /opt/app/oracle/oradata/orcl/drop.dbf 2,048K 1,024K EXAMPLE 5 /opt/app/oracle/oradata/orcl/example01.dbf 102,400K 80,640K PERFSTAT 12 /opt/app/oracle/oradata/orcl/anuj_perfstat.dbf 1024,000K 117,760K RMAN 10 /opt/app/oracle/oradata/orcl/rman.dbf 51,200K 7,104K SYSAUX 2 /opt/app/oracle/oradata/orcl/sysaux01.dbf 849,920K 743,488K SYSTEM 1 /opt/app/oracle/oradata/orcl/system01.dbf 849,920K 832,960K TEST 9 /opt/app/oracle/oradata/orcl/test.dbf 20,480K 1,024K TSAPEXF 7 /opt/app/oracle/oradata/orcl/tsapexf01.dbf 51,200K 1,024K TSAPEXU 8 /opt/app/oracle/oradata/orcl/tsapexu01.dbf 112,640K 99,840K UNDOTBS1 3 /opt/app/oracle/oradata/orcl/undotbs01.dbf 332,800K 12,800K USERS 4 /opt/app/oracle/oradata/orcl/users01.dbf 604,160K 542,208K Tablespace definitions Name Init Next %Inc Min Max Stat Percent full ---------- -------- -------- ---- ---- ----------- ---- ------------------------------ ANUJTEST 65,536 1 2147483645 OnL |********************** | DROP1 65,536 1 2147483645 OnL |************* | EXAMPLE 65,536 1 2147483645 OnL |********************* | PERFSTAT 524,288 524,288 0 1 2147483645 OnL |** | RMAN 65,536 1 2147483645 OnL |*** | SYSAUX 65,536 1 2147483645 OnL |************************ | SYSTEM 65,536 1 2147483645 OnL |*************************** | TEST 65,536 1 2147483645 OnL | | TSAPEXF 65,536 1 2147483645 OnL | TSAPEXU 65,536 1 2147483645 OnL |************************ | UNDOTBS1 65,536 1 2147483645 OnL | | USERS 65,536 1 2147483645 OnL |************************* |
==================
for PDB
set pagesize 9999 line 9999 col TS_Name format a35 col PDBNAME format a15 col TS_Name format a35 col TYPE format a12 col LOGGING format a10 SELECT CON_ID, PDBNAME, TS#, TS_NAME, TYPE, TS_SIZE_M, FREE_SIZE_M, USED_SIZE_M, USED_PER, MAX_SIZE_G, USED_PER_MAX, BLOCK_SIZE, LOGGING, TS_DF_COUNT FROM (WITH wt1 AS (SELECT ts.CON_ID, (SELECT np.NAME FROM V$CONTAINERS np WHERE np.CON_ID = tS.con_id) PDBNAME, (SELECT A.TS# FROM V$TABLESPACE A WHERE A.NAME = UPPER(tS.TABLESPACE_NAME) AND a.CON_ID = tS.con_id) TS#, 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, df.ts_df_count FROM cdb_tablespaces ts, (SELECT d.CON_ID, 'D' TYPE, TABLESPACE_NAME, COUNT(*) ts_df_count, SUM(BYTES) all_bytes, SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ FROM cdb_data_files d GROUP BY d.CON_ID, TABLESPACE_NAME UNION ALL SELECT d.CON_ID, 'T', TABLESPACE_NAME, COUNT(*) ts_df_count, SUM(BYTES) all_bytes, SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) FROM cdb_temp_files d GROUP BY d.CON_ID, TABLESPACE_NAME) df, (SELECT d.CON_ID, TABLESPACE_NAME, SUM(BYTES) FREESIZ FROM cdb_free_space d GROUP BY d.CON_ID, TABLESPACE_NAME UNION ALL SELECT d.CON_ID, tablespace_name, SUM(d.BLOCK_SIZE * a.BLOCKS) bytes FROM gv$sort_usage a, cdb_tablespaces d WHERE a.tablespace = d.tablespace_name AND a.CON_ID = d.CON_ID GROUP BY d.CON_ID, tablespace_name) fs WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME AND ts.CON_ID = df.CON_ID AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+) AND ts.CON_ID = fs.CON_ID(+)) SELECT T.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#, t.TABLESPACE_NAME TS_Name, CONTENTS type, 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 DISTINCT T.CON_ID, '' PDBNAME, to_number('') TS#, 'ALL TS:' TS_Name, ' ' type, 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 GROUP BY rollup(CON_ID,PDBNAME) ) ORDER BY CON_ID,TS# ;
====
from
https://github.com/carlos-sierra/cscripts/blob/master/cdb_tablespace_usage_metrics.sql
SET HEA ON LIN 2490 PAGES 0 TAB OFF FEED OFF ECHO OFF VER OFF TRIMS ON TRIM ON TI OFF TIMI OFF LONG 240000 LONGC 2400 SERVEROUT OFF;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS';
SET RECSEP OFF;
--
CLEAR BREAK COMPUTE;
COL pdb_tablespace_name1 FOR A35 HEA 'PDB|TABLESPACE_NAME';
COL pdb_tablespace_name2 FOR A35 HEA 'PDB|TABLESPACE_NAME';
COL used_space_gbs1 FOR 999,990.000 HEA 'USED_SPACE|(GB)';
COL used_space_gbs2 FOR 999,990.000 HEA 'USED_SPACE|(GB)';
COL max_size_gbs1 FOR 999,990.000 HEA 'MAX_SIZE|(GB)';
COL max_size_gbs2 FOR 999,990.000 HEA 'MAX_SIZE|(GB)';
COL used_percent1 FOR 990.000 HEA 'USED|PERCENT';
COL used_percent2 FOR 990.000 HEA 'USED|PERCENT';
--
BREAK ON REPORT;
COMPUTE SUM LABEL 'TOTAL' OF used_space_gbs1 max_size_gbs1 used_space_gbs2 max_size_gbs2 ON REPORT;
--
COL output_file_name NEW_V output_file_name NOPRI;
SELECT 'cdb_tablespace_usage_metrics_'||LOWER(name)||'_'||LOWER(REPLACE(SUBSTR(host_name, 1 + INSTR(host_name, '.', 1, 2), 30), '.', '_'))||'_'||TO_CHAR(SYSDATE, 'yyyymmdd"T"hh24miss') output_file_name FROM v$database, v$instance;
--
WITH
t AS (
SELECT c.name||'('||c.con_id||')' pdb,
m.tablespace_name,
ROUND(m.used_percent, 3) used_percent, -- as per maximum size (considering auto extend)
ROUND(m.used_space * t.block_size / POWER(10, 9), 3) used_space_gbs,
ROUND(m.tablespace_size * t.block_size / POWER(10, 9), 3) max_size_gbs,
ROW_NUMBER() OVER (ORDER BY c.name, m.tablespace_name) row_number1,
ROW_NUMBER() OVER (ORDER BY m.used_percent DESC, m.used_space * t.block_size DESC, m.tablespace_size * t.block_size DESC) row_number2
FROM cdb_tablespace_usage_metrics m,
cdb_tablespaces t,
v$containers c
WHERE t.con_id = m.con_id
AND t.tablespace_name = m.tablespace_name
AND t.status = 'ONLINE'
AND t.contents = 'PERMANENT'
-- AND t.tablespace_name NOT IN ('SYSTEM', 'SYSAUX')
AND c.con_id = m.con_id
AND c.open_mode = 'READ WRITE'
)
SELECT t1.pdb||CHR(10)||' '||
t1.tablespace_name pdb_tablespace_name1,
t1.used_percent used_percent1,
t1.used_space_gbs used_space_gbs1,
t1.max_size_gbs max_size_gbs1,
'|'||CHR(10)||'|' "|",
t2.used_percent used_percent2,
t2.used_space_gbs used_space_gbs2,
t2.max_size_gbs max_size_gbs2,
t2.pdb||CHR(10)||' '||
t2.tablespace_name pdb_tablespace_name2
FROM t t1, t t2
WHERE t1.row_number1 = t2.row_number2
ORDER BY
t1.row_number1
/
No comments:
Post a Comment