Oracle active session report ( ASH report )  .. 

1 Hr Interval report 
SELECT TO_CHAR(SYSDATE-1/24, 'DD-MM-YYYY HH24:MI') bdate , TO_CHAR(SYSDATE , 'DD-MM-YYYY HH24:MI') edate FROM dual;

BDATE EDATE
---------------- ----------------
25-08-2017 09:34 25-08-2017 10:34



vi ash.sql 
---========
VAR dbid NUMBER
VAR inst_id NUMBER

COL bdate NEW_VALUE def_bdate
COL edate NEW_VALUE def_edate

SET TERMOUT OFF

SELECT TO_CHAR(SYSDATE-1/24, 'DD-MM-YYYY HH24:MI') bdate , TO_CHAR(SYSDATE , 'DD-MM-YYYY HH24:MI') edate FROM dual
/

SET TERMOUT ON

ACCEPT bdate DATE FORMAT 'DD-MM-YYYY HH24:MI' DEFAULT '&def_bdate' PROMPT "Enter begin time [&def_bdate]: " 
ACCEPT edate DATE FORMAT 'DD-MM-YYYY HH24:MI' DEFAULT '&def_edate' PROMPT "Enter end time [&def_edate]: " 


BEGIN
SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database WHERE inst_id = SYS_CONTEXT('USERENV', 'INSTANCE');
END;
/


SPOOL ash_report.txt
SET TERMOUT OFF PAGESIZE 0 HEADING OFF LINESIZE 1000 TRIMSPOOL ON TRIMOUT ON TAB OFF

--SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(:dbid, :inst_id, TO_DATE('&bdate', 'DD-MM-YYYY HH24:MI'), TO_DATE('&edate', 'DD-MM-YYYY HH24:MI'), null, null, null, null ));

SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:dbid, :inst_id, TO_DATE('&bdate', 'DD-MM-YYYY HH24:MI'), TO_DATE('&edate', 'DD-MM-YYYY HH24:MI'), null, null, null, null ));

SPOOL OFF
SET TERMOUT ON PAGESIZE 1000 HEADING ON
PROMPT Done.


----========= End

SYS@rac1> @ash

Enter begin time [25-08-2017 09:28]:
Enter end time [25-08-2017 10:28]:

PL/SQL procedure successfully completed.


SYS@rac1> SYS@icrac1> !ls -ltr
total 284
-rw-r--r-- 1 oracle oinstall 15323 Aug 25 10:28 ash_report.txt


!cat ash_report.txt
cat: !cat: No such file or directory
old 1: SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:dbid, :inst_id, TO_DATE('&bdate', 'DD-MM-YYYY HH24:MI'), TO_DATE('&edate', 'DD-MM-YYYY HH24:MI'), null, null, null, null ))
new 1: SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:dbid, :inst_id, TO_DATE('25-08-2017 09:28', 'DD-MM-YYYY HH24:MI'), TO_DATE('25-08-2017 10:28', 'DD-MM-YYYY HH24:MI'), null, null, null, null ))
ASH Report For 

==========

define report_type = 'text'
define begin_time = '-5'
define duration = ''
define report_name = 'ashrpt.text'


set echo off heading on underline on;
column inst_num  heading "Inst Num"  new_value inst_num  format 99999
column inst_name heading "Instance"  new_value inst_name format a12
column db_name   heading "DB Name"   new_value db_name   format a12
column dbid      heading "DB Id"     new_value dbid      format 9999999999 just c


prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~


select d.dbid            dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
  from v$database d,  v$instance i
  ;

@?/rdbms/admin/ashrpt

undefine  report_type
undefine  begin_time 
undefine  duration 
undefine  report_name 

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

VAR dbid NUMBER
VAR inst_id NUMBER

BEGIN
SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database WHERE inst_id = SYS_CONTEXT('USERENV', 'INSTANCE');
END;
/

define bdate='28:01:2022 19:00'
define edate='28:01:2022 20:10'

SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:dbid, :inst_id, TO_DATE('&bdate', 'DD-MM-YYYY HH24:MI'), TO_DATE('&edate', 'DD-MM-YYYY HH24:MI'), null, null, null, null ));

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

--- rac

define slot_width = ''
define target_session_id = ''
define target_sql_id = ''
define target_wait_class = ''
define target_service_hash = ''
define target_module_name = ''
define target_action_name = ''
define target_client_id = ''
define target_plsql_entry = ''



define report_type = 'text'
define begin_time = '-5'
define duration = ''
define report_name = 'ashrpt.text'


set echo off heading on underline on;
column inst_num  heading "Inst Num"  new_value inst_num  format 99999
column inst_name heading "Instance"  new_value inst_name format a12
column db_name   heading "DB Name"   new_value db_name   format a12
column dbid      heading "DB Id"     new_value dbid      format 9999999999 just c


prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~


select d.dbid            dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
  from v$database d,  v$instance i
  ;

@?/rdbms/admin/ashrpti

undefine  report_type
undefine  begin_time 
undefine  duration 
undefine  report_name 

====

VAR dbid NUMBER
VAR inst_id NUMBER
BEGIN
SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database WHERE inst_id = SYS_CONTEXT('USERENV', 'INSTANCE');
END;
/

define FROM_TIMESTAMP='2023-06-01 14:00:00'
define TO_TIMESTAMP  ='2023-06-02 14:00:00'
define sql_id='gqkr2um43ga39'
SELECT * from table(dbms_workload_repository.ash_global_report_text
                       (  l_dbid         => :dbid
                        , l_inst_num     => :inst_id
                        , l_btime        => TO_DATE('&&FROM_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
                        , l_etime        => TO_DATE('&&TO_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
                        , l_sid          => NULL
                        , l_sql_id       => '&sql_id'
                        , l_wait_class   => NULL
                        , l_service_hash => NULL
                        , l_module       => NULL
                        , l_action       => NULL
                        , l_client_id    => NULL
                       )
                   );

VAR dbid NUMBER
VAR inst_id NUMBER

BEGIN
SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database WHERE inst_id = SYS_CONTEXT('USERENV', 'INSTANCE');
END;
/

define bdate='15:07:2023 10:00'
define edate='15:07:2023 11:10'

SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:dbid, :inst_id, TO_DATE('&bdate', 'DD-MM-YYYY HH24:MI'), TO_DATE('&edate', 'DD-MM-YYYY HH24:MI'), null, null, null, null ));







VAR dbid NUMBER
VAR inst_id NUMBER
BEGIN
SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database WHERE inst_id = SYS_CONTEXT('USERENV', 'INSTANCE');
END;
/

define FROM_TIMESTAMP='2023-07-15 10:00:00'
define TO_TIMESTAMP  ='2023-07-15 11:00:00'
define sql_id='dnm7fqpf08p8w'
SELECT * from table(dbms_workload_repository.ash_report_text
                       (  l_dbid         => :dbid
                        , l_inst_num     => :inst_id
                        , l_btime        => TO_DATE('&&FROM_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
                        , l_etime        => TO_DATE('&&TO_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
                        , l_sid          => NULL
                        , l_sql_id       => '&sql_id'
                        , l_wait_class   => NULL
                        , l_service_hash => NULL
                        , l_module       => NULL
                        , l_action       => NULL
                        , l_client_id    => NULL
                       )
                   );

with out sql !!!

VAR dbid NUMBER
VAR inst_id NUMBER
BEGIN
SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database WHERE inst_id = SYS_CONTEXT('USERENV', 'INSTANCE');
END;
/

define FROM_TIMESTAMP='2023-07-15 10:00:00'
define TO_TIMESTAMP  ='2023-07-15 11:00:00'
define sql_id='dnm7fqpf08p8w' ----<<<<<<
SELECT * from table(dbms_workload_repository.ash_report_text
                       (  l_dbid         => :dbid
                        , l_inst_num     => :inst_id
                        , l_btime        => TO_DATE('&&FROM_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
                        , l_etime        => TO_DATE('&&TO_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
                        , l_sid          => NULL
                        , l_sql_id       => NULL
                        , l_wait_class   => NULL
                        , l_service_hash => NULL
                        , l_module       => NULL
                        , l_action       => NULL
                        , l_client_id    => NULL
                       )
                   );


======

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

Awr report 

 set linesize 300 pagesize 300
 col command for a150
SELECT
    'spool awr_XYZ_inst_1_'
    || t.si
    || '_'
    || t.se
    || '.text '
    || CHR(10)
    || 'SELECT * FROM TABLE(dbms_workload_repository.awr_report_text('
    || t.dbid
    || ','
    || t.instance_number
    || ','
    || t.si
    || ','
    || t.se
    || '));'
    || CHR(10)
    || ' spool off;' command 
FROM
    (SELECT
            dbid,
            snap_id si,
            snap_id + 1 se,
            instance_number
     FROM   dba_hist_snapshot
     WHERE 1=1
    --        begin_interval_time >   TO_DATE('27102019 18:00:00', 'ddmmyyyy hh24:mi:ss')
   --   AND end_interval_time   <=  TO_DATE('27102019 19:30:00', 'ddmmyyyy hh24:mi:ss')
	  and begin_interval_time > sysdate -1
      AND  instance_number = 1
      ) t;

COMMAND
------------------------------------------------------------------------------------------------------------------------------------------------------
spool awr_XYZ_inst_1_33103_33104.text
SELECT * FROM TABLE(dbms_workload_repository.awr_report_text(1222414252,1,33103,33104));
 spool off;

spool awr_XYZ_inst_1_33104_33105.text
SELECT * FROM TABLE(dbms_workload_repository.awr_report_text(1222414252,1,33104,33105));
 spool off;

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

--- global Report

set head off  pages 0  lines 300 echo off  feedback off

VARIABLE BgnSnap NUMBER

VARIABLE EndSnap NUMBER

VARIABLE DID NUMBER

VARIABLE INST_NUMBER number

VARIABLE x VARCHAR2(30)

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 ;

SELECT output FROM TABLE (dbms_workload_repository.awr_global_report_text (:DID,'',:BgnSnap,:EndSnap,0 ) );

=====

define sql_id='8cnh50qfgwg73'

set linesize 300 pagesize 300
select * from table(dbms_workload_repository.awr_sql_report_text((select dbid from v$database), (select instance_number from v$instance), :BgnSnap,:EndSnap, '&sql_id'));

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

set linesize 300 pagesize 300
select * from table(dbms_workload_repository.awr_sql_report_text((select dbid from v$database), (select instance_number from gv$instance where instance_number=1), :BgnSnap,:EndSnap, '&sql_id'));


set linesize 300 pagesize 300
select * from table(dbms_workload_repository.awr_sql_report_text((select dbid from v$database), (select instance_number from gv$instance where instance_number=2), :BgnSnap,:EndSnap, '&sql_id'));

===

Global report !!!

set linesize 300

VARIABLE DID NUMBER

exec select DBID into :DID from v$database;

SELECT * FROM TABLE(dbms_workload_repository.ash_global_report_text(:DID, 1, SYSDATE-1/24, SYSDATE, l_wait_class=>'Other'));

SELECT * FROM TABLE(dbms_workload_repository.ash_global_report_text(:DID, 1, SYSDATE-1/24, SYSDATE));

SELECT * FROM TABLE(dbms_workload_repository.ash_global_report_text(:DID, '', SYSDATE-1/24, SYSDATE, l_wait_class=>'Other'));

set serveroutput on 

declare

  i_dbid number;

  i_inst_id NUMBER;

begin

  DBMS_OUTPUT.ENABLE (buffer_size => NULL); 

  select dbid into i_dbid from v$database;

  select instance_number into i_inst_id from v$instance;

  for cursor1 in 

  (

  select * from table( DBMS_WORKLOAD_REPOSITORY.ASH_GLOBAL_REPORT_text( l_dbid => i_dbid, l_inst_num => i_inst_id,   l_btime => SYSDATE-1/24,  l_etime =>  SYSDATE  ))

  )

    LOOP

    DBMS_OUTPUT.PUT_LINE( cursor1.output);

    END LOOP;

end;

/

====

DBMS_WORKLOAD_REPOSITORY.ASH_GLOBAL_REPORT_TEXT(

   l_dbid          IN VARCHAR2(1023),

   l_inst_num      IN NUMBER,

   l_btime         IN DATE,

   l_etime         IN DATE,

   l_options       IN NUMBER    DEFAULT 0,      -- Not currently used by Oracle

   l_slot_width    IN NUMBER    DEFAULT 0,      -- Not currently used by Oracle 

   l_sid           IN NUMBER    DEFAULT NULL,   -- v$session.sid

   l_sql_id        IN VARCHAR2  DEFAULT NULL,   -- V$SQL.SQL_ID                (Wildcard allowed)

   l_wait_class    IN VARCHAR2  DEFAULT NULL,   -- v$event_name.wait_class     (Wildcard allowed)

   l_service_hash  IN NUMBER    DEFAULT NULL,   -- v$active_services.name_hash

   l_module        IN VARCHAR2  DEFAULT NULL,   -- v$session.module            (Wildcard allowed)

   l_action        IN VARCHAR2  DEFAULT NULL,   -- v$session.action            (Wildcard allowed)

   l_client_id     IN VARCHAR2  DEFAULT NULL,   -- v$session.client_identifier (Wildcard allowed)

   l_plsql_entry   IN VARCHAR2  DEFAULT NULL,

   l_data_src      IN NUMBER    DEFAULT 0)

 RETURN awrrpt_text_type_table PIPELINED;

define FROM_TIMESTAMP='2020-04-28 15:00:00'

define TO_TIMESTAMP='2020-04-28 15:10:00'

SELECT * from table(dbms_workload_repository.ash_global_report_text

                       (  l_dbid         => (select dbid from v$database)

                        , l_inst_num     => DECODE(upper('&INSTANCE_LIST'),'%',NULL,'&INSTANCE_LIST')

                        , l_btime        => TO_DATE('&&FROM_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')

                        , l_etime        => TO_DATE('&&TO_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')

                        , l_sid          => NULL

                        , l_sql_id       => NULL

                        , l_wait_class   => NULL

                        , l_service_hash => NULL

                        , l_module       => NULL

                        , l_action       => NULL

                        , l_client_id    => NULL

                       )

                   );

/*

sysdate - interval '3' hour

sysdate - interval '5' MINUTE

*/

 SELECT * from table(dbms_workload_repository.ash_global_report_text

                       (  l_dbid         => (select dbid from v$database)

                        , l_inst_num     => ''

                        , l_btime        => (SYSDATE - interval '3' hour)

                        , l_etime        => (sysdate - interval '5' MINUTE)

                        , l_sid          => NULL

                        , l_sql_id       => NULL

                        , l_wait_class   => NULL

                        , l_service_hash => NULL

                        , l_module       => NULL

                        , l_action       => NULL

                        , l_client_id    => NULL

                       )

                   );

===

for sql !!

 SELECT * from table(dbms_workload_repository.ash_global_report_text

                       (  l_dbid         => (select dbid from v$database)

                        , l_inst_num     => ''

                        , l_btime        => (SYSDATE - interval '3' hour)

                        , l_etime        => (sysdate - interval '5' MINUTE)

                        , l_sid          => NULL

                        , l_sql_id       => '34cd4y8mbqvsk'

                        , l_wait_class   => NULL

                        , l_service_hash => NULL

                        , l_module       => NULL

                        , l_action       => NULL

                        , l_client_id    => NULL

                       )

                   );

====

Ash Top

set linesize 500 pagesize 300

COL "%This" FOR A7

--COL p1     FOR 99999999999999

--COL p2     FOR 99999999999999

--COL p3     FOR 99999999999999

COL p1text FOR A30 word_wrap

COL p2text FOR A30 word_wrap

COL p3text FOR A30 word_wrap

COL p1hex  FOR A17

COL p2hex  FOR A17

COL p3hex  FOR A17

COL AAS    FOR 9999.9

COL totalseconds HEAD "Total|Seconds" FOR 99999999

COL event  FOR A40 WORD_WRAP

SELECT * FROM (

    SELECT /*+ LEADING(a) USE_HASH(u) */

        COUNT(*)                                                     totalseconds

      , ROUND(COUNT(*) / ((CAST(sysdate AS DATE) - CAST(sysdate-1/24 AS DATE)) * 86400), 1) AAS

      , LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This"

      , username,sql_id

    , SUM(CASE WHEN wait_class IS NULL           THEN 1 ELSE 0 END) "CPU"

    , SUM(CASE WHEN wait_class ='User I/O'       THEN 1 ELSE 0 END) "User I/O"

    , SUM(CASE WHEN wait_class ='Application'    THEN 1 ELSE 0 END) "Application"

    , SUM(CASE WHEN wait_class ='Concurrency'    THEN 1 ELSE 0 END) "Concurrency"

    , SUM(CASE WHEN wait_class ='Commit'         THEN 1 ELSE 0 END) "Commit"

    , SUM(CASE WHEN wait_class ='Configuration'  THEN 1 ELSE 0 END) "Configuration"

    , SUM(CASE WHEN wait_class ='Idle'           THEN 1 ELSE 0 END) "Idle"

    , SUM(CASE WHEN wait_class ='Network'        THEN 1 ELSE 0 END) "Network"

    , SUM(CASE WHEN wait_class ='System I/O'     THEN 1 ELSE 0 END) "System I/O"

    , SUM(CASE WHEN wait_class ='Scheduler'      THEN 1 ELSE 0 END) "Scheduler"

    , SUM(CASE WHEN wait_class ='Administrative' THEN 1 ELSE 0 END) "Administrative"

    , SUM(CASE WHEN wait_class ='Cluster'        THEN 1 ELSE 0 END) "Cluster"

    , SUM(CASE WHEN wait_class ='Queueing'       THEN 1 ELSE 0 END) "Queueing"

    , SUM(CASE WHEN wait_class ='Other'          THEN 1 ELSE 0 END) "Other"

--      , TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen

--      , TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen

--      , MAX(sql_exec_id) - MIN(sql_exec_id)

--      , COUNT(DISTINCT sql_exec_start||':'||sql_exec_id)

    FROM

        (SELECT

             a.*

           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex

           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex

           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex

        FROM gv$active_session_history a) a

      , dba_users u

    WHERE

        a.user_id = u.user_id (+)

    AND session_type='FOREGROUND'

    AND sample_time BETWEEN sysdate-1/24 AND sysdate

    GROUP BY  username,sql_id

    ORDER BY  TotalSeconds DESC   , username

)

WHERE  ROWNUM <= 20

/

set linesize 300 pagesize 300

COL "%This" FOR A7

--COL p1     FOR 99999999999999

--COL p2     FOR 99999999999999

--COL p3     FOR 99999999999999

COL p1text      FOR A30 word_wrap

COL p2text      FOR A30 word_wrap

COL p3text      FOR A30 word_wrap

COL p1hex       FOR A17

COL p2hex       FOR A17

COL p3hex       FOR A17

COL AAS         FOR 9999.9

COL totalseconds HEAD "Total|Seconds" FOR 99999999

COL dist_sqlexec_seen HEAD "Distinct|Execs Seen"

COL event       FOR A42 WORD_WRAP

COL event2      FOR A46 WORD_WRAP

COL time_model_name FOR A50 WORD_WRAP

COL program2    FOR A40 TRUNCATE

COL username    FOR A20 wrap

COL obj         FOR A30

COL objt        FOR A50

COL sql_opname  FOR A20

COL top_level_call_name FOR A30

COL wait_class  FOR A15

SELECT * FROM (

    WITH bclass AS (SELECT class, ROWNUM r from v$waitstat)

    SELECT /*+ LEADING(a) USE_HASH(u) */

        10 * COUNT(*)                                                      "TotalSeconds"

      , ROUND(10 * COUNT(*) / ((CAST(sysdate AS DATE) - CAST(sysdate-2/24 AS DATE)) * 86400), 1) AAS

      , LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This"

      , username,sql_id

--      , 10 * SUM(CASE WHEN wait_class IS NULL           THEN 1 ELSE 0 END) "CPU"

--      , 10 * SUM(CASE WHEN wait_class ='User I/O'       THEN 1 ELSE 0 END) "User I/O"

--      , 10 * SUM(CASE WHEN wait_class ='Application'    THEN 1 ELSE 0 END) "Application"

--      , 10 * SUM(CASE WHEN wait_class ='Concurrency'    THEN 1 ELSE 0 END) "Concurrency"

--      , 10 * SUM(CASE WHEN wait_class ='Commit'         THEN 1 ELSE 0 END) "Commit"

--      , 10 * SUM(CASE WHEN wait_class ='Configuration'  THEN 1 ELSE 0 END) "Configuration"

--      , 10 * SUM(CASE WHEN wait_class ='Cluster'        THEN 1 ELSE 0 END) "Cluster"

--      , 10 * SUM(CASE WHEN wait_class ='Idle'           THEN 1 ELSE 0 END) "Idle"

--      , 10 * SUM(CASE WHEN wait_class ='Network'        THEN 1 ELSE 0 END) "Network"

--      , 10 * SUM(CASE WHEN wait_class ='System I/O'     THEN 1 ELSE 0 END) "System I/O"

--      , 10 * SUM(CASE WHEN wait_class ='Scheduler'      THEN 1 ELSE 0 END) "Scheduler"

--      , 10 * SUM(CASE WHEN wait_class ='Administrative' THEN 1 ELSE 0 END) "Administrative"

--      , 10 * SUM(CASE WHEN wait_class ='Queueing'       THEN 1 ELSE 0 END) "Queueing"

--      , 10 * SUM(CASE WHEN wait_class ='Other'          THEN 1 ELSE 0 END) "Other"

      , TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen

      , TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen

    FROM

        (SELECT

             a.*

           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex

           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex

           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex

           , NVL(event, session_state)||

                CASE 

                    WHEN event like 'enq%' AND session_state = 'WAITING'

                    THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']'

                    WHEN a.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#') 

                    THEN ' ['||CASE WHEN a.p3 <= (SELECT MAX(r) FROM bclass) 

                               THEN (SELECT class FROM bclass WHERE r = a.p3)

                               ELSE (SELECT DECODE(MOD(BITAND(a.p3,TO_NUMBER('FFFF','XXXX')) - 17,2),0,'undo header',1,'undo data', 'error') FROM dual)

                               END  ||']' 

                    ELSE null 

                END event2 -- event is NULL in ASH if the session is not waiting (session_state = ON CPU)

           , CASE WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN

                REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')

             ELSE

                '('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'

             END || ' ' program2

           , CASE WHEN BITAND(time_model, POWER(2, 01)) = POWER(2, 01) THEN 'DBTIME '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 02)) = POWER(2, 02) THEN 'BACKGROUND '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 03)) = POWER(2, 03) THEN 'CONNECTION_MGMT '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 04)) = POWER(2, 04) THEN 'PARSE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 05)) = POWER(2, 05) THEN 'FAILED_PARSE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 06)) = POWER(2, 06) THEN 'NOMEM_PARSE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 07)) = POWER(2, 07) THEN 'HARD_PARSE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 08)) = POWER(2, 08) THEN 'NO_SHARERS_PARSE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 09)) = POWER(2, 09) THEN 'BIND_MISMATCH_PARSE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 10)) = POWER(2, 10) THEN 'SQL_EXECUTION '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 11)) = POWER(2, 11) THEN 'PLSQL_EXECUTION '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 12)) = POWER(2, 12) THEN 'PLSQL_RPC '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 13)) = POWER(2, 13) THEN 'PLSQL_COMPILATION '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 14)) = POWER(2, 14) THEN 'JAVA_EXECUTION '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 15)) = POWER(2, 15) THEN 'BIND '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 16)) = POWER(2, 16) THEN 'CURSOR_CLOSE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 17)) = POWER(2, 17) THEN 'SEQUENCE_LOAD '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 18)) = POWER(2, 18) THEN 'INMEMORY_QUERY '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 19)) = POWER(2, 19) THEN 'INMEMORY_POPULATE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 20)) = POWER(2, 20) THEN 'INMEMORY_PREPOPULATE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 21)) = POWER(2, 21) THEN 'INMEMORY_REPOPULATE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 22)) = POWER(2, 22) THEN 'INMEMORY_TREPOPULATE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 23)) = POWER(2, 23) THEN 'TABLESPACE_ENCRYPTION ' END time_model_name

        FROM dba_hist_active_sess_history a) a

      , dba_users u

      , (SELECT

             object_id,data_object_id,owner,object_name,subobject_name,object_type

           , owner||'.'||object_name obj

           , owner||'.'||object_name||' ['||object_type||']' objt

         FROM dba_objects) o

    WHERE

        a.user_id = u.user_id (+)

    AND a.current_obj# = o.object_id(+)

    AND session_type='FOREGROUND'

    AND a.sample_time BETWEEN sysdate-2/24 AND sysdate

    AND a.dbid = (SELECT d.dbid FROM v$database d) -- for partition pruning

    --AND a.snap_id IN (SELECT sn.snap_id FROM dba_hist_snapshot sn WHERE sn.begin_interval_time >= sysdate-2/24 AND sn.end_interval_time <= sysdate) -- for partition pruning

    AND snap_id IN (SELECT snap_id FROM dba_hist_snapshot WHERE sample_time BETWEEN sysdate-2/24 AND sysdate) -- for2partition pruning

    AND (a.dbid, a.snap_id) IN (SELECT d.dbid, sn.snap_id FROM v$database d, dba_hist_snapshot sn 

WHERE d.dbid = sn.dbid AND sn.begin_interval_time >= sysdate-2/24 AND sn.end_interval_time <= sysdate) -- for patition pruning

    GROUP BY

        username,sql_id

    ORDER BY

        "TotalSeconds" DESC

       , username,sql_id

)

WHERE

    ROWNUM <= 20

/

with time !!!!

define FROM_TIMESTAMP='2022-04-28 15:00:00'

define TO_TIMESTAMP='2022-04-28 15:10:00'

set linesize 300 pagesize 300

COL "%This" FOR A7

--COL p1     FOR 99999999999999

--COL p2     FOR 99999999999999

--COL p3     FOR 99999999999999

COL p1text      FOR A30 word_wrap

COL p2text      FOR A30 word_wrap

COL p3text      FOR A30 word_wrap

COL p1hex       FOR A17

COL p2hex       FOR A17

COL p3hex       FOR A17

COL AAS         FOR 9999.9

COL totalseconds HEAD "Total|Seconds" FOR 99999999

COL dist_sqlexec_seen HEAD "Distinct|Execs Seen"

COL event       FOR A42 WORD_WRAP

COL event2      FOR A46 WORD_WRAP

COL time_model_name FOR A50 WORD_WRAP

COL program2    FOR A40 TRUNCATE

COL username    FOR A20 wrap

COL obj         FOR A30

COL objt        FOR A50

COL sql_opname  FOR A20

COL top_level_call_name FOR A30

COL wait_class  FOR A15

SELECT * FROM (

    WITH bclass AS (SELECT class, ROWNUM r from v$waitstat)

    SELECT /*+ LEADING(a) USE_HASH(u) */

        10 * COUNT(*)                                                      "TotalSeconds"

      , ROUND(10 * COUNT(*) / ((CAST(sysdate AS DATE) - CAST(sysdate-2/24 AS DATE)) * 86400), 1) AAS

      , LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This"

      , username,sql_id

--      , 10 * SUM(CASE WHEN wait_class IS NULL           THEN 1 ELSE 0 END) "CPU"

--      , 10 * SUM(CASE WHEN wait_class ='User I/O'       THEN 1 ELSE 0 END) "User I/O"

--      , 10 * SUM(CASE WHEN wait_class ='Application'    THEN 1 ELSE 0 END) "Application"

--      , 10 * SUM(CASE WHEN wait_class ='Concurrency'    THEN 1 ELSE 0 END) "Concurrency"

--      , 10 * SUM(CASE WHEN wait_class ='Commit'         THEN 1 ELSE 0 END) "Commit"

--      , 10 * SUM(CASE WHEN wait_class ='Configuration'  THEN 1 ELSE 0 END) "Configuration"

--      , 10 * SUM(CASE WHEN wait_class ='Cluster'        THEN 1 ELSE 0 END) "Cluster"

--      , 10 * SUM(CASE WHEN wait_class ='Idle'           THEN 1 ELSE 0 END) "Idle"

--      , 10 * SUM(CASE WHEN wait_class ='Network'        THEN 1 ELSE 0 END) "Network"

--      , 10 * SUM(CASE WHEN wait_class ='System I/O'     THEN 1 ELSE 0 END) "System I/O"

--      , 10 * SUM(CASE WHEN wait_class ='Scheduler'      THEN 1 ELSE 0 END) "Scheduler"

--      , 10 * SUM(CASE WHEN wait_class ='Administrative' THEN 1 ELSE 0 END) "Administrative"

--      , 10 * SUM(CASE WHEN wait_class ='Queueing'       THEN 1 ELSE 0 END) "Queueing"

--      , 10 * SUM(CASE WHEN wait_class ='Other'          THEN 1 ELSE 0 END) "Other"

      , TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen

      , TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen

    FROM

        (SELECT

             a.*

           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex

           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex

           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex

           , NVL(event, session_state)||

                CASE 

                    WHEN event like 'enq%' AND session_state = 'WAITING'

                    THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']'

                    WHEN a.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#') 

                    THEN ' ['||CASE WHEN a.p3 <= (SELECT MAX(r) FROM bclass) 

                               THEN (SELECT class FROM bclass WHERE r = a.p3)

                               ELSE (SELECT DECODE(MOD(BITAND(a.p3,TO_NUMBER('FFFF','XXXX')) - 17,2),0,'undo header',1,'undo data', 'error') FROM dual)

                               END  ||']' 

                    ELSE null 

                END event2 -- event is NULL in ASH if the session is not waiting (session_state = ON CPU)

           , CASE WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN

                REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')

             ELSE

                '('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'

             END || ' ' program2

           , CASE WHEN BITAND(time_model, POWER(2, 01)) = POWER(2, 01) THEN 'DBTIME '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 02)) = POWER(2, 02) THEN 'BACKGROUND '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 03)) = POWER(2, 03) THEN 'CONNECTION_MGMT '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 04)) = POWER(2, 04) THEN 'PARSE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 05)) = POWER(2, 05) THEN 'FAILED_PARSE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 06)) = POWER(2, 06) THEN 'NOMEM_PARSE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 07)) = POWER(2, 07) THEN 'HARD_PARSE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 08)) = POWER(2, 08) THEN 'NO_SHARERS_PARSE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 09)) = POWER(2, 09) THEN 'BIND_MISMATCH_PARSE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 10)) = POWER(2, 10) THEN 'SQL_EXECUTION '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 11)) = POWER(2, 11) THEN 'PLSQL_EXECUTION '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 12)) = POWER(2, 12) THEN 'PLSQL_RPC '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 13)) = POWER(2, 13) THEN 'PLSQL_COMPILATION '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 14)) = POWER(2, 14) THEN 'JAVA_EXECUTION '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 15)) = POWER(2, 15) THEN 'BIND '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 16)) = POWER(2, 16) THEN 'CURSOR_CLOSE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 17)) = POWER(2, 17) THEN 'SEQUENCE_LOAD '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 18)) = POWER(2, 18) THEN 'INMEMORY_QUERY '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 19)) = POWER(2, 19) THEN 'INMEMORY_POPULATE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 20)) = POWER(2, 20) THEN 'INMEMORY_PREPOPULATE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 21)) = POWER(2, 21) THEN 'INMEMORY_REPOPULATE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 22)) = POWER(2, 22) THEN 'INMEMORY_TREPOPULATE '  END

           ||CASE WHEN BITAND(time_model, POWER(2, 23)) = POWER(2, 23) THEN 'TABLESPACE_ENCRYPTION ' END time_model_name

        FROM dba_hist_active_sess_history a) a

      , dba_users u

      , (SELECT

             object_id,data_object_id,owner,object_name,subobject_name,object_type

           , owner||'.'||object_name obj

           , owner||'.'||object_name||' ['||object_type||']' objt

         FROM dba_objects) o

    WHERE

        a.user_id = u.user_id (+)

    AND a.current_obj# = o.object_id(+)

    AND session_type='FOREGROUND'

    AND a.sample_time BETWEEN sysdate-2/24 AND sysdate

    AND a.dbid = (SELECT d.dbid FROM v$database d) -- for partition pruning

    --AND a.snap_id IN (SELECT sn.snap_id FROM dba_hist_snapshot sn WHERE sn.begin_interval_time >= sysdate-2/24 AND sn.end_interval_time <= sysdate) -- for partition pruning

    AND snap_id IN (SELECT snap_id FROM dba_hist_snapshot WHERE sample_time BETWEEN sysdate-2/24 AND sysdate) -- for2partition pruning

    AND (a.dbid, a.snap_id) IN (SELECT d.dbid, sn.snap_id FROM v$database d, dba_hist_snapshot sn 

WHERE d.dbid = sn.dbid 

    -- AND sn.begin_interval_time >= sysdate-2/24 AND sn.end_interval_time <= sysdate) -- for patition pruning

   -- AND sn.begin_interval_time >=TO_DATE('&&FROM_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')  AND sn.end_interval_time <=TO_DATE('&&TO_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')

)

    GROUP BY

        username,sql_id

    ORDER BY

        "TotalSeconds" DESC

       , username,sql_id

)

WHERE

    ROWNUM <= 20

/

prompt ************************************

prompt **** ASH OVERALL WAIT PROFILE

prompt ************************************

prompt RR columns are ratio to report

define how_many_min_back='10'  -----------!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Min

alter session set nls_date_format='dd-mm-yyyy hh24:mi'; 

col MIN_ASH_AVAILABLE   for a28                                                        

col MAX_ASH_AVAILABLE    for a28    

SELECT MIN(sample_time) min_ash_available,max(sample_time) max_ash_available,sysdate-MIN(sample_time) available_duration FROM v$active_session_history;

set linesize 300 pagesize 300

col EVENT for a40

select * from (

select NVL(event,'CPU') event,count(*),round((ratio_to_report(sum(1)) over ()*100),1) rr from gv$active_session_history

WHERE user_id<>0

AND sample_time<trunc(SYSDATE+1) AND sample_time>trunc(sysdate-1)

group by event

order by 2 desc

) where rownum<10;

 

prompt ************************************

prompt **** ASH I/O by SQL_ID

prompt ************************************

prompt RR columns are ratio to report

 

set linesize 300 pagesize 300

col EVENT for a30 

COLUMN force_matching_signature FOR 999999999999999999999999999

select * from (

select

sql_id ,sql_plan_hash_value,force_matching_signature,NVL(event,'CPU') Event,count(*),round((ratio_to_report(sum(1)) over ()*100),1) rr from gv$active_session_history

where 1=1

AND wait_class LIKE '%I/O'

--AND event IS null

and user_id<>0

AND sql_id IS NOT NULL

group by sql_id,sql_plan_hash_value,event,force_matching_signature

order by 5 desc

) where rownum<30;

prompt ************************************

prompt **** ASH OVERALL WAIT PROFILE

prompt ************************************

prompt RR columns are ratio to report

SELECT MIN(sample_time) min_ash_available,sysdate-MIN(sample_time) available_duration FROM v$active_session_history;

select * from (

select NVL(event,'CPU') event,count(*),round((ratio_to_report(sum(1)) over ()*100),1) rr from gv$active_session_history

WHERE user_id<>0

AND SAMPLE_TIME > sysdate - ('&how_many_min_back'/(24*60))

group by event

order by 2 desc

) where rownum<10;

 

prompt ************************************

prompt **** ASH I/O by SQL_ID, Top 10

prompt ************************************

prompt RR columns are ratio to report

 

COLUMN force_matching_signature FOR 999999999999999999999999999

select * from (

select

sql_id ,sql_plan_hash_value,force_matching_signature,NVL(event,'CPU') Event,count(*),round((ratio_to_report(sum(1)) over ()*100),1) rr from gv$active_session_history

where 1=1

AND SAMPLE_TIME > sysdate - ('&how_many_min_back'/(24*60))

AND wait_class LIKE '%I/O'

--AND event IS null

and user_id<>0

AND sql_id IS NOT NULL

group by sql_id,sql_plan_hash_value,event,force_matching_signature

order by 5 desc

) where rownum<11;

prompt ************************************

prompt **** ASH DB_TIME by SQL_ID, Top 10

prompt ************************************

select * from (

select sql_id

, count(*) DBTime

, round(count(*)*100/sum(count(*))

over (), 2) pctload

from gv$active_session_history

where sample_time > sysdate - ('&how_many_min_back'/(24*60))

and session_type <> 'BACKGROUND'

group by sql_id

order by count(*) desc

) where rownum < 11;

====

SQL REPORT ...

@?/rdbms/admin/awrsqrpt.sql

====

if running slow !!!!!

alter session set "_push_join_predicate" = FALSE ;

SELECT * from table(dbms_workload_repository.ash_global_report_text

                       (  l_dbid         => (select dbid from v$database)

                        , l_inst_num     => ''

                        , l_btime        => (SYSDATE - interval '1' hour)

                        , l_etime        => (sysdate - interval '5' MINUTE)

                        , l_sid          => NULL

                        , l_sql_id       => NULL

                        , l_wait_class   => NULL

                        , l_service_hash => NULL

                        , l_module       => NULL

                        , l_action       => NULL

                        , l_client_id    => NULL

                       )

                   );

define sql_id='36psp2j8kf741'

VARIABLE BgnSnap NUMBER

VARIABLE EndSnap NUMBER

VARIABLE DID NUMBER

VARIABLE INST_NUMBER number

VARIABLE x VARCHAR2(30)

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;

set linesize 300 pagesize 300

select * from table(dbms_workload_repository.awr_sql_report_text((SELECT DBID FROM V$DATABASE), (SELECT INSTANCE_NUMBER FROM V$INSTANCE), :BgnSnap,:EndSnap, '&sql_id'));

alter session set "_push_join_predicate" = FALSE ;

define sql_id='ga5m3ggy97233'

SELECT * from table(dbms_workload_repository.ash_global_report_text

                       (  l_dbid         => (select dbid from v$database)

                        , l_inst_num     => ''

                        , l_btime        => (SYSDATE - interval '1' hour)

                        , l_etime        => (sysdate - interval '5' MINUTE)

                        , l_sid          => NULL

                        , l_sql_id       => '&sql_id'  

                        , l_wait_class   => NULL

                        , l_service_hash => NULL

                        , l_module       => NULL

                        , l_action       => NULL

                        , l_client_id    => NULL

                       )

                   );

alter session set "_push_join_predicate" = FALSE ;

set serveroutput on linesize 400
declare
  i_dbid number;
  i_inst_id NUMBER;
begin

  DBMS_OUTPUT.ENABLE (buffer_size => NULL); 

  select dbid into i_dbid from v$database;
  select instance_number into i_inst_id from v$instance;

  for cursor1 in 
  (
  select * from table( DBMS_WORKLOAD_REPOSITORY.ASH_GLOBAL_REPORT_text( l_dbid => i_dbid, l_inst_num => i_inst_id,   l_btime => SYSDATE - 10/24/60,  l_etime =>  SYSDATE  ))
  )
    LOOP
    DBMS_OUTPUT.PUT_LINE( cursor1.output);
    END LOOP;


end;
/

Oracle user role (user metadata )

Oracle Copy user role  .. 

Oracle user metadata 

user role copy 

Oracle user role...

Save following script on user.sql
---===
SET LONG 10000 LONGCHUNKSIZE 10000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
COLUMN DDL FORMAT A500
BEGIN
 DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
 DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE);
END;
/
VARIABLE V_USERNAME VARCHAR2(30);
EXEC :V_USERNAME := UPPER('&1');
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) FROM DBA_USERS WHERE DBA_USERS.USERNAME=:V_USERNAME
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) FROM DBA_USERS
WHERE EXISTS (SELECT 1 FROM DBA_ROLE_PRIVS DRP WHERE DRP.GRANTEE = DBA_USERS.USERNAME AND DBA_USERS.USERNAME=:V_USERNAME )
UNION ALL 
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) FROM DBA_USERS
WHERE EXISTS (SELECT 1 FROM DBA_ROLE_PRIVS DRP 
WHERE DRP.GRANTEE = DBA_USERS.USERNAME AND DBA_USERS.USERNAME=:V_USERNAME )
UNION ALL 
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) FROM DBA_USERS
WHERE EXISTS (SELECT 1 FROM DBA_TAB_PRIVS DTP
WHERE DTP.GRANTEE = DBA_USERS.USERNAME AND DBA_USERS.USERNAME=:V_USERNAME );
---===


SYS@rac1> @user
Enter value for 1: ANUJ

 CREATE USER "ANUJ" IDENTIFIED BY VALUES 'S:C33592E1CFCDA4D8913D7071B93B43F6E9CBE507DB0A382ABA149701720A;T:4193DB93E7931628A37396A83E7015C0CCD4291BA3CEB56AD5F5B36D7DB1EDD1356E899AB1B67A9ACFB21FFB4734F1FF475A1459BA40B2FB615319A0D8B95B0BAF3CD834930B6'
 DEFAULT TABLESPACE "USERS"
 TEMPORARY TABLESPACE "TEMP";


 GRANT "DBA" TO "ANUJ";


 GRANT UNLIMITED TABLESPACE TO "ANUJ";

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

define v_owner='SCOTT'

select dbms_metadata.get_ddl('USER', du.username) AS DDL_SCRIPT from dba_users du
where du.username = '&v_owner'
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', dtq.username) AS DDL_SCRIPT from dba_ts_quotas dtq
where dtq.username = '&v_owner'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', drp.grantee)        AS DDL_SCRIPT from dba_role_privs drp
where drp.grantee = '&v_owner'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', dsp.grantee)      AS DDL_SCRIPT from dba_sys_privs dsp
where dsp.grantee = '&v_owner'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', dtp.grantee)      AS DDL_SCRIPT from dba_tab_privs dtp
where dtp.grantee = '&v_owner'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', drp.grantee)      AS DDL_SCRIPT from dba_role_privs drp
where drp.grantee = '&v_owner'
and drp.default_role = 'YES'
and rownum = 1
;

   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:3AA2B0A8810266587ADBC8D7B6CAB4A34
71D9562076B10D5474A05A18043;T:9A9DBC55845F58FEFC5FFC6CC3D499C1C0666E9E3A67E38E5D
853941D00215094E3FD1D6B0787CDBE9F0A96C10194A0A31ED2380D203EE40717467628024EBC073
5B89D7663F1E97E1EBBF21B2B3AC60'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"


   GRANT "CONNECT" TO "SCOTT"
   GRANT "RESOURCE" TO "SCOTT"


  GRANT UNLIMITED TABLESPACE TO "SCOTT"


  GRANT READ ON DIRECTORY "DP_EXP_DIR" TO "SCOTT"
  GRANT WRITE ON DIRECTORY "DP_EXP_DIR" TO "SCOTT"


   ALTER USER "SCOTT" DEFAULT ROLE ALL

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

DEFINE schema_owner = &1
-- WHENEVER SQLERROR EXIT FAILURE

SET LINESIZE 132 PAGESIZE 0 FEEDBACK off VERIFY off TRIMSPOOL on LONG 1000000
COLUMN ddl_string FORMAT A100 WORD_WRAP
COLUMN row_order FORMAT 999 NOPRINT

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false);

COLUMN rundate FORMAT A8 NEW_VALUE ddl_date NOPRINT
COLUMN dbname FORMAT A10 NEW_VALUE db_name NOPRINT
COLUMN spoolname FORMAT A50 NEW_VALUE spool_name NOPRINT
COLUMN maxtextlength FORMAT 9999 NEW_VALUE max_text_length NOPRINT
COLUMN schemaid NEW_VALUE schema_id NOPRINT

SELECT u.user# schemaid
FROM sys.user$ u
WHERE u.name = UPPER('&&schema_owner')
/

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') rundate
FROM dual
/

SELECT UPPER(SYS_CONTEXT('USERENV', 'DB_NAME')) dbname
FROM dual
/

SELECT 'schema_'||'&&db_name'||'_'||'&&schema_owner'||'_'||'&&ddl_date'||'_ddl.log' spoolname
FROM dual
/

SELECT MAX(LENGTH(s.source)) maxtextlength
FROM sys.obj$ o,
     sys.source$ s
WHERE o.owner# = &&schema_id
  AND o.obj# = s.obj#
/

SET LINESIZE &&max_text_length

-- SPOOL schema_&&db_name\_&&schema_owner\_&&ddl_date\_ddl.sql
SPOOL schema_ddl.sql

PROMPT WHENEVER SQLERROR EXIT FAILURE
PROMPT WHENEVER OSERROR EXIT FAILURE
PROMPT SPOOL &&spool_name
PROMPT
PROMPT

SELECT 0 row_order, '-- Object Count' ddl_string
FROM dual
UNION 
SELECT 1 row_order, '-- '||DECODE(o.type#, 
                                  1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 
                                  6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 
                                 10, 'NON-EXISTENT', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 
                                 14, 'TYPE BODY', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 42, 'MATERIALIZED VIEW', 
                                 43, 'DIMENSION', 56, 'JAVA DATA', 'UNDEFINED')||' -- '||COUNT(1)
FROM sys.obj$ o
WHERE o.owner# = &&schema_id
GROUP BY 1, o.type#
UNION
SELECT 2 row_order, CHR(10)||CHR(10)
FROM dual
/

PROMPT
PROMPT -- Profile Creation
PROMPT

SELECT DBMS_METADATA.GET_DDL('PROFILE', pr.name) ddl_string
FROM (SELECT DISTINCT pi.name 
      FROM sys.profname$ pi
      WHERE pi.name != 'DEFAULT') pr
/

PROMPT
PROMPT -- User Creation
PROMPT

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('USER', '&&schema_owner')) ddl_string
FROM dual
/

PROMPT
PROMPT -- User Tablespace Quotas
PROMPT
-- This is failing with an error message, causing the script to terminate. No workaround yet.

/*
SELECT CASE
           WHEN COUNT(1) != 0 THEN DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', '&&schema_owner')
           ELSE NULL
       END ddl_string
FROM sys.ts$ ts,
     sys.tsq$ tq
WHERE tq.user# = &&schema_id
  AND ts.ts# = tq.ts# 
/
*/

PROMPT
PROMPT -- User Role
PROMPT

SELECT /*+ ordered */ 'GRANT "'||u.name||'" TO "'||upper('&&schema_owner')||'"'||
       CASE WHEN min(sa.option$) = 1 THEN ' WITH ADMIN OPTION;' ELSE ';' END ddl_string
FROM sys.sysauth$ sa,
     sys.user$ u
WHERE sa.grantee# = &&schema_id
  AND u.user# = sa.privilege#
  AND sa.grantee# != 1
GROUP BY u.name
/

PROMPT
PROMPT -- User System Privileges
PROMPT
-- If the dbms_metadata call for system grants does not find any for the schema owner
-- it fails with an error message, causing the script to terminate. The SELECT is used as a 
-- workaround. A TAR has been filed w/Oracle (response 'expected behaviour' - RFE filed)

SELECT CASE
           WHEN COUNT(1) != 0 THEN DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', '&&schema_owner')
           ELSE NULL
       END ddl_string
FROM sys.sysauth$ sa
WHERE sa.grantee# = &&schema_id
/

PROMPT
PROMPT -- User Object Privileges
PROMPT
-- If the dbms_metadata call for object grants does not find any for the schema owner
-- it fails with an error message, causing the script to terminate. The SELECT is used as a 
-- workaround. A TAR has been filed w/Oracle (response 'expected behaviour' - RFE filed)

SELECT CASE
           WHEN COUNT(1) != 0 THEN DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', '&&schema_owner')
           ELSE NULL
       END ddl_string
FROM sys.objauth$ oa
WHERE oa.grantee# = &&schema_id
/


PROMPT
PROMPT -- Schema Sequences
PROMPT

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('SEQUENCE', o.name,'&&schema_owner')) ddl_string
FROM sys.seq$ s,
     sys.obj$ o
WHERE o.owner# = &&schema_id
  AND o.obj# = s.obj#
/


PROMPT
PROMPT -- Schema Database Links
PROMPT

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('DB_LINK', l.name,'&&schema_owner')) ddl_string
FROM sys.link$ l
WHERE l.owner# = &&schema_id
/


PROMPT
PROMPT -- Schema Directories
PROMPT

SELECT DBMS_METADATA.GET_DDL('DIRECTORY', o.name, '&&schema_owner') ddl_string
FROM sys.obj$ o,
     sys.dir$ d
WHERE o.owner# = &&schema_id
  AND o.obj# = d.obj#
/

PROMPT
PROMPT -- Schema Tables
PROMPT

/* Add the BITAND(o.flags, 128) to exclude tables in the recyclebin */

SELECT     TO_CHAR(DBMS_METADATA.GET_DDL('TABLE', o.name,'&&schema_owner')) ddl_string
FROM       sys.obj$ o,
           sys.tab$ t
WHERE      o.owner# = &&schema_id
  AND      o.obj# = t.obj#
  AND      BITAND(o.flags, 128) = 0
/

PROMPT
PROMPT -- Schema Table RI Constraints
PROMPT

SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT', oc.name, '&&schema_owner')||'/' ddl_string
FROM sys.con$ oc,
     sys.obj$ o,
     sys.cdef$ c
WHERE oc.owner# = &&schema_id
  AND oc.con# = c.con#
  AND c.obj# = o.obj#
  AND c.type# = 4
/

PROMPT
PROMPT -- Schema Indexes
PROMPT
--- This is used to exclude primary key and unique key indexes that have already been defined
--- as part of the table generation statement.

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('INDEX', o.name,'&&schema_owner')) ddl_string
FROM sys.ind$ i,
     sys.obj$ o
WHERE o.owner# = &&schema_id
  AND o.obj# = i.obj#
  AND bitand(i.property,1) = 1
  AND i.type# != 8
/

PROMPT
PROMPT -- Schema Views
PROMPT
-- View extraction is not functioning properly in 9.2. Breaks occur in middle of words (column_names, clauses, etc). 
-- Bug has been accepted with Oracle. No response of when/if backport to 9.2.0.5 will be available

-- SELECT REPLACE(TO_CHAR(DBMS_METADATA.GET_DDL('VIEW', v.view_name,'&&schema_owner')), '","','", "') ddl_string
-- FROM dba_views v
-- WHERE v.owner = '&&schema_owner'
-- /

-- Here is a workaround version
COLUMN viewname NOPRINT

CREATE GLOBAL TEMPORARY TABLE parsed_view_text 
  (view_name VARCHAR2(30), 
   text_id NUMBER, 
   view_text VARCHAR2(4000)
  ) ON COMMIT PRESERVE ROWS;

DECLARE
    num_iter NUMBER := 0;
    whole_clob CLOB;
    parsed_string VARCHAR2(32767);
    start_pos NUMBER := 1;
    num_chars NUMBER := 3000;

    CURSOR view_text_cur IS 
       SELECT o.name view_name, v.text text, v.textlength text_length, v.cols view_columns
       FROM sys.obj$ o,
            sys.view$ v
       WHERE o.obj# = v.obj#
         AND o.owner# = &&schema_id;

    view_text_rec view_text_cur%ROWTYPE;

BEGIN

   FOR view_text_rec IN view_text_cur
   LOOP
      whole_clob := TO_CLOB(view_text_rec.text);
 
      DBMS_OUTPUT.PUT_LINE('View Name: '||view_text_rec.view_name||' Text Length :'|| view_text_rec.text_length);
      LOOP
         IF (view_text_rec.text_length - start_pos) < 3000
         THEN
              parsed_string := SUBSTR(whole_clob, start_pos);
              INSERT INTO parsed_view_text VALUES (view_text_rec.view_name, (view_text_rec.view_columns + num_iter), parsed_string||CHR(10)||'/');
            EXIT;
         END if;
         parsed_string := SUBSTR(whole_clob, start_pos, 3000);
         num_chars := GREATEST(INSTR(parsed_string, ', ', -1, 1), INSTR(parsed_string, ',"', -1, 1),
                               (INSTR(parsed_string, '),', -1, 1)+1), INSTR(parsed_string, ')', -1, 1));
         parsed_string := SUBSTR(whole_clob, start_pos, num_chars);
         INSERT INTO parsed_view_text VALUES (view_text_rec.view_name, (view_text_rec.view_columns + num_iter), parsed_string);
         start_pos := start_pos + num_chars;
         num_iter := num_iter + 1;
      END LOOP;
      COMMIT;
      start_pos := 1;
      num_chars := 3000;
      num_iter := 1;
   END LOOP;
END;
/

SELECT 0 row_order,
       o.name viewname, 
       'CREATE OR REPLACE FORCE VIEW "'||'&&schema_owner'||'"."'||o.name||'"' ddl_string
FROM sys.obj$ o,
     sys.view$ v
WHERE o.obj# = v.obj#
  AND o.owner# = &&schema_iD
UNION
SELECT decode(c.col#, 0, to_number(null), c.col#) row_order, 
       o.name viewname,
       CASE WHEN decode(c.col#, 0, to_number(null), c.col#) = 1 THEN '("'||c.name||'",'
            WHEN decode(c.col#, 0, to_number(null), c.col#) = v.cols THEN ' "'||c.name||'") AS '
            ELSE ' "'||c.name||'",'
       END ddl_string
FROM sys.col$ c,
     sys.obj$ o,
     sys.view$ v
WHERE o.obj# = c.obj#
  AND o.owner# = &&schema_id
  AND o.obj# = v.obj#
UNION
SELECT pv.text_id row_order,
       pv.view_name viewname,
       REPLACE(TO_CHAR(pv.view_text), '","', '", "') ddl_string
FROM parsed_view_text pv 
ORDER BY viewname, row_order
/

TRUNCATE TABLE parsed_view_text;
DROP TABLE parsed_view_text;

SET LINESIZE 4005
COLUMN ddl_string FORMAT A4000

PROMPT
PROMPT -- Schema Functions
PROMPT

SELECT DBMS_METADATA.GET_DDL('FUNCTION', o.name,'&&schema_owner') ddl_string
FROM sys.obj$ o
WHERE o.owner# = &&schema_id
  AND o.type# = 8
/

PROMPT
PROMPT -- Schema Packages (specs and body)
PROMPT

SELECT DBMS_METADATA.GET_DDL('PACKAGE', o.name,'&&schema_owner') ddl_string
FROM sys.obj$ o
WHERE o.owner# = &&schema_id
  AND o.type# = 9
/

PROMPT
PROMPT -- Schema Procedures
PROMPT

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', o.name,'&&schema_owner') ddl_string
FROM sys.obj$ o
WHERE o.owner# = &&schema_id
  AND o.type# = 7
/

COLUMN ddl_string FORMAT A125
SET LINESIZE 132

PROMPT
PROMPT -- Schema Synonyms
PROMPT

SELECT 'CREATE SYNONYM "&&schema_owner"."'||o.name||'" FOR "'||s.owner||'"."'||s.name||NVL2(s.node, '@'||s.node||'";', '";') ddl_string
FROM sys.syn$ s,
     sys.obj$ o
WHERE o.owner# = &&schema_id
  AND o.obj# = s.obj#
  AND o.type# = 5
/

PROMPT
PROMPT -- End of ddl
PROMPT 

PROMPT SET LINESIZE 132 PAGESIZE 45 FEEDBACK OFF
PROMPT COLUMN line FORMAT 9999 
PROMPT COLUMN text FORMAT A40 WORD_WRAP
PROMPT
PROMPT -- Checking for errors
PROMPT
PROMPT SELECT name, type, line, text
PROMPT FROM dba_errors
PROMPT WHERE owner = '&&schema_owner'
PROMPT /

PROMPT SPOOL OFF

SPOOL OFF

UNDEFINE schema_owner

-- EXIT