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 .. ....

Oracle Tablespace space report
http://anuj-singh.blogspot.com/2011/10/oracle-space-used-in-tablespace.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
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.






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
;

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