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
/

Oracle Top 10 events from latest AWR snapshot

from https://blog.dbi-services.com/show-the-top-10-events-from-latest-awr-snapshot/

check this as well 
http://anuj-singh.blogspot.com/2013/11/top-sql-from-between-snapid.html

define 1='Top Timed Events'
define 2=''


with snap as (
  select * from (
    select dbid,lead(snap_id)over(partition by instance_number order by end_interval_time desc) bid,snap_id eid,row_number() over(order by end_interval_time desc) n
    from dba_hist_snapshot where dbid=(select dbid from v$database)
  ) where n=1
),
awr as (
        select rownum line,output
        from table(
                dbms_workload_repository.awr_report_text(l_dbid=>(select dbid from snap),l_inst_num=>(select instance_number from v$instance),l_bid=>(select bid from snap),l_eid=>(select eid from snap),l_options=>1+4+8)
        )
),
awr_sections as (
        select
         last_value(case when regexp_replace(output,' *DB/Inst.*$') in (''
        ,'Database Summary'
        ,'Database Instances Included In Report'
        ,'Top Event P1/P2/P3 Values'
        ,'Top SQL with Top Events'
        ,'Top SQL with Top Row Sources'
        ,'Top Sessions'
        ,'Top Blocking Sessions'
        ,'Top PL/SQL Procedures'
        ,'Top Events'
        ,'Top DB Objects'
        ,'Activity Over Time'
        ,'Wait Event Histogram Detail (64 msec to 2 sec)'
        ,'Wait Event Histogram Detail (4 sec to 2 min)'
        ,'Wait Event Histogram Detail (4 min to 1 hr)'
        ,'SQL ordered by Elapsed Time'
        ,'SQL ordered by CPU Time'
        ,'SQL ordered by User I/O Wait Time'
        ,'SQL ordered by Gets'
        ,'SQL ordered by Reads'
        ,'SQL ordered by Physical Reads (UnOptimized)'
        ,'SQL ordered by Optimized Reads'
        ,'SQL ordered by Executions'
        ,'SQL ordered by Parse Calls'
        ,'SQL ordered by Sharable Memory'
        ,'SQL ordered by Version Count'
        ,'SQL ordered by Cluster Wait Time'
        ,'Key Instance Activity Stats'
        ,'Instance Activity Stats'
        ,'IOStat by Function summary'
        ,'IOStat by Filetype summary'
        ,'IOStat by Function/Filetype summary'
        ,'Tablespace IO Stats'
        ,'File IO Stats'
        ,'Checkpoint Activity'
        ,'MTTR Advisory'
        ,'Segments by Logical Reads'
        ,'Segments by Physical Reads'
        ,'Segments by Direct Physical Reads'
        ,'Segments by Physical Read Requests'
        ,'Segments by UnOptimized Reads'
        ,'Segments by Optimized Reads'
        ,'Segments by Physical Write Requests'
        ,'Segments by Physical Writes'
        ,'Segments by Direct Physical Writes'
        ,'Segments by DB Blocks Changes'
       ,'Segments by Table Scans'
        ,'Segments by Row Lock Waits'
        ,'Segments by ITL Waits'
        ,'Segments by Buffer Busy Waits'
        ,'Segments by Global Cache Buffer Busy'
        ,'Segments by CR Blocks Received'
        ,'Segments by Current Blocks Received'
        ,'In-Memory Segments by Scans'
        ,'In-Memory Segments by DB Block Changes'
        ,'In-Memory Segments by Populate CUs'
        ,'In-Memory Segments by Repopulate CUs'
        ,'Interconnect Device Statistics'
        ,'Dynamic Remastering Stats'
        ,'Resource Manager Plan Statistics'
        ,'Resource Manager Consumer Group Statistics'
        ,'Replication System Resource Usage'
        ,'Replication SGA Usage'
        ,'GoldenGate Capture'
        ,'GoldenGate Capture Rate'
        ,'GoldenGate Apply Reader'
        ,'GoldenGate Apply Coordinator'
        ,'GoldenGate Apply Server'
        ,'GoldenGate Apply Coordinator Rate'
        ,'GoldenGate Apply Reader and Server Rate'
        ,'XStream Capture'
        ,'XStream Capture Rate'
        ,'XStream Apply Reader'
        ,'XStream Apply Coordinator'
        ,'XStream Apply Server'
        ,'XStream Apply Coordinator Rate'
        ,'XStream Apply Reader and Server Rate'
        ,'Table Statistics by DML Operations'
        ,'Table Statistics by Conflict Resolutions'
        ,'Replication Large Transaction Statistics'
        ,'Replication Long Running Transaction Statistics'
        ,'Streams Capture'
        ,'Streams Capture Rate'
        ,'Streams Apply'
        ,'Streams Apply Rate'
        ,'Buffered Queues'
        ,'Buffered Queue Subscribers'
        ,'Persistent Queues'
        ,'Persistent Queues Rate'
        ,'Persistent Queue Subscribers'
        ,'Rule Set'
        ,'Shared Servers Activity'
        ,'Shared Servers Rates'
        ,'Shared Servers Utilization'
        ,'Shared Servers Common Queue'
        ,'Shared Servers Dispatchers'
        ,'init.ora Parameters'
        ,'init.ora Multi-Valued Parameters'
        ,'Cluster Interconnect'
        ,'Wait Classes by Total Wait Time'
        ,'Top 10 Foreground Events by Total Wait Time'
        ,'Top ADDM Findings by Average Active Sessions'
        ,'Cache Sizes'
        ,'Host Configuration Comparison'
        ,'Top Timed Events'
        ,'Top SQL Comparison by Elapsed Time'
        ,'Top SQL Comparison by I/O Time'
        ,'Top SQL Comparison by CPU Time'
        ,'Top SQL Comparison by Buffer Gets'
        ,'Top SQL Comparison by Physical Reads'
        ,'Top SQL Comparison by UnOptimized Read Requests'
        ,'Top SQL Comparison by Optimized Reads'
        ,'Top SQL Comparison by Executions'
        ,'Top SQL Comparison by Parse Calls'
        ,'Top SQL Comparison by Cluster Wait Time'
        ,'Top SQL Comparison by Sharable Memory'
        ,'Top SQL Comparison by Version Count'
        ,'Top Segments Comparison by Logical Reads'
        ,'Top Segments Comparison by Physical Reads'
        ,'Top Segments Comparison by Direct Physical Reads'
        ,'Top Segments Comparison by Physical Read Requests'
        ,'Top Segments Comparison by Optimized Read Requests'
        ,'Top Segments Comparison by Physical Write Requests'
        ,'Top Segments Comparison by Physical Writes'
        ,'Top Segments Comparison by Table Scans'
        ,'Top Segments Comparison by DB Block Changes'
        ,'Top Segments by Buffer Busy Waits'
        ,'Top Segments by Row Lock Waits'
        ,'Top Segments by ITL Waits'
        ,'Top Segments by CR Blocks Received'
        ,'Top Segments by Current Blocks Received'
        ,'Top Segments by GC Buffer Busy Waits'
        ,'Top In-Memory Segments Comparison by Scans'
        ,'Top In-Memory Segments Comparison by DB Block Changes'
        ,'Top In-Memory Segments Comparison by Populate CUs'
        ,'Top In-Memory Segments Comparison by Repopulate CUs'
        ,'Service Statistics'
        ,'Service Statistics (RAC)'
        ,'Global Messaging Statistics'
        ,'Global CR Served Stats'
        ,'Global CURRENT Served Stats'
        ,'Replication System Resource Usage'
        ,'Replication SGA Usage'
        ,'Streams by CPU Time'
        ,'GoldenGate Capture'
        ,'GoldenGate Capture Rate'
        ,'GoldenGate Apply Coordinator'
        ,'GoldenGate Apply Reader'
        ,'GoldenGate Apply Server'
        ,'GoldenGate Apply Coordinator Rate'
        ,'GoldenGate Apply Reader and Server Rate'
        ,'XStream Capture'
        ,'XStream Capture Rate'
        ,'XStream Apply Coordinator'
        ,'XStream Apply Reader'
        ,'XStream Apply Server'
        ,'XStream Apply Coordinator Rate'
        ,'XStream Apply Reader and Server Rate'
        ,'Table Statistics by DML Operations'
        ,'Table Statistics by Conflict Resolutions'
        ,'Replication Large Transaction Statistics'
        ,'Replication Long Running Transaction Statistics'
        ,'Streams by IO Time'
        ,'Streams Capture'
        ,'Streams Capture Rate'
        ,'Streams Apply'
        ,'Streams Apply Rate'
        ,'Buffered Queues'
        ,'Rule Set by Evaluations'
        ,'Rule Set by Elapsed Time'
        ,'Persistent Queues'
        ,'Persistent Queues Rate'
        ,'IOStat by Function - Data Rate per Second'
        ,'IOStat by Function - Requests per Second'
        ,'IOStat by File Type - Data Rate per Second'
        ,'IOStat by File Type - Requests per Second'
        ,'Tablespace IO Stats'
        ,'Top File Comparison by IO'
        ,'Top File Comparison by Read Time'
        ,'Top File Comparison by Buffer Waits'
        ,'Key Instance Activity Stats'
        ,'Other Instance Activity Stats'
        ,'Enqueue Activity'
        ,'Buffer Wait Statistics'
        ,'Dynamic Remastering Stats'
        ,'Library Cache Activity'
        ,'Library Cache Activity (RAC)'
        ,'init.ora Parameters'
        ,'init.ora Multi-Valued Parameters'
        ,'Buffered Subscribers'
        ,'Persistent Queue Subscribers'
        ,'Shared Servers Activity'
        ,'Shared Servers Rates'
        ,'Shared Servers Utilization'
        ,'Shared Servers Common Queue'
        ,'Shared Servers Dispatchers'
        ,'Database Summary'
        ,'Database Instances Included In Report'
        ,'Top ADDM Findings by Average Active Sessions'
        ,'Cache Sizes'
        ,'OS Statistics By Instance'
        ,'Foreground Wait Classes -  % of Total DB time'
        ,'Foreground Wait Classes'
        ,'Foreground Wait Classes -  % of DB time '
        ,'Time Model'
        ,'Time Model - % of DB time'
        ,'System Statistics'
        ,'System Statistics - Per Second'
        ,'System Statistics - Per Transaction'
        ,'Global Cache Efficiency Percentages'
        ,'Global Cache and Enqueue Workload Characteristics'
        ,'Global Cache and Enqueue Messaging Statistics'
        ,'SysStat and Global Messaging  - RAC'
        ,'SysStat and  Global Messaging (per Sec)- RAC'
        ,'SysStat and Global Messaging (per Tx)- RAC'
        ,'CR Blocks Served Statistics'
        ,'Current Blocks Served Statistics'
        ,'Global Cache Transfer Stats'
        ,'Global Cache Transfer (Immediate)'
        ,'Cluster Interconnect'
        ,'Interconnect Client Statistics'
        ,'Interconnect Client Statistics (per Second)'
        ,'Interconnect Device Statistics'
        ,'Interconnect Device Statistics (per Second)'
        ,'Ping Statistics'
        ,'Top Timed Events'
        ,'Top Timed Foreground Events'
        ,'Top Timed Background Events'
        ,'Resource Manager Plan Statistics'
        ,'Resource Manager Consumer Group Statistics'
        ,'SQL ordered by Elapsed Time (Global)'
        ,'SQL ordered by CPU Time (Global)'
        ,'SQL ordered by User I/O Time (Global)'
        ,'SQL ordered by Gets (Global)'
        ,'SQL ordered by Reads (Global)'
        ,'SQL ordered by UnOptimized Read Requests (Global)'
        ,'SQL ordered by Optimized Reads (Global)'
        ,'SQL ordered by Cluster Wait Time (Global)'
        ,'SQL ordered by Executions (Global)'
        ,'IOStat by Function (per Second)'
        ,'IOStat by File Type (per Second)'
        ,'Segment Statistics (Global)'
        ,'Library Cache Activity'
        ,'System Statistics (Global)'
        ,'Global Messaging Statistics (Global)'
        ,'System Statistics (Absolute Values)'
        ,'PGA Aggregate Target Statistics'
        ,'Process Memory Summary'
        ,'init.ora Parameters'
        ,'init.ora Multi-valued Parameters'
        ,'Database Summary'
        ,'Database Instances Included In Report'
        ,'Time Model Statistics'
        ,'Operating System Statistics'
        ,'Host Utilization Percentages'
        ,'Global Cache Load Profile'
        ,'Wait Classes'
        ,'Wait Events'
        ,'Cache Sizes'
        ,'PGA Aggr Target Stats'
        ,'init.ora Parameters'
        ,'init.ora Multi-valued Parameters'
        ,'Global Cache Transfer Stats'
        ,' Exadata Storage Server Model'
        ,' Exadata Storage Server Version'
        ,' Exadata Storage Information'
        ,' Exadata Griddisks'
        ,' Exadata Celldisks'
        ,' ASM Diskgroups'
        ,' Exadata Non-Online Disks'
        ,' Exadata Alerts Summary'
        ,' Exadata Alerts Detail'
        ,'Exadata Statistics'
) then output end ) ignore nulls over(order by line) section    ,output         from awr
)
select output AWR_REPORT_TEXT from awr_sections 
 --where regexp_like(section,'&1') or regexp_like(output,'&2')
  where section like ('%Events%') 
--w where section like ('%Top 10 Foreground%')
-- w where section like ('%Top Event P1/P2/P3 Values%')
-- w where section like ('%Top SQL%')
/

 ---------------  Change where clause based on your requirement 




'Top SQL Comparison by Buffer Gets'
'SQL ordered by User I/O Wait Time'

AWR_REPORT_TEXT
--------------------------------------------------------------------------------
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Avg   % DB Wait
Event                                Waits Time (sec)      Wait   time Class
------------------------------ ----------- ---------- --------- ------ --------
DB CPU                                         2886.9             51.8
direct path read                   231,557      815.4    3.52ms   14.6 User I/O
db file sequential read            977,929        760  777.19us   13.6 User I/O
db file scattered read             634,019      585.2  923.01us   10.5 User I/O
log file sync                       82,557      138.1    1.67ms    2.5 Commit
gc current block busy              136,235      118.8  872.26us    2.1 Cluster

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

or

set linesize 200  pages 999
define p_inst =1
define p_days=3
column start_time format a17
column end_time format a17
select snap_id, s.instance_number, to_char(begin_interval_time,'DD-MM-YY HH24:MI') start_time, 
		   to_char(end_interval_time,'DD-MM-YY HH24:MI') end_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;



	
select 'Database Summary'					as section from dual union all
select 'Database Instances Included In Report'                  as section from dual union all
select 'Top Event P1/P2/P3 Values'                              as section from dual union all
select 'Top SQL with Top Events'                                as section from dual union all
select 'Top SQL with Top Row Sources'                           as section from dual union all
select 'Top Sessions'                                           as section from dual union all
select 'Top Blocking Sessions'                                  as section from dual union all
select 'Top PL/SQL Procedures'                                  as section from dual union all
select 'Top Events'                                             as section from dual union all
select 'Top DB Objects'                                         as section from dual union all
select 'Activity Over Time'                                     as section from dual union all
select 'Wait Event Histogram Detail (64 msec to 2 sec)'         as section from dual union all
select 'Wait Event Histogram Detail (4 sec to 2 min)'           as section from dual union all
select 'Wait Event Histogram Detail (4 min to 1 hr)'            as section from dual union all
select 'SQL ordered by Elapsed Time'                            as section from dual union all
select 'SQL ordered by CPU Time'                                as section from dual union all
select 'SQL ordered by User I/O Wait Time'                      as section from dual union all
select 'SQL ordered by Gets'                                    as section from dual union all
select 'SQL ordered by Reads'                                   as section from dual union all
select 'SQL ordered by Physical Reads (UnOptimized)'            as section from dual union all
select 'SQL ordered by Optimized Reads'                         as section from dual union all
select 'SQL ordered by Executions'                              as section from dual union all
select 'SQL ordered by Parse Calls'                             as section from dual union all
select 'SQL ordered by Sharable Memory'                         as section from dual union all
select 'SQL ordered by Version Count'                           as section from dual union all
select 'SQL ordered by Cluster Wait Time'                       as section from dual union all
select 'Key Instance Activity Stats'                            as section from dual union all
select 'Instance Activity Stats'                                as section from dual union all
select 'IOStat by Function summary'                             as section from dual union all
select 'IOStat by Filetype summary'                             as section from dual union all
select 'IOStat by Function/Filetype summary'                    as section from dual union all
select 'Tablespace IO Stats'                                    as section from dual union all
select 'File IO Stats'                                          as section from dual union all
select 'Checkpoint Activity'                                    as section from dual union all
select 'MTTR Advisory'                                          as section from dual union all
select 'Segments by Logical Reads'                              as section from dual union all
select 'Segments by Physical Reads'                             as section from dual union all
select 'Segments by Direct Physical Reads'                      as section from dual union all
select 'Segments by Physical Read Requests'                     as section from dual union all
select 'Segments by UnOptimized Reads'                          as section from dual union all
select 'Segments by Optimized Reads'                            as section from dual union all
select 'Segments by Physical Write Requests'                    as section from dual union all
select 'Segments by Physical Writes'                            as section from dual union all
select 'Segments by Direct Physical Writes'                     as section from dual union all
select 'Segments by DB Blocks Changes'                          as section from dual union all
select 'Segments by Table Scans'                                as section from dual union all
select 'Segments by Row Lock Waits'                             as section from dual union all
select 'Segments by ITL Waits'                                  as section from dual union all
select 'Segments by Buffer Busy Waits'                          as section from dual union all
select 'Segments by Global Cache Buffer Busy'                   as section from dual union all
select 'Segments by CR Blocks Received'                         as section from dual union all
select 'Segments by Current Blocks Received'                    as section from dual union all
select 'In-Memory Segments by Scans'                            as section from dual union all
select 'In-Memory Segments by DB Block Changes'                 as section from dual union all
select 'In-Memory Segments by Populate CUs'                     as section from dual union all
select 'In-Memory Segments by Repopulate CUs'                   as section from dual union all
select 'Interconnect Device Statistics'                         as section from dual union all
select 'Dynamic Remastering Stats'                              as section from dual union all
select 'Resource Manager Plan Statistics'                       as section from dual union all
select 'Resource Manager Consumer Group Statistics'             as section from dual union all
select 'Replication System Resource Usage'                      as section from dual union all
select 'Replication SGA Usage'                                  as section from dual union all
select 'GoldenGate Capture'                                     as section from dual union all
select 'GoldenGate Capture Rate'                                as section from dual union all
select 'GoldenGate Apply Reader'                                as section from dual union all
select 'GoldenGate Apply Coordinator'                           as section from dual union all
select 'GoldenGate Apply Server'                                as section from dual union all
select 'GoldenGate Apply Coordinator Rate'                      as section from dual union all
select 'GoldenGate Apply Reader and Server Rate'                as section from dual union all
select 'XStream Capture'                                        as section from dual union all
select 'XStream Capture Rate'                                   as section from dual union all
select 'XStream Apply Reader'                                   as section from dual union all
select 'XStream Apply Coordinator'                              as section from dual union all
select 'XStream Apply Server'                                   as section from dual union all
select 'XStream Apply Coordinator Rate'                         as section from dual union all
select 'XStream Apply Reader and Server Rate'                   as section from dual union all
select 'Table Statistics by DML Operations'                     as section from dual union all
select 'Table Statistics by Conflict Resolutions'               as section from dual union all
select 'Replication Large Transaction Statistics'               as section from dual union all
select 'Replication Long Running Transaction Statistics'        as section from dual union all
select 'Streams Capture'                                        as section from dual union all
select 'Streams Capture Rate'                                   as section from dual union all
select 'Streams Apply'                                          as section from dual union all
select 'Streams Apply Rate'                                     as section from dual union all
select 'Buffered Queues'                                        as section from dual union all
select 'Buffered Queue Subscribers'                             as section from dual union all
select 'Persistent Queues'                                      as section from dual union all
select 'Persistent Queues Rate'                                 as section from dual union all
select 'Persistent Queue Subscribers'                           as section from dual union all
select 'Rule Set'                                               as section from dual union all
select 'Shared Servers Activity'                                as section from dual union all
select 'Shared Servers Rates'                                   as section from dual union all
select 'Shared Servers Utilization'                             as section from dual union all
select 'Shared Servers Common Queue'                            as section from dual union all
select 'Shared Servers Dispatchers'                             as section from dual union all
select 'init.ora Parameters'                                    as section from dual union all
select 'init.ora Multi-Valued Parameters'                       as section from dual union all
select 'Cluster Interconnect'                                   as section from dual union all
select 'Wait Classes by Total Wait Time'                        as section from dual union all
select 'Top 10 Foreground Events by Total Wait Time'            as section from dual union all
select 'Top ADDM Findings by Average Active Sessions'           as section from dual union all
select 'Cache Sizes'                                            as section from dual union all
select 'Host Configuration Comparison'                          as section from dual union all
select 'Top Timed Events'                                       as section from dual union all
select 'Top SQL Comparison by Elapsed Time'                     as section from dual union all
select 'Top SQL Comparison by I/O Time'                         as section from dual union all
select 'Top SQL Comparison by CPU Time'                         as section from dual union all
select 'Top SQL Comparison by Buffer Gets'                      as section from dual union all
select 'Top SQL Comparison by Physical Reads'                   as section from dual union all
select 'Top SQL Comparison by UnOptimized Read Requests'        as section from dual union all
select 'Top SQL Comparison by Optimized Reads'                  as section from dual union all
select 'Top SQL Comparison by Executions'                       as section from dual union all
select 'Top SQL Comparison by Parse Calls'                      as section from dual union all
select 'Top SQL Comparison by Cluster Wait Time'                as section from dual union all
select 'Top SQL Comparison by Sharable Memory'                  as section from dual union all
select 'Top SQL Comparison by Version Count'                    as section from dual union all
select 'Top Segments Comparison by Logical Reads'               as section from dual union all
select 'Top Segments Comparison by Physical Reads'              as section from dual union all
select 'Top Segments Comparison by Direct Physical Reads'       as section from dual union all
select 'Top Segments Comparison by Physical Read Requests'      as section from dual union all
select 'Top Segments Comparison by Optimized Read Requests'     as section from dual union all
select 'Top Segments Comparison by Physical Write Requests'     as section from dual union all
select 'Top Segments Comparison by Physical Writes'             as section from dual union all
select 'Top Segments Comparison by Table Scans'                 as section from dual union all
select 'Top Segments Comparison by DB Block Changes'            as section from dual union all
select 'Top Segments by Buffer Busy Waits'                      as section from dual union all
select 'Top Segments by Row Lock Waits'                         as section from dual union all
select 'Top Segments by ITL Waits'                              as section from dual union all
select 'Top Segments by CR Blocks Received'                     as section from dual union all
select 'Top Segments by Current Blocks Received'                as section from dual union all
select 'Top Segments by GC Buffer Busy Waits'                   as section from dual union all
select 'Top In-Memory Segments Comparison by Scans'             as section from dual union all
select 'Top In-Memory Segments Comparison by DB Block Changes'  as section from dual union all
select 'Top In-Memory Segments Comparison by Populate CUs'      as section from dual union all
select 'Top In-Memory Segments Comparison by Repopulate CUs'    as section from dual union all
select 'Service Statistics'                                     as section from dual union all
select 'Service Statistics (RAC)'                               as section from dual union all
select 'Global Messaging Statistics'                            as section from dual union all
select 'Global CR Served Stats'                                 as section from dual union all
select 'Global CURRENT Served Stats'                            as section from dual union all
select 'Replication System Resource Usage'                      as section from dual union all
select 'Replication SGA Usage'                                  as section from dual union all
select 'Streams by CPU Time'                                    as section from dual union all
select 'GoldenGate Capture'                                     as section from dual union all
select 'GoldenGate Capture Rate'                                as section from dual union all
select 'GoldenGate Apply Coordinator'                           as section from dual union all
select 'GoldenGate Apply Reader'                                as section from dual union all
select 'GoldenGate Apply Server'                                as section from dual union all
select 'GoldenGate Apply Coordinator Rate'                      as section from dual union all
select 'GoldenGate Apply Reader and Server Rate'                as section from dual union all
select 'XStream Capture'                                        as section from dual union all
select 'XStream Capture Rate'                                   as section from dual union all
select 'XStream Apply Coordinator'                              as section from dual union all
select 'XStream Apply Reader'                                   as section from dual union all
select 'XStream Apply Server'                                   as section from dual union all
select 'XStream Apply Coordinator Rate'                         as section from dual union all
select 'XStream Apply Reader and Server Rate'                   as section from dual union all
select 'Table Statistics by DML Operations'                     as section from dual union all
select 'Table Statistics by Conflict Resolutions'               as section from dual union all
select 'Replication Large Transaction Statistics'               as section from dual union all
select 'Replication Long Running Transaction Statistics'        as section from dual union all
select 'Streams by IO Time'                                     as section from dual union all
select 'Streams Capture'                                        as section from dual union all
select 'Streams Capture Rate'                                   as section from dual union all
select 'Streams Apply'                                          as section from dual union all
select 'Streams Apply Rate'                                     as section from dual union all
select 'Buffered Queues'                                        as section from dual union all
select 'Rule Set by Evaluations'                                as section from dual union all
select 'Rule Set by Elapsed Time'                               as section from dual union all
select 'Persistent Queues'                                      as section from dual union all
select 'Persistent Queues Rate'                                 as section from dual union all
select 'IOStat by Function - Data Rate per Second'              as section from dual union all
select 'IOStat by Function - Requests per Second'               as section from dual union all
select 'IOStat by File Type - Data Rate per Second'             as section from dual union all
select 'IOStat by File Type - Requests per Second'              as section from dual union all
select 'Tablespace IO Stats'                                    as section from dual union all
select 'Top File Comparison by IO'                              as section from dual union all
select 'Top File Comparison by Read Time'                       as section from dual union all
select 'Top File Comparison by Buffer Waits'                    as section from dual union all
select 'Key Instance Activity Stats'                            as section from dual union all
select 'Other Instance Activity Stats'                          as section from dual union all
select 'Enqueue Activity'                                       as section from dual union all
select 'Buffer Wait Statistics'                                 as section from dual union all
select 'Dynamic Remastering Stats'                              as section from dual union all
select 'Library Cache Activity'                                 as section from dual union all
select 'Library Cache Activity (RAC)'                           as section from dual union all
select 'init.ora Parameters'                                    as section from dual union all
select 'init.ora Multi-Valued Parameters'                       as section from dual union all
select 'Buffered Subscribers'                                   as section from dual union all
select 'Persistent Queue Subscribers'                           as section from dual union all
select 'Shared Servers Activity'                                as section from dual union all
select 'Shared Servers Rates'                                   as section from dual union all
select 'Shared Servers Utilization'                             as section from dual union all
select 'Shared Servers Common Queue'                            as section from dual union all
select 'Shared Servers Dispatchers'                             as section from dual union all
select 'Database Summary'                                       as section from dual union all
select 'Database Instances Included In Report'                  as section from dual union all
select 'Top ADDM Findings by Average Active Sessions'           as section from dual union all
select 'Cache Sizes'                                            as section from dual union all
select 'OS Statistics By Instance'                              as section from dual union all
select 'Foreground Wait Classes -  % of Total DB time'          as section from dual union all
select 'Foreground Wait Classes'                                as section from dual union all
select 'Foreground Wait Classes -  % of DB time '               as section from dual union all
select 'Time Model'                                             as section from dual union all
select 'Time Model - % of DB time'                              as section from dual union all
select 'System Statistics'                                      as section from dual union all
select 'System Statistics - Per Second'                         as section from dual union all
select 'System Statistics - Per Transaction'                    as section from dual union all
select 'Global Cache Efficiency Percentages'                    as section from dual union all
select 'Global Cache and Enqueue Workload Characteristics'      as section from dual union all
select 'Global Cache and Enqueue Messaging Statistics'          as section from dual union all
select 'SysStat and Global Messaging  - RAC'                    as section from dual union all
select 'SysStat and  Global Messaging (per Sec)- RAC'           as section from dual union all
select 'SysStat and Global Messaging (per Tx)- RAC'             as section from dual union all
select 'CR Blocks Served Statistics'                            as section from dual union all
select 'Current Blocks Served Statistics'                       as section from dual union all
select 'Global Cache Transfer Stats'                            as section from dual union all
select 'Global Cache Transfer (Immediate)'                      as section from dual union all
select 'Cluster Interconnect'                                   as section from dual union all
select 'Interconnect Client Statistics'                         as section from dual union all
select 'Interconnect Client Statistics (per Second)'            as section from dual union all
select 'Interconnect Device Statistics'                         as section from dual union all
select 'Interconnect Device Statistics (per Second)'            as section from dual union all
select 'Ping Statistics'                                        as section from dual union all
select 'Top Timed Events'                                       as section from dual union all
select 'Top Timed Foreground Events'                            as section from dual union all
select 'Top Timed Background Events'                            as section from dual union all
select 'Resource Manager Plan Statistics'                       as section from dual union all
select 'Resource Manager Consumer Group Statistics'             as section from dual union all
select 'SQL ordered by Elapsed Time (Global)'                   as section from dual union all
select 'SQL ordered by CPU Time (Global)'                       as section from dual union all
select 'SQL ordered by User I/O Time (Global)'                  as section from dual union all
select 'SQL ordered by Gets (Global)'                           as section from dual union all
select 'SQL ordered by Reads (Global)'                          as section from dual union all
select 'SQL ordered by UnOptimized Read Requests (Global)'      as section from dual union all
select 'SQL ordered by Optimized Reads (Global)'                as section from dual union all
select 'SQL ordered by Cluster Wait Time (Global)'              as section from dual union all
select 'SQL ordered by Executions (Global)'                     as section from dual union all
select 'IOStat by Function (per Second)'                        as section from dual union all
select 'IOStat by File Type (per Second)'                       as section from dual union all
select 'Segment Statistics (Global)'                            as section from dual union all
select 'Library Cache Activity'                                 as section from dual union all
select 'System Statistics (Global)'                             as section from dual union all
select 'Global Messaging Statistics (Global)'                   as section from dual union all
select 'System Statistics (Absolute Values)'                    as section from dual union all
select 'PGA Aggregate Target Statistics'                        as section from dual union all
select 'Process Memory Summary'                                 as section from dual union all
select 'init.ora Parameters'                                    as section from dual union all
select 'init.ora Multi-valued Parameters'                       as section from dual union all
select 'Database Summary'                                       as section from dual union all
select 'Database Instances Included In Report'                  as section from dual union all
select 'Time Model Statistics'                                  as section from dual union all
select 'Operating System Statistics'                            as section from dual union all
select 'Host Utilization Percentages'                           as section from dual union all
select 'Global Cache Load Profile'                              as section from dual union all
select 'Wait Classes'                                           as section from dual union all
select 'Wait Events'                                            as section from dual union all
select 'Cache Sizes'                                            as section from dual union all
select 'PGA Aggr Target Stats'                                  as section from dual union all
select 'init.ora Parameters'                                    as section from dual union all
select 'init.ora Multi-valued Parameters'                       as section from dual union all
select 'Global Cache Transfer Stats'                            as section from dual union all
select ' Exadata Storage Server Model'                          as section from dual union all
select ' Exadata Storage Server Version'                        as section from dual union all
select ' Exadata Storage Information'                           as section from dual union all
select ' Exadata Griddisks'                                     as section from dual union all
select ' Exadata Celldisks'                                     as section from dual union all
select ' ASM Diskgroups'                                        as section from dual union all
select ' Exadata Non-Online Disks'                              as section from dual union all
select ' Exadata Alerts Summary'                                as section from dual union all
select ' Exadata Alerts Detail'                                 as section from dual union all
select 'Exadata Statistics'                                     as section from dual;


set linesize 500 pagesize 500
set linesize 700

VARIABLE dbid NUMBER
VARIABLE bid NUMBER
VARIABLE eid NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -1 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;
exec select DBID into :dbid from v$database;

define p_section='Top Sessions'
define p_inst=1

with snap as (
  select :bid bid, :eid eid from dual
),
awr as (
        select rownum line,output
        from table(
                dbms_workload_repository.awr_report_text(l_dbid=>(select dbid from v$database),l_inst_num=>&p_inst,l_bid=>(select bid from snap),l_eid=>(select eid from snap),l_options=>1+4+8)
        )
),
awr_sections as (
        select
         last_value(case when regexp_replace(output,' *DB/Inst.*$') in (''
        ,'Database Summary'
        ,'Database Instances Included In Report'
        ,'Top Event P1/P2/P3 Values'
        ,'Top SQL with Top Events'
        ,'Top SQL with Top Row Sources'
        ,'Top Sessions'
        ,'Top Blocking Sessions'
        ,'Top PL/SQL Procedures'
        ,'Top Events'
        ,'Top DB Objects'
        ,'Activity Over Time'
        ,'Wait Event Histogram Detail (64 msec to 2 sec)'
        ,'Wait Event Histogram Detail (4 sec to 2 min)'
        ,'Wait Event Histogram Detail (4 min to 1 hr)'
        ,'SQL ordered by Elapsed Time'
        ,'SQL ordered by CPU Time'
        ,'SQL ordered by User I/O Wait Time'
        ,'SQL ordered by Gets'
        ,'SQL ordered by Reads'
        ,'SQL ordered by Physical Reads (UnOptimized)'
        ,'SQL ordered by Optimized Reads'
        ,'SQL ordered by Executions'
        ,'SQL ordered by Parse Calls'
        ,'SQL ordered by Sharable Memory'
        ,'SQL ordered by Version Count'
        ,'SQL ordered by Cluster Wait Time'
        ,'Key Instance Activity Stats'
        ,'Instance Activity Stats'
        ,'IOStat by Function summary'
        ,'IOStat by Filetype summary'
        ,'IOStat by Function/Filetype summary'
        ,'Tablespace IO Stats'
        ,'File IO Stats'
        ,'Checkpoint Activity'
        ,'MTTR Advisory'
        ,'Segments by Logical Reads'
        ,'Segments by Physical Reads'
        ,'Segments by Direct Physical Reads'
        ,'Segments by Physical Read Requests'
        ,'Segments by UnOptimized Reads'
        ,'Segments by Optimized Reads'
        ,'Segments by Physical Write Requests'
        ,'Segments by Physical Writes'
        ,'Segments by Direct Physical Writes'
        ,'Segments by DB Blocks Changes'
       ,'Segments by Table Scans'
        ,'Segments by Row Lock Waits'
        ,'Segments by ITL Waits'
        ,'Segments by Buffer Busy Waits'
        ,'Segments by Global Cache Buffer Busy'
        ,'Segments by CR Blocks Received'
        ,'Segments by Current Blocks Received'
        ,'In-Memory Segments by Scans'
        ,'In-Memory Segments by DB Block Changes'
        ,'In-Memory Segments by Populate CUs'
        ,'In-Memory Segments by Repopulate CUs'
        ,'Interconnect Device Statistics'
        ,'Dynamic Remastering Stats'
        ,'Resource Manager Plan Statistics'
        ,'Resource Manager Consumer Group Statistics'
        ,'Replication System Resource Usage'
        ,'Replication SGA Usage'
        ,'GoldenGate Capture'
        ,'GoldenGate Capture Rate'
        ,'GoldenGate Apply Reader'
        ,'GoldenGate Apply Coordinator'
        ,'GoldenGate Apply Server'
        ,'GoldenGate Apply Coordinator Rate'
        ,'GoldenGate Apply Reader and Server Rate'
        ,'XStream Capture'
        ,'XStream Capture Rate'
        ,'XStream Apply Reader'
        ,'XStream Apply Coordinator'
        ,'XStream Apply Server'
        ,'XStream Apply Coordinator Rate'
        ,'XStream Apply Reader and Server Rate'
        ,'Table Statistics by DML Operations'
        ,'Table Statistics by Conflict Resolutions'
        ,'Replication Large Transaction Statistics'
        ,'Replication Long Running Transaction Statistics'
        ,'Streams Capture'
        ,'Streams Capture Rate'
        ,'Streams Apply'
        ,'Streams Apply Rate'
        ,'Buffered Queues'
        ,'Buffered Queue Subscribers'
        ,'Persistent Queues'
        ,'Persistent Queues Rate'
        ,'Persistent Queue Subscribers'
        ,'Rule Set'
        ,'Shared Servers Activity'
        ,'Shared Servers Rates'
        ,'Shared Servers Utilization'
        ,'Shared Servers Common Queue'
        ,'Shared Servers Dispatchers'
        ,'init.ora Parameters'
        ,'init.ora Multi-Valued Parameters'
        ,'Cluster Interconnect'
        ,'Wait Classes by Total Wait Time'
        ,'Top 10 Foreground Events by Total Wait Time'
        ,'Top ADDM Findings by Average Active Sessions'
        ,'Cache Sizes'
        ,'Host Configuration Comparison'
        ,'Top Timed Events'
        ,'Top SQL Comparison by Elapsed Time'
        ,'Top SQL Comparison by I/O Time'
        ,'Top SQL Comparison by CPU Time'
        ,'Top SQL Comparison by Buffer Gets'
        ,'Top SQL Comparison by Physical Reads'
        ,'Top SQL Comparison by UnOptimized Read Requests'
        ,'Top SQL Comparison by Optimized Reads'
        ,'Top SQL Comparison by Executions'
        ,'Top SQL Comparison by Parse Calls'
        ,'Top SQL Comparison by Cluster Wait Time'
        ,'Top SQL Comparison by Sharable Memory'
        ,'Top SQL Comparison by Version Count'
        ,'Top Segments Comparison by Logical Reads'
        ,'Top Segments Comparison by Physical Reads'
        ,'Top Segments Comparison by Direct Physical Reads'
        ,'Top Segments Comparison by Physical Read Requests'
        ,'Top Segments Comparison by Optimized Read Requests'
        ,'Top Segments Comparison by Physical Write Requests'
        ,'Top Segments Comparison by Physical Writes'
        ,'Top Segments Comparison by Table Scans'
        ,'Top Segments Comparison by DB Block Changes'
        ,'Top Segments by Buffer Busy Waits'
        ,'Top Segments by Row Lock Waits'
        ,'Top Segments by ITL Waits'
        ,'Top Segments by CR Blocks Received'
        ,'Top Segments by Current Blocks Received'
        ,'Top Segments by GC Buffer Busy Waits'
        ,'Top In-Memory Segments Comparison by Scans'
        ,'Top In-Memory Segments Comparison by DB Block Changes'
        ,'Top In-Memory Segments Comparison by Populate CUs'
        ,'Top In-Memory Segments Comparison by Repopulate CUs'
        ,'Service Statistics'
        ,'Service Statistics (RAC)'
        ,'Global Messaging Statistics'
        ,'Global CR Served Stats'
        ,'Global CURRENT Served Stats'
        ,'Replication System Resource Usage'
        ,'Replication SGA Usage'
        ,'Streams by CPU Time'
        ,'GoldenGate Capture'
        ,'GoldenGate Capture Rate'
        ,'GoldenGate Apply Coordinator'
        ,'GoldenGate Apply Reader'
        ,'GoldenGate Apply Server'
        ,'GoldenGate Apply Coordinator Rate'
        ,'GoldenGate Apply Reader and Server Rate'
        ,'XStream Capture'
        ,'XStream Capture Rate'
        ,'XStream Apply Coordinator'
        ,'XStream Apply Reader'
        ,'XStream Apply Server'
        ,'XStream Apply Coordinator Rate'
        ,'XStream Apply Reader and Server Rate'
        ,'Table Statistics by DML Operations'
        ,'Table Statistics by Conflict Resolutions'
        ,'Replication Large Transaction Statistics'
        ,'Replication Long Running Transaction Statistics'
        ,'Streams by IO Time'
        ,'Streams Capture'
        ,'Streams Capture Rate'
        ,'Streams Apply'
        ,'Streams Apply Rate'
        ,'Buffered Queues'
        ,'Rule Set by Evaluations'
        ,'Rule Set by Elapsed Time'
        ,'Persistent Queues'
        ,'Persistent Queues Rate'
        ,'IOStat by Function - Data Rate per Second'
        ,'IOStat by Function - Requests per Second'
        ,'IOStat by File Type - Data Rate per Second'
        ,'IOStat by File Type - Requests per Second'
        ,'Tablespace IO Stats'
        ,'Top File Comparison by IO'
        ,'Top File Comparison by Read Time'
        ,'Top File Comparison by Buffer Waits'
        ,'Key Instance Activity Stats'
        ,'Other Instance Activity Stats'
        ,'Enqueue Activity'
        ,'Buffer Wait Statistics'
        ,'Dynamic Remastering Stats'
        ,'Library Cache Activity'
        ,'Library Cache Activity (RAC)'
        ,'init.ora Parameters'
        ,'init.ora Multi-Valued Parameters'
        ,'Buffered Subscribers'
        ,'Persistent Queue Subscribers'
        ,'Shared Servers Activity'
        ,'Shared Servers Rates'
        ,'Shared Servers Utilization'
        ,'Shared Servers Common Queue'
        ,'Shared Servers Dispatchers'
        ,'Database Summary'
        ,'Database Instances Included In Report'
        ,'Top ADDM Findings by Average Active Sessions'
        ,'Cache Sizes'
        ,'OS Statistics By Instance'
        ,'Foreground Wait Classes -  % of Total DB time'
        ,'Foreground Wait Classes'
        ,'Foreground Wait Classes -  % of DB time '
        ,'Time Model'
        ,'Time Model - % of DB time'
        ,'System Statistics'
        ,'System Statistics - Per Second'
        ,'System Statistics - Per Transaction'
        ,'Global Cache Efficiency Percentages'
        ,'Global Cache and Enqueue Workload Characteristics'
        ,'Global Cache and Enqueue Messaging Statistics'
        ,'SysStat and Global Messaging  - RAC'
        ,'SysStat and  Global Messaging (per Sec)- RAC'
        ,'SysStat and Global Messaging (per Tx)- RAC'
        ,'CR Blocks Served Statistics'
        ,'Current Blocks Served Statistics'
        ,'Global Cache Transfer Stats'
        ,'Global Cache Transfer (Immediate)'
        ,'Cluster Interconnect'
        ,'Interconnect Client Statistics'
        ,'Interconnect Client Statistics (per Second)'
        ,'Interconnect Device Statistics'
        ,'Interconnect Device Statistics (per Second)'
        ,'Ping Statistics'
        ,'Top Timed Events'
        ,'Top Timed Foreground Events'
        ,'Top Timed Background Events'
        ,'Resource Manager Plan Statistics'
        ,'Resource Manager Consumer Group Statistics'
        ,'SQL ordered by Elapsed Time (Global)'
        ,'SQL ordered by CPU Time (Global)'
        ,'SQL ordered by User I/O Time (Global)'
        ,'SQL ordered by Gets (Global)'
        ,'SQL ordered by Reads (Global)'
        ,'SQL ordered by UnOptimized Read Requests (Global)'
        ,'SQL ordered by Optimized Reads (Global)'
        ,'SQL ordered by Cluster Wait Time (Global)'
        ,'SQL ordered by Executions (Global)'
        ,'IOStat by Function (per Second)'
        ,'IOStat by File Type (per Second)'
        ,'Segment Statistics (Global)'
        ,'Library Cache Activity'
        ,'System Statistics (Global)'
        ,'Global Messaging Statistics (Global)'
        ,'System Statistics (Absolute Values)'
        ,'PGA Aggregate Target Statistics'
        ,'Process Memory Summary'
        ,'init.ora Parameters'
        ,'init.ora Multi-valued Parameters'
        ,'Database Summary'
        ,'Database Instances Included In Report'
        ,'Time Model Statistics'
        ,'Operating System Statistics'
        ,'Host Utilization Percentages'
        ,'Global Cache Load Profile'
        ,'Wait Classes'
        ,'Wait Events'
        ,'Cache Sizes'
        ,'PGA Aggr Target Stats'
        ,'init.ora Parameters'
        ,'init.ora Multi-valued Parameters'
        ,'Global Cache Transfer Stats'
        ,' Exadata Storage Server Model'
        ,' Exadata Storage Server Version'
        ,' Exadata Storage Information'
        ,' Exadata Griddisks'
        ,' Exadata Celldisks'
        ,' ASM Diskgroups'
        ,' Exadata Non-Online Disks'
        ,' Exadata Alerts Summary'
        ,' Exadata Alerts Detail'
        ,'Exadata Statistics'
) then output end ) ignore nulls over(order by line) section
        ,output
        from awr
)
select output AWR_REPORT_TEXT from awr_sections where regexp_like(section,'&p_section') or regexp_like(output,'')
/

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

 


top event 




VARIABLE dbid NUMBER
VARIABLE bid NUMBER
VARIABLE eid NUMBER

exec select max(snap_id) -1 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;
exec select DBID into :dbid from v$database;



col  EVENT_NAME for a25
col WAIT_CLASS for a15


SELECT snap_id,
  wait_class,
  event_name,
  pctdbt,
  total_time_s
FROM
  (SELECT a.snap_id,
    wait_class,
    event_name,
    b.dbt,
    ROUND(SUM(a.ttm) /b.dbt*100,2) pctdbt,
    SUM(a.ttm) total_time_s,
    dense_rank() over (partition BY a.snap_id order by SUM(a.ttm)/b.dbt*100 DESC nulls last) rnk
  FROM
    (SELECT snap_id,
      wait_class,
      event_name,
      ttm
    FROM
      (SELECT
        /*+ qb_name(systemevents) */
        (CAST (s.end_interval_time AS DATE) - CAST (s.begin_interval_time AS DATE)) * 24 * 3600 ela,
        s.snap_id,
        wait_class,
        e.event_name,
        CASE
          WHEN s.begin_interval_time = s.startup_time
          THEN e.time_waited_micro
          ELSE e.time_waited_micro - lag (e.time_waited_micro ) over (partition BY e.instance_number,e.event_name order by e.snap_id)
        END ttm
      FROM dba_hist_snapshot s,
        dba_hist_system_event e
      WHERE s.dbid          = e.dbid
      AND s.dbid            = :dbid
      AND s.instance_number = e.instance_number
      AND s.snap_id         = e.snap_id
      AND s.snap_id BETWEEN :bid AND :eid
      AND e.wait_class != 'Idle'
      UNION ALL
      SELECT
        /*+ qb_name(dbcpu) */
        (CAST (s.end_interval_time AS DATE) - CAST (s.begin_interval_time AS DATE)) * 24 * 3600 ela,
        s.snap_id,
        t.stat_name wait_class,
        t.stat_name event_name,
        CASE
          WHEN s.begin_interval_time = s.startup_time
          THEN t.value
          ELSE t.value - lag (t.value ) over (partition BY s.instance_number order by s.snap_id)
        END ttm
      FROM dba_hist_snapshot s,
        dba_hist_sys_time_model t
      WHERE s.dbid          = t.dbid
      AND s.dbid            = :dbid
      AND s.instance_number = t.instance_number
      AND s.snap_id         = t.snap_id
     AND s.snap_id BETWEEN :bid AND :eid
      AND t.stat_name = 'DB CPU'
      )
    ) a,
    (SELECT snap_id,
      SUM(dbt) dbt
    FROM
      (SELECT
        /*+ qb_name(dbtime) */
        s.snap_id,
        t.instance_number,
        t.stat_name nm,
        CASE
          WHEN s.begin_interval_time = s.startup_time
          THEN t.value
          ELSE t.value - lag (t.value ) over (partition BY s.instance_number order by s.snap_id)
        END dbt
      FROM dba_hist_snapshot s,
        dba_hist_sys_time_model t
      WHERE s.dbid          = t.dbid
      AND s.dbid            = :dbid
      AND s.instance_number = t.instance_number
      AND s.snap_id         = t.snap_id
      AND s.snap_id BETWEEN :bid AND :eid
      AND t.stat_name = 'DB time'
      ORDER BY s.snap_id,
        s.instance_number
      )
    GROUP BY snap_id
    HAVING SUM(dbt) > 0
    ) b
  WHERE a.snap_id = b.snap_id
  GROUP BY a.snap_id,
    a.wait_class,
    a.event_name,
    b.dbt
  )
WHERE pctdbt > 0
AND rnk     <= 10
ORDER BY snap_id,
  pctdbt DESC; 



       
                                SNAP_ID WAIT_CLASS      EVENT_NAME                                                 PCTDBT                            TOTAL_TIME_S
--------------------------------------- --------------- ------------------------- --------------------------------------- ---------------------------------------
                               39824.00 User I/O        db file sequential read                                     20.52                           2202438865.00
                               39824.00 DB CPU          DB CPU                                                      20.40                           2188755269.00
                               39824.00 Cluster         gc buffer busy acquire                                      19.70                           2113555251.00
                               39824.00 User I/O        read by other session                                        9.07                            973330492.00
                               39824.00 Cluster         gc cr disk read                                              6.85                            735609865.00

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

VARIABLE DAYS_HISTORY NUMBER
VARIABLE INTERVAL_HOURS NUMBER



 begin :DAYS_HISTORY := 1; end;
/

begin :INTERVAL_HOURS := 1; end;
/



SELECT *
FROM  
(select to_char(trunc(sysdate-:DAYS_HISTORY+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-:DAYS_HISTORY+1)))*24/(:INTERVAL_HOURS))*(:INTERVAL_HOURS)/24,'dd.mm.yyyy hh24:mi:ss') time,
    sum(hss.executions_delta) executions,
    round(sum(hss.cpu_time_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) cpu_time,
    round(sum(hss.iowait_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) io_time,
    round(sum(hss.clwait_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) cl_time,
    round(sum(hss.apwait_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) ap_time,
    round(sum(hss.ccwait_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) cc_time
from dba_hist_sqlstat hss, dba_hist_snapshot hs
where 1=1
--and ss.sql_id=:SQL_ID
    and hss.snap_id=hs.snap_id
    and hss.instance_number=hs.instance_number
    and hs.begin_interval_time>=trunc(sysdate)-:DAYS_HISTORY+1
group by hss.instance_number, trunc(sysdate-:DAYS_HISTORY+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-:DAYS_HISTORY+1)))*24/(:INTERVAL_HOURS))*(:INTERVAL_HOURS)/24
order by trunc(sysdate-:DAYS_HISTORY+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-:DAYS_HISTORY+1)))*24/(:INTERVAL_HOURS))*(:INTERVAL_HOURS)/24
)
UNPIVOT (Time_S FOR STATE_TIME IN (cpu_time AS 'CPU_TIME', executions AS 'EXECS',io_time AS 'IO_TIME', cl_time AS 'CL_TIME', ap_time AS 'AP_TIME', cc_time as 'CC_TIME'))

  
How to Check Tablespace Creation Time in Oracle


col FILE_NAME for a45
col tablespace_name for a25
col creation_time for a15
col last_datafile_creation_time for a25
select  a.tablespace_name, min(b.creation_time) creation_time , max(b.creation_time) last_datafile_creation_time,count(FILE#) total_files from dba_data_files a, v$datafile b 
where a.file_name=b.name
group by a.tablespace_name;