Search This Blog

Total Pageviews

Friday 25 August 2017

Oracle active session report ( ASH report ) ..

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




Oracle DBA

anuj blog Archive