Oracle Role info

role info



from web ,,,,,


SET LINESIZE 300 PAGESIZE 50000 FEEDBACK OFF ECHO OFF VERIFY OFF SHOWMODE OFF TRIMSPOOL ON
COLUMN grantee_name FORMAT A30 HEADING 'User Name'
COLUMN role_path FORMAT A50 HEADING 'Role Hierarchy'
COLUMN priv_obj_name FORMAT A60 HEADING 'Privilege/Object Name'
COLUMN sort_order1 NOPRINT
COLUMN priv_path_names NOPRINT
COLUMN insert_priv FORMAT A2 HEADING 'IN'
COLUMN update_priv FORMAT A2 HEADING 'UP'
COLUMN delete_priv FORMAT A2 HEADING 'DE'
COLUMN select_priv FORMAT A2 HEADING 'SE'
COLUMN alter_priv FORMAT A2 HEADING 'AL'
COLUMN execute_priv FORMAT A2 HEADING 'EX'
COLUMN other_priv FORMAT A2 HEADING 'OT'


BREAK ON grantee_name NODUPLICATE SKIP 1 ON role_path NODUPLICATE

SELECT 1 sort_order1,
urm.priv_path_names priv_path_names,
CASE WHEN urm.root_user_id != urm.priv_path THEN SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')-1)
ELSE urm.priv_path_names
END grantee_name,
CASE WHEN urm.root_user_id = urm.priv_path THEN 'Direct'
ELSE 'Role '||REPLACE(urm.priv_path_names, SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')))
END role_path,
spm.name priv_obj_name,
NULL insert_priv,
NULL update_priv,
NULL delete_priv,
NULL select_priv,
NULL alter_priv,
NULL execute_priv,
NULL other_priv
FROM system_privilege_map spm,
sysauth$ sa,
( SELECT CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, 1, INSTR(urm2.priv_path, '.')-1)
ELSE urm2.priv_path
END root_user_id,
CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, INSTR(urm2.priv_path, '.', -1)+1)
ELSE urm2.priv_path
END role_id,
urm2.priv_path,
urm2.priv_path_names
FROM ( SELECT TO_CHAR(u.user#) priv_path,
u.name priv_path_names
FROM user$ u
UNION
SELECT sam.priv_path priv_path,
sam.priv_path_names priv_path_names
FROM (SELECT sa.grantee#,
sa.privilege#,
LTRIM(SYS_CONNECT_BY_PATH(sa.grantee#, '.'), '.')||'.'||sa.privilege# priv_path,
LTRIM(SYS_CONNECT_BY_PATH(u1.name, '.'), '.')||'.'||u2.name priv_path_names
FROM sysauth$ sa,
user$ u1,
user$ u2
WHERE sa.privilege# > 0
AND u1.user# = sa.grantee#
AND u2.user# = sa.privilege#
CONNECT BY sa.grantee# = PRIOR sa.privilege#
ORDER SIBLINGS BY sa.privilege#
) sam
) urm2
) urm
WHERE urm.root_user_id IN ( SELECT usq.user#
FROM user$ usq
WHERE usq.type# = 1
AND usq.name NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','CTXSYS','PERFSTAT')
)
AND sa.grantee# = urm.role_id
AND sa.privilege# < 0
AND sa.privilege# = spm.privilege
UNION
SELECT 2 sort_order1,
urm.priv_path_names priv_path_names,
CASE WHEN urm.root_user_id != urm.priv_path THEN SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')-1)
ELSE urm.priv_path_names
END grantee_name,
CASE WHEN urm.root_user_id = urm.priv_path THEN 'Direct'
ELSE 'Role '||REPLACE(urm.priv_path_names, SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')))
END role_path,
u.name||'.'||o.name priv_obj_name,
MAX(DECODE(tpm.name, 'INSERT', 'X', NULL)) insert_priv,
MAX(DECODE(tpm.name, 'UPDATE', 'X', NULL)) update_priv,
MAX(DECODE(tpm.name, 'DELETE', 'X', NULL)) delete_priv,
MAX(DECODE(tpm.name, 'SELECT', 'X', NULL)) select_priv,
MAX(DECODE(tpm.name, 'ALTER', 'X', NULL)) alter_priv,
MAX(DECODE(tpm.name, 'EXECUTE', 'X', NULL)) execute_priv,
MAX(DECODE(tpm.name, 'INSERT', NULL, 'UPDATE', NULL, 'DELETE', NULL,
'SELECT', NULL, 'ALTER', NULL, 'EXECUTE', NULL,
'X' )) other_priv
FROM objauth$ oa,
obj$ o,
user$ u,
table_privilege_map tpm,
( SELECT CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, 1, INSTR(urm2.priv_path, '.')-1)
ELSE urm2.priv_path
END root_user_id,
CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, INSTR(urm2.priv_path, '.', -1)+1)
ELSE urm2.priv_path
END role_id,
urm2.priv_path,
urm2.priv_path_names
FROM ( SELECT TO_CHAR(u.user#) priv_path,
u.name priv_path_names
FROM user$ u
UNION
SELECT sam.priv_path priv_path,
sam.priv_path_names priv_path_names
FROM (SELECT sa.grantee#,
sa.privilege#,
LTRIM(SYS_CONNECT_BY_PATH(sa.grantee#, '.'), '.')||'.'||sa.privilege# priv_path,
LTRIM(SYS_CONNECT_BY_PATH(u1.name, '.'), '.')||'.'||u2.name priv_path_names
FROM sysauth$ sa,
user$ u1,
user$ u2
WHERE sa.privilege# > 0
AND u1.user# = sa.grantee#
AND u2.user# = sa.privilege#
CONNECT BY sa.grantee# = PRIOR sa.privilege#
ORDER SIBLINGS BY sa.privilege#
) sam
) urm2
) urm
WHERE urm.root_user_id IN ( SELECT usq.user#
FROM user$ usq
WHERE usq.type# = 1
AND usq.name NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','CTXSYS','PERFSTAT')
)
AND oa.grantee# = urm.role_id
AND oa.obj# = o.obj#
AND o.owner# != 59
AND o.owner# = u.user#
AND oa.privilege# = tpm.privilege
GROUP BY 2, urm.root_user_id, urm.priv_path, urm.priv_path_names, u.name, o.name
ORDER BY priv_path_names, sort_order1, priv_obj_name


SPOOL allprivs.lst

/



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




XYZ Direct UNLIMITED TABLESPACE
ABC.X X
Role CONNECT CREATE SESSION
Role RESOURCE CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE

Oracle Response Time Components

Oracle wait event
Oracle wait


@response.sql

SET LINESIZE 132 PAGESIZE 100 FEEDBACK OFF
COLUMN time_sum NOPRINT
COLUMN total_time NOPRINT
COLUMN wait_class FORMAT A15 HEADING 'Class'
COLUMN event FORMAT A40 HEADING 'Event'
COLUMN wait_seconds FORMAT 999,999,999,999.99 HEADING 'Wait Seconds'
COLUMN wait_pct FORMAT 99.999999 HEADING '% of T' NOPRINT
COLUMN nonidle_pct FORMAT 99.999999 HEADING '% of R'

BREAK ON wait_class SKIP PAGE
COMPUTE SUM OF wait_seconds nonidle_pct ON wait_class

TTITLE LEFT "*** Response Time Components by Class ***"

SELECT vswc.time_waited time_sum,
ven.wait_class,
vse.event,
ROUND((vse.time_waited_micro/1000000),4) wait_seconds,
instime.time_waited total_time,
(ROUND((vse.time_waited_micro/1000000),6)/(instime.time_waited/100))*100 wait_pct,
TO_NUMBER(DECODE(vswc.wait_class, 'Idle', NULL, 'Network', NULL,
(ROUND((vse.time_waited_micro/1000000),6)/
((instime.time_waited - idletime.time_waited)/100))*100)) nonidle_pct
FROM v$system_event vse,
v$event_name ven,
v$system_wait_class vswc,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) instime,
(SELECT SUM(time_waited) time_waited
FROM v$system_wait_class
WHERE wait_class IN ('Idle', 'Network')) idletime
WHERE ven.event_id = vse.event_id
AND vswc.wait_class = ven.wait_class
UNION
SELECT vss.value time_sum,
'CPU Utilization' wait_class,
NULL,
vss.value/100 wait_seconds,
instime.time_waited total_time,
(ROUND((vss.value/100),6)/(instime.time_waited/100))*100 wait_pct,
((ROUND((vss.value/100),6)/((instime.time_waited - idletime.time_waited)/100))*100) nonidle_pct
FROM v$sysstat vss,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) instime,
(SELECT SUM(time_waited) time_waited
FROM v$system_wait_class
WHERE wait_class IN ('Idle', 'Network')) idletime
WHERE vss.name = 'CPU used when call started'
ORDER BY time_sum, wait_class, wait_seconds DESC
/

CLEAR BREAK
CLEAR COMPUTE

TTITLE LEFT "*** Response Time Components by Time ***"

SELECT vse.event,
ROUND((vse.time_waited_micro/1000000),4) wait_seconds,
(ROUND((vse.time_waited_micro/1000000),6)/((nonidletime.time_waited)/100))*100 nonidle_pct
FROM v$system_event vse,
v$event_name ven,
v$system_wait_class vswc,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
WHERE wait_class NOT IN ('Idle', 'Network')
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) nonidletime
WHERE ven.event_id = vse.event_id
AND vswc.wait_class = ven.wait_class
AND vswc.wait_class NOT IN ('Idle', 'Network')
UNION
SELECT 'CPU Utilization' wait_class,
vss.value/100 wait_seconds,
(ROUND((vss.value/100),6)/((nonidletime.time_waited)/100))*100 nonidle_pct
FROM v$sysstat vss,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
WHERE wait_class NOT IN ('Idle', 'Network')
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) nonidletime
WHERE vss.name = 'CPU used when call started'
ORDER BY wait_seconds DESC
/












SQL> @responce

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Network SQL*Net more data from client .03
SQL*Net more data to client .01
SQL*Net message to client .01
*************** ------------------- ----------
sum .05

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Configuration checkpoint completed .43 .013184
log buffer space .10 .003156
undo segment extension .02 .000676
enq: SQ - contention .00 .000024
latch: redo writing .00 .000004
*************** ------------------- ----------
sum .56 .017044

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Administrative JS coord start wait .86 .026148
JS kgl get object wait .10 .003051
*************** ------------------- ----------
sum .96 .029199

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Commit log file sync 39.11 1.192503
*************** ------------------- ----------
sum 39.11 1.192503

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Other job scheduler coordinator slave wait 14.21 .433321
enq: CR - block range reuse ckpt 4.26 .129956
control file heartbeat 4.00 .121970
enq: CF - contention 2.95 .090048
ADR block file read 2.47 .075283
rdbms ipc reply 2.24 .068444
ARCH wait for process start 3 2.00 .061011
ADR block file write 1.84 .056206
enq: JS - queue lock .34 .010249
reliable message .17 .005181
latch: enqueue hash chains .17 .005119
kksfbc child completion .10 .003079
asynch descriptor resize .09 .002878
LGWR wait for redo copy .08 .002580
ADR file lock .08 .002286
SGA: allocation forcing component growth .07 .002012
enq: PR - contention .05 .001598
CRS call completion .04 .001141
latch: call allocation .02 .000728
Streams AQ: qmn coordinator waiting for .01 .000354
slave to start

latch free .01 .000204
latch: object queue header operation .01 .000200
instance state change .00 .000098
latch: cache buffers lru chain .00 .000030
latch: redo allocation .00 .000005
ARCH wait for archivelog lock .00 .000004
*************** ------------------- ----------
sum 35.22 1.073983

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Concurrency os thread startup 91.94 2.803294
library cache load lock 9.20 .280573
latch: shared pool 3.48 .106173
cursor: pin S wait on X 1.18 .035971
row cache lock .33 .009924
resmgr:internal state change .10 .003065
buffer busy waits .01 .000277
latch: row cache objects .00 .000131
latch: cache buffers chains .00 .000042
library cache: mutex X .00 .000036
*************** ------------------- ----------
sum 106.24 3.239486

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Scheduler resmgr:cpu quantum 121.26 3.697244
*************** ------------------- ----------
sum 121.26 3.697244

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
CPU Utilization 145.04 4.422423
*************** ------------------- ----------
sum 145.04 4.422423

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
User I/O db file sequential read 316.38 9.646716
db file scattered read 61.88 1.886868
Data file init write 31.37 .956459
local write wait 4.91 .149644
db file parallel read 3.21 .097827
read by other session 2.78 .084886
utl_file I/O 2.10 .064176
Disk file operations I/O 1.86 .056811
Parameter File I/O 1.58 .048154
direct path sync .86 .026246
db file single write .60 .018347
direct path read .34 .010384
Disk file Mirror/Media Repair Write .15 .004546
direct path write .05 .001602
direct path write temp .01 .000394
*************** ------------------- ----------
sum 428.09 13.053059

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Application enq: TX - row lock contention 490.69 14.961517
SQL*Net break/reset to client .73 .022267
enq: RO - fast object reuse .10 .002963
*************** ------------------- ----------
sum 491.51 14.986747

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
System I/O control file parallel write 1,144.43 34.894774
db file async I/O submit 427.99 13.049917
log file parallel write 254.10 7.747749
log file sequential read 58.29 1.777309
control file sequential read 6.41 .195364
Log archive I/O 1.26 .038356
recovery read .34 .010387
RMAN backup & recovery I/O .16 .004989
log file single write .12 .003611
control file single write .02 .000723
*************** ------------------- ----------
sum 1,893.12 57.723180

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Idle rdbms ipc message 1,665,247.86
DIAG idle wait 208,746.17
SQL*Net message from client 104,462.03
Streams AQ: qmn slave idle wait 104,419.46
pmon timer 104,385.85
shared server idle wait 104,382.54
Streams AQ: qmn coordinator idle wait 104,371.62
dispatcher timer 104,356.51
Streams AQ: waiting for time management 104,325.21
or cleanup tasks

smon timer 104,168.34
Space Manager: slave idle wait 103,983.65
jobq slave wait 35,947.71
VKRM Idle 14,399.80
class slave wait 299.96
PL/SQL lock timer 2.00
*************** ------------------- ----------
sum 2,863,498.73

*** Response Time Components by Time ***
Event Wait Seconds % of R
---------------------------------------- ------------------- ----------
control file parallel write 1,144.43 34.894242
enq: TX - row lock contention 490.69 14.961289
db file async I/O submit 427.99 13.049718
db file sequential read 316.38 9.646569
log file parallel write 254.10 7.747631
CPU Utilization 145.09 4.423880
resmgr:cpu quantum 121.26 3.697187
os thread startup 91.94 2.803251
db file scattered read 61.88 1.886839
log file sequential read 58.29 1.777282
log file sync 39.11 1.192485
Data file init write 31.37 .956444
job scheduler coordinator slave wait 14.21 .433315
library cache load lock 9.20 .280569
control file sequential read 6.41 .195361
local write wait 4.91 .149641
enq: CR - block range reuse ckpt 4.26 .129954
control file heartbeat 4.00 .121968
latch: shared pool 3.48 .106171
db file parallel read 3.21 .097825
enq: CF - contention 2.95 .090046
read by other session 2.78 .084885
ADR block file read 2.47 .075282
rdbms ipc reply 2.24 .068443
utl_file I/O 2.10 .064175
ARCH wait for process start 3 2.00 .061010
Disk file operations I/O 1.86 .056810
ADR block file write 1.84 .056205
Parameter File I/O 1.58 .048153
Log archive I/O 1.26 .038356
cursor: pin S wait on X 1.18 .035971
direct path sync .86 .026246
JS coord start wait .86 .026147
SQL*Net break/reset to client .73 .022267
db file single write .60 .018347
checkpoint completed .43 .013183
recovery read .34 .010387
direct path read .34 .010383
enq: JS - queue lock .34 .010249
row cache lock .33 .009923
reliable message .17 .005181
latch: enqueue hash chains .17 .005119
RMAN backup & recovery I/O .16 .004989
Disk file Mirror/Media Repair Write .15 .004546
log file single write .12 .003611
log buffer space .10 .003156
kksfbc child completion .10 .003079
resmgr:internal state change .10 .003065
JS kgl get object wait .10 .003051
enq: RO - fast object reuse .10 .002963
asynch descriptor resize .09 .002878
LGWR wait for redo copy .08 .002580
ADR file lock .08 .002286
SGA: allocation forcing component growth .07 .002011
direct path write .05 .001602
enq: PR - contention .05 .001598
CRS call completion .04 .001141
latch: call allocation .02 .000728
control file single write .02 .000723
undo segment extension .02 .000676
direct path write temp .01 .000394
Streams AQ: qmn coordinator waiting for .01 .000354
slave to start

buffer busy waits .01 .000277
latch free .01 .000204
latch: object queue header operation .01 .000200
latch: row cache objects .00 .000131
instance state change .00 .000098
latch: cache buffers chains .00 .000042
library cache: mutex X .00 .000036
latch: cache buffers lru chain .00 .000030
enq: SQ - contention .00 .000024
latch: redo allocation .00 .000005
ARCH wait for archivelog lock .00 .000004
latch: redo writing .00 .000004

Oracle Role info

Oracle role list




CREATE OR REPLACE PROCEDURE LIST_USER_ROLE_PRIVS(UNAME VARCHAR2)
IS
U1 VARCHAR2(100):=UNAME;

CURSOR one IS SELECT u1.name USERNAME ,U2.NAME ROLENAME ,SUBSTR(SPM.NAME,1,27) PRIVILEGE
FROM SYS.SYSAUTH$ SA1, SYS.SYSAUTH$ SA2,SYS.USER$ U1, SYS.USER$ U2,SYS.SYSTEM_PRIVILEGE_MAP SPM
WHERE SA1.GRANTEE# = U1.USER#
AND SA1.PRIVILEGE# = U2.USER#
AND U2.USER# = SA2.GRANTEE# (+)
AND SA2.PRIVILEGE# = SPM.PRIVILEGE (+) AND
(U1.NAME IN
(SELECT GRANTEE FROM DBA_ROLE_PRIVS connect by prior
granted_role=GRANTEE start with GRANTEE IN
(SELECT NAME FROM USER$ WHERE user# in
(select privilege# from sysauth$ t1, user$ t2
where t1.grantee#=t2.user# and t2.name=U1)
)
UNION
SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS connect by prior
granted_role=GRANTEE start with GRANTEE IN
(SELECT NAME FROM USER$ WHERE user# in
(select privilege# from sysauth$ t1, user$ t2
where t1.grantee#=t2.user# and t2.name=U1)
)
)
OR U1.NAME=U1
)
ORDER BY U2.USER#, U2.NAME ;

CURSOR two IS select u.name username,spm.name privilege
from user$ u, sysauth$ s, system_privilege_map spm
where u.user#=s.grantee# and s.privilege#=spm.privilege
AND U.NAME=U1
ORDER BY 1,2;

BEGIN
dbms_output.put_line(rpad('USERNAME',30,' ')||rPAD('ROLENAME',21,' ')||rPAD('PRIVILEGE',21,' '));
DBMS_OUTPUT.PUT_LINE('----------------------------- -------------------- --------------------');
for y in one loop
dbms_output.put_line(rPAD(y.USERNAME,30,' ')||rPAD(y.ROLENAME,21,' ')||rPAD(y.PRIVILEGE,21,' '));
end loop;
for x in two loop
dbms_output.put_line(rPAD(x.username,51,' ')||rPAD(x.privilege,21,' '));
end loop;

END;
/


undef username
set verify on






SQL> set serveroutput on
SQL> exec LIST_USER_ROLE_PRIVS('SCOTT');

USERNAME ROLENAME PRIVILEGE
----------------------------- -------------------- --------------------
SCOTT CONNECT CREATE SESSION
SCOTT RESOURCE CREATE INDEXTYPE
SCOTT RESOURCE CREATE OPERATOR
SCOTT RESOURCE CREATE TYPE
SCOTT RESOURCE CREATE TABLE
SCOTT RESOURCE CREATE PROCEDURE
SCOTT RESOURCE CREATE SEQUENCE
SCOTT RESOURCE CREATE CLUSTER
SCOTT RESOURCE CREATE TRIGGER
SCOTT DBA CREATE USER
SCOTT DBA UNLIMITED TABLESPACE
SCOTT DBA DROP TABLESPACE
SCOTT DBA MANAGE TABLESPACE
SCOTT DBA ALTER TABLESPACE
SCOTT DBA CREATE TABLESPACE
SCOTT DBA RESTRICTED SESSION
SCOTT DBA ALTER SESSION
SCOTT DBA CREATE SESSION
SCOTT DBA AUDIT SYSTEM
SCOTT DBA ALTER SYSTEM

Oracle Users' System Privileges

Oracle Users' System Privileges
Oracle role PRIVILEGE

Oracle Role


set linesize 200
col PRIVILEGE format a30
col ROLENAME format a20



-- CREATE OR REPLACE VIEW DBA_USER_PRIVS (USERNAME, ROLENAME, PRIVILEGE) AS
SELECT DECODE(SA1.GRANTEE#, 1, 'PUBLIC', U1.NAME), SUBSTR(U2.NAME,1,20), SUBSTR(SPM.NAME,1,27)
FROM SYS.SYSAUTH$ SA1, SYS.SYSAUTH$ SA2, SYS.USER$ U1,
SYS.USER$ U2, SYS.SYSTEM_PRIVILEGE_MAP SPM
WHERE SA1.GRANTEE# = U1.USER#
AND SA1.PRIVILEGE# = U2.USER#
AND U2.USER# = SA2.GRANTEE#
AND SA2.PRIVILEGE# = SPM.PRIVILEGE
UNION
SELECT U.NAME, NULL, SUBSTR(SPM.NAME,1,27)
FROM SYS.SYSTEM_PRIVILEGE_MAP SPM, SYS.SYSAUTH$ SA, SYS.USER$ U
WHERE SA.GRANTEE#=U.USER#
AND SA.PRIVILEGE#=SPM.PRIVILEGE
/


USERNAME ROLENAME PRIVILEGE
------------------------------ -------------------- ------------------------------
VIHAAN RESOURCE CREATE INDEXTYPE
VIHAAN RESOURCE CREATE OPERATOR
VIHAAN RESOURCE CREATE PROCEDURE
VIHAAN RESOURCE CREATE SEQUENCE
VIHAAN RESOURCE CREATE TABLE
VIHAAN RESOURCE CREATE TRIGGER
VIHAAN RESOURCE CREATE TYPE
VIHAAN UNLIMITED TABLESPACE
WMSYS CONNECT CREATE SESSION

Oracle Corruption mater note ID metalink id 1088018.1

Master Note for Handling Oracle Database Corruption Issues [ID 1088018.1]

Oracle re create user

recreate oracle user
create oracle user
re-create oracle user
re create oracle schema
re create oracle user
user re create script


from web
http://www.optimaldba.com/scripts/extract_schema_ddl.sql

schema.sql --------------------<<<<<<<<<<<<<<<<<<<<<<<<<<


@schema
Enter value for 1: SCOTT




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

















---------------------------------------------------

SQL> @schema
Enter value for 1: SCOTT





WHENEVER SQLERROR EXIT FAILURE
WHENEVER OSERROR EXIT FAILURE
SPOOL schema_ORCL_SCOTT_20111010_ddl.log


-- Object Count
-- FUNCTION -- 1
-- INDEX -- 5
-- TABLE -- 7
-- UNDEFINED -- 3


-- Profile Creation

CREATE PROFILE "MONITORING_PROFILE"
LIMIT
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER DEFAULT
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME DEFAULT
CONNECT_TIME DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME DEFAULT
PASSWORD_REUSE_TIME DEFAULT
PASSWORD_REUSE_MAX DEFAULT
PASSWORD_VERIFY_FUNCTION DEFAULT
PASSWORD_LOCK_TIME DEFAULT
PASSWORD_GRACE_TIME DEFAULT ;



-- User Creation

CREATE USER "SCOTT" IDENTIFIED BY VALUES
'S:BBC0FB9CB71F042D7FB057C69F207243244DE2A7003614142
E9CA4D76467;F894844C34402B67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";



-- User Tablespace Quotas


-- User Role

GRANT "DBA" TO "SCOTT";
GRANT "RESOURCE" TO "SCOTT";
GRANT "CONNECT" TO "SCOTT";

-- User System Privileges

GRANT UNLIMITED TABLESPACE TO "SCOTT";



-- User Object Privileges

GRANT EXECUTE ON "SYS"."DBMS_REDEFINITION" TO
"SCOTT";



-- Schema Sequences


-- Schema Database Links


-- Schema Directories


-- Schema Tables

CREATE TABLE "SCOTT"."ANUJ10"
( "FIRST_NAME" VARCHAR2(128),
"LAST_NAME" VARCHAR2(128),
"EMPID" NUMBER,
"SALARY" NUMBER(6,0) ENCRYPT USING 'AES192'
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;


CREATE TABLE "SCOTT"."BONUS"
( "ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"SAL" NUMBER,
"COMM" NUMBER
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;


CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;


CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;


CREATE TABLE "SCOTT"."MYEMP"
( "EMPID" NUMBER,
"ENAME" VARCHAR2(30),
"SALARY" NUMBER(8,2),
"DEPTNO" NUMBER,
PRIMARY KEY ("EMPID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;


CREATE TABLE "SCOTT"."MYEMP_WORK"
( "EMP#" NUMBER,
"ENAME" VARCHAR2(30),
"SALARY" NUMBER(8,2),
"DEPTNO" NUMBER,
PRIMARY KEY ("EMP#")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
TABLESPACE "USERS"
PARTITION BY LIST ("DEPTNO")
(PARTITION "P10" VALUES (10)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "P20" VALUES (20)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "P30" VALUES (30, 40)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
TABLESPACE "USERS" NOCOMPRESS ) ;


CREATE TABLE "SCOTT"."SALGRADE"
( "GRADE" NUMBER,
"LOSAL" NUMBER,
"HISAL" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;



-- Schema Table RI Constraints


-- Schema Indexes

CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON
"SCOTT"."DEPT" ("DEPTNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE
STATISTICS
TABLESPACE "USERS" ;


CREATE UNIQUE INDEX "SCOTT"."SYS_C0022541" ON
"SCOTT"."MYEMP" ("EMPID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE
STATISTICS
TABLESPACE "USERS" ;


CREATE UNIQUE INDEX "SCOTT"."SYS_C0022542" ON
"SCOTT"."MYEMP_WORK" ("EMP#")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE
STATISTICS
TABLESPACE "USERS" ;


CREATE UNIQUE INDEX "SCOTT"."SYS_C0022543" ON
"SCOTT"."EMP" ("EMPNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE
STATISTICS
TABLESPACE "USERS" ;



-- Schema Views


-- Schema Functions

CREATE OR REPLACE FUNCTION "SCOTT"."RAISE_SAL" (salary NUMBER) RETURN NUMBER AS
BEGIN
return salary + salary*0.10;
END;
/



-- Schema Packages (specs and body)


-- Schema Procedures


-- Schema Synonyms


-- End of ddl

SET LINESIZE 132 PAGESIZE 45 FEEDBACK OFF
COLUMN line FORMAT 9999
COLUMN text FORMAT A40 WORD_WRAP

-- Checking for errors

SELECT name, type, line, text
FROM dba_errors
WHERE owner = 'SCOTT'
/
SPOOL OFF

Oracle AWR run manully

Oracle AWR report run



from web
http://www.optimaldba.com/scripts/cust_awr_10g.sql



-- awr_report.sql


---===========================================


SET LINESIZE 152 PAGESIZE 45 FEEDBACK off VERIFY off
TTITLE off
CLEAR SCREEN

-- Generic Columns
COLUMN rpt_db_time_pct FORMAT A7 HEADING 'DB Time' JUSTIFY RIGHT
COLUMN rpt_event_name FORMAT A50 HEADING 'Event'
COLUMN rpt_spacer FORMAT A32 HEADING ""

COLUMN rpt_sql_id FORMAT A14 HEADING 'SQL ID'
COLUMN rpt_sql_elapsed FORMAT A26 HEADING 'Elapsed Time (us)'
COLUMN rpt_sql_cpu FORMAT A26 HEADING 'CPU Time (us)'
COLUMN rpt_sql_lios FORMAT A26 HEADING 'Logical I/O'
COLUMN rpt_sql_pios FORMAT A26 HEADING 'Physical I/O'
COLUMN rpt_sql_execs_num FORMAT 999,999,999 HEADING 'Executions'
COLUMN rpt_sql_rows_num FORMAT 999,999,999,999 HEADING 'Rows'

COLUMN rpt_stat_name FORMAT A30 HEADING 'Statistic'
COLUMN rpt_stat_value FORMAT 999,999,999,999 HEADING 'Value'

COLUMN rpt_val_minutes FORMAT 999,999,999.99 HEADING 'Minutes'
COLUMN rpt_val_seconds FORMAT 999,999,999.99 HEADING 'Seconds'
COLUMN rpt_val_centi FORMAT 999,999,999 HEADING 'Centiseconds'
COLUMN rpt_val_milli FORMAT 999,999,999,999 HEADING 'Milliseconds'
COLUMN rpt_val_micro FORMAT 999,999,999,999,999 HEADING 'Microseconds'

COLUMN rpt_val_count FORMAT 999,999,999 HEADING 'Count'
COLUMN rpt_val_name FORMAT A40
COLUMN rpt_val_pct FORMAT 999,990.99 HEADING 'Pct'
COLUMN rpt_val_per_second FORMAT 999,999,999.99 HEADING 'Per Second'
COLUMN rpt_val_per_tx FORMAT 999,999,999.99 HEADING 'Per Trans'
COLUMN sort_order FORMAT 99999 NOPRINT


SET PAGESIZE 0

/*
Get Current database and instance to use as default values
*/

COLUMN curr_db_id FORMAT 999999999999 HEADING 'DBID' NEW_VALUE curr_db_id NOPRINT
COLUMN curr_db_name FORMAT A10 HEADING 'DB Name' NEW_VALUE curr_db_name NOPRINT
COLUMN curr_inst_id FORMAT 999 HEADING 'InstID' NEW_VALUE curr_inst_id NOPRINT
COLUMN curr_inst_name FORMAT A10 HEADING 'Instance Name' NEW_VALUE curr_inst_name NOPRINT


SELECT d.dbid curr_db_id
, d.name curr_db_name
FROM sys.v$database d
/

SELECT i.instance_number curr_inst_id
, i.instance_name curr_inst_name
FROM v$instance i
/

SET VERIFY OFF

SET PAGESIZE 45

/*
Database Selection
*/

COLUMN awr_db_id FORMAT A12 HEADING 'DBID' NEW_VALUE awr_db_id
COLUMN awr_db_name FORMAT A10 HEADING 'DB Name' NEW_VALUE awr_db_name
COLUMN awr_db_version FORMAT A10 HEADING 'Version'
COLUMN awr_db_rac FORMAT A03 HEADING 'RAC'

PROMPT
PROMPT *** Databases in AWR Repository ***
PROMPT

SELECT wdi.db_name||CASE WHEN wdi.db_name = '&curr_db_name' THEN '*' END awr_db_name
, RPAD(wdi.dbid,12) awr_db_id
, wdi.version awr_db_version
, REPLACE(wdi.parallel, 'YES', 'RAC') awr_db_rac
FROM sys.wrm$_database_instance wdi
WHERE wdi.instance_number = 1
AND wdi.startup_time = ( SELECT MAX(wdi2.startup_time)
FROM sys.wrm$_database_instance wdi2
WHERE wdi.dbid = wdi2.dbid
AND wdi.instance_number = wdi2.instance_number
)
ORDER BY wdi.db_name
/

PROMPT
ACCEPT usr_db_name PROMPT 'Database Name <&curr_db_name> : ' DEFAULT &curr_db_name

-- Instance Selection
COLUMN awr_inst_host_name FORMAT A30 HEADING 'Host Name'
COLUMN awr_inst_id FORMAT A4 HEADING 'Inst|ID' NEW_VALUE awr_inst_id
COLUMN awr_inst_name FORMAT A10 HEADING 'Instance|Name' NEW_VALUE awr_inst_name
COLUMN awr_inst_start_time FORMAT A30 HEADING 'Start Time'

PROMPT
PROMPT *** Instances for &usr_db_name in AWR Repository ***
PROMPT

SELECT wdi.instance_number||CASE WHEN wdi.instance_name = '&curr_inst_name' THEN '*' END awr_inst_id
, wdi.instance_name awr_inst_name
, wdi.startup_time awr_inst_start_time
, wdi.host_name awr_inst_host_name
FROM sys.wrm$_database_instance wdi
WHERE UPPER(wdi.db_name) = UPPER('&usr_db_name')
AND wdi.startup_time = ( SELECT MAX(wdi2.startup_time)
FROM sys.wrm$_database_instance wdi2
WHERE wdi.dbid = wdi2.dbid
AND wdi.instance_number = wdi2.instance_number
)
ORDER BY wdi.instance_number
/

PROMPT
ACCEPT usr_inst_name PROMPT 'Instance ID <&curr_inst_name> : ' DEFAULT &curr_inst_name

SET HEADING OFF
COLUMN rpt_db_id FORMAT 999999999999 HEADING 'DBID' NEW_VALUE rpt_db_id NOPRINT
COLUMN rpt_inst_id FORMAT A4 HEADING 'Inst|ID' NEW_VALUE rpt_inst_id NOPRINT

SELECT DISTINCT wdi.dbid rpt_db_id
, wdi.instance_number rpt_inst_id
FROM sys.wrm$_database_instance wdi
WHERE UPPER(wdi.db_name) = UPPER('&usr_db_name')
AND UPPER(wdi.instance_name) = UPPER('&usr_inst_name')
/
SET HEADING ON

SET HEADING OFF
COLUMN snap_int_min FORMAT A100
COLUMN snap_retention FORMAT A100 FOLD_BEFORE
SELECT 'Current Snapshot Interval for &usr_db_name ==> '||EXTRACT(minute FROM snap_interval)||' minutes' snap_int_min
, 'Current Snapshot Retention for &usr_db_name ==> '||EXTRACT(day FROM retention)||' days' snap_retention
FROM sys.wrm$_wr_control
WHERE dbid = &rpt_db_id
/
SET HEADING ON

@awr_snap_wkly_matrix &rpt_db_id &rpt_inst_id

SET PAGESIZE 45

ACCEPT usr_begin_snap PROMPT 'Enter Beginning Snapshot ID : '
ACCEPT usr_end_snap PROMPT 'Enter Ending Snapshot ID : '

/*
Set spool name as "cust_awr_dbid_instid_begintime_endtime.lst
*/

/*
Get report times
Elapsed
DB Time
*/

COLUMN elapsed_time_minutes FORMAT 9999999999999999 HEADING 'Elapsed Time|Minutes' NEW_VALUE elapsed_time_minutes NOPRINT
COLUMN elapsed_time_seconds FORMAT 9999999999999999 HEADING 'Elapsed Time|Seconds' NEW_VALUE elapsed_time_seconds NOPRINT

SELECT ROUND(SUM(EXTRACT (DAY FROM (sn.end_interval_time - sn.begin_interval_time)) * 86400 +
EXTRACT (HOUR FROM (sn.end_interval_time - sn.begin_interval_time)) * 3600 +
EXTRACT (MINUTE FROM (sn.end_interval_time - sn.begin_interval_time)) * 60 +
ROUND(EXTRACT (SECOND FROM (sn.end_interval_time - sn.begin_interval_time)))
)/60) elapsed_time_minutes
, SUM(EXTRACT (DAY FROM (sn.end_interval_time - sn.begin_interval_time)) * 86400 +
EXTRACT (HOUR FROM (sn.end_interval_time - sn.begin_interval_time)) * 3600 +
EXTRACT (MINUTE FROM (sn.end_interval_time - sn.begin_interval_time)) * 60 +
ROUND(EXTRACT (SECOND FROM (sn.end_interval_time - sn.begin_interval_time)))
) elapsed_time_seconds
FROM sys.wrm$_snapshot sn
WHERE sn.snap_id between (&usr_begin_snap + 1) and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
/

SET HEADING off
COLUMN db_time_stat_id FORMAT 9999999999999999 NEW_VALUE db_time_stat_id NOPRINT

SELECT sname.stat_id db_time_stat_id
FROM wrh$_stat_name sname
WHERE sname.stat_name = 'DB time'
AND sname.dbid = &rpt_db_id
/

COLUMN total_db_time_micro FORMAT 9999999999999999999 NEW_VALUE total_db_time_micro NOPRINT
COLUMN total_db_time_minutes FORMAT 9999999999999999 HEADING 'DB Time|Minutes' NEW_VALUE total_db_time_minutes NOPRINT
COLUMN total_db_time_seconds FORMAT 9999999999999999 HEADING 'DB Time|Seconds' NEW_VALUE total_db_time_seconds NOPRINT

WITH db_time_q
AS ( SELECT wtm.value - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap
THEN 0
ELSE LAG(wtm.value) OVER (PARTITION BY sn.startup_time
ORDER BY sn.snap_id) END),wtm.value) snap_db_time
FROM sys.wrh$_sys_time_model wtm
, sys.wrm$_snapshot sn
WHERE wtm.stat_id = &db_time_stat_id
AND wtm.snap_id between &usr_begin_snap and &usr_end_snap
AND wtm.dbid = &rpt_db_id
AND wtm.instance_number = &rpt_inst_id
AND sn.snap_id = wtm.snap_id
AND sn.dbid = wtm.dbid
AND sn.instance_number = wtm.instance_number
)
SELECT SUM(snap_db_time) total_db_time_micro
, ROUND(SUM(snap_db_time)/60000000) total_db_time_minutes
, ROUND(SUM(snap_db_time)/1000000) total_db_time_seconds
FROM db_time_q
/

SET HEADING ON PAGESIZE 45
COLUMN time_desc FORMAT A20 HEADING ""

PROMPT
PROMPT *** Report Times ***
PROMPT

SELECT 'Elapsed Time' time_desc
, &elapsed_time_minutes rpt_val_minutes
, &elapsed_time_seconds rpt_val_seconds
FROM dual
UNION ALL
SELECT 'Database Time' time_desc
, &total_db_time_minutes rpt_val_minutes
, &total_db_time_seconds rpt_val_seconds
FROM dual
ORDER BY time_desc DESC
/



SET HEADING off
COLUMN rpt_transactions FORMAT 9999999999999999 HEADING 'Transactions' NEW_VALUE rpt_transactions NOPRINT

WITH sysstat_value_q
AS ( SELECT wsv.value - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0
ELSE LAG(wsv.value) OVER (PARTITION BY sn.startup_time, wsv.stat_name
ORDER BY sn.snap_id) END),wsv.value) snap_metric_value
FROM dba_hist_sysstat wsv
, sys.wrm$_snapshot sn
WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND wsv.snap_id = sn.snap_id
AND wsv.dbid = sn.dbid
AND wsv.instance_number = sn.instance_number
AND wsv.stat_name IN ('user commits', 'user rollbacks')
)
SELECT SUM(svq.snap_metric_value) rpt_transactions
FROM sysstat_value_q svq
/
SET HEADING on

PROMPT
PROMPT
PROMPT
PROMPT *** Load Profile ***
PROMPT

WITH sysstat_value_q
AS ( SELECT wsv.stat_name
, wsv.value - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0
ELSE LAG(wsv.value) OVER (PARTITION BY sn.startup_time, wsv.stat_name
ORDER BY sn.snap_id) END),wsv.value) snap_metric_value
FROM dba_hist_sysstat wsv
, sys.wrm$_snapshot sn
WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND wsv.snap_id = sn.snap_id
AND wsv.dbid = sn.dbid
AND wsv.instance_number = sn.instance_number
AND wsv.stat_name IN ('user calls', 'execute count',
'parse count (total)', 'parse count (hard)',
'session logical reads', 'db block changes',
'redo size', 'physical reads', 'physical writes',
'recursive calls', 'sorts (memory)', 'sorts (disk)')
),
sysstat_value_summary
AS ( SELECT CASE WHEN svq.stat_name LIKE 'sorts%' THEN 'sorts'
ELSE svq.stat_name
END stat_name
, SUM(svq.snap_metric_value) rpt_value
, ROUND((SUM(svq.snap_metric_value) / &elapsed_time_seconds),2) rpt_val_per_second
, ROUND((SUM(svq.snap_metric_value) / &rpt_transactions),2) rpt_val_per_tx
FROM sysstat_value_q svq
GROUP BY CASE WHEN svq.stat_name LIKE 'sorts%' THEN 'sorts'
ELSE svq.stat_name
END
UNION ALL
SELECT 'transactions' stat_name
, &rpt_transactions rpt_value
, ROUND((&rpt_transactions / &elapsed_time_seconds),2) rpt_val_per_second
, NULL
FROM dual
)
SELECT DECODE(svs.stat_name,
'transactions', 001,
'user calls', 002,
'recursive calls', 003,
'execute count', 004,
'parse count (total)', 005,
'parse count (hard)', 006,
'session logical reads', 007,
'physical reads', 008,
'physical writes',009,
'db block changes',010,
'redo size',011,
'sorts',012,
9999) sort_order
, svs.stat_name rpt_stat_name
, svs.rpt_value rpt_stat_value
, svs.rpt_val_per_second rpt_val_per_second
, svs.rpt_val_per_tx rpt_val_per_tx
FROM sysstat_value_summary svs
ORDER BY sort_order
/

PROMPT
PROMPT
PROMPT
PROMPT *** Operating System Statistics ***
PROMPT
PROMPT *** O/S Configuration (end) ***
PROMPT

SELECT wsv.stat_name rpt_stat_name
, wsv.value rpt_stat_value
FROM dba_hist_osstat wsv
, sys.wrm$_snapshot sn
WHERE sn.snap_id = &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND wsv.snap_id = sn.snap_id
AND wsv.dbid = sn.dbid
AND wsv.instance_number = sn.instance_number
AND wsv.stat_name IN ('NUM_CPUS', 'PHYSICAL_MEMORY_BYTES')
/

PROMPT
PROMPT
PROMPT
PROMPT *** O/S Time ***
PROMPT

COLUMN qry_val_per_second FORMAT 999,999,999.99 HEADING 'Seconds|Per Second'

WITH sysstat_value_q
AS ( SELECT wsv.stat_name
, wsv.value - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0
ELSE LAG(wsv.value) OVER (PARTITION BY sn.startup_time, wsv.stat_name
ORDER BY sn.snap_id) END),wsv.value) snap_metric_value
FROM dba_hist_osstat wsv
, sys.wrm$_snapshot sn
WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND wsv.snap_id = sn.snap_id
AND wsv.dbid = sn.dbid
AND wsv.instance_number = sn.instance_number
AND wsv.stat_name IN ('BUSY_TIME', 'IDLE_TIME', 'SYS_TIME', 'USER_TIME')
)
SELECT DECODE(svq.stat_name,
'BUSY_TIME', 001,
'SYS_TIME', 002,
'USER_TIME', 003,
'IDLE_TIME', 004,
9999) sort_order
, svq.stat_name rpt_stat_name
, SUM(svq.snap_metric_value) rpt_val_centi
, CASE WHEN svq.stat_name LIKE '%TIME' THEN SUM(svq.snap_metric_value)/100 END rpt_val_seconds
, ROUND(((CASE WHEN svq.stat_name LIKE '%TIME' THEN SUM(svq.snap_metric_value)/100 END) / &elapsed_time_seconds),4) qry_val_per_second
FROM sysstat_value_q svq
GROUP BY svq.stat_name
ORDER BY sort_order
/

PROMPT
PROMPT
PROMPT
PROMPT *** Time Model Statistics ***
PROMPT

WITH sysstat_value_q
AS ( SELECT wsv.stat_name
, wsv.value - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0
ELSE LAG(wsv.value) OVER (PARTITION BY sn.startup_time, wsv.stat_name
ORDER BY sn.snap_id) END),wsv.value) snap_metric_value
FROM dba_hist_sys_time_model wsv
, sys.wrm$_snapshot sn
WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND wsv.snap_id = sn.snap_id
AND wsv.dbid = sn.dbid
AND wsv.instance_number = sn.instance_number
AND wsv.stat_name IN ('DB CPU', 'DB time', 'sql execute elapsed time',
'parse time elapsed', 'hard parse elapsed time',
'PL/SQL execution elapsed time', 'background elapsed time',
'background cpu time')
)
SELECT DECODE(svq.stat_name,
'DB time', 001,
'DB CPU', 002,
'sql execute elapsed time', 003,
'parse time elapsed', 004,
'hard parse elapsed time', 005,
'PL/SQL execution elapsed time', 006,
'background elapsed time', 007,
'background cpu time', 008,
9999) sort_order
, svq.stat_name rpt_stat_name
, SUM(svq.snap_metric_value) rpt_val_micro
, SUM(svq.snap_metric_value)/1000000 rpt_val_seconds
, CASE WHEN svq.stat_name IN ('DB CPU','sql execute elapsed time','parse time elapsed',
'hard parse elapsed time', 'PL/SQL execution elapsed time')
THEN LPAD(TO_CHAR(ROUND((SUM(svq.snap_metric_value)/&total_db_time_micro),4)*100)||'%',7)
END rpt_db_time_pct
FROM sysstat_value_q svq
GROUP BY svq.stat_name
ORDER BY sort_order
/

PROMPT
PROMPT
PROMPT
PROMPT *** Top 10 Timed Events ***
PROMPT

COLUMN rpt_avg_micro FORMAT 999,999.99 HEADING 'Avg Time|(micro)'
COLUMN rpt_event_count FORMAT 999,999,999,999 HEADING 'Event Count'
WITH sysstat_value_q
AS ( SELECT wsv.event_name
, wsv.time_waited_micro - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0
ELSE LAG(wsv.time_waited_micro) OVER (PARTITION BY sn.startup_time, wsv.event_name
ORDER BY sn.snap_id) END),wsv.time_waited_micro) snap_wait_time
, wsv.total_waits - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0
ELSE LAG(wsv.total_waits) OVER (PARTITION BY sn.startup_time, wsv.event_name
ORDER BY sn.snap_id) END),wsv.total_waits) snap_wait_count
FROM dba_hist_system_event wsv
, sys.wrm$_snapshot sn
WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND wsv.snap_id = sn.snap_id
AND wsv.dbid = sn.dbid
AND wsv.instance_number = sn.instance_number
AND wsv.wait_class != 'Idle'
UNION ALL
SELECT 'CPU time' stat_name
, wsv.value - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0
ELSE LAG(wsv.value) OVER (PARTITION BY sn.startup_time, wsv.stat_name
ORDER BY sn.snap_id) END),wsv.value) snap_wait_time
, NULL snap_wait_count
FROM dba_hist_sys_time_model wsv
, sys.wrm$_snapshot sn
WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND wsv.snap_id = sn.snap_id
AND wsv.dbid = sn.dbid
AND wsv.instance_number = sn.instance_number
AND wsv.stat_name = 'DB CPU'
),
sysstat_wait_summary
AS ( SELECT svq.event_name
, SUM(svq.snap_wait_time) event_value
, DENSE_RANK() OVER (ORDER BY SUM(svq.snap_wait_time) DESC) event_rank
, SUM(svq.snap_wait_count) event_count
FROM sysstat_value_q svq
GROUP BY svq.event_name
),
sysstat_wait_pcts
AS ( SELECT sws.event_name
, RATIO_TO_REPORT(sws.event_value) OVER () event_pct
FROM sysstat_wait_summary sws
)
SELECT sws.event_rank sort_order
, sws.event_name rpt_event_name
, ROUND((sws.event_value/1000000),2) rpt_val_seconds
, ROUND((swp.event_pct*100),2) rpt_val_pct
, ROUND(sws.event_value/sws.event_count)/1000 rpt_avg_micro
, sws.event_count rpt_event_count
FROM sysstat_wait_summary sws
, sysstat_wait_pcts swp
WHERE sws.event_rank <= 10
AND sws.event_name = swp.event_name
ORDER BY sws.event_rank ASC
/

PROMPT
PROMPT
PROMPT
PROMPT *** SQL Ranking Overview ***
PROMPT

WITH sql_stats_summary
AS ( SELECT sqs.sql_id
, CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.buffer_gets_delta
END lios
, CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.disk_reads_delta
END pios
, CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.elapsed_time_delta
END ela_time
, CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.cpu_time_delta
END cpu_time
, CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.executions_delta
END execs
, CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.rows_processed_delta
END q_rows
FROM dba_hist_sqlstat sqs
, sys.wrm$_snapshot sn
WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND sqs.snap_id = sn.snap_id
AND sqs.dbid = sn.dbid
AND sqs.instance_number = sn.instance_number
),
sql_ranking
AS ( SELECT sqs.sql_id
, SUM(lios) lios
, DENSE_RANK() OVER (ORDER BY SUM(lios) DESC) lios_rank
, SUM(pios) pios
, DENSE_RANK() OVER (ORDER BY SUM(pios) DESC) pios_rank
, SUM(ela_time) ela_time
, DENSE_RANK() OVER (ORDER BY SUM(ela_time) DESC) ela_rank
, SUM(cpu_time) cpu_time
, DENSE_RANK() OVER (ORDER BY SUM(cpu_time) DESC) cpu_rank
, SUM(execs) execs
, SUM(q_rows) q_rows
FROM sql_stats_summary sqs
GROUP BY sqs.sql_id
),
sql_top_5
AS ( SELECT sqlr.sql_id
, sqlr.lios
, sqlr.lios_rank
, sqlr.pios
, sqlr.pios_rank
, sqlr.ela_time
, sqlr.ela_rank
, sqlr.cpu_time
, sqlr.cpu_rank
, sqlr.execs
, sqlr.q_rows
FROM sql_ranking sqlr
WHERE ( sqlr.lios_rank <= 5
OR sqlr.pios_rank <= 5
OR sqlr.ela_rank <= 5
OR sqlr.cpu_rank <= 5
)
),
sql_plan_count
AS ( SELECT sqs.sql_id
, COUNT(DISTINCT sqs.plan_hash_value) plan_count
FROM dba_hist_sqlstat sqs
, sys.wrm$_snapshot sn
, sql_top_5 st5
WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND sqs.snap_id = sn.snap_id
AND sqs.dbid = sn.dbid
AND sqs.instance_number = sn.instance_number
AND sqs.sql_id = st5.sql_id
AND sqs.executions_delta > 0
GROUP BY sqs.sql_id
)
SELECT sql5.sql_id||(CASE WHEN spc.plan_count > 1 THEN '*' ELSE NULL END) rpt_sql_id
, TO_CHAR(sql5.ela_time, '99,999,999,999,999')||LPAD('('||TO_CHAR(ela_rank)||')', 6) rpt_sql_elapsed
, TO_CHAR(sql5.cpu_time, '99,999,999,999,999')||LPAD('('||TO_CHAR(cpu_rank)||')', 6) rpt_sql_cpu
, TO_CHAR(sql5.lios, '99,999,999,999,999')||LPAD('('||TO_CHAR(lios_rank)||')', 6) rpt_sql_lios
, TO_CHAR(sql5.pios, '99,999,999,999,999')||LPAD('('||TO_CHAR(pios_rank)||')', 6) rpt_sql_pios
, sql5.execs rpt_sql_execs_num
, sql5.q_rows rpt_sql_rows_num
FROM sql_top_5 sql5
, sql_plan_count spc
WHERE sql5.sql_id = spc.sql_id (+)
ORDER BY (sql5.ela_rank + sql5.lios_rank + sql5.pios_rank + sql5.cpu_rank + sql5.wait_rank) ASC
/





SET PAGESIZE 0
BREAK ON qry_sql_id NODUP SKIP 2 ON qry_sql_text NODUP SKIP 2
COLUMN qry_sql_id FORMAT A20
COLUMN qry_sql_text FORMAT A100 WORD_WRAP FOLD_AFTER
COLUMN qry_exec_plan_hash FORMAT A36 FOLD_BEFORE
COLUMN qry_execs FORMAT A36
COLUMN qry_rows FORMAT A36
COLUMN qry_rows_per_exec FORMAT A36

COLUMN qry_spacer FORMAT A36 FOLD_BEFORE

COLUMN qry_ela_time FORMAT A36
COLUMN qry_cpu_time FORMAT A36
COLUMN qry_wait_time FORMAT A36

COLUMN qry_ela_per_exec FORMAT A36
COLUMN qry_cpu_per_exec FORMAT A36
COLUMN qry_wait_per_exec FORMAT A36

COLUMN qry_lios FORMAT A36
COLUMN qry_lios_per_exec FORMAT A36
COLUMN qry_lios_per_row FORMAT A36
COLUMN qry_pios FORMAT A36
COLUMN qry_pios_per_exec FORMAT A36
COLUMN qry_pios_per_row FORMAT A36


PROMPT
PROMPT
PROMPT
PROMPT *** SQL Executions ***
PROMPT

WITH sql_stats_summary
AS ( SELECT sqs.sql_id
, DENSE_RANK() OVER (ORDER BY SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.buffer_gets_delta
END) DESC) lios_rank
, DENSE_RANK() OVER (ORDER BY SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.disk_reads_delta
END) DESC) pios_rank
, DENSE_RANK() OVER (ORDER BY SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.elapsed_time_delta
END) DESC) ela_rank
, DENSE_RANK() OVER (ORDER BY SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.cpu_time_delta
END) DESC) cpu_rank
, DENSE_RANK() OVER (ORDER BY SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE (sqs.elapsed_time_delta - sqs.cpu_time_delta)
END) DESC) wait_rank
FROM dba_hist_sqlstat sqs
, sys.wrm$_snapshot sn
WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND sqs.snap_id = sn.snap_id
AND sqs.dbid = sn.dbid
AND sqs.instance_number = sn.instance_number
GROUP BY sqs.sql_id
),
sql_top_5
AS ( SELECT sqlr.sql_id
FROM sql_stats_summary sqlr
WHERE ( sqlr.lios_rank <= 5
OR sqlr.pios_rank <= 5
OR sqlr.ela_rank <= 5
OR sqlr.cpu_rank <= 5
OR sqlr.wait_rank <= 5
)
),
sql_plan_stats
AS ( SELECT sqs.sql_id
, sqs.plan_hash_value
, SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.buffer_gets_delta
END) lios
, SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.disk_reads_delta
END) pios
, SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.elapsed_time_delta
END) ela_time
, SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.cpu_time_delta
END) cpu_time
, SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE (sqs.elapsed_time_delta - sqs.cpu_time_delta)
END) wait_time
, SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.executions_delta
END) execs
, SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.rows_processed_delta
END) q_rows
FROM dba_hist_sqlstat sqs
, sys.wrm$_snapshot sn
, sql_top_5 sql5
WHERE sqs.sql_id = sql5.sql_id
AND sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND sqs.snap_id = sn.snap_id
AND sqs.dbid = sn.dbid
AND sqs.instance_number = sn.instance_number
GROUP BY sqs.sql_id, sqs.plan_hash_value
)
SELECT sps.sql_id qry_sql_id
, SUBSTR(sqt.sql_text, 1, 500) qry_sql_text
, 'Plan Hash ==> '||sps.plan_hash_value qry_exec_plan_hash
, 'Executions ==> '||TO_CHAR(execs,'999,999,999,999') qry_execs
, 'Rows ==> '||TO_CHAR(q_rows,'999,999,999,999') qry_rows
, 'Rows per Exec ==> '||TO_CHAR(ROUND(q_rows/DECODE(execs,0,1,execs)),'999,999,999,999') qry_rows_per_exec
, NULL qry_spacer
, 'Ela Time(s) ==> '||LPAD(TO_CHAR(ela_time/1000000,'999,990.900000'),16) qry_ela_time
, 'CPU Time(us) ==> '||LPAD(TO_CHAR(cpu_time/1000000,'999,990.900000'),16) qry_cpu_time
, 'Wait Time(us) ==> '||LPAD(TO_CHAR(wait_time/1000000,'999,990.900000'),16) qry_wait_time
, NULL qry_spacer
, 'Ela per Exec ==> '||LPAD(TO_CHAR(ROUND((ela_time/1000000)/DECODE(execs,0,1,execs),6),'999,990.900000'),16) qry_ela_per_exec
, 'CPU per Exec ==> '||LPAD(TO_CHAR(ROUND((cpu_time/1000000)/DECODE(execs,0,1,execs),6),'999,990.900000'),16) qry_cpu_per_exec
, 'Wait per Exec ==> '||LPAD(TO_CHAR(ROUND((wait_time/1000000)/DECODE(execs,0,1,execs),6),'999,990.900000'),16) qry_wait_per_exec
, NULL qry_spacer
, 'Logical I/O ==> '||TO_CHAR(lios,'999,999,999,999') qry_lios
, 'LIO per Exec ==> '||TO_CHAR(ROUND(lios/DECODE(execs,0,1,execs)),'999,999,999,999') qry_lios_per_exec
, 'LIO per Row ==> '||TO_CHAR(ROUND(lios/DECODE(q_rows,0,1,q_rows)),'999,999,999,999') qry_lios_per_row
, NULL qry_spacer
, 'Physical I/O ==> '||TO_CHAR(pios,'999,999,999,999') qry_pios
, 'PIO per Exec ==> '||TO_CHAR(ROUND(pios/DECODE(execs,0,1,execs)),'999,999,999,999') qry_pios_per_exec
, 'PIO per Row ==> '||TO_CHAR(ROUND(pios/DECODE(q_rows,0,1,q_rows)),'999,999,999,999') qry_pios_per_row
FROM sql_plan_stats sps
, dba_hist_sqltext sqt
WHERE sps.execs > 0
AND sps.sql_id = sqt.sql_id
ORDER BY sps.sql_id
, sps.ela_time DESC
/



==============================================================
snap matrix report


prompt awr_snap_wkly_matrix.sql <<<<<<<<<<<<<<<<< copy this script in same dir


COLUMN sort_ord NOPRINT
COLUMN snap_week NOPRINT

DEFINE awr_dbid = &1
DEFINE awr_instid = &2

SET HEADING off PAGESIZE 0
TTITLE off

PROMPT *** Snapshots in AWR repository for Database &usr_db_name Instance &usr_inst_name
PROMPT *** Weekly by hour (intermediate snapshots not listed)

SELECT DISTINCT 001 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, 'Sunday' sunday_snapid
, 'Monday' monday_snapid
, 'Tuesday' tuesday_snapid
, 'Wednesday' wednesday_snapid
, 'Thursday' thursday_snapid
, 'Friday' friday_snapid
, 'Saturday' saturday_snapid
FROM sys.wrm$_snapshot
UNION ALL
SELECT 010 sort_ord
, TO_CHAR(s.first_sunday, 'YYYYMMDD') snap_week
, NULL hour_of_day
, TO_CHAR(s.first_sunday, 'MM/DD/YY') sunday_snapid
, TO_CHAR(s.first_sunday+1, 'MM/DD/YY') monday_snapid
, TO_CHAR(s.first_sunday+2, 'MM/DD/YY') tuesday_snapid
, TO_CHAR(s.first_sunday+3, 'MM/DD/YY') wednesday_snapid
, TO_CHAR(s.first_sunday+4, 'MM/DD/YY') thursday_snapid
, TO_CHAR(s.first_sunday+5, 'MM/DD/YY') friday_snapid
, TO_CHAR(s.first_sunday+6, 'MM/DD/YY') saturday_snapid
FROM ( SELECT NEXT_DAY(MIN(end_interval_time) - 7, 'SUNDAY') first_sunday
FROM sys.wrm$_snapshot
WHERE dbid = &awr_dbid
AND instance_number = &awr_instid
) s
UNION ALL
SELECT 011 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) sunday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),2,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) monday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),3,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) tuesday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),4,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) wednesday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),5,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) thursday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),6,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) friday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),7,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) saturday_of_week
FROM sys.wrm$_snapshot
WHERE dbid = &awr_dbid
AND instance_number = &awr_instid
GROUP BY TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD')
HAVING MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) IS NOT NULL
UNION ALL
SELECT DISTINCT 020 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, '---------' hour_of_day
, '---------' sunday_snapid
, '---------' monday_snapid
, '---------' tuesday_snapid
, '---------' wednesday_snapid
, '---------' thursday_snapid
, '---------' friday_snapid
, '---------' saturday_snapid
FROM sys.wrm$_snapshot
WHERE dbid = &awr_dbid
AND instance_number = &awr_instid
UNION ALL
SELECT 030 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, TO_CHAR(end_interval_time, 'hh24')||':00' hour_of_day
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),1,snap_id,NULL)),'999999') sunday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),2,snap_id,NULL)),'999999') monday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),3,snap_id,NULL)),'999999') tuesday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),4,snap_id,NULL)),'999999') wednesday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),5,snap_id,NULL)),'999999') thursday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),6,snap_id,NULL)),'999999') friday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),7,snap_id,NULL)),'999999') saturday_of_week
FROM sys.wrm$_snapshot
WHERE dbid = &awr_dbid
AND instance_number = &awr_instid
GROUP BY TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD')
, TO_CHAR(end_interval_time, 'hh24')||':00'
UNION ALL
SELECT DISTINCT 999 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, NULL sunday_snapid
, NULL monday_snapid
, NULL tuesday_snapid
, NULL wednesday_snapid
, NULL thursday_snapid
, NULL friday_snapid
, NULL saturday_snapid
FROM sys.wrm$_snapshot
WHERE dbid = &awr_dbid
AND instance_number = &awr_instid
ORDER BY snap_week
, sort_ord
, hour_of_day
/

SET HEADING ON