Anuj Singh Oracle DBA: 10 Oct 2011

Monday, 10 October 2011

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

Oracle AWR snap id

Snap id
snapshot id
awr snapshot information
awr snapshot info

awr_snapid.sql

Check the current AWR interval time and retention period

select snapshot_Interval,retention_interval_minute,retention_interval_minute/60 retention_interval_hour,(retention_interval_minute/60)/24 retention_interval_day

from (SELECT extract(day from snap_interval) *24*60+extract(hour from snap_interval) *60+extract(minute from snap_interval) snapshot_Interval,

extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention) retention_interval_minute

FROM dba_hist_wr_control

)


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




 20160410 Sunday Monday Tuesday Wednesday Thursday Friday Saturday
20160410 04/10/16 04/11/16 04/12/16 04/13/16 04/14/16 04/15/16 04/16/16
20160410 --------- --------- --------- --------- --------- --------- --------- ---------
20160410 00:00 4296 4363 4398 4422
20160410 01:00 4297 4369 4399 4423
20160410 02:00 4298 4375 4400 4424
20160410 03:00 4299 4377 4401 4425
20160410 04:00 4300 4378 4402 4426
20160410 05:00 4301 4379 4403 4427
20160410 06:00 4302 4380 4404 4428
20160410 07:00 4303 4381 4405 4429
20160410 08:00 4304 4382 4406 4430
20160410 09:00 4305 4383 4407 4431
20160410 10:00 4306 4384 4408 4432
20160410 11:00 4307 4385 4409 4433
20160410 12:00 4308 4386 4410 4434
20160410 13:00 4309 4387 4411 4435
20160410 14:00 4310 4388 4412 4436
20160410 15:00 4311 4389 4413 4437
20160410 16:00 4315 4390 4414 4438
20160410 17:00 4321 4391 4415 4439
20160410 18:00 4327 4392 4416 4440
20160410 19:00 4333 4393 4417 4441
20160410 20:00 4339 4394 4418 4442
20160410 21:00 4293 4345 4395 4419 4443
20160410 22:00 4294 4351 4396 4420 4444
20160410 23:00 4295 4357 4397 4421 4445
20160410
20160417 Sunday Monday Tuesday Wednesday Thursday Friday Saturday
20160417 04/17/16 04/18/16 04/19/16 04/20/16 04/21/16
20160417 --------- --------- --------- --------- --------- --------- --------- ---------
20160417 00:00 4446 4470 4494 4518 4542
20160417 01:00 4447 4471 4495 4519 4543
20160417 02:00 4448 4472 4496 4520 4544
20160417 03:00 4449 4473 4497 4521 4545
20160417 04:00 4450 4474 4498 4522 4546
20160417 05:00 4451 4475 4499 4523 4547
20160417 06:00 4452 4476 4500 4524 4548
20160417 07:00 4453 4477 4501 4525
20160417 08:00 4454 4478 4502 4526
20160417 09:00 4455 4479 4503 4527
20160417 10:00 4456 4480 4504 4528
20160417 11:00 4457 4481 4505 4529
20160417 12:00 4458 4482 4506 4530
20160417 13:00 4459 4483 4507 4531
20160417 14:00 4460 4484 4508 4532
20160417 15:00 4461 4485 4509 4533
20160417 16:00 4462 4486 4510 4534
20160417 17:00 4463 4487 4511 4535
20160417 18:00 4464 4488 4512 4536
20160417 19:00 4465 4489 4513 4537
20160417 20:00 4466 4490 4514 4538
20160417 21:00 4467 4491 4515 4539
20160417 22:00 4468 4492 4516 4540
20160417 23:00 4469 4493 4517 4541
20160417

56 rows selected.

====


define p_inst=1
define p_days =3


set linesize 400 pages 200
set verify off
column event_name format a40

column dt heading 'Date/Hour' format a11
set linesize 500
set pages 9999	 
select * from (
select min(snap_id) as snap_id,  
		     to_char(start_time,'MM/DD/YY') as dt, to_char(start_time,'HH24') as hr
	from (
	select snap_id, s.instance_number, begin_interval_time start_time, 
		   end_interval_time end_time, snap_level, flush_elapsed,
		   lag(s.startup_time) over (partition by s.dbid, s.instance_number 
		   					   order by s.snap_id) prev_startup_time,
		   s.startup_time
	from  dba_hist_snapshot s, gv$instance i
	where begin_interval_time between trunc(sysdate)-&p_days and sysdate 
	and   s.instance_number = i.instance_number
	and   s.instance_number = &p_inst
	order by snap_id
	)
	group by to_char(start_time,'MM/DD/YY') , to_char(start_time,'HH24') 
	order by snap_id, start_time )
	pivot
	(sum(snap_id)
	 for hr in ('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23')
	 )
	 order by dt;

===



define p_days=2
set linesize 200
set pages 200
set verify off
column event_name format a40

column dt heading 'Date/Hour' format a11
set linesize 500
set pages 9999	 
select * from (
select min(snap_id) as snap_id,  
		     to_char(start_time,'MM/DD/YY') as dt, to_char(start_time,'HH24') as hr
	from (
	select snap_id, s.instance_number, begin_interval_time start_time, 
		   end_interval_time end_time, snap_level, flush_elapsed,
		   lag(s.startup_time) over (partition by s.dbid, s.instance_number 
		   					   order by s.snap_id) prev_startup_time,
		   s.startup_time
	from  dba_hist_snapshot s, gv$instance i
	where begin_interval_time between trunc(sysdate)-&p_days and sysdate 
	and   s.instance_number = i.instance_number
	--and   s.instance_number = &p_inst
	order by snap_id
	)
	group by to_char(start_time,'MM/DD/YY') , to_char(start_time,'HH24') 
	order by snap_id, start_time )
	pivot
	(sum(snap_id)
	 for hr in ('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23')
	 )
	 order by dt;




set linesize 200 pagesize 200
col snaptime for a25
select dhdi.instance_name,
dhdi.db_name,
dhdi.DBID,
dhs.snap_id,
to_char(dhs.begin_interval_time,'dd/MM/YYYY:HH24:MI') begin_snap_time,
to_char(dhs.end_interval_time,'DD/MM/YYYY:HH24:MI') end_snap_time,
decode(dhs.startup_time,dhs.begin_interval_time,'**db restart**',null) db_bounce
from dba_hist_snapshot dhs, dba_hist_database_instance dhdi
where dhdi.dbid = dhs.dbid
and dhdi.instance_number = dhs.instance_number
and dhdi.startup_time = dhs.startup_time
and dhs.end_interval_time >= sysdate -2
order by db_name, instance_name, snap_id;

define  num_days     = 2;
define  db_name      = 'RAC';
define  dbid         = 1222414252;
define  begin_snap   = 10319;
define  end_snap     = 10320;
define  report_type  = 'html';
define  instance_numbers_or_ALL = 'ALL'
define  report_name  =  awrrpt_RAC_&&begin_snap._&&end_snap..&&report_type

@?/rdbms/admin/awrgrpti

====

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

col Max_SAMPLE_TIME for a27

col Min_SAMPLE_TIME for a27

select min(SAMPLE_TIME) Min_SAMPLE_TIME,max(SAMPLE_TIME) Max_SAMPLE_TIME from gv$active_session_history

where 1=1

and IS_AWR_SAMPLE='N'

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

set linesize 200 pagesize 200

col BEGIN_INTERVAL_TIME for a28

col END_INTERVAL_TIME for a28

select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot

where 1=1

-- to_char(begin_interval_time,'DD-MON-YYYY')='26-AUG-2017'

and begin_interval_time> sysdate -1 ;

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

set linesize 200 pagesize 200

col min_BEGIN_INTERVAL_TIME for a28

col MAX_BEGIN_INTERVAL_TIME for a28

select min(snap_id),min(begin_interval_time) min_BEGIN_INTERVAL_TIME,max(snap_id),max(begin_interval_time) MAX_BEGIN_INTERVAL_TIME from dba_hist_snapshot

where 1=1

-- to_char(begin_interval_time,'DD-MON-YYYY')='26-AUG-2017'

and begin_interval_time> sysdate -1 ;

====

from web awr_os_stat.sql

define days_history=1

define inst=1

set ver off pages 50000 lines 140 tab off linesize 300 pages 9999

col end_snap_time format a30

col load format 990.00 heading "OS|Load"

col num_cpus format 9999 heading "CPU"

col mem format 999990.00 heading "Memory|(GB)"

col oscpupct format 990 heading "OS|CPU%"

col oscpuusr format 990 heading "USR%"

col oscpusys format 990 heading "SYS%"

col oscpuio format 990 heading "IO%"

BREAK ON instance_number SKIP 1

WITH

base_line AS

(

SELECT

FROM

(

SELECT

snp.instance_number,

snp.end_interval_time ,

sst.snap_id,

sst.stat_name,

sst.value

FROM

dba_hist_snapshot snp,

dba_hist_osstat sst

WHERE

sst.instance_number = snp.instance_number

AND sst.snap_id = snp.snap_id

AND snp.instance_number = decode(&inst,0,snp.instance_number,&inst)

AND snp.begin_interval_time >= TRUNC(sysdate)- &days_history

)

pivot (SUM(value) FOR (stat_name) IN (

'LOAD' AS LOAD,

'NUM_CPUS' AS NUM_CPUS,

'PHYSICAL_MEMORY_BYTES' AS PHYSICAL_MEMORY_BYTES,

'BUSY_TIME' AS BUSY_TIME,

'USER_TIME' AS USER_TIME,

'SYS_TIME' AS SYS_TIME,

'IOWAIT_TIME' AS IOWAIT_TIME))

)

SELECT

b2.instance_number,

to_char(b2.end_interval_time,'MM/DD/YY HH24:MI:SS') end_snap_time,

b2.NUM_CPUS,

round(b2.LOAD,1) LOAD,

round(b2.PHYSICAL_MEMORY_BYTES/1024/1024/1024,0) mem,

(((b2.busy_time - b1.busy_time)/100) / ((round(EXTRACT(DAY FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 1440

+ EXTRACT(HOUR FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 60

+ EXTRACT(MINUTE FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME)

+ EXTRACT(SECOND FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) / 60, 2)*60)*b2.NUM_CPUS))*100 as oscpupct,

(((b2.user_time - b1.user_time)/100) / ((round(EXTRACT(DAY FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 1440

+ EXTRACT(HOUR FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 60

+ EXTRACT(MINUTE FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME)

+ EXTRACT(SECOND FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) / 60, 2)*60)*b2.NUM_CPUS))*100 as oscpuusr,

(((b2.sys_time - b1.sys_time)/100) / ((round(EXTRACT(DAY FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 1440

+ EXTRACT(HOUR FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 60

+ EXTRACT(MINUTE FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME)

+ EXTRACT(SECOND FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) / 60, 2)*60)*b2.NUM_CPUS))*100 as oscpusys,

(((b2.iowait_time - b1.iowait_time)/100) / ((round(EXTRACT(DAY FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 1440

+ EXTRACT(HOUR FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 60

+ EXTRACT(MINUTE FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME)

+ EXTRACT(SECOND FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) / 60, 2)*60)*b2.NUM_CPUS))*100 as oscpuio

FROM

base_line b1,

base_line b2

WHERE

b1.instance_number = b2.instance_number

AND b1.snap_id + 1 = b2.snap_id

ORDER BY

1,2

;

OS Memory OS

INSTANCE_NUMBER END_SNAP_TIME CPU Load (GB) CPU% USR% SYS% IO%

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

1 03/23/23 02:00:01 224 16.20 754.00 17 17 1 0

03/23/23 03:00:08 224 27.50 754.00 16 15 1 0

03/23/23 04:00:17 224 30.40 754.00 19 18 1 0

03/23/23 05:00:59 224 94.80 754.00 27 24 3 2

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Monday, 10 October 2011

Oracle re create user

Oracle AWR run manully

Oracle AWR snap id

Oracle DBA

anuj blog Archive