Find Row Count Of All Partitions for oracle Table

--- create test table 


 https://asktom.oracle.com/pls/apex/asktom.search?tag=daywise-partition-automatically

create table sales
  ( ts   timestamp,
    id   int,
    amt  number,
    product int,
    customer int,
    item_cnt int,
    terminal int,
    operator int,
    credit_card int
      )
partition by range ( ts )
interval ( numtodsinterval(1,'HOUR') )
(
  partition p1 values less than ( timestamp '2022-04-01 00:00:00' )
);



insert /*+ APPEND */ into sales (ts,id,amt,product,customer)
   select date '2022-04-01' + rownum / 240, rownum, dbms_random.value(1,100), dbms_random.value(1,100),dbms_random.value(1,100)
   from dual
   connect by level ;

 
 
 set line 300 pagesize 300
 col high_value format a60
 col PARTITION_NAME for a20
select partition_name, high_value from dba_tab_partitions
where table_name = 'SALES'
order by partition_position;




define table_owner='SCOTT'
define TABLE_NAME='SALES'

set serverout on size 1000000
set verify off
declare
sql_stmt varchar2(1024);
row_count number;
cursor get_tab is
select table_name,partition_name
from dba_tab_partitions
where table_owner=upper('&&TABLE_OWNER') 
and table_name='&&TABLE_NAME'
;
begin
dbms_output.put_line('Checking Record Counts for table_name');
dbms_output.put_line('Log file to numrows_part_&&TABLE_OWNER.lst ....');
dbms_output.put_line('....');
for get_tab_rec in get_tab loop
BEGIN
sql_stmt := 'select count(*) from &&TABLE_OWNER..'||get_tab_rec.table_name||' partition ( '||get_tab_rec.partition_name||' )';

EXECUTE IMMEDIATE sql_stmt INTO row_count;
dbms_output.put_line('Table '||rpad(get_tab_rec.table_name||'('||get_tab_rec.partition_name||')',50)||' '||TO_CHAR(row_count)||' rows.');
exception when others then
dbms_output.put_line
('Error counting rows for table '||get_tab_rec.table_name);
END;
end loop;
end;
/
set verify on


....
Table SALES(P1)                                          0 rows.
Table SALES(SYS_P67687)                                  9 rows.
Table SALES(SYS_P67688)                                  10 rows.
Table SALES(SYS_P67689)                                  10 rows.
Table SALES(SYS_P67690)                                  10 rows.
Table SALES(SYS_P67691)                                  10 rows.

User has no SELECT privilege on V$SQL_PLAN_STATISTICS_ALL

User has no SELECT privilege on V$SQL_PLAN_STATISTICS_ALL User has no SELECT privilege on V$SESSION

error 


 select * from table(dbms_xplan.display_cursor(null,null,format=>'ADVANCED +ALLSTATS LAST, IOSTATS -PROJECTION -OUTLINE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION



PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION



PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
User has no SELECT privilege on V$SQL_PLAN_STATISTICS_ALL

====

Grant below to user  !!!


grant select on v_$sql_plan to scott ;

grant select on v_$session to scott ;

grant select on v_$sql_plan_statistics_all to scott ;

grant select on v_$sql to scott ;

===

Now all good .


set linesize 150 pagesize 300
select * from table(dbms_xplan.display_cursor(null,null,format=>'ADVANCED +ALLSTATS LAST, IOSTATS -PROJECTION -OUTLINE'));SQL> SQL>

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8trj2kacqhm6f, child number 0
-------------------------------------
select count(*) from t where a = 42 and b=42

Plan hash value: 2966233522

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |    22 (100)|          |      1 |00:00:00.01 |      75 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |     6 |            |          |      1 |00:00:00.01 |      75 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |      1 |     6 |    22   (0)| 00:00:01 |    100 |00:00:00.01 |      75 |
---------------------------------------------------------------------------------------------------------------------

Oracle Objects metric

from 
https://github.com/xtender/xt_scripts/blob/master/tops/sessmetric.sql


set linesize 500 
col time_interval       format a19
col username            format a25
col osuser              format a20
col action              format a25
col module              format a30
col sql_exec_start      format a14
col PHYSICAL_READS      heading "Phy reads"
col PHYSICAL_READ_PCT   format 999.90
col LOGICAL_READ_PCT    format 999.90
col PE_OBJECT           format a40
col PO_OBJECT           format a40


with v as (
          select--+ no_merge
             begin_time
            ,end_time
            ,intsize_csec/100       as seconds
            ,session_id             as sid
           -- ,session_serial_num     as serial#
			,m.SERIAL_NUM as serial#
            ,cpu
            ,physical_reads
            ,logical_reads
            ,pga_memory
            ,hard_parses
            ,soft_parses
            ,physical_read_pct
            ,logical_read_pct
            ,dense_rank()over(order by cpu            desc) cpu_rnk
            ,dense_rank()over(order by physical_reads desc) phy_reads_rnk
            ,dense_rank()over(order by logical_reads  desc) logical_reads_rnk
          from gv$sessmetric m
          where m.cpu>0 or m.PHYSICAL_READS>0
)
select 
    to_char(begin_time,'hh24:mi:ss')
  ||' - '
  ||to_char(end_time,'hh24:mi:ss') time_interval
   ,v.seconds
   ,s.sid
   ,s.serial#
   ,s.username
   ,s.osuser
   ,substr(s.action,1,25) action
   ,substr(s.module,1,30) module
   ,s.sql_id
     ,nvl2( pe.owner
           ,pe.owner
            ||'.'||pe.OBJECT_NAME
            ||nvl2(pe.PROCEDURE_NAME,'.'||pe.PROCEDURE_NAME,'')
           ,''
          )                                        as pe_object
     ,nvl2( po.owner
           ,po.owner
            ||'.'||po.OBJECT_NAME
            ||nvl2(po.PROCEDURE_NAME,'.'||po.PROCEDURE_NAME,'')
           ,null
          )                                        as po_object
   ,cpu
   ,physical_reads
   ,logical_reads
   ,pga_memory
   ,hard_parses
   ,soft_parses
   ,physical_read_pct
   ,logical_read_pct
-- _IF_ORA11_OR_HIGHER 
    ,to_char(s.sql_exec_start,'dd/mm hh24:mi:ss')   as sql_exec_start
from  v
     ,gv$session s
     ,dba_procedures pe
     ,dba_procedures po
where v.sid     = s.sid
  and v.serial# = s.serial#
  and pe.OBJECT_ID    (+)    = s.PLSQL_ENTRY_OBJECT_ID
  and pe.SUBPROGRAM_ID(+)    = s.PLSQL_ENTRY_SUBPROGRAM_ID
  and po.OBJECT_ID    (+)    = s.PLSQL_OBJECT_ID
  and po.SUBPROGRAM_ID(+)    = s.PLSQL_SUBPROGRAM_ID
  and(   v.cpu_rnk           <=10
      or v.phy_reads_rnk     <=10
      or v.logical_reads_rnk <= 10
     )
order by 
      cpu_rnk
     ,phy_reads_rnk
     ,logical_reads_rnk
/

col time_interval       clear
col username            clear
col osuser              clear
col action              clear
col module              clear
col sql_exec_start      clear
col PHYSICAL_READS      clear
col PHYSICAL_READ_PCT   clear
col LOGICAL_READ_PCT    clear
col PE_OBJECT           clear
col PO_OBJECT           clear

How much CPU a session consuming at a given time in oracle....

https://stackoverflow.com/questions/58820965/how-much-cpu-a-session-consuming-at-a-given-time-in-oracle
Roger Cornejo


-- uncomment based on your requirement 

set linesize 2000
var order_by varchar2(10); 

-- begin :order_by := 'ELAP'; end;
--/

 -- begin :order_by := 'IO'; end;
 --/


 --begin :order_by := 'CPU'; end;
 -- /

 --begin :order_by := 'GET'; end;
 -- /

 begin :order_by := 'READ'; end;
 /

 --begin :order_by := 'EXEC'; end;
 -- /

 --begin :order_by := 'PARSE'; end;
  --/

  --begin :order_by := 'MEM'; end;
 /


 --begin :order_by := 'VERS'; end;
 -- /
 
 
 --begin :order_by := 'ELAP_EXEC'; end;
  --/

 --begin :order_by := 'SNAP'; end;
 -- /

set numf 9999999999999999999
col "Tot Wait"  		for 99999999999999999   
col IOWAIT  			for 99999999999999999
col CLWAIT  			for 99999999999999999
col BASELINE_PLAN_NAME 	for a15
col SCHEMA  			for a20
col sql_text 			for a70 wrap
col "Time Per Exec" 	for a10

select ord ord
,  case 
       when nvl(:order_by, 'GET') in ('ELAP' , '1') then 'elapsed_sec' 
       when nvl(:order_by, 'GET') in ('CPU'  , '2') then 'cpu_sec'
       when nvl(:order_by, 'GET') in ('IO'   , '3') then 'iowait'
       when nvl(:order_by, 'GET') in ('GET'  , '4') then 'buffer_gets'
       when nvl(:order_by, 'GET') in ('READ' , '5') then 'disk_reads'
       when nvl(:order_by, 'GET') in ('EXEC' , '6') then 'executions'
       when nvl(:order_by, 'GET') in ('PARSE', '7') then 'parse_calls'
       when nvl(:order_by, 'GET') in ('MEM'  , '8') then 'sharable_mem'
       when nvl(:order_by, 'GET') in ('VERS' , '9') then 'version_count' 
       when nvl(:order_by, 'GET') in ('ELAP_EXEC' , '10') then 'time_per_exec' 
       when nvl(:order_by, 'GET') in ('SNAP' , '11') then 'snap_id' 
       else 'buffer_gets'
  end order_by
, schema  
, sql_id
, plan_hash_value "Plan Hash Value"
,      (select 
        max(to_number(extractvalue(
        xmltype(other_xml),'other_xml/info[@type="plan_hash_2"]'))) plan_hash_2
        from   dba_hist_sql_plan hp
        where  hp.sql_id          = main_query.sql_id
        and    hp.plan_hash_value = main_query.plan_hash_value
        and    hp.other_xml is not null) plan_hash_2
, (select max(last_refresh_time) from gv$sql_monitor sm where sm.sql_id = main_query.sql_id and sm.sql_plan_hash_value = main_query.plan_hash_value) monitor_last_refresh_time
, time_per_exec "Time Per Exec"
, executions "Exec-utions"
, clock_time "Clock Time"
, px_servers_execs "px servers execs"
, sql_text
, buffer_gets "Buffer Gets"
, fetches
, rows_processed "rows processed"
, round(rows_processed / nullif(fetches, 0)) "rows per fetch" 
, end_of_fetch_count "end of fetch count"
, sorts
, disk_reads "disk reads"
, tot_wait "Tot Wait"
, iowait
, clwait
, apwait
, ccwait
, direct_writes "direct writes"
, elapsed_sec "Elap-sed (Sec)"
, cpu_sec "CPU Sec"
, plsql_sec "PL/SQL sec"
, plsexec_time "pls exec time"
, javexec_time "java exec time"
, sharable_mem "shar-able mem"
-- per exec calculations
, case when executions > 0 then buffer_gets/executions else 0 end "Buffer Gets per exec"
, case when executions > 0 then fetches/executions else 0 end  "Fetches Gets per exec"
, case when executions > 0 then rows_processed/executions else 0 end  "rows per exec"
, case when executions > 0 then sorts/executions else 0 end  "sorts per exec"
, case when executions > 0 then disk_reads/executions else 0 end  "disk reads per exec"
, case when executions > 0 then tot_wait/executions else 0 end  "Tot Wait per exec"
, case when executions > 0 then iowait/executions else 0 end  "iowait per exec"
, case when executions > 0 then clwait/executions else 0 end  "clwait  per exec"
, case when executions > 0 then apwait/executions else 0 end  "apwait per exec"
, case when executions > 0 then ccwait/executions else 0 end  "ccwait  per exec"
, case when executions > 0 then direct_writes/executions else 0 end  "direct writes  per exec"
, case when executions > 0 then elapsed_sec/executions else 0 end  "Elap-sed (Sec)  per exec"
, case when executions > 0 then cpu_sec/executions else 0 end  "CPU Sec per exec"
, case when executions > 0 then plsql_sec/executions else 0 end  "PL/SQL sec  per exec"
, case when executions > 0 then plsexec_time/executions else 0 end  "pls exec time  per exec"
, case when executions > 0 then javexec_time/executions else 0 end  "java exec time per exec"
, case when executions > 0 then sharable_mem/executions else 0 end  "shar-able mem per exec"
-- per row calculations
, case when rows_processed > 0 then buffer_gets/rows_processed else 0 end "Buffer Gets per row"
, case when rows_processed > 0 then fetches/rows_processed else 0 end  "Fetches Gets per row"
, case when rows_processed > 0 then rows_processed/rows_processed else 0 end  "rows per row"
, case when rows_processed > 0 then sorts/rows_processed else 0 end  "sorts per row"
, case when rows_processed > 0 then disk_reads/rows_processed else 0 end  "disk reads per row"
, case when rows_processed > 0 then tot_wait/rows_processed else 0 end  "Tot Wait per row"
, case when rows_processed > 0 then iowait/rows_processed else 0 end  "iowait per row"
, case when rows_processed > 0 then clwait/rows_processed else 0 end  "clwait  per row"
, case when rows_processed > 0 then apwait/rows_processed else 0 end  "apwait per row"
, case when rows_processed > 0 then ccwait/rows_processed else 0 end  "ccwait  per row"
, case when rows_processed > 0 then direct_writes/rows_processed else 0 end  "direct writes  per row"
, case when rows_processed > 0 then elapsed_sec/rows_processed else 0 end  "Elap-sed (Sec)  per row"
, case when rows_processed > 0 then cpu_sec/rows_processed else 0 end  "CPU Sec per row"
, case when rows_processed > 0 then plsql_sec/rows_processed else 0 end  "PL/SQL sec  per row"
, case when rows_processed > 0 then plsexec_time/rows_processed else 0 end  "pls exec time  per row"
, case when rows_processed > 0 then javexec_time/rows_processed else 0 end  "java exec time per row"
, case when rows_processed > 0 then sharable_mem/rows_processed else 0 end  "shar-able mem per row"
, loaded_versions "loaded vers-ions" 
, version_count "ver-sion count"
, loads
, invalidations "invalid-ations"
, parse_calls "parse calls"
, module 
, command_type_name
, to_char(min_time, 'dd/mm/yyyy HH24:MI:SS') min_time
, to_char(max_time ,'dd/mm/yyyy HH24:MI:SS') max_time
, min_snap_id "Min Snap Id"
, max_snap_id "Max Snap Id"
, sql_profile
, Baseline_plan_name -- does not work for 10g
from
(
select schema  
   , plan_hash_value
   , sql_id
   , rownum ord
   , sub.elapsed_sec
   , CASE 
     WHEN elapsed_sec > 86399
          THEN elapsed_sec || ' sec' 
     WHEN elapsed_sec <= 86399
          THEN to_char(to_date(round(elapsed_sec) ,'SSSSS'), 'HH24:MI:SS') 
     END as clock_time
   , case when executions <> 0
     then CASE 
     WHEN round(elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs))) > 86399
          THEN round(elapsed_sec/(executions)*decode(px_servers_execs, 0, 1, px_servers_execs)) || ' sec' 
     WHEN round(elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs))) <= 86399
          THEN to_char(to_date(round(elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs))) ,'SSSSS'), 'HH24:MI:SS') 
     END 
     end as time_per_exec
   , cpu_sec
   , plsql_sec
   , executions
   , buffer_gets
   , sharable_mem
   , loaded_versions
   , version_count
   , module 
   , fetches
   , end_of_fetch_count
   , sorts
   , px_servers_execs
   , loads
   , invalidations
   , parse_calls
   , disk_reads
   , rows_processed
   , iowait
   , clwait
   , apwait
   , ccwait
   , tot_wait
   , direct_writes
   , plsexec_time
   , javexec_time
   , (select max(DBMS_LOB.SUBSTR(sql_text, 3800)) from dba_hist_sqltext st where st.sql_id = sub.sql_id) sql_text
   , (select max(name) from dba_hist_sqltext st, audit_actions aa where st.sql_id = sub.sql_id and aa.action = st.command_type)   command_type_name
   , min_time
   , max_time
   , min_snap_id
   , max_snap_id
   , sql_profile
   , (select nvl(min(sql_plan_baseline), 'none') from v$sql sql where sql.sql_id  = sub.sql_id and sql.plan_hash_value = sub.plan_hash_value) Baseline_plan_name -- does not work for 10g
from
   ( -- sub to sort before rownum
     select
        sql_id
        , plan_hash_value
        , round(sum(elapsed_time_delta)/1000000) as elapsed_sec
        , round(sum(cpu_time_delta)    /1000000) as cpu_sec 
        , round(sum(plsexec_time_delta)/1000000) as plsql_sec 
        , sum(executions_delta) as executions
        , sum(buffer_gets_delta) as buffer_gets      
        , sum(sharable_mem) as sharable_mem
        , sum(loaded_versions) as loaded_versions
        , sum(version_count) as version_count
        , max(module) as module 
        , sum(fetches_delta) as fetches
        , sum(end_of_fetch_count_delta) as end_of_fetch_count
        , sum(sorts_delta) as sorts
        , sum(px_servers_execs_delta) as px_servers_execs
        , sum(loads_delta) as loads
        , sum(invalidations_delta) as invalidations
        , sum(parse_calls_delta) as parse_calls
        , sum(disk_reads_delta) as disk_reads
        , sum(rows_processed_delta) as rows_processed
        , sum(iowait_delta) as iowait
        , sum(clwait_delta) as clwait
        , sum(apwait_delta) as apwait
        , sum(ccwait_delta) as ccwait
        , sum(iowait_delta) + sum(clwait_delta) + sum(apwait_delta) + sum(ccwait_delta) as tot_wait
        , sum(direct_writes_delta) as direct_writes
        , sum(plsexec_time_delta) as plsexec_time
        , sum(javexec_time_delta) as javexec_time
        , max(parsing_schema_name) as schema
        , max(snap.end_INTERVAL_TIME) max_time
        , min(snap.end_INTERVAL_TIME) min_time
        , min(stat.snap_id) min_snap_id
        , max(stat.snap_id) max_snap_id
        , min(nvl(sql_profile, 'none')) sql_profile     
     from
        dba_hist_snapshot snap
        , dba_hist_sqlstat stat
     where 1=1
          and nvl(:order_by, 'GET') like '%' 
          and snap.dbid = stat.dbid
          and snap.instance_number = stat.instance_number
          and snap.snap_id = stat.snap_id
        --  and snap.snap_id between nvl(:start_snap_id, snap.snap_id) and nvl(:end_snap_id, snap.snap_id)
        --  and nvl(parsing_schema_name,'%') like nvl(upper(:username), nvl(parsing_schema_name,'%')  )
        --  and sql_id = nvl(:sql_id, sql_id)
        --  and nvl(plan_hash_value,0) = nvl(:plan_hash_value, nvl(plan_hash_value,0))
        --   and nvl(module,'x') like nvl(:module, nvl(module,'x'))
        --  and stat.instance_number = nvl(:inst_id, stat.instance_number)
        --  and decode(:days_back_only_Y_N,'Y', end_INTERVAL_TIME, trunc(sysdate-:days_back) ) >= trunc(sysdate-:days_back)
        --  and (trunc(begin_INTERVAL_TIME, 'MI') >=  to_date(nvl(:sam_tm_str_MM_DD_YYYY_HH24_MI, to_char(begin_interval_time, 'MM_DD_YYYY_HH24_MI')),'MM_DD_YYYY_HH24_MI') 
        --  and trunc(end_interval_time, 'MI') <= to_date(nvl(:sam_tm_end_MM_DD_YYYY_HH24_MI, to_char(end_interval_time, 'MM_DD_YYYY_HH24_MI')),'MM_DD_YYYY_HH24_MI'))
        --  and (to_number(to_char(begin_INTERVAL_TIME, 'HH24')) between nvl(:begin_hour, 0) and  nvl(:end_hour, 24) 
        --  or to_number(to_char(begin_INTERVAL_TIME, 'HH24')) between nvl(:begin_hour2, nvl(:begin_hour, 0)) and  nvl(:end_hour2, nvl(:end_hour, 24)))
group by sql_id, plan_hash_value --, force_matching_signature  -- , stat.instance_number
order by 
  case 
       when nvl(:order_by, 'GET') in ('ELAP' , '1') then elapsed_sec 
       when nvl(:order_by, 'GET') in ('CPU'  , '2') then cpu_sec 
       when nvl(:order_by, 'GET') in ('IO'   , '3') then iowait 
       when nvl(:order_by, 'GET') in ('GET'  , '4') then buffer_gets 
       when nvl(:order_by, 'GET') in ('READ' , '5') then disk_reads 
       when nvl(:order_by, 'GET') in ('EXEC' , '6') then executions 
       when nvl(:order_by, 'GET') in ('PARSE', '7') then parse_calls 
       when nvl(:order_by, 'GET') in ('MEM'  , '8') then sharable_mem 
       when nvl(:order_by, 'GET') in ('VERS' , '9') then version_count 
       when nvl(:order_by, 'GET') in ('ELAP_EXEC' , '10') then 
	   case when executions <> 0 
	   then elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs)) 
	   else elapsed_sec end 
       when nvl(:order_by, 'GET') in ('SNAP' , '11') then min_snap_id 
       else buffer_gets
  end desc
   ) sub
where 1=1
  and rownum <= 10
) main_query
where 1=1
 -- and nvl(upper(sql_text), '%') like nvl(upper(:sql_text), '%')
 -- and nvl(command_type_name, 'x') like nvl(:command_type_name, nvl(command_type_name, 'x'))
order by 
  case 
       when nvl(:order_by, 'GET') in ('ELAP' , '1') then elapsed_sec 
       when nvl(:order_by, 'GET') in ('CPU'  , '2') then cpu_sec 
       when nvl(:order_by, 'GET') in ('IO'   , '3') then iowait 
       when nvl(:order_by, 'GET') in ('GET'  , '4') then buffer_gets  -- essentially an overall workload ordering
       when nvl(:order_by, 'GET') in ('READ' , '5') then disk_reads 
       when nvl(:order_by, 'GET') in ('EXEC' , '6') then executions 
       when nvl(:order_by, 'GET') in ('PARSE', '7') then parse_calls 
       when nvl(:order_by, 'GET') in ('MEM'  , '8') then sharable_mem 
       when nvl(:order_by, 'GET') in ('VERS' , '9') then version_count 
       when nvl(:order_by, 'GET') in ('ELAP_EXEC' , '10') then case when executions <> 0 then elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs)) else elapsed_sec end 
       when nvl(:order_by, 'GET') in ('SNAP' , '11') then min_snap_id 
       else buffer_gets
  end desc
;

                 ORD ORDER_BY      SCHEMA               SQL_ID             Plan Hash Value          PLAN_HASH_2 MONITOR_L Time Per E          Exec-utions Clock Time                           px servers execs SQL_TEXT                                                                         Buffer Gets              FETCHES       rows processed       rows per fetch   end of fetch count             SORTS           disk reads           Tot Wait             IOWAIT             CLWAIT               APWAIT               CCWAIT        direct writes       Elap-sed (Sec)  CPU Sec    PL/SQL sec        pls exec time       java exec time        shar-able mem Buffer Gets per exec Fetches Gets per exec        rows per exec       sorts per exec  disk reads per exec Tot Wait per exec      iowait per exec     clwait  per exec      apwait per exec     ccwait  per exec direct writes  per exec Elap-sed (Sec)  per exec     CPU Sec per exec PL/SQL sec  per exec pls exec time  per exec java exec time per exec shar-able mem per exec  Buffer Gets per row Fetches Gets per row      rows per row        sorts per row   disk reads per row     Tot Wait per row            iowait per row      clwait  per row       apwait per row      ccwait  per row direct writes  per row Elap-sed (Sec)  per row      CPU Sec per row  PL/SQL sec  per row pls exec time  per row java exec time per row shar-able mem per row     loaded vers-ions    ver-sion count                LOADS       invalid-ations          parse calls MODULE               COMMAND_TYPE_NAME             MIN_TIME            MAX_TIME                     Min Snap Id          Max Snap Id SQL_PROFILE                                                      BASELINE_PLAN_N
-------------------- ------------- -------------------- ------------- -------------------- -------------------- --------- ---------- -------------------- -------------------------------------------- -------------------- ---------------------------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ------------------ ------------------ ------------------ -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------------------- ------------------------ -------------------- -------------------- ----------------------- ----------------------- ---------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ---------------------- ----------------------- -------------------- -------------------- ---------------------- ---------------------- --------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------------------------------- ---------------------------- ------------------- ------------------- -------------------- -------------------- ---------------------------------------------------------------- ---------------
                                                                                                                                                                                            (:sig IS NULL AND
                                                                                                                                                                                             ((:existingSQL IS NOT NULL AND :newSQL IS NOT NULL) OR
                                                                                                                                                                                              (:existingSQL IS NOT NULL AND
                                                                                                                                                                                               :newSQL IS NULL AND

User Tablespaces

alter session enable parallel query;	
	  
define owner='SCOTT'		  --- change if required 
with all_ts_user as
   (
   select tablespace_name 		from dba_lobs   			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_clusters  			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_indexes 			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_rollback_segs 			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_tables  			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_object_tables 			where 1=1 and OWNER='&owner'         union all
   select def_tablespace_name 	        from dba_part_tables  			where 1=1 and OWNER='&owner'         union all
   select def_tablespace_name 	        from dba_part_indexes 			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_tab_partitions  		where 1=1 and TABLE_OWNER='&owner'   union all
   select tablespace_name 		from dba_ind_partitions  		where 1=1 and index_OWNER='&owner'   union all
   select tablespace_name 		from dba_tab_subpartitions 		where 1=1 and TABLE_OWNER='&owner'   union all
   select tablespace_name 		from dba_ind_subpartitions  		where 1=1 and index_OWNER='&owner'   union all
   select def_tablespace_name 	        from dba_part_lobs 			where 1=1 and TABLE_OWNER='&owner'   union all
   select tablespace_name 		from dba_lob_partitions   		where 1=1 and TABLE_OWNER='&owner'   union all
   select tablespace_name 		from dba_lob_subpartitions  		where 1=1 and TABLE_OWNER='&owner'   union all
   select tablespace_name 		from dba_subpartition_templates 	where 1=1 and USER_NAME='&owner'     union all
   select tablespace_name 		from dba_lob_templates  		where 1=1 and USER_NAME='&owner'     union all
   select tablespace_name 		from dba_segments     			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_extents   			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_undo_extents 			where 1=1 and OWNER='&owner'
   )
   select distinct tablespace_name from all_ts_user
alter session disable parallel query;



TABLESPACE_NAME
------------------------------
USERS
DATA

	
alter session enable parallel query;	

set pagesize 300 	  
define owner='SCOTT'	
		  
with all_possible_ts as
   (
   select 'dba_lobs' From1 			,tablespace_name from dba_lobs   where 1=1 and OWNER= '&owner'         					union all
   select 'dba_clusters'			,tablespace_name from dba_clusters  where 1=1 and OWNER= '&owner'                 		union all
   select 'dba_indexes' 			,tablespace_name from dba_indexes where 1=1 and OWNER= '&owner'                   		union all
   select 'dba_rollback_segs' 			,tablespace_name from dba_rollback_segs where 1=1 and OWNER= '&owner'             		union all
   select 'dba_tables'				,tablespace_name from dba_tables  where 1=1 and OWNER= '&owner'                   		union all
   select 'dba_object_tables' 			,tablespace_name from dba_object_tables where 1=1 and OWNER= '&owner'              		union all
   select 'dba_part_tables' 			,def_tablespace_name from dba_part_tables  where 1=1 and OWNER= '&owner'          		union all
   select 'dba_part_indexes' 			,def_tablespace_name from dba_part_indexes where 1=1 and OWNER= '&owner'          		union all
   select 'dba_tab_partitions' 			,tablespace_name from dba_tab_partitions  where 1=1 and TABLE_OWNER= '&owner'          	union all
   select 'dba_ind_partitions' 			,tablespace_name from dba_ind_partitions  where 1=1 and index_OWNER= '&owner'          	union all
   select 'dba_tab_subpartitions' 		,tablespace_name from dba_tab_subpartitions where 1=1 and TABLE_OWNER= '&owner'        	union all
   select 'dba_ind_subpartitions' 		,tablespace_name from dba_ind_subpartitions  where 1=1 and index_OWNER= '&owner'       	union all
   select 'dba_part_lobs'			,def_tablespace_name from dba_part_lobs where 1=1 and TABLE_OWNER= '&owner'            	union all
   select 'dba_lob_partitions' 			,tablespace_name from dba_lob_partitions   where 1=1 and TABLE_OWNER= '&owner'         	union all
   select 'dba_lob_subpartitions'		,tablespace_name from dba_lob_subpartitions  where 1=1 and TABLE_OWNER= '&owner'       	union all
   select 'dba_subpartition_templates' 	        ,tablespace_name from dba_subpartition_templates where 1=1 and USER_NAME= '&owner'     	union all
   select 'dba_lob_templates' 			,tablespace_name from dba_lob_templates  where 1=1 and USER_NAME= '&owner'            	union all
   select 'dba_segments ' 			,tablespace_name from dba_segments     where 1=1 and OWNER= '&owner'              		union all
   select 'dba_extents' 			,tablespace_name from dba_extents   where 1=1 and OWNER= '&owner'                 		union all
   select 'dba_undo_extents' 			,tablespace_name from dba_undo_extents where 1=1 and OWNER= '&owner'
   )
   select distinct * from all_possible_ts

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