High_Water_Mark.sql
SET SERVEROUTPUT ON
SET VERIFY OFF
undefine 1
undefine 2
DECLARE
CURSOR cu_tables IS
SELECT a.owner,
a.table_name
FROM all_tables a
WHERE a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
AND a.owner = Upper('&&2');
op1 NUMBER;
op2 NUMBER;
op3 NUMBER;
op4 NUMBER;
op5 NUMBER;
op6 NUMBER;
op7 NUMBER;
BEGIN
Dbms_Output.Disable;
Dbms_Output.Enable(1000000);
Dbms_Output.Put_Line('TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK');
Dbms_Output.Put_Line('------------------------------ --------------- --------------- ---------------');
FOR cur_rec IN cu_tables LOOP
Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);
Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
LPad(op3,15,' ') ||
LPad(op1,15,' ') ||
LPad(Trunc(op1-op3-1),15,' '));
END LOOP;
END;
/
SET VERIFY ON
undefine 1
undefine 2
SQL> /
Enter value for 1: EMP
Enter value for 2: SCOTT
TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK
------------------------------ --------------- --------------- ---------------
EMP 4 8 3
PL/SQL procedure successfully completed.
Tuesday, 22 November 2011
Oracle High Water Mark for the specified table ( HWM )
Oracle User constraints info insert into a table
SQL> CREATE TABLE cons_def (ID NUMBER,table_name VARCHAR2(30),DDL VARCHAR2(4000));
Table created.
SQL> CREATE SEQUENCE seq_cons START WITH 1 INCREMENT BY 1 NOCYCLE NOCACHE NOMAXVALUE;
Sequence created.
DECLARE
my_stmt VARCHAR2 (4000);
BEGIN
FOR x IN (SELECT UPPER (table_name) NAME
FROM user_tables
ORDER BY 1)
LOOP
my_stmt := NULL;
for y in (select 'alter table "' || child_tname || '"' || chr(10) ||
'add constraint "' || child_cons_name || '"' ||
chr(10) ||'foreign key ( ' || child_columns || ' ) ' ||
chr(10) || 'references "' || parent_tname || '" ( ' || parent_columns || ') '|| decode(DEFERRED ,'DEFERRED','DEFERRABLE INITIALLY DEFERRED',' ') fkey ,
x.name
from ( select a.table_name child_tname, a.constraint_name child_cons_name,
b.r_constraint_name parent_cons_name, b.deferred,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL)) child_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.table_name, a.constraint_name,b.r_constraint_name,b.deferred ) child,
( select a.constraint_name parent_cons_name,a.table_name parent_tname,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL))
parent_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type in ( 'P', 'U' )
group by a.table_name, a.constraint_name ) parent
where child.parent_cons_name = parent.parent_cons_name
and parent.parent_tname = x.name)
loop
INSERT INTO cons_def
(ID,
table_name,
DDL
)
VALUES (seq_cons.NEXTVAL,
y.NAME,
y.fkey
);
end loop;
END LOOP;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Error in creating DDL: ' || SQLERRM);
RAISE;
END;
/
SQL> select * from cons_def;
ID TABLE_NAME
---------- ------------------------------
DDL
------------------------------------------------------------------------
1 ANUJ1
alter table "ANUJ2"
add constraint "ANUJ2_FK"
foreign key ( "N" )
references "ANUJ1" ( "X")
2 DAM_PARAM_TAB$
alter table "DAM_CONFIG_PARAM$"
add constraint "DAM_CONFIG_PARAM_FK1"
foreign key ( "PARAM_ID" )
references "DAM_PARAM_TAB$" ( "PARAMETER#")
Oracle 11gr2 compression
procedure example works only in 11gR2
you will get this message in 11gR2
Please use DBMS_COMPRESSION package for estimating compression ratio
go to rdbms dir
oracle@apt-amd-02:/opt/app/oracle/product/11.2/rdbms/admin> ls -ltr dbmscomp.sql
-rw-r--r-- 1 oracle oinstall 5180 2009-07-02 13:48 dbmscomp.sql
oracle@apt-amd-02:/opt/app/oracle/product/11.2/rdbms/admin> ls -ltr prvtcomp.plb
-rw-r--r-- 1 oracle oinstall 1226 2009-08-14 22:33 prvtcomp.plb
oracle@apt-amd-02:/opt/app/oracle/product/11.2/rdbms/admin> !sql
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 22 14:48:28 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @dbmscomp.sql
Package created.
Synonym created.
Grant succeeded.
No errors.
SQL> @prvtcomp.plb
Library created.
Package body created.
No errors.
SQL> set serveroutput on
SQL>
DECLARE
blkcnt_cmp pls_integer;
blkcnt_uncmp pls_integer;
row_cmp pls_integer;
row_uncmp pls_integer;
cmp_ratio pls_integer;
comptype_str varchar2(100);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO ('USERS', 'SCOTT', 'EMP', '',
DBMS_COMPRESSION.COMP_FOR_OLTP,
blkcnt_cmp, blkcnt_uncmp, row_cmp, row_uncmp, cmp_ratio, comptype_str);
DBMS_OUTPUT.PUT_LINE('Block count compressed = ' || blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Block count uncompressed = ' || blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || row_cmp);
DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || row_uncmp);
DBMS_OUTPUT.PUT_LINE('Compression type = ' || comptype_str);
DBMS_OUTPUT.PUT_LINE('Compression ratio = '||blkcnt_uncmp/blkcnt_cmp||' to 1');
DBMS_OUTPUT.PUT_LINE('Compression ratio org= '||cmp_ratio);
END;
/
Block count compressed = 1
Block count uncompressed = 1
Row count per block compressed = 14
Row count per block uncompressed = 14
Compression type = "Compress For OLTP"
Compression ratio = 1 to 1
Compression ratio org= 1
PL/SQL procedure successfully completed.
===
DECLARE
blkcnt_cmp pls_integer;
blkcnt_uncmp pls_integer;
row_cmp pls_integer;
row_uncmp pls_integer;
cmp_ratio pls_integer;
comptype_str varchar2(100);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO ('USERS', 'SCOTT', 'ANUJ_BIG_TABLE', '',
DBMS_COMPRESSION.COMP_FOR_OLTP,
blkcnt_cmp, blkcnt_uncmp, row_cmp, row_uncmp, cmp_ratio, comptype_str);
DBMS_OUTPUT.PUT_LINE('Block count compressed = ' || blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Block count uncompressed = ' || blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || row_cmp);
DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || row_uncmp);
DBMS_OUTPUT.PUT_LINE('Compression type = ' || comptype_str);
DBMS_OUTPUT.PUT_LINE('Compression ratio = '||blkcnt_uncmp/blkcnt_cmp||' to 1');
DBMS_OUTPUT.PUT_LINE('Compression ratio org= '||cmp_ratio);
END;
/
Block count compressed = 12
Block count uncompressed = 194
Row count per block compressed = 550
Row count per block uncompressed = 34
Compression type = "Compress For OLTP"
Compression ratio = 16.16666666666666666666666666666666666667 to 1
Compression ratio org= 16
PL/SQL procedure successfully completed.
col SEGMENT_NAME format a20
select segment_name, bytes, a.blocks, compression, compress_for from dba_segments a, dba_tables b
where a.segment_name = b.table_name
and a.segment_name='ANUJ_BIG_TABLE';
SEGMENT_NAME BYTES BLOCKS COMPRESS COMPRESS_FOR
-------------------- ---------- ---------- -------- ------------
ANUJ_BIG_TABLE 26673152 3256 DISABLED
declare
lv_cmp_ratio number;
lv_comptype_str varchar2(300);
lv_BLKCNT_CMP number;
lv_BLKCNT_UNCMP number;
lv_ROW_CMP number;
lv_ROW_UNCMP number;
begin
dbms_compression.GET_COMPRESSION_RATIO(
SCRATCHTBSNAME=>'USERS', ---- TableSpace
OWNNAME=>'SCOTT', ---- User Name
TABNAME=>'ANUJ_BIG_TABLE', --- Table Name
PARTNAME =>null,
COMPTYPE =>2, ---2 means OLTP
BLKCNT_CMP=>lv_BLKCNT_CMP,
BLKCNT_UNCMP=>lv_BLKCNT_UNCMP,
ROW_CMP =>lv_ROW_CMP,
ROW_UNCMP =>lv_ROW_UNCMP,
CMP_RATIO=>lv_cmp_ratio,
COMPTYPE_STR=>lv_COMPTYPE_STR);
dbms_output.put_line('====================================================');
dbms_output.put_line('1. Compression Ratio :'||lv_cmp_ratio);
dbms_output.put_line('2. Block Count :'||lv_blkcnt_cmp);
dbms_output.put_line('3. Compression Type :'||lv_comptype_str);
dbms_output.put_line('4. Blk Count Compressed :'||lv_BLKCNT_CMP);
dbms_output.put_line('5. Blk Count Un-compressed:'||lv_BLKCNT_UNCMP);
dbms_output.put_line('6. Row Count Compressed :'||lv_row_cmp);
dbms_output.put_line('4. Row Count Un-Compressed:'||lv_row_uncmp);
dbms_output.put_line('====================================================');
end;
/
====================================================
1. Compression Ratio :17.25
2. Block Count :12
3. Compression Type :"Compress For OLTP"
4. Blk Count Compressed :12
5. Blk Count Un-compressed:207
6. Row Count Compressed :584
4. Row Count Un-Compressed:33
====================================================
PL/SQL procedure successfully completed.
Oracle Export ( Data pump ) Shell script
Oracle Export ( Data pump ) Shell script
cat oraexpdb.sh
# !/bin/bash
TODAY=`date`
ORACLE_SID=db ; export ORACLE_SID
ORACLE_BASE=/opt/oracle ; export ORACLE_BASE
ORACLE_HOME=/opt/oracle/product/10.2 ; export ORACLE_HOME
PATH=${ORACLE_HOME}/bin:$PATH ; export PATH
EXPFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.dmp; export EXPFILE
ERRFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.err; export ERRFILE
LOGFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.log ; export LOGFILE
SUCC=/opt/oracle/backup/DataPump/Backup/SUCC.txt ; export SUCC
# echo $EXPFILE
ps -ef | grep -i smon|grep -v grep|wc -l> oracle.exp
if [ `cat oracle.exp|grep -v grep|grep -i "1"|wc -l` -eq 1 ]; then
[ -f $ERRFILE ]; rm $ERRFILE
expdp "'/ as sysdba'" directory=datapump DUMPFILE=$EXPFILE SCHEMAS=PROD1,UAT2,ESEARCH logfile=$LOGFILE EXCLUDE=STATISTICS parallel=4
fi
echo "-------------- Deleting old File ----------------------"
find /opt/oracle/backup/DataPump/Backup -name "*.err" -ctime +2 -exec rm {} \;
find /opt/oracle/backup/DataPump/Backup -name "*.dmp*" -ctime +2 -exec rm {} \;
# find /opt/oracle/backup/DataPump/Backup -name "$ORACLE_SID.export.*" -mtime +2 -exec rm {} \;
echo "Done........ "
exit 0
Monday, 21 November 2011
Oracle license Info
Oracle product Info
col DESCRIPTION format a30
col name format a20
SELECT
DBID ,NAME ,VERSION ,DETECTED_USAGES ,TOTAL_SAMPLES ,CURRENTLY_USED ,FIRST_USAGE_DATE ,LAST_USAGE_DATE ,
-- LAST_SAMPLE_DATE ,LAST_SAMPLE_PERIOD ,
--SAMPLE_INTERVAL ,
DESCRIPTION
from DBA_FEATURE_USAGE_STATISTICS
where detected_usages > 0 and
( name like '%ADDM%'
or name like '%Automatic Database Diagnostic Monitor%'
or name like '%Automatic Workload Repository%'
or name like '%AWR%'
or name like '%Baseline%'
or name like '%Compression%'
or name like '%Data Guard%'
or name like '%Data Mining%'
or name like '%Database Replay%'
or name like '%EM%'
or name like '%Encrypt%'
or name like '%Exadata%'
or name like '%Flashback Data Archive%'
or name like '%Label Security%'
or name like '%OLAP%'
or name like '%Pack%'
or name like '%Partitioning%'
or name like '%Real Application Clusters%'
or name like '%SecureFile%'
or name like '%Spatial%'
or name like '%SQL Monitoring%'
or name like '%SQL Performance%'
or name like '%SQL Profile%'
or name like '%SQL Tuning%'
or name like '%SQL Access Advisor%'
or name like '%Vault%')
and CURRENTLY_USED='TRUE' ---------------------<<<<<<<<<<<<<<<<<<<<<<<
order by name;
DBID NAME VERSION DETECTED_USAGES TOTAL_SAMPLES CURRE FIRST_USA LAST_USAG DESCRIPTION
---------- -------------------- ----------------- --------------- ------------- ----- --------- --------- ------------------------------
1267852645 Automatic Maintenanc 11.2.0.1.0 40 40 TRUE 10-JAN-11 16-NOV-11 Automatic initiation of SQL Tu
e - SQL Tuning Advis ning Advisor
or
1267852645 Automatic SQL Tuning 11.2.0.1.0 39 40 TRUE 18-JAN-11 16-NOV-11 Automatic SQL Tuning Advisor h
Advisor as been used.
1267852645 Backup BASIC Compres 11.2.0.1.0 9 40 TRUE 28-JUL-11 16-NOV-11 BASIC compressed backups are b
sion eing used.
1267852645 Partitioning (system 11.2.0.1.0 40 40 TRUE 10-JAN-11 16-NOV-11 Oracle Partitioning option is
) being used - there is at least
one partitioned object create
d.
1267852645 Partitioning (user) 11.2.0.1.0 8 40 TRUE 29-SEP-11 16-NOV-11 Oracle Partitioning option is
being used - there is at least
one user partitioned object c
reated.
1267852645 SQL Profile 11.2.0.1.0 5 40 TRUE 20-OCT-11 16-NOV-11 SQL profiles have been used.
1267852645 SQL Tuning Set (syst 11.2.0.1.0 21 40 TRUE 30-JUN-11 16-NOV-11 A SQL Tuning Set has been crea
em) ted in the database in the SYS
schema.
1267852645 SecureFiles (system) 11.2.0.1.0 40 40 TRUE 10-JAN-11 16-NOV-11 SecureFiles is being used by s
ystem users
1267852645 Transparent Data Enc 11.2.0.1.0 8 40 TRUE 29-SEP-11 16-NOV-11 Transparent Database Encryptio
ryption n is being used. There is atle
ast one column or tablespace t
hat is encrypted.
9 rows selected.
Sunday, 20 November 2011
Oracle role info for schema
schema role info
user role info
col pr head "Profile" for a8
col rn head "Resource" for a25
col rt head "Type" for a10
col li head "Value" for a10
break on pr skip
prompt
prompt Profile Details
prompt ===============
select p.profile pr,p.resource_name rn,p.resource_type rt,p.limit li from dba_users u,dba_profiles p
where u.profile=p.profile
and u.username='&&user';
col gr head "Grantor" for a8
col tn head "Object" for a20
col ow head "Owner" for a8
col pr head "Privilege" for a10
prompt
prompt Object Privileges
prompt =================
select t.grantor gr,t.table_name tn,t.owner ow,t.privilege pr from dba_tab_privs t
where t.grantee='&&user';
col cn head "Column" for a20
prompt
prompt Column Privileges
prompt =================
select c.grantor gr,c.column_name cn,c.table_name tn,c.owner ow,c.privilege pr from dba_col_privs c
where c.grantee='&&user';
col ad head "Adm" for a3
col pr head "Privilege" for a30
prompt
prompt System Privileges
prompt =================
select s.privilege pr,s.admin_option ad from dba_sys_privs s
where s.grantee='&&user';
col gr head "Granted Role" for a30
col dr head "Def" for a3
col ad head "Adm" for a3
prompt
prompt Role Privileges
prompt ===============
select r.granted_role gr,r.default_role dr,r.admin_option ad from dba_role_privs r
where r.grantee='&&user';
user role info
col pr head "Profile" for a8
col rn head "Resource" for a25
col rt head "Type" for a10
col li head "Value" for a10
break on pr skip
prompt
prompt Profile Details
prompt ===============
select p.profile pr,p.resource_name rn,p.resource_type rt,p.limit li from dba_users u,dba_profiles p
where u.profile=p.profile
and u.username='&&user';
col gr head "Grantor" for a8
col tn head "Object" for a20
col ow head "Owner" for a8
col pr head "Privilege" for a10
prompt
prompt Object Privileges
prompt =================
select t.grantor gr,t.table_name tn,t.owner ow,t.privilege pr from dba_tab_privs t
where t.grantee='&&user';
col cn head "Column" for a20
prompt
prompt Column Privileges
prompt =================
select c.grantor gr,c.column_name cn,c.table_name tn,c.owner ow,c.privilege pr from dba_col_privs c
where c.grantee='&&user';
col ad head "Adm" for a3
col pr head "Privilege" for a30
prompt
prompt System Privileges
prompt =================
select s.privilege pr,s.admin_option ad from dba_sys_privs s
where s.grantee='&&user';
col gr head "Granted Role" for a30
col dr head "Def" for a3
col ad head "Adm" for a3
prompt
prompt Role Privileges
prompt ===============
select r.granted_role gr,r.default_role dr,r.admin_option ad from dba_role_privs r
where r.grantee='&&user';
Profile Details
===============
Enter value for user: SCOTT
old 3: and u.username='&&user'
new 3: and u.username='SCOTT'
Profile Resource Type Value
-------- ------------------------- ---------- ----------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
SESSIONS_PER_USER KERNEL UNLIMITED
CPU_PER_SESSION KERNEL UNLIMITED
CPU_PER_CALL KERNEL UNLIMITED
LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
LOGICAL_READS_PER_CALL KERNEL UNLIMITED
IDLE_TIME KERNEL UNLIMITED
CONNECT_TIME KERNEL UNLIMITED
PRIVATE_SGA KERNEL UNLIMITED
FAILED_LOGIN_ATTEMPTS PASSWORD 10
PASSWORD_LIFE_TIME PASSWORD 180
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD UNLIMITED
PASSWORD_VERIFY_FUNCTION PASSWORD NULL
PASSWORD_LOCK_TIME PASSWORD 1
PASSWORD_GRACE_TIME PASSWORD 7
16 rows selected.
Object Privileges
=================
old 2: where t.grantee='&&user'
new 2: where t.grantee='SCOTT'
Grantor Object Owner Privilege
-------- -------------------- -------- ----------
SYS DBMS_REDEFINITION SYS EXECUTE
Column Privileges
=================
old 2: where c.grantee='&&user'
new 2: where c.grantee='SCOTT'
no rows selected
System Privileges
=================
old 2: where s.grantee='&&user'
new 2: where s.grantee='SCOTT'
Privilege Adm
------------------------------ ---
UNLIMITED TABLESPACE NO
Role Privileges
===============
old 2: where r.grantee='&&user'
new 2: where r.grantee='SCOTT'
Granted Role Def Adm
------------------------------ --- ---
RESOURCE YES NO
DBA YES NO
CONNECT YES NO
Friday, 18 November 2011
Oracle AWR Report for a Single SQL Statement
Generating an AWR Report for a Single SQL Statement
AWR for single sql
Oracle AWR report for single sql
SQL> select DBID from v$database ;
DBID
----------
1267852645
select SNAP_ID,BEGIN_INTERVAL_TIME from dba_hist_snapshot ;
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
65j29t2qc8cqt
sys> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
AWR for single sql
Oracle AWR report for single sql
SQL> select DBID from v$database ;
DBID
----------
1267852645
select SNAP_ID,BEGIN_INTERVAL_TIME from dba_hist_snapshot ;
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
65j29t2qc8cqt
sys> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
204677159 xxxDB 1 xxxdb
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text
Type Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 204677159 1 xxxDB aptdb apt-xxxx-01
.xxxxxxxx
Using 204677159 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 2
Listing the last 2 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
xxxdb xxxDB 3937 17 Nov 2011 00:00 1
3938 17 Nov 2011 01:00 1
3939 17 Nov 2011 02:00 1
3940 17 Nov 2011 03:00 1
3941 17 Nov 2011 04:00 1
3942 17 Nov 2011 05:00 1
3943 17 Nov 2011 06:00 1
3944 17 Nov 2011 07:00 1
3945 17 Nov 2011 08:00 1
3946 17 Nov 2011 09:00 1
3947 17 Nov 2011 10:00 1
3948 17 Nov 2011 11:00 1
3949 17 Nov 2011 12:00 1
3950 17 Nov 2011 13:00 1
3951 17 Nov 2011 14:00 1
3952 17 Nov 2011 15:00 1
3953 17 Nov 2011 16:00 1
3954 17 Nov 2011 17:00 1
3955 17 Nov 2011 18:00 1
3956 17 Nov 2011 19:00 1
3957 17 Nov 2011 20:00 1
3958 17 Nov 2011 21:00 1
3959 17 Nov 2011 22:00 1
3960 17 Nov 2011 23:00 1
3961 18 Nov 2011 00:00 1
3962 18 Nov 2011 01:00 1
3963 18 Nov 2011 02:00 1
3964 18 Nov 2011 03:00 1
3965 18 Nov 2011 04:00 1
3966 18 Nov 2011 05:00 1
3967 18 Nov 2011 06:00 1
3968 18 Nov 2011 07:00 1
3969 18 Nov 2011 08:00 1
3970 18 Nov 2011 09:00 1
3971 18 Nov 2011 10:00 1
3972 18 Nov 2011 11:00 1
3973 18 Nov 2011 12:00 1
3974 18 Nov 2011 13:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 3937
Begin Snapshot Id specified: 3937
Enter value for end_snap: 3974
End Snapshot Id specified: 3974
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 65j29t2qc8cqt --- <<<<<<<<<<<<<<<<<<<< you have to put sql id here
Subscribe to:
Posts (Atom)
