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

/

if sql is running slow

Query Against Dictionary Views Extracting Tablespace Information is Slow or Appears to Hang (Doc ID 1292253.1)

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NO_INVALIDATE => FALSE);

 column owner format a6

 column table_name format a10

 column last_anal format a10

SELECT owner,  table_name,  last_analyzed FROM dba_tab_statistics

WHERE table_name='X$KTFBUE';

SELECT owner,  table_name,  last_analyzed FROM dba_tab_statistics

WHERE table_name='X$KGLCURSOR_CHILD';

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'SYS', TABNAME => 'X$KTFBUE',ESTIMATE_PERCENT=>100);

OWNER  TABLE_NAME LAST_ANAL

------ ---------- ---------

SYS    X$KTFBUE   07-JAN-25

 

=====

set linesize 300 

col ALLOC for a10

col FREE  for a10

col USED  for a10

col MAX  for a10

SELECT a.tablespace_name,

 dbms_xplan.FORMAT_SIZE( ROUND (a.bytes_alloc ) ) alloc,

  dbms_xplan.FORMAT_SIZE(ROUND (NVL (b.bytes_free, 0) , 2)) free,

  dbms_xplan.FORMAT_SIZE(ROUND ((a.bytes_alloc         - NVL (b.bytes_free, 0)) , 2 )) used,

    dbms_xplan.FORMAT_SIZE(ROUND (maxbytes               / 1048576, 2) ) MAX,

  ROUND ((NVL (b.bytes_free, 0) / a.bytes_alloc) * 100, 2) pct_free,

  100 - ROUND ((NVL (b.bytes_free, 0) / a.bytes_alloc) * 100, 2) pct_used

FROM

  (SELECT f.tablespace_name,

    SUM (f.BYTES) bytes_alloc,

    SUM (DECODE (f.autoextensible, 'YES', f.maxbytes, 'NO', f.BYTES ) ) maxbytes

  FROM dba_data_files f

  GROUP BY tablespace_name

  ) a,

  (SELECT f.tablespace_name,

    SUM (f.BYTES) bytes_free

  FROM dba_free_space f

  GROUP BY tablespace_name

  ) b

WHERE a.tablespace_name = b.tablespace_name(+)

UNION ALL

SELECT h.tablespace_name,

  dbms_xplan.FORMAT_SIZE(ROUND (SUM (h.bytes_free           + h.bytes_used) , 2)) alloc,

  dbms_xplan.FORMAT_SIZE(ROUND ( SUM ((h.bytes_free         + h.bytes_used) - NVL (p.bytes_used, 0)) , 2 )) free,

  dbms_xplan.FORMAT_SIZE(ROUND (SUM (NVL (p.bytes_used, 0)) , 2)) used,

  dbms_xplan.FORMAT_SIZE(ROUND ( ( SUM ( (h.bytes_free      + h.bytes_used) - NVL (p.bytes_used, 0) ) / SUM (h.bytes_used + h.bytes_free) ) * 100, 2 )) pct_free,

  100  - ROUND ( ( SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0) ) / SUM (h.bytes_used + h.bytes_free) ) * 100, 2 ) pct_used,

  ROUND (SUM (f.maxbytes), 2) MAX

FROM SYS.v_$temp_space_header h,

  SYS.v_$temp_extent_pool p,

  dba_temp_files f

WHERE p.file_id(+)       = h.file_id

AND p.tablespace_name(+) = h.tablespace_name

AND f.file_id            = h.file_id

AND f.tablespace_name    = h.tablespace_name

GROUP BY h.tablespace_name

ORDER BY 1 ;

TABLESPACE_NAME                ALLOC      FREE       USED       MAX          PCT_FREE   PCT_USED

------------------------------ ---------- ---------- ---------- ---------- ---------- ----------

SYSAUX                         2000M      1621M      378M       2000            81.08      18.92

SYSTEM                         3000M      2634M      365M       3000            87.81      12.19

TEMP                           200M       197M       3072K      98                1.5          0

UNDOTBS1                       300M       57M        242M       300             19.25      80.75

UNDOTBS2                       100M       89M        10M        100             89.44      10.56

Oracle database check script

Database_check2.sql




--- Author: Francisco Munoz Alvarez
--- Date : 20/07/2008
--- Script: Checklist v.1.3


COLUMN y new_value sid NOPRINT
SELECT name||'_'||TO_CHAR(sysdate, 'ddmonyy_hh24mi') y FROM v$database;
SPOOL checklist_&sid..txt

alter system checkpoint;

alter system check datafiles;

Set Linesize 200
Set Pagesize 45
Set SQLPROMPT 'Sql>'
Set Desc Linenum On

Set Arraysize 1
Set Long 2000
Set Serveroutput On size 800000 ;

Set Heading Off
Set Feedback Off
Set Verify Off

Column Var_Date new_value Var_Date noprint

Select To_Char(Sysdate, 'DD-MM-YYYY HH24:MI') Var_Date from v$database ;



-- Prompt ---

Select
' ****** &Var_Date ************** Base : ' || Name || ' ************** '
from
v$database;

Set Heading On

column sid heading "Id" format 9999
column spid heading "Unix" format A7
column username heading "Utilis." format A20
column terminal heading "Terminal" format A11
column program heading "Programme" format A27 word_wrapped

select s.sid, p.spid, substr(s.username,1,20) username, s.terminal, p.Program
from v$session s, v$process p
where s.paddr = p.addr
and s.sid = (select sid from v$mystat where rownum=1)
;

Set Heading Off
Set Termout Off

Column Var_Prompt new_value Var_Prompt noprint

Select ''''||Rpad(Lpad(Initcap(Substr(Name,1,4)),3,'*'),4,'*')||'>''' Var_Prompt from v$database ;


Set SQLPROMPT &Var_Prompt

Set Termout On

Prompt

-- Prompt ---


Set Heading On
Set Feedback On

-- prompt

prompt
prompt Oracle Instance Information
prompt

Set Heading Off
Set Feedback Off
Set Verify Off

column status format a120 wrap heading "Status"

Select status_01||' | '||status_02 status
From
(Select ' Host_Name '||Lpad(Host_Name,18) status_02 from V$Instance)
, (Select ' Cpu_Count '||Lpad(value,8) status_01 from V$PARAMETER where name='cpu_count' and value is not null)
Union
Select status_01||' | '||status_02 status
From
(Select ' Instance_Name '||Lpad(Instance_Name,12) Status_01 from V$Instance)
, (Select ' Database_Status '||Lpad(Database_Status,12) Status_02 from V$Instance)
Union
Select status_01||' | '||status_02 status
From
(Select ' Startup_Time '||To_Char(Startup_Time, 'DD-MM-YYYY HH24:MI') Status_02 from V$Instance)
, (Select ' Status '||Lpad(Status,12) Status_01 from V$Instance)
Union
Select status_01||' | '||status_02 status
From
(Select ' Version '||Lpad(Version,12) Status_01 from V$Instance)
, (Select ' Instance_Role '||Lpad(Instance_Role,16) Status_02 from V$Instance)
;

select ' Database log mode '||log_mode "Parameter" from V$DATABASE
union
select ' Archive destination '||value from V$PARAMETER where (name='log_archive_dest' or name = 'log_archive_dest_1') and value is not null
;

select ' Spfile '||value from V$PARAMETER where name='spfile' and value is not null
union
select ' Background Dump Dest '||value from V$PARAMETER where name='background_dump_dest' and value is not null
;


col OWNER format a15
col SEGMENT format a20
col PARTITION format a12

prompt Check Redo Size

select distinct ' Redo size (Kb) '|| Lpad(To_Char(round(bytes/1024)),'16') Status from v$log;


Prompt


Declare
--
Cursor Cur_Req Is
Select distinct object_name
from dba_objects
where object_name='DBA_TEMP_FILES'
;
--
Cursor Cur_SGA Is
select ' SGA (Mb) '||Lpad(To_Char(Round(sum (value)/1024/1024)),8) status_02 from v$sga;
--
W_Texte Varchar2(2000);
Curs Integer;
Return_code Integer;
W_Temp Varchar2(40);
--
X Varchar2(100);
Nb_Tf Number(8);
SGA Varchar2(40);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
Open Cur_SGA;
Fetch Cur_SGA Into SGA;
Close Cur_SGA;
--
If X Is Not Null Then
--
W_Texte := 'Select ''Database Space (Mb) ''||Lpad(To_Char(Round((nb_ctl.nb * ctl_size.the_size) ';
W_Texte := W_texte ||' + (rlf_size.the_size/1024) ';
W_Texte := W_texte ||' + (dtf_size.the_size/1024) ';
W_Texte := W_texte ||' + (nvl(dtft_size.the_size,0)/1024))),8) From ';
W_Texte := W_texte ||' (select count(1) nb from v$controlfile) nb_ctl ';
W_Texte := W_texte ||' , (select round(sum(record_size)/1024) the_size from V$CONTROLFILE_RECORD_SECTION) ctl_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from v$log) rlf_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from dba_data_files) dtf_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from dba_temp_files) dtft_size';
--
Else
--
W_Texte := 'Select ''Database Space (Mb) ''||Lpad(To_Char(Round((nb_ctl.nb * ctl_size.the_size) ';
W_Texte := W_texte ||' + (rlf_size.the_size/1024) ';
W_Texte := W_texte ||' + (dtf_size.the_size/1024) ';
W_Texte := W_texte ||' + (nvl(dtft_size.the_size,0)/1024))),8) From ';
W_Texte := W_texte ||' (select count(1) nb from v$controlfile) nb_ctl ';
W_Texte := W_texte ||' , (select round(sum(record_size)/1024) the_size from V$CONTROLFILE_RECORD_SECTION) ctl_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from v$log) rlf_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from dba_data_files) dtf_size ';
--
End If;
--
Curs := Dbms_Sql.Open_Cursor;
--
Dbms_Sql.Parse(Curs, W_texte, Dbms_Sql.Native);
Dbms_Sql.Define_Column(Curs, 1, W_Temp, 40);
--
Return_Code := Dbms_Sql.Execute(Curs);
--
IF dbms_sql.FETCH_ROWS(Curs)>0 THEN
Dbms_Sql.Column_Value(Curs, 1, W_Temp);
End If;
--
Dbms_OutPut.Put_Line('-- '||W_Temp||' | '||SGA||' --');
--
Dbms_Sql.Close_Cursor(Curs);
--
End;
/





Declare
--
Cursor Cur_Req Is
Select distinct object_name
from dba_objects
where object_name='DBA_TEMP_FILES'
;
--
Cursor Cur_Df Is
Select Count(*)
From dba_data_files
;
--
W_Texte Varchar2(2000);
Curs Integer;
Return_code Integer;
W_Temp Varchar2(20);
--
X Varchar2(100);
Nb_Tf Number(8);
Nb_Df Number(8);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
Open Cur_Df;
Fetch Cur_Df Into Nb_Df;
Close Cur_Df;
--
If X Is Not Null Then
--
W_Texte := 'Select To_Char(Count(*)) From dba_temp_files';
--
Curs := Dbms_Sql.Open_Cursor;
--
Dbms_Sql.Parse(Curs, W_texte, Dbms_Sql.Native);
Dbms_Sql.Define_Column(Curs, 1, W_Temp, 20);
--
Return_Code := Dbms_Sql.Execute(Curs);
--
IF dbms_sql.FETCH_ROWS(Curs)>0 THEN
Dbms_Sql.Column_Value(Curs, 1, W_Temp);
End If;
--
Dbms_OutPut.Put_Line('-- Nb. Datafiles '||Lpad(To_Char(Nb_Df),5)||' | Nb. Tempfiles '||Lpad(W_Temp,5)||' --' );
--
Dbms_Sql.Close_Cursor(Curs);
--
Else
Dbms_OutPut.Put_Line('-- Nb. Datafiles '||Lpad(To_Char(Nb_Df),5));
End If;
--
End;
/


prompt
prompt


prompt
prompt Instance CheckList
prompt



Select status_01||' | '||status_02 status
From
(Select ' Instance Status '||Lpad('OK',12) Status_01 from V$Instance)
, (Select ' Listener Status '||Lpad('OK',12) Status_02 from V$Instance);


prompt
prompt


prompt
prompt Performance Memory CheckList
prompt
prompt


declare
cursor c1 is
select count(*)
from v$session
where serial# != 1
and osuser is not null;
---
cursor c2 is
select count(*)
from v$session
where SERIAL# != 1
and osuser is not null
and status='ACTIVE';
---
cursor c3 is
select ((1-a.value/(b.value+c.value))*100)
from v$sysstat a, v$sysstat b , v$sysstat c
where a.name = 'physical reads'
and b.name = 'db block gets'
and c.name = 'consistent gets';
---
cursor c4 is
select ((sum(pins)/(sum(pins)+sum(reloads))*100))
from v$librarycache;
---
cursor c5 is
select ((1-(sum(getmisses)/sum(gets)))*100)
from v$rowcache;
---
cursor c6 is
select (count(*)/24)
from sys.v_$log_history
where first_time > sysdate - 1;
---
cursor c7 is
select count(*)
From dba_jobs Where broken != 'N';
---
cursor c8 is
select count(*)
From v$shared_pool_reserved where request_failures != 0;
---
cursor c9 is
select round(100*retries.value/entries.value,4)
from gv$sysstat retries, gv$sysstat entries
where retries.name = 'redo buffer allocation retries' AND
entries.name = 'redo entries' and
entries.inst_id = retries.inst_id;

---
cursor c10 is
select nvl(total_waits,0)
from gv$system_event
where event like '%log buffer space%';
---
cursor c11 is
select round(100*SUM(getmisses)/SUM(gets),3)
from gv$rowcache;
---
A Number(8);
B Number(8);
C Number(8);
D Number(8);
E Number(8);
F Number(8);
G Number(8);
H Number(8);
I Number(8,4);
J Number(8);
K Number(8,3);
---
AA Varchar2(5);
BB Varchar2(5);
CC Varchar2(5);
DD Varchar2(5);
EE Varchar2(5);
FF Varchar2(5);
GG Varchar2(5);
HH Varchar2(5);
II Varchar2(5);
JJ Varchar2(5);
KK Varchar2(5);
---
begin
Open c1;
Fetch c1 Into A;
Close c1;
Open c2;
Fetch c2 Into B;
Close c2;
Open c3;
Fetch c3 Into C;
Close c3;
Open c4;
Fetch c4 Into D;
Close c4;
Open c5;
Fetch c5 Into E;
Close c5;
Open c6;
Fetch c6 Into F;
Close c6;
Open c7;
Fetch c7 Into G;
Close c7;
Open c8;
Fetch c8 Into H;
Close c8;
Open c9;
Fetch c9 Into I;
Close c9;
Open c10;
Fetch c10 Into J;
Close c10;
Open c11;
Fetch c11 Into K;
Close c11;
if A <= 700 then
AA := 'OK';
else
AA := 'NO';
end if;
if B <= 15 then
BB := 'OK';
else
BB := 'NO';
end if;
if C >= 80 then
CC := 'OK';
else
CC := 'NO';
end if;
if D >= 99 then
DD := 'OK';
else
DD := 'NO';
end if;
if E >= 80 then
EE := 'OK';
else
EE := 'NO';
end if;
if F <= 5 then
FF := 'OK';
else
FF := 'NO';
end if;
if G = 0 then
GG := 'OK';
else
GG := 'NO';
end if;
if H = 0 then
HH := 'OK';
else
HH := 'NO';
end if;
if I <= 0.0010 then
II := 'OK';
else
II := 'NO';
end if;
if J = 0 then
JJ := 'OK';
elsif J is null then
JJ := 'OK';
J := 0;
else
JJ := 'NO';
end if;
if K <= 0.015 then
KK := 'OK';
else
KK := 'NO';
end if;
dbms_OutPut.Put_Line('-- '||'Total Sessions < 700 '||Lpad(AA,5)||' - '||Lpad(To_Char(A),5));
dbms_OutPut.Put_Line('-- '||'Active sessions number <15 '||Lpad(BB,5)||' - '||Lpad(To_Char(B),5));
dbms_OutPut.Put_Line('-- '||'Data Buffer Hit Ratio > 80 '||Lpad(CC,5)||' - '||Lpad(To_Char(C),5));
dbms_OutPut.Put_Line('-- '||'L.Buffer Reload Pin Ratio > 99'||Lpad(DD,5)||' - '||Lpad(To_Char(D),5));
dbms_OutPut.Put_Line('-- '||'Row Cache Miss Ratio < 0.015 '||Lpad(KK,5)||' - '||Lpad(To_Char(K),5));
dbms_OutPut.Put_Line('-- '||'Dict.Buffer Hit Ratio > 80 '||Lpad(EE,5)||' - '||Lpad(To_Char(E),5));
dbms_OutPut.Put_Line('-- '||'Log Buffer Waits = 0 '||Lpad(JJ,5)||' - '||Lpad(To_Char(J),5));
dbms_OutPut.Put_Line('-- '||'Log Buffer Retries < 0.0010 '||Lpad(II,5)||' - '||Lpad(To_Char(I),5));
dbms_OutPut.Put_Line('-- '||'Switch number (Daily Avg) < 5 '||Lpad(FF,5)||' - '||Lpad(To_Char(F),5));
dbms_OutPut.Put_Line('-- '||'Jobs Broken = 0 '||Lpad(GG,5)||' - '||Lpad(To_Char(G),5));
dbms_OutPut.Put_Line('-- '||'Shared_Pool Failure = 0 '||Lpad(HH,5)||' - '||Lpad(To_Char(H),5));
end;
/


prompt
prompt
prompt Storage CheckList
prompt
prompt

Select status_01||' | '||status_02 status
From
(select distinct
decode (status
, 'ONLINE', ' V$Datafile Status '||Lpad('OK',12)
, 'SYSTEM', ' V$Datafile Status '||Lpad('OK',12)
, ' V$Datafile Status '||Lpad('NO',12)
) STATUS_01 from v$datafile)
, (select distinct
decode (status
, 'ONLINE', ' V$Tempfile Status '||Lpad('OK',14)
, 'SYSTEM', ' V$Tempfile Status '||Lpad('OK',14)
, ' V$Tempfile Status '||Lpad('NO',14)
) STATUS_02 from v$tempfile)
Union
Select status_01||' | '||status_02 status
From
(select distinct
decode (status
, 'ONLINE', ' Dba_Tablespaces Status '||Lpad('OK',7)
, 'READ ONLY', ' Dba_Tablespaces Status '||Lpad('OK',7)
, ' Dba_Tablespaces Status '||Lpad('NO',7)
) STATUS_01 from dba_tablespaces)
, (select distinct
decode (status
, 'CURRENT', ' V$Log Status '||Lpad('OK',19)
, 'ACTIVE', ' V$Log Status '||Lpad('OK',19)
, 'INACTIVE', ' V$Log Status '||Lpad('OK',19)
, ' V$Log Status '||Lpad('NO',19)
) STATUS_02 from v$log)
union
Select status_01||' | '||status_02 status
From
(select
distinct
decode (count(1)
, 0, ' V$Recover_File '||Lpad('OK',15)
, ' V$Recover_File '||Lpad('NO',15)
) STATUS_01 from v$recover_file)
, (select
distinct
decode (count(1)
, 0, ' V$Recovery_Log '||Lpad('OK',17)
, ' V$Recovery_Log '||Lpad('NO',17)
) STATUS_02 from v$recovery_log)
;


prompt


declare
cursor c1 is
select count(*)
from v$backup where status != 'NOT ACTIVE';

---
cursor c2 is
select count(*)
from ( select sum (bytes)/1048576 free, max (bytes)/1048576 fragmax, tablespace_name from sys.dba_free_space group by tablespace_name ) fsp, ( select sum(bytes)/1048576 alloc, tablespace_name from sys.dba_data_files group by tablespace_name) df, dba_tablespaces dt where fsp.tablespace_name (+) = df.tablespace_name and df.tablespace_name = dt.tablespace_name and dt.status = 'ONLINE' and (((alloc - nvl (free, 0)) / alloc) * 100 > 95);
---
cursor c3 is
select count(*)
From dba_objects where status != 'VALID'
and owner not in ('PUBLIC', 'SYS', 'SYSTEM','OUTLN','WMSYS','MDSYS','CTXSYS','DBSNMP');
---
cursor c4 is
select count(*)
from dba_indexes where status = 'UNUSABLE';
---
cursor c5 is
select count(*)
from dba_triggers where status != 'ENABLED'
and owner not in ('PUBLIC', 'SYS', 'SYSTEM','OUTLN','WMSYS','MDSYS','CTXSYS','DBSNMP');
---
cursor c6 is
select count(*)
From dba_constraints where status != 'ENABLED'
and owner not in ('PUBLIC', 'SYS', 'SYSTEM','OUTLN','WMSYS','MDSYS','CTXSYS','DBSNMP');
---
A Number(8);
B Number(8);
C Number(8);
D Number(8);
E Number(8);
F Number(8);
---
AA Varchar2(5);
BB Varchar2(5);
CC Varchar2(5);
DD Varchar2(5);
EE Varchar2(5);
FF Varchar2(5);
---
begin
Open c1;
Fetch c1 Into A;
Close c1;
Open c2;
Fetch c2 Into B;
Close c2;
Open c3;
Fetch c3 Into C;
Close c3;
Open c4;
Fetch c4 Into D;
Close c4;
Open c5;
Fetch c5 Into E;
Close c5;
Open c6;
Fetch c6 Into F;
Close c6;
if A = 0 then
AA := 'OK';
else
AA := 'NO';
end if;
if B = 0 then
BB := 'OK';
else
BB := 'NO';
end if;
if C = 0 then
CC := 'OK';
else
CC := 'NO';
end if;
if D = 0 then
DD := 'OK';
else
DD := 'NO';
end if;
if E = 0 then
EE := 'OK';
else
EE := 'NO';
end if;
if F = 0 then
FF := 'OK';
else
FF := 'NO';
end if;
dbms_OutPut.Put_Line('-- '||'Tablespace in Backup Mode = 0 '||Lpad(AA,5)||' - '||Lpad(To_Char(A),5));
dbms_OutPut.Put_Line('-- '||'Tablespace < 95% '||Lpad(BB,5)||' - '||Lpad(To_Char(B),5));
dbms_OutPut.Put_Line('-- '||'Objects Invalid = 0 '||Lpad(CC,5)||' - '||Lpad(To_Char(C),5));
dbms_OutPut.Put_Line('-- '||'Indexes unusable = 0 '||Lpad(DD,5)||' - '||Lpad(To_Char(D),5));
dbms_OutPut.Put_Line('-- '||'Trigger Disabled = 0 '||Lpad(EE,5)||' - '||Lpad(To_Char(E),5));
dbms_OutPut.Put_Line('-- '||'Constraint Disabled = 0 '||Lpad(FF,5)||' - '||Lpad(To_Char(F),5));
end;
/



declare
cursor c1 is
select count(*)
from dba_segments
where max_extents-extents < 10 AND
segment_type <> 'CACHE';
---
cursor c2 is
select count(*)
from sys.dba_segments a
where a.tablespace_name not like 'T%MP%'
and next_extent * 2 > (
select max(b.bytes)
from dba_free_space b
where a.tablespace_name = b.tablespace_name);
---
cursor c3 is
select count(*)
from sys.dba_segments
where owner not in ('PUBLIC', 'SYS', 'SYSTEM','OUTLN','WMSYS','MDSYS','CTXSYS','DBSNMP')
and tablespace_name = 'SYSTEM';
---
cursor c4 is
select count(*)
from
( select substr(a.table_name,1,30) table_name,
substr(a.constraint_name,1,30) constraint_name,
max(decode(position, 1, substr(column_name,1,30),NULL)) ||
max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,16,', '||substr(column_name,1,30),NULL)) columnsx
from dba_cons_columns a, dba_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
and b.owner not in ('PUBLIC', 'SYS', 'SYSTEM','OUTLN','WMSYS','MDSYS','CTXSYS','DBSNMP')
group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
max(decode(column_position, 1, substr(column_name,1,30),NULL)) ||
max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columnsx
from dba_ind_columns
group by substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name (+)
and b.columnsx (+) like a.columnsx || '%'
and b.table_name is null;
A Number(8);
B Number(8);
C Number(8);
D Number(8);
---
AA Varchar2(5);
BB Varchar2(5);
CC Varchar2(5);
DD Varchar2(5);
---
begin
Open c1;
Fetch c1 Into A;
Close c1;
Open c2;
Fetch c2 Into B;
Close c2;
Open c3;
Fetch c3 Into C;
Close c3;
Open c4;
Fetch c4 Into D;
Close c4;
if A = 0 then
AA := 'OK';
else
AA := 'NO';
end if;
if B = 0 then
BB := 'OK';
else
BB := 'NO';
end if;
if C = 0 then
CC := 'OK';
else
CC := 'NO';
end if;
if D = 0 then
DD := 'OK';
else
DD := 'NO';
end if;
dbms_OutPut.Put_Line('-- '||'Objects close max extents = 0 '||Lpad(AA,5)||' - '||Lpad(To_Char(A),5));
dbms_OutPut.Put_Line('-- '||'Objects can not extent = 0 '||Lpad(BB,5)||' - '||Lpad(To_Char(B),5));
dbms_OutPut.Put_Line('-- '||'User Objects on Systems = 0 '||Lpad(CC,5)||' - '||Lpad(To_Char(C),5));
dbms_OutPut.Put_Line('-- '||'FK Without Index = 0 '||Lpad(DD,5)||' - '||Lpad(To_Char(D),5));
end;
/


prompt
prompt
prompt Datagard CheckList
prompt


declare
cursor c1 is
select count(*)
FROM gv$dataguard_status
WHERE severity IN ('Error','Fatal','Warning');
---
cursor c2 is
select count(*)
FROM v$archive_gap;
---
cursor c3 is
select max(sequence#) from v$archived_log where applied = 'YES';
---
cursor c4 is
select max(sequence#) from v$archived_log;
---
cursor c5 is
select value from v$parameter where name = 'log_archive_dest_2';
---
cursor c6 is
select to_number(substr(version,1,3)) from v$instance;
---
A Number(8);
B Number(8);
C Number(10);
D Number(10);
E Number(10);
F Number(8,2);
---
AA Varchar2(5);
BB Varchar2(5);
CC Varchar2(5);
DD Varchar2(5);
EE Varchar2(50);
---
begin
Open c1;
Fetch c1 Into A;
Close c1;
Open c2;
Fetch c2 Into B;
Close c2;
Open c3;
Fetch c3 Into C;
Close c3;
Open c4;
Fetch c4 Into D;
Close c4;
Open c5;
Fetch c5 Into EE;
Close c5;
Open c6;
Fetch c6 Into F;
Close c6;
if A = 0 then
AA := 'OK';
elsif A is null then
AA := 'OK';
A := 0;
else
AA := 'NO';
end if;
if B = 0 then
BB := 'OK';
elsif B is null then
BB := 'OK';
B := 0;
else
BB := 'NO';
end if;
if D is null then
D := 0;
end if;
if C is null then
C := 0;
end if;
E := ( D - C);
if E <= 5 then
CC := 'OK';
else
CC := 'NO';
end if;
if EE is not null and F >= 9 then
dbms_OutPut.Put_Line('-- '||'Datagard Errors = 0 '||Lpad(AA,5)||' - '||Lpad(To_Char(A),5));
dbms_OutPut.Put_Line('-- '||'Datagard Gap = 0 '||Lpad(BB,5)||' - '||Lpad(To_Char(B),5));
dbms_OutPut.Put_Line('-- '||'Archives not Aplied < 5 '||Lpad(CC,5)||' - '||Lpad(To_Char(E),5));
else
dbms_OutPut.Put_Line('-- '||'No Datagard Available or Database version is not supported for this check!');
end if;
end;
/



prompt

prompt
prompt Installed options :
prompt


select ' - ' || parameter || ' option'
from sys.v_$option
where value = 'TRUE'
;


set head on

Prompt

set feedback on


prompt
prompt V$Recovery_Log Status
prompt

column Thread# format 9999999 heading "Thread"
column Sequence# format 9999999 heading "Sequence"
column archive_name format A60 wrap heading "Archive|Name"
column Time heading "Time"

select *
from v$recovery_log
order by thread#
;


prompt
prompt V$Recover_File Status
prompt

column file# format 9999999 heading "File#"
column online format A10 heading "Online"
column online_status format A10 heading "Online|Status"
column error format A20 wrap heading "Error"
column change format 99999 heading "Change"
column Time heading "Time"

select * from v$recover_file
order by file# ;


prompt
prompt V$Log Status
prompt

Declare
--
Cursor Cur_Req Is
select 'X'
from v$log
where status not in ('ACTIVE', 'CURRENT', 'INACTIVE')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- V$Log Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/

clear breaks
break on redogroup -
skip 1

column redogroup format 99999 heading "Group"
column redothread format 99999 heading "Thread"
column redosequence format 999999 heading "Sequence"
column file_name format A55 heading "RedoLog Name"
column KB format 99999999B heading "Size|(Kb)"
column status format A5 heading "Stat." Trunc

select
lf.group# redogroup
, l.thread# redothread
, l.sequence# redosequence
, lf.member file_name
, l.bytes/1024 Kb
, l.status status
from
v$logfile lf
, v$log l
where
l.group# = lf.group#
and
l.status not in ('ACTIVE', 'CURRENT', 'INACTIVE')
order by
lf.group#
, l.thread#
, l.sequence#
, lf.member
;

clear breaks


prompt
prompt V$Tempfile Status
prompt

Declare
--
Cursor Cur_Req Is
select
'X'
from
v$tempfile
where
status not in ('ONLINE', 'SYSTEM')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- V$Tempfile Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/

clear breaks
break on status skip 1


column name format a60 wrap heading "DataFile Name"

select
name
, status
from V$tempfile
where status not in ('ONLINE', 'SYSTEM')
Order By Status , Name
;

clear breaks


prompt
prompt V$Datafile Status
prompt

Declare
--
Cursor Cur_Req Is
select
'X'
from v$datafile
where status not in ('ONLINE', 'SYSTEM')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- V$Datafile Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/

clear breaks
break on status skip 1


column name format a60 wrap heading "DataFile Name"

select
name
, status
from V$datafile
where status not in ('ONLINE', 'SYSTEM')
Order By Status , Name
;

clear breaks


prompt -----------------------------
prompt Dba_Tablespaces Status
prompt ---------------------------

Declare
--
Cursor Cur_Req Is
select
'X'
from dba_tablespaces
where status not in ('ONLINE', 'READ ONLY')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Dba_Tablespaces Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/

column tblsp format a20 wrap heading "Tablespace Name"
column contents format a10 heading "Content"

clear breaks
break on contents -
skip 1

select
contents
, tablespace_name tblsp
, status
from dba_tablespaces
where status not in ('ONLINE', 'READ ONLY')
order by contents
;


prompt
prompt Tablespaces Backup Mode
prompt

Declare
--
Cursor Cur_Req Is
select 'X'
from v$backup bck
, dba_data_files df
where bck.file# = df.file_id
and bck.status != 'NOT ACTIVE'
and 'ARCHIVELOG' = (select log_mode from v$database);
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Tablespaces in Backup Mode ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/

column tablespace_name format a18 wrap heading "Tablespace Name"
column file_name format a50 wrap heading "DataFile Name"
column status format a12 wrap heading "Status"
column change# format 999999999999 heading "Change"
column time format a18 wrap heading "Time"

select
df.tablespace_name
, df.file_name
, bck.status
, bck.change#
, To_Char(bck.time,'DD-MM-YYYY HH24:MI') time
from
v$backup bck
, dba_data_files df
where
bck.file# = df.file_id
and bck.status != 'NOT ACTIVE'
and 'ARCHIVELOG' = (select log_mode from v$database)
order by df.tablespace_name, df.file_name;


prompt
prompt Lock list
prompt

Declare
--
Cursor Cur_Req Is
select
'X'
from
v$session s
, v$process p
, v$lock l
, dba_objects o
where
s.paddr = p.addr
And l.sid=s.sid
And l.id1 = o.object_id
And s.username is NOT NULL
And l.ctime > 60
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Lock list ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/

column username heading "Utilis." format A15
column sid heading "Id" format 9999
column spid heading "Unix" format A7
column state heading "Etat" format A15
column Lmode_H heading "Lock mode" format A15
column terminal heading "Terminal" format A10
column command heading "C" format 99
Column serial# heading "Serial#" format 99999
column ctime heading "Duration" format 999999
column logon heading "Date Connexion" format A16
column object_name heading "Object Name" format A18

select
s.sid
, s.serial#
, p.spid
, substr(s.username,1,15) username
, s.terminal
, l.type
, to_char(s.logon_time,'DD-MM-YYYY HH24:MI') logon
, decode(l.lmode, 1,'null' , 2,'Row share'
, 3,'Row Exclusive' , 4,'Share'
, 5,'Share Row Excl.' , 6,'Exclusive') Lmode_H
, o.object_name
, Round(ctime/60) ctime
from
v$session s
, v$process p
, v$lock l
, dba_objects o
where
s.paddr = p.addr
And l.sid=s.sid
And l.id1 = o.object_id
And s.username is NOT NULL
And l.ctime > 60
;


prompt
prompt
prompt Tablespaces
prompt



Set Heading Off
Set Termout Off

Column Var_DB_BLOCK_SIZE new_value Var_DB_BLOCK_SIZE noprint

Select value Var_DB_BLOCK_SIZE
from v$parameter
where Upper(name) = Upper ('db_block_size')
;

Set Termout On
Set Heading On



clear breaks
break on contents -
skip 1
compute Sum of alloc used free nbfrag on contents


column tblsp format a20 wrap heading "Tablespace Name"
column Alloc format 999,999 heading "Alloc|(Mb)"
column Free format 999,999 heading "Free|(Mb)"
column used format 999,999 heading "Used|(Mb)"
column pused format 990.9 heading "%|Used|Space"
column fragmax format 99,999.9 heading "Largest|Free|Ext.(Mb)"
column nbfrag format 99999 heading "Nb|frag"
column contents format a10 heading "Content"
column pct_ext_coal format 999 heading "% Ext.|Coal."
column ext_manage format a7 wrap heading "Ext.|M."
column autoext format a7 wrap heading "Auto|Ext."


select
contents
, nvl (dt.tablespace_name, nvl (fsp.tablespace_name, 'Unknown')) tblsp
, alloc
, alloc - nvl (free, 0) Used
, nvl (free, 0) Free
, ((alloc - nvl (free, 0)) / alloc) * 100 pused
, nbfrag
, fragmax
, dfsc.pct_ext_coal pct_ext_coal
, dt.ext_manage
, df.inc autoext
from
( select sum (bytes)/1048576 free
, max (bytes)/1048576 fragmax
, tablespace_name
, count(*) nbfrag
from sys.dba_free_space
group by tablespace_name
) fsp
, ( select sum(bytes)/1048576 alloc
, tablespace_name
, Decode(((inc * &Var_DB_BLOCK_SIZE)/1024), Null, 'No', 'Yes') inc
from sys.dba_data_files sddf
, sys.filext$ aut
where sddf.file_id = aut.file# (+)
group by tablespace_name
, Decode(((inc * &Var_DB_BLOCK_SIZE)/1024), Null, 'No', 'Yes')
Union
select sum(bytes)/1048576 alloc
, tablespace_name
, Decode(((increment_by * &Var_DB_BLOCK_SIZE)/1024), Null, 'No', 'Yes') inc
from sys.dba_temp_files sddf
group by tablespace_name
, Decode(((increment_by * &Var_DB_BLOCK_SIZE)/1024), Null, 'No', 'Yes')
) df
, ( select contents
, tablespace_name
, initial_extent/1024 initial_ext
, next_extent/1024 next_ext
, pct_increase
, max_extents
, min_extents
, Substr(extent_management,1,5) ext_manage
from dba_tablespaces
) dt
, ( select percent_extents_coalesced pct_ext_coal
, tablespace_name
from dba_free_space_coalesced
) dfsc
where fsp.tablespace_name (+) = dt.tablespace_name
and df.tablespace_name (+) = dt.tablespace_name
and dfsc.tablespace_name (+) = dt.tablespace_name
order by contents , pused desc
;


Prompt

PROMPT
PROMPT ******************************************** INVALID OBJECTS BY TYPE

BREAK ON REPORT
COMPUTE SUM LABEL TOTAL of quantity ON REPORT

SELECT object_type, COUNT(*) quantity
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY object_type;

CLEAR BREAKS
CLEAR COMPUTES

PROMPT
PROMPT ******************************************** INVALID OBJECTS BY TYPE AND OWNER

SELECT owner, object_type, COUNT(*) quantity
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type;

PROMPT
PROMPT ******************************************** MODIFIED OBJECTS IN THE LAST 7 DAYS

col objet format a40
SELECT owner||'.'||object_name objet, object_type, created, last_ddl_time modified, ROUND(sysdate - last_ddl_time,2) days
FROM dba_objects
WHERE sysdate - last_ddl_time < 7 AND
subobject_name IS NULL
and object_name not like 'WRH$%'
ORDER BY created DESC;

PROMPT
PROMPT ******************************************** UNUSABLE INDEXES

SELECT owner||'.'||index_name indice FROM dba_indexes
WHERE status = 'UNUSABLE'
ORDER BY 1;

col partition format a45
SELECT index_owner||'.'||index_name||'.'||partition_name partition
FROM dba_ind_partitions
WHERE status = 'UNUSABLE'
ORDER BY 1;

PROMPT
PROMPT ******************************************** DISABLED TRIGGERS

col owntab format a45
col triggering_event format a20
SELECT owner||'.'||trigger_name "TRIGGER", triggering_event, table_owner||'.'||table_name owntab , status
FROM dba_triggers
WHERE status = 'DISABLED';

PROMPT
PROMPT ******************************************** DISABLED CONSTRAINTS

col owntab format a45
SELECT constraint_name constraint, constraint_type, owner||'.'||table_name owntab , status
FROM dba_constraints
WHERE status = 'DISABLED';

-- PROMPT
-- PROMPT ******************************************** ANALYZED TABLES

-- SELECT owner, NVL(TO_CHAR(TRUNC(last_analyzed),'dd-mm-yyyy'), 'Never Analyzed') last_analyzed, COUNT(*) quantity
-- FROM dba_tables
-- GROUP BY owner, TRUNC(last_analyzed)
-- ORDER BY 1,2;

-- PROMPT
-- PROMPT ******************************************** ANALYZED INDEXES

-- SELECT owner, NVL(TO_CHAR(TRUNC(last_analyzed),'dd-mm-yyyy'), 'Never Analyzed') last_analyzed, COUNT(*) quantity
-- FROM dba_indexes
-- GROUP BY owner, TRUNC(last_analyzed)
-- ORDER BY 1,2;

PROMPT
PROMPT ******************************************** TABLESPACES WITH LESS THAN 15% FREE

SELECT a.tblspc tablespace, a.fbytes TOTAL, NVL(u.ebytes,0) RESERVED, a.fbytes-NVL(u.ebytes,0) FREE, 100*NVL(u.ebytes,0)/a.fbytes "% RESERVED"
FROM (SELECT tablespace_name tblspc, SUM(bytes)/1024/1024 ebytes FROM dba_segments GROUP BY tablespace_name) u,
(SELECT tablespace_name tblspc, SUM(bytes)/1024/1024 fbytes FROM dba_data_files GROUP BY tablespace_name) a
WHERE u.tblspc(+) = a.tblspc AND
100*NVL(u.ebytes,0)/a.fbytes > 85.00
ORDER BY tablespace;

PROMPT
PROMPT ******************************************** OBJECTS CLOSE TO MAX EXTENTS

SELECT tablespace_name tablespace, segment_type tipo, owner||'.'||segment_name segment, extents, bytes/1024/1024 mb,
max_extents
FROM dba_segments
WHERE max_extents-extents < 10 AND
segment_type <> 'CACHE'
ORDER BY 1,2,3,4;

PROMPT
PROMPT ******************************************** OBJECTS THAT CAN.T EXTENT

prompt Objects that cannot extend (no space in TS)

column Sname form a40 heading 'Object Name'
column Stype form a15 heading 'Type'
column Size form 9,999 heading 'Size'
column Next form 99,999 heading 'Next'
column Tname form a15 heading 'TsName'

select a.owner||'.'||a.segment_name "Sname",
a.segment_type "Stype",
a.bytes/1024/1024 "Size",
a.next_extent/1024/1024 "Next",
a.tablespace_name "TName"
from sys.dba_segments a
where a.tablespace_name not like 'T%MP%' -- Exclude TEMP tablespaces
and next_extent * 2 > ( -- Cannot extend 1x, can change to 2x...
select max(b.bytes)
from dba_free_space b
where a.tablespace_name = b.tablespace_name)
order by 3 desc
/

PROMPT
PROMPT ******************************************** OBJECTS IN THE SYSTEM TABLESPACE

select owner, substr(segment_name,1,25) segment , substr(partition_name,1,25) partition, segment_type
from sys.dba_segments
where owner not in ('PUBLIC', 'SYS', 'SYSTEM')
and tablespace_name = 'SYSTEM';




PROMPT
PROMPT ******************************************** DATAGUARD STATE (CRITICAL MESSAGES)

SELECT inst_id, facility, severity, dest_id, error_code, callout, timestamp, message
FROM gv$dataguard_status
WHERE severity IN ('Error','Fatal','Warning')
ORDER BY timestamp;

--PROMPT
--PROMPT ******************************************** NO APLIED ARCHIVELOGS

--selecT sequence#,substr(a.name,1,46) archivelog, a.blocks, a.archived, a.applied, a.status, a.end_of_redo eor, a.standby_dest, a.first_time, a.completion_time completion, SYSDATE time
--From v$archived_log a
--WHERE a.sequence# between (select max(b.sequence#)
--from v$archived_log b where b.applied = 'YES') and (select max(c.sequence#) from v$archived_log c)
--/

PROMPT
PROMPT ******************************************** GAP SEQUENCE

SELECT * FROM v$archive_gap;

PROMPT
PROMPT ******************************************** DIFERENCE ON RECEPTION

SELECT thread#, MAX(next_time) next_time, SYSDATE time, ROUND((SYSDATE - MAX(next_time))*24,2) horas, ROUND((SYSDATE - MAX(next_time))*24*60,0) minutos
FROM v$archived_log
GROUP BY thread#;

PROMPT
PROMPT ******************************************** DIFERENCE TO APPLY

SELECT thread#, MAX(next_time) next_time, SYSDATE time, ROUND((SYSDATE - MAX(next_time))*24,2) horas, ROUND((SYSDATE - MAX(next_time))*24*60,0) minutos
FROM v$archived_log
WHERE applied='YES'
GROUP BY thread#;

PROMPT
PROMPT ******************************************** PROCESSES

col "group#" format a10
SELECT inst_id, process, pid, status, substr(to_char(group#),1,8) group#, thread#,
sequence#, block#, blocks, delay_mins, known_agents, active_agents, client_process
FROM gv$managed_standby;

PROMPT



set heading on
set echo off
set linesize 150
set pagesize 500
column day format a16 heading 'Day'
column d_0 format a3 heading '00'
column d_1 format a3 heading '01'
column d_2 format a3 heading '02'
column d_3 format a3 heading '03'
column d_4 format a3 heading '04'
column d_5 format a3 heading '05'
column d_6 format a3 heading '06'
column d_7 format a3 heading '07'
column d_8 format a3 heading '08'
column d_9 format a3 heading '09'
column d_10 format a3 heading '10'
column d_11 format a3 heading '11'
column d_12 format a3 heading '12'
column d_13 format a3 heading '13'
column d_14 format a3 heading '14'
column d_15 format a3 heading '15'
column d_16 format a3 heading '16'
column d_17 format a3 heading '17'
column d_18 format a3 heading '18'
column d_19 format a3 heading '19'
column d_20 format a3 heading '20'
column d_21 format a3 heading '21'
column d_22 format a3 heading '22'
column d_23 format a3 heading '23'
column Total format 9999
column status format a8
column member format a40
column archived heading 'Archived' format a8
column bytes heading 'Bytes|(MB)' format 9999
col group# format 999
Ttitle 'Log Info' skip 2
select l.group#,f.member,l.archived,l.bytes/1078576 bytes,l.status,f.type
from v$log l, v$logfile f
where l.group# = f.group#
/
Ttitle off
prompt =========================================================================================================================
Ttitle 'Log Switch on hour basis' skip 2

select to_char(FIRST_TIME,'DY, DD-MON-YYYY') day,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'00',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'00',1,0))) d_0,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'01',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'01',1,0))) d_1,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'02',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'02',1,0))) d_2,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'03',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'03',1,0))) d_3,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'04',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'04',1,0))) d_4,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'05',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'05',1,0))) d_5,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'06',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'06',1,0))) d_6,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'07',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'07',1,0))) d_7,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'08',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'08',1,0))) d_5,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'09',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'09',1,0))) d_9,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'10',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'10',1,0))) d_10,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'11',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'11',1,0))) d_11,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'12',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'12',1,0))) d_12,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'13',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'13',1,0))) d_13,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'14',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'14',1,0))) d_14,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'15',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'15',1,0))) d_15,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'16',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'16',1,0))) d_16,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'17',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'17',1,0))) d_17,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'18',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'18',1,0))) d_18,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'19',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'19',1,0))) d_19,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'20',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'20',1,0))) d_20,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'21',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'21',1,0))) d_21,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'22',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'22',1,0))) d_22,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'23',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'23',1,0))) d_23,
count(trunc(FIRST_TIME)) Total
from v$log_history
group by to_char(FIRST_TIME,'DY, DD-MON-YYYY')
order by to_date(substr(to_char(FIRST_TIME,'DY, DD-MON-YYYY'),5,15) )
/
Ttitle off
prompt
spool off