Search This Blog

Total Pageviews

Monday, 31 October 2011

Oracle Tablespace space report

Tablespace space report
 

Bug 28821847  DBA_TABLESPACE_USAGE_METRICS Does Not Include Undo Tablespace Info


-- Tablespace space report
from 12c 

set 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
col free for a12
col "USED_SPACE" for a12 
col "TABLESPACE_SIZE" for a18
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,
dbms_xplan.FORMAT_SIZE(a.tablespace_size * b.block_size) as "TABLESPACE_SIZE",
dbms_xplan.FORMAT_SIZE((a.used_space * b.block_size)) as "USED_SPACE",
dbms_xplan.FORMAT_SIZE((a.tablespace_size * b.block_size) - (a.used_space * b.block_size) ) Free,
  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='PERMANENT'
and CONTENTS!='UNDO'
and a.con_id=b.con_id
and a.con_id=c.con_id(+)
union all 
-- for Undo <<<<<
SELECT  a.tablespace_name,a.con_id,nvl(PDB_NAME,'CDB$ROOT') PDB_NAME,round ((Used*100)/Total,2) "%Used_%",block_size,force_logging, bigfile,encrypted,allocation_type,status,contents,retention,extent_management,dbms_xplan.FORMAT_SIZE(Total) total ,dbms_xplan.FORMAT_SIZE(Used) Used,dbms_xplan.FORMAT_SIZE((Total - Used)) Free,round ((Used*100)/Total,2) "%Used_%"
FROM  ( SELECT b.con_id,SUM (maxbytes)  Total, b.tablespace_name,b.block_size,b.force_logging, bigfile,encrypted,allocation_type,b.status,contents,retention,extent_management,PDB_NAME
      FROM cdb_data_files a, cdb_tablespaces b,dba_pdbs c
      WHERE a.tablespace_name = b.tablespace_name 
	  and c.con_id=a.con_id
	  AND b.contents like '%UNDO%'
      GROUP BY b.con_id,b.tablespace_name,b.block_size,b.force_logging, bigfile,encrypted,allocation_type,b.status,contents,retention,extent_management,PDB_NAME) a,
    ( SELECT con_id,c.tablespace_name, SUM (bytes) Used
      FROM cdb_UNDO_EXTENTS c
      WHERE status <> 'EXPIRED' 
	  GROUP BY con_id,c.tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
      and a.con_id=b.con_id
     order by "USED_%" desc 
;


************************************************************************


set 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 
;




set head on  pagesize 300 linesize 200 numf 999999.99
col tablespace_name for a28
col status          for a20
col pdb_name        for a15
col CON_ID for 9999
col TABLESPACE_SIZE for a10 
col USED_SPACE for a10 
col TABLESPACE_FREE_SIZE for a10
col TotalSpace for a10 
col currentspace for a10



VARIABLE value1 NUMBER

exec SELECT  to_number(value) into :value1 FROM   v$parameter WHERE   name = lower('DB_BLOCK_SIZE') ;
/

col NUM_FILES for 999
col NUM_FILES1 for 9999
col "USED_PERCENT%" for 99
col num_files_left for 9999
col "Tb_status"  for a10
col "Datafilecountstatus"  for a20
select tf.con_id,tf.PDB_NAME,tf.status,tf.tablespace_name ,num_files
--,num_files1 num_files_left
,
(case when ( num_files1 <= 300 and num_files1 >= 250) then 'Bad <300 ***'     
     when ( num_files1<= 250 and num_files1>= 201) then 'warning <250 ***'  
     when ( num_files1<= 200) then 'critical <200 ***'                              
    else 'Good' end ) as "Datafilecountstatus" 
 ,
tf.TABLESPACE_SIZE,dbms_xplan.FORMAT_SIZE(TotalSpace) TotalSpace ,dbms_xplan.FORMAT_SIZE(currentspace) currentspace,tf.USED_SPACE,tf.TABLESPACE_FREE_SIZE,tf."USED_PERCENT%",tf."status1" Tb_status
from (select 
tb.con_id con_id,
nvl(pdb_name,'CDB$ROOT') PDB_NAME,
nvl(pdb.status,'CDB$ROOT') status,
tablespace_name tablespace_name ,
dbms_xplan.FORMAT_SIZE(tb.TABLESPACE_SIZE * :value1 )  					TABLESPACE_SIZE,
dbms_xplan.FORMAT_SIZE(tb.USED_SPACE * :value1 )       					USED_SPACE,
dbms_xplan.FORMAT_SIZE((tb.TABLESPACE_SIZE - tb.USED_SPACE) * :value1 ) 	TABLESPACE_FREE_SIZE,
trunc(used_percent)                                                         "USED_PERCENT%", 
case when ((used_percent) > 95.00) then '---(>95.00)% full ##'
                                   else 'good' end  as "status1"
from cdb_tablespace_usage_metrics tb,cdb_pdbs pdb
where 1=1  
and tb.con_id= pdb.con_id(+)
-- and pdb.con_id=3
)tf,
 (select 
        tablespace_name,count(distinct(file_id)) num_files,(1022 - count(distinct(file_id))) num_files1,
        round(sum(decode(AUTOEXTENSIBLE,'YES',GREATEST(MAXBYTES,bytes),'NO',bytes))) TotalSpace, 
		round(sum(bytes)) currentspace,
        round(sum(decode(AUTOEXTENSIBLE,'YES',greatest(maxbytes,bytes)-bytes,'NO',0))) reservespace
  from  cdb_data_files
  group by tablespace_name  ) df
  where 1=1
  and  df.tablespace_name=tf.tablespace_name
  ;
  

***** 
undo tablespace only



set pagesize 10000 linesize 300 tab off
 
col tablespace_name format A28          heading "Tablespace"
col ts_type         format A13          heading "TS Type"
col segments        format 999999       heading "Segments"
col files           format 9999
col allocated    format a14   		heading "Allocated Size"
col used         format a14    		heading "Used Space"
col Free         format a14     	heading "Free Space"
col used_pct        format 999   	heading "Used|%"
col max_ext_mb      format a14   	heading "Max Size"
col max_free_mb     format a14    	heading "Max Free"
col max_used_pct    format 999    	heading "Max Used|(%)"
 col max_size 		for a15
BREAK ON REPORT
COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE   ==========>" OF segments files allocated_mb used_mb Free_MB max_ext_mb 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)
-- 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,
      dbms_xplan.FORMAT_SIZE(a.bytes ) Allocated, 
       dbms_xplan.FORMAT_SIZE(u.bytes) Used, 
       dbms_xplan.FORMAT_SIZE(NVL(a.bytes - NVL(u.bytes, 0), 0)) Free,
       ROUND(NVL(u.bytes / a.bytes * 100, 0), 2) Used_pct, 
       dbms_xplan.FORMAT_SIZE(a.maxbytes )  max_size,
       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 11 desc 
/







set 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 for a15
col USED_SPACE for a12
col FREE for a8
col "USED_%" for 999.99
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,
  dbms_xplan.FORMAT_SIZE(a.tablespace_size * b.block_size) "TABLESPACE_SIZE",
  dbms_xplan.FORMAT_SIZE(a.used_space * b.block_size )     "USED_SPACE",
dbms_xplan.FORMAT_SIZE((a.tablespace_size * b.block_size) - (a.used_space * b.block_size))  Free,
b.CONTENTS ,
a.used_percent  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 b.CONTENTS like '%UNDO%' -- ??
--and CONTENTS='PERMANENT'
and a.con_id=b.con_id(+)
and a.con_id=c.con_id
order by "USED_%" desc 
;


--- with cdb_tablespace_usage_metrics and dbms_xplan.format_size

 

set pagesize 10000 linesize 300 tab off

col tablespace_name format A27              heading "Tablespace"
col ts_type         format A13              heading "TS Type"
col segments        format 999999           heading "Segments"
col files           format 9999
col used_pct        format 999              heading "Used|%"
col max_used_pct    format 999              heading "Max Used|(%)*"
col Max_Free        for A10      heading "Max_Free"
col ALLOCATED for a12
col USED for a10 
col FREE for a10 
col MAX_EXT for a10



BREAK ON REPORT
--COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE   ==========>" OF segments files allocated used Free max_ext Max_Free ON REPORT

select distinct tablespace_name,con_id,ts_type,status,files,segments,dbms_xplan.format_size(Allocated) Allocated,dbms_xplan.format_size(Used) Used,dbms_xplan.format_size(Free) Free,Used_pct
--, dbms_xplan.format_size(max_ext) max_ext
,dbms_xplan.format_size(Max_Free) Max_Free, Max_used_pct from (
WITH df AS (SELECT con_id,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 cdb_data_files GROUP BY con_id,tablespace_name),
     tf AS (SELECT con_id,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 cdb_temp_files GROUP BY con_id,tablespace_name),
     tm AS (SELECT con_id,tablespace_name, used_percent FROM cdb_tablespace_usage_metrics),
     ts AS (SELECT con_id,tablespace_name, COUNT(*) segcnt FROM cdb_segments GROUP BY con_id,tablespace_name)
SELECT distinct d.tablespace_name,
d.con_id,
       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,
     (a.bytes)  Allocated,
       (a.bytes - f.bytes) Used,
      (f.bytes) Free ,
       ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) Used_pct,
     (a.maxbytes)  max_ext,
	   (a.maxbytes  - (a.bytes - f.bytes) ) Max_Free,
       ROUND(NVL(m.used_percent,0), 2) Max_used_pct
  FROM cdb_tablespaces d, df a, tm m, ts s, (SELECT con_id,tablespace_name, SUM(bytes) bytes FROM cdb_free_space GROUP BY con_id,tablespace_name) f
 WHERE d.tablespace_name = a.tablespace_name
   and d.con_id = a.con_id
    AND d.con_id = s.con_id
     AND d.con_id = m.con_id
   AND d.tablespace_name = f.tablespace_name
   AND d.tablespace_name = m.tablespace_name
   AND d.tablespace_name = s.tablespace_name
     AND d.con_id = f.con_id
    AND NOT d.contents = 'UNDO'
  AND NOT ( d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' )
  --and  d.contents != 'TEMPORARY' 
 -- TEMP TS
union 
SELECT distinct d.tablespace_name,
d.con_id,
       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,
      (a.bytes)  Allocated,
       (t.ub*d.block_size) Used ,
      (a.bytes - (t.ub*d.block_size)) Free,
       ROUND(NVL((t.ub*d.block_size) / a.bytes * 100, 0), 2) Used_pct,
   (a.maxbytes )  max_size,
	(a.maxbytes  - (t.ub*d.block_size)) Max_Free,
       ROUND(NVL(m.used_percent,0), 2) Max_used_pct
  FROM cdb_tablespaces d, tf a, tm m, (SELECT con_id,ss.tablespace_name , sum(ss.used_blocks) ub FROM gv$sort_segment ss GROUP BY con_id,ss.tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name
   and d.con_id = a.con_id
      AND d.con_id = m.con_id
   AND d.tablespace_name = t.tablespace_name
   AND d.tablespace_name = m.tablespace_name
     AND d.con_id = t.con_id
   AND d.extent_management = 'LOCAL'
   AND d.contents = 'TEMPORARY'
/*
   union 
   -- UNDO TS 
SELECT distinct d.tablespace_name,
d.con_id,
       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,
    (a.bytes)  Allocated,
       (u.bytes) Used,
     (a.bytes - u.bytes)  Free,
       ROUND(NVL(u.bytes / a.bytes * 100, 0), 2) Used_pct,
     (a.maxbytes) max_size,
	  (a.maxbytes  - u.bytes) Max_Free ,
       ROUND(NVL(m.used_percent,0), 2) Max_used_pct
FROM cdb_tablespaces d, df a, tm m, ts s, (SELECT con_id,tablespace_name, SUM(bytes) bytes FROM cdb_undo_extents where status in ('ACTIVE','UNEXPIRED') GROUP BY con_id,tablespace_name) u
WHERE 1=1
--and d.tablespace_name = a.tablespace_name
   and d.con_id = a.con_id
   AND d.con_id = m.con_id
 --and d.tablespace_name = a.tablespace_name
 and d.con_id = a.con_id
AND d.tablespace_name = u.tablespace_name
AND m.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 13 desc
/
       




--- with distinct !!!!!!!!!!!!!!!!!!!!!

set pagesize 10000 linesize 300 tab off

col tablespace_name format A27              heading "Tablespace"
col ts_type         format A13              heading "TS Type"
col segments        format 999999           heading "Segments"
col files           format 9999
col used_pct        format 999              heading "Used|%"
col max_used_pct    format 999              heading "Max Used|(%)*"
col Max_Free        for A10      heading "Max_Free"
col ALLOCATED for a12
col USED for a10 
col FREE for a10 
col MAX_EXT for a10



BREAK ON REPORT
--COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE   ==========>" OF segments files allocated used Free max_ext Max_Free ON REPORT

select distinct * 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 cdb_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 cdb_temp_files GROUP BY tablespace_name),
     tm AS (SELECT tablespace_name, used_percent FROM cdb_tablespace_usage_metrics),
     ts AS (SELECT tablespace_name, COUNT(*) segcnt FROM cdb_segments GROUP BY tablespace_name)
SELECT distinct d.tablespace_name,
d.con_id,
       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,
      dbms_xplan.format_size(a.bytes)  Allocated,
       dbms_xplan.format_size(a.bytes - f.bytes) Used,
       dbms_xplan.format_size(f.bytes) Free ,
       ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) Used_pct,
      dbms_xplan.format_size(a.maxbytes)  max_ext,
	   dbms_xplan.format_size(a.maxbytes  - (a.bytes - f.bytes) ) Max_Free,
       ROUND(NVL(m.used_percent,0), 2) Max_used_pct
  FROM cdb_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM cdb_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 distinct d.tablespace_name,
d.con_id,
       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,
       dbms_xplan.format_size(a.bytes)  Allocated,
       dbms_xplan.format_size(t.ub*d.block_size) Used ,
      dbms_xplan.format_size(a.bytes - (t.ub*d.block_size)) Free,
       ROUND(NVL((t.ub*d.block_size) / a.bytes * 100, 0), 2) Used_pct,
       dbms_xplan.format_size(a.maxbytes )  max_size,
	 dbms_xplan.format_size(a.maxbytes  - (t.ub*d.block_size)) Max_Free,
       ROUND(NVL(m.used_percent,0), 2) Max_used_pct
  FROM cdb_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 distinct d.tablespace_name,
d.con_id,
       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,
     dbms_xplan.format_size(a.bytes)  Allocated,
       dbms_xplan.format_size(u.bytes) Used,
      dbms_xplan.format_size(a.bytes - u.bytes)  Free,
       ROUND(NVL(u.bytes / a.bytes * 100, 0), 2) Used_pct,
       dbms_xplan.format_size(a.maxbytes) max_size,
	  dbms_xplan.format_size(a.maxbytes  - u.bytes) Max_Free ,
       ROUND(NVL(m.used_percent,0), 2) Max_used_pct
FROM cdb_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM cdb_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
)
ORDER BY 13 desc

/






datafile info 




set linesize 300 pagesize 100
set heading on
col fname 		heading "Filename" 						format a85
col fnum 		heading "#" 							format 999
col ts 			heading "Tablespace|Name" 				format a15
col tb 			heading "Total|Potential|File Size" 	format a10
col cb  		heading "Total|Current|File Size" 		like tb
col used 		heading "Used" 							like tb
col free 		heading "Potential|Free" 				like tb
col autoext 	heading "Auto|Ext." 					format a4
col percentfree heading "% Free|of|Total|Bytes" 		format 999

break on report
compute sum of tb cb used free on report

-- spool TablespaceUsage.txt
select	substr(tablespace_name,1,15) ts
	,d.file_id fnum
	,dbms_xplan.FORMAT_SIZE(decode(e.file#,null,d.bytes,(e.maxextend * blksize)) ) tb
	,dbms_xplan.FORMAT_SIZE(d.bytes) cb
	,dbms_xplan.FORMAT_SIZE(decode(d.bytes,0,0,d.bytes-nvl(freebytes,0))) used
	,dbms_xplan.FORMAT_SIZE(decode(e.file#,null,d.bytes,(e.maxextend * blksize))  -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0))) free
	,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize)) -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/ decode(e.file#,null,d.bytes,(e.maxextend * blksize))),2) * 100 percentfree
	,decode(e.file#,null,'No','Yes') autoext
	,''''||substr(file_name,1,80)||'''' fname
from 	sys.dba_data_files d
	,(select file_id,sum(bytes) freebytes
		from sys.dba_free_space
		group by file_id) f
	,sys.filext$ e
	,v$datafile v
	,(select value blksize from v$parameter
		where name = 'db_block_size') b
where	d.file_id=f.file_id(+)
  and	d.file_id=e.file#(+)
  and	v.file#=d.file_id
and d.tablespace_name='Ts_TS'
order by tablespace_name,creation_time
/


==============================




 alter session set container=XXXXXXX;
 
 
-- alter session set "_push_join_predicate" = FALSE ;
 
define t_name='TS_TS'

set linesize 300 pagesize 40 time on timing on 
set heading on
col fname 							heading "Filename" 								format a90
col fnum 							heading "#" format 999
col ts 								heading "Tablespace|Name" 						format a15
col tb 								heading "Total|Potential|File Size" 			format 999,999,999,999,999,999
col cb 								heading "Total|Current|File Size" like tb
col used 							heading "Bytes Used" like tb
col free 							heading "Potential|Bytes Free" like tb
col autoext 						heading "Auto|Ext." 							format a4
col percentfree 					heading "% Free|of|Pot.|Total|Bytes" 			format 999

col tb1 							heading "Total|File Size" 						format a10
col cb1 like tb1
col free1 like tb1
col used1 like tb1

break on report
compute sum of tb cb used free on report

-- spool TablespaceUsage.txt

--select /*+ PARALLEL(50) */ substr(tablespace_name,1,15) ts
select substr(tablespace_name,1,15) ts
,d.file_id fnum
,dbms_xplan.FORMAT_SIZE(decode(e.file#,null,d.bytes,(e.maxextend * blksize)) ) tb1
,decode(e.file#,null,d.bytes,(e.maxextend * blksize)) tb
,d.bytes cb
,dbms_xplan.FORMAT_SIZE(d.bytes) cb1
,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) used
,dbms_xplan.FORMAT_SIZE(decode(d.bytes,0,0,d.bytes-nvl(freebytes,0))) used1
,decode(e.file#,null,d.bytes,(e.maxextend * blksize))-decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) free
,dbms_xplan.FORMAT_SIZE(decode(e.file#,null,d.bytes,(e.maxextend * blksize))  -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0))) free1
,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize)) -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/decode(e.file#,null,d.bytes,(e.maxextend * blksize))),2) * 100 percentfree
,decode(e.file#,null,'No','Yes') autoext
,''''||substr(file_name,1,85)||'''' fname
from sys.dba_data_files d
,(select file_id,sum(bytes) freebytes
from sys.dba_free_space
group by file_id) f
,sys.filext$ e
,v$datafile v
,(select value blksize from v$parameter
where name = 'db_block_size') b
where d.file_id=f.file_id(+)
and d.file_id=e.file#(+)
and v.file#=d.file_id
and d.tablespace_name='&t_name'
order by tablespace_name,creation_time
/



size in gb 

define t_name='xxx'

set linesize 300 pagesize 40 time on timing on 
set heading on
col fname 							heading "Filename" 								format a90
col fnum 							heading "FNo#" format 9999
col ts 								heading "Tablespace|Name" 						format a15
col tb 								heading "Total|Potential|File GbSize" 			format 99999
col cb 								heading "Total|Current|File GbSize" like tb
col used 							heading "Gb Used" like tb
col free 							heading "Potential|GB Free" like tb
col autoext 						heading "Auto|Ext." 							format a4
col percentfree 					heading "% Free|of|Total" 			format 999

col tb1 							heading "Total|File Size" 						format a10
col cb1 like tb1
col free1 like tb1
col used1 like tb1

break on report
compute sum of tb cb used free on report

-- spool TablespaceUsage.txt

--select /*+ PARALLEL(50) */ substr(tablespace_name,1,15) ts
select substr(tablespace_name,1,15) ts
,d.file_id fnum
,dbms_xplan.FORMAT_SIZE(decode(e.file#,null,d.bytes,(e.maxextend * blksize)) ) tb1
,(decode(e.file#,null,d.bytes,(e.maxextend * blksize)))/1024/1024/1024 tb
,(d.bytes/1024/1024/1024)  cb
,dbms_xplan.FORMAT_SIZE(d.bytes) cb1
,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0))/1024/1024/1024  used
,dbms_xplan.FORMAT_SIZE(decode(d.bytes,0,0,d.bytes-nvl(freebytes,0))) used1
,(decode(e.file#,null,d.bytes,(e.maxextend * blksize))-decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/1024/1024/1024  free
,dbms_xplan.FORMAT_SIZE(decode(e.file#,null,d.bytes,(e.maxextend * blksize))  -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0))) free1
,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize)) -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/decode(e.file#,null,d.bytes,(e.maxextend * blksize))),2) * 100 percentfree
,decode(e.file#,null,'No','Yes') autoext
,''''||substr(file_name,1,85)||'''' fname
from sys.dba_data_files d
,(select file_id,sum(bytes)/1024/1024/1024 freebytes
from sys.dba_free_space
group by file_id) f
,sys.filext$ e
,v$datafile v
,(select value blksize from v$parameter
where name = 'db_block_size') b
where d.file_id=f.file_id(+)
and d.file_id=e.file#(+)
and v.file#=d.file_id
and d.tablespace_name='&t_name'
order by tablespace_name,creation_time
/









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:

Oracle DBA

anuj blog Archive