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

Oracle Find archive from SCN number 

alter session set nls_date_format='DD-MON-RRRR HH24:MI:SS';
set pages 300 lines 300
col name for a90
col first_change# for 9999999999
col next_change# for 9999999999
select name, thread#, sequence#, first_time, next_time, first_change#, next_change#, decode (STATUS,'A','Available','D', 'Deleted','U','Unavailable','X' , 'Expired' ) arc_status from v$archived_log
where 1=1
--and &SCN_no between first_change# and next_change#
and first_time > sysdate -3
;


NAME                                                                                          THREAD#  SEQUENCE# FIRST_TIME           NEXT_TIME            FIRST_CHANGE# NEXT_CHANGE# ARC_STATUS
------------------------------------------------------------------------------------------ ---------- ---------- -------------------- -------------------- ------------- ------------ -----------
                                                                                                    1       1285 04-MAR-2021 18:00:53 04-MAR-2021 23:47:23    1598584462   1598757896 Deleted
                                                                                                    2       1654 04-MAR-2021 19:03:02 05-MAR-2021 00:09:54    1598599944   1598785468 Deleted
                                                                                                    1       1286 04-MAR-2021 23:47:23 05-MAR-2021 01:00:03    1598757896   1598833446 Deleted
                                                                                                    2       1655 05-MAR-2021 00:09:54 05-MAR-2021 01:00:04    1598785468   1598833469 Deleted
                                                                                                    1       1287 05-MAR-2021 01:00:03 05-MAR-2021 18:00:02    1598833446   1599094323 Deleted

col name for a90
set linesize 300 pagesize 300 
select thread#,sequence#, name ,creator
	-- , to_char(first_time,'DD-MON HH24:MI')
	, to_char(completion_time,'DD-MON HH24:MI') arc_completion,decode (STATUS,'A','Available','D', 'Deleted','U','Unavailable','X' , 'Expired' ) arc_status
    from v$archived_log
    where 1=1  
	-- and first_time > sysdate -3
	-- and CREATOR!='LGWR'
	--and SEQUENCE#>593460
	--and THREAD# =2
  order by 5
 -- FETCH FIRST 10 ROWS ONLY
  ;

 

 sql Bind variable ....

http://anuj-singh.blogspot.com/2021/02/sql-report.html

define sqlid='aca4xvmz0rzup'

set linesize 300 pagesize 300
col name 			for a10
col value_string 	for a50
col datatype_string for a50
col bind_value 		for a20 
col bind_name 		for a20
col sql_text 		for a50 wrap
col is_bind_sensitive for a15
col is_bind_aware     for a15
select
b.sql_id,
b.hash_value,
USERS_EXECUTING,
is_bind_sensitive,
is_bind_aware,
b.last_captured,
b.name bind_name,
b.value_string bind_value,
t.sql_text sql_text
from gv$sql  t , gv$sql_bind_capture b 
where 1=1
--and  b.value_string is not null
and t.sql_id=b.sql_id
and t.inst_id=b.inst_id
AND b.sql_id='&sqlid'
/





define sqlid='0za9fv0j1vgkk'

set linesize 300 pagesize 300
col name 			for a10
col value_string 	for a50
col datatype_string for a50
col bind_value 		for a20 
col bind_name 		for a20
col sql_text 		for a50 wrap
col is_bind_sensitive for a15
col is_bind_aware     for a15
select
b.sql_id,
b.hash_value,
b.last_captured,
b.name bind_name,
b.value_string bind_value,
t.sql_text sql_text
from DBA_HIST_SQLTEXT  t , gv$sql_bind_capture b 
where 1=1
--and  b.value_string is not null
and t.sql_id=b.sql_id
AND b.sql_id='&sqlid'
/



select  sh.snap_id, sql.sql_id, sql.name,sql.NAME from DBA_HIST_SQLBIND  sql , DBA_HIST_SNAPSHOT   sh 
where 1=1
and SQL_ID='&SQLID' 
and sql.snap_id=sh.snap_id
and sql.instance_number=sh.instance_number
-- and sq.instance_number='&INST_NO'     -- For info from a specific instance
-- and snap_id = '&SNAP_ID'                           -- For a specific snap id.
--and snap_id between '&BEGIN_SNAP' and '&END_SNAP'   -- For a range of snap id.
-- and sh.begin_interval_time between to_date('12/12/2021 10:00,'DD/MM/YYYY HH24:MI') and to_date('12/12/2021 12:00,'DD/MM/YYYY HH24:MI')
;


   SNAP_ID SQL_ID        NAME       NAME
---------- ------------- ---------- ----------
     56650 0za9fv0j1vgkk :B7        :B7
     56650 0za9fv0j1vgkk :B6        :B6
     56650 0za9fv0j1vgkk :B5        :B5
     56650 0za9fv0j1vgkk :B21       :B21
     56650 0za9fv0j1vgkk :B21       :B21
     56650 0za9fv0j1vgkk :B27       :B27
     56650 0za9fv0j1vgkk :B11       :B11
     56650 0za9fv0j1vgkk :B10       :B10
     56650 0za9fv0j1vgkk :B34       :B34
     5665

======

set line 200 pagesize 300
define 1='g6abypytc0004'


col username format a15
col sid format 9999

col datatype_string format a15 head 'DATA TYPE'
col child_nume format 999999 head 'CHILD|NUMBER'
col position format 999 head 'POS'
col name format a20
col value_string format a40
col bind_string format a40
col type_name format a15


var v_sql_id varchar2(30)
exec :v_sql_id := '&1'

break on inst_id on child_address on child_number on plan_hash_value

with plans as (
   select distinct inst_id, address, sql_id, child_address, child_number, plan_hash_value
   from gv$sql_plan
   where sql_id = :v_sql_id
)
select
   b.inst_id
   , b.child_address
   , b.child_number
   , p.plan_hash_value
   , b.position
   , b.name
   , b.value_string
	, anydata.GETTYPENAME(b.value_anydata) type_name
	-- use the anydata values as they are sometimes more reliable dependent on oracle version
	, case anydata.GETTYPENAME(b.value_anydata)
		when 'SYS.VARCHAR' then  anydata.accessvarchar(b.value_anydata)
		when 'SYS.VARCHAR2' then anydata.accessvarchar2(b.value_anydata)
		when 'SYS.CHAR' then anydata.accesschar(b.value_anydata)
		when 'SYS.DATE' then to_char(anydata.accessdate(b.value_anydata),'yyyy-mm-dd hh24:mi:ss')
		when 'SYS.TIMESTAMP' then to_char(anydata.accesstimestamp(b.value_anydata),'yyyy-mm-dd hh24:mi:ss')
				when 'SYS.NUMBER' then to_char(anydata.accessnumber(b.value_anydata))
	end bind_string
from GV$SQL_BIND_CAPTURE b
join plans p on p.address = b.address
   and p.inst_id = b.inst_id
   and p.sql_id = b.sql_id
   and p.child_address = b.child_address
   and p.child_number = b.child_number
where b.sql_id = :v_sql_id
order by b.inst_id, b.child_address, b.child_number, b.position
/


undef 1

COL binds_begin_time FOR A25

COL binds_instance_number HEAD INST FOR 9999

COL binds_name HEAD NAME FOR A15

COL binds_value_string HEAD VALUE_STRING FOR A100 WRAP

COL binds_position HEAD POS FOR 9999

COL binds_dup_position HEAD DPOS FOR 9999

SELECT

    sn.begin_interval_time binds_begin_time

  , sn.dbid

  , sn.instance_number     binds_instance_number

  , sb.sql_id

  , sb.name                binds_name

  , sb.position            binds_position

  , sb.dup_position        binds_dup_position

  , sb.datatype_string

--  , sb.character_sid

--  , sb.precision

--  , sb.scale

  , sb.was_captured

  , sb.last_captured

  , sb.value_string        binds_value_string

FROM

    dba_hist_snapshot sn

  , dba_hist_sqlbind  sb

WHERE

    sn.snap_id = sb.snap_id

AND sn.dbid    = sb.dbid

AND sn.instance_number = sb.instance_number

AND sb.sql_id = '&1'

AND &2

AND begin_interval_time >= &3

AND end_interval_time   <= &4

ORDER BY

    sn.begin_interval_time

  , sn.dbid

  , sn.instance_number

  , sb.sql_id

  , sb.name

  , sb.position

/

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

set linesize 500 pagesize 300

VARIABLE BgnSnap NUMBER

VARIABLE EndSnap NUMBER

VARIABLE DID NUMBER

VARIABLE INST_NUMBER number

exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;

exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;

exec select DBID into :DID from v$database;

exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;

define SQL_ID='XXXXXXX'

col sql_id format a13

col name format a12 trunc

col pos format 999

col dtyp format 99999

col dtyps format a15

col lcap format a17

col value_string format a40

col snapid format 999999

select sql_id

,snap_id snapid

,to_char(last_captured,'YYMMDD HH24:MI:SS') lcap

,name

,position pos

,datatype dtyp

,datatype_string dtyps

,value_string

from DBA_HIST_SQLBIND 

where SQL_ID ='&&sql_id'

--and SNAP_ID between bsnap and esnap

and snap_id BETWEEN :BgnSnap AND :EndSnap

order by snap_id, last_captured, position

;

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

https://unbeta.wordpress.com/2018/08/15/gather-latest-captured-bind-values/

set lines 500 verify off serveroutput on serveroutput on format wrapped feedback off

define SQL_ID='gqkr2um43ga39'

define HISTORY=1000

declare

  v_date date;

  v_snap_id number;

  v_name varchar2(30);

  v_datatype varchar2(15);

  v_captured varchar2(8);

  v_value_string varchar2(4000);

begin

 -- for v_counter in 0..&HISTORY-

 for v_counter in 0..2

  loop

    SELECT last_captured

      INTO v_date

      FROM (SELECT last_captured, ROWNUM AS idx

              FROM (  SELECT /*+ NO_QUERY_TRANSFORMATION(DBA_HIST_SQLBIND) */ last_captured

                        FROM dba_hist_sqlbind

                       WHERE sql_id = '&SQL_ID' AND last_captured IS NOT NULL

                    ORDER BY last_captured DESC))

     WHERE idx = v_counter + 1;

   SELECT MAX (snap_id)

     INTO v_snap_id

     FROM dba_hist_sqlbind

    WHERE sql_id = '&SQL_ID' AND last_captured = v_date;

    if v_date IS NOT NULL

    then

      v_name := 'Bind Name';

      v_datatype := 'Type';

      v_captured := 'Captured';

      v_value_string := 'Value';

      DBMS_OUTPUT.PUT_LINE('');

      DBMS_OUTPUT.PUT_LINE('      SQL ID: ' || '&SQL_ID');

      DBMS_OUTPUT.PUT_LINE('   Capture #: ' || TO_CHAR(v_counter+1));

      DBMS_OUTPUT.PUT_LINE('Capture Date: ' || TO_CHAR(v_date, 'YYYY-MM-DD HH24:MI:SS'));

      DBMS_OUTPUT.PUT_LINE('     Snap ID: ' || v_snap_id);

      DBMS_OUTPUT.PUT_LINE('');

      DBMS_OUTPUT.PUT_LINE(' ' || rpad(v_name,30)  || ' ' || rpad(v_datatype,15) || ' ' || rpad(v_captured,8) || ' ' || v_value_string);

      DBMS_OUTPUT.PUT_LINE(' ' || rpad('-',30,'-') || ' ' || rpad('-',15,'-')    || ' ' || rpad('-',8,'-')    || ' ' || rpad('-',24,'-'));

    end if;

    for bind in (select distinct name from dba_hist_sqlbind where sql_id='&SQL_ID' order by 1) loop

      select distinct name, datatype_string, was_captured, value_string into v_name, v_datatype, v_captured, v_value_string from (

        select name, datatype_string, was_captured, value_string

        from dba_hist_sqlbind

        where sql_id='&SQL_ID'

        and name = bind.name

        and last_captured = v_date and snap_id = v_snap_id

        union all

        select name, datatype_string, was_captured, value_string

        from dba_hist_sqlbind

        where sql_id='&SQL_ID'

        and name = bind.name

        and last_captured is null

        and snap_id = v_snap_id)

        where rownum < 2;

      DBMS_OUTPUT.PUT_LINE(' ' || rpad(v_name,30) || ' ' || rpad(v_datatype,15) || ' ' || rpad(v_captured,8) || ' ' || v_value_string);

    end loop;

  end loop;

end;

/

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

set linesize 400 pagesize 300

define sql_id='f705bwx3q0ydq'

col KEY1 for a15
col KEY2 for a15
col KEY3 for a15
col KEY4 for a15
col REPORT_PARAMETERS for a20
col REPORT_SUMMARY for a50 wrap 
col COMPONENT_NAME for a20
col REPORT_NAME for a20
col report_id new_v report_id
select * from 
 (
 select * from DBA_HIST_REPORTS
 where 1=1
 and key1 = '&&sql_id'
 and COMPONENT_NAME = 'sqlmonitor'
 order by generation_time desc
)
where rownum = 1;

 
   SNAP_ID       DBID INSTANCE_NUMBER  REPORT_ID COMPONENT_ID SESSION_ID SESSION_SERIAL# PERIOD_START_T PERIOD_END_TIM GENERATION_TIM COMPONENT_NAME       REPORT_NAME          REPORT_PARAMETERS    KEY1            KEY2            KEY3            KEY4            GENERATION_COST_SECONDS REPORT_SUMMARY                                       CON_DBID     CON_ID
---------- ---------- --------------- ---------- ------------ ---------- --------------- -------------- -------------- -------------- -------------------- -------------------- -------------------- --------------- --------------- --------------- --------------- ----------------------- -------------------------------------------------- ---------- ----------
     15923 3962735431               1      20591          263        487           26095 14-04-22 07:46 14-04-22 07:46 14-04-22 07:47 sqlmonitor           main                 /orarep/sqlmonitor/m f705bwx3q0ydq   16790304        04:14:2022 07:4 6#5305194#52924                       0 <report_repository_summary><sql sql_id="f705bwx3q0 3962735431          1
                                                                                                                                                                                ain?sql_id=f705bwx3q                                 6:26            02#0#0                                  ydq" sql_exec_start="04/14/2022 07:46:26" sql_exec
                                                                                                                                                                                0ydq&sql_exec_id=167                                                                                         _id="16790304"><status>DONE (ALL ROWS)</status><sq
                                                                                                                                                                                90304&sql_exec_start                                                                                         l_text>select count(*) from dba_autotask_window_cl
                                                                                                                                                                                =04:14:2022 07:46:26                                                                                         ients c, (select window_name, max(log_date) max_lo
                                                                                                                                                                                &last_refresh_time=0                                                                                         g_date </sql_text><first_refresh_time>04/14/2022 0
                                                                                                                                                                                                                                                                                                 90304&sql_exec_start
     
define report_id=20591
SELECT DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(RID => &&report_id , TYPE => 'text') FROM dual;

====

from Web !!!!
https://clarodba.wordpress.com/2023/10/09/showing-bind-values-from-sql-monitor-views-even-when-the-sql-monitor-report-itself-does-not-show-it-correctly/


def sqlid="48zqmd3dv8001"
def exec=""
def phv=""
def ins="1"
def datini="sysdate-30"
def datfin="sysdate"
 
col bind_name format a10 wrap
col used_value format a40
col peeked_value format a40
 
WITH
reports as (
    select *
    from (
        select snap_id, INSTANCE_NUMBER, r.key1 as sql_id, to_number(r.key2) as sql_exec_id, d.REPORT, r.report_summary,
            to_number(EXTRACTVALUE(XMLType(r.report_summary),'/report_repository_summary/sql/plan_hash')) SQL_PLAN_HASH_VALUE
        from DBA_HIST_REPORTS_DETAILS d 
        natural join DBA_HIST_REPORTS r
        where r.component_name = 'sqlmonitor'
        AND r.report_name = 'main'
        AND KEY1='&sqlid'
        and ('&exec' is null or KEY2 = to_number('&exec'))
        and ('&ins' is null or INSTANCE_NUMBER = to_number(nvl('&ins','1')))
        and GENERATION_TIME between &datini and &datfin
    )
    where ('&phv' is null or SQL_PLAN_HASH_VALUE = to_number('&phv'))
),
reports_u as (
    select
        snap_id, INSTANCE_NUMBER, sql_id, sql_exec_id, bind_name, bind_pos, bind_type, bind_typestr,
        case
             when bind_type in (1,2,96) /*varchar2,number,char*/ then bind_data
             when bind_type = 12 /*date*/ then to_char(to_date(bind_data,'mm/dd/yyyy hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
             when bind_type = 180 /*timestamp*/ then
                    to_char(
                        to_timestamp(
                            to_char(
                                dbms_stats.convert_raw_to_date(hextoraw(substr(bind_data,1,14)))
                                ,'yyyy-mm-dd hh24:mi:ss')
                            ,'yyyy-mm-dd hh24:mi:ss') 
                            + numtodsinterval(nvl(to_number(hextoraw(substr(bind_data,15,8)),'XXXXXXXX')/1e9,0), 'SECOND')
                        ,'yyyy-mm-dd hh24:mi:ss.ff9')
             else 'Not printable'
        end used_value
    from reports r
    cross join xmltable
        (
        '/report/sql_monitor_report/binds/bind' passing xmltype(REPORT)
        columns 
            bind_name varchar2(30) path './@name',
            bind_pos  number path './@pos',
            bind_type number path './@dty',
            bind_typestr varchar2(30) path './@dtystr',
            bind_data varchar2(1000) path '.'
        ) c
),
reports_p as (
    select
        snap_id, INSTANCE_NUMBER, sql_id, sql_exec_id, bind_name, bind_pos, bind_type, bind_typestr,
        case
             when bind_type = 1 /*varchar2*/ then utl_raw.cast_to_varchar2(bind_data)
             when bind_type = 2 /*number*/ then to_char(utl_raw.cast_to_number (bind_data))
             when bind_type = 96 /*char*/ then to_char(utl_raw.cast_to_varchar2(bind_data))
             when bind_type = 12 /*date*/ then TO_CHAR(dbms_stats.convert_raw_to_date(bind_data),'yyyy-mm-dd hh24:mi:ss')
             when bind_type = 180 /*timestamp*/ then
                    to_char(
                        to_timestamp(
                            to_char(
                                dbms_stats.convert_raw_to_date(hextoraw(substr(bind_data,1,14)))
                                ,'yyyy-mm-dd hh24:mi:ss')
                            ,'yyyy-mm-dd hh24:mi:ss') 
                            + numtodsinterval(nvl(to_number(hextoraw(substr(bind_data,15,8)),'XXXXXXXX')/1e9,0), 'SECOND')
                        ,'yyyy-mm-dd hh24:mi:ss.ff9')
             else 'Not printable'
        end peeked_value
    from reports r
    cross join xmltable
        (
        '/report/sql_monitor_report/plan/operation/other_xml/peeked_binds/bind'
        passing xmltype(REPORT)
        columns 
            bind_name varchar2(30) path './@nam',
            bind_pos  number path './@pos',
            bind_type number path './@dty',
            bind_typestr varchar2(30) path './@dtystr',
            bind_data varchar2(1000) path '.'
        ) p
),
reports_binds as (
    select snap_id, INSTANCE_NUMBER, sql_id, sql_exec_id, bind_name, bind_pos, 
        u.bind_type, u.bind_typestr,
        u.used_value, p.peeked_value
    from reports_p p
    full join reports_u u using (snap_id, INSTANCE_NUMBER, sql_id, sql_exec_id, bind_name, bind_pos)
    order by bind_pos
),
sqlmon as (
    select INST_ID, sql_id, sql_exec_id, BINDS_XML, SQL_CHILD_ADDRESS, SQL_PLAN_HASH_VALUE
    from gv$sql_monitor m
    where SQL_ID='&sqlid'
    and ('&exec' is null or SQL_EXEC_ID = to_number('&exec'))
    and ('&ins' is null or INST_ID = to_number(nvl('&ins','1')))
    and ('&phv' is null or SQL_PLAN_HASH_VALUE = to_number('&phv'))
    and SQL_PLAN_HASH_VALUE <> 0
    and nvl(PX_QCSID,SID) = SID and nvl(PX_QCINST_ID,INST_ID) = INST_ID --don't show Parallel slaves
    and SQL_EXEC_START between &datini and &datfin
),
sqlmon_u as (
    select
        INST_ID, sql_id, sql_exec_id, bind_name, bind_pos, bind_type, bind_typestr, 
        case
             when bind_type in (1,2,96) /*varchar2,number,char*/ then bind_data
             when bind_type = 12 /*date*/ then to_char(to_date(bind_data,'mm/dd/yyyy hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
             when bind_type = 180 /*timestamp*/ then 
                    to_char(
                        to_timestamp(
                            to_char(
                                dbms_stats.convert_raw_to_date(hextoraw(substr(bind_data,1,14)))
                                ,'yyyy-mm-dd hh24:mi:ss')
                            ,'yyyy-mm-dd hh24:mi:ss') 
                            + numtodsinterval(nvl(to_number(hextoraw(substr(bind_data,15,8)),'XXXXXXXX')/1e9,0), 'SECOND')
                        ,'yyyy-mm-dd hh24:mi:ss.ff9')
             else 'Not printable'
        end as used_value
    from sqlmon
    cross join xmltable
        (
            '/binds/bind' passing xmltype(BINDS_XML)
            columns 
                bind_name varchar2(30) path './@name',
                bind_pos  number path './@pos',
                bind_type number path './@dty',
                bind_typestr varchar2(30) path './@dtystr',
                bind_data varchar2(1000) path '.'
        ) u
    where BINDS_XML is not null
),
sqlmon_plan_p as (
    select
        m.INST_ID, m.sql_id, m.sql_exec_id, x.bind_name, x.bind_pos, x.bind_type,  
        case
             when bind_type = 1 /*varchar2*/ then utl_raw.cast_to_varchar2(bind_data)
             when bind_type = 2 /*number*/ then to_char(utl_raw.cast_to_number (bind_data))
             when bind_type = 96 /*char*/ then to_char(utl_raw.cast_to_varchar2(bind_data))
             when bind_type = 12 /*date*/ then TO_CHAR(dbms_stats.convert_raw_to_date(bind_data),'yyyy-mm-dd hh24:mi:ss')
             when bind_type = 180 /*timestamp*/ then 
                    to_char(
                        to_timestamp(
                            to_char(
                                dbms_stats.convert_raw_to_date(hextoraw(substr(bind_data,1,14)))
                                ,'yyyy-mm-dd hh24:mi:ss')
                            ,'yyyy-mm-dd hh24:mi:ss') 
                            + numtodsinterval(nvl(to_number(hextoraw(substr(bind_data,15,8)),'XXXXXXXX')/1e9,0), 'SECOND')
                        ,'yyyy-mm-dd hh24:mi:ss.ff9')
             else 'Not printable'
        end peeked_value
    from sqlmon m, gv$sql_plan p,
        xmltable
        (
            '/*/peeked_binds/bind' passing xmltype(p.OTHER_XML)
            columns 
                bind_name varchar2(30) path './@nam',
                bind_pos  number path './@pos',
                bind_type number path './@dty',
                bind_data varchar2(1000) path '.'
        ) x
    where p.OTHER_XML is not null
    and m.inst_id = p.inst_id
    and m.sql_id = p.sql_id
    and m.SQL_CHILD_ADDRESS = p.child_address
),
sqlmon_binds as (
    select INST_ID, sql_id, sql_exec_id, bind_name, bind_pos, 
        u.bind_type, u.bind_typestr,
        u.used_value, p.peeked_value
    from sqlmon_plan_p p
    full join sqlmon_u u using (INST_ID, sql_id, sql_exec_id, bind_name, bind_pos)
)
select 'HIST' src, snap_id, instance_number as inst, sql_id, sql_exec_id, 
    bind_name, bind_pos, bind_type, bind_typestr, used_value, peeked_value
from reports_binds
UNION ALL
select 'MEM' src, NULL as snap_id, inst_id as inst, sql_id, sql_exec_id, 
    bind_name, bind_pos, bind_type, bind_typestr, used_value, peeked_value
from sqlmon_binds
order by src, snap_id, inst, sql_id, sql_exec_id, bind_pos, used_value, peeked_value
/

define 1='ckz8r5cvwyui'
define 2=3134758917


col Full_Notes for a100

SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
'   sql_profile:     ' || extractvalue(xmlval, '/*/info[@type = "sql_profile"]')||'
   sql_patch:       ' || extractvalue(xmlval, '/*/info[@type = "sql_patch"]')||'
   baseline:        ' ||   extractvalue(xmlval, '/*/info[@type = "baseline"]')||'
   outline:         ' ||   extractvalue(xmlval, '/*/info[@type = "outline"]')||'
   dyn_sampling:    ' ||   extractvalue(xmlval, '/*/info[@type = "dynamic_sampling"]')||'
   dop:             ' ||   extractvalue(xmlval, '/*/info[@type = "dop"]')||'
   dop_reason:      ' ||   extractvalue(xmlval, '/*/info[@type = "dop_reason"]')||'
   pdml_reason:     ' ||   extractvalue(xmlval, '/*/info[@type = "pdml_reason"]')||'
   idl_reason:      ' ||   extractvalue(xmlval, '/*/info[@type = "idl_reason"]')||'
   queuing_reason:  ' ||   extractvalue(xmlval, '/*/info[@type = "queuing_reason"]')||'
   px_in_memory:    ' ||   extractvalue(xmlval, '/*/info[@type = "px_in_memory"]')||'
   px_in_memory_imc:' ||   extractvalue(xmlval, '/*/info[@type = "px_in_memory_imc"]')||'
   row_shipping:    ' ||   extractvalue(xmlval, '/*/info[@type = "row_shipping"]')||'
   index_size:      ' ||   extractvalue(xmlval, '/*/info[@type = "index_size"]')||'
   result_checksum: ' ||   extractvalue(xmlval, '/*/info[@type = "result_checksum"]')||'
   card_feedback:   ' ||   extractvalue(xmlval, '/*/info[@type = "cardinality_feedback"]')||'
   perf_feedback:   ' ||   extractvalue(xmlval, '/*/info[@type = "performance_feedback"]')||'
   xml_suboptimal:  ' ||   extractvalue(xmlval, '/*/info[@type = "xml_suboptimal"]')||'
   adaptive_plan:   ' || extractvalue(xmlval, '/*/info[@type = "adaptive_plan"]')||'
   spd_used:        ' || extractvalue(xmlval, '/*/spd/cu')||'
   spd_valid:       ' || extractvalue(xmlval, '/*/spd/cv')||'
   gtt_sess_stat:   ' || extractvalue(xmlval, '/*/info[@type = "gtt_session_st"]')||'
   db_version:      ' || extractvalue(xmlval, '/*/info[@type = "db_version"]')||'
   plan_hash_full:  ' || extractvalue(xmlval, '/*/info[@type = "plan_hash_full"]')||' 
   plan_hash:       ' || extractvalue(xmlval, '/*/info[@type = "plan_hash"]')||'
   plan_hash_2:     ' || extractvalue(xmlval, '/*/info[@type = "plan_hash_2"]') as Full_Notes
from
(select xmltype(other_xml) xmlval from
   (select other_xml
                   from dba_hist_sql_plan
                  where sql_id = '&&1'
                    and plan_hash_value = nvl('&&2',0)
                    and other_xml is not null
                    -- and not exists (select 1 from gv$sql_plan where sql_id = '&&1' and plan_hash_value = nvl('&&2',0) and other_xml is not null)
    union all
    select other_xml
                   from gv$sql_plan
                  where sql_id = '&&1'
                    and plan_hash_value = nvl('&&2',0)
                    and other_xml is not null
                 --   and (inst_id, child_number) in (select inst_id, child_number from gv$sql_plan where sql_id = '&&1' and plan_hash_value = nvl('&&2', 0) and rownum <= 1)
   )
);


FULL_NOTES
----------------------------------------------------------------------------------------------------
   sql_profile:
   sql_patch:
   baseline:
   outline:
   dyn_sampling:
   dop:
   dop_reason:
   pdml_reason:
   idl_reason:
   queuing_reason:
   px_in_memory:
   px_in_memory_imc:
   row_shipping:
   index_size:
   result_checksum:
   card_feedback:   yes
   perf_feedback:
   xml_suboptimal:
   adaptive_plan:
   spd_used:
   spd_valid:
   gtt_sess_stat:
   db_version:      12.2.0.1
   plan_hash_full:  3951519781
   plan_hash:       3134758917
   plan_hash_2:     3951519781

=======

define sql_id='0nrnusum1gq7t'

set linesi 200 pages 999 feed off verify off  

col bind_name format a20  

col end_time format a19  

col start_time format a19  

col peeked format a20  

col passed format a20

alter session set nls_date_format     ='DD/MM/YYYY HH24:MI:SS';  

alter session set nls_timestamp_format='DD/MM/YYYY HH24:MI:SS';

select  

pee.sql_id,  

ash.starting_time,  

ash.end_time,  

(EXTRACT(HOUR FROM ash.run_time) * 3600  

+ EXTRACT(MINUTE FROM ash.run_time) * 60  

+ EXTRACT(SECOND FROM ash.run_time)) run_time_sec,  

pee.plan_hash_value,  

pee.bind_name,  

pee.bind_pos,  

pee.bind_data peeked,  

run_t.bind_data passed  

from  

(  

select  

p.sql_id,  

p.sql_child_address,  

p.sql_exec_id,  

c.bind_name,  

c.bind_pos,  

c.bind_data  

from  

v$sql_monitor p,  

xmltable  

(  

'/binds/bind' passing xmltype(p.binds_xml)  

columns bind_name varchar2(30) path '/bind/@name',  

bind_pos number path '/bind/@pos',  

bind_data varchar2(30) path '/bind'  

) c  

where  

p.binds_xml is not null  

) run_t  

,  

(  

select  

p.sql_id,  

p.child_number,  

p.child_address,  

c.bind_name,  

c.bind_pos,  

p.plan_hash_value,  

case  

when c.bind_type = 1 then utl_raw.cast_to_varchar2(c.bind_data)  

when c.bind_type = 2 then to_char(utl_raw.cast_to_number(c.bind_data))  

when c.bind_type = 96 then to_char(utl_raw.cast_to_varchar2(c.bind_data))  

else 'Sorry: Not printable try with DBMS_XPLAN.DISPLAY_CURSOR'  

end bind_data  

from  

v$sql_plan p,  

xmltable  

(  

'/*/peeked_binds/bind' passing xmltype(p.other_xml)  

columns bind_name varchar2(30) path '/bind/@nam',  

bind_pos number path '/bind/@pos',  

bind_type number path '/bind/@dty',  

bind_data raw(2000) path '/bind'  

) c  

where  

p.other_xml is not null  

) pee,  

(  

select  

sql_id,  

sql_exec_id,  

max(sample_time - sql_exec_start) run_time,  

max(sample_time) end_time,  

sql_exec_start starting_time  

from  

v$active_session_history  

group by sql_id,sql_exec_id,sql_exec_start  

) ash  

where  

pee.sql_id=run_t.sql_id and  

pee.sql_id=ash.sql_id and  

run_t.sql_exec_id=ash.sql_exec_id and  

pee.child_address=run_t.sql_child_address and  

pee.bind_name=run_t.bind_name and  

pee.bind_pos=run_t.bind_pos 

and  pee.sql_id like nvl('&sql_id',pee.sql_id)  

order by 1,2,3,7 ;  

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

snap info !!!!

define start_time=''
define end_time=''
col PSEC for 9999.999

select to_char(end_interval_time,'mm-dd hh24') snap_time
,SNAP_ID
, instance_number
, avg(v_ps) pSec
from (
select end_interval_time
, instance_number
, v/ela v_ps
,SNAP_ID
from (
select round(s.end_interval_time,'hh24') end_interval_time
, s.instance_number
, (case when s.begin_interval_time = s.startup_time
then value
else value - lag(value,1) over (partition by sy.stat_id
, sy.dbid
, s.instance_number
, s.startup_time
order by sy.snap_id)
end)/1000000 v
, (cast(s.end_interval_time as date) - cast(s.begin_interval_time as date))*24*3600 ela
,s.SNAP_ID
from dba_hist_snapshot s
, dba_hist_sys_time_model sy
where s.dbid = sy.dbid
and s.instance_number = sy.instance_number
and s.snap_id = sy.snap_id
and sy.stat_name = 'DB time'
--and s.end_interval_time > to_date(&start_time,'MMDDYYYY')
--and s.end_interval_time < to_date(&end_time,'MMDDYYYY')
and s.end_interval_time > sysdate -1
 ))
group by to_char(end_interval_time,'mm-dd hh24'),SNAP_ID, instance_number
/






set linesize 300 pagesize 300
col ELAPSED for a27
select SNAP_ID, END_INTERVAL_TIME - BEGIN_INTERVAL_TIME as elapsed
, (cast(END_INTERVAL_TIME as date) - cast(BEGIN_INTERVAL_TIME as date))   *86400 as elapsed2
    from dba_hist_snapshot
    where 1=1
--and rownum < 24
and end_interval_time > sysdate -1
    order by 1;

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


Bind variable report !!!


set sqlblanklines on  trimspool on trimout on feedback off linesize 255  pagesize 50000  timing off  head off

var isdigits number
col sql_text for a140 word_wrap


define sql_id='7chqwp8a4f2cd'

--define SNAP_ID=3405

begin
select case regexp_substr(replace(name,':',''),'[[:digit:]]') when replace(name,':','') then 1 end into :isdigits
from DBA_HIST_SQL_BIND_METADATA b
where b.sql_id = '&&sql_id'
and rownum < 2
order by position;
end;
/
--
-- Create variable statements
--
select
'variable ' ||
   case :isdigits when 1 then replace(name,':','N') else substr(name,2,30) end || ' ' ||
replace(datatype_string,'CHAR(','VARCHAR2(') txt
from
DBA_HIST_SQL_BIND_METADATA
where sql_id='&&sql_id';

--
-- Set variable values from DBA_HIST_SQLSTAT 
--

select 'begin' txt from dual;

SELECT  
   case :isdigits when 1 then replace(b.name,':',':N') else b.name end ||
' := ' ||
case when b.datatype = 1 then '''' else null end ||
a.value_string ||
case when b.datatype = 1 then '''' else null end ||
';' txt
from table(
  select dbms_sqltune.extract_binds(bind_data) from DBA_HIST_SQLSTAT
  where sql_id like nvl('&&sql_id',sql_id)
  and snap_id like nvl('&&snap_id',snap_id)
and rownum < 2
and bind_data is not null) a, DBA_HIST_SQL_BIND_METADATA b
where b.sql_id = '&&sql_id'
and a.position = b.position
order by b.position;

select 'end;' txt from dual;
select '/' txt from dual;
--
-- Generate statement
--
select regexp_replace(sql_text,'(select |SELECT )','select /* test &&sql_id */ ',1,1) sql_text from (
select case :isdigits when 1 then replace(sql_text,':',':N') else sql_text end ||';' sql_text
from dba_hist_sqltext
where sql_id = '&&sql_id');

undef sql_id
undef snap_id
set feedback on head on

alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 400 pagesize 300

col FIRST_TM for a27
col LAST_TM for a27
col NAME for a15
col VALUE_STRING for a80

define 1='5tpxq8mnbtkj9'
select ash.session_id, ash.session_serial#,
       coalesce(ash.sql_exec_start,sb.last_captured) sql_exec_start,
       ash.user_id, ash.sql_id, ash.sql_plan_hash_value,
       ash.MAX_PGA, ash.MAX_TEMP,
       ash.first_tm, ash.last_tm, sb.name, sb.value_string
from (
  select session_id, session_serial#, sql_exec_start,
         user_id, sql_id, /*sql_child_number,*/ sql_plan_hash_value,
         /*sql_adaptive_plan_resolved*/
         max(pga_allocated) max_pga,
         max(temp_space_allocated) max_temp,
         min(sample_time) first_tm , max(sample_time) last_tm
    from dba_hist_active_sess_history where sql_id='&&1'
     and sql_child_number=0 /* only check parent process if parallel query */
     and sql_exec_start is not null /* Query actually ran ? */
--     and trunc(sample_time)>=trunc(sysdate-1)
--   order by sql_plan_hash_value,sql_exec_start desc
--
   group by session_id, session_serial#, sql_exec_start,
         user_id, sql_id, sql_child_number, sql_plan_hash_value,
         sql_adaptive_plan_resolved
--   order by sql_exec_start 
  ) ash
full JOIN
  (  select sb2.sql_id, sb2.last_captured, sb2.position, sb2.name, 
         sb2.value_string from (
    select distinct sb.sql_id,sb.LAST_CAPTURED,sb.SNAP_ID,    row_number() over (partition by last_captured     order by snap_id) rn
      from dba_hist_sqlbind sb 
	  where sql_id='&&1'
     group by sb.sql_id,sb.LAST_CAPTURED,sb.SNAP_ID
     order by last_captured,rn
    ) a 
      join dba_hist_sqlbind sb2 
        on a.sql_id=sb2.sql_id 
       and a.last_captured=sb2.LAST_CAPTURED
       and a.snap_id=sb2.SNAP_ID
     where a.rn=1 and sb2.dup_position is null
--     order by sb2.sql_id,sb2.last_captured,sb2.position 
  ) SB
  on sb.sql_id=ash.sql_id and sb.last_captured=ash.sql_exec_start
order by sql_exec_start desc, name;

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

set serveroutput on 

define sql_id='4s17ktwqy8ak8'

declare
v_sql_id varchar(100);
v_fulltext clob;
v_childnumber number;
begin
v_sql_id := '&sql_id';
--v_childnumber := 'childnumber';
SELECT LISTAGG(SQL_text, '') within 
       group (order by piece) 
     INTO v_fulltext
     FROM v$sqltext
    WHERE sql_id =v_sql_id;      
    for I in (select name,VALUE_STRING from v$sql_bind_capture 
where sql_id = V_SQL_ID 
--and child_number = V_CHILDNUMBER
)
LOOP
        v_fulltext := regexp_replace(v_fulltext,i.name||' ',i.value_string);
      end LOOP;
      DBMS_OUTPUT.PUT_LINE(v_fulltext);
end;

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

--- from web
https://clarodba.wordpress.com/2023/10/11/showing-captured-bind-values-from-execution-plans-and-captured-binds-joelkallmanday/

	
col bind_name format a10 wrap
col peeked_value format a40
col datatype head "DATA_TYPE" for a20
col src for a9
 
def sqlid="fb3gqgd87ac8h"
def datini=sysdate-1
def datfin=sysdate
 
select src, INST_ID, sql_id, plan_hash_value, child_number, c.bind_name, c.bind_pos, c.bind_type,
    decode(bind_type,1,'VARCHAR2',2,'NUMBER',12,'DATE',96,'CHAR',180,'TIMESTAMP',181,'TIMESTAMP WITH TZ',231,'TIMESTAMP WITH LTZ',to_char(bind_type)) datatype,
    case
         when c.bind_type = 1 /*varchar2*/ then utl_raw.cast_to_varchar2(c.bind_data)
         when c.bind_type = 2 /*number*/ then to_char(utl_raw.cast_to_number(c.bind_data))
         when c.bind_type = 96 /*char*/ then to_char(utl_raw.cast_to_varchar2(c.bind_data))
         when c.bind_type = 12 /*date*/ then TO_CHAR(dbms_stats.convert_raw_to_date(c.bind_data),'yyyy-mm-dd hh24:mi:ss')
         when bind_type = 180 /*timestamp*/ then
                to_char(
                    to_timestamp(
                        to_char(
                            dbms_stats.convert_raw_to_date(hextoraw(substr(bind_data,1,14)))
                            ,'yyyy-mm-dd hh24:mi:ss')
                        ,'yyyy-mm-dd hh24:mi:ss') 
                        + numtodsinterval(nvl(to_number(hextoraw(substr(bind_data,15,8)),'XXXXXXXX')/1e9,0), 'SECOND')
                    ,'yyyy-mm-dd hh24:mi:ss.ff9')
         else 'Not printable'
    end as peeked_value
from (
    select 'SQLPLAN-M' src, sql_id, plan_hash_value, OTHER_XML, child_number, INST_ID
    from gv$sql_plan
    UNION ALL
    select 'SQLPLAN-H' src, sql_id, plan_hash_value, OTHER_XML, to_number(NULL) as child_number, to_number(NULL) as INST_ID
    from dba_hist_sql_plan
    join dba_hist_snapshot using (DBID)
    where END_INTERVAL_TIME between &datini and &datfin
) p, 
xmltable
    (
    '/*/peeked_binds/bind' passing xmltype(p.other_xml)
    columns 
        bind_name varchar2(30) path './@nam',
        bind_pos number path './@pos',
        bind_type number path './@dty',
        bind_data  raw(2000) path '.'
    ) c
where sql_id = '&sqlid'
and p.other_xml is not null
order by src, inst_id, sql_id, plan_hash_value, child_number, bind_pos, peeked_value
/



col bind_name format a10 wrap
col captured_value format a40
col DATATYPE_STRING for a20
col src for a9
 
def sqlid="4s17ktwqy8ak8"
def datini=sysdate-1
def datfin=sysdate
 
select DISTINCT src, INST_ID, sql_id, plan_hash_value, child_number, 
    name as bind_name, position as bindpos, DATATYPE_STRING,
    case
         when DATATYPE in (1,2,96) /*varchar2,number,char*/ then VALUE_STRING
         when DATATYPE = 12 /*date*/ then to_char(anydata.accessDate(value_anydata),'yyyy-mm-dd hh24:mi:ss')
         when DATATYPE = 180 /*timestamp*/ then to_char(anydata.accesstimestamp(value_anydata),'yyyy-mm-dd hh24:mi:ss.ff9')
        else 'Not printable'
    END captured_value
from (
    select 'CAPTURE-M' src, 
        sql_id, child_number, PLAN_HASH_VALUE, INST_ID, 
        name, position, datatype, DATATYPE_STRING, value_string, value_anydata
    from gv$sql_bind_capture
    join gv$sql s using (INST_ID, sql_id, child_number)
    UNION ALL
    select 'CAPTURE-H' src, 
        sql_id, to_number(NULL) as child_number, to_number(NULL) PLAN_HASH_VALUE, INSTANCE_NUMBER as INST_ID, 
        name, position, datatype, DATATYPE_STRING, value_string, value_anydata
    from dba_hist_sqlbind
    join dba_hist_snapshot using (DBID, INSTANCE_NUMBER, SNAP_ID)
    where END_INTERVAL_TIME between &datini and &datfin
    ) c
where sql_id = '&sqlid'
order by src, INST_ID, sql_id, plan_hash_value, child_number, position, captured_value
/