snap info !!!!!....

https://anuj-singh.blogspot.com/2011/09/oracle-awr-matrix-report.html




SET HEADING off PAGESIZE 0 linesize 200
COLUMN sort_ord NOPRINT
SELECT DISTINCT 001 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, 'Sunday' sunday_snapid
, 'Monday' monday_snapid
, 'Tuesday' tuesday_snapid
, 'Wednesday' wednesday_snapid
, 'Thursday' thursday_snapid
, 'Friday' friday_snapid
, 'Saturday' saturday_snapid
FROM sys.wrm$_snapshot
UNION ALL
SELECT 010 sort_ord
, TO_CHAR(s.first_sunday, 'YYYYMMDD') snap_week
, NULL hour_of_day
, TO_CHAR(s.first_sunday, 'MM/DD/YY') sunday_snapid
, TO_CHAR(s.first_sunday+1, 'MM/DD/YY') monday_snapid
, TO_CHAR(s.first_sunday+2, 'MM/DD/YY') tuesday_snapid
, TO_CHAR(s.first_sunday+3, 'MM/DD/YY') wednesday_snapid
, TO_CHAR(s.first_sunday+4, 'MM/DD/YY') thursday_snapid
, TO_CHAR(s.first_sunday+5, 'MM/DD/YY') friday_snapid
, TO_CHAR(s.first_sunday+6, 'MM/DD/YY') saturday_snapid
FROM ( SELECT NEXT_DAY(MIN(end_interval_time) - 7, 'SUNDAY') first_sunday
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
) s
UNION ALL
SELECT 011 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) sunday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),2,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) monday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),3,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) tuesday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),4,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) wednesday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),5,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) thursday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),6,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) friday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),7,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) saturday_of_week
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
GROUP BY TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD')
HAVING MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) IS NOT NULL
UNION ALL
SELECT DISTINCT 020 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, '---------' hour_of_day
, '---------' sunday_snapid
, '---------' monday_snapid
, '---------' tuesday_snapid
, '---------' wednesday_snapid
, '---------' thursday_snapid
, '---------' friday_snapid
, '---------' saturday_snapid
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
UNION ALL
SELECT 030 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, TO_CHAR(end_interval_time, 'hh24')||':00' hour_of_day
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),1,snap_id,NULL)),'999999') sunday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),2,snap_id,NULL)),'999999') monday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),3,snap_id,NULL)),'999999') tuesday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),4,snap_id,NULL)),'999999') wednesday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),5,snap_id,NULL)),'999999') thursday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),6,snap_id,NULL)),'999999') friday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),7,snap_id,NULL)),'999999') saturday_of_week
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE')
GROUP BY TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD'), TO_CHAR(end_interval_time, 'hh24')||':00'
UNION ALL
SELECT DISTINCT 999 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, NULL sunday_snapid
, NULL monday_snapid
, NULL tuesday_snapid
, NULL wednesday_snapid
, NULL thursday_snapid
, NULL friday_snapid
, NULL saturday_snapid
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
ORDER BY snap_week, sort_ord , hour_of_day
/

20211003           Sunday    Monday    Tuesday   Wednesday Thursday  Friday    Saturday
20211003           10/03/21  10/04/21  10/05/21  10/06/21  10/07/21  10/08/21  10/09/21
20211003 --------- --------- --------- --------- --------- --------- --------- ---------
20211003 00:00                                     36504     36528     36552     36576
20211003 01:00                                     36505     36529     36553     36577
20211003 02:00                                     36506     36530     36554     36578

=====

define p_inst=1
define p_days=1

set linesize 200
set pages 200
set verify off
column event_name format a40

column dt heading 'Date/Hour' format a11
set linesize 500
set pages 9999	 
select * from (
select min(snap_id) as snap_id,  
		     to_char(start_time,'MM/DD/YY') as dt, to_char(start_time,'HH24') as hr
	from (
	select snap_id, s.instance_number, begin_interval_time start_time, 
		   end_interval_time end_time, snap_level, flush_elapsed,
		   lag(s.startup_time) over (partition by s.dbid, s.instance_number 
		   					   order by s.snap_id) prev_startup_time,
		   s.startup_time
	from  dba_hist_snapshot s, gv$instance i
	where begin_interval_time between trunc(sysdate)-&p_days and sysdate 
	and   s.instance_number = i.instance_number
	and   s.instance_number = &p_inst
	order by snap_id
	)
	group by to_char(start_time,'MM/DD/YY') , to_char(start_time,'HH24') 
	order by snap_id, start_time )
	pivot
	(sum(snap_id)
	 for hr in ('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23')
	 )
	 order by dt;
	 

Date/Hour             '00'           '01'           '02'           '03'           '04'           '05'           '06'           '07'           '08'           '09'           '10'           '11'           '12'           '13'           '14'           '15'           '16'           '17'           '18'           '19'           '20'           '21'           '22'           '23'
----------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- --------------
03/22/22             40513          40514          40515          40516          40517          40518          40519          40520          40521          40522          40523          40524          40525          40526          40527          40528          40529          40530          40531          40532          40533          40534          40535          40536
03/23/22             40537          40538          40539          40540          40541          40542          40543

--os stats !!!!

col end_snap_time format a30
col load        format 990.00           heading "OS|Load"
col num_cpus    format 90               heading "CPU"
col mem         format 999990.00        heading "Memory|(GB)"
col oscpupct    format 990              heading "OS|CPU%"
col oscpuusr    format 990              heading "USR%"
col oscpusys    format 990              heading "SYS%"
col oscpuio     format 990              heading "IO%"



set ver off pages 50000 lines 140 tab off  linesize 300  pages 9999

 define days_history=1                     
define inst=1
BREAK ON instance_number SKIP 1


WITH
  base_line AS
  (
		SELECT
		*
			FROM
				(
				SELECT
				  snp.instance_number,
				  snp.end_interval_time ,
				  sst.snap_id,
				  sst.stat_name,
				  sst.value
				FROM
				  dba_hist_snapshot snp,
				  dba_hist_osstat sst
				WHERE
				  sst.instance_number = snp.instance_number
				AND sst.snap_id       = snp.snap_id
				AND snp.instance_number = decode(&inst,0,snp.instance_number,&inst)
				AND snp.begin_interval_time >= TRUNC(sysdate)- &days_history
			   )
		  pivot (SUM(value) FOR (stat_name) IN (
		  'LOAD'									   AS LOAD,
		  'NUM_CPUS'								   AS NUM_CPUS,
		  'PHYSICAL_MEMORY_BYTES'                      AS PHYSICAL_MEMORY_BYTES, 
		  'BUSY_TIME'           					   AS BUSY_TIME,
		  'USER_TIME'                                  AS USER_TIME,
		  'SYS_TIME'                                   AS SYS_TIME,
		  'IOWAIT_TIME'                                AS IOWAIT_TIME))
  )
SELECT
    b2.instance_number,
	to_char(b2.end_interval_time,'MM/DD/YY HH24:MI:SS') end_snap_time,
	b2.NUM_CPUS,
	round(b2.LOAD,1) LOAD,
	round(b2.PHYSICAL_MEMORY_BYTES/1024/1024/1024,0) mem,
	(((b2.busy_time - b1.busy_time)/100) / ((round(EXTRACT(DAY FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 1440 
                                                                                              + EXTRACT(HOUR FROM b2.END_INTERVAL_TIME   - b1.END_INTERVAL_TIME) * 60 
                                                                                              + EXTRACT(MINUTE FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) 
                                                                                              + EXTRACT(SECOND FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) / 60, 2)*60)*b2.NUM_CPUS))*100 as oscpupct,
	(((b2.user_time - b1.user_time)/100) / ((round(EXTRACT(DAY FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 1440 
                                                                                              + EXTRACT(HOUR FROM b2.END_INTERVAL_TIME   - b1.END_INTERVAL_TIME) * 60 
                                                                                              + EXTRACT(MINUTE FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) 
                                                                                              + EXTRACT(SECOND FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) / 60, 2)*60)*b2.NUM_CPUS))*100 as  oscpuusr,
    (((b2.sys_time - b1.sys_time)/100) / ((round(EXTRACT(DAY FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 1440 
                                                                                              + EXTRACT(HOUR FROM b2.END_INTERVAL_TIME   - b1.END_INTERVAL_TIME) * 60 
                                                                                              + EXTRACT(MINUTE FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) 
                                                                                              + EXTRACT(SECOND FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) / 60, 2)*60)*b2.NUM_CPUS))*100 as  oscpusys,
    (((b2.iowait_time - b1.iowait_time)/100) / ((round(EXTRACT(DAY FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 1440 
                                                                                              + EXTRACT(HOUR FROM b2.END_INTERVAL_TIME   - b1.END_INTERVAL_TIME) * 60 
                                                                                              + EXTRACT(MINUTE FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) 
                                                                                              + EXTRACT(SECOND FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) / 60, 2)*60)*b2.NUM_CPUS))*100 as  oscpuio
FROM
  base_line b1,
  base_line b2
WHERE
     b1.instance_number 	= b2.instance_number
AND  b1.snap_id + 1         = b2.snap_id
ORDER BY 
  1,2   ;


undef inst
undef fileno
undef days_history
undef interval_minutes



                                                       OS     Memory   OS
INSTANCE_NUMBER END_SNAP_TIME                  CPU    Load       (GB) CPU% USR% SYS%  IO%
--------------- ------------------------------ --- ------- ---------- ---- ---- ---- ----
              1 03/22/22 02:00:13               16    1.00      71.00    6    4    2    4
                03/22/22 03:00:08               16    1.20      71.00    6    4    2    3
                03/22/22 04:00:16               16    0.70      71.00    6    4    2    3

 Tablespace info .. ....
http://anuj-singh.blogspot.com/2010/05/oracle-tablespace-cron-job.html

https://anuj-singh.blogspot.com/search?q=fs.tablespace_name+tablespace%2C+num_files%2Cnum_files

Oracle Tablespace space report
http://anuj-singh.blogspot.com/2011/10/oracle-space-used-in-tablespace.html

https://anuj-singh.blogspot.com/2025/08/oracle-datafile-info.html

or 

set head off verify off echo off pages 1500 linesize 110 feedback off
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select
'TABLESPACE_NAME......................................: '||TABLESPACE_NAME ,         
'BLOCK_SIZE...........................................: '||BLOCK_SIZE    ,           
'INITIAL_EXTENT.......................................: '||INITIAL_EXTENT  ,         
'NEXT_EXTENT..........................................: '||NEXT_EXTENT ,             
'MIN_EXTENTS..........................................: '||MIN_EXTENTS  ,            
'MAX_EXTENTS..........................................: '||MAX_EXTENTS ,             
'MAX_SIZE.............................................: '||MAX_SIZE  ,               
'PCT_INCREASE.........................................: '||PCT_INCREASE ,            
'MIN_EXTLEN...........................................: '||MIN_EXTLEN ,              
'STATUS...............................................: '||STATUS  ,                 
'CONTENTS.............................................: '||CONTENTS  ,              
'LOGGING..............................................: '||LOGGING ,                 
'FORCE_LOGGING........................................: '||FORCE_LOGGING  ,          
'EXTENT_MANAGEMENT....................................: '||EXTENT_MANAGEMENT ,       
'ALLOCATION_TYPE......................................: '||ALLOCATION_TYPE ,         
'PLUGGED_IN...........................................: '||PLUGGED_IN   ,            
'SEGMENT_SPACE_MANAGEMENT ..........................: '||SEGMENT_SPACE_MANAGEMENT ,
'DEF_TAB_COMPRESSION..................................: '||DEF_TAB_COMPRESSION   ,   
'RETENTION............................................: '||RETENTION     ,           
'BIGFILE..............................................: '||BIGFILE     ,             
'PREDICATE_EVALUATION.................................: '||PREDICATE_EVALUATION,     
'ENCRYPTED............................................: '||ENCRYPTED  ,              
'COMPRESS_FOR ........................................: '||COMPRESS_FOR   ,          
'DEF_INMEMORY.........................................: '||DEF_INMEMORY   ,          
'DEF_INMEMORY_PRIORITY................................: '||DEF_INMEMORY_PRIORITY   , 
'DEF_INMEMORY_DISTRIBUTE.............................: '||DEF_INMEMORY_DISTRIBUTE  ,
'DEF_INMEMORY_COMPRESSION.............................: '||DEF_INMEMORY_COMPRESSION ,
'DEF_INMEMORY_DUPLICATE...............................: '||DEF_INMEMORY_DUPLICATE   ,
'SHARED...............................................: '||SHARED         ,          
'DEF_INDEX_COMPRESSION ...............................: '||DEF_INDEX_COMPRESSION   , 
'INDEX_COMPRESS_FOR...................................: '||INDEX_COMPRESS_FOR ,      
'DEF_CELLMEMORY ......................................: '||DEF_CELLMEMORY   ,       
'DEF_INMEMORY_SERVICE.................................: '||DEF_INMEMORY_SERVICE   ,  
'DEF_INMEMORY_SERVICE_NAME............................: '||DEF_INMEMORY_SERVICE_NAME,
'LOST_WRITE_PROTECT...................................: '||LOST_WRITE_PROTECT  ,     
'CHUNK_TABLESPACE ....................................: '||CHUNK_TABLESPACE   
from  dba_tablespaces
where 1=1
-- and TABLESPACE_NAME='XXX'
;  

SET LINESIZE 300 PAGESIZE 200
CLEAR COLUMNS

-- Format Columns
COL TABLESPACE_NAME FOR A28          HEADING 'Tablespace Name'
COL PERUSD          FOR 999.0        HEADING 'Used % (Max)'
COL PERC            FOR 999.0        HEADING 'Used % (Cur)'
COL TOTALM          FOR 999,999,999  HEADING 'Max (MB)'
COL TOTAL           FOR 999,999,999  HEADING 'Alloc (MB)'
COL USED            FOR 999,999,999  HEADING 'Used (MB)'
COL FREEM           FOR 999,999,999  HEADING 'Max Free (MB)'
COL FREE            FOR 999,999,999  HEADING 'Alloc Free (MB)'
COL FREEM_GB        FOR 999,999.0    HEADING 'Max Free (GB)'
COL FREE_GB         FOR 999,999.0    HEADING 'Alloc Free (GB)'
COL DBF_NO          FOR 999          HEADING 'Files'
COL WARN            FOR 999          HEADING 'Warn'
COL CRIT            FOR 999          HEADING 'Crit'
COL "OK?"           FOR A4           HEADING 'OK?'

SELECT 
    m.tablespace_name,
    round(max(m.used_percent), 1)                                                                               PERUSD,
    round(max(m.used_space * t.block_size) * 100 / (sum(d.bytes) * count(distinct d.file_id) / count(d.file_id)), 1) PERC,
    round(max(m.tablespace_size * t.block_size / 1024 / 1024), 1)                                              TOTALM,
    round((sum(d.bytes) * count(distinct d.file_id)) / count(d.file_id) / 1024 / 1024, 1)                       TOTAL,
    round(max(m.used_space * t.block_size / 1024 / 1024), 1)                                                   USED,
    round(max((m.tablespace_size - m.used_space) * t.block_size / 1024 / 1024), 1)                             FREEM,
    round(max((m.tablespace_size - m.used_space) * t.block_size / 1024 / 1024 / 1024), 2)                      FREEM_GB,
    round(((sum(d.bytes) * count(distinct d.file_id)) / count(d.file_id) - max(m.used_space * t.block_size)) / 1024 / 1024, 1) FREE,
    round(((sum(d.bytes) * count(distinct d.file_id)) / count(d.file_id) - max(m.used_space * t.block_size)) / 1024 / 1024 / 1024, 2) FREE_GB,
    count(distinct d.file_id)                                                                                   DBF_NO,
    max(to_number(tt.warning_value))                                                                            WARN,
    max(to_number(tt.critical_value))                                                                           CRIT,
    max(case when m.used_percent > tt.warning_value OR m.used_percent > tt.critical_value then 'NO!' else 'OK' end) "OK?"
FROM dba_tablespace_usage_metrics m, dba_tablespaces t, dba_data_files d, dba_thresholds tt
WHERE m.tablespace_name = t.tablespace_name
  AND d.tablespace_name = t.tablespace_name
  AND tt.metrics_name   = 'Tablespace Space Usage'
  AND tt.object_name IS NULL
-- AND d.tablespace_name LIKE 'DATA%'  <<<<<
GROUP BY m.tablespace_name
ORDER BY 2 DESC;





SET LINESIZE 300 PAGESIZE 200

CLEAR COLUMNS

-- Format Columns
COL CON_ID          FOR 999          HEADING 'Con ID'
COL CONTAINER_NAME  FOR A15          HEADING 'Container Name'
COL TABLESPACE_NAME FOR A28          HEADING 'Tablespace Name'
COL PERUSD          FOR 999.0        HEADING 'Used % (Max)'
COL PERC            FOR 999.0        HEADING 'Used % (Cur)'
COL TOTALM          FOR 999,999,999  HEADING 'Max (MB)'
COL TOTAL           FOR 999,999,999  HEADING 'Alloc (MB)'
COL USED            FOR 999,999,999  HEADING 'Used (MB)'
COL FREEM           FOR 999,999,999  HEADING 'Max Free (MB)'
COL FREE            FOR 999,999,999  HEADING 'Alloc Free (MB)'
COL FREEM_GB        FOR 999,999.0    HEADING 'Max Free (GB)'
COL FREE_GB         FOR 999,999.0    HEADING 'Alloc Free (GB)'
COL DBF_NO          FOR 999          HEADING 'Files'
COL WARN            FOR 999          HEADING 'Warn'
COL CRIT            FOR 999          HEADING 'Crit'
COL "OK?"           FOR A4           HEADING 'OK?'

SELECT 
    m.con_id,
    c.name AS container_name,
    m.tablespace_name,
    round(max(m.used_percent), 1)                                                                               PERUSD,
    round(max(m.used_space * t.block_size) * 100 / (sum(d.bytes) * count(distinct d.file_id) / count(d.file_id)), 1) PERC,
    round(max(m.tablespace_size * t.block_size / 1024 / 1024), 1)                                              TOTALM,
    round((sum(d.bytes) * count(distinct d.file_id)) / count(d.file_id) / 1024 / 1024, 1)                       TOTAL,
    round(max(m.used_space * t.block_size / 1024 / 1024), 1)                                                   USED,
    round(max((m.tablespace_size - m.used_space) * t.block_size / 1024 / 1024), 1)                             FREEM,
    round(max((m.tablespace_size - m.used_space) * t.block_size / 1024 / 1024 / 1024), 2)                      FREEM_GB,
    round(((sum(d.bytes) * count(distinct d.file_id)) / count(d.file_id) - max(m.used_space * t.block_size)) / 1024 / 1024, 1) FREE,
    round(((sum(d.bytes) * count(distinct d.file_id)) / count(d.file_id) - max(m.used_space * t.block_size)) / 1024 / 1024 / 1024, 2) FREE_GB,
    count(distinct d.file_id)                                                                                   DBF_NO,
    max(to_number(tt.warning_value))                                                                            WARN,
    max(to_number(tt.critical_value))                                                                           CRIT,
    max(case when m.used_percent > tt.warning_value OR m.used_percent > tt.critical_value then 'NO!' else 'OK' end) "OK?"
FROM cdb_tablespace_usage_metrics m, cdb_tablespaces t, cdb_data_files d, cdb_thresholds tt, v$containers c
WHERE m.tablespace_name = t.tablespace_name
  AND m.con_id          = t.con_id
  AND d.tablespace_name = t.tablespace_name
  AND d.con_id          = t.con_id
  AND m.con_id          = c.con_id
  AND tt.con_id (+      = m.con_id
  AND tt.metrics_name (+)= 'Tablespace'   ---<<<<
  AND tt.object_name IS NULL
GROUP BY m.con_id, c.name, m.tablespace_name
ORDER BY m.con_id, PERUSD DESC;




set linesize 300 pagesize 200
col TABLESPACE_NAME   for a25
col PERUSD            for 999999999
SELECT m.tablespace_name,
    round(max(m.used_percent),1)                                                                                  PERUSD,
    round(max(m.used_space*t.block_size)*100/(sum(d.bytes)*count(distinct d.file_id)/count(d.file_id)),1)         PERC,
    round(max(m.tablespace_size*t.block_size/1024/1024),1)                                                        TOTALM,
    round((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)/1024/1024,1)                                  TOTAL,
    round(max(m.used_space*t.block_size/1024/1024),1)                                                             USED,
    round(max((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1)                                         FREEM,
    round(((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)-max(m.used_space*t.block_size))/1024/1024,1) FREE,    
    count(distinct d.file_id)                                                                                     DBF_NO,
    max(to_number(tt.warning_value))                                                                              WARN,
    max(to_number(tt.critical_value))                                                                             CRIT,
    max(case when m.used_percent>tt.warning_value OR m.used_percent>tt.critical_value then 'NO!' else 'OK' end) "OK?"
FROM  dba_tablespace_usage_metrics m, dba_tablespaces t, dba_data_files d, dba_thresholds tt
WHERE m.tablespace_name =t.tablespace_name
AND d.tablespace_name   =t.tablespace_name
and tt.metrics_name     ='Tablespace Space Usage'
and tt.object_name is null
-- and d.tablespace_name   ='DATA'
GROUP BY m.tablespace_name
order by 2 desc;




--- CDB 

set linesize 300 pagesize 200
col TABLESPACE_NAME   for a25
col PERUSD            for 999999999
SELECT m.tablespace_name,
    round(max(m.used_percent),1)                                                                                  PERUSD,
    round(max(m.used_space*t.block_size)*100/(sum(d.bytes)*count(distinct d.file_id)/count(d.file_id)),1)         PERC,
    round(max(m.tablespace_size*t.block_size/1024/1024),1)                                                        TOTALM,
    round((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)/1024/1024,1)                                  TOTAL,
    round(max(m.used_space*t.block_size/1024/1024),1)                                                             USED,
    round(max((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1)                                         FREEM,
    round(((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)-max(m.used_space*t.block_size))/1024/1024,1) FREE,    
    count(distinct d.file_id)                                                                                     DBF_NO,
    max(to_number(tt.warning_value))                                                                              WARN,
    max(to_number(tt.critical_value))                                                                             CRIT,
    max(case when m.used_percent>tt.warning_value OR m.used_percent>tt.critical_value then 'NO!' else 'OK' end) "OK?"
FROM  dba_tablespace_usage_metrics m, dba_tablespaces t, dba_data_files d, dba_thresholds tt
WHERE m.tablespace_name =t.tablespace_name
AND d.tablespace_name   =t.tablespace_name
and tt.metrics_name     ='Tablespace Space Usage'
and tt.object_name is null
-- and d.tablespace_name   ='DATA'
GROUP BY m.tablespace_name
order by 2 desc;

Oracle tablespace Report

http://anuj-singh.blogspot.com/2011/10/oracle-space-used-in-tablespace.html




set linesize 300
COLUMN tablespace_name FORMAT a115

SELECT con_id , LOWER(LISTAGG(tablespace_name, ', ') WITHIN GROUP (ORDER BY tablespace_name)) AS tablespace_name
  FROM cdb_tablespaces
  group by con_id;










           

break on resized
  with ts_history as (
  select * from (
  select v.name
 , v.ts#
 , s.instance_number
 , h.tablespace_size
  * p.value/1024/1024 ts_mb
 , h.tablespace_maxsize
  * p.value/1024/1024 max_mb
 , h.tablespace_usedsize
  * p.value/1024/1024 used_mb
 , to_date(h.rtime, 'MM/DD/YYYY HH24:MI:SS') resize_time
 , lag(h.tablespace_usedsize * p.value/1024/1024, 1, h.tablespace_usedsize * p.value/1024/1024)
  over (partition by v.ts# order by h.snap_id) last
 , (h.tablespace_usedsize * p.value/1024/1024)
  - lag(h.tablespace_usedsize * p.value/1024/1024, 1, h.tablespace_usedsize * p.value/1024/1024)
  over (partition by v.ts# order by h.snap_id) incr
  from dba_hist_tbspc_space_usage h
 , dba_hist_snapshot s
 , v$tablespace v
 , dba_tablespaces t
 , v$parameter p
  where h.tablespace_id = v.ts#
  and v.name = t.tablespace_name
  and t.contents not in ('UNDO', 'TEMPORARY')
  and p.name = 'db_block_size'
  and h.snap_id = s.snap_id
 order by v.name, h.snap_id asc)
  where incr > 0)
  select to_char(resize_time, 'YYYY-MM') as resized
 , name
 , sum(incr) incr
  from ts_history
 group by name
 , to_char(resize_time, 'YYYY-MM')
 order by 1, 3 desc;
 


set serverout on
set verify off
set lines 200
set pages 2000
DECLARE
v_ts_id number;
not_in_awr EXCEPTION;
v_ts_name varchar2(200) := UPPER('DATA_2022');
v_ts_block_size number;
v_begin_snap_id number;
v_end_snap_id number;
v_begin_snap_date date;
v_end_snap_date date;
v_numdays number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_count number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
BEGIN
SELECT ts# into v_ts_id FROM v$tablespace where name = v_ts_name;
SELECT count(*) INTO v_count FROM dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
IF v_count = 0 THEN
RAISE not_in_awr;
END IF ;
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_ts_name;
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
v_numdays := v_end_snap_date - v_begin_snap_date;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_begin_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_end_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
v_ts_growth := v_ts_end_size - v_ts_begin_size;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('—————————');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||round(v_ts_end_size/v_ts_end_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space on '||v_begin_snap_date||': '||v_ts_begin_allocated_space||' MB'||' ('||round(v_ts_begin_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||v_ts_begin_size||' MB'||' ('||round(v_ts_begin_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('5) Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||': '||v_ts_growth||' MB'||' ('||round(v_ts_growth/1024,2)||' GB)');
IF (v_ts_growth <= 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! NO DATA GROWTH WAS FOUND FOR TABLESPCE '||V_TS_NAME||' !!!');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||round(v_ts_growth/v_numdays,2)||' MB'||' ('||round((v_ts_growth/v_numdays)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Expected Growth');
DBMS_OUTPUT.PUT_LINE('===============');
DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '|| round((v_ts_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '|| round((v_ts_growth/v_numdays)*60,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*60)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '|| round((v_ts_growth/v_numdays)*90,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*90)/1024,2)||' GB)');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! TABLESPACE DOES NOT EXIST !!!');
WHEN NOT_IN_AWR THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! TABLESPACE USAGE INFORMATION NOT FOUND IN AWR !!!');
END;
/



Summary
========
1) Allocated Space: 11138772.61 MB (10877.71 GB)
2) Used Space: 11130561.36 MB (10869.69 GB)
3) Used Space Percentage: 99.93 %


History
========
1) Allocated Space on 25-MAR-24: 9767973.16 MB (9539.04 GB)
2) Current Allocated Space on 27-JUN-24: 11138772.61 MB (10877.71 GB)
3) Used Space on 25-MAR-24: 9760848.09 MB (9532.08 GB)
4) Current Used Space on 27-JUN-24: 11130561.36 MB (10869.69 GB)
5) Total growth during last 94 days between 25-MAR-24 and 27-JUN-24: 1369713.27 MB (1337.61 GB)
6) Per day growth during last 94 days: 14571.42 MB (14.23 GB)


Expected Growth
===============
1) Expected growth for next 30 days: 437142.53 MB (426.9 GB)
2) Expected growth for next 60 days: 874285.07 MB (853.79 GB)
3) Expected growth for next 90 days: 1311427.6 MB (1280.69 GB)

PL/SQL procedure successfully completed.





set linesize 300 pagesize 300
col USED_PERCENT for 99.99
col TABLESPACE_NAME for a20
SELECT tbm.con_id,tbm.TABLESPACE_NAME,
   round(tbm.USED_SPACE * tb.BLOCK_SIZE /(1024*1024*1024),2) USED_SPACE_GB,
   round(tbm.TABLESPACE_SIZE * tb.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_SIZE_GB,
   round((tbm.TABLESPACE_SIZE - tbm.USED_SPACE) * tb.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_FREE_SIZE_GB,
   tbm.USED_PERCENT
FROM cdb_tablespace_usage_metrics tbm
     join cdb_tablespaces tb on tb.TABLESPACE_NAME = tbm.TABLESPACE_NAME 
and tb.con_id = tbm.con_id
order by 6 desc 
/


set linesize 300 pagesize 300
col USED_PERCENT for 99.99
col TABLESPACE_NAME for a25
col CONTAINER_NAME for a15

SELECT 
    tbm.con_id,
    c.name AS CONTAINER_NAME,
    tbm.TABLESPACE_NAME,
    round(tbm.USED_SPACE * tb.BLOCK_SIZE /(1024*1024*1024),2) USED_SPACE_GB,
    round(tbm.TABLESPACE_SIZE * tb.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_SIZE_GB,
    round((tbm.TABLESPACE_SIZE - tbm.USED_SPACE) * tb.BLOCK_SIZE /(1024*1024*1024),2) FREE_SIZE_GB,
    tbm.USED_PERCENT
FROM cdb_tablespace_usage_metrics tbm
JOIN cdb_tablespaces tb ON tb.tablespace_name = tbm.tablespace_name 
    AND tb.con_id = tbm.con_id
JOIN v$containers c ON tbm.con_id = c.con_id
ORDER BY tbm.USED_PERCENT DESC;
/








With colour !!!!

 cd $ORACLE_HOME/sqldeveloper/
 pwd
/u01/app/oracle/product/19.0.0/dbhome_1/sqldeveloper

 sql /nolog
 
 
SQL> connect / as sysdba
Connected.
SQL>


SQL> def
DEFINE _DATE =  "12-SEP-24" (CHAR)
DEFINE _CONNECT_IDENTIFIER =  "CDB$ROOT" (CHAR)
DEFINE _USER =  "SYS" (CHAR)
DEFINE _PRIVILEGE =  "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE =  "1901000000" (CHAR)
DEFINE _EDITOR =  "vi" (CHAR)
DEFINE _O_VERSION =  "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0" (CHAR)
DEFINE _O_RELEASE =  "2002000000" (CHAR)
DEFINE _PWD =  "/u01/app/oracle/product/19.0.0/dbhome_1/sqldeveloper/sqldeveloper/bin" (CHAR)

 

col TABLESPACE_SIZE for a12
col USED_SPACE for a12
col TABLESPACE_FREE_SIZE for a22
col tablespace_name for a20
col USED_PERCENT for 99
VARIABLE value NUMBER
SELECT  value into :value FROM   v$parameter WHERE   name = lower('DB_BLOCK_SIZE');

print value


VARIABLE value1 NUMBER
SELECT  to_number(value) into :value1 FROM   v$parameter WHERE   name = lower('DB_BLOCK_SIZE');
		
define value1=8192
set head on  pagesize 300 linesize 200 numf 999999.99
col tablespace_name for a28
col status          for a10
col pdb_name        for a15
col CON_ID for 9999
with ts_details as
(
select 
tb.con_id||' '||
nvl(pdb_name,'CDB$ROOT')||' '||nvl(pdb.status,'    ')||' '||rpad(tablespace_name,20, ' ')||' ' ||dbms_xplan.FORMAT_SIZE(tb.TABLESPACE_SIZE * &value1 )||' '||dbms_xplan.FORMAT_SIZE(tb.USED_SPACE * &value1 )||' ' ||dbms_xplan.FORMAT_SIZE((tb.TABLESPACE_SIZE - tb.USED_SPACE) * &value1 ) ts_line ,
case when ((used_percent) > 95.00) then '---(>95.00)% full ##'
                             else 'good' end  as "STATUS"
			 ,	trunc(used_percent) used_percent
from cdb_tablespace_usage_metrics tb,cdb_pdbs pdb
where 1=1  
and tb.con_id= pdb.con_id(+)
-- and pdb.con_id=3
-- and used_percent >1
--and tablespace_name like 'TEMP%'
--order by 1 desc 
 )
select
    case
        when used_percent > 70 then '@|bg_red '||ts_line||' '||STATUS||'|@'
        when used_percent < 1 then '@|bg_green '||ts_line||' '||STATUS||'|@'
        else '@|bg_yellow '||ts_line||' '||STATUS||'|@'
    end as ts_usage_percentage
from ts_details
;






https://anuj-singh.blogspot.com/search?q=fs.tablespace_name+tablespace%2C+num_files%2Cnum_files

Tablespace metadata !!!

http://anuj-singh.blogspot.com/2011/11/oracle-tablespace-metadata.html

http://anuj-singh.blogspot.com/   How to Check Tablespace Creation Time in Oracle

https://anuj-singh.blogspot.com/2012/03/asm-shell-script-add-file.html