SCHEDULER JOBS:-----
begin
dbms_scheduler.create_job(
job_name=>'USV_POPULATE_DCR_DEC',
job_type=>'stored_procedure',
job_action=>'proc_dcr_DECEMBER',
start_date=>to_date('19/01/2011 19:15:00','dd/mm/rrrr hh24:mi:ss'),
enabled=>TRUE,
auto_drop=>FALSE,
comments=>'POPULATE DCR FOR DECEMBER REQ BY SIDDHESH'
);
end;
/
begin
dbms_scheduler.enable(name=>'POPULATE_DATA_DCR_DECEMBER');
end;
/
To drop Or create job first login with user in which u want to create a job
begin
dbms_scheduler.drop_job(job_name=>'MPOWERI_PROC_EOD');
end;
/
begin
dbms_scheduler.create_job(
job_name=>'MPOWERI_PROC_EOD',
job_type=>'stored_procedure',
job_action=>'PROC_EOD',
start_date=>to_date('19/11/2011 2:00:00','dd/mm/rrrr hh24:mi:ss'),
enabled=>TRUE,
auto_drop=>FALSE,
repeat_interval=>'Freq=Daily;interval=1',
comments=>'DAILY EOD JOB'
);
end;
/
CRONTAB:-----
00 1 * * * sh /u02/RMAN/RMANbackup_batch.sh > /u02/RMAN/RMAN_BACKUP.logDBLINK CREATION:----user should have create database link and create session privilege to create check global_names..it should be same on both ends command to rename global_name alter database rename global_name to XYZ(all databse links should be dropped before firing) create database link MPOWER.REGRESS.RDBMS.DEV.US.ORACLE.COM connect to rbxynp identified by rbxynp using 'MPOWER';create database link RMT.REGRESS.RDBMS.DEV.US.ORACLE.COM connect to CWT using 'DEV'; create database link RMT1.REGRESS.RDBMS.DEV.US.ORACLE.COM connect to MPWR24 using 'DEV'; create database link RMT2.REGRESS.RDBMS.DEV.US.ORACLE.COM connect to MPWRCIS2 using 'DEV'; ====================================================================================== mpwrcis2 create database link MPOWER.REGRESS.RDBMS.DEV.US.ORACLE.COM connect to RBXYPROD IDENTIFIED BY RBXYPROD using 'MPOWER'; =============================================================================================== CREATE DATABASE LINK MPWR01.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO RBXYPROD IDENTIFIED BY RBXYPROD USING 'MPOWER'; ============================================================================================ CREATE DATABASE LINK CIS.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO RBXYCIS IDENTIFIED BY RBXYCIS USING 'CIS'; CREATE DATABASE LINK RBXY1.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO RBXY IDENTIFIED BY RBXY USING 'RBXY1'; CREATE DATABASE LINK MPWRCIS.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO RBXYCIS IDENTIFIED BY RBXYCIS USING 'MPWRCIS'; ========================================================================================== check global_names parameter is true and global_name should be in CIS.REGRESS.RDBMS.DEV.US.ORACLE.COM format alter database rename global_name to MPWRCIS.REGRESS.RDBMS.DEV.US.ORACLE.COM; ALTER SYSTEM SET global_names = TRUE SCOPE=BOTH; CREATE DATABASE LINK RBXY.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO RBXYKY IDENTIFIED BY RBXYKY USING 'RBXY'; CREATE DATABASE LINK CIS.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO RNIGCIS IDENTIFIED BY RNIGCIS USING 'CIS'; CREATE DATABASE LINK STREAM1.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO STREAMADMIN IDENTIFIED BY STREAMADMIN USING 'STREAM1'; CREATE DATABASE LINK STREAM2.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO STREAMADMIN IDENTIFIED BY STREAMADMIN USING 'STREAM2'; create database link MPOWER.REGRESS.RDBMS.DEV.US.ORACLE.COM connect to MPWR24 IDENTIFIED BY MPWR24 using 'MPOWER'; CREATE DATABASE LINK MPWRCIS.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO RGCHCIS IDENTIFIED BY RGCHCIS USING 'MPWRCIS'; CREATE DATABASE LINK MPWR01.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO RGCH IDENTIFIED BY r2g#c4h8 USING 'MPWR02'; CREATE DATABASE LINK MPWRCIS.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO RBXYCIS IDENTIFIED BY RBXYCIS USING 'MPWRCIS';DBSIZE:-----DATAFILES: select sum(bytes)/1024/1024/1024 "GB" from dba_data_files; ====================================================================== Tempfile: select nvl(sum(bytes),0)/1024/1024/1024 "GB" from dba_temp_files; ====================================================================== Redolog Size: select sum(bytes)/1024/1024 "Meg" from sys.v_$log; ====================================================================== Total Size: select (a.data_size+b.temp_size+c.redo_size)/1024/1024/1024 "total_size" from ( select sum(bytes) data_size from dba_data_files ) a, ( select nvl(sum(bytes),0) temp_size from dba_temp_files ) b, ( select sum(bytes) redo_size from sys.v_$log ) c; ====================================================================== "Free space" reports data files free space: col "Database Size" format a20 col "Free space" format a20 select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' MB' "Database Size" , round(free.p / 1024 / 1024 / 1024) || ' MB' "Free space" from (select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log) used , (select sum(bytes) as p from dba_free_space) free group by free.p / ======================================================================= Used Space: SELECT SUM(bytes)/1024/1024/1024 "GB" FROM dba_segments; ======================================================================= high water mark for a table: SELECT BLOCKS FROM DBA_SEGMENTS WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table); ANALYZE TABLE owner.table ESTIMATE STATISTICS; SELECT EMPTY_BLOCKS FROM DBA_TABLES WHERE OWNER=UPPER(owner) AND TABLE_NAME = UPPER(table);DYNAMIC:----SELECT 'ALTER TABLE '||TABLE_NAME||' MOVE TABLESPACE MED_MST ;' FROM USER_TABLES WHERE TABLE_NAME IN ('USVPARTYDATA'); SELECT 'GRANT EXECUTE ON MEDLEY.'||OBJECT_NAME||' TO MED_DML ;' FROM USER_OBJECTS where object_type in ('FUNCTION','PROCEDURE','PACKAGE') ; SELECT 'GRANT SELECT,UPDATE,INSERT ON MEDLEY.'||TABLE_NAME||' TO MED_DML ;' FROM USER_TABLES SELECT 'ALTER TABLE '||TABLE_NAME||' MOVE TABLESPACE MED_MST ;' FROM USER_TABLES WHERE owner='MPOWERI6'; SELECT TABLE_NAME FROM USER_TABLES where table_name like 'HIS_%'; SELECT TABLE_NAME FROM USER_TABLES where table_name like 'ARC_%'; SELECT TABLE_NAME FROM USER_TABLES where table_name like 'M_%'; SELECT TABLE_NAME FROM USER_TABLES where table_name like 'BCK_%'; SELECT 'ALTER TABLE '||TABLE_NAME||' MOVE TABLESPACE TS_ARC_MPOWERI6 ;' FROM USER_TABLES WHERE table_name like 'ARC_%'; SELECT 'ALTER TABLE '||TABLE_NAME||' MOVE TABLESPACE TS_HIS_MPOWERI6 ;' FROM USER_TABLES where table_name like 'M_%'; SELECT INDEX_NAME FROM USER_INDEXES where table_name like 'M_%'; SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD TABLESPACE TS_ARC_MPOWERI6 ;' FROM USER_INDEXES WHERE table_name like 'ARC_%'; SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD TABLESPACE TS_HIS_MPOWERI6 ;' FROM USER_INDEXES WHERE table_name like 'HIS_%'; SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD TABLESPACE TS_BCK_MPOWERI6 ;' FROM USER_INDEXES WHERE table_name like 'BCK_%'; SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD TABLESPACE TS_MPOWERI6 ;' FROM USER_INDEXES where table_name like 'M_%'; ======================================================================================================================================================================= SELECT 'GRANT SELECT,UPDATE,INSERT ON RBXY.'||TABLE_NAME||' TO RBXYDEV2_DML ;' FROM USER_TABLES ; SELECT 'GRANT EXECUTE ON RBXY.'||OBJECT_NAME||' TO RBXYDEV2_DML ;' FROM USER_OBJECTS where object_type in ('FUNCTION','PROCEDURE','PACKAGE') ; SELECT 'GRANT SELECT ON RBXYKY.'||SEQUENCE_NAME||' TO RBXKY_DML ;' FROM USER_SEQUENCES ; SELECT 'GRANT SELECT ON RBXY.'||TABLE_NAME||' TO SEL_ALL ;' FROM USER_TABLES ; ============================================================================================================================ SELECT 'CREATE PUBLIC SYNONYM,UPDATE,INSERT,DELETE ON MPOWERI1ODS.'||TABLE_NAME||' TO MPOWERI1 ;' FROM USER_TABLES CREATE PUBLIC SYNONYM MPOWERI1ODS. FOR MPOWERI1. . SELECT 'GRANT SELECT,UPDATE,INSERT,DELETE ON MPOWERI1ODS.'||TABLE_NAME||' TO MPOWERI1 ;' FROM USER_TABLES =========================================================================================================================== SELECT 'GRANT SELECT,UPDATE,INSERT ON GLENMARK.'||TABLE_NAME||' TO GLM_DML ;' FROM USER_TABLES ; SELECT 'GRANT EXECUTE ON GLENMARK.'||OBJECT_NAME||' TO GLM_DML ;' FROM USER_OBJECTS where object_type in ('FUNCTION','PROCEDURE','PACKAGE') ; SELECT 'GRANT SELECT ON GLENMARK.'||SEQUENCE_NAME||' TO GLM_DML ;' FROM USER_SEQUENCES ; SELECT 'GRANT SELECT ON GLENMARK.'||TABLE_NAME||' TO SEL_ALL ;' FROM USER_TABLES ;EXPDP / IMPDP :----impdp directory=DATA_PUMP_DIR dumpfile=kli_31july.dmp logfile=ppkli_1mp_31july.log remap_schema=kli:ppkli remap_tablespace=kli:ppkli expdp directory=DATA_PUMP_DIR schemas=mpoweri2 dumpfile=mpoweri2_08Oct.dmp logfile=mpoweri2_08Oct.log impdp directory=DATA_PUMP_DIR dumpfile=RBXYSL_26JUN_NODATA.DMP logfile=RBXYSL_26JUN_NODATA.log remap_schema=RBXYSL:RBXYSL remap_tablespace=RBXYSL:RBXYSL exec dbms_utility.compile_schema(schema=>'KLI2'); select object_name from user_objects where status='INVALID'; grant execute on mpower.proc_populate_mail to kotakdev; grant execute on dbms_crypto to kotakdev; expdp directory=DATA_PUMP_DIR schemas=mpoweri1 dumpfile=mpoweri1_20Sept_nodata.dmp logfile=mpoweri1_20Sept_nodata.log content=metadata_only impdp directory=DATA_PUMP_DIR dumpfile=mpoweri1_20Sept_nodata.dmp logfile=mpoweri1_20_Sept_2011_nodata.log remap_schema=mpoweri1:kli2 remap_tablespace=ts_mpoweri1:kli2 remap_tablespace=users:kli2 content=metadata_only exp file=c:\AllUsers_13Oct_2011.dmp log=c:\AllUser_13Oct_2011.log owner=RBXY,RBXYKY,RBXYMS,RBXYNG,RBXYNP,RBXYSL,RBXYME rows=n imp file=C:\RBXY_25Oct_2011.dmp log=C:\RBXY_25Oct2011.log fromuser=RBXY touser=RBXY exp file=H:\CWT_11DEC_2011.dmp log=H:\CWT_11DEC_2011.log owner=CWT rows=n exp file=c:\AllUsers_13Oct_2011.dmp log=c:\AllUser_13Oct_2011.log owner=RBXYCIS,RINTLCIS,RKENCIS,RNIGCIS,RMAUCIS,RNPLCIS,RNSLCIS,PPALEMBIC,PPBMD,PPCIS,PPFERRING,PPIPCA,PPMEDLEY,PPUSV,MPOWER,MPWRCIS2 rows=n ========================================================================= ========================================================================= ========================================================================= For tables exp file=c:\RBXY.dmp log=c:\RBXY.log tables=DMMSTMATCHID imp file=c:\RBXY.dmp log=C:\RBXY_25Oct2011.log fromuser=RBXY touser=RBXY tables=DMMSTMATCHID ========================================================================== ========================================================================== ========================================================================== OEL EXPORT exp file=/u02/oradata/PPAXA_07DEV_2011.dmp log=/u02/oradata/PPAXA_07DEC_2011.log owner=PPAXA expdp directory=DATA_PUMP_DIR schemas=mpoweri2 dumpfile=mpoweri2_26_DEC_2011.dmp logfile=mpoweri2_26_DEC_2011.log impdp directory=DATA_PUMP_DIR dumpfile=PRODAXA_06_DEC_2011.dmp logfile=prodaxa_06_DEC2011.log remap_schema=prodaxa:insr30 remap_tablespace=hist_axa:insr30 remap_tablespace=arc_axa:insr30 remap_tablespace=bck_axa:insr30 remap_tablespace=prodaxa:insr30 impdp directory=DATA_PUMP_DIR dumpfile=kotak_23_DEC_2011.dmp logfile=kotak_23DEC_2011.log remap_schema=kotak:devi20 remap_tablespace=kotak:devi20 ============================================================================ ============================================================================ ============================================================================ imp file=c:\ppkli.dmp fromuser=ppkli touser=ppkli tables=mmstusrbranch ================================================================================ exp file=H:\rxysl_tables_18_july.dmp log=H:\rxysl_tables_18_july.log tables=MMSTCODT1_MIG,MMSTGEOHDR_MIG,MMSTLOOKUP_MIG,MMSTLOOKUPGRP_MIG,MMSTGEODT1_MIG,MMSTREPHDR_MIG,MMSTREPDT1_MIG,MMSTBRNDGROUP_MIG,MMSTBRNDHDR_MIG,MMSTBRNDDT1_MIG,MMSTBRNDDT2_MIG,MMSTREPDT2_MIG,MMSTPARTYHDR_MIG,MMSTPARTYDT1_MIG,MMSTPARTYDT1_MIG,MMSTCAMPAIGNDT1_MIG,MMSTCAMPAIGNDT2_MIG,MMSTCAMPAIGNDT3_MIG,MMSTCAMPAIGNHDR_MIG,MMSTCAMPCUSTSEL_MIG,MMSTROLEHDR_MIG,MMSTGEOLINK_MIG,MMSTGIFTHDR_MIG,MMSTGIFTDT1_MIG,MMSTREPDT4_MIG rows=y imp file=H:\rxysl_tables_18_july.dmp log=H:\rbxy_tables_18_july.log fromuser=RBXYSL touser=RBXY====================Create a batch file============================================= #!/bin/bash export ORACLE_HOME=/u07/Installation/102 export ORACLE_SID=DEV export ORACLE_BASE=/u07/Installation PATH=$PATH:$ORACLE_HOME/bin DD=`date +%d%m%y` mkdir /u03/EXP_DEV/bkp_${DD} exp system/sysdev@dev file=/u03/EXP_DEV/bkp_${DD}/EXPDEVDB.dmp log=/u03/EXP_DEV/bkp_${DD}/EXPDEV.log direct=y compress=y OWNER='ALEMBIC','AXA','CRP20','CRP201','CWT','CWTDAR','DEVI20','FERRING','GLENMARK','INSR','INSDEMO','IDSUSV','INSR351','INSR40','KLI2','MEDLEY','RBXY','MPOWER','MOBILEPHARMA','MPOWER','MPOWERI2','PFIZER','PMS','POC1','POC2','POCLIVE','PRODAXA','PRODKLI','PRODKOTAK','QCPHARMA','QCAXA','RBXY','USV','UCB','UATAXA','WEBPHARMA' EXIT; EOFexp file=C:\cxnlog_medley_29mar.dmp log=C:\cxn.log tables=mtxncxnlog query="""where REPCODE='M0047' AND CXNDATE>=TO_DATE('03/29/2011 11:10:00','MM/DD/RRRR HH24:MI:SS') AND CXNDATE<=TO_DATE('03/29/2011 11:30:00','MM/DD/RRRR HH24:MI:SS')""" select * from medley.mtxncxnlog where REPCODE='M0047' and TRUNC(CXNDATE)=TRUNC(SYSDATE) AND CXNDATE>=TO_DATE('03/29/2011 11:10:00','MM/DD/RRRR HH24:MI:SS') AND CXNDATE<=TO_DATE('03/29/2011 11:30:00','MM/DD/RRRR HH24:MI:SS');Hit ratios Buffer cache hit ratio: ======================= select sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets", sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets", sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads", round((sum(decode(name, 'consistent gets',value, 0)) + sum(decode(name, 'db block gets',value, 0)) - sum(decode(name, 'physical reads',value, 0))) / (sum(decode(name, 'consistent gets',value, 0)) + sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio" from v$sysstat; data dictionary hit ratio: ========================== select sum(GETS), sum(GETMISSES), round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2) from v$rowcache; sql cache hit ratio: =================== select sum(PINS) Pins, sum(RELOADS) Reloads, round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio from v$librarycache; library cache hit ratio: ========================= select sum(PINS) Executions, sum(RELOADS) cache_misses, sum(RELOADS) / sum(PINS) miss_ratio from v$librarycache;SEE WHICH SESSIONS ARE TAKING TIME: ============================================================================================================================= SELECT s.sid,s.username,s.module,round(t.value/1000000,2) "Elapsed Processing Time (Sec)" FROM v$sess_time_model t,v$session s WHERE t.sid = s.sid AND t.stat_name = 'DB time' AND s.username IS NOT NULL AND t.value/1000000 >= 1; ============================================================================================================================= FOR A PARTICULAR SESSION FIND THE STATS: ============================================================================================================================= select * from v$sess_time_model where sid = '894 ' order by value desc; =============================================================================================================================Scripts to find out slow queries ================================================================================================================== FINDING Which sql has problem: SELECT sql_id, application_wait_time appl,concurrency_wait_time concurr,user_io_wait_time user_io FROM v$sqlarea; =================================================================================================================== FINDING which the source of the sql(insert sql_id) : select SID,USERNAME,SCHEMANAME,TERMINAL,SECONDS_IN_WAIT from v$session where SID='849'; ====================================================================================================================== FINDING what is the text of the sql(insert SID): SELECT T.SQL_TEXT FROM V$SQLTEXT T, V$SESSION S WHERE S.SID =98 AND S.SQL_ADDRESS=T.ADDRESS ORDER BY T.PIECE; =====================================================================================================================select distinct sid from v$mystat Archive to be check ------------------- ls -lrt *core*|grep "Aug 29"|awk '{print $9}' ls -lrt *core*|grep "Aug 29"|awk '{print $9}'|xargs compress & ls -lrt |grep "Sep 4"|grep arc|awk '{print $9}'|xargs rm select max(sequence#) from v$archived_log where applied='YES'; ################################ archive deletion using RMAN ################################ cd $ORACLE_HOME/RMAN/scripts/log rman target / catalog rman/rman_2005@rmancat DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 2 TIMES TO 'SBT_TAPE' COMPLETED BEFORE 'SYSDATE -2'; list ARCHIVELOG ALL BACKED UP 2 TIMES TO 'SBT_TAPE' COMPLETED BEFORE 'SYSDATE -2'; ls -lrt *arc*|grep "Sep 1"|awk '{print $9}' ls -lrt *arc|grep "Apr 5"|awk '{print $9}'|xargs compress & ls -lrt |grep "Oct 31"|grep arc.Z|awk '{print $9}'|xargs rm & select max(sequence#) from v$archived_log where applied='YES'; ################################Check duplicate file################################ select substr(name,instr(name,'/',-1)+1,60) name from v$datafile where lower(substr(name,instr(lower(name),'/',-1)+1,60)) = lower('&1'); set lines 132 set pages 1000 column duplicate_datafiles for a40 select substr(name,instr(name,'/',-1)+1) Duplicate_datafiles, count(*) from v$datafile group by substr(name,instr(name,'/',-1)+1) having count(*)>1 / select name nm,creation_time from v$datafile where substr(name,instr(name,'/',-1,1)+1) in (select substr(file_name,instr(file_name,'/',-1,1)+1) from dba_data_files group by substr(file_name,instr(file_name,'/',-1,1)+1) having count(*)> 1) / select GROUP_NUMBER,NAME,STATE,TOTAL_MB,FREE_MB from v$asm_diskgroup order by 1 / *********************************Junk ********************************************* col name for a80 set lines 120 set pages 5000 select file#, name, trunc(creation_time), bytes/1024/1024 "SIZE" from v$datafile where length(translate(upper(name),'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789./\_:','$'))<>0 order by 3; ############################### To KILL FINN PROCESSES ############################### ps -ef | grep "LOCAL=NO"|grep FINN|awk '{print $2}'|xargs -t -I {} kill -9 {} ############################### Kill OS level Oracle Process ############################### ps -ef|grep oraclePSPROD |grep "LOCAL=NO" |awk '{print $2}'|xargs kill -9 select 'Alter system kill session '||''''||sid||','||serial#||''''||'immediate ;' from v$session where status='ACTIVE' and sid=&sid; ps -ef|grep PRODSYMB|grep LOCAL=NO|awk '{print $2}'|xargs -t -I {} kill -9 {} ps -ef|grep ora9i| grep exp|awk '{print $10}'|xargs -t -I {} lsnrctl stat {} ######################kill session before 1hrs################## select 'ALTER SYSTEM KILL SESSION '||''''||sid||','||serial#||''''||';'from v$session where status='INACTIVE' and last_call_et>3600 and username is not null and username not in ('SYS','SYSTEM','lCICIOMS'); ###############################Kill session############################### select 'Alter system kill session '||''''||sid||','||serial#||''''||' immediate ;' from v$session where sid =&sid; select 'Alter system kill session '||''''||sid||','||serial#||''''||' immediate ;' from v$session where sid in (select sid from v$session where status='INACTIVE' and to_char(logon_time,'dd-mon-yyyy hh24:mi')<'01-JUN-2010 15:00:00' and username='SGMADM'); select 'Alter system kill session '||''''||sid||','||serial#||''''||'immediate ;' from v$session where sid in (select sid from v$session where status='INACTIVE' and username='CRMUSER' and LAST_CALL_ET>3600); select 'Alter system kill session '||''''||sid||','||serial#||''''||' immediate ;' from v$session where sid in (select sid from v$session where event in ('db file sequential read','read by other session') and machine='sf1280-01'); ******************** To kill All locked inactive sessions *************** select OWNER, OBJECT_NAME, SID, SERIAL#,a.logon_time,a.status, locked_mode from v$session a, v$locked_object b, dba_objects c where a.SID=b.session_id and b.OBJECT_ID=c.OBJECT_ID and a.status='INACTIVE' and b.ORACLE_USERNAME='IBSLSFA' order by 5; ############################### Standby database############################### To stop ===== recover managed standby database cancel; shut immediate To start ======= startup nomount alter database mount standby database; recover managed standby database parallel(degree 32) disconnect from session; recover managed standby database parallel(degree 16) disconnect from session; ALTER DATABASE RECOVER managed standby database disconnect from session; ALTER DATABASE RECOVER managed standby database using current logfile disconnect from session parallel 16; FINN---> alter database recover managed standby database parallel(degree 32) using current logfile disconnect from session; Check if archived logs are synching OK, compare both results: ------------------------------------------------------------- on primary db:- select max(sequence#) from v$log_history; on standby db: select max(sequence#) from v$archived_log; ************************Listener permission ********************* chmod 01777 /var/tmp/.oracle chmod 6751 $ORACLE_HOME/bin/oracleFki ************************ Dataguard Status ************************ set lines 120 select p.prim_max_sequence, d.dr_max_archived_sequence, ad.dr_max_applied_sequence, p.prim_max_sequence-d.dr_max_archived_sequence archive_lag_in_dr, p.prim_max_sequence-ad.dr_max_applied_sequence applied_lag_in_dr from (select max(sequence#) prim_max_sequence from v$archived_log where dest_id=1 and archived='YES') p, (select max(sequence#) dr_max_archived_sequence from v$archived_log where standby_dest='YES' and archived='YES') d, (select max(sequence#) dr_max_applied_sequence from v$archived_log where standby_dest='YES' and archived='YES' and applied='YES') ad; set lines 150 select p.prim_max, f.fall_max_archived, d.dr_max_archived, af.fall_max_applied, ad.dr_max_applied, p.prim_max-f.fall_max_archived archive_lag_fall, p.prim_max-d.dr_max_archived archive_lag_dr, p.prim_max-af.fall_max_applied applied_lag_fall, p.prim_max-ad.dr_max_applied applied_lag_dr from (select max(sequence#) prim_max from v$archived_log where dest_id=1 and archived='YES') p, (select max(sequence#) fall_max_archived from v$archived_log where standby_dest='YES' and archived='YES' and dest_id=2) f, (select max(sequence#) dr_max_archived from v$archived_log where standby_dest='YES' and archived='YES' and dest_id=3) d, (select max(sequence#) fall_max_applied from v$archived_log where standby_dest='YES' and archived='YES' and applied='YES' and dest_id=2) af, (select max(sequence#) dr_max_applied from v$archived_log where standby_dest='YES' and archived='YES' and applied='YES' and dest_id=3) ad; ************************************************ Dataguard Status ************************************************ ON LIVE ========== set lines 110 select p.prim_max_sequence, d.dr_max_archived_sequence, ad.dr_max_applied_sequence, p.prim_max_sequence-d.dr_max_archived_sequence archive_lag_in_dr, p.prim_max_sequence-ad.dr_max_applied_sequence applied_lag_in_dr from (select max(sequence#) prim_max_sequence from v$archived_log where dest_id=1 and archived='YES' and RESETLOGS_ID in (select max(RESETLOGS_ID) from v$archived_log)) p, (select max(sequence#) dr_max_archived_sequence from v$archived_log where standby_dest='YES' and archived='YES' and RESETLOGS_ID in (select max(RESETLOGS_ID) from v$archived_log)) d, (select max(sequence#) dr_max_applied_sequence from v$archived_log where standby_dest='YES' and archived='YES' and applied='YES' and RESETLOGS_ID in (select max(RESETLOGS_ID) from v$archived_log)) ad; ON DR ========= select max(sequence#) from v$archived_log where applied='YES' and applied='YES' and RESETLOGS_ID in (select max(RESETLOGS_ID) from v$archived_log); ****************************** v$archived_log APPLIED column not updating for standby_dest ************************* select * from v$archive_processes where status <> 'STOPPED'; PROCESS STATUS LOG_SEQUENCE STAT ---------- ---------- ------------ ---- 0 ACTIVE 0 IDLE 1 ACTIVE 0 IDLE 2 ACTIVE 0 IDLE 3 ACTIVE 0 IDLE 4 ACTIVE 0 BUSY 5 ACTIVE 0 IDLE 6 ACTIVE 0 IDLE 7 ACTIVE 0 IDLE 8 ACTIVE 327890 IDLE 9 ACTIVE 0 IDLE Check arch process with status BUSY. If status of arch process is busy means its in hang state Get arch process SPID from below query : select program,spid from v$process where program like '%ARC%'; PROGRAM SPID -------------------- ------------ ORACLE.EXE (ARC0) 2688 ORACLE.EXE (ARC1) 3356 ORACLE.EXE (ARC2) 1536 ORACLE.EXE (ARC3) 596 ORACLE.EXE (ARC4) 2892 ORACLE.EXE (ARC5) 3448 ORACLE.EXE (ARC6) 3504 ORACLE.EXE (ARC7) 3956 ORACLE.EXE (ARC8) 3984 ORACLE.EXE (ARC9) 4224 User orakill utility on window to kill arch thread orakill nornew 2892 ************************ Data File space status ****************** col name for a60 select dd.tablespace_name, dd.name, round(dd.bytes / 1024 / 1024) "Allocated space MB", round((dd.bytes / 1024 / 1024) - sum(df.bytes / 1024 / 1024)) "Used Space MB", round(sum(df.bytes / 1024 / 1024)) "Free space MB" from v$datafile_header dd, dba_free_space df where dd.file# = df.file_id -- and dd.tablespace_name in ('&tablespace_name') group by dd.tablespace_name,v#ses dd.name, dd.status, dd.creation_time, dd.bytes / 1024 / 1024 order by 1; ************************ DataFile resize ******************** set verify off column file_name format a70 word_wrapped column smallest format 999,990 heading "Smallest|Size|Poss." column currsize format 999,990 heading "Current|Size" column savings format 999,990 heading "Poss.|Savings" break on report compute sum of savings on report column tb_name new_val tablespace_name select '&tablespace_name' tb_name from dual / column value new_val blksize select value from v$parameter where name = 'db_block_size' / set lines 200 set pages 200 col FILE_NAME for a70 select /*+ RULE */ file_name, ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest, ceil( blocks*&&blksize/1024/1024) currsize, ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and tablespace_name='&tablespace_name' / column cmd format a75 word_wrapped select /*+ RULE */ 'alter database datafile '''||file_name||''' resize ' || ceil( (nvl(hwm,1)*2048)/1024/1024 ) || 'm;' cmd from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*2048)/1024/1024 ) > 0 and tablespace_name='&tablespace_name' / ************************** DB SIZE ****************************** set lines 200 set pagesize 50000 tti "Database growth per month for last year" select to_char(creation_time, 'RRRR Month') "Month", sum(bytes)/1024/1024 "Growth in Meg" from sys.v$datafile where creation_time > SYSDATE-365 group by to_char(creation_time, 'RRRR Month') order by 1; select sum(a.BYTES/1024/1024/1024) "Database Size GB", sum(b.BYTES/1024/1024/1024) "Database Free Size GB", sum(c.BYTES/1024/1024/1024) "Database Used Size GB" from sm$ts_avail a, sm$ts_free b, sm$ts_used c where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.TABLESPACE_NAME=c.TABLESPACE_NAME; ************************* Tablespace growth **************************** select t.TARGET_NAME,m.METRIC_NAME,v.KEY_VALUE,to_number(nvl(v.VALUE,'0')) value,v.COLLECTION_TIMESTAMP, CASE WHEN m.is_transposed = 0 THEN m.column_label ELSE key_value END column_label from mgmt_targets t, mgmt_metrics m, mgmt_metrics_raw v where t.TARGET_GUID=v.TARGET_GUID AND m.metric_guid = v.METRIC_GUID --AND m.type_meta_ver = t.type_meta_ver and m.metric_name in ('problemTbsp') and t.TARGET_NAME='MXG' and v.KEY_VALUE='DATA' and v.METRIC_GUID='1F9B062F8B273CD8819C5D80CA96E323' order by v.COLLECTION_TIMESTAMP ************************* Create User **************************** APPLICATION USER ----------------- set lines 150 set pages 2500 select username, default_tablespace, temporary_tablespace, profile from dba_users; create user username identified by username_123 default tablespace tablespace_name temporary tablespace tablespace_name; grant connect, resource, unlimited tablespace to username; alter user RIBQUERY profile icicibank_profile_system; NON APPLICATION USER ---------------- create user RIBQUERY identified by RIB_QUERY321 default tablespace CORP_CUST temporary tablespace LMTEMP; grant ICICI_CONNECT to RIBQUERY; alter user RIBQUERY profile icicibank_profile; grant create session to ICICI_CONNECT; create role ICICI_CONNECT; ******************* Disable sqlplus access *********************** INSERT into SYSTEM.product_user_profile (product,userid,attribute,char_value,date_value) values('SQL*Plus','CANFINNQRY','CONNECT','DISABLED',NULL); ****************To enable explain plan privilege to User********** Creates a role with access to Dynamic Performance Tables for the SQL*Plus SET AUTOTRACE ... STATISTICS command. After this script has been run, each user requiring access to the AUTOTRACE feature should be granted the PLUSTRACE role by the DBA. Create plustrace role : @$ORACLE_HOME/SQLPLUS/ADMIN/PLUSTRCE.SQL grant plustrace to user; alter session set current_schema=username; Create plan_table in user schema : $ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL **************************COMMAND_TYPE ***************************** Insert 2 Select 3 Update 6 Delete 7 Lock Table 26 Alter Database 35 Alter Session 42 Commit 44 Rollback 45 Savepoint 46 Begin/Declare 47 ************************** Dynamic SGA Resizing ******************* set linesize 120 set pagesize 60 set trimspool on break on timed_at skip 1 column oper_type format a14 column component format a24 column parameter format a21 select start_time timed_at, oper_type, component, parameter, oper_mode, initial_size, final_size from $sga_resize_ops where start_time between trunc(sysdate-3) and trunc(sysdate) order by start_time, component; ************************** underscore param *********************** set lines 150 set pages 500 col name for a40 col value for a15 col description for a65 SELECT ksppinm name,ksppstvl value,ksppdesc description FROM x$ksppi x, x$ksppcv y WHERE (x.indx = y.indx) AND x.inst_id=userenv('instance') AND x.inst_id=y.inst_id AND ksppinm LIKE '%&name%' ORDER BY name; DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION(SID,SERIAL#,PARAM,VALUE) ************************** spid from sid *************************** set lines 123 col USERNAME for a15 col OSUSER for a8 col MACHINE for a15 col PROGRAM for a20 select b.spid, a.username, a.program , a.osuser ,a.machine, a.sid, a.serial#, a.status from v$session a, v$process b where addr=paddr(+) and sid=&sid ************************** sid from spid ******************************* set lines 150 set pages 500 col USERNAME for a15 col OSUSER for a8 col MACHINE for a15 col PROGRAM for a20 select a.username, a.program , a.osuser ,a.machine, a.sid, a.serial#, a.status from v$session a, v$process b where a.paddr=b.addr and b.spid=&spid********************** Active SID for Specific PLSQL Object ************************ select sid, serial#, status, username, program from v$session where PLSQL_ENTRY_OBJECT_ID in (select object_id from dba_objects where object_name in ('REPLACE_MAIL_TEMPLATE_PROC','REPLACE_MAIL_TEMPLATE_TEST')); **************************************** Shutdown Issue Resolving Steps ***************************************** Shut down immediate may hang/slowdown because of various reasons. - processes still continue to be connected to the database and do not terminate. - SMON is cleaning temp segments or performing delayed block cleanouts. - Uncommitted transactions are being rolled back. Perform the below tracing while shutting down the database. select count(*) from v$session_longops where time_remaining>0; select sum(used_ublk) from v$transaction; select * from v$fast_start_transactions; select * from v$fast_start_servers; Enable Tracing: alter session set tracefile_identifier='10046'; alter session set timed_statistics = true; alter session set statistics_level=all; alter session set max_dump_file_size = unlimited; alter session set events '10046 trace name context forever,level 12'; alter session set events '10400 trace name context forever, level 1'; shutdown immediate; Note: 10046 turns on extended SQL_TRACE for the shutdown process. 10400 dumps a systemstate every 5 minutes. ****************************** hot_buffer from database cache******************************** set line 130 set pages 99,99,999 column avg_touches format 99,99,999 column myname heading 'Name' format a30 column mytype heading 'Type' format a10 column buffers format 999,999 column owner format a12 select object_type mytype, b.owner owner , object_name myname, blocks , count(1) buffer, AVG(tch) avg_touches From x$bh a, dba_objects b, dba_segments s Where a.obj=b.object_id and b.object_name=s.segment_name and b.owner not in ('SYS','SYSTEM') group by object_name,object_type,b.owner,blocks,obj having avg(tch) > 5 and count(1) > 20; select sid, p1raw, p2, p3, seconds_in_wait, wait_time, state from v$session_wait where event like '%latch%' order by p2, p1raw; select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name from x$bh a, dba_objects b where (a.obj = b.object_id or a.obj = b.data_object_id) and a.hladdr = '&ADDR' union select hladdr, file#, dbablk, tch, obj, null from x$bh where obj in (select obj from x$bh where hladdr = '&ADDR' minus select object_id from dba_objects minus select data_object_id from dba_objects) and hladdr = '&ADDR' order by 4; select 'Segment Header' class, a.segment_type, a.segment_name, a.partition_name from dba_segments a, v$session_wait b where a.header_file= b.p1 and a.header_block = b.p2 and b.event='buffer busy waits' union select 'Freelist Groups' class, a.segment_type, a.segment_name, a.partition_name from dba_segments a, v$session_wait b where b.p2 between a.header_block + 1 and (a.header_block + a.freelist_groups) and a.header_file= b.p1 and a.freelist_groups > 1 and b.event='read by other session' union select a.segment_type || ' block' class, a.segment_type, a.segment_name, a.partition_name from dba_extents a, v$session_wait b where b.p2 between a.block_id and a.block_id + a.blocks - 1 and a.file_id = b.p1 and b.event = 'buffer busy waits' and not exists (select 1 from dba_segments where header_file = b.p1 and header_block = b.p2); select * from ( select DECODE (GROUPING(a.object_name), 1, 'All Objects', a.object_name) AS "Object", sum(case when a.statistic_name = 'ITL waits' then a.value else null end) "ITL Waits", sum(case when a.statistic_name = 'buffer busy waits' then a.value else null end) "Buffer Busy Waits", sum(case when a.statistic_name = 'row lock waits' then a.value else null end) "Row Lock Waits", sum(case when a.statistic_name = 'physical reads' then a.value else null end) "Physical Reads", sum(case when a.statistic_name = 'logical reads' then a.value else null end) "Logical Reads" from v$segment_statistics a where a.owner like upper('&owner') group by rollup(a.object_name)) b where (b."ITL Waits">0 or b."Buffer Busy Waits">0) / v$segment_statistics view to get individual waits on specific tables :- select * from( select DECODE(GROUPING(a.object_name), 1, 'All Objects', a.object_name) AS "Object", sum(case when a.statistic_name = 'ITL Waits' then a.value else null end) "ITL Waits", sum(case when a.statistic_name = 'buffer busy waits' then a.value else null end) "Buffer Busy Waits", sum(case when a.statistic_name = 'row lock waits' then a.value else null end) "Row Lock Waits", sum(case when a.statistic_name = 'physical reads' then a.value else null end) "Physical Reads", sum(case when a.statistic_name = 'logical reads' then a.value else null end) "Logical Reads" from v$segment_statistics a where a.owner like upper('&owner') group by rollup(a.object_name)) b where (b."ITL Waits">0 or b."Buffer Busy Waits">0); select o.object_name obj, o.object_type otype, ash.SQL_ID, w.class from v$active_session_history ash, ( select rownum class#, class from v$waitstat ) w, all_objects o where event='read by other session' and w.class#(+)=ash.p3 and o.object_id (+)= ash.CURRENT_OBJ# Order by sample_time; **************************IN oracle 9i ********************************************* SELECT s.sid,s.serial#,username,status,program,logon_time,s.sql_hash_value, sw.p1 file#, sw.p2 block#, sw.p3 reason FROM v$session_wait sw, v$session s WHERE sw.event = 'buffer busy waits' AND sw.sid = s.sid order by logon_time ************************ Identify Address For Higher Sleep Counts ******************* First determine which latch id(ADDR) are interesting by examining the number of sleeps for this latch. The higher the sleep count, the more interesting the latch id(ADDR) is: select CHILD# "cCHILD" , ADDR "sADDR" , GETS "sGETS" , MISSES "sMISSES" , SLEEPS "sSLEEPS" from v$latch_children where name = 'cache buffers chains' order by 5, 1, 2, 3; Get Segment Name ================ Depending on the TCH column (The number of times the block is hit by a SQL statement), you can identify a hotblock. The higher the value of the TCH column, the more frequent the block is accessed by SQL statements. column segment_name format a35 select /*+ RULE */ e.owner ||'.'|| e.segment_name segment_name, e.extent_id extent#, x.dbablk - e.block_id + 1 block#, x.tch, l.child# from sys.v$latch_children l, sys.x$bh x, sys.dba_extents e where x.hladdr = '&ADDR' and e.file_id = x.file# and x.hladdr = l.addr and x.dbablk between e.block_id and e.block_id + e.blocks -1 order by x.tch desc ; col OBJECT_NAME for a30 col OBJECT_TYPE for a10 col OWNER for a10 col TCH for 99999 with bh_lc as (select /*+ ORDERED */ lc.addr, lc.child#, lc.gets, lc.misses, lc.immediate_gets, lc.immediate_misses, lc.spin_gets, lc.sleeps, bh.hladdr, bh.tch tch, bh.file#, bh.dbablk, bh.class, bh.state, bh.obj from x$kslld ld, v$session_wait sw, v$latch_children lc, x$bh bh where lc.addr =sw.p1raw and sw.p2= ld.indx and ld.kslldnam='cache buffers chains' and lower(sw.event) like '%latch%' --and sw.state='WAITING' and bh.hladdr=lc.addr ) select bh_lc.hladdr, bh_lc.tch, o.owner, o.object_name, o.object_type, bh_lc.child#, bh_lc.gets, bh_lc.misses, bh_lc.immediate_gets, bh_lc.immediate_misses, spin_gets, sleeps from bh_lc, dba_objects o where bh_lc.obj = o.object_id(+) union select bh_lc.hladdr, bh_lc.tch, o.owner, o.object_name, o.object_type, bh_lc.child#, bh_lc.gets, bh_lc.misses, bh_lc.immediate_gets, bh_lc.immediate_misses, spin_gets, sleeps from bh_lc, dba_objects o where bh_lc.obj = o.data_object_id(+) order by 1,2 desc;****************************************** LOGICAL READ SEGMENT **************************************************** set lines 150 pages 5000 col object_name for a30 col snap_time for a20 break on snap_time SELECT to_char(begin_interval_time,'YYYY_MM_DD') snap_time, dhsso.object_name, sum(LOGICAL_READS_DELTA) LOGICAL_READS_DELTA FROM dba_hist_seg_stat dhss, dba_hist_seg_stat_obj dhsso, dba_hist_snapshot dhs WHERE dhs.snap_id = dhss.snap_id AND dhs.instance_number = dhss.instance_number AND dhss.obj# = dhsso.obj# AND dhss.dataobj# = dhsso.dataobj# AND begin_interval_time BETWEEN to_date('2012_05_17 18','YYYY_MM_DD HH24') AND to_date('2012_05_18 06','YYYY_MM_DD HH24') AND dhsso.owner='&ownername' GROUP BY to_char(begin_interval_time,'YYYY_MM_DD'),dhsso.object_name having sum(LOGICAL_READS_DELTA) > 1000000 order by 1,3 desc; ******************************************Sequential read object ************************************************* set lines 300 SELECT a.event, SUM (a.time_waited) total, COUNT (*), ROUND (AVG (a.time_waited)) AVG, o.object_name, object_type, i.table_name FROM v$active_session_history a, dba_objects o, dba_indexes i WHERE wait_class = 'User I/O' and i.owner(+) = o.owner and i.index_name(+) = o.object_name AND a.current_obj# = o.object_id GROUP BY event, current_obj#, o.object_name, o.object_type, i.table_name ORDER BY 3 DESC; ******************************************* Unstable Plans ********************************************************* set lines 155 col execs for 999,999,999 col min_etime for 999,999.99 col max_etime for 999,999.99 col avg_etime for 999,999.999 col avg_lio for 999,999,999.9 col norm_stddev for 999,999.9999 col begin_interval_time for a30 col node for 99999 break on plan_hash_value on startup_time skip 1 select * from ( select sql_id, sum(execs), min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev from ( select sql_id, plan_hash_value, execs, avg_etime, stddev(avg_etime) over (partition by sql_id) stddev_etime from ( select sql_id, plan_hash_value, sum(nvl(executions_delta,0)) execs, (sum(elapsed_time_delta)/decode(sum(nvl(executions_delta,0)),0,1,sum(executions_delta))/1000000) avg_etime -- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 group by sql_id, plan_hash_value ) ) group by sql_id, stddev_etime ) where norm_stddev > nvl(to_number('&min_stddev'),2) and max_etime > nvl(to_number('&min_etime'),.1) order by norm_stddev / *************************************************Column stats *************************************************** select column_name, num_distinct, num_nulls, density,num_buckets, histogram,last_analyzed from dba_tab_columns where table_name = upper('&t') and owner = '&o' order by 1 / *************************************************Version count ************************************************** set linesize 100 set pagesize 100 SELECT * FROM (SELECT substr(sql_text,1,40) sql,sql_id, version_count, executions, hash_value,address FROM V$SQLAREA WHERE version_count > 20 ORDER BY version_count DESC) WHERE rownum <= 10 ; set linesize 100 set pagesize 100 SELECT * FROM (SELECT substr(sql_text,1,40) sql, disk_reads, executions, disk_reads/executions "Reads/Exec", hash_value,address FROM V$SQLAREA WHERE disk_reads > 1000 ORDER BY disk_reads DESC) WHERE rownum <= 10 ; %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%SQL Queries details in history%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% set lines 200 set pages 200 col username for a7 col sql_id for a15 col sql_text for a45 select username, sql_id, (select substr(sql_text,1,4000) from dba_hist_sqltext where sql_id = a.sql_id) sql_text, execs,avg_etime,avg_lio,avg_pio from ( select username,sql_id,sum(execs) execs,avg(avg_etime) avg_etime,avg(avg_lio) avg_lio,avg(avg_pio) avg_pio from ( select PARSING_SCHEMA_NAME as username,s.sql_id, (select sql_text from dba_hist_sqltext where sql_id = s.sql_id) sql_text, nvl(executions_delta,0) execs, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio, (DISK_READS_DELTA/decode(nvl(DISK_READS_DELTA,0),0,1,executions_delta)) avg_pio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 --and plan_hash_value=3099046451 and begin_interval_time between '10-AUG-11 16.00.00.000 PM' and '10-AUG-11 17.00.00.000 PM' order by 6,7 ) group by username,sql_id order by 5,6 ) a where username not in ('SYS','SYSTEM','ICICIOMS'); ********************************************* Execution change history *********************************************** accept days_ago - prompt 'Enter Days ago: ' - default '10' set lines 155 col execs for 999,999,999 col before_etime for 999,990.99 col after_etime for 999,990.99 col before_avg_etime for 999,990.99 head AVG_ETIME_BEFORE col after_avg_etime for 999,990.99 head AVG_ETIME_AFTER col min_etime for 999,990.99 col max_etime for 999,990.99 col avg_etime for 999,990.999 col avg_lio for 999,999,990.9 col norm_stddev for 999,990.9999 col begin_interval_time for a30 col node for 99999 break on plan_hash_value on startup_time skip 1 select * from ( select sql_id, execs, before_avg_etime, after_avg_etime, norm_stddev, case when to_number(before_avg_etime) < to_number(after_avg_etime) then 'Slower' else 'Faster' end result -- select * from ( select sql_id, sum(execs) execs, sum(before_execs) before_execs, sum(after_execs) after_execs, sum(before_avg_etime) before_avg_etime, sum(after_avg_etime) after_avg_etime, min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev, case when sum(before_avg_etime) > sum(after_avg_etime) then 'Slower' else 'Faster' end better_or_worse from ( select sql_id, period_flag, execs, avg_etime, stddev_etime, case when period_flag = 'Before' then execs else 0 end before_execs, case when period_flag = 'Before' then avg_etime else 0 end before_avg_etime, case when period_flag = 'After' then execs else 0 end after_execs, case when period_flag = 'After' then avg_etime else 0 end after_avg_etime from ( select sql_id, period_flag, execs, avg_etime, stddev(avg_etime) over (partition by sql_id) stddev_etime from ( select sql_id, period_flag, sum(execs) execs, sum(etime)/sum(decode(execs,0,1,execs)) avg_etime from ( select sql_id, 'Before' period_flag, nvl(executions_delta,0) execs, (elapsed_time_delta)/1000000 etime -- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 and elapsed_time_delta > 0 and ss.begin_interval_time <= sysdate-&&days_ago union select sql_id, 'After' period_flag, nvl(executions_delta,0) execs, (elapsed_time_delta)/1000000 etime -- (elapsed_time_delta)/decode(nvl(executions_delta,0),0,1,executions_delta)/1000000 avg_etime -- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 and elapsed_time_delta > 0 and ss.begin_interval_time > sysdate-&&days_ago -- and s.snap_id > 7113 ) group by sql_id, period_flag ) ) ) group by sql_id, stddev_etime ) where norm_stddev > nvl(to_number('&min_stddev'),2) and max_etime > nvl(to_number('&min_etime'),.1) ) where result like nvl('&Faster_Slower',result) order by norm_stddev / ************************SGA FREE MEMORY **************************************** select name, sgasize/1024/1024 "Allocated (M)", bytes/1024 "Free (K)", round(bytes/sgasize*100, 2) "% Free" from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f where f.name = 'free memory' / ************************CPU ************************ select BEGIN_TIME, METRIC_NAME, AVERAGE from DBA_HIST_SYSMETRIC_SUMMARY where METRIC_NAME in ('Host CPU Utilization (%)') and MAXVAL=100 and AVERAGE>90; ************************ holder /waiter **************************************** SELECT /*+ RULE */ DECODE(request, 0,'HOLDER','WAITER'), sid , lmode, TYPE, ctime FROM v$lock WHERE (id1, id2, TYPE ) IN (SELECT id1, id2, TYPE FROM v$lock WHERE request>0) ORDER BY id1, request; SELECT t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.STATISTIC_NAME, t.VALUE ,i.table_name,i.INI_TRANS,i.MAX_TRANS FROM v$segment_statistics t ,dba_indexes i WHERE t.STATISTIC_NAME = 'ITL waits' AND t.VALUE > 0 and t.OBJECT_type='INDEX' and t.owner='STIRM2I' and i.index_name=t.object_name and i.table_name='&tname'; select dbms_metadata.get_ddl('&ttype','&tname','&owner') from dual; select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb; set linesize 150 set echo off col oruser format a20 heading "Oracle Username" col osuser format a20 heading "O/S Username" col obj format a30 heading "Locked Object" col ss heading "SID/Ser#" format a12 col time heading "Logon Date/Time" format a19 col unix heading "Unix|Process" format a9 set linesize 120 select owner||'.'||object_name obj ,oracle_username||' ('||s.status||')' oruser ,os_user_name osuser ,l.process unix ,s.sid||','||s.serial# as ss ,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time from v$locked_object l ,dba_objects o ,v$session s where l.object_id = o.object_id and s.sid=l.session_id order by osuser, ss, obj; ******************************************************************************** TO GET THE INFORMATION OF LOCKS ON A PARTICULAR OBJECT ********************************************************************************* SELECT b.sid, b.serial#, d.spid, a.KGLPNMOD, a.KGLPNREQ, b.username, c.KGLNAOBJ, c.KGLOBTYP FROM x$kglpn a, v$session b, x$kglob c, v$process d WHERE d.addr=b.paddr and a.KGLPNUSE = b.saddr and upper(c.KGLNAOBJ) like upper('%&OBJNAME%') and a.KGLPNHDL = c.KGLHDADR order by 4; ---------------------library cache pin in 10g --------------------- select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE', 32, 'INDEXTYPE', 33, 'OPERATOR', 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', 40, 'LOB PARTITION', 41, 'LOB SUBPARTITION', 42, 'MATERIALIZED VIEW', 43, 'DIMENSION', 44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN', 48, 'CONSUMER GROUP', 51, 'SUBSCRIPTION', 52, 'LOCATION', 55, 'XML SCHEMA', 56, 'JAVA DATA', 57, 'SECURITY PROFILE', 59, 'RULE', 62, 'EVALUATION CONTEXT', 'UNDEFINED') object_type, lob.KGLNAOBJ object_name, pn.KGLPNMOD lock_mode_held, pn.KGLPNREQ lock_mode_requested, ses.sid, ses.serial#, ses.username FROM x$kglpn pn, v$session ses, x$kglob lob, v$session_wait vsw WHERE pn.KGLPNUSE = ses.saddr and pn.KGLPNHDL = lob.KGLHDADR and lob.kglhdadr = vsw.p1raw and vsw.event = 'library cache pin' order by lock_mode_held desc / ************************get sql from sid *************************************** set linesize 132 SELECT SQL_TEXT FROM V$SESSION, V$SQLtext WHERE V$SESSION.SQL_ADDRESS = V$SQLtext.ADDRESS AND V$SESSION.SID = &SID order by piece **************************get sql from spid ************************** set linesize 132 SELECT SQL_HASH_VALUE,SQL_TEXT FROM V$SESSION, V$SQLtext WHERE V$SESSION.SQL_ADDRESS = V$SQLtext.ADDRESS AND V$SESSION.SID in ( select a.sid from v$session a, v$process b where a.paddr=b.addr and b.spid=&spid ) order by piece **************************wait events***************************** col sid format 99999 col event format a30 col state format a25 col p1 format 99999999999999 col p2 format 99999999999999 set pages 150 set lines 100 break on event compute count of event on event select sid,event,p1,p2 , p3 from v$session_wait where event not like '%message%' and state = 'WAITING' order by event; ****************************************** Top 5 events in Database **************************************** select event, total_waits, round(time_waited/100) "TIME(s)", average_wait*10 "AVG(ms)", TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') time from v$system_event where time_waited > 100 and event not in ('rdbms ipc message','pmon timer','control file heartbeat','smon timer') order by time_waited; **************************************** Top CPU SQL ******************************************************* SELECT * FROM ( SELECT s.username, s.module, s.sid, s.serial#, count(*) FROM v$active_session_history h, v$session s WHERE h.session_id = s.sid AND h.session_serial# = s.serial# AND session_state= 'ON CPU' AND sample_time > sysdate - interval '15' minute GROUP BY s.username, s.module, s.sid, s.serial# ORDER BY count(*) desc ) where rownum <= 15; *************************Long Running Sessions********************* SELECT SID, target || opname target , TOTALWORK, SOFAR, TIME_REMAINING, ELAPSED_SECONDS FROM V$SESSION_LONGOPS where time_remaining > 0 order by time_remaining; ******************** No. of open cursor per session ******************** col sid for a12 col program for a30 col username for a15 select substr(a.sid,1,10) sid, substr(nvl(b.program,machine),1,30) program, username, count(*) from v$open_cursor a, v$session b where a.saddr=b.saddr --and username='CRMUSER' group by substr(a.sid,1,10), substr(nvl(b.program,machine),1,30),username order by 4 desc ; set lines 150 set pages 5000 col sql_text for a50 select sid,user_name,sql_id,cnt,(select sql_text from v$sqlarea where sql_id=a.sql_id) as sql_text from (select sid,user_name,sql_id,count(*) as cnt from v$open_cursor a group by sid,user_name,sql_id) a where user_name not in ('SYS','SYSTEM') order by 4; select max(a.value) as highest_open_cur, p.value as max_open_cur from v$sesstat a, v$statname b, v$parameter p where a.statistic# = b.statistic# and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value; select cach.value cache_hits, prs.value all_parses,prs.value-cach.value sess_cur_cache_not_used from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2 where cach.statistic# = nm1.statistic# and nm1.name = 'session cursor cache hits' and prs.statistic#=nm2.statistic# and nm2.name= 'parse count (total)' and cach.sid= &sid and prs.sid= cach.sid ; select s.username, max(a.value) from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid (+)= a.sid and b.name = 'opened cursors current' group by s.username; list the top 10 sessions which are currently opening most cursors:- select * from ( select ss.value, sn.name, ss.sid from v$sesstat ss, v$statname sn where ss.statistic# = sn.statistic# and sn.name like '%opened cursors current%' order by value desc) where rownum < 11; select sid, status, event, seconds_in_wait, state, blocking_session, prev_sql_id from v$session where sid=1515 select sql_text from v$sqlarea where sql_id ='g2b20uz9xpdg0' select * from (select SQL_TEXT, sql_id, user_name, count(*) from v$open_cursor where sid=1515 group by SQL_TEXT, sql_id, user_name order by count(*) desc) where rownum < 11; The cursor has been opened with closing in whole instance:- select * from (select SQL_TEXT, sql_id, user_name, count(*) from v$open_cursor group by SQL_TEXT, sql_id, user_name order by count(*) desc) where rownum < 11; --total cursors open, by session select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current'; If youÃre running several N-tiered applications with multiple webservers, you may find it useful to monitor open cursors by username and machine: --total cursors open, by username & machine select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, s.username, s.machine from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current' group by s.username, s.machine order by 1 desc; *************************Script to tune open_cursor ********************** select 'session_cached_cursors' parameter, lpad(value, 5) value, decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage from ( select max(s.value) used from v$statname n, v$sesstat s where n.name = 'session cursor cache count' and s.statistic# = n.statistic# ), ( select value from v$parameter where name = 'session_cached_cursors' ) union all select 'open_cursors', lpad(value, 5), to_char(100 * used / value, '990') || '%' from ( select max(sum(s.value)) used from v$statname n, v$sesstat s where n.name in ('opened cursors current', 'session cursor cache count') and s.statistic# = n.statistic# group by s.sid ), ( select value from v$parameter where name = 'open_cursors' ) /*************************Import Performance******************************* select substr(sql_text,instr(sql_text,'INTO "'),30) table_name, rows_processed, round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes, trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min from sys.v_$sqlarea where sql_text like 'INSERT %INTO "%' and command_type = 2 and open_versions > 0; *********************who is using which UNDO or TEMP SEGMENT************** col SID_SERIAL for a11 col ORAUSER for a10 col UNDOSEG for a11 col Undo for a5 col PROGRAM for a30 SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial, NVL(s.username, 'None') orauser,s.program,r.name undoseg, t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo" FROM sys.v_$rollname r,sys.v_$session s,sys.v_$transaction t,sys.v_$parameter x WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size' col username for a10 col SID_SERIAL for a11 col size for a10 col TABLESPACE for a10 col PROGRAM for a40 SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE", a.sid||','||a.serial# SID_SERIAL, a.username, a.program FROM sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr ORDER BY b.tablespace, b.blocks; SELECT sid, to_char(sysdate,'mi:ss') time,round(work_area_size/1048576, 1) work_area_size_mb, round(max_mem_used/1048576, 1) max_mem_used_mb, number_passes, nvl(tempseg_size/1048576, 0) tempseg_size_mb FROM v$sql_workarea_active ORDER BY sid; -For values of PAT below 1 GB, _PGA_MAX_SIZE is 200 MB. -For values of PAT between 1 GB and 2 GB, _PGA_MAX_SIZE is 20% of PAT. -At values beyond 2 GB, _PGA_MAX_SIZE keeps on growing as PAT is increased, but at alower rate, such that _PGA_MAX_SIZE is less than 20% of PAT. -A limit of 480 MB on _PGA_MAX_SIZE takes effect at a PAT value of 4 GB. -Increasing PAT beyond 4 GB does not result in higher values of _PGA_MAX_SIZE than 480 MB. -In Oracle9i, _PGA_MAX_SIZE had a limit of 200 MB. Process memory usage - AIX ps -A -o user,pid,pcpu,pmem,vsz,time,args | grep EBA | sort +4 SELECT PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM/(1024*1024) "ALLOC(MB)", PGA_FREEABLE_MEM FREEABLE, PGA_MAX_MEM FROM V$PROCESS order by pga_alloc_mem asc; ********************Undo usage************************** col program format a30 col sid_serial format a30 set lines 500 SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial, NVL(s.username, 'None') orauser,s.status, s.program, r.name undoseg, t.used_ublk * TO_NUMBER(x.value)/1024/1024||'MB' "Undo" FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size'; **************************************************EXPIRED************************************************* select((select (nvl(sum(bytes),0)) from dba_undo_extents where tablespace_name='&undo_name' and status in ('ACTIVE','UNEXPIRED')) *100) / (select sum(bytes) from dba_data_files where tablespace_name='&undo_name') "PCT_INUSE" from dual; ******************** Undo recover session ******************** select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo", decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete" from v$fast_start_transactions; select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk from v$session a, v$transaction b where a.saddr=b.ses_addr; ******************** Find out required space for undo tablespace if undo retention period is known ******************** SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", (TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024) "NEEDED UNDO SIZE [MByte]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f, ( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size' / **************************** OR (Dynamicly get undo retention time in hrs) ********************************* SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", (&reqiered_undoretentionin_hrs*60*60 * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024) "NEEDED UNDO SIZE [MByte]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f, ( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size' / ************************Redo Usage **************************************** select round(t.value/s.value,5) "Redo Log Space Request Ratio" from v$sysstat s, v$sysstat t where s.name = 'redo log space requests' and t.name = 'redo entries'; The redo log space request ratio is simply the ratio of total redo log space requests to redo entries. You can also query the V$SYSSTAT view to find the value of the statistic redo buffer allocation retries. This statistic shows the number of times processes waited for space in the redo log buffer: select name,value from V$SYSSTAT where name= 'redo buffer allocation retries'; ************************how full is current log file*********************** SELECT le.leseq "Current log sequence No", 100*cp.cpodr_bno/le.lesiz "Percent Full", cp.cpodr_bno "Current Block No", le.lesiz "Size of Log in Blocks" FROM x$kcccp cp, x$kccle le WHERE le.leseq =CP.cpodr_seq AND bitand(le.leflg,24) = 8 ********************PARTATION********************************** select distinct substr(name,1,instr(name,'/',-1,1)-1) partion from v$datafile union select substr(name,1,instr(name,'/',-1,1)-1) partion from V$tempfile union select substr(name,1,instr(name,'/',-1,1)-1) partion from v$controlfile union select substr(member,1,instr(member,'/',-1,1)-1) partion from v$logfile; **********************CRETAE TABLESPACE*********************************** create tablespace &TABLESPACE_NAME datafile '&datafile' size &size segment space management auto extent management local uniform size 1M ***********************ADD DATAFILE**************************************** alter tablespace &TABLESPACE_NAME add datafile '&DATAFILE_NAME' size &SIZE ************************privileges***************************************** select grantee,privilege from dba_sys_privs where privilege like '%&NAME%'; ************************aud.sql******************************************** set lines 122 col grantee for a8 col grantor for a8 col owner for a10 col granted_role for a10 col temporary_tablespace for a5 col default_tablespace for a12 col privilege for a10 col profile for a27 Select grantee, granted_role from dba_role_privs where grantee in ('RQUERY','BQUERY','HQUERY'); Select userid, scope from SYSTEM.PRODUCT_USER_PROFILE where userid in ('RQUERY','BQUERY','HQUERY'); Select grantee,owner,table_name,grantor,SELECT_PRIV, INSERT_PRIV,DELETE_PRIV,UPDATE_PRIV, REFERENCES_PRIV, ALTER_PRIV,INDEX_PRIV from table_privileges where grantee in ('RQUERY','BQUERY','HQUERY'); Select * from dba_tab_privs where Owner != 'SYS' and Owner != 'SYSTEM' and Grantable ='YES'; select * from dba_tab_privs where table_name in ('UTL_TCP','UTL_HTTP','UTL_FILE','UTL_SMTP','UTL_FILE_DIR'); SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE PRIVILEGE IN ('CREATE LIBRARY', 'ALTER SYSTEM'); Select username,PROFILE, Default_tablespace, temporary_tablespace from dba_users where username in ('RQUERY','BQUERY','HQUERY'); Column tab_name format a20 Column grantee format a20 select rpad(table_name,20) tab_name,rpad(grantee,20) grantee from dba_tab_privs where privilege='EXECUTE' and table_name in ('UTL_FILE','UTL_HTTP','UTL_TCP','UTL_SMTP', 'UTL_FILE_DIR'); *************************ITL waits ***************************************** select * from (select DECODE(GROUPING(a.object_name), 1, 'All Objects', a.object_name) AS "Object", sum(case when a.statistic_name = 'ITL waits' then a.value else null end) "ITL Waits",sum(case when a.statistic_name = 'buffer busy waits' then a.value else null end) "Buffer Busy Waits",sum(case when a.statistic_name = 'row lock waits' then a.value else null end) "Row Lock Waits",sum(case when a.statistic_name = 'physical reads' then a.value else null end) "Physical Reads",sum(case when a.statistic_name = 'logical reads' then a.value else null end) "Logical Reads" from v$segment_statistics a / scripts for detecting ITL waits: Select s.sid SID, s.serial# Serial#, l.type type, ' ' object_name, lmode held, request request from v$lock l, v$session s, v$process p where s.sid = l.sid and s.username <> ' ' and s.paddr = p.addr and l.type <> 'TM' and (l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6) union select s.sid SID, s.serial# Serial#, l.type type, object_name object_name, lmode held, request request from v$lock l, v$session s, v$process p, sys.dba_objects o where s.sid = l.sid and o.object_id = l.id1 and l.type = 'TM' and s.username <> ' ' and s.paddr = p.addr union select s.sid SID, s.serial# Serial#, l.type type, '(Rollback='||rtrim(r.name)||')' object_name, lmode held, request request from v$lock l, v$session s, v$process p, v$rollname r where s.sid = l.sid and l.type = 'TX' and l.lmode = 6 and trunc(l.id1/65536) = r.usn and s.username <> ' ' and s.paddr = p.addr order by 5, 6; *************************FREE TABLESPACE QUERIES**************************** SELECT Total.tablespace_name "Tablespace Name", Free_space, Max_Size, (total_space-Free_space) Used_space, total_space FROM (select tablespace_name, sum(bytes/1024/1024) Free_Space,max(bytes/1024/1024) Max_Size from sys.dba_free_space group by tablespace_name ) Free, (select tablespace_name, sum(bytes/1024/1024) TOTAL_SPACE from sys.dba_data_files group by tablespace_name ) Total WHERE Free.Tablespace_name = Total.tablespace_name; OR ttitle - center 'Database Freespace Summary' skip 2 comp sum of nfrags totsiz avasiz on report break on report col tsname format a16 justify c heading 'Tablespace' col nfrags format 999,990 justify c heading 'Free|Frags' col mxfrag format 999,999,990 justify c heading 'Largest|Frag (KB)' col totsiz format 999,999,990 justify c heading 'Total|(KB)' col avasiz format 999,999,990 justify c heading 'Available|(KB)' col pctusd format 990 justify c heading 'Pct|Used' select total.tablespace_name tsname, count(free.bytes) nfrags, nvl(max(free.bytes)/1024,0) mxfrag, total.bytes/1024 totsiz, nvl(sum(free.bytes)/1024,0) avasiz, (1-nvl(sum(free.bytes),0)/total.bytes)*100 pctusd from dba_data_files total, dba_free_space free where total.tablespace_name = free.tablespace_name(+) and total.file_id=free.file_id(+) group by total.tablespace_name,total.bytes; OR with ts as (select name,blocksize,ts#,bitmapped from ts$) select /*+ RULE */ a.name "Tablespace Name",total "Total Space",total-free "Used Space",free "Free Space",(free/total)*100 "% Free" from (select ts.name, sum(f.length * ts.blocksize/1024/1024) free from ts,sys.fet$ f where ts.ts# = f.ts# and ts.bitmapped = 0 group by ts.name )a, (select ts.name, sum((ts.blocksize * f.blocks)/1024/1024) Total from sys.file$ f,ts where f.ts# = ts.ts# and bitmapped=0 group by ts.name )b where a.name=b.name union all SELECT name,Total,total-free,free,(free/total)*100 "% used" from (select name, sum(round(KTFBHCSZ*blocksize/1024/1024,2)) total, sum(round(KTFBHCFREE*blocksize/1024/1024,2)) free FROM x$ktfbhc,ts where ts#=KTFBHCTSN group by name )order by 5;******************************* Check Table Fragmentation If analyzed **************************************** select owner,table_name,last_analyzed, round((blocks*&blksize_KB/1024),2) "Total Table size MB", round(((num_rows*avg_row_len/1024/1024)+(blocks*8/1024/&pctfree)),2) "Actual Table size MB" from dba_tables where table_name='&table_name'; set lines 200 set pages 200 col owner for a15 select owner,table_name,last_analyzed, round(blocks*8/1024) "Total Table size MB", round(num_rows*avg_row_len/1024/1024) "Actual Table size MB", round((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)) "Fragmentation MB" from dba_tables where owner='ICICI_RB_ED' and TEMPORARY='N' and SECONDARY='N' and DROPPED='NO' and TABLE_NAME in ('Table List') order by 6 desc; ****************************** Archive Apply timing details ************************************************* alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss'; set lines 150 set pages 5000 select TIMESTAMP,completion_time "ArchTime", SEQUENCE#,round((blocks*block_size)/(1024*1024),1) "SizeM", round((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60,1) "Diff(sec)", round((blocks*block_size)/1024/ decode(((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),0,1, (TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),1) "KB/sec", round((blocks*block_size)/(1024*1024)/ decode(((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),0,1, (TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),3) "MB/sec", round(((lead(TIMESTAMP,1,TIMESTAMP) over (order by TIMESTAMP))-completion_time)*24*60*60,1) "Lag(sec)" from v$archived_log a, v$dataguard_status dgs where a.name = replace(dgs.MESSAGE,'Media Recovery Log ','') and dgs.FACILITY = 'Log Apply Services' order by TIMESTAMP desc; **************************archive generation************************** select trunc(first_time) on_date, thread# thread, min(sequence#) min_sequence, max(sequence#) max_sequence, max(sequence#) - min(sequence#) nos_archives, (max(sequence#) - min(sequence#)) * log_avg_mb req_space_mb from v$log_history, (select avg(bytes/1024/1024) log_avg_mb from v$log) group by trunc(first_time), thread#, log_avg_mb order by trunc(first_time); HOURLY Statistics: select to_char(first_time,'dd-mon-yy hh24') on_date, min(sequence#) min_sequence, max(sequence#) max_sequence, max(sequence#) - min(sequence#) nos_archives, (max(sequence#) - min(sequence#)) * log_avg_mb req_space_mb from v$log_history, (select avg(bytes/1024/1024) log_avg_mb from v$log) where to_char(first_time,'dd-mon-yyyy')='25-apr-2011' group by to_char(first_time,'dd-mon-yy hh24'), thread#, log_avg_mb order by 1; SELECT * FROM ( SELECT * FROM ( SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '999') "00:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '999') "01:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '999') "02:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '999') "03:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '999') "04:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '999') "05:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '999') "06:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '999') "07:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '999') "08:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '999') "09:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '999') "10:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '999') "11:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '999') "12:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '999') "13:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '999') "14:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '999') "15:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '999') "16:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '999') "17:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '999') "18:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '999') "19:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '999') "20:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '999') "21:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '999') "22:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '999') "23:00" FROM V$LOG_HISTORY WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate) GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM') ) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC ) WHERE ROWNUM < 15; select to_char(first_time,'dd-mon-yy hh24:MI:SS') on_date, sequence#, BLOCKS*BLOCK_SIZE/1024/1024 Msize from V$ARCHIVED_LOG, (select avg(bytes/1024/1024) log_avg_mb from v$log) where to_char(first_time,'dd-mon-yyyy')>sysdate-30 and to_char(first_time,'hh24:MI:SS') between ('15:00:00') and ('15:30:00') group by to_char(first_time,'dd-mon-yy hh24:MI:SS'), thread#, log_avg_mb,sequence#,BLOCKS*BLOCK_SIZE/1024/1024 order by 1; LOG SWITCH INFORMATION:- -------------------------- select trunc(first_time, 'HH') , count(*) from v$loghist group by trunc(first_time, 'HH') order by 1; ---------------------------- alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; select trunc(first_time, 'HH') , count(*) from v$loghist group by trunc(first_time, 'HH') order by trunc(first_time, 'HH'); Session generating lots of redo:- -------------------------------- SELECT s.sid, s.serial#, s.username, s.program, i.block_changes FROM v$session s, v$sess_io i WHERE s.sid = i.sid ORDER BY 5 desc, 1, 2, 3, 4; ******************* See Logfile Contents using LogMnr ************************** exec DBMS_LOGMNR.ADD_LOGFILE('/dev/vgredo01/rlog1a'); exec DBMS_LOGMNR.START_LOGMNR(options => dbms_logmnr.dict_from_online_catalog); For Pretty output: exec dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.print_pretty_sql + dbms_logmnr.no_sql_delimiter); select username, to_char(timestamp,'mm/dd/yy hh24:mi:ss') timestamp, seg_type_name, seg_name, table_space, session# SID, serial# from v$logmnr_contents where seg_owner = 'PRIMECC' and operation in ('UPDATE','INSERT'); select sql_undo, sql_redo from v$logmnr_contents where seg_owner = 'PRIMECC' and operation = 'UPDATE'; For Remote mining : exec dbms_logmnr_d.build (options => dbms_logmnr_d.store_in_redo_logs); exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_redo_logs); OR utl_file_dir = '/tmp' exec dbms_logmnr_d.build (dictionary_filename => 'ora_dict.txt',dictionary_location => '/tmp'); dbms_logmnr.start_logmnr(dictfilename => '/tmp/ora_dict.txt'); execute dbms_logmnr.end_logmnr; *********************** Redo genrated per session ***************************** set linesize 120 col module format a20 col osuser format a20 select * from ( sELECT s.sid, module, osuser,username,status, sql_hash_value, value redo, to_char(logon_time, 'dd/mm/yyyy hh24:mi:ss') LOGON FROM gv$session s, gv$sesstat ss, gv$statname sn WHERE s.sid = ss.sid AND ss.statistic# = sn.statistic# AND name = 'redo size' ORDER BY redo desc ) where rownum <=5 / set linesize 120 col module format a20 select * from ( SELECT distinct s.SESSION_ID, module, USER_ID,s.sql_id,value redo, to_char(SAMPLE_TIME, 'dd/mm/yyyy hh24:mi:ss') lt FROM DBA_HIST_ACTIVE_SESS_HISTORY s, DBA_HIST_SYSSTAT ss WHERE s.SNAP_ID = ss.SNAP_ID AND ss.STAT_NAME = 'redo size' and s.SNAP_ID between 21774 and 21776 ORDER BY redo desc ) where rownum <=5 / Total Redo By Session:- set linesize 121 col module format a30 SELECT module, osuser, sql_hash_value, value redo FROM gv$session s, gv$sesstat ss, gv$statname sn WHERE s.sid = ss.sid AND ss.statistic# = sn.statistic# AND name = 'redo size' ORDER BY redo; Total Redo By Session Per Time Period:- SELECT module, osuser, sql_hash_value, value/(sysdate-logon_time) redo FROM gv$session s, gv$sesstat ss, gv$statname sn WHERE s.sid = ss.sid AND ss.statistic# = sn.statistic# AND name = 'redo size' ORDER BY redo; Redo Generated by SID:- col sid form 9999 col username form a10 col value Head "Redo|Generated|in MB" form 9999999999.999 col program form a30 col logtime head "Logon Time" form a15 SELECT st.sid, se.username, TO_CHAR(se.logon_time,'dd-mon-yy hh24:mi') logtime, se.program, (value/1048576) VALUE FROM gv$sesstat st, gv$statname sn, gv$session se WHERE sn.name = 'redo size' AND sn.statistic# = st.statistic# AND st.sid = se.sid AND value <> 0 ORDER BY 5; Redo Generation by SID:- col value format 9999999 SELECT s.sid, n.name, s.value FROM gv$sesstat s, gv$statname n WHERE n.name = 'redo blocks written' AND s.statistic# = n.statistic# ORDER BY value; *********************************************PARAMETER HISTORY **************** set lines 200 pages 200 col BEGIN_INTERVAL_TIME for a25 col END_INTERVAL_TIME for a25 col PARAMETER_NAME for a45 col value for a25 select s.snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,PARAMETER_NAME,VALUE from dba_hist_snapshot s,dba_hist_parameter p where parameter_name='¶meter' and s.snap_id=p.snap_id order by BEGIN_INTERVAL_TIME; ***************************CREATE DATABASE************************************* CREATE DATABASE PROFUNDS LOGFILE GROUP 1 ('/profundsdb1/PROFUNDS/oradata/log/PROFUNDS_redo1a.log', '/profundsarch/PROFUNDS/log/PROFUNDS_redo1b.log') SIZE 100M , GROUP 2 ('/profundsdb1/PROFUNDS/oradata/log/PROFUNDS_redo2a.log', '/profundsarch/PROFUNDS/log/PROFUNDS_redo2b.log') SIZE 100M , GROUP 3 ('/profundsdb1/PROFUNDS/oradata/log/PROFUNDS_redo3a.log', '/profundsarch/PROFUNDS/log/PROFUNDS_redo3b.log') SIZE 100M , GROUP 4 ('/profundsdb1/PROFUNDS/oradata/log/PROFUNDS_redo4a.log', '/profundsarch/PROFUNDS/log/PROFUNDS_redo4b.log') SIZE 100M , GROUP 5 ('/profundsdb1/PROFUNDS/oradata/log/PROFUNDS_redo5a.log', '/profundsarch/PROFUNDS/log/PROFUNDS_redo5b.log') SIZE 100M MAXLOGFILES 50 MAXLOGHISTORY 2336 MAXDATAFILES 1024 MAXINSTANCES 4 NOARCHIVELOG CHARACTER SET US7ASCII DATAFILE '/profundsdb1/PROFUNDS/oradata/data/PROFUNDS_SYSTEM_01.dbf' size 1000M Extent management local sysaux datafile '/profundsdb1/PROFUNDS/oradata/data/PROFUNDS_SYSAUX_01.dbf' size 1000m DEFAULT TEMPORARY TABLESPACE LMTEMP tempfile '/profundsdb1/PROFUNDS/oradata/data/PROFUNDS_LMTEMP_01.dbf' reuse Extent management local uniform size 1m UNDO TABLESPACE UNDOTBS datafile '/profundsdb1/PROFUNDS/oradata/data/PROFUNDS_UNDOTBS_01.dbf' size 1000M; ******************************PARALLEL Query********************************** set lines 200 column OSUSER format a25 column child_wait format a30 column parent_wait format a30 column server_name format a4 heading 'Name' column x_status format a10 heading 'Status' column schemaname format a10 heading 'Schema' column x_sid format 9990 heading 'Sid' column x_pid format 9990 heading 'Pid' column p_sid format 9990 heading 'Parent' break on p_sid skip 1 select x.server_name , x.status as x_status , x.pid as x_pid , x.sid as x_sid , w2.sid as p_sid , v.osuser , v.schemaname , w1.event as child_wait , w2.event as parent_wait from v$px_process x , v$lock l , v$session v , v$session_wait w1 , v$session_wait w2 where x.sid <> l.sid(+) and to_number (substr(x.server_name,2)) = l.id2(+) and x.sid = w1.sid(+) and l.sid = w2.sid(+) and x.sid = v.sid(+) and nvl(l.type,'PS') = 'PS' order by 1,2 / col username for a12 col "QC SID" for A6 col "SID" for A6 col "QC/Slave" for A8 col "Req. DOP" for 9999 col "Actual DOP" for 9999 col "Slaveset" for A8 col "Slave INST" for A9 col "QC INST" for A6 set pages 300 lines 300 col wait_event format a30 select decode(px.qcinst_id,NULL,username, ' - '||lower(substr(pp.SERVER_NAME, length(pp.SERVER_NAME)-4,4) ) )"Username", decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" , to_char( px.server_set) "SlaveSet", to_char(s.sid) "SID", to_char(px.inst_id) "Slave INST", decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE, case sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event , decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID", to_char(px.qcinst_id) "QC INST", px.req_degree "Req. DOP", px.degree "Actual DOP" from gv$px_session px, gv$session s , gv$px_process pp, gv$session_wait sw where px.sid=s.sid (+) and px.serial#=s.serial#(+) and px.inst_id = s.inst_id(+) and px.sid = pp.sid (+) and px.serial#=pp.serial#(+) and sw.sid = s.sid and sw.inst_id = s.inst_id order by decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID), px.QCSID, decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID / set pages 300 lines 300 col wait_event format a30 select sw.SID as RCVSID, decode(pp.server_name, NULL, 'A QC', pp.server_name) as RCVR, sw.inst_id as RCVRINST, case sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event , decode(bitand(p1, 65535), 65535, 'QC', 'P'||to_char(bitand(p1, 65535),'fm000')) as SNDR, bitand(p1, 16711680) - 65535 as SNDRINST, decode(bitand(p1, 65535), 65535, ps.qcsid, (select sid from gv$px_process where server_name = 'P'||to_char(bitand(sw.p1, 65535),'fm000') and inst_id = bitand(sw.p1, 16711680) - 65535) ) as SNDRSID, decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE from gv$session_wait sw, gv$px_process pp, gv$px_session ps where sw.sid = pp.sid (+) and sw.inst_id = pp.inst_id (+) and sw.sid = ps.sid (+) and sw.inst_id = ps.inst_id (+) and p1text = 'sleeptime/senderid' and bitand(p1, 268435456) = 268435456 order by decode(ps.QCINST_ID, NULL, ps.INST_ID, ps.QCINST_ID), ps.QCSID, decode(ps.SERVER_GROUP, NULL, 0, ps.SERVER_GROUP), ps.SERVER_SET, ps.INST_ID / set pages 300 lines 300 col "Username" for a12 col "QC/Slave" for A8 col "Slaveset" for A8 col "Slave INST" for A9 col "QC SID" for A6 col "QC INST" for A6 col "operation_name" for A30 col "target" for A30 select decode(px.qcinst_id,NULL,username, ' - '||lower(substr(pp.SERVER_NAME, length(pp.SERVER_NAME)-4,4) ) )"Username", decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" , to_char( px.server_set) "SlaveSet", to_char(px.inst_id) "Slave INST", substr(opname,1,30) operation_name, substr(target,1,30) target, sofar, totalwork, units, start_time, timestamp, decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID", to_char(px.qcinst_id) "QC INST" from gv$px_session px, gv$px_process pp, gv$session_longops s where px.sid=s.sid and px.serial#=s.serial# and px.inst_id = s.inst_id and px.sid = pp.sid (+) and px.serial#=pp.serial#(+) order by decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID), px.QCSID, decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID /***************** Take a log before down any production database for any activity. ****************** set timing on; set echo on; set pagesize 150; set linesize 1000; set time on; spool ODTRA_02SEP09.lst select '--------------------db-logs '||sysdate||'--------------------' from dual; select * from v$database; select * from v$instance; select * from v$version; select * from v$controlfile; select * from v$log; select * from v$logfile; select * from v$recover_file; select file#,error,fuzzy from v$datafile_header; select * from dba_data_files order by file_name; select * from dba_temp_files order by file_name; select file_name, status, bytes/1024/1024 sz_mb from dba_data_files order by file_name; select file_name, status, bytes/1024/1024 sz_mb from dba_temp_files order by file_name; select username, status, count(*) from v$session where username is not null group by username, status; archive log list; show parameter background; show parameter; select process, status, state from v$archive_processes; alter system flush shared_pool; alter database backup controlfile to trace; alter system switch logfile; alter system switch logfile; alter system checkpoint; alter system checkpoint; set echo off select '--------------------'||database_name ||' is ready for clean Shutdown----------' from v$database; spool off ***************************** Rollback Remaining **************************** select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo", decode(cputime,0,'unknown', sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete" from v$fast_start_transactions; select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk from v$session a, v$transaction b where a.saddr=b.ses_addr; select sum(distinct(ktuxesiz)) from x$ktuxe where ktuxecfl = 'DEAD' ****************Sessions waiting for a Library Cache Pin: ***************** select sid Waiter, substr(rawtohex(p1),1,30) Handle, substr(rawtohex(p2),1,30) Pin_addr from v$session_wait where wait_time=0 and event like 'library cache lock%'; ********Objets concerning to Library Cache Pin we are waiting for:********* select to_char(SESSION_ID,'999') sid , substr(LOCK_TYPE,1,30) Type, substr(lock_id1,1,23) Object_Name, substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ, lock_id2 Lock_addr from dba_lock_internal where mode_requested<>'None' and mode_requested<>mode_held and session_id in ( select sid from v$session_wait where wait_time=0 and event like 'library cache lock%'); *********Library Cache Pin holders we are wainting for:******************** select sid Holder ,KGLPNUSE Sesion , KGLPNMOD Held, KGLPNREQ Req from x$kglpn , v$session where KGLPNHDL in (select p1raw from v$session_wait where wait_time=0 and event like 'library cache lock%') and KGLPNMOD <> 0 and v$session.saddr=x$kglpn.kglpnuse; *****************What are the holders waiting for?************************** select sid,substr(event,1,30),wait_time from v$session_wait where sid in (select sid from x$kglpn , v$session where KGLPNHDL in (select p1raw from v$session_wait where wait_time=0 and event like 'library cache lock%') and KGLPNMOD <> 0 and v$session.saddr=x$kglpn.kglpnuse ); ***********************To identify SQL: ************************************ select sid,sql_text from v$session, v$sqlarea where v$session.sql_address=v$sqlarea.address and sid=&sid; ************************To get Enquence name******************************** col Name format a4 select sid,chr(bitand(p1, -16777216)/16777215) || chr(bitand(p1,16711680)/65535) "Name", (bitand(p1, 65535)) "Mode"from v$session_wait where event = 'enqueue'; ****************Blocking session*********************** SELECT sid, blocking_session, username, event, seconds_in_wait siw FROM v$session WHERE blocking_session_status = 'VALID' and event like 'library cache lock'; select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session where blocking_session is not NULL order by blocking_session; *********************** Waiting Session - enq: TX - contention event ************************ SELECT sid, CHR (BITAND (p1,-16777216) / 16777215) || CHR (BITAND (p1, 16711680) / 65535) enq, DECODE (BITAND (p1, 65535), 1, 'Null', 2, 'Sub-Share', 3, 'Sub-Exclusive', 4, 'Share', 5, 'Share/Sub-Exclusive', 6, 'Exclusive', 'Other') lock_mode FROM v$session_wait WHERE sid = 481; set linesize 100 set pagesize 66 col c1 for a15 col c1 heading "Program Name " select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST from gv$lock l,gv$session s where l.type like 'TX' and l.REQUEST =6 and l.inst_id=s.inst_id and l.sid=s.sid order by id1 / select distinct a.sid "waiting sid" , d.sql_text "waiting SQL" , a.ROW_WAIT_OBJ# "locked object" , a.BLOCKING_SESSION "blocking sid" , c.sql_text "SQL from blocking session" from v$session a, v$active_session_history b, v$sql c, v$sql d where a.event='enq: TX - row lock contention' and a.sql_id=d.sql_id and a.blocking_session=b.session_id and c.sql_id=b.sql_id and b.CURRENT_OBJ#=a.ROW_WAIT_OBJ# and b.CURRENT_FILE#= a.ROW_WAIT_FILE# and b.CURRENT_BLOCK#= a.ROW_WAIT_BLOCK# / SELECT DISTINCT a.sid "waiting sid" , a.event , c.sql_text "SQL from blocked session" , b.sid "blocking sid" , b.event , b.sql_id , b.prev_sql_id , d.sql_text "SQL from blocking session" FROM v$session a, v$session b, v$sql c , v$sql d WHERE a.event ='enq: TX - row lock contention' AND a.blocking_session=b.sid AND c.sql_id =a.sql_id AND d.sql_id =NVL(b.sql_id,b.prev_sql_id) / *************** Holding Session - enq: TX - contention event ********************** select sid,serial#, status from v$session where taddr in (select b.addr from v$session_wait a,v$transaction b where a.event='enq: TX - contention' and trunc(a.p2/power(2,16)) = xidusn and (bitand(a.p2,to_number('ffff','xxxx'))+0) = xidslot and a.p3 = xidsqn); select sid,id1,id2,trunc(id1/power(2,16)) rbs,bitand(id1,to_number('ffff','xxxx'))+0 slot, id2 seq,lmode,request,type from v$lock where type = 'TX' and sid=&sid; select sid,trunc(id1/power(2,16)) rbs,bitand(id1,to_number('ffff','xxxx'))+0 slot,id2 seq,lmode,request from v$lock where type='TX' and id1=&id1 and id2=&id2; set linesize 100 set pagesize 66 col c1 for a15 col c1 heading "Program Name " select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST from gv$lock l,gv$session s where l.type like 'TX' and l.LMODE =6 and (l.ID1,l.ID2) in (select id1,id2 from gv$lock where type like 'TX' and REQUEST =6) and l.inst_id=s.inst_id and l.sid=s.sid order by id1 / *****************Oracle EXPORT using OS compression online**************************** /etc/mknod per1 p nohup compress < per1 > exp_CIBIL_DETAIL_RAPG_22mar09.comp & sleep 120 exp \"/ as sysdba\" file=per1 log=exp_CIBIL_DETAIL_RAPG_22mar09.log tables=FINNQUERY.CIBIL_DETAIL_RAPG buffer=500000000 feedback=10000 statistics=none compress=n rm per1 OR /etc/mknod /icicipru_archs/DATAEXT_EXPORT/per3 p nohup compress < per3 > /icicipru_archs/DATAEXT_EXPORT/exp_DATAEXT_01May09.comp & sleep 10 exp userid=\'/ as sysdba\' buffer=500000000 file=per3 log=exp_DATAEXT_01May09.log full=Y indexes=Y ******************** Install All Components (XML,JAVA,OLAP) **************************** set echo on set timing on set ti on spool FULL_OLAPINST.log; alter system set "_system_trig_enabled" = false scope=memory; CREATE TABLESPACE XDB DATAFILE '/apsrussia2/oradata/testa/test10g/data/TEST_XDB_01.dbf' SIZE 1000M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; SQL> drop trigger sys.xdb_installation_trigger; SQL> drop trigger sys.dropped_xdb_instll_trigger; SQL> drop table dropped_xdb_instll_tab; @/ora10204/oracle/rdbms/admin/catqm.sql password XDB LMTEMP @/ora10204/oracle/rdbms/admin/catxdbj.sql select count(*), object_type from dba_objects where owner='XDB' group by object_type; @/ora10204/oracle/javavm/install/initjvm.sql select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; @/ora10204/oracle/xdk/admin/initxml.sql select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; @/ora10204/oracle/xdk/admin/xmlja.sql select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; @/ora10204/oracle/rdbms/admin/catjava.sql select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; @/ora10204/oracle/rdbms/admin/catexf.sql select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; @/ora10204/oracle/olap/admin/olap.sql SYSAUX LMTEMP spool off exit Excute utlrp.sql and restart the database. ******************** Install Oracle Database JVM server ****************************** The system requirements for a JVM installation are as follows :- SHARED_POOL_SIZE >= 60Mb (minimum) JAVA_POOL_SIZE >= 30Mb The SYSTEM tablespace has at least 120Mb of free space The RBS tablespace has at least 250Mb of free space SQL>@$ORACLE_HOME/javavm/install/initjvm.sql -- Loads the Java stored procedures as required by the -- Summary Advisor. SQL>@$ORACLE_HOME/rdbms/admin/initqsma.sql; Check JAccelerator Installation select dbms_java.full_ncomp_enabled from dual; OR drop table "JAVA$JVM$STATUS" cascade constraints; shutdown startup alter system set "_system_trig_enabled" = false scope=memory; @?/javavm/install/rmjvm shutdown immediate exit startup alter system set "_system_trig_enabled" = false scope=memory; @?/javavm/install/initjvm.sql @?/rdbms/admin/utlrp.sql ***************************** Install Oracle XDB *********************************** grant execute on dbms_sql to xdb; grant execute on dbms_lob to xdb; grant execute on utl_file to xdb; XDB Installation : CREATE TABLESPACE XDB DATAFILE '/apsrussia2/oradata/testa/test10g/data/TEST_XDB_01.dbf' SIZE 1000M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; set echo on set ti on set timing on set verify on spool XDB.log @/ora10204/oracle/rdbms/admin/catqm.sql password XDB LMTEMP @/ora10204/oracle/rdbms/admin/catxdbj.sql spool off exit ****************************** Install and Deinstall Oracle OLAP *********************************** OLAP Installation : set echo on set ti on set timing on set verify on spool olap.log @/ora10204/oracle/olap/admin/olap.sql SYSAUX LMTEMP spool off exit OLAP DeInstallation : set echo on set ti on set timing on set verify on spool remove_olap.log @/ora10204/oracle/olap/admin/catnoamd.sql @/ora10204/oracle/olap/admin/olapidrp.plb spool off exit ****************************** Install and Deinstall Oracle Text *********************************** OLAP Installation : set echo on set ti on set timing on set verify on spool text_install.log @?/ctx/admin/catctx.sql CTXSYS CTXDATA LMTEMP NOLOCK Where: CTXSYS - is the ctxsys user password SYSAUX - is the default tablespace for ctxsys TEMP - is the temporary tablespace for ctxsys LOCK|NOLOCK - ctxsys user account will be locked or not connect CTXSYS/password@tns_alias @?/ctx/admin/defaults/drdefus.sql spool off exit --------------------------------------------------------------------------------- Check with Following queries : connect SYS/password as SYSDBA set pages 1000 col object_name format a40 col object_type format a20 col comp_name format a30 column library_name format a8 column file_spec format a60 wrap spool text_install_verification.log -- check on setup select comp_name, status, substr(version,1,10) as version from dba_registry where comp_id = 'CONTEXT'; select * from ctxsys.ctx_version; select substr(ctxsys.dri_version,1,10) VER_CODE from dual; select count(*) from dba_objects where owner='CTXSYS'; -- Get a summary count select object_type, count(*) from dba_objects where owner='CTXSYS' group by object_type; -- Any invalid objects select object_name, object_type, status from dba_objects where owner='CTXSYS' and status != 'VALID' order by object_name; spool off ------------------------------------------------------------------------------- OLAP DeInstallation : set echo on set ti on set timing on set verify on spool text_deinstall.log @?/ctx/admin/catnoctx.sql drop procedure sys.validate_context; spool off exit *************************************** Check System Statistics **************************************** select PNAME,PVAL1,PVAL2 from aux_stats$ where sname='SYSSTATS_MAIN'; *************************************** dbms_Stats package default value ******************************* SELECT sname AS parameter, nvl(spare4,sval1) AS default_value FROM sys.optstat_hist_control$ WHERE sname IN ('CASCADE','ESTIMATE_PERCENT','DEGREE', 'METHOD_OPT','NO_INVALIDATE','GRANULARITY'); ****************************************LOB Details******************************************* set pagesize 10000 col tablespace_name format a10 col TS_Name format a10 col Col format a10 col segment_type format a12 select a.tablespace_name TS_Name, b.table_name, b.column_name Col, a.segment_type, sum(a.bytes)/1024/1024 Bytes_MB from dba_segments a, DBA_lobs b where a.segment_name = b.segment_name and b.owner='PIHIST' group by a.tablespace_name, b.table_name, b.column_name, a.segment_type; --generate sql to move LOBs to another tablespace. See genLobMove.sql set linesize 140 select distinct owner,count(1) from DBA_OBJECTS where object_type = 'LOB' group by owner; ACCEPT tschema PROMPT 'Enter Owner of LOBS to move: ' select distinct tablespace_name from DBA_SEGMENTS order by 1; ACCEPT ttbspace_name PROMPT 'Enter Tablespace to move to: ' select 'alter table '||owner||'.'||table_name||' move lob('||column_name||') store as (tablespace '||'&ttbspace_name'||');' as "sql to move LOBS" from DBA_LOBS where owner = upper('&tschema'); exit *********************************DBMS_WORKLOAD *************************************************** EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot; ****************************** Change parameters for Oracle 10G/11G database ********************************* col SNAP_INTERVAL for a30 col RETENTION for a20 select * From dba_hist_wr_control; exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>259200); col DEFAULT_VALUE for a30 SELECT sname AS parameter, nvl(spare4,sval1) AS default_value FROM sys.optstat_hist_control$ WHERE sname IN ('CASCADE','ESTIMATE_PERCENT','DEGREE', 'METHOD_OPT','NO_INVALIDATE','GRANULARITY'); exec DBMS_STATS.SET_PARAM('METHOD_OPT','for all columns size 1'); exec DBMS_STATS.SET_PARAM('ESTIMATE_PERCENT',100); select OWNER,PROGRAM_NAME,PROGRAM_TYPE,ENABLED from dba_scheduler_programs where PROGRAM_NAME='GATHER_STATS_PROG'; Exec dbms_scheduler.disable('GATHER_STATS_PROG'); col COMMENTS for a40 Select WINDOW_GROUP_NAME,ENABLED,NUMBER_OF_WINDOWS,COMMENTS from DBA_SCHEDULER_WINDOW_GROUPS where WINDOW_GROUP_NAME in('ORA$AT_WGRP_OS','ORA$AT_WGRP_SQ'); Exec dbms_scheduler.disable('ORA$AT_WGRP_OS'); Exec dbms_scheduler.disable('ORA$AT_WGRP_SQ'); Select window_name, window_next_time, optimizer_stats From DBA_AUTOTASK_WINDOW_CLIENTS; Exec dbms_scheduler.disable('MONDAY_WINDOW'); Exec dbms_scheduler.disable('TUESDAY_WINDOW'); Exec dbms_scheduler.disable('WEDNESDAY_WINDOW'); Exec dbms_scheduler.disable('THURSDAY_WINDOW'); Exec dbms_scheduler.disable('FRIDAY_WINDOW'); Exec dbms_scheduler.disable('SATURDAY_WINDOW'); Exec dbms_scheduler.disable('SUNDAY_WINDOW'); ************************************* Create Scheduler Job ******************************************** BEGIN DBMS_SCHEDULER.create_job ( job_name => 'MVIEW_REFRESH', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN MVIEW_REFRESH_PROC; END;', start_date => SYSTIMESTAMP, repeat_interval => 'freq=MINUTELY; INTERVAL=15', end_date => NULL, enabled => TRUE, comments => 'JOB for MVIEW Refresh'); END; /*************************************** Table analyze *************************************************** select table_name from dba_tables where table_name like '%STAT%' and table_name not like 'WRH%' and table_name not like 'WRI%' and owner='SYS' ; Object Level exec DBMS_STATS.lock_table_stats('DC_OWNER', 'CUSTOMER_MASTER'); exec EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('SYS','MPIDB3D_STATS'); exec dbms_stats.create_stat_table('SYS','&stattablename'); exec dbms_stats.export_database_stats('&stattable','&statid','SYS'); exec dbms_stats.export_schema_stats('&owner','&stattablename','&statid','SYS'); exec DBMS_STATS.DELETE_TABLE_STATS('ACTUATE','ISECPD_MAT_CP_REP'); exec dbms_stats.export_table_stats(OWNNAME=>'&owner',TABNAME=>'&tablename',STATTAB=>'&stattable',STATID=>'&statid',STATOWN=>'SYS'); exec dbms_Stats.IMPORT_TABLE_STATS(ownname=>'PRIMECC',tabname=>'MTRANSACTIONS',STATTAB=>'PRIME_STATS',STATID=>'PRIME_STATS_06jan10_17',CASCADE=>true,STATOWN=>'SYS'); exec dbms_stats.gather_table_stats(OWNNAME=>'METISDB_PROD',TABNAME=>'TBL_UNIT_TRN_TRANSACTION',partname=>'BATCACTMN7',granularity=>'APPROX_GLOBAL and partition',cascade=>TRUE); Normal Tables :- select 'REM ###### Analyze of Table '||owner||'.'||table_name|| ' of size => '||mb ||'Mb'||chr(13)||chr(10)|| 'exec DBMS_STATS.GATHER_TABLE_STATS('''||owner||''','''||table_name||''',DEGREE=>40,estimate_percent=>20,CASCADE=>TRUE);' from ( select a.owner owner,a.table_name table_name,b.tablespace_name tablespace_name,b.bytes/1024/1024 as mb from dba_tables a, dba_segments b where a.owner=b.owner and a.table_name=b.segment_name and segment_type='TABLE' and a.owner='PWYADM' order by bytes/1024/1024 desc) / Partitioned Table: select 'REM ###### Analyze of Table '||owner||'.'||segment_name||' of size '||MB||' MB'||chr(13)||chr(10)|| 'exec DBMS_STATS.GATHER_TABLE_STATS('||''''||owner||''''||','||''''||segment_name||''''||', DEGREE=>2, estimate_percent=>100,CASCADE=>TRUE,granularity =>'||'''ALL'''||') ;' from ( select a.owner owner,a.segment_name segment_name,a.segment_type segment_type,sum(a.bytes/1024/1024) as mb from dba_segments a, dba_tables b where a.owner = b.owner and a.segment_name = b.table_name and a.owner='DBOPFSRECO' and a.segment_type = 'TABLE PARTITION' and a.segment_name in ('TRAN_ANNEXURE_DATA_CL','TRAN_ANNEXURE_DATA_HL') group by a.owner,a.segment_name,a.segment_type order by mb desc) / spool pwyadm_analyze_180309.log set echo on set time on set timing on alter session set db_file_multiblock_read_count=128; alter session set resumable_timeout=3600; alter session set "_px_max_granules_per_slave"=3000; alter session set "_px_index_sampling"=3000; alter session set "_px_dynamic_sample_size"=3000; exec dbms_stats.gather_table_stats('CARDB','CARTBL_ISC_MULTI_ACCOUNTS',estimate_percent=>60,CASCADE=>TRUE,degree=>30); exec dbms_stats.gather_table_stats('DMS','GNUDYN_CHEQUE_BOOK',estimate_percent=>20,CASCADE=>TRUE,degree=>4); spool off exit Schema Level exec dbms_stats.export_schema_stats('SYS','&stattable','&statid','SYS'); exec dbms_stats.gather_schema_stats(OWNNAME=>'SYSADM',ESTIMATE_PERCENT=>20,DEGREE=>4,CASCADE=>true); **************************************** Index Rebuild ********************************************************* set echo off; set feed off; set head off; set lines 200 set pages 500 set trimspool on; spool PROFUNDS_index_rebuild.sql select 'spool PROFUNDS_index_rebuild.log' from dual; set serveroutput on size 1000000; exec dbms_output.enable(500000000); declare TYPE mycursor IS REF CURSOR; c2 mycursor; tname varchar2(50); towner varchar2(50); tsize number; cursor c1 is select owner,segment_name,bytes from dba_segments where segment_type='INDEX' and owner='PROFUNDS' order by bytes; begin dbms_output.put_line('set time on'); dbms_output.put_line('set timing on'); dbms_output.put_line('set echo on'); dbms_output.put_line('set lines 200'); dbms_output.put_line('alter session set db_file_multiblock_read_count=128;'); dbms_output.put_line('alter session set resumable_timeout=3600;'); dbms_output.put_line('alter session set "_px_max_granules_per_slave"=3000;'); dbms_output.put_line('alter session set "_px_index_sampling"=3000;'); dbms_output.put_line('alter session set "_px_dynamic_sample_size"=3000;'); for i in c1 loop towner := i.owner; tname := i.segment_name; tsize := i.bytes; dbms_output.put_line(chr(5)); dbms_output.put_line(' -- Rebuilding Index ' ||i.owner||'.'||i.segment_name||' of '||i.bytes/1024/1024|| ' MB.'); dbms_output.put_line('alter index ' ||i.owner ||'.'||i.segment_name||' rebuild parallel(degree 24);'); dbms_output.put_line('alter index '||i.owner||'.'||i.segment_name||' noparallel;'); end loop; end; / select 'spool off' from dual; select 'exit' from dual; spool off exit ********************************************* Table Analyze ****************************************************** set echo off; set feed off; set head off; set lines 200 set pages 5000 set trimspool on; spool Esearch_table_analyze.sql select 'spool Esearch_table_analyze.log' from dual; set serveroutput on; declare TYPE mycursor IS REF CURSOR; c2 mycursor; tname varchar2(50); towner varchar2(50); tsize number; cursor c1 is select owner,segment_name,bytes from dba_segments where segment_type='TABLE' and owner in ('SOX') and segment_name not like '%BIN%' order by bytes; begin dbms_output.put_line('set time on'); dbms_output.put_line('set timing on'); dbms_output.put_line('set echo on'); dbms_output.put_line('set lines 200'); dbms_output.put_line('alter session set sort_area_size=100000000;'); dbms_output.put_line('alter session set db_file_multiblock_read_count=64;'); dbms_output.put_line('alter session enable resumable timeout 3600;'); for i in c1 loop towner := i.owner; tname := i.segment_name; tsize := i.bytes; dbms_output.put_line('REM ###### Analyze of Table ' ||i.owner||'.'||i.segment_name||' of '||i.bytes/1024/1024|| ' MB.'); dbms_output.put_line('exec dbms_stats.gather_table_stats('''||i.owner||''','''||i.segment_name||''', estimate_percent=>100,CASCADE=>TRUE,degree=>8);'); dbms_output.put_line(' '); end loop; end; / select 'spool off' from dual; select 'exit' from dual; spool off select ('exec dbms_stats.gather_table_stats('''||owner||''','''||segment_name||''', estimate_percent=>40,CASCADE=>TRUE,degree=>8,method_opt=>''FOR ALL COLUMNS SIZE REPEAT'');') from dba_segments where owner='SYMBOLS' ********************************** Table analyze with Multiple Columns Histograms ******************************* CREATE OR REPLACE PROCEDURE R1 as -- VARIABLE DECARATION str_exec_variable varchar2(32600); l_file UTL_FILE.FILE_TYPE; l_location VARCHAR2(100):= 'DIR_ANN'; l_filename VARCHAR2(100); -- CURSOR DECLARATION cursor c1 is select distinct table_name from dba_tab_columns where histogram != 'NONE' and owner = 'MXG'; cursor c2(tname varchar2) is select table_name, column_name, num_buckets, histogram from dba_tab_columns where table_name = tname and histogram != 'NONE' and owner = 'MXG'; begin l_filename := 'MXG_HISTOGRAM_TABLES.sql'; l_file := utl_file.fopen(l_location,l_filename,'w',32600); utl_file.new_line(l_file ); UTL_FILE.PUT_LINE(l_file,'set echo on'); UTL_FILE.PUT_LINE(l_file,'set time on'); UTL_FILE.PUT_LINE(l_file,'set timing on'); utl_file.new_line(l_file); UTL_FILE.PUT_LINE(l_file,'SPOOL MXG_HISTOGRAM_TABLES.log'); utl_file.new_line(l_file); UTL_FILE.PUT_LINE(l_file,'alter session set db_file_multiblock_read_count=128;'); UTL_FILE.PUT_LINE(l_file,'alter session set resumable_timeout=3600;'); UTL_FILE.PUT_LINE(l_file,'alter session set "_px_max_granules_per_slave"=3000;'); UTL_FILE.PUT_LINE(l_file,'alter session set "_px_index_sampling"=3000;'); UTL_FILE.PUT_LINE(l_file,'alter session set "_px_dynamic_sample_size"=3000;'); utl_file.new_line(l_file); for i in c1 loop select 'EXEC DBMS_STATS.GATHER_TABLE_STATS(' || '''' || 'MXG' || '''' || ',' || '''' || i.table_name || '''' || ',' || 'DEGREE => 28, ESTIMATE_PERCENT=> 100, CASCADE=> TRUE, METHOD_OPT=> ' || '''' into str_exec_variable FROM SYS.DUAL; for j in c2(i.table_name) loop str_exec_variable := str_exec_variable|| 'FOR COLUMNS size ' || j.NUM_BUCKETS || ' ' || j.column_name || ' '; end loop; str_exec_variable := str_exec_variable || '''' || ');'; UTL_FILE.PUT_LINE(l_file,'REM --- ANALYZE OF TABLE WITH HISTOGRAM ------> '||i.table_name); UTL_FILE.PUT_LINE(l_file,str_exec_variable); UTL_FILE.FFLUSH(l_file); utl_file.new_line(l_file ); --dbms_output.put_line('String is : ' || str_exec_variable); str_exec_variable := ''; end loop; UTL_FILE.PUT_LINE(l_file,'SPOOL OFF'); UTL_FILE.PUT_LINE(l_file,'EXIT'); utl_file.fclose(l_file); end; / *************************************************Histogram ***************************************************** select owner, table_name, column_name, num_buckets, num_distinct, histogram from dba_tab_columns where table_name in ( 'TRN_HDR_DBF', 'TABLE#DATA#DEALCURR_DBF', 'TRN_HDRO_DBF', 'FD111200_DBF', 'TRN_HDRF_DBF', 'SI_GRID_DBF', 'FXNG_DBF', 'FD121200_DBF', 'TRN_SECL_DBF', 'MKT_OP_DBF') and histogram<>'NONE' order by table_name; ************************************************** Table reorganisation ***************************************** SET SERVEROUTPUT ON format WORD_WRAPPED exec dbms_output.enable(100000000); break on segment_name; declare tname varchar2(50); towner varchar2(50); cursor c1 is select owner,segment_name,tablespace_name,bytes/1024/1024 bytes from dba_segments where segment_type='TABLE' and owner='KIT' and segment_name not like 'BIN$%' order by bytes/1024/1024 desc; cursor c2 is select owner,segment_name as index_name,tablespace_name,bytes/1024/1024 as mb from dba_segments where segment_name in (select index_name from dba_indexes where table_name=tname and owner=towner) and owner=towner; begin dbms_output.put_line('set time on'); dbms_output.put_line('set timing on'); dbms_output.put_line('set echo on'); dbms_output.put_line('set verify on'); dbms_output.put_line(chr(5)); dbms_output.put_line('alter session set db_file_multiblock_read_count=128;'); dbms_output.put_line('alter session set resumable_timeout=3600;'); dbms_output.put_line('alter session set "_px_max_granules_per_slave"=3000;'); dbms_output.put_line('alter session set "_px_index_sampling"=3000;'); dbms_output.put_line('alter session set "_px_dynamic_sample_size"=3000;'); dbms_output.put_line(chr(5)); dbms_output.put_line('spool Esearch_kit_rebuild.log'); dbms_output.put_line(chr(5)); for i in c1 loop towner := i.owner; tname := i.segment_name; dbms_output.put_line('REM --- MOVING TABLE '||i.segment_name||' OF SIZE ----> '||i.bytes||' MB ') ; dbms_output.put_line('alter table '||i.owner||'.'||i.segment_name||' move tablespace '||i.tablespace_name||';'); for j in c2 loop dbms_output.put_line(null); dbms_output.put_line(''); dbms_output.put_line(chr(5)); dbms_output.put_line('REM --- REBUILD INDEX '||tname||'.'||j.index_name||' OF SIZE ----> '||j.mb||' MB ') ; dbms_output.put_line('alter index '||j.owner||'.'||j.index_name||' rebuild parallel(degree 8);'); dbms_output.put_line('alter index '||j.owner||'.'||j.index_name||' noparallel;'); end loop; dbms_output.put_line(''); dbms_output.put_line(chr(3)); dbms_output.put_line('REM *************************************************************************'); dbms_output.put_line(chr(3)); end loop; dbms_output.put_line('spool off'); dbms_output.put_line('exit'); end; / ****************************************** Analyze Multiple Schemas ********************************************* CREATE OR REPLACE PROCEDURE P2 as -- VARIABLE DECARATION L_COUNT NUMBER; TOWNER VARCHAR2(30); TFILE VARCHAR2(30); l_file UTL_FILE.FILE_TYPE; l_location VARCHAR2(100) := 'DIR_ANN'; l_filename VARCHAR2(100) ; -- CURSOR DECLARATION CURSOR C1 IS SELECT USERNAME FROM DBA_USERS WHERE USERNAME IN ('FINNONE_LAM','FINNBANKCPS','FINNONESEC','FINNONEEOD', 'FINNONEFA','FINNHFCFA','FINNHFCCPS','FINNONECPS','FINNLOGIN', 'FINNONELEA','FINNBANKPDC','FINNONEEAI','FINNHFCSEC', 'FINNBANKLEA','FINNHFCEOD','FINNONEPDC1','FINNONEGCD', 'FINNHFCLEA','FINNBANKFA','FINNHFCPDC','FINNBANKEOD', 'FINNBANKSEC','FINNONEPDCBAK'); CURSOR C2 IS SELECT A.OWNER,A.SEGMENT_NAME,A.SEGMENT_TYPE,SUM(A.BYTES/1024/1024) MB FROM DBA_SEGMENTS A, DBA_TABLES B WHERE A.OWNER = B.OWNER AND A.SEGMENT_NAME = B.TABLE_NAME AND A.OWNER = TOWNER AND A.SEGMENT_TYPE = 'TABLE' GROUP BY A.OWNER,A.SEGMENT_NAME,A.SEGMENT_TYPE ORDER BY MB DESC; CURSOR C3 IS SELECT A.OWNER,A.SEGMENT_NAME,A.SEGMENT_TYPE,SUM(A.BYTES/1024/1024) MB FROM DBA_SEGMENTS A, DBA_TABLES B WHERE A.OWNER = B.OWNER AND A.SEGMENT_NAME = B.TABLE_NAME AND A.OWNER = TOWNER AND A.SEGMENT_TYPE = 'TABLE PARTITION' GROUP BY A.OWNER,A.SEGMENT_NAME,A.SEGMENT_TYPE ORDER BY MB DESC; BEGIN FOR i IN C1 LOOP TOWNER := i.USERNAME; BEGIN SELECT COUNT(1) INTO L_COUNT FROM DBA_SEGMENTS WHERE OWNER=TOWNER AND SEGMENT_TYPE='TABLE'; IF L_COUNT > 0 THEN l_filename := TOWNER||'_NONPART_TABLES.sql'; l_file := utl_file.fopen(l_location,l_filename,'w'); utl_file.new_line(l_file ); UTL_FILE.PUT_LINE(l_file,'set echo on'); UTL_FILE.PUT_LINE(l_file,'set time on'); UTL_FILE.PUT_LINE(l_file,'set timing on'); utl_file.new_line(l_file); UTL_FILE.PUT_LINE(l_file,'SPOOL TOWNER||'_NONPART_TABLES.log'); utl_file.new_line(l_file); UTL_FILE.PUT_LINE(l_file,'alter session set db_file_multiblock_read_count=128;'); UTL_FILE.PUT_LINE(l_file,'alter session set resumable_timeout=3600;'); UTL_FILE.PUT_LINE(l_file,'alter session set "_px_max_granules_per_slave"=3000;'); UTL_FILE.PUT_LINE(l_file,'alter session set "_px_index_sampling"=3000;'); UTL_FILE.PUT_LINE(l_file,'alter session set "_px_dynamic_sample_size"=3000;'); utl_file.new_line(l_file); FOR j IN C2 LOOP UTL_FILE.PUT_LINE(l_file,'REM --- ANALYZE OF TABLE ------> '||j.segment_name||' OF SIZE '||j.MB||' MB' ); UTL_FILE.PUT_LINE(l_file,'exec DBMS_STATS.GATHER_TABLE_STATS('||''''||j.owner||''''||','||''''||j.segment_name||''''||', DEGREE=>40, estimate_percent=>20,CASCADE=>TRUE);'); utl_file.new_line(l_file ); END LOOP; UTL_FILE.PUT_LINE(l_file,'SPOOL OFF'); UTL_FILE.PUT_LINE(l_file,'EXIT'); utl_file.fclose(l_file); END IF; END; BEGIN SELECT COUNT(1) INTO L_COUNT FROM DBA_SEGMENTS WHERE OWNER=TOWNER AND SEGMENT_TYPE='TABLE PARTITION'; IF L_COUNT > 0 THEN l_filename := TOWNER||'_PART_TABLES.sql'; l_file := utl_file.fopen(l_location,l_filename,'w'); utl_file.new_line(l_file ); UTL_FILE.PUT_LINE(l_file,'set echo on'); UTL_FILE.PUT_LINE(l_file,'set time on'); UTL_FILE.PUT_LINE(l_file,'set timing on'); utl_file.new_line(l_file); UTL_FILE.PUT_LINE(l_file,'SPOOL TOWNER||'_PART_TABLES.log'); utl_file.new_line(l_file); UTL_FILE.PUT_LINE(l_file,'alter session set db_file_multiblock_read_count=128;'); UTL_FILE.PUT_LINE(l_file,'alter session set resumable_timeout=3600;'); UTL_FILE.PUT_LINE(l_file,'alter session set "_px_max_granules_per_slave"=3000;'); UTL_FILE.PUT_LINE(l_file,'alter session set "_px_index_sampling"=3000;'); UTL_FILE.PUT_LINE(l_file,'alter session set "_px_dynamic_sample_size"=3000;'); utl_file.new_line(l_file); FOR k IN C3 LOOP UTL_FILE.PUT_LINE(l_file,'REM --- ANALYZE OF TABLE ------> '||k.segment_name||' OF SIZE '||k.MB||' MB' ); UTL_FILE.PUT_LINE(l_file,'exec DBMS_STATS.GATHER_TABLE_STATS('||''''||k.owner||''''||','||''''||k.segment_name||''''||', DEGREE=>40, estimate_percent=>20,CASCADE=>TRUE, granularity =>'||'''ALL'''||') ;'); utl_file.new_line(l_file ); END LOOP; UTL_FILE.PUT_LINE(l_file,'SPOOL OFF'); UTL_FILE.PUT_LINE(l_file,'EXIT'); utl_file.fclose(l_file); END IF; END; END LOOP; END; /****************************************** Analyze Multiple Schemas without Partition *************************** CREATE OR REPLACE PROCEDURE D2 AS -- VARIABLE DECARATION L_COUNT NUMBER; TOWNER VARCHAR2(30); TFILE VARCHAR2(30); l_file UTL_FILE.FILE_TYPE; l_location VARCHAR2(100) := 'DIR_ANN'; l_filename VARCHAR2(100) ; -- CURSOR DECLARATION CURSOR C1 IS SELECT USERNAME FROM DBA_USERS WHERE USERNAME IN ('SYMBOLS','APPRO','CONV'); CURSOR C2 IS SELECT A.OWNER,A.SEGMENT_NAME,A.SEGMENT_TYPE,SUM(A.BYTES/1024/1024) MB FROM DBA_SEGMENTS A, DBA_TABLES B WHERE A.OWNER = B.OWNER AND A.SEGMENT_NAME = B.TABLE_NAME AND A.OWNER = TOWNER AND A.SEGMENT_TYPE = 'TABLE' GROUP BY A.OWNER,A.SEGMENT_NAME,A.SEGMENT_TYPE ORDER BY MB DESC; BEGIN FOR i IN C1 LOOP TOWNER := i.USERNAME; BEGIN SELECT COUNT(1) INTO L_COUNT FROM DBA_SEGMENTS WHERE OWNER=TOWNER AND SEGMENT_TYPE='TABLE'; IF L_COUNT > 0 THEN l_filename := TOWNER||'_TABLES.sql'; l_file := utl_file.fopen(l_location,l_filename,'w'); utl_file.new_line(l_file ); UTL_FILE.PUT_LINE(l_file,'set echo on'); UTL_FILE.PUT_LINE(l_file,'set time on'); UTL_FILE.PUT_LINE(l_file,'set timing on'); utl_file.new_line(l_file ); UTL_FILE.PUT_LINE(l_file,'SPOOL '||TOWNER||'_TABLES.log'); utl_file.new_line(l_file ); UTL_FILE.PUT_LINE(l_file,'alter session set workarea_size_policy=manual;'); UTL_FILE.PUT_LINE(l_file,'alter session set sort_area_size=100000000;'); UTL_FILE.PUT_LINE(l_file,'alter session enable resumable timeout 3600;'); UTL_FILE.PUT_LINE(l_file,'alter session set db_file_multiblock_read_count=128;'); utl_file.new_line(l_file ); FOR j IN C2 LOOP UTL_FILE.PUT_LINE(l_file,'REM --- ANALYZE OF TABLE ------> '||j.segment_name||' OF SIZE '||j.MB||' MB' ); UTL_FILE.PUT_LINE(l_file,'exec DBMS_STATS.GATHER_TABLE_STATS('||''''||j.owner||''''||','||''''||j.segment_name||''''||', DEGREE=>8, estimate_percent=>100,CASCADE=>TRUE);'); utl_file.new_line(l_file ); END LOOP; UTL_FILE.PUT_LINE(l_file,'SPOOL OFF'); UTL_FILE.PUT_LINE(l_file,'EXIT'); utl_file.fclose(l_file); END IF; END; END LOOP; END; / ********************************************* Analyze Multiple Schemas with Partition ******************************** CREATE OR REPLACE PROCEDURE D2 AS -- VARIABLE DECARATION L_COUNT NUMBER; TOWNER VARCHAR2(30); TFILE VARCHAR2(30); l_file UTL_FILE.FILE_TYPE; l_location VARCHAR2(100) := 'DIR_ANN'; l_filename VARCHAR2(100) ; -- CURSOR DECLARATION CURSOR C1 IS SELECT USERNAME FROM DBA_USERS WHERE USERNAME='PROFUNDS'; CURSOR C3 IS SELECT A.OWNER,A.SEGMENT_NAME,A.SEGMENT_TYPE,SUM(A.BYTES/1024/1024) MB FROM DBA_SEGMENTS A, DBA_TABLES B WHERE A.OWNER = B.OWNER AND A.SEGMENT_NAME = B.TABLE_NAME AND A.OWNER = TOWNER AND A.SEGMENT_TYPE = 'TABLE PARTITION' GROUP BY A.OWNER,A.SEGMENT_NAME,A.SEGMENT_TYPE ORDER BY MB; BEGIN FOR i IN C1 LOOP TOWNER := i.USERNAME; BEGIN SELECT COUNT(1) INTO L_COUNT FROM DBA_SEGMENTS WHERE OWNER=TOWNER AND SEGMENT_TYPE='TABLE PARTITION'; IF L_COUNT > 0 THEN l_filename := TOWNER||'_PART_TABLES.sql'; l_file := utl_file.fopen(l_location,l_filename,'w'); utl_file.new_line(l_file ); UTL_FILE.PUT_LINE(l_file,'set echo on'); UTL_FILE.PUT_LINE(l_file,'set time on'); UTL_FILE.PUT_LINE(l_file,'set timing on'); utl_file.new_line(l_file ); UTL_FILE.PUT_LINE(l_file,'SPOOL '||TOWNER||'_PART_TABLES.log'); utl_file.new_line(l_file ); UTL_FILE.PUT_LINE(l_file,'alter session set resumable_timeout=3600;'); UTL_FILE.PUT_LINE(l_file,'alter session set db_file_multiblock_read_count=128;'); utl_file.new_line(l_file ); FOR k IN C3 LOOP UTL_FILE.PUT_LINE(l_file,'REM --- ANALYZE OF TABLE ------> '||k.segment_name||' OF SIZE '||k.MB||' MB' ); UTL_FILE.PUT_LINE(l_file,'exec DBMS_STATS.GATHER_TABLE_STATS('||''''||k.owner||''''||','||''''||k.segment_name||''''||', DEGREE=>12, estimate_percent=>100,CASCADE=>TRUE,granularity =>'||'''ALL'''||') ;'); utl_file.new_line(l_file ); END LOOP; UTL_FILE.PUT_LINE(l_file,'SPOOL OFF'); -- UTL_FILE.PUT_LINE(l_file,'EXIT'); utl_file.fclose(l_file); END IF; END; END LOOP; END; / **************************************************** Copy Table Statistics ************************************** CREATE OR REPLACE PROCEDURE SETSTAT(old varchar2, new varchar2) AS l_tab_owner VARCHAR2(50); l_tab_name VARCHAR2(50); l_src_part VARCHAR2(50); l_targ_part VARCHAR2(50); l_col_type VARCHAR2(50); l_srec sys.dbms_stats.statrec; l_distcnt number; l_density number; l_nullcnt number; l_avgclen number; l_statown varchar2(80); out varchar2(32767); date_out varchar2(2000); minvv DATE; maxvv DATE; dt DATE; maxrv raw(32) := l_srec.maxval; minrv raw(32) := l_srec.minval; srec dbms_stats.statrec; datevals dbms_stats.datearray; -- CURSOR DECLARATION CURSOR C1 IS select a.table_owner,a.table_name, a.partition_name, b.column_name from dba_tab_partitions a, dba_part_key_columns b where a.table_name=b.name and a.table_owner='CLICKDB' and regexp_like(a.partition_name, '*_'||old) order by 1,3; CURSOR C2 IS select a.table_owner,a.table_name, a.partition_name, b.column_name from dba_tab_partitions a, dba_part_key_columns b where a.table_name=b.name and a.table_owner='CLICKDB' and regexp_like(a.partition_name, '*_'||new) order by 1,3; begin for i in C1 loop for j in C2 loop l_tab_owner := i.table_owner; l_tab_name := i.table_name; l_src_part := i.partition_name; l_targ_part := j.partition_name; l_col_type := i.column_name; dbms_output.put_line('You have selected following options'); dbms_output.put_line('Table owner: '||l_tab_owner); dbms_output.put_line('Table name: '||l_tab_name); dbms_output.put_line('Source Partition: '||l_src_part); dbms_output.put_line('Destination Partition: '||l_targ_part); dbms_output.put_line('Column name for range partititioned table(date datatype): '||l_col_type); select HIGH_VALUE into out from dba_tab_partitions where table_name=l_tab_name and table_owner=l_tab_owner and partition_name=l_src_part; select to_char(to_date(substr(out,10,11),'yyyy-mm-dd'),'dd-mm-yyyy') into date_out from dual; dbms_output.put_line('Target partition for stats date: '||date_out); dbms_stats.copy_table_stats(l_tab_owner,l_tab_name,l_src_part,l_targ_part); dbms_stats.get_column_stats(l_tab_owner,l_tab_name,l_col_type,l_targ_part,distcnt=>l_distcnt,density=>l_density,nullcnt=>l_nullcnt,srec=>l_srec,avgclen=>l_avgclen,statown=>l_statown); dbms_output.put_line('No of Distinct Values: '||l_distcnt); dbms_output.put_line('Density : '||l_density); dbms_output.put_line('NullCount : '||l_nullcnt); dbms_output.put_line('Average Row Length : '||l_avgclen); dbms_output.put_line('Stat Owner : '||l_statown); maxrv := l_srec.maxval; minrv := l_srec.minval; dbms_stats.convert_raw_value(minrv,minvv) ; dbms_stats.convert_raw_value(maxrv,maxvv) ; dbms_output.put_line('PRE MAX VALUE: '||TO_CHAR(maxvv,'DD-MM-RRRR HH24:MI:SS')); dbms_output.put_line('PRE MIN VALUE: '||TO_CHAR(minvv,'DD-MM-RRRR HH24:MI:SS')); execute immediate 'alter session set nls_date_format=''DD-MM-YYYY'''; srec.EPC:=2; srec.EAVS:=NULL; dt := date_out; dbms_output.put_line('Target partition for stats date: '||to_char(dt,'DD-MM-YYYY HH24:MI:SS')); datevals:= SYS.DBMS_STATS.DATEARRAY(dt-28,dt-1); dbms_stats.prepare_column_values(srec,datevals); dbms_stats.set_column_stats(l_tab_owner,l_tab_name,l_col_type,l_targ_part,srec=>srec,NO_INVALIDATE =>false); commit; dbms_stats.get_column_stats(l_tab_owner,l_tab_name,l_col_type,l_targ_part,distcnt=>l_distcnt,density=>l_density,nullcnt=>l_nullcnt,srec=>l_srec,avgclen=>l_avgclen,statown=>l_statown); maxrv := l_srec.maxval; minrv := l_srec.minval; dbms_stats.convert_raw_value(minrv,minvv) ; dbms_stats.convert_raw_value(maxrv,maxvv) ; dbms_output.put_line('POST MAX VALUE: '||TO_CHAR(maxvv,'DD-MM-RRRR HH24:MI:SS')); dbms_output.put_line('POST MIN VALUE: '||TO_CHAR(minvv,'DD-MM-RRRR HH24:MI:SS')); end loop; end loop; end; / exec setstat('10_10','11_10'); set lines 150 set pages 500 col high_value for a30 col TABLE_NAME for a20 col PARTITION_NAME for a25 select TABLE_NAME, PARTITION_NAME, HIGH_VALUE, NUM_ROWS, BLOCKS, AVG_SPACE, AVG_ROW_LEN, LAST_ANALYZED from dba_tab_partitions where table_owner='PROFUNDS' and regexp_like(partition_name, '*_1[0-1]_11') order by 1,2; set lines 150 set pages 500 col high_value for a30 col TABLE_NAME for a20 col PARTITION_NAME for a25 select TABLE_NAME, PARTITION_NAME, HIGH_VALUE, NUM_ROWS, BLOCKS, AVG_SPACE, AVG_ROW_LEN, LAST_ANALYZED from dba_tab_partitions where table_owner='PROFUNDS' and partition_name like '%1_11%' order by 1,2; and regexp_like(partition_name, '*_1[0-1]_10') order by 1,2; ******************************************* Change column Stats high value **************************************** DECLARE -- Procedure to change high_value of a date column with no histograms. -- Uses table cvj_hv_columns to determine columns to modify -- Assumes table cvj_hv_columns has 3 varchar2 columns of name owner, table_name, colname and 1 number column named days. -- The Days column is how many days in the future you wish to maintain the max value for the date column. -- Example: insert into cvj_hv_columns values('MYSCHEMA','MYTABLE','MYCOLUMN', 90); srec dbms_stats.statrec; m_distcnt number; m_density number; m_nullcnt number; m_avgclen number; days number; DATEVALS DBMS_STATS.DATEARRAY; --NUMVALS DBMS_STATS.NUMARRAY; minval raw(32); prev_high_raw raw(32); minval_date date; prev_high_value date; BEGIN for c_rec in (select owner,table_name ,colname, days from cvj_hv_columns) loop dbms_stats.get_column_stats( ownname => c_rec.owner, tabname => c_rec.table_name, colname => c_rec.colname, distcnt => m_distcnt, density => m_density, nullcnt => m_nullcnt, srec => srec, avgclen => m_avgclen); -- Looks for min_value and max_value begin select low_value ,high_value into minval,prev_high_raw from dba_tab_columns d where table_name = upper(c_rec.table_name) and owner=upper(c_rec.owner) and column_name like upper(c_rec.colname); end; -- Converts raw values dbms_stats.convert_raw_value(minval, minval_date); dbms_stats.convert_raw_value(prev_high_raw, prev_high_value); DBMS_OUTPUT.PUT_LINE ( c_rec.owner||'.'||c_rec.table_name||'.'||c_rec.colname||' minval_date= ' ||minval_date ||' maxval_date = ' ||prev_high_value ); IF prev_high_value < trunc(sysdate+c_rec.days) then -- Only executes if hv is less than sysdate+c_rec.days -- Sets sysdate+c_rec.days as HV DATEVALS := DBMS_STATS.DATEARRAY(minval_date,trunc(sysdate+c_rec.days)); -- NUMVALS := DBMS_STATS.NUMARRAY(minval,maxval); DBMS_OUTPUT.PUT_LINE ( 'Changes the max_value of '||c_rec.owner||'.'||c_rec.table_name||'.'||c_rec.colname||' to ' || novals(2) ); dbms_stats.prepare_column_values(srec, novals); dbms_stats.set_column_stats( ownname => c_rec.owner, tabname => c_rec.table_name, colname => c_rec.colname, distcnt => m_distcnt, density => m_density, nullcnt => m_nullcnt, srec => srec, avgclen => m_avgclen); ELSE DBMS_OUTPUT.PUT_LINE ( 'No changes for '||c_rec.owner||'.'||c_rec.table_name||'.'||c_rec.colname ); END IF; END LOOP; COMMIT; END; / ******************************************* Getting Output in HTML format *************************************** set pages 5000 SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON - HEAD "<TITLE>Department Report</TITLE> - <STYLE type='text/css'> - <!-- BODY {background: #FFFFC6} --> - </STYLE>" - BODY "TEXT='#FF00Ff'" - TABLE "WIDTH='90%' BORDER='5'" spool report.html Select * from dba_tables; spool off exit ************************************************ LOB INSERT: ***************************************************** CREATE OR REPLACE PROCEDURE "CRMUSER"."INCI_ATTACHMOVE" AS V_IncidentId INTEGER; CURSOR inciattach_cursor IS SELECT incidentid FROM INCIDMP_OPEN where processed='N'; BEGIN OPEN inciattach_cursor; LOOP BEGIN FETCH inciattach_cursor INTO V_IncidentId ; EXIT WHEN inciattach_cursor%NOTFOUND; insert into Attachments (select * from attachments_old where attachmentid in ( select attachmentid from incident_attachment where incidentid=V_Incidentid)); UPDATE INCIDMP_OPEN SET Processed = 'Y' WHERE incidentid=V_Incidentid; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('Inside Exception'||SQLERRM); UPDATE INCIDMP_OPEN SET Processed = 'E' WHERE incidentid=V_Incidentid; COMMIT; END; END LOOP; CLOSE inciattach_cursor; END; *********************************************** BULK INSERT: ***************************************************** DECLARE TYPE prod_tab IS TABLE OF products%ROWTYPE; products_tab prod_tab := prod_tab(); start_time number; end_time number; BEGIN -- Populate a collection - 100000 rows SELECT * BULK COLLECT INTO products_tab FROM products; EXECUTE IMMEDIATE 'TRUNCATE TABLE products'; Start_time := DBMS_UTILITY.get_time; FORALL i in products_tab.first .. products_tab.last INSERT INTO products VALUES products_tab(i); end_time := DBMS_UTILITY.get_time; DBMS_OUTPUT.PUT_LINE(ëBulk Insert: Ã||to_char(end_time-start_time)); COMMIT; END; *********************************************** BULK INSERT: ***************************************************** DECLARE TYPE faaccount_prod_tab IS TABLE OF IAXISADM.STG_FAACCOUNTMOVEMENT%ROWTYPE; faaccount_product_tab faaccount_prod_tab := faaccount_prod_tab(); start_time number; end_time number; BEGIN -- Populate a collection - 100000 rows SELECT /*+ full(a) parallel(a,10) */ * BULK COLLECT INTO faaccount_product_tab limit 100000 FROM IAXISADM.STG_FAACCOUNTMOVEMENT where DTL__CAPXACTION = 'I'; Start_time := DBMS_UTILITY.get_time; FORALL i in faaccount_product_tab.first .. faaccount_product_tab.last INSERT /*+ append */ INTO LF$APP_FAACCOUNTMOVEMENT VALUES faaccount_product_tab(i); end_time := DBMS_UTILITY.get_time; DBMS_OUTPUT.PUT_LINE('Bulk Insert: '||to_char(end_time-start_time)); COMMIT; END; / Exchange Partition : ALTER TABLE table_name EXCHANGE PARTITION Partition_name WITH TABLE new_tablename WITHOUT VALIDATION; ******************************************* Multiple AWR Report Generator ******************************************** set lines 150 set pages 500 col BEGIN_INTERVAL_TIME for a30 col END_INTERVAL_TIME for a30 select SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME from dba_hist_snapshot where trunc(BEGIN_INTERVAL_TIME)=trunc(sysdate-&no) order by 1; CREATE OR REPLACE DIRECTORY AWR_REPORTS_DIR AS '/tmp/awrreports'; DECLARE -- Adjust before use. l_snap_start NUMBER := 44509; --Specify Initial Snap ID l_snap_end NUMBER := 44557; --Specify End Snap ID l_dir VARCHAR2(50) := 'AWR_REPORTS_DIR'; l_last_snap NUMBER := NULL; l_dbid v$database.dbid%TYPE; l_instance_number v$instance.instance_number%TYPE; l_file UTL_FILE.file_type; l_file_name VARCHAR(50); BEGIN SELECT dbid INTO l_dbid FROM v$database; SELECT instance_number INTO l_instance_number FROM v$instance; FOR cur_snap IN (SELECT snap_id FROM dba_hist_snapshot WHERE instance_number = l_instance_number AND snap_id BETWEEN l_snap_start AND l_snap_end ORDER BY snap_id) LOOP IF l_last_snap IS NOT NULL THEN l_file := UTL_FILE.fopen(l_dir, 'awr_' || l_last_snap || '_' || cur_snap.snap_id || '.html', 'w', 32767); FOR cur_rep IN (SELECT output FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number, l_last_snap, cur_snap.snap_id))) LOOP UTL_FILE.put_line(l_file, cur_rep.output); END LOOP; UTL_FILE.fclose(l_file); END IF; l_last_snap := cur_snap.snap_id; END LOOP; EXCEPTION WHEN OTHERS THEN IF UTL_FILE.is_open(l_file) THEN UTL_FILE.fclose(l_file); END IF; RAISE; END; /******************************************* Multiple ADDM Report Generator ******************************************** set lines 150 set pages 500 col BEGIN_INTERVAL_TIME for a30 col END_INTERVAL_TIME for a30 select SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME from dba_hist_snapshot where trunc(BEGIN_INTERVAL_TIME)=trunc(sysdate-&no) order by 1; create or replace directory ADDM_DIRECTORY as '/tmp/addmreports'; create or replace procedure multiple_addm(start_id number, end_id number) AS id number; name varchar2(100); descr varchar2(500); fhandle UTL_FILE.FILE_TYPE; fdir varchar2(50) := 'ADDM_DIRECTORY'; vclob CLOB; len number; amt NUMBER DEFAULT 32760; offset NUMBER DEFAULT 1; x varchar2(32760); BEGIN for i in start_id .. end_id loop if i != end_id then name := 'addm_report_' || to_char(i) || '_' ||to_char(i + 1); descr := 'ADDM run: snapshots [' || to_char(i) || ', ' ||to_char(i + 1) || ']'; dbms_advisor.create_task('ADDM', id, name, descr, null); dbms_advisor.set_task_parameter(name, 'START_SNAPSHOT', i); dbms_advisor.set_task_parameter(name, 'END_SNAPSHOT', i + 1); dbms_advisor.execute_task(name); fhandle := UTL_FILE.fopen(fdir, name || '.txt', 'wb', 32760); select dbms_advisor.get_task_report(name, 'TEXT', 'TYPICAL') into vclob from dual; select dbms_lob.getlength(dbms_advisor.get_task_report(name,'TEXT','TYPICAL')) into len from dual; WHILE (offset < len) LOOP dbms_lob.read(vclob, amt, offset, x); UTL_FILE.PUT_raw(fhandle, utl_raw.cast_to_raw(x), TRUE); UTL_FILE.fflush(fhandle); offset := offset + amt; END LOOP; UTL_FILE.fclose(fhandle); dbms_advisor.delete_task(name); offset := 1; end if; end loop; END; / exec multiple_addm(20766,20777); ********************************************** To check Semaphores used by Database ****************************** Oracle11G Database not shutting down not even aborting. Ora-7445 Occured. Below temporary solution used. export ORACLE_SID=name sysresv IPC Resources for ORACLE_SID "TEST" : Shared Memory: ID KEY 6923 0xe9c5c36c Semaphores: ID KEY 2293784 0x503689b8 Oracle Instance alive for sid "TEST" ipcs -s | grep 2293784 ipcrm -s 2293784 ipcrm -m 6923 ************************************ Get Index Creation Space Usage before creating it actually ********************** SET SERVEROUTPUT ON DECLARE v_used_bytes NUMBER(10); v_Allocated_Bytes NUMBER(10); BEGIN DBMS_SPACE.CREATE_INDEX_COST ( 'create index EBAWEB.IDX_UPD_PROCESS_FLG on EBAWEB.UPD_PRTFLO_DTL (UPD_PROCESS_FLG,UPD_DELETE_FLG)', v_used_Bytes, v_Allocated_Bytes ); DBMS_OUTPUT.PUT_LINE('Used Bytes MB: ' || round(v_used_Bytes/1024/1024)); DBMS_OUTPUT.PUT_LINE('Allocated Bytes MB: ' || round(v_Allocated_Bytes/1024/1024)); END; / ************************************ Get Table Creation Space Usage before creating it actually ********************** CREATE TABLE SAMPLE ( SNAP_DATE DATE DB_NAME VARCHAR2(20) PARAMETER VARCHAR2(60) VALUE VARCHAR2(1000) ) Here is the code snippet: SET SERVEROUTPUT ON DECLARE v_used_bytes NUMBER(10); v_Allocated_Bytes NUMBER(10); v_type sys.create_table_cost_columns; BEGIN v_Type := sys.create_table_cost_columns ( sys.create_table_cost_colinfo('DATE',NULL), sys.create_table_cost_colinfo('VARCHAR2',20), sys.create_table_cost_colinfo('VARCHAR2',60), sys.create_table_cost_colinfo('VARCHAR2',1000) ); DBMS_SPACE.CREATE_TABLE_COST('USERS',v_Type,4600000,7,v_used_Bytes,v_Allocated_Bytes); DBMS_OUTPUT.PUT_LINE(ÃUsed Bytes: ë || TO_CHAR(v_used_Bytes)); DBMS_OUTPUT.PUT_LINE(ÃAllocated Bytes: ë || TO_CHAR(v_Allocated_Bytes)); END; / ********************************************* IO Statistics For past Day ********************************************* set lines 130 col filename for a40 select to_char(begin_interval_time,'dd-mon-yyyy hh24:mi') "Time", substr(filename,1,instr(filename,'/',-1,1)) filename, sum(phyrds) reads, sum(PHYWRTS) writes, sum(READTIM)/sum(phyrds) read_ratio, sum(WRITETIM)/sum(PHYWRTS) write_ratio from dba_hist_filestatxs filestat, dba_hist_snapshot snap where filestat.SNAP_ID=snap.SNAP_ID and to_char(snap.begin_interval_time,'dd-mon-yyyy') = '18-oct-2009' group by to_char(begin_interval_time,'dd-mon-yyyy hh24:mi'),substr(filename,1,instr(filename,'/',-1,1)) order by 1,2; set lines 130 col filename for a40 select to_char(begin_interval_time,'dd-mon-yyyy hh24:mi') "Time", substr(filename,1,instr(filename,'/',-1,1)) filename, sum(phyrds) reads, sum(PHYWRTS) writes, sum(READTIM)/sum(phyrds) read_ratio, sum(WRITETIM)/sum(PHYWRTS) write_ratio from DBA_HIST_TEMPSTATXS tempstat, dba_hist_snapshot snap where tempstat.SNAP_ID=snap.SNAP_ID and snap.snap_id in (4594,4595) group by to_char(begin_interval_time,'dd-mon-yyyy hh24:mi'),substr(filename,1,instr(filename,'/',-1,1)) order by 1,2; ********************************************** IO Statistics For per disk ********************************************* set linesize 132 set pagesize 66 column name format a20 select substr(name, 1, 18) name, sum(phyrds) reads, sum(readtim) read_time, sum(readtim)/sum(phyrds) read_ratio, sum(phywrts) writes, sum(writetim) write_time, sum(writetim)/sum(phywrts) write_ratio from v$filestat a, v$datafile b where a.file#=b.file# group by substr(name, 1, 18) order by name desc / ********************************************** IO Statistics For Tablespace Level ********************************************* set lines 500 pages 5000 col tsname format a30 heading 'Tablespace'; col reads format 9,999,999,990 heading 'Reads'; col atpr format 990.0 heading 'Av|Rd(ms)' just c; col writes format 999,999,990 heading 'Writes'; col waits format 9,999,990 heading 'Buffer|Waits' col atpwt format 990.0 heading 'Av Buf|Wt(ms)' just c; col bpr format 999.0 heading 'Av|Blks/Rd' just c; col ios noprint select e.tsname, sum(e.phyrds - nvl(b.phyrds, 0)) reads, decode(sum(e.phyrds - nvl(b.phyrds, 0)), 0, 0, (sum(e.readtim - nvl(b.readtim, 0)) / sum(e.phyrds - nvl(b.phyrds, 0))) * 10) atpr, decode(sum(e.phyrds - nvl(b.phyrds, 0)), 0, to_number(NULL), sum(e.phyblkrd - nvl(b.phyblkrd, 0)) / sum(e.phyrds - nvl(b.phyrds, 0))) bpr, sum(e.phywrts - nvl(b.phywrts, 0)) writes, sum(e.wait_count - nvl(b.wait_count, 0)) waits, decode(sum(e.wait_count - nvl(b.wait_count, 0)), 0, 0, (sum(e.time - nvl(b.time, 0)) / sum(e.wait_count - nvl(b.wait_count, 0))) * 10) atpwt, sum(e.phyrds - nvl(b.phyrds, 0)) + sum(e.phywrts - nvl(b.phywrts, 0)) ios from DBA_HIST_FILESTATXS e, DBA_HIST_FILESTATXS b where b.snap_id(+) = &&bid and e.snap_id = &&eid and b.dbid(+) = e.dbid and b.instance_number(+) = e.instance_number and b.tsname(+) = e.tsname and b.filename(+) = e.filename and ((e.phyrds - nvl(b.phyrds, 0)) + (e.phywrts - nvl(b.phywrts, 0))) > 0 group by e.tsname union all select e.tsname, sum(e.phyrds - nvl(b.phyrds, 0)) reads, decode(sum(e.phyrds - nvl(b.phyrds, 0)), 0, 0, (sum(e.readtim - nvl(b.readtim, 0)) / sum(e.phyrds - nvl(b.phyrds, 0))) * 10) atpr, decode(sum(e.phyrds - nvl(b.phyrds, 0)), 0, to_number(NULL), sum(e.phyblkrd - nvl(b.phyblkrd, 0)) / sum(e.phyrds - nvl(b.phyrds, 0))) bpr, sum(e.phywrts - nvl(b.phywrts, 0)) writes, sum(e.wait_count - nvl(b.wait_count, 0)) waits, decode(sum(e.wait_count - nvl(b.wait_count, 0)), 0, 0, (sum(e.time - nvl(b.time, 0)) / sum(e.wait_count - nvl(b.wait_count, 0))) * 10) atpwt, sum(e.phyrds - nvl(b.phyrds, 0)) + sum(e.phywrts - nvl(b.phywrts, 0)) ios from DBA_HIST_TEMPSTATXS e, DBA_HIST_TEMPSTATXS b where b.snap_id(+) = &&bid and e.snap_id = &&eid and b.dbid(+) = e.dbid and b.instance_number(+) = e.instance_number and b.tsname(+) = e.tsname and b.filename(+) = e.filename and ((e.phyrds - nvl(b.phyrds, 0)) + (e.phywrts - nvl(b.phywrts, 0))) > 0 group by e.tsname order by ios desc; ************************************ Parameter-driven command file ************************************ $ORACLE_HOME/bin/rman <<EOF @backup_ts_generic.rman "/tmp" USERS EOF connect target / connect catalog rman/secretpass@rmancat run { allocate channel c1 type disk format ë&1/%UÃ; backup tablespace &2; } C:\> rman RMAN> connect target / RMAN> connect catalog rman/secretpass@rmancat RMAN> create script backup_ts_users comment ëTablespace Users Backupà { allocate channel c1 type disk format ëc:\temp\%UÃ; backup tablespace users; } RMAN> create script backup_ts_any comment ëAny Tablespace Backupà { allocate channel c1 type disk format ëc:\temp\%UÃ; backup tablespace &1; Enter value for 1: users users; } ******************************************* List RMAN Backup ******************************************* LIST BACKUP; # lists backup sets, image copies, and proxy copies LIST BACKUPSET; # lists only backup sets and proxy copies LIST COPY; # lists only disk copies LIST EXPIRED BACKUP; LIST BACKUP BY FILE; # shows backup sets, proxy copies, and image copies LIST COPY BY FILE; # shows only disk copies LIST EXPIRED BACKUP BY FILE; LIST BACKUP SUMMARY; # lists backup sets, proxy copies, and disk copies LIST EXPIRED BACKUP SUMMARY; LIST BACKUP OF DATABASE; # lists backups of all files in database LIST COPY OF DATAFILE 'ora_home/oradata/trgt/system01.dbf'; # lists copy of specified datafile LIST BACKUPSET 213; # lists specified backup set LIST DATAFILECOPY '/tmp/tools01.dbf'; # lists datafile copy LIST BACKUPSET TAG 'weekly_full_db_backup'; # specify a backup set by tag LIST COPY OF DATAFILE 'ora_home/oradata/trgt/system01.dbf' DEVICE TYPE sbt; # specify a backup or copy by device type LIST BACKUP LIKE '/tmp/%'; # specify a backup by directory or path LIST COPY OF DATAFILE 2 COMPLETED BETWEEN '10-DEC-2002' AND '17-DEC-2002'; # specify a backup or copy by a range of completion dates LIST ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE sbt; # specify logs backed up at least twice to tape LIST INCARNATION; LIST INCARNATION OF DATABASE prod3; *******************************************RMAN restore *********************************************** select OPERATION,STATUS,MBYTES_PROCESSED,START_TIME,END_TIME from v$rman_status where trunc(START_TIME)=trunc(sysdate) and OPERATION='RESTORE'; ******************************************* RMAN Restoration ******************************************* export ORACLE_SID=AMLCA copy pfile from 10.16.167.116 to target server startup nomount rman target / rcvcat rman/rman_2005@rmancat set dbid=1728600520 RMAN> RUN { ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE'; ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE'; ALLOCATE CHANNEL ch03 TYPE 'SBT_TAPE'; send 'NB_ORA_SERV=icstgbk1, NB_ORA_CLIENT=MLXBANKALERT'; restore database from tag AMLCA_full_hot_15_Jun_2009; RELEASE CHANNEL ch01; RELEASE CHANNEL ch02; RELEASE CHANNEL ch03; } ********************************* Check Corruption using Database export ******************************** exp system/manager full=y log=exp_db_chk.log file=/dev/null volsize=100g ***************************************** Drop Database in 9i ******************************************* set serveroutput on size 1000000; exec dbms_output.enable(100000000); SET SERVEROUT ON DECLARE TYPE string_arr IS TABLE OF VARCHAR2(1024); file_list string_arr; BEGIN SELECT t.file_path BULK COLLECT INTO file_list FROM (SELECT NAME file_path FROM V$DATAFILE UNION SELECT MEMBER file_path FROM V$LOGFILE UNION SELECT NAME file_path FROM v$controlfile UNION SELECT VALUE file_path FROM v$parameter WHERE NAME LIKE '%dest' UNION SELECT VALUE file_path FROM v$parameter2 WHERE NAME = 'utl_file_dir' ) t; FOR i IN file_list.FIRST .. file_list.LAST LOOP DBMS_OUTPUT.PUT_LINE('rm -rf ' || file_list(i)); END LOOP; END; / *************************************** Installation Error ******************************************** [ Unable to alloc heap of requested size, perhaps the maxdata value is too small - see README.HTML for more information. ] [ Unable to allocate an initial java heap of 157286400 bytes. ] [ **Out of memory, aborting** ] [ ] [ *** panic: JVMST016: Cannot allocate memory for initial java heap ] /tmp/OraInstall2009-07-16_12-59-10AM/jre/1.4.2/bin/java[3]: 17230 IOT/Abort trap(coredump) To Resolve use : export LDR_CNTRL=MAXDATA=0x40000000@DSA export IBM_JAVA_MMAP_JAVA_HEAP=true *************************************** Metadata set param ******************************************** DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', true); DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true); **************************************** Using dbms_rowid ********************************************* select x.*, ts.status from ( select t.*, dbms_rowid.rowid_relative_fno( rowid ) rfno, dbms_rowid.rowid_to_absolute_fno( rowid, user, ÃTà ) afno from t ) x, dba_data_files df, dba_tablespaces ts where df.relative_fno = rfno and df.file_id = afno and df.tablespace_name = ts.tablespace_name; create or replace function ts_status( p_rowid in rowid ) return varchar2 is l_status dba_tablespaces.status%type; begin select ts.status into l_status from dba_data_files df, dba_tablespaces ts where df.relative_fno = dbms_rowid.rowid_relative_fno( p_rowid ) and df.file_id = dbms_rowid.rowid_to_absolute_fno( p_rowid, ÃOPS$TKYTEÃ, ÃTà ) and df.tablespace_name = ts.tablespace_name; return l_status; exception when NO_DATA_FOUND then raise program_error; end; / select t.*, ts_status(t.rowid) ts_status from t; **************************************** Using Stored Outlines *************************************** ALTER SYSTEM SET create_stored_outlines=TRUE; ALTER SESSION SET create_stored_outlines=TRUE; CONN sys/password AS SYSDBA GRANT CREATE ANY OUTLINE TO SCOTT; GRANT EXECUTE_CATALOG_ROLE TO SCOTT; CREATE OUTLINE GOLD_OUTLINE_1 FOR CATEGORY GOLD ON select sum(gbim_inv_post_sales_tax_total) USED from GOLD.GOLD_BRANCH_INVOICE_MASTER, GOLD.SOL_MASTER a, GOLD.GOLD_BRANCH_DETAILS b, GOLD.SOL_MASTER c where a.sol_id = GBIM_INV_BRANCH_CD and a.RPC_SRNO = c.SOL_SRNO and a.RPC_SRNO = :"SYS_B_0" and a.RPC_SRNO = b.gbd_branch_region_cd AND b.gbd_branch_state_cd = :"SYS_B_1" AND a.sol_id= b.gbd_branch_cd and trunc(GBIM_INV_DATE) >= to_date(:"SYS_B_2",:"SYS_B_3") and trunc(GBIM_INV_DATE) <= to_date(:"SYS_B_4",:"SYS_B_5") and GBIM_INV_CANCELLED = :"SYS_B_6" and GBIM_CANCEL_DATE IS NULL; COLUMN name FORMAT A30 set long 9999 SELECT owner, name, category, sql_text, used FROM dba_outlines WHERE category = 'GOLD'; COLUMN hint FORMAT A50 SELECT node, stage, join_pos, hint FROM dba_outline_hints WHERE name = 'GOLD_OUTLINE_1'; ALTER SESSION SET query_rewrite_enabled=TRUE; ALTER SESSION SET use_stored_outlines=GOLD; ***************************** Index Stats Validate Structure ******************************* CREATE OR REPLACE PROCEDURE PRC_GET_INDEX_STATS AS cursor c1 is select a.owner,b.index_name,b.table_name,a.segment_type,a.tablespace_name,a.bytes/1024/1024 as from dba_segments a, dba_indexes b where a.segment_name=b.index_name and a.owner=b.owner and b.table_name in ('SCHNAV','SCHNAVBD','MKTPRICE','WEEKNAV','LASTNAV') and b.owner='MFUND' order by b.table_name; BEGIN for i in c1 loop execute immediate 'ANALYZE INDEX '||i.owner||'.'||i.index_name ||' VALIDATE STRUCTURE' ; insert into view_index_stats SELECT HEIGHT,BLOCKS,NAME,PARTITION_NAME,LF_ROWS,LF_BLKS,LF_ROWS_LEN,LF_BLK_LEN,BR_ROWS,BR_BLKS, BR_ROWS_LEN,BR_BLK_LEN,DEL_LF_ROWS,DEL_LF_ROWS_LEN,DISTINCT_KEYS,MOST_REPEATED_KEY,BTREE_SPACE, USED_SPACE,PCT_USED,ROWS_PER_KEY,BLKS_GETS_PER_ACCESS,PRE_ROWS,PRE_ROWS_LEN,OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE,i.table_name,i.owner,sysdate FROM index_stats WHERE name = i.index_name ; commit; end loop; end; / ***************** Top 10 Components to have allocated memory in Shared Pool ******************* select * From (select name, bytes/1024/1024 MB from v$sgastat where pool='shared pool' order by 2 desc) where rownum<11; ****************************** Parsing Details in Shared Pool ********************************** select substr(d.host_name,1,8) machine_name, substr(d.instance_name,1,8) instance_name, rpad(nvl(program,machine),30,' ') program_name, round(avg((a.value/b.value)*100),2) avg_parse, round(max((a.value/b.value)*100),2) max_parse, count(*) program_session_count from v$session s,v$sesstat a,v$sesstat b, v$instance d where b.value>0 and s.sid=a.sid and a.sid=b.sid and a.statistic#=(select statistic# from v$statname where name='parse count (hard)') and b.statistic#=(select statistic# from v$statname where name='parse count (total)') group by substr(d.host_name,1,8),substr(d.instance_name,1,8),rpad(nvl(program,machine),30,' ') order by round(avg((a.value/b.value)*100),2); select a.sid, a.program, b.value total_parse, c.value soft_parse, (b.value-c.value) hard_parse, d.value cpu_time, e.value elapsed_time from v$session a, v$sesstat b, v$sesstat c, v$sesstat d, v$sesstat e where a.sid = b.sid and a.sid = c.sid and b.statistic# = 179 and c.statistic# = 180 and d.statistic# = 177 and e.statistic# = 178 and a.sid = d.sid and a.sid = e.sid order by b.value desc; select sql_id from (select sql_id,PARSING_SCHEMA_NAME, SHARABLE_MEM from v$sqlarea order by SHARABLE_MEM desc) where rownum<10; ****************** Largest 2 objects from each namespace in v$DB_OBJECT_CACHE ****************** select * from (select row_number () over (partition by namespace order by sharable_mem desc) row_within, namespace, sharable_mem, substr(name, 1,40) name from v$db_object_cache order by sharable_mem desc ) where row_within<=2 order by namespace, row_within; ***************** Retrieve unrecoverable data file information directly from v$ table *************** SELECT df.name datafile_name, df.unrecoverable_time FROM v$datafile df, v$backup bk WHERE df.file#=bk.file# and df.unrecoverable_change#!=0 and df.unrecoverable_time >(select max(end_time) from v$rman_backup_job_details where INPUT_TYPE in ('DB FULL' ,'DB INCR'));****************** Database objects are affected by the unrecoverable operation ********************** select distinct dbo.owner,dbo.object_name, dbo.object_type, dfs.tablespace_name, dbt.logging table_level_logging, ts.logging tablespace_level_logging from v$segstat ss, dba_tablespaces ts, dba_objects dbo, dba_tables dbt, v$datafile df, dba_data_files dfs, v$tablespace vts where ss.statistic_name ='physical writes direct' and dbo.object_id = ss.obj# and vts.ts# = ss.ts# and ts.tablespace_name = vts.name and ss.value != 0 and df.unrecoverable_change# != 0 and dfs.file_name = df.name and ts.tablespace_name = dfs.tablespace_name and dbt.owner = dbo.owner and dbt.table_name = dbo.object_name; ******************************* Procedure for enable trace ************************************* CREATE OR REPLACE PROCEDURE "SYS"."SET_USERPERF_TRACE" (sidin number,serialinnumber,enb varchar2) as sprocid number; begin IF enb = 'Y' THEN sys.dbms_system.set_ev(sidin,serialin,10046,12,''); select spid into sprocid from sys.v_$process p, sys.v_$session s where p.addr=s.paddr and s.sid=sidin and s.serial#=serialin; dbms_output.put_line ('Trace Enabled'); dbms_output.put_line ('Session process id is '||sprocid); end if; if enb = 'N' THEN sys.dbms_system.set_ev(sidin,serialin,10046,0,''); dbms_output.put_line ('Trace Disabled'); end if; if enb not in ('Y', 'N') THEN dbms_output.put_line ('The 3rd parameter must me either Y or N'); end if; end; ****************************** Estimate Index Creation size *********************************** SET SERVEROUTPUT ON DECLARE v_used_bytes NUMBER(10); v_Allocated_Bytes NUMBER(10); BEGIN DBMS_SPACE.CREATE_INDEX_COST ( 'CREATE INDEX EBAWEB.IDX_COD_TRD_DT ON EBAWEB.COD_CDX_ORDR_DTLS(COD_TRD_DT)', v_used_Bytes, v_Allocated_Bytes ); DBMS_OUTPUT.PUT_LINE('Used Bytes MB: ' || round(v_used_Bytes/1024/1024)); DBMS_OUTPUT.PUT_LINE('Allocated Bytes MB: ' || round(v_Allocated_Bytes/1024/1024)); END; / ******************************** Partition Creation options ************************************ Exchange Partition : ALTER TABLE big_table2 EXCHANGE PARTITION big_table_2007 WITH TABLE big_table WITHOUT VALIDATION UPDATE GLOBAL INDEXES; Split Partition : ALTER TABLE big_table SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2005 23:59:59', 'DD-MON-YYYY HH24:MI:SS')) INTO (PARTITION big_table_2005, PARTITION big_table_2007) UPDATE GLOBAL INDEXES; ********************************** Get SQL Plan using SQL_PLAN *********************************** SELECT '| Operation | PHV/Object Name | Rows | Bytes| Cost |' as "Optimizer Plan:" FROM dual UNION ALL SELECT * FROM (SELECT rpad('|'||substr(lpad(' ',1*(depth-1))||operation|| decode(options, null,'',' '||options), 1, 32), 33, ' ')||'|'|| rpad(decode(id, 0, '----- '||to_char(hash_value)||' ['||to_char(child_number)||'] -----' , substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null,object_name) ||' ',1, 20)), 21, ' ')||'|'|| lpad(decode(cardinality,null,' ', decode(sign(cardinality-1000), -1, cardinality||' ', decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K', decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M', trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' || lpad(decode(bytes,null,' ', decode(sign(bytes-1024), -1, bytes||' ', decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K', decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M', trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' || lpad(decode(cost,null,' ', decode(sign(cost-10000000), -1, cost||' ', decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M', trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan" FROM v$sql_plan WHERE address='&&addr' AND hash_value='&&hash' AND child_number=&&child ORDER BY hash_value,child_number,id); ********************************** Get SQL Plan using dbms_xplan *********************************** INSERT INTO plan_table SELECT DISTINCT address,sysdate,'REMARKS',operation,options,object_node ,object_owner,object_name, 0,'object_type',optimizer,search_columns ,id,parent_id,position,cost,cardinality,bytes,other_tag ,partition_start,partition_stop,partition_id,other,distribution ,cpu_cost,io_cost,temp_space FROM v$sql_plan WHERE address='&&addr' AND hash_value='&&hash' AND child_number=&&child; SELECT plan_table_output FROM TABLE(dbms_xplan.display('plan_table',null,'serial')); ***************************** Statement details and impact ***************************************** SELECT executions ,cpu_time ,DECODE(executions,0,0,ROUND(cpu_time/executions,2)) avg_cpu_time ,buffer_gets ,DECODE(executions,0,0,ROUND(buffer_gets/executions)) avg_buffer_gets ,rows_processed ,DECODE(executions,0,0,ROUND(rows_processed/executions)) avg_row_process ,disk_reads ,DECODE(executions,0,0,ROUND(disk_reads/executions)) avg_disk_read ,address||'/'||hash_value||'['||child_number||']' d FROM v$sql WHERE address='&&addr' AND hash_value='&&hash'; ******************************************PICK HOURS************************* ORACLE 9i :- ---------- column "CPU" format 999,999,999 column "Recursive" format 999,999,999 select to_char(snap_time,'HH24'), avg(newcpu.value - oldcpu.value) "CPU", avg(newrecursive.value - oldrecursive.value) "Recursive" from stats$sysstat newCPU, stats$sysstat newrecursive, stats$sysstat oldCPU, stats$sysstat oldrecursive, stats$snapshot sn where newCPU.snap_id = sn.snap_id and newrecursive.snap_id = sn.snap_id and oldCPU.snap_id=sn.snap_id-1 and oldrecursive.snap_id=sn.snap_id-1 and newcpu.statistic#=11 and oldcpu.statistic#=11 and newrecursive.statistic#=8 and oldrecursive.statistic#=8 and trunc(snap_time)=to_date('&date') having avg(newcpu.value - oldcpu.value)>0 and avg(newrecursive.value - oldrecursive.value)>0 group by to_char(snap_time,'HH24') / ORACLE 10g:- ------------ column "CPU" format 999,999,999 column "Recursive" format 999,999,999 select to_char(BEGIN_INTERVAL_TIME,'HH24') "Time", avg(newcpu.value - oldcpu.value) "CPU", avg(newrecursive.value - oldrecursive.value) "Recursive" from DBA_HIST_SYSSTAT newCPU, DBA_HIST_SYSSTAT newrecursive, DBA_HIST_SYSSTAT oldCPU, DBA_HIST_SYSSTAT oldrecursive, dba_hist_snapshot sn where newCPU.snap_id = sn.snap_id and newrecursive.snap_id = sn.snap_id and oldCPU.snap_id=sn.snap_id-1 and oldrecursive.snap_id=sn.snap_id-1 and newcpu.STAT_ID=572264820 and oldcpu.STAT_ID=572264820 and newrecursive.STAT_ID=4009879262 and oldrecursive.STAT_ID=4009879262 and trunc(BEGIN_INTERVAL_TIME)=to_date('&date') having avg(newcpu.value - oldcpu.value)>0 and avg(newrecursive.value - oldrecursive.value)>0 group by to_char(BEGIN_INTERVAL_TIME,'HH24') order by 1 / ***************************************CPU and IO *************************************** SELECT wait_class, SUM (time_secs) time_secs, SUM (pct) pct FROM (SELECT wait_class, NAME, ROUND (time_secs, 2) time_secs, ROUND (time_secs * 100 / SUM (time_secs) OVER (), 2) pct FROM (SELECT n.wait_class, e.event NAME, e.time_waited / 100 time_secs FROM v$system_event e, v$event_name n WHERE n.NAME = e.event AND n.wait_class <> 'Idle' AND time_waited > 0 UNION SELECT 'CPU', 'server CPU', SUM (VALUE / 1000000) time_secs FROM v$sys_time_model WHERE stat_name IN ('background cpu time', 'DB CPU'))) GROUP BY wait_class ORDER BY time_secs DESC; *************************************** dbms_system package ***************************************** SET AUTOPRINT ON VARIABLE val VARCHAR2(4000) BEGIN dbms_system.get_env('ORACLE_HOME', :val); END; / EXEC DBMS_SYSTEM.KCFRMS(); flush max_wait, max read/write time to Zero EXEC DBMS_SYSTEM.KSDDDT(); Added timestamp in trace file DBMS_SYSTEM.KSDWRT(dest IN BINARY_INTEGER,tst IN VARCHAR2); where dest Destination file, 1=SQL trace file, 2=alert log, 3=both e.g. DECLARE elapsed_time_t1 number; elapsed_time_t2 number; cpu_time_t1 number; cpu_time_t2 number; BEGIN elapsed_time_t1:=dbms_utility.get_time; cpu_time_t1:=dbms_utility.get_cpu_time; dbms_stats.gather_schema_stats(user); -- do something expensive elapsed_time_t2:=dbms_utility.get_time; cpu_time_t2:=dbms_utility.get_cpu_time; sys.dbms_system.ksdddt; sys.dbms_system.ksdwrt(1, '=== Elapsed time: ' ||to_char((elapsed_time_t2 - elapsed_time_t1)/100) ||' sec CPU: ' || to_char((cpu_time_t2 - cpu_time_t1)/100) || ' sec'); END; / EXEC DBMS_SYSTEM.READ_EV(10046,:lev) EXEC DBMS_SYSTEM.SET_EV(:sid, :serial, 65535, 3, 'errorstack'); Own session. ****************************** interesting facts !!!!!!!!!!!!!!!!!! **************************** -- Dump control file contents The event setting to disable free extent coalescing by SMON is 10269. event = "10269 trace name context forever" -- Dump optimizer statistics whenever a SQL statement is parsed (hint: change statement or flush pool) alter session set events '10053 trace name context forever, level 1'; -- Dump a database block (File/ Block must be converted to DBA address) -- Convert file and block number to a DBA (database block address). Eg: variable x varchar2; exec :x := dbms_utility.make_data_block_address(1,12); print x alter session set events 'immediate trace name blockdump level 50360894'; To know what is the Operating System Block size as also the Log Block Size use this SQL logging in as SYS user. select max(lebsz) from sys.x$kccle; Adjust_SCN alter session set events 'immediate trace name adjust_scn level 1' calculate the target wrap as the wrap in above SCN + 1. alter session set events 'immediate trace name DUMP_SCN_CHECK level target_wrap'; Oracle Dumps ALTER SESSION SET EVENTS 'immediate trace name global_area level level'; ORADEBUG DUMP GLOBAL_AREA level Level Description 1 Include PGA 2 Include SGA 4 Include UGA 8 Include indirect memory dumps ALTER SESSION SET EVENTS 'immediate trace name library_cache level level'; ORADEBUG DUMP LIBRARY_CACHE level Level Description 1 Dump library cache statistics 2 Include hash table histogram 3 Include dump of object handles 4 Include dump of object structures (heap 0) ALTER SESSION SET EVENTS 'immediate trace name row_cache level level'; ORADEBUG DUMP ROW_CACHE level Level Description 1 Dump row cache statistics 2 Include hash table histogram 8 Include dump of object structures ALTER SESSION SET EVENTS 'immediate trace name buffers level level'; Level Description 1 Buffer headers only 2 Level 1 + block headers 3 Level 2 + block contents 4 Buffer headers only + hash chain 5 Level 1 + block headers + hash chain 6 Level 2 + block contents + hash chain 8 Buffer headers only + hash chain + users/waiters 9 Level 1 + block headers + hash chain + users/waiters 10 Level 2 + block contents + hash chain + users/waiters ALTER SESSION SET EVENTS 'immediate trace name heapdump level level'; Level Description 1 PGA summary 2 SGA summary 4 UGA summary 8 Callheap (Current) 16 Callheap (User) 32 Large pool 64 Streams pool 128 Java pool 1025 PGA with contents 2050 SGA with contents 4100 UGA with contents 8200 Callheap with contents (Current) 16400 Callheap with contents (User) 32800 Large pool with contents 65600 Streams pool with contents 131200 Java pool with contents ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level 2, addr 2153245560'; ORADEBUG DUMP HEAPDUMP_ADDR 2 2153245560 ALTER SESSION SET EVENTS 'immediate trace name processstate level 10'; ORADEBUG DUMP PROCESSSTATE level ALTER SESSION SET EVENTS 'immediate trace name systemstate level 10'; ORADEBUG DUMP SYSTEMSTATE level event = "60 trace name systemstate level 10" ALTER SESSION SET EVENTS 'immediate trace name errorstack level level'; ORADEBUG DUMP ERRORSTACK level Level Description 0 Error stack only 1 Error stack and function call stack 2 As level 1 plus the process state 3 As level 2 plus the context area ORADEBUG EVENT IMMEDIATE TRACE NAME ERRORSTACK level ALTER SESSION SET EVENTS '942 trace name errorstack level 3'; ALTER SESSION SET EVENTS '604 trace name errorstack'; event = "942 trace name errorstack level 3" ORADEBUG EVENT 942 TRACE NAME ERRORSTACK LEVEL 3 ALTER SESSION SET EVENTS 'immediate trace name hanganalyze level 5'; ORADEBUG DUMP HANGANALYZE 5 event = "60 trace name hanganalyze level 5" ALTER SESSION SET EVENTS '60 trace name hanganalyze level 5'; ALTER SESSION SET EVENTS 'immediate trace name workareatab_dump level level'; ORADEBUG DUMP WORKAREATAB_DUMP level Level Description 1 Global SGA Info 2 Workarea Table (Summary) 3 Workarea Table (Detail) ALTER SESSION SET EVENTS 'immediate trace name enqueues level level'; ORADEBUG DUMP ENQUEUES level Level Description 1 Dump a summary of active resources and locks, the resource free list and the hash table 2 Include a dump of resource structures 3 Include a dump of lock structures ALTER SESSION SET EVENTS 'immediate trace name latches level level'; ORADEBUG DUMP LATCHES level Level Description 1 Latches 2 Include statistics ALTER SESSION SET EVENTS 'immediate trace name events level level'; ORADEBUG DUMP EVENTS level Level Description 1 Session 2 Process 4 System ALTER SYSTEM DUMP DATAFILE absolute_file_number BLOCK block_number; ALTER SYSTEM DUMP DATAFILE absolute_file_number BLOCK MIN minimum_block_number BLOCK MAX maximum_block_number; ALTER SYSTEM DUMP DATAFILE 'file_name' BLOCK block_number; dd bs=8k if=filename skip=200 count=4 | od -x ALTER SESSION SET EVENTS 'immediate trace name treedump level object_id'; ORADEBUG DUMP TREEDUMP object_id ALTER SYSTEM DUMP UNDO_HEADER 'segment_name'; SELECT segment_id, segment_name FROM dba_rollback_segs ORDER BY segment_id; ALTER SYSTEM DUMP UNDO BLOCK 'segment_name' XID xidusn xidslot xidsqn; SELECT xidusn, xidslot, xidsqn FROM v$transaction; ALTER SESSION SET EVENTS 'immediate trace name file_hdrs level level'; ORADEBUG DUMP FILE_HDRS level Level Description 1 Dump datafile entry from control file 2 Level 1 + generic file header 3 Level 2 + datafile header 10 Same as level 3 ALTER SESSION SET EVENTS 'immediate trace name controlf level level'; ORADEBUG DUMP CONTROLF level Level Description 1 Generic file header 2 Level 1 + database information + checkpoint progress records 3 Level 2 + reuse record section 10 Same as level 3 ALTER SESSION SET EVENTS 'immediate trace name redohdr level 1'; ORADEBUG DUMP REDOHDR level Level Description 1 Dump redo log entry from control file 2 Level 1 + generic file header 3 Level 2 + log file header 10 Same as level 3 ALTER SYSTEM DUMP LOGFILE 'FileName' SCN MIN MinimumSCN SCN MAX MaximumSCN TIME MIN MinimumTime TIME MAX MaximumTime LAYER Layer OPCODE Opcode DBA MIN FileNumber . BlockNumber DBA MAX FileNumber . BlockNumber RBA MIN LogFileSequenceNumber . BlockNumber RBA MAX LogFileSequenceNumber . BlockNumber; 01Jan1988 time = (((((((yyyy - 1988)) * 12 + mm - 1) * 31 + dd - 1) * 24 + hh) * 60 + mi) * 60 + ss); ********************************************* Plan change query *************************************************** set lines 155 col execs for 999,999,999 col avg_etime for 999,999.999 col avg_lio for 999,999,999.9 col begin_interval_time for a30 col node for 99999 break on plan_hash_value on startup_time skip 1 select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id = nvl('&sql_id','4dqs2k5tynk61') and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 order by 1, 2, 3 /********************************************* Current SQLAREA Query *************************************************** cool begin_interval_time for a30 break on plan_hash_value on startup_time skip 1 select s.sql_id, s.plan_hash_value, nvl(executions,0) execs, (elapsed_time/decode(nvl(executions,0),0,1,executions))/1000000 avg_etime, (buffer_gets/decode(nvl(buffer_gets,0),0,1,executions)) avg_lio from V$sqlarea S where s.sql_id = nvl('&sql_id','4dqs2k5tynk61') order by 1, 2, 3 / ********************************************* SYSTEMSTATE Dump *************************************************** To get HANGANALYZE trace: connect / as sysdba oradebug setmypid oradebug unlimit oradebug hanganalyze 5 --Wait 30 seconds to give time to identify process state changes. oradebug hanganalyze 5 exit SECOND session: Please make sure a separate session - To get SYSTEM STATE DUMP: From another SQL*Plus session: connect / as sysdba oradebug setmypid oradebug unlimit oradebug dump systemstate 266 --Wait 30 seconds oradebug dump systemstate 10 -- this allows Oracle to show you the file name oradebug tracefile_name exit Set Oracle Trace : select spid from v$process where addr = (select paddr from v$session where sid=&sid); oradebug SETOSPID 7154 oradebug UNLIMIT oradebug EVENT 10046 trace name context forever,level 12; oradebug TRACEFILE_NAME oradebug CLOSE_TRACE; OR exec dbms_system.set_ev(&sid,&serial,10046,12,''); exec dbms_system.set_ev(&sid,&serial,10046,0,''); exec dbms_system.set_ev(627,25756,10032,10,''); alter session set events '10032 trace name context forever, level 10'; ORA11G Tracing alter session set events 'sql_trace[SQL: 32cqz71gd8wy3] {pgadep: exactdepth 0} plan_stat=all_executions,wait=true,bind=true'; alter session set events 'sql_trace[SQL: 32cqz71gd8wy3] {pgadep: exactdepth 0} {callstack: fname opiexe} plan_stat=all_executions, wait=true,bind=true'; alter session set events 'trace[RDBMS.SQL_Transform] [SQL: 32cqz71gd8wy3] disk=high RDBMS.query_block_dump(1) processstate(1) callstack(1)'; alter session set events 'sql_trace wait=true, plan_stat=never'; alter session set events 'trace[sql_mon.*] memory=high,get_time=highres'; alter system set events 'trace[sql_mon | sql_optimizer.*]{process: pname = p000 | p005}'; alter system set events 'trace[sql_mon | sql_optimizer.*][sql: 7ujay4u33g337]{process: pname = p000 | p005}'; ********************************************* Revoke SQLPLUS Access ************************************************* INSERT INTO SYSTEM.sqlplus_product_profile VALUES ('SQL*Plus', 'RAJESHSH', 'CONNECT', null, null, 'DISABLED', null, null); INSERT INTO SYSTEM.sqlplus_product_profile VALUES ('SQL*Plus', 'RAJESHSH', 'INSERT', null, null, 'DISABLED', null, null); INSERT INTO SYSTEM.sqlplus_product_profile VALUES ('SQL*Plus', 'RAJESHSH', 'UPDATE', null, null, 'DISABLED', null, null); INSERT INTO SYSTEM.sqlplus_product_profile VALUES ('SQL*Plus', 'RAJESHSH', 'DELETE', null, null, 'DISABLED', null, null); INSERT INTO SYSTEM.sqlplus_product_profile VALUES ('SQL*Plus', 'RAJESHSH', 'SELECT', null, null, 'DISABLED', null, null); INSERT INTO SYSTEM.sqlplus_product_profile VALUES ('SQL*Plus', 'RAJESHSH', 'DROP', null, null, 'DISABLED', null, null); INSERT INTO SYSTEM.sqlplus_product_profile VALUES ('SQL*Plus', 'RAJESHSH', 'CREATE', null, null, 'DISABLED', null, null); INSERT INTO SYSTEM.sqlplus_product_profile VALUES ('SQL*Plus', 'RAJESHSH', 'SET', null, null, 'DISABLED', null, null); *************************** Failed login details through Database Auditing ******************************************* col OS_USERNAME for a20 col TERMINAL for a25 col USERHOST for a30 col time for a15 set lines 150 select count(*),OS_USERNAME,username,USERHOST,terminal,to_char(timestamp,'DD-MON-YYYY') "Time" from dba_audit_session where returncode<>0 --and to_char(timestamp,'MON-YYYY')='JAN-2010' and username='EBAPMS' group by OS_USERNAME,username,USERHOST,terminal,to_char(timestamp,'DD-MON-YYYY'); **************************************** Get DBA jobs metadata details ********************************************** variable MYCALL VARCHAR2(1000) exec dbms_ijob.FULL_EXPORT(3221,:MYCALL); set lines 150 select :MYCALL from dual; **************************************** Change Database DBTIMEZONE ********************************************** select tzname,tzabbrev from V$TIMEZONE_NAMES where tzabbrev = 'MST' alter database set TIME_ZONE = '-07:00'; alter session set TIME_ZONE='-03:00'; select DBTIMEZONE from dual; select SESSIONTIMEZONE from dual; **************************************** Check Column USage Statistics **************************************** select a.object_name, sum(b.EXECUTIONS) from v$sqlarea b, v$sql_plan a where a.hash_value = b.hash_value and object_name in (select index_name from dba_indexes where table_name = '&tname' ) group by a.object_name order by 2; OR set lines 150 set pages 500 col table_name for a20 col column_name for a20 select a.object_name table_name, c.column_name,equality_preds, equijoin_preds, range_preds, like_preds from dba_objects a, col_usage$ b, dba_tab_columns c where a.object_id=b.OBJ# and c.COLUMN_ID=b.INTCOL# and a.object_name=c.table_name and b.obj#=a.object_id and a.object_name='&table_name' and a.object_type='TABLE' order by 3 desc,4 desc, 5 desc; OR set lines 150 set pages 5000 col c1 heading 'Object Name' format a30 col c2 heading 'Operation' format a15 col c3 heading 'Option' format a30 col c4 heading 'Index Usage Count' format 999,999 break on c1 skip 2 break on c2 skip 2 select p.object_name c1,p.operation c2,p.options c3,count(1) c4 from dba_hist_sql_plan p, dba_hist_sqlstat s where p.object_owner='&&tableowner' and p.operation like '%INDEX%' and p.sql_id = s.sql_id and p.object_name in (select index_name from dba_indexes where table_name='&tablename' and owner='&tableowner') group by p.object_name, p.operation, p.options order by 4,1,2,3; ********************************************TABLE SCCESS FULL *********************************************** col text for a70 select a.sql_id, a.plan_hash_value, b.buffer_gets/decode(executions,null,1,0,1,executions) bu_ex, b.executions, substr(b.sql_text, 1, 70) text from v$sql_plan a, v$sql b where a.sql_id = b.sql_id and OPERATION='TABLE ACCESS' and OPTIONS='FULL' order by 3; select sql_id, executions, parse_calls, substr(sql_text, 1, 50) text from v$sql where plan_hash_value=1359677021 order by executions; ********************************************Hard Parse ******************************************************* set lines 300 pages 50000 col snap_date for a30 select to_char(hhmi,'DY DD-MON-YYYY HH24:MI:SS') SNAP_DATE, hard_parse, failed_parse, round((failed_parse/nvl(decode(hard_parse,0,1,hard_parse),1))*100) "FAILED_PERCENT", round(hard_parse/(hhmi_diff*24*60*60),0) "HARD_PARSE/SEC", round(failed_parse/(hhmi_diff*24*60*60),0) "FAILED_PARSE/SEC" from ( select hhmi, hhmi - lag(hhmi,1,null) over (order by hhmi) hhmi_diff, nvl(hard_parse - lag(hard_parse,1,null) over (order by hhmi),0) hard_parse, nvl(failed_parse - lag(failed_parse,1,null) over (order by hhmi),0) failed_parse from ( select hhmi, sum(case when stat_name='parse count (hard)' then value end) hard_parse, sum(case when stat_name='parse count (failures)' then value end) failed_parse from ( select to_date(to_char(end_interval_time,'DD-MM-YYYY HH24:MI'),'DD-MM-YYYY HH24:MI') hhmi, value, stat_name from sys.WRM$_SNAPSHOT S, sys.WRH$_SYSSTAT ST, sys.WRH$_STAT_NAME SN where s.snap_id = st.snap_id and st.stat_id = sn.stat_id and sn.stat_name in ('parse count (hard)', 'parse count (failures)') and trunc(END_INTERVAL_TIME) >= trunc(sysdate-&diff) and s.instance_number= &instance -- for RAC and s.instance_number = st.instance_number -- for RAC order by 1 ) group by hhmi )); *************************************Hard parse *********************************** select * from ( select plan_hash_value, count(distinct(hash_value)), sum(executions), sum(parse_calls) from v$sql group by plan_hash_value having count(distinct(hash_value)) > 10 order by 2 desc ) where rownum<21; select sql_text from v$sql where plan_hash_value=&phv and rownum<10; ************ Index usage with disk reads and rows processed per index usage with snap time ******************* col c0 heading ëBegin|Interval|timeà format a8 col c1 heading ëIndex|Nameà format a40 col c2 heading ëDisk|Readsà format 99,999,999 col c3 heading ëRows|Processedà format 99,999,999 select to_char(s.begin_interval_time,'mm-dd hh24') c0, p.object_name c1, sum(t.disk_reads_total) c2, sum(t.rows_processed_total) c3 from dba_hist_sql_plan p, dba_hist_sqlstat t,dba_hist_snapshot s where p.sql_id = t.sql_id and t.snap_id = s.snap_id and p.object_type like '%INDEX%' and p.OBJECT_OWNER='&name' group by to_char(s.begin_interval_time,'mm-dd hh24'),p.object_name order by c0,c1,c2 desc; ********************** Extract rows from a CORRUPT table creating ROWID from DBA_EXTENTS ********************** connect / as sysdba set serveroutput on set concat off DECLARE nrows number; rid rowid; dobj number; ROWSPERBLOCK number; BEGIN ROWSPERBLOCK:=<VALUE CALCULATED IN STEP 1>; nrows:=0; select data_object_id into dobj from dba_objects where owner = '&&table_owner' and object_name = '&&table_name' -- and subobject_name = '<table partition>' Add this condition if table is partitioned; for i in (select relative_fno, block_id, block_id+blocks-1 totblocks from dba_extents where owner = '&&owner' and segment_name = '&&table_name' -- and partition_name = '<table partition>' Add this condition if table is partitioned -- and file_id != <OFFLINED DATAFILE> This condition is only used if a datafile needs to be skipped due to ORA-376 order by extent_id) loop for br in i.block_id..i.totblocks loop for j in 1..ROWSPERBLOCK loop begin rid := dbms_rowid.ROWID_CREATE(1,dobj,i.relative_fno, br , j-1); insert into <OWNER.NEW_TABLE> (<columns here>) select /*+ ROWID(A) */ <columns here> from &&table_owner.&&table_name A where rowid = rid; if sql%rowcount = 1 then nrows:=nrows+1; end if; if (mod(nrows,10000)=0) then commit; end if; exception when others then null; end; end loop; end loop; end loop; COMMIT; dbms_output.put_line('Total rows: '||to_char(nrows)); END; / ******************************* Get datapump dump file header details ********************************** CREATE PROCEDURE show_dumpfile_info( p_dir VARCHAR2 DEFAULT 'DATA_PUMP_DIR', p_file VARCHAR2 DEFAULT 'EXPDAT.DMP') AS -- p_dir = directory object where dumpfile can be found -- p_file = simple filename of export dumpfile (case-sensitive) v_separator VARCHAR2(80) := '--------------------------------------' || '--------------------------------------'; v_path all_directories.directory_path%type := '?'; v_filetype NUMBER; -- 0=unknown 1=expdp 2=exp v_fileversion VARCHAR2(15); -- 0.1=10gR1 1.1=10gR2 2.1=11g v_info_table sys.ku$_dumpfile_info; -- PL/SQL table with file info type valtype IS VARRAY(22) OF VARCHAR2(2048); var_values valtype := valtype(); no_file_found EXCEPTION; PRAGMA exception_init(no_file_found, -39211); BEGIN -- Show generic info: -- ================== dbms_output.put_line(v_separator); dbms_output.put_line('Purpose..: Obtain details about export ' || 'dumpfile. Version: 19-MAR-2008'); dbms_output.put_line('Required.: RDBMS version: 10.2.0.1.0 or higher'); dbms_output.put_line('. ' || 'Export dumpfile version: 7.3.4.0.0 or higher'); dbms_output.put_line('. ' || 'Export Data Pump dumpfile version: 10.1.0.1.0 or higher'); dbms_output.put_line('Usage....: ' || 'execute show_dumfile_info(''DIRECTORY'', ''DUMPFILE'');'); dbms_output.put_line('Example..: ' || 'exec show_dumfile_info(''MY_DIR'', ''expdp_s.dmp'')'); dbms_output.put_line(v_separator); dbms_output.put_line('Filename.: ' || p_file); dbms_output.put_line('Directory: ' || p_dir); -- Retrieve Export dumpfile details: -- ================================= SELECT directory_path INTO v_path FROM all_directories WHERE directory_name = p_dir OR directory_name = UPPER(p_dir); dbms_datapump.get_dumpfile_info( filename => p_file, directory => UPPER(p_dir), info_table => v_info_table, filetype => v_filetype); var_values.EXTEND(22); FOR i in 1 .. 22 LOOP BEGIN SELECT value INTO var_values(i) FROM TABLE(v_info_table) WHERE item_code = i; EXCEPTION WHEN OTHERS THEN var_values(i) := ''; END; END LOOP; -- Show dumpfile details: -- ====================== -- For Oracle10g Release 2 and higher: -- dbms_datapump.KU$_DFHDR_FILE_VERSION CONSTANT NUMBER := 1; -- dbms_datapump.KU$_DFHDR_MASTER_PRESENT CONSTANT NUMBER := 2; -- dbms_datapump.KU$_DFHDR_GUID CONSTANT NUMBER := 3; -- dbms_datapump.KU$_DFHDR_FILE_NUMBER CONSTANT NUMBER := 4; -- dbms_datapump.KU$_DFHDR_CHARSET_ID CONSTANT NUMBER := 5; -- dbms_datapump.KU$_DFHDR_CREATION_DATE CONSTANT NUMBER := 6; -- dbms_datapump.KU$_DFHDR_FLAGS CONSTANT NUMBER := 7; -- dbms_datapump.KU$_DFHDR_JOB_NAME CONSTANT NUMBER := 8; -- dbms_datapump.KU$_DFHDR_PLATFORM CONSTANT NUMBER := 9; -- dbms_datapump.KU$_DFHDR_INSTANCE CONSTANT NUMBER := 10; -- dbms_datapump.KU$_DFHDR_LANGUAGE CONSTANT NUMBER := 11; -- dbms_datapump.KU$_DFHDR_BLOCKSIZE CONSTANT NUMBER := 12; -- dbms_datapump.KU$_DFHDR_DIRPATH CONSTANT NUMBER := 13; -- dbms_datapump.KU$_DFHDR_METADATA_COMPRESSED CONSTANT NUMBER := 14; -- dbms_datapump.KU$_DFHDR_DB_VERSION CONSTANT NUMBER := 15; -- For Oracle11gR1: -- dbms_datapump.KU$_DFHDR_MASTER_PIECE_COUNT CONSTANT NUMBER := 16; -- dbms_datapump.KU$_DFHDR_MASTER_PIECE_NUMBER CONSTANT NUMBER := 17; -- dbms_datapump.KU$_DFHDR_DATA_COMPRESSED CONSTANT NUMBER := 18; -- dbms_datapump.KU$_DFHDR_METADATA_ENCRYPTED CONSTANT NUMBER := 19; -- dbms_datapump.KU$_DFHDR_DATA_ENCRYPTED CONSTANT NUMBER := 20; -- For Oracle11gR2: -- dbms_datapump.KU$_DFHDR_COLUMNS_ENCRYPTED CONSTANT NUMBER := 21; -- dbms_datapump.KU$_DFHDR_ENCPWD_MODE CONSTANT NUMBER := 22; -- For Oracle10gR2: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 15; -- For Oracle11gR1: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 20; -- For Oracle11gR2: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 22; dbms_output.put_line('Disk Path: ' || v_path); IF v_filetype = 1 OR v_filetype = 2 THEN -- Get characterset name: BEGIN SELECT var_values(5) || ' (' || nls_charset_name(var_values(5)) || ')' INTO var_values(5) FROM dual; EXCEPTION WHEN OTHERS THEN null; END; IF v_filetype = 2 THEN dbms_output.put_line( 'Filetype.: ' || v_filetype || ' (Original Export dumpfile)'); dbms_output.put_line(v_separator); SELECT DECODE(var_values(13), '0', '0 (Conventional Path)', '1', '1 (Direct Path)', var_values(13)) INTO var_values(13) FROM dual; dbms_output.put_line('...Characterset ID.: ' || var_values(5)); dbms_output.put_line('...Direct Path.....: ' || var_values(13)); dbms_output.put_line('...Export Version..: ' || var_values(15)); ELSIF v_filetype = 1 THEN dbms_output.put_line( 'Filetype.: ' || v_filetype || ' (Export Data Pump dumpfile)'); dbms_output.put_line(v_separator); SELECT SUBSTR(var_values(1), 1, 15) INTO v_fileversion FROM dual; SELECT DECODE(var_values(1), '0.1', '0.1 (Oracle10g Release 1: 10.1.0.x)', '1.1', '1.1 (Oracle10g Release 2: 10.2.0.x)', '2.1', '2.1 (Oracle11g Release 1: 11.1.0.x)', '3.1', '3.1 (Oracle11g Release 2: 11.2.0.x)', var_values(1)) INTO var_values(1) FROM dual; SELECT DECODE(var_values(2), '0', '0 (No)', '1', '1 (Yes)', var_values(2)) INTO var_values(2) FROM dual; SELECT DECODE(var_values(14), '0', '0 (No)', '1', '1 (Yes)', var_values(14)) INTO var_values(14) FROM dual; SELECT DECODE(var_values(18), '0', '0 (No)', '1', '1 (Yes)', var_values(18)) INTO var_values(18) FROM dual; SELECT DECODE(var_values(19), '0', '0 (No)', '1', '1 (Yes)', var_values(19)) INTO var_values(19) FROM dual; SELECT DECODE(var_values(20), '0', '0 (No)', '1', '1 (Yes)', var_values(20)) INTO var_values(20) FROM dual; SELECT DECODE(var_values(21), '0', '0 (No)', '1', '1 (Yes)', var_values(21)) INTO var_values(21) FROM dual; SELECT DECODE(var_values(22), '1', '1 (Unknown)', '2', '2 (None)', '3', '3 (Password)', '4', '4 (Dual)', '5', '5 (Transparent)', var_values(22)) INTO var_values(22) FROM dual; dbms_output.put_line('...File Version....: ' || var_values(1)); dbms_output.put_line('...Master Present..: ' || var_values(2)); dbms_output.put_line('...GUID............: ' || var_values(3)); dbms_output.put_line('...File Number.....: ' || var_values(4)); dbms_output.put_line('...Characterset ID.: ' || var_values(5)); dbms_output.put_line('...Creation Date...: ' || var_values(6)); dbms_output.put_line('...Flags...........: ' || var_values(7)); dbms_output.put_line('...Job Name........: ' || var_values(8)); dbms_output.put_line('...Platform........: ' || var_values(9)); IF v_fileversion >= '2.1' THEN dbms_output.put_line('...Instance........: ' || var_values(10)); END IF; dbms_output.put_line('...Language........: ' || var_values(11)); dbms_output.put_line('...Block size......: ' || var_values(12)); dbms_output.put_line('...Metadata Compres: ' || var_values(14)); IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN dbms_output.put_line('...Data Compressed.: ' || var_values(18)); dbms_output.put_line('...Metadata Encrypt: ' || var_values(19)); dbms_output.put_line('...Data Encrypted..: ' || var_values(20)); dbms_output.put_line('...Column Encrypted: ' || var_values(21)); dbms_output.put_line('...Encrypt.pwd. mod: ' || var_values(22)); IF v_fileversion = '2.1' or v_fileversion = '3.1' THEN dbms_output.put_line('...Master Piece Cnt: ' || var_values(16)); dbms_output.put_line('...Master Piece Num: ' || var_values(17)); END IF; END IF; IF v_fileversion >= '1.1' THEN dbms_output.put_line('...Job Version.....: ' || var_values(15)); END IF; dbms_output.put_line('...Max Items Code..: ' || dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE); END IF; ELSE dbms_output.put_line('Filetype.: ' || v_filetype); dbms_output.put_line(v_separator); dbms_output.put_line('ERROR....: Not an export dumpfile.'); END IF; dbms_output.put_line(v_separator); EXCEPTION WHEN no_data_found THEN dbms_output.put_line('Disk Path: ?'); dbms_output.put_line('Filetype.: ?'); dbms_output.put_line(v_separator); dbms_output.put_line('ERROR....: Directory Object does not exist.'); dbms_output.put_line(v_separator); WHEN no_file_found THEN dbms_output.put_line('Disk Path: ' || v_path); dbms_output.put_line('Filetype.: ?'); dbms_output.put_line(v_separator); dbms_output.put_line('ERROR....: File does not exist.'); dbms_output.put_line(v_separator); END; / SET serveroutput on SIZE 1000000 exec show_dumpfile_info('my_dir', 'exp_s.dmp') ******************************* Alternative ways to obtain dumpfile details ********************************** impdp DIRECTORY=my_dir DUMPFILE=expdp.dmp NOLOGFILE=y SQLFILE=impdp_s.sql TABLES=notexist TRACE=100300********************************************** Opatch **************************************************** opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./9352164 OR opatch prereq CheckConflictAgainstOHWithDetail -ph ./9352164 opatch lsinventory -bugs_fixed | grep -i 'DATABASE PSU' ********************************** Recreate Oracle Inventory ********************************************* $ORACLE_HOME/oui/bin/runInstaller -silent -invPtrLoc /oracle10/oracle/oraInst.loc -attachHome ORACLE_HOME=/oracle10/oracle ORACLE_HOME_NAME=Ora10gHome *************************************** Enable runInstaller Tracing ************************************** ./runInstaller -silent -responseFile /ora11201/dump/database/response/db_install.rsp -force -ignoreSysPrereqs -debug -J-DTRACING.ENABLED=true -J-DTRACING.LEVEL=2 -DSRVM_TRACE_LEVEL=2 *****************************************Cloning********************************************************* $ORACLE_HOME/oui/bin/runInstaller -silent -detachHome ORACLE_HOME=/ora10202/oracle -invPtrLoc /ora10202/oracle/oraInst.loc $ORACLE_HOME/oui/bin/runInstaller -clone -silent ORACLE_HOME=/ora10202/oracle ORACLE_HOME_NAME=ora10ghome -invPtrLoc /ora10202/oracle/oraInst.loc ************************************************************************************************ $ORACLE_HOME/oui/bin/runInstaller -silent -detachHome ORACLE_HOME=/ora11gr2/orabase/oracle -invPtrLoc /ora11gr2/orabase/oracle/oraInst.loc $ORACLE_HOME/oui/bin/runInstaller -clone -silent -ignorePreReq ORACLE_HOME="/ora11gr2/orabase/oracle" ORACLE_HOME_NAME="ora11gr2home" ORACLE_BASE="/ora11gr2/orabase" OSDBA_GROUP=dba OSOPER_GROUP=dba -invPtrLoc /ora11gr2/orabase/oracle/oraInst.loc ****************************************** Windows Silent Installation ********************************** call C:\Users\ORADBA\Desktop\database\setup.exe -silent -nowait -waitforcompletion -responseFile C:\Users\ORADBA\Desktop\database\response\enterprise.rsp -ignoreSysPrereqs -force -nowelcome -ignorePrereq ***********************Deinstall******************************************************** $ cd $ORACLE_HOME/oui/bin $ ./runInstaller -silent -deinstall -removeallfiles REMOVE_HOMES={"/oracle11g/oracle/ora11g"} The parameter used are the following: -silent must be use to run without any X Display -deinstall is used to deinstall the software -removeallfiles forces all the files, including the Universal Installer to be deleted REMOVE_HOMES contains a list of ORACLE_HOME to be deleted. Triple-check that directory before running the command (e.g. /u01/app/oracle/product/10.2.0/db_1**************************************** Statistics in ROWCACHE ****************************************** SELECT parameter , sum(gets) , sum(getmisses) , 100*sum(gets - getmisses) / sum(gets) pct_succ_gets , sum(modifications) updates FROM V$ROWCACHE WHERE gets > 0 GROUP BY parameter; ********************************************* SQL Tuning Advisor for sql_text or sql_id ********************************************* SET SERVEROUTPUT ON -- Tuning task created for specific a statement from the AWR. DECLARE l_sql_tune_task_id VARCHAR2(2000); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( SQL_TEXT => 'select distinct b.Bonds_ShortName ID , case when count(a.CashFlowType) > 1 then 801 else 700 end NewCashFlowType from actuate.VIEW_BONDS_CASH_FLOW a,mxg.VIEW_BOND_FISV b where trim(a.Bonds_ShortName) = trim(b.Bonds_ShortName) and upper(a.CashFlowType) = ''N'' group by b.Bonds_ShortName', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 2000, task_name => 'sqltext_tuning_task', description => 'Tuning task for statement sqltext'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; / EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sqltext_tuning_task'); SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SYS'; SET LONG 10000; SET PAGESIZE 1000 SET LINESIZE 200 SELECT DBMS_SQLTUNE.report_tuning_task('sqltext_tuning_task') AS recommendations FROM dual; BEGIN DBMS_SQLTUNE.drop_tuning_task (task_name => 'sqltext_tuning_task'); END; / ******************sql_id level DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_id => '6b708s6cx9vv3', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 60, task_name => '6b708s6cx9vv3_tuning_task', description => 'Tuning task for statement gtzk9df8zbh3u'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; / EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'gtzk9df8zbh3u_tuning_task'); SET LONG 10000; SET PAGESIZE 1000 SET LINESIZE 200 SELECT DBMS_SQLTUNE.report_tuning_task('gtzk9df8zbh3u_tuning_task') AS recommendations FROM dual; SET PAGESIZE 24 set pages 500 set lines 150 col hint for a100 select p.name,attr#,attr_val hint from dba_sql_profiles p, sqlprof$attr h where p.signature = h.signature and name like ('&profile_name') order by attr# / ************************************************** Wait Type ********************************************* set lines 150 set pages 5000 col machine for a30 col username for a10 col Wait_type for a10 col program for a30 select sid,serial#,username,status, (case when event='read by other session' then 'BBW' when event='db file scattered read' then 'FTS' else 'CBC' end) Wait_type, sql_id,(select executions from v$sqlarea where sql_id=a.sql_id) executions,machine,program from v$session a where event in ('read by other session','db file scattered read','latch: cache buffers chains') and state='WAITING' order by Wait_type,executions desc / ********************************************* SQL Refactoring ******************************************** VARIABLE name VARCHAR2(20) exec name := 'SCOTT' SELECT ename, hiredate, sal FROM emp WHERE ename = NVL (:name, ename); TO VARIABLE name VARCHAR2(20) exec name := 'SCOTT' SELECT ename, hiredate, sal FROM emp WHERE ename LIKE NVL (:name, '%'); ------------------------------------------------------------------------------------------------- SELECT ename, hiredate, sal FROM emp WHERE TRUNC (hiredate) = TRUNC (SYSDATE); TO SELECT ename, hiredate, sal FROM emp WHERE hiredate BETWEEN TRUNC (SYSDATE) AND TRUNC (SYSDATE) + .99999; ------------------------------------------------------------------------------------------------- and to_char(date_col,'MM/YYYY') = to_char(p_date,'MM/YYYY') to and date_col >= trunc(p_date,'MON') and date_col < trunc(add_months(p_date,1),'MON') and to_char(date_col,'DD-MM-YYYY') = to_char(sysdate,'DD-MM-YYYY') to and date_col >= trunc(p_date,'DD') and date_col < trunc(p_date+1,'DD') TRUNC(DOJ)='15-Jul-2010' To doj >= trunc(to_Date('15-Jul-2010','DD-Mon-YYYY')) and doj < trunc(to_Date('15-Jul-2010','DD-Mon-YYYY')+1)select cloc.cust_id,branch_id,substr(ltrim(rtrim(cloc.loc_desc)),1,30) from retadm.cloc where cloc.bank_id= 'ICI' and cloc.cust_id not in (select cust_id from retadm.brcm) order by cloc.r_cre_time; TO select cloc.cust_id,branch_id,substr(ltrim(rtrim(cloc.loc_desc)),1,30) from retadm.cloc cloc where cloc.bank_id= 'ICI' and not exists (select 1 from retadm.brcm brcm where cloc.cust_id=brcm.cust_id) order by cloc.r_cre_time; OR select cloc.cust_id,cloc.branch_id,substr(ltrim(rtrim(cloc.loc_desc)),1,30) from retadm.cloc cloc left outer join retadm.brcm brcm on cloc.cust_id=brcm.cust_id where cloc.bank_id= 'ICI' and brcm.cust_id is null order by cloc.r_cre_time; OR with d_cust as (select cloc.cust_id,branch_id,substr(ltrim(rtrim(cloc.loc_desc)),1,30) from retadm.cloc where cloc.bank_id= 'ICI') select * from d_cust where not exists (select d_cust.cust_id from retadm.brcm where brcm.cust_id =d_cust.cust_id); ********************************************* SQL PROFILE ********************************************* SET SERVEROUTPUT ON -- Tuning task created for specific a statement from the AWR. DECLARE l_sql_tune_task_id VARCHAR2(2000); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( SQL_TEXT => 'select distinct b.Bonds_ShortName ID , case when count(a.CashFlowType) > 1 then 801 else 700 end NewCashFlowType from actuate.VIEW_BONDS_CASH_FLOW a,mxg.VIEW_BOND_FISV b where trim(a.Bonds_ShortName) = trim(b.Bonds_ShortName) and upper(a.CashFlowType) = ''N'' group by b.Bonds_ShortName', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 2000, task_name => 'sqltext_tuning_task', description => 'Tuning task for statement sqltext'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; / EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sqltext_tuning_task'); SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SYS';SET LONG 10000; SET PAGESIZE 1000 SET LINESIZE 200 SELECT DBMS_SQLTUNE.report_tuning_task('sqltext_tuning_task') AS recommendations FROM dual; BEGIN DBMS_SQLTUNE.drop_tuning_task (task_name => 'sqltext_tuning_task'); END; / set pages 500 set lines 150 col hint for a100 select p.name,attr#,attr_val hint from dba_sql_profiles p, sqlprof$attr h where p.signature = h.signature and name like ('&profile_name') order by attr# / ************************ used in 10g to disable the merge join cartesian and not in 9i.****************** alter session set "_optimizer_mjc_enabled"=false ; alter session set "_optimizer_cartesian_enabled"=false ; alter session set "_optimizer_sortmerge_join_enabled"=false ; ************************************* Generate SQL_ID from SQL_TEXT ************************************* select kglnahsv, kglnahsh from x$kglob where kglnaobj ='select sysdate from dual'; KGLNAHSV KGLNAHSH --------------------------------- ---------- b3dbd4abf1156b09780cbaeb8ba84e61 2343063137 b3dbd4abf1156b09780cbaeb8ba84e61 2343063137 select sql_id, hash_value, old_hash_value from v$sql where sql_text ='select sysdate from dual'; SQL_ID HASH_VALUE OLD_HASH_VALUE ------------- ---------- -------------- 7h35uxf5uhmm1 2343063137 3742653144 Python script : def sqlid_2_hash(sqlid): sum = 0 i = 1 alphabet = '0123456789abcdfghjkmnpqrstuvwxyz' for ch in sqlid: sum += alphabet.index(ch) * (32**(len(sqlid) - i)) i += 1 return sum % (2 ** 32) def stmt_2_sqlid(stmt): h = hashlib.md5(stmt + '\x00').digest() (d1,d2,msb,lsb) = struct.unpack('IIII', h) sqln = msb * (2 ** 32) + lsb stop = math.log(sqln, math.e) / math.log(32, math.e) + 1 sqlid = '' alphabet = '0123456789abcdfghjkmnpqrstuvwxyz' for i in range(0, stop): sqlid = alphabet[(sqln / (32 ** i)) % 32] + sqlid return sqlid def stmt_2_hash(stmt): return struct.unpack('IIII', hashlib.md5(stmt + '\x00').digest())[3] select lower(trim('&1')) sql_id , trunc(mod(sum((instr('0123456789abcdfghjkmnpqrstuvwxyz',substr(lower(trim('&1')),level,1))-1) *power(32,length(trim('&1'))-level)),power(2,32))) hash_value from dual connect by level <= length(trim('&1')) / Oracle 11G select dbms_sqltune_util0.sqltext_to_sqlid('select sysdate from dual'||chr(0)) sql_id from dual; ###############################PROFUNDS INDEX REBUILD############################### ps -ef | grep LOCAL=NO | grep oracleTBMS1| awk '{print $2}' | xargs kill -9 alter system enable restrict session; nohup sqlplus "/as sysdba" @PROFUNDS_INDEX_REBUILD.sql >INDEX_REBUILD.out & nohup sqlplus "/as sysdba" @PROFUNDS_PART_TABLES.sql >PART_TABLES.out & nohup sqlplus "/as sysdba" @PROFUNDS_TABLES.sql >TABLES.out & **************************************Temp Usage********************************************** set lines 200 select TABLESPACE_NAME, sum(BYTES_USED/1024/1024),sum(BYTES_FREE/1024/1024) from V$TEMP_SPACE_HEADER group by TABLESPACE_NAME; SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total; col TABLESPACE for a15 col username for a10 col OSUSER for a20 set lines 120 set pages 50000 SELECT b.tablespace, b.segfile#, b.segblk#, (b.blocks*8192)/1024/1024 "Size in MB", a.sid, a.serial#, a.username, a.osuser, a.status FROM v$session a,v$sort_usage b WHERE a.saddr = b.session_addr ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks; SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, COUNT(*) sort_ops FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, S.program, TBS.block_size, T.tablespace ORDER BY sid_serial; ---temp_usage by statement(9i onwards) SELECT S.sid || ',' || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, T.sqladdr address, Q.hash_value, Q.sql_text FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.sqladdr = Q.address (+) AND T.tablespace = TBS.tablespace_name ORDER BY S.sid;***************************************** Temp Space Usage Growth ********************************** col "Usage Date" for a20 select trunc(a.BEGIN_INTERVAL_TIME) "Usage Date",round(sum(VALUE/1024/1024)) "Temp Space Usage in MB" from DBA_HIST_SNAPSHOT a, DBA_HIST_SYSSTAT b where a.SNAP_ID=b.SNAP_ID and a.SNAP_ID between 17993 and 18175 and b.STAT_NAME IN('physical writes direct temporary tablespace', 'sorts (disk)', 'workarea executions - multipass') group by trunc(a.BEGIN_INTERVAL_TIME) order by 1; ********************Undo seg usage************************** col program format a30 col sid_serial format a30 set lines 500 SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial, NVL(s.username, 'None') orauser,s.status, s.program, r.name undoseg, t.used_ublk * TO_NUMBER(x.value)/1024/1024||'M' "Undo" FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size'; ******************************** GETTING SORT AREA USED BY SESSION ***************************/ SELECT s.username,s.sid,s.status,u.tablespace, u.contents, u.extents, u.blocks FROM v$session s,v$sort_usage u WHERE s.saddr=u.session_addr; OR SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.username, a.osuser, a.status FROM v$session a,v$sort_usage b WHERE a.saddr = b.session_addr ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks; To make file mkfile 1G pfor_temp_01.dbf (Aix is not by default for Solaris we can add a temp file) alter tablespace temp add tempfile '/pformweb/oradata/data/pfor_temp_01.dbf' reuse; /usr/sbin/mkfile 500m TEST_LMTEMP_02.dbf -------if path is not proper ############################### Start/Stop Trace a session ############################### To start trace : EXECUTE dbms_support.start_trace_in_session (SID, SERIAL#); to stop trace: EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#); - or - EXECUTE dbms_system.set_sql_trace_in_session (&SID, &SERIAL, TRUE); EXECUTE dbms_system.set_sql_trace_in_session (&SID, &SERIAL, FALSE); ###############################To increase the file transfer limit############################### ulimit -f unlimit ###############################To replace oracle10g to ora10gr3############################### :%s;/oracle10g;/ora10gr3;g ###############################To find the pmon process for the logged user############################### ps -ef | grep pmon | grep $LOGNAME ps -ef | grep tns | grep $LOGNAME for getting user ================== ps -ef|grep pmon|awk '{print $1}' for getting which all user are statred ========================================= ps -ef|grep pmon|awk '{print $1}'|sort -u ###############################Find last archive applied at dr ############################### select dest_id , max(sequence#) , applied from v$archived_log group by dest_id ,applied; select NAME, PARAMETER1, PARAMETER2 ,PARAMETER3 from V$EVENT_NAME where name='latch free'; ###############################Find sid if process spid is known############################### select sid,program ,type from v$session where paddr in (select addr from v$process where spid=&spid); ***********************************PROSESS HISTORY ******************************** set lines 200 set pages 200 col BEGIN_INTERVAL_TIME for a25 col END_INTERVAL_TIME for a25 col RESOURCE_NAME for a10 select s.SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,CURRENT_UTILIZATION,MAX_UTILIZATION,INITIAL_ALLOCATION,RESOURCE_NAME from dba_hist_snapshot s,dba_hist_resource_limit r where s.SNAP_ID=r.SNAP_ID and RESOURCE_NAME in ('processes','sessions') order by BEGIN_INTERVAL_TIME; ###############################Database size info############################### nohup sqlplus "/as sysdba" @script_name & select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk from v$session a, v$transaction b where a.saddr=b.ses_addr; select round(sum(bytes/1024/1024/1024),2) "Total Database size in Gb" from sm$ts_avail; select round(sum(bytes/1024/1024/1024),2) "Total Used space in Gb" from sm$ts_used; select round(sum(bytes/1024/1024/1024),2) "Total Free space in Gb"from sm$ts_free; select name "Controlfile locations" from v$controlfile; select member "Redo logfile locations" from v$logfile; select distinct(substr(name,1,instr(name,'/',-1,1))) location from v$datafile; select distinct(substr(name,1,instr(name,'/',-1,1))) location from v$controlfile; select distinct(substr(member,1,instr(member,'/',-1,1))) location from v$logfile; distinct partitions select distinct(substr(name,1,instr(name,'/',+1,2))) location from (select name from v$datafile union all select name from v$tempfile union all select member from v$logfile union all select name from v$controlfile ) / #######################Check wether locks are present or not##################### select * from v$lock where request>0 or block>0; #######################Find out the sessions locking a particular object############################## select SESSION_ID from v$locked_object where object_id=(select object_id from dba_objects where object_name like '&boject_name' and owner like '&owner'); *****************************************OR******************************************* select SESSION_ID from v$locked_object where object_id in (select object_id from dba_objects where object_name like '&boject_name') ###################Find out blockers######################################### select HOLDING_SESSION from dba_blockers; ####################Find out who is blocking whom############################ select WAITING_SESSION,HOLDING_SESSION from dba_waiters; ******************************************************* crontab format ******************************************************* at that interval. * * * * * command to be executed - - - - - | | | | | | | | | +----- day of week (0 - 6) (Sunday=0) | | | +------- month (1 - 12) | | +--------- day of month (1 - 31) | +----------- hour (0 - 23) +------------- min (0 - 59)%s/\/blrbscs_oracle\/product\/8.1.7\/rdbms\/admin\/orassc/\/blrbscs_ora10g\/oracle\/product\/10.2.0\/db_1\/rdbms\/admin\/orassc ########################to remove file when giving following error#################### ksh: /bin/rm: 0403-027 The parameter list is too long ls |wc -l ls >> file for i in `cat file` > do > rm -rf $i > done ###########################################SCP##################################################### scp file between 10.16.59.111(target) to 10.16.167.238(source) to get file scp oraftp@10.16.59.111:/tmp/prash.txt . scp file from 10.16.59.111(to put file) to put on another server scp p4898608_10203_GENERIC.zip ora10g@10.16.167.238:/tmp/patch ###############To check the version of database#################### select metadata from sys.kopm$; METADATA -------------------------------------------------------------------------------- 0000006001240F050B0C030C0C0504050D0609070805050505050F05050505050A05050505050405 0607080823432347081123081141B0470083000107D0130000000000000000000000000000000000 0000000000000000000000000000000000000000 check out if it is B047 then it is 64 bit check out if it is B023 then it is 32 bit *************************Purge SQL from shared_pool ******************************************** declare v_address_hash varchar2(60); begin select address||', '||hash_value into v_address_hash from v$sqlarea where sql_id = '$sqlid'; sys.dbms_shared_pool.purge(v_address_hash, 'c'); end; /****************************************** Table Partitioning Prerequistics ************************************ set verify off set echo off set trims on set linesize 1000 set heading off set pagesize 10000 set long 500000 set feedback off column XXXX format a1000 col segment_name for a30 exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', true); execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true); REM ########### Table size information of &&Table_name ###################################### spool table_detail_&&filename select 'REM ########### Table size information of &&Table_name ######################################' from dual; select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 as mb from dba_segments where segment_name ='&&Table_name' and owner='&&Owner'; spool off REM #################### Drop Referntial Contraints of table &&Table_name ############################# spool drop_ref_cons_&&filename select 'REM #################### Drop Referntial Contraints of table &&Table_name #############################' from dual; Select 'Alter table '||owner||'.'||table_name||' drop constraints '||CONSTRAINT_NAME||';' as "Output" from ( select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name from all_constraints where constraint_type='R' and r_constraint_name in (select constraint_name from all_constraints where constraint_type in ('P','U') and table_name= '&&Table_name' and owner= '&&Owner') and r_owner='&&Owner' ); spool off REM #################### Rename Contraints of table &&Table_name ############################# spool rename_cons_&&filename select 'REM #################### Rename Contraints of table &&Table_name #############################' from dual; Select 'Alter table '||owner||'.'||table_name||' rename constraint '||constraint_name||' to '||constraint_name||'_old ;' from dba_constraints where owner = '&&Owner' and table_name ='&&Table_name'; spool off REM ##################### Rename Index on Table &&Table_name ################################ spool rename_index_&&filename select 'REM ##################### Rename Index on Table &&Table_name ################################' from dual; select 'alter index '||owner ||'.'||index_name ||' rename to '||index_name||'_old ;' from dba_indexes where owner = '&&Owner' and table_name = '&&Table_name'; spool off REM #################### Drop Triggers on Table &&Table_name ############################# spool drop_trigger_&&filename select 'REM #################### Drop Triggers on Table &&Table_name #############################' from dual; select 'drop trigger '||owner ||'.'||trigger_name ||';' from dba_triggers where owner='&&Owner' and table_name='&&Table_name'; spool off REM ################ Rename table &&Table_name ####################### spool rename_table_&&filename select 'REM ################ Rename table &&Table_name #######################' from dual; select 'alter table '||owner||'.'||table_name||' rename to '||'&&Table_name'||'_old;' from dba_tables where owner='&&Owner' and table_name ='&&Table_name'; spool off REM ################ Create Index on Table &&Table_name ################################# spool create_index_&&filename select 'REM ################ Create Index on Table &&Table_name #################################' from dual; Select dbms_metadata.get_ddl('INDEX',index_name,owner) XXXX from dba_indexes where table_name= '&&Table_name' and owner= '&&Owner'; spool off REM ########### Index Noparallel of Table &&Table_name ################################# spool index_noparallel_&&filename select 'REM ########### Index Noparallel of Table &&Table_name #################################' from dual; select 'alter index '||owner ||'.'||index_name ||' noparallel;' from dba_indexes where owner = '&&Owner' and table_name = '&&Table_name'; spool off REM ############### Create Constraint on Table &&Table_name ############################ spool create_cons_&&filename select 'REM ############### Create Constraint on Table &&Table_name ############################' from dual; Select decode (constraint_type,'R',(select dbms_metadata.get_ddl('REF_CONSTRAINT',constraint_name,owner) from dual), (select dbms_metadata.get_ddl('CONSTRAINT',constraint_name,owner) from dual)) "XXXX" from ( select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name from all_constraints where table_name= '&&Table_name' and owner= '&&Owner' ); spool off REM ############## Create Referential Constraint on Table &&Table_name ############################ spool create_ref_cons_&&filename select 'REM ############## Create Referential Constraint on Table &&Table_name ############################' from dual; Select (select dbms_metadata.get_ddl('REF_CONSTRAINT',constraint_name,owner) from dual) "XXXX" from (select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name from all_constraints where constraint_type='R' and r_constraint_name in (select constraint_name from all_constraints where constraint_type in ('P','U') and table_name= '&&TABLE_NAME' and owner= '&&OWNER') and r_owner='&&OWNER' ) XXXX; spool off REM ############### Create Trigger on Table &&Table_name ################################### spool create_trigger_&&filename select 'REM ############### Create Trigger on Table &&Table_name ###################################' from dual; select (select dbms_metadata.get_ddl('TRIGGER',trigger_name,owner) from dual) "XXXX" from ( select owner,trigger_name,table_owner,table_name from dba_triggers where table_name='&&Table_name' and owner='&&Owner' ); spool off #######################RENAME FILE DYNAMICALLY######################################################################### select 'alter database rename '||''''||file_name||''''||' to '||''''||'/unicadata/oradata/data/'||substr(file_name,instr(file_name,'/',-1)+1,60)||''''|| ';' from dba_data_files; ###############################Monitor Redo Log Writer Process############################### select SUM(value) "Redo Buffer Waits" FROM v$sysstat WHERE name = 'redo log space wait time'; select SUM(value) "Redo Buffer Waits" FROM v$sysstat WHERE name = 'redo log space requests'; ###############################Monitor Hit Ratio############################### SELECT TO_CHAR(SUM(DECODE(name, 'consistent gets', value, 0)), '999,999,999,999,999,999') con , TO_CHAR(SUM(DECODE(name, 'db block gets' , value, 0)), '999,999,999,999,999,999') dbblockgets , TO_CHAR(SUM(DECODE(name, 'physical reads' , value, 0)), '999,999,999,999,999,999') physrds , ROUND( ( ( SUM(DECODE(name, 'consistent gets', Value,0)) + SUM(DECODE(name, 'db block gets', value,0)) - SUM(DECODE(name, 'physical reads', value,0)) ) / ( SUM(DECODE(name, 'consistent gets', Value,0)) + SUM(DECODE(name, 'db block gets', Value,0)) ) ) *100,2 ) Hitratio FROM v$sysstat / ------------------------------------------------------------------------------------- SET LINESIZE 180 SET PAGESIZE 66 COLUMN unix_id FORMAT a10 HEAD Username COLUMN oracle_id FORMAT a10 HEAD OracleID COLUMN os_user FORMAT a10 HEAD OS_User COLUMN sid FORMAT 999 HEAD SID COLUMN serial_id FORMAT 999999 HEAD Serial# COLUMN unix_pid FORMAT a9 HEAD UNIX_Pid COLUMN consistent_gets FORMAT 999,999,999,999,999 HEAD Cons_Gets COLUMN block_gets FORMAT 999,999,999,999,999 HEAD Block_Gets COLUMN physical_reads FORMAT 999,999,999,999,999 HEAD Phys_Reads COLUMN hit_ratio FORMAT 999.00 HEAD Hit_Ratio SELECT p.username unix_id , s.username oracle_id , s.osuser os_user , s.sid sid , s.serial# serial_id , LPAD(p.spid,7) unix_pid , sio.consistent_gets consistent_gets , sio.block_gets block_gets , sio.physical_reads physical_reads , ROUND((consistent_gets+Block_gets-Physical_reads) / (Consistent_gets+Block_gets)*100,2) hit_ratio FROM v$process p , v$session s , v$sess_io sio WHERE p.addr=s.paddr AND s.sid = sio.sid AND (sio.consistent_gets + sio.block_gets) > 0 AND s.username is not null ORDER BY hit_ratio / ############################### SGA free report ############################### COLUMN pool HEADING "Pool" COLUMN name HEADING "Name" COLUMN sgasize HEADING "Allocated" FORMAT 999,999,999 COLUMN bytes HEADING "Free" FORMAT 999,999,999 SELECT f.pool , f.name , s.sgasize , f.bytes , ROUND(f.bytes/s.sgasize*100, 2) "% Free" FROM (SELECT SUM(bytes) sgasize, pool FROM v$sgastat GROUP BY pool) s , v$sgastat f WHERE f.name = 'free memory' AND f.pool = s.pool / conn perfstat/w1z_perf----------------------------------PLANC----------------------------- set lines 155 col execs for 999,999,999 col avg_etime for 999,999.999 col avg_lio for 999,999,999.9 col begin_interval_time for a30 col node for 99999 break on plan_hash_value on startup_time skip 1 select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id = nvl('&sql_id','4dqs2k5tynk61') and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 order by 1, 2, 3 / select SQL_ID from v$sqltext where HASH_VALUE=(select SQL_HASH_VALUE from v$session where SQL_HASH_VALUE='4144196489'); select * from table(dbms_xplan.display_awr('&sql_id')); select * from table(dbms_xplan.display_cursor('&sql_id')); SELECT * FROM TABLE (dbms_xplan.display_cursor('&sql_id', 1,'ADVANCED ALLSTATS LAST +PEEKED BINDS')); SELECT * FROM TABLE(SELECT DBMS_XPLAN.DISPLAY_CURSOR(sql_id,sql_child_number,'ADVANCED +PARALLEL ALLSTATS LAST') FROM v$session WHERE sid IN (&1)); select plan_table_output from table(dbms_xplan.display); set lines 200 set pages 200 col DATATYPE_STRING for a30 col VALUE_STRING for a30 select NAME,POSITION,DATATYPE_STRING,VALUE_STRING from dba_hist_sqlbind where sql_id='&sql_id'; select SQL_ID,FETCHES,EXECUTIONS,(buffer_gets/decode(nvl(buffer_gets,0),0,1,executions)) avg_lio,CHILD_NUMBER,CPU_TIME,(elapsed_time/decode(nvl(executions,0),0,1,executions))/1000000 avg_etime from v$sql where sql_id='&sql_id'; ******************************************Plan comparision ******************************* set lines 300 set pages 500 select a.sql_id,a.executions exection_11g, b.executions exection_10g,trunc(a.buffer_gets/a.executions) gets_11g, trunc(b.buffer_gets/b.executions) gets_10g,a.plan_hash_value phv_11g,b.plan_hash_value phv_10g , (trunc(a.buffer_gets/a.executions) - trunc(b.buffer_gets/b.executions)) Diff from v$sqlarea a, SQLAREA_PROD_10G b where a.sql_id=b.sql_id and a.executions>0 and b.executions>0 and a.plan_hash_value<>b.plan_hash_value order by b.executions / ******************************************sess_wait**************************************** col username for a15 col event for a30 col module for a25 select sid,serial#,username,sql_id,event,command,last_call_et/60,module from v$session where status='ACTIVE' and username not in ('SYS','SYSTEM') and event not in ('SQL*Net message from client') order by 7; ******************************************Plan Fix **************************************** {whaibmp595_01} /ora10g/oracle/OraHome1/sqlwork $ cat create_sql_profile_awr.sql accept sql_id - prompt 'Enter value for sql_id: ' - default 'X0X0X0X0' accept plan_hash_value - prompt 'Enter value for plan_hash_value: ' accept category - prompt 'Enter value for category: ' - default 'DEFAULT' accept force_matching - prompt 'Enter value for force_matching: ' - default 'false' @sqlprof2 '&&sql_id' &&plan_hash_value '&&category' '&&force_matching' undef sql_id undef plan_hash_value undef category undef force_matching sqlprof2.sql -----> -- create sql profile from awr -- sql_id plan_hash_valeu category force_matching declare ar_profile_hints sys.sqlprof_attr; cl_sql_text clob; begin select extractvalue(value(d), '/hint') as outline_hints bulk collect into ar_profile_hints from xmltable('/*/outline_data/hint' passing ( select xmltype(other_xml) as xmlval from dba_hist_sql_plan where sql_id = '&&1' and plan_hash_value = &&2 and other_xml is not null ) ) d; select sql_text into cl_sql_text from dba_hist_sqltext where sql_id = '&&1'; dbms_sqltune.import_sql_profile( sql_text => cl_sql_text , profile => ar_profile_hints , category => '&&3' , name => 'PROFILE_&&1' -- use force_match => true -- to use CURSOR_SHARING=SIMILAR -- behaviour, i.e. match even with -- differing literals , force_match => &&4 ); end; / ******************************************Plan drop ************************************** BEGIN DBMS_SQLTUNE.drop_sql_profile ( name => 'PROFILE_c02yhk6j3g75b', ignore => TRUE); END; / ******************************************Outln ******************************************* select sql_id, plan_hash_value, HASH_VALUE, CHILD_NUMBER from v$sql where SQL_ID='c8raxa40151c0'; exec dbms_outln.CREATE_OUTLINE(2110381224,0); MLXCTGKPLUS01 (DTMIS) SQL >exec dbms_outln.CREATE_OUTLINE(2110381224,0); PL/SQL procedure successfully completed. ********************************************************************************************* select ss.snap_id, begin_interval_time, s1.sql_id, nvl(executions_delta,0) execs, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio ,ROWS_PROCESSED_TOTAL from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS,DBA_HIST_SQLtext s1 where s1.sql_id=s.sql_id and s1.DBID=ss.DBID and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 and s1.sql_id='&sql_id' order by 1, 2, 3; prompt 15 Most expensive SQL in the workload repository prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ select * from ( select stat.sql_id as sql_id, sum(elapsed_time_delta) / 1000000 as elapsed, (select to_char(substr(replace(st.sql_text,:newl,' '),1,55)) from dba_hist_sqltext st where st.dbid = stat.dbid and st.sql_id = stat.sql_id) as sql_text_fragment from dba_hist_sqlstat stat, dba_hist_sqltext text where stat.sql_id = text.sql_id and stat.dbid = text.dbid group by stat.dbid, stat.sql_id order by elapsed desc ) where ROWNUM <= 15; ***********************************Index Usage ********************************** select p.sql_id, p.object_name c1,p.operation c2,p.options c3,count(1) c4 from dba_hist_sql_plan p, dba_hist_sqlstat s where p.object_owner='CARDB' and p.operation like '%INDEX%' and p.sql_id = s.sql_id and p.object_name in (select index_name from dba_indexes where table_name='CARADT_CCIF_CCM_DUMP' and owner='CARDB'and index_name='IDX_CACD_ACTION_DT') group by p.sql_id, p.object_name, p.operation, p.options order by 5,2,3,4; You should be able to use SYS.COL_USAGE$ to work out which columns are being used in Join predicates using the following SQL: select r.name owner, o.name table , c.name column, equality_preds, equijoin_preds, nonequijoin_preds, range_preds, like_preds, null_preds, timestamp from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r where o.obj# = u.obj# and c.obj# = u.obj# and c.col# = u.intcol# and o.owner# = r.user# and (u.equijoin_preds > 0 or u.nonequijoin_preds > 0); -----------------------------------latch: cache buffers chains-------------------- select event, sql_hash_value,state, count(*) from v$session w where event='latch: cache buffers chains' and status='ACTIVE' group by sql_hash_value, state , event; select latch#, name from v$latch where latch# in (127, 122); select SQL_TEXT from v$sqltext where HASH_VALUE =(select sql_hash_value from v$session where sql_hash_value='4039843788'); select name, gets, misses, immediate_gets, immediate_misses, sleeps from v$latch order by sleeps; select sid, p1, p2, p3,p1raw, seconds_in_wait, wait_time, state from v$session_wait where event = 'latch: cache buffers chains' order by p2, p1; col OBJECT_NAME for a45 select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name from x$bh a, dba_objects b where (a.obj = b.object_id or a.obj = b.data_object_id) and a.hladdr = '&P1RAW' union select hladdr, file#, dbablk, tch, obj, null from x$bh where obj in (select obj from x$bh where hladdr = '&P1RAW' minus select object_id from dba_objects minus select data_object_id from dba_objects) and hladdr = '&P1RAW' order by 4; -----------------------------------TKPROF COMMAND ---------------- tkprof <trace file name> <output file name> sort=fchela,exeela,prsela ---------------------------------Hidden parameter ---------------------------- set lines 200 set pages 1000 col ksppinm for a40 col ksppstvl for a40 col ksppdesc for a60 SELECT ksppinm, ksppstvl, ksppdesc FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND translate(ksppinm,'_','#') like '%mutex%'; ************************** underscore param *********************** set lines 150 set pages 500 col name for a40 col value for a15 col description for a65 SELECT ksppinm name,ksppstvl value,ksppdesc description FROM x$ksppi x, x$ksppcv y WHERE (x.indx = y.indx) AND x.inst_id=userenv('instance') AND x.inst_id=y.inst_id AND ksppinm LIKE '%&media%' ORDER BY name; ------------------------------FIND PRIVILAGES -------------------------- SELECT LPAD(' ', 2*level) || granted_role "USER PRIVS" FROM ( SELECT NULL grantee, username granted_role FROM dba_users WHERE username LIKE UPPER('%&uname%') UNION SELECT grantee, granted_role FROM dba_role_privs UNION SELECT grantee, privilege FROM dba_sys_privs) START WITH grantee IS NULL CONNECT BY grantee = prior granted_role; --> To find out library cache locks. ##################################################### SELECT s.sid, kglpnmod "Mode", kglpnreq "Req", SPID "OS Process" FROM v$session_wait w, x$kglpn p, v$session s ,v$process o WHERE p.kglpnuse=s.saddr AND kglpnhdl=w.p1raw and w.event like '%library cache lock%' and s.paddr=o.addr / select 'host kill -9 '||spid from v$process where addr in (select paddr from v$session where sid in (select sid from v$session_wait where event='library cache pin')); select /*+ ordered */ w1.sid waiting_session, h1.sid holding_session, w.kgllktype lock_or_pin, w.kgllkhdl address, decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_held, decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_requested from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1 where (((h.kgllkmod != 0) and (h.kgllkmod != 1) and ((h.kgllkreq = 0) or (h.kgllkreq = 1))) and (((w.kgllkmod = 0) or (w.kgllkmod= 1)) and ((w.kgllkreq != 0) and (w.kgllkreq != 1)))) and w.kgllktype = h.kgllktype and w.kgllkhdl = h.kgllkhdl and w.kgllkuse = w1.saddr and h.kgllkuse = h1.saddr; select * from x$kgllk where KGLLKSES ='saddr_from_v$session'; SELECT s.sid, case kglpnmod when 2 then 'S' when 3 then 'X' end "Mode", case kglpnreq when 2 then 'S' when 3 then 'X' end "Req" FROM x$kglpn p, v$session s AND kglpnhdl='C00000021D3DF508'; select a.username,a.sid, a.serial#,a.process,b.pid,a.program,b.spid, a.status,a.last_call_et from v$session a , v$process b where a.paddr(+)=b.addr and a.sid in ( select sid from v$session_Wait where event='db file scattered read'); ######################################################### enq-TX Troubleshooting: For which SQL currently is waiting on: select sid, sql_text from v$session s, v$sql q where sid in (select sid from v$session where state in ('WAITING') and wait_class != 'Idle' and event='enq: TX - row lock contention' and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id)); The blocking session is: select blocking_session, sid, serial#, wait_class, seconds_in_wait,username from v$session where blocking_session is not NULL order by blocking_session; #####################################cursor: pin S wait on X########################### On 32 bit platforms 4 bytes are used, so the top 2 bytes are the holding session Id (if the mutex is held X) and the bottom 2 bytes are the ref count (if the mutex is held S). select p1, p2raw, count(*) from v$session where event = ëcursor: pin S wait on Xà and wait_time = 0 group by p1, p2raw; p1 = The mutex Id This has the same definition as v$mutex_sleep_history.mutex_identifier p2raw = Holding Session Id | Ref Count The most significant bytes always store the Holding Session Id (Holding SId). The least significant bytes always store the Ref Count. select p2raw,to_number(substr(to_char(rawtohex(p2raw)),1,8),'XXXXXXXX') sid from v$session where event = 'cursor: pin S wait on X'; select sid,serial#, SQL_ID, ACTION, BLOCKING_SESSION, BLOCKING_SESSION_STATUS, EVENT from v$session where SID=31; select sid,serial#,SQL_ID,BLOCKING_SESSION,BLOCKING_SESSION_STATUS,EVENT from v$session where event ='cursor: pin S wait on X' ; ******************************************* MUTEX Findings ********************************************** set lines 150 col TIME for a10 col OBJECT for a30 select to_char(sysdate, 'HH:MI:SS') time, KGLNAHSH hash, sum(sleeps) sleeps,location,MUTEX_TYPE, KGLNAOBJ as object from x$kglob , v$mutex_sleep_history where kglnahsh=mutex_identifier group by KGLNAOBJ,KGLNAHSH,location,MUTEX_TYPE order by sleeps desc; select s.sid, t.sql_text from v$session s, v$sql t where s.event like '%mutex%' and t.sql_id = s.sql_id; select KGLNAOBJ, KGLNAOWN, KGLHDNSP, KGLOBTYP, KGLNAHSV, KGLOBT23, KGLOBT24 from x$kglob where KGLOBT23 > 100000 or KGLOBT24 > 100000 order by KGLOBT24; select p2raw from v$session where event = 'cursor: pin S wait on X'; SELECT p2raw ,to_number(substr(to_char(rawtohex(p2raw)), 1, 8), 'XXXXXXXX') sid FROM v$session WHERE event = 'cursor: pin S wait on X'; OR SELECT decode(trunc(&&P2/4294967296), 0,trunc(&&P2/65536), trunc(&&P2/4294967296)) SID_HOLDING_MUTEX FROM dual; SELECT decode(trunc(&&P3/4294967296), 0,trunc(&&P3/65536), trunc(&&P3/4294967296)) LOCATION_ID FROM dual; SELECT MUTEX_TYPE, LOCATION FROM x$mutex_sleep WHERE mutex_type like 'Cursor Pin%' and location_id=&&LOCATION_ID; select sid,serial#, SQL_ID, username, ACTION, BLOCKING_SESSION, BLOCKING_SESSION_STATUS, EVENT ,status from v$session where SID=31;********************************** CPU Consuming Queries ************************************** col USERNAME for a10 col spid for a10 col sql_text for a30 set lines 200 pages 5000 SELECT /*+ ordered */ p.spid, s.sid, s.serial#, s.username, TO_CHAR(s.logon_time, 'mm-dd-yyyy hh24:mi') logon_time, st.value, s.sql_id, sq.sql_text FROM v$statname sn, v$sesstat st, v$process p, v$session s, v$sql sq WHERE s.paddr=p.addr AND s.sql_hash_value = sq.hash_value and s.sql_Address = sq.address AND s.sid = st.sid AND st.STATISTIC# = sn.statistic# AND sn.NAME = 'CPU used by this session' --AND p.spid = &osPID -- parameter to restrict for a specific PID AND s.status = 'ACTIVE' ORDER BY st.value desc; ####################################################################################### 1- cd $ORACLE_HOME/bin 2- chmod 4550 oradism 3- chmod g+s oradism 4- chown root:dba oradism 5- Bounce the database The permissions for oradism must be 4550 with the setid for the group. (-r-sr-s--- 1 root dba 12912 Feb 16 2005 oradism) ORACLE FILE PERMISSION:- sqlplus "/as sysdba" chmod u+s oracle chmod g+s oracle ================================LOG SWITCH TIME======================== SELECT ROUND(AVG(1440 * (b.first_time - a.first_time)), 0) "Log switch time - minutes" FROM v$loghist a, v$log b WHERE b.sequence# = a.sequence# + 1 AND a.sequence# = (SELECT MAX(sequence#) FROM v$loghist) ORDER BY a.sequence#; ************************************************** Database TPS ************************************************* compute avg of average on report break on report col BEGIN_INTERVAL_TIME for a30 col METRIC_NAME for a30 select BEGIN_INTERVAL_TIME, METRIC_NAME, AVERAGE from dba_hist_snapshot a, dba_hist_sysmetric_summary b where a.snap_id=b.snap_id and trunc(BEGIN_INTERVAL_TIME)>trunc(sysdate-10) and metric_name='User Transaction Per Sec' order by 1; ********************************************* Tablespace High Water Mark ***************************************** select a.tablespace_name, a.file_name,(b.maximum+c.blocks-1)*d.db_block_size highwater from dba_data_files a ,(select file_id,max(block_id) maximum from dba_extents group by file_id) b ,dba_extents c,(select value db_block_size from v$parameter where name='db_block_size') d where a.file_id = b.file_id and c.file_id = b.file_id and c.block_id = b.maximum and a.tablespace_name='&tablespace_name'; ######################################Fragament################## select table_name,((round((blocks*8),2))/1024) "Physical Size (MB)" , ((round((num_rows*avg_row_len/1024),2))/1024) "Actual Size (MB)", (((round((blocks*8),2))/1024)-((round((num_rows*avg_row_len/1024),2))/1024)) "Space Wasted" from DBA_tables where owner='ICICI_RB_ED' and table_name in ('ACCOUNT_HOLDING', 'CCS_AUDIT_LOG', 'CCS_CURRENT_INVESTMENT', 'CCS_GRIP_FAMILY', 'CCS_ORGANISATION_HIERARCHY', 'CCS_PLANNING_MASTER', 'CCS_POSITION_BRIDGE', 'CCS_POSITION_HIERARCHY', 'CCS_SMSEMAIL_AUTOACK_HISTORY', 'CCS_SUMMARY_EQUITY_TRANS', 'CCS_SUMMARY_MF_TRANS', 'CCS__BRANCH_PINCODE_PRODUCT', 'CONTACT', 'CONTACT_COMPANY_ASSETS', 'CONTACT_COMPANY_LIABILITIES', 'CONTACT_EXPENSES', 'CONTACT_INCOME', 'EMPLOYEE', 'GOAL', 'GRIP_CASHFLOW', 'INSURANCE_CASHFLOW', 'LEAD_', 'OFFLINE_REVENUE', 'RN_APPOINTMENTS'); ##############################GLOGIN ######################### set termout off host echo "define hname=`hostname`" > /tmp/icicilogin.startup start /tmp/icicilogin.startup host rm /tmp/icicilogin.startup define gname=. column name NEW_VALUE gname select name||'-LIVE' name from v$database; set termout on set sqlprompt '&hname (&gname) SQL >' ********************************WINDOWS GLOGIN ********************* set termout off define hname='MLXRTGSFB01' define gname=. column global_name NEW_VALUE gname select global_name from global_name; set termout on set sqlprompt '&hname (&gname) SQL >' ################################CLIENT TRACE ######################## TRACE_UNIQUE_CLIENT = ON TRACE_LEVEL_CLIENT = 16 TRACE_DIRECTORY_CLIENT = C:\temp TRACE_FILE_CLIENT = SQLNetTrace TRACE_TIMESTAMP_CLIENT = ON ************************************************* LISTENER TRACE:- TRACE_LEVEL_<Listener Name> = 16 TRACE_FILE_<Listener Name> = <File Name> TRACE_DIRECTORY_<Listener Name> = <Directory> TRACE_TIMESTAMP_<Listener Name> = ON TRACE_FILELEN_<Listener Name> = <Size in KiloBytes> TRACE_FILENO_<Listener Name> = <Number> $lsnrctl reload Stop listener tracing, run set trc_level 0 via lsnrctl set current_listener <listener name> in listener control set trc_level 0 When TNS listener tracing is required to be re-enabled. run set trc_level 16 via lsnrctl. LSNRCTL> set trc_level 16 ***********OR ************* TRACE_LEVEL_LISTENER_1659=16 TRACE_FILELEN_LISTENER_1659 = 1000 TRACE_FILENO_LISTENER_1659 = 4 ************************************************* (SID_NAME = m2i) (ENVS='EPC_DISABLED=TRUE')) TRACE_LEVEL_LISTENER_1659=16 TRACE_FILELEN_LISTENER_1659 = 1000 TRACE_FILENO_LISTENER_1659 = 4 Place these parameters in the sqlnet.ora file on the client node. TRACE_LEVEL_CLIENT=16 TRACE_FILE_CLIENT=CLIENT TRACE_DIRECTORY_CLIENT=full path to directory where you want the trace file created. TRACE_TIMESTAMP_CLIENT=on TRACE_UNIQUE_CLIENT=on DIAG_ADR_ENABLED=offvmstat ***Fri Jan 28 12:50:36 EST 2005 procs memory page disk faults cpu r b w swap free re mf pi po fr de sr dd f0 s0 in sy cs us sy id 0 0 0 1761344 1246520 1 6 0 0 0 0 0 2 0 0 0 380 1364 900 4 1 95 0 0 0 1643920 1086776 331 1485 8 16 16 0 0 31 0 0 0 447 4966 1315 15 31 54 0 0 0 1643872 1086728 6 0 0 0 0 0 0 0 0 0 0 389 1472 932 0 0 100 Field Descriptions : PROCS r Number of processes that are in a wait state and basically not doing anything but waiting to run b Number of processes that were in sleep mode and were interrupted since the last update w Number of processes that have been swapped out by mm and vm subsystems and have yet to run MEMORY swap The amount of swap space currently available free The size of the free list PAGE re page reclaims mf minor faults pi kilobytes paged in po kilobytes paged out fr kilobytes freed de anticipated short-term memory shortfall (Kbytes) sr pages scanned by clock algorithm DISK Bi Disk blocks sent to disk devices in blocks per second FAULTS In Interrupts per second, including the CPU clocks Sy System calls Cs Context switches per second within the kernel CPU Us Percentage of CPU cycles spent on user processes Sy Percentage of CPU cycles spent on system processes Id Percentage of unused CPU cycles or idle time when the CPU is basically doing nothing . What to look for : -Large run queue. Adrian Cockcroft defines anything over 4 processes per CPU on the run queue as the threshold for CPU saturation. This is certainly a problem if this last for any long period of time. -CPU utilization. The amount of time spent running system code should not exceed 30% especially if idle time is close to 0%. -A combination of large run queue with no idle CPU is an indication the system has insufficient CPU capacity. -Memory bottlenecks are determined by the scan rate (sr) . The scan rate is the pages scanned by the clock algorithm per second. If the scan rate (sr) is continuously over 200 pages per second then there is a memory shortage. -Disk problems may be identified if the number of processes blocked exceeds the number of processes on run queue.**********************************Block corruption ******************************************************** select /*+ RULE*/ distinct owner,segment_name,segment_type from dba_extents, v$database_block_corruption where v$database_block_corruption.file#=file_id and v$database_block_corruption. block# between block_id and dba_extents.block_id+dba_extents.blocks-1; column corruption_change# format 99999999999999999999 select * from v$database_block_corruption order by file#, block# ; SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file# , greatest(e.block_id, c.block#) corr_start_block# , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block# , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) - greatest(e.block_id, c.block#) + 1 blocks_corrupted , null description FROM dba_extents e, v$database_block_corruption c WHERE e.file_id = c.file# AND e.block_id <= c.block# + c.blocks - 1 AND e.block_id + e.blocks - 1 >= c.block# UNION SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file# , header_block corr_start_block# , header_block corr_end_block# , 1 blocks_corrupted , 'Segment Header' description FROM dba_segments s, v$database_block_corruption c WHERE s.header_file = c.file# AND s.header_block between c.block# and c.block# + c.blocks - 1 UNION SELECT null owner, null segment_type, null segment_name, null partition_name, c.file# , greatest(f.block_id, c.block#) corr_start_block# , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block# , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) - greatest(f.block_id, c.block#) + 1 blocks_corrupted , 'Free Block' description FROM dba_free_space f, v$database_block_corruption c WHERE f.file_id = c.file# AND f.block_id <= c.block# + c.blocks - 1 AND f.block_id + f.blocks - 1 >= c.block# order by file#, corr_start_block#; SOLUTION:- ---------- 1.- If you don't have index DDL you can get with: SQL> set long 6000 SQL> select dbms_metadata.get_ddl('INDEX','<INDEX_NAME>','<OWNER>') from dual; 2.-Determine if the index supports a CONSTRAINT: Eg: SELECT owner, constraint_name, constraint_type, table_name FROM dba_constraints WHERE owner='&OWNER' AND constraint_name='&INDEX_NAME'; Possible values for CONSTRAINT_TYPE are: P The index supports a primary key constraint. U The index supports a unique constraint. If the INDEX supports a PRIMARY KEY constraint (type "P") then check if the primary key is referenced by any foreign key constraints: Eg:- SELECT owner, constraint_name, constraint_type, table_name FROM dba_constraints WHERE r_owner='&OWNER' AND r_constraint_name='&INDEX_NAME'; 3.- If the index has dependent FOREIGN KEY constraints then you will need to do something like this: - ALTER TABLE <child_table> DISABLE CONSTRAINT <fk_constraint>; for each foreign key - Rebuild the primary key using ALTER TABLE <table> DISABLE CONSTRAINT <pk_constraint>; DROP INDEX <index_name>; CREATE INDEX <index_name> .. with appropriate storage clause ( From step 1 ) ALTER TABLE <table> ENABLE CONSTRAINT <pk_constraint>; - Enable the foreign key constraints ALTER TABLE <child_table> ENABLE CONSTRAINT <fk_constraint>; *********************************Insert data ********************************* BEGIN FOR i IN 1..1000000000 LOOP INSERT INTO scott.s VALUES(i,'x'); commit; END LOOP; END; / *******************************Large Pool Size ******************************* For disk channels LARGE_POOL_SIZE = (No:channels * (16 + 4)) +16 Mb For tape channels: LARGE_POOL_SIZE = (No:channels * (16 + 4(<tape blksize>)) +16 Mb ****************************************************************************** sqlplus -prelim '/ as sysdba' *************************Show partition sizes for the specified table ******************** set pages 999 lines 100 col tablespace_name format a20 col num_rows format 999,999,999 select p.partition_name , p.tablespace_name , p.num_rows , ceil(s.bytes / 1024 / 1204) mb from dba_tab_partitions p , dba_segments s where p.table_owner = s.owner and p.partition_name = s.partition_name and p.table_name = s.segment_name and p.table_owner = '&owner' and p.table_name = '&table_name' order by partition_position / *************************************************************** Show all used features select name , detected_usages from dba_feature_usage_statistics where detected_usages > 0 / *******************************Grant to valid object ********************* grant execute on dbms_sql to public; grant execute on dbms_lob to public; grant execute on utl_file to public; grant execute on utl_file to public; grant execute on utl_http to public; grant execute on utl_tcp to public; grant execute on utl_smtp to public; grant execute on utl_file_dir to public; >>>>Does not exist grant select on all_users to public; grant select on all_tab_privs to public; grant select on all_db_links to public; grant select on all_source to public; ****************************************Wait Class *********************************** select event "Event Name", waits "Waits", timeouts "Timeouts", time "Wait Time (s)", avgwait "Avg Wait (ms)", waitclass "Wait Class" from (select e.event_name event , e.total_waits - nvl(b.total_waits,0) waits , e.total_timeouts - nvl(b.total_timeouts,0) timeouts , (e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000 time , decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0)) ) avgwait , e.wait_class waitclass from dba_hist_system_event b , dba_hist_system_event e where b.snap_id(+) = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid(+) = &pDbId and e.dbid = &pDbId and b.instance_number(+) = &pInstNum and e.instance_number = &pInstNum and b.event_id(+) = e.event_id and e.total_waits > nvl(b.total_waits,0) and e.wait_class <> 'Idle' ) order by time desc, waits desc ************************************************* Resize ACFS filesystem ************************************** ALTER DISKGROUP ACFSDATA ADD DISK '/dev/rhdisk21'; ALTER DISKGROUP ACFSDATA ADD DISK '/dev/rhdisk22'; ALTER DISKGROUP ACFSDATA ADD DISK '/dev/rhdisk23'; ALTER DISKGROUP ACFSDATA ADD DISK '/dev/rhdisk24'; Run through ROOT user:- # /sbin/acfsutil size +5G /ACFSDATA acfsutil size: new file system size: 16106127360 (15360MB) /sbin/helpers/acfs/mount -o all For Aix :- mount -V acfs -o rw /dev/asm/cmsacfsvol-30 /CMSACFS For Solaris:- mount -F acfs -o rw /dev/asm/cmsacfsvol-30 /CMSACFS *****************************************************dbms_stats.gather_schema_stats *********************** We can use below procedure to check state statistics for perticular schema. SET SERVEROUTPUT ON declare mystaleobjs dbms_stats.objecttab; begin -- check whether there is any stale objects dbms_stats.gather_schema_stats(ownname=>'FINNONELEA', options=>'LIST STALE',objlist=> mystaleobjs); for i in 1 .. mystaleobjs.count loop dbms_output.put_line(mystaleobjs(i).objname||' '||mystaleobjs(i).ObjType||' '||mystaleobjs(i).partname); end loop; end; / This will just provide list of table having state stats and not gather stats. Refer below DOC. How to list the objects having stale statistics (Use dbms_stats.gather_schema_stats options=>'LIST STALE') [ID 457666.1] Prior to Oracle11g, the staleness threshold is hardcoded at 10%. This means that an object is considered stale if the number of rows inserted, updated or deleted since the last statistics gathering time is more than 10% of the number of rows. There is no way to modify this value in Oracle8i, Oracle9i and Oracle10g. Starting with Oracle11g, the staleness threshold can be set using the STALE_PERCENT statistics preference. This can be set globally using DBMS_STATS.SET_GLOBAL_PREFS or at the table level using DBMS_STATS.SET_TABLE_PREFS. We can modify stale_stats threshold using below command. execute dbms_stats.set_global_prefs ('STALE_PERCENT', '13'); OR execute dbms_stats.set_table_prefs ('OWNNAME', 'TABNAME', ëSTALE_PERCENT', '55'); Refer below DOC. Modifying the 10% Staleness Threshold for DBMS_STATS Automatic Statistics Gathering [ID 390737.1] CREATE OR REPLACE PROCEDURE D1 AS -- VARIABLE DECARATION TOWNER VARCHAR2(30); TNAME VARCHAR2(50); l_file UTL_FILE.FILE_TYPE; l_location VARCHAR2(100) := 'DIR_ANN'; l_filename VARCHAR2(100) ; dateval varchar2(20); dateid varchar2(50); mystaleobjs dbms_stats.objecttab; SIZEMB number(10,5); -- CURSOR DECLARATION CURSOR C1 IS SELECT OWNER, OBJNAME, SIZEMB FROM STALESTATS ORDER BY SIZEMB DESC; BEGIN select to_char(sysdate,'DD_MON_YYYY') into dateval from dual; select 'STATS_'||to_char(sysdate,'DD_MON_YYYY') into dateid from dual; l_filename := 'CXPSPROD_CXPSADM_TABLES.sql'; l_file := utl_file.fopen(l_location,l_filename,'w'); utl_file.new_line(l_file ); UTL_FILE.PUT_LINE(l_file,'set echo on'); UTL_FILE.PUT_LINE(l_file,'set time on'); UTL_FILE.PUT_LINE(l_file,'set timing on'); UTL_FILE.NEW_LINE(l_file ); UTL_FILE.PUT_LINE(l_file,'SPOOL /oracle10g/ora10g/maint/analyze/BIMONTHLY/logs/CXPSPROD_CXPSADM_TABLES_'||dateval||'.log'); UTL_FILE.NEW_LINE(l_file ); UTL_FILE.PUT_LINE(l_file,'alter session set resumable_timeout=3600;'); UTL_FILE.PUT_LINE(l_file,'alter session set "_px_max_granules_per_slave"=3000;'); UTL_FILE.PUT_LINE(l_file,'alter session set "_px_index_sampling"=3000;'); UTL_FILE.PUT_LINE(l_file,'alter session set "_px_dynamic_sample_size"=3000;'); UTL_FILE.PUT_LINE(l_file,'alter session set db_file_multiblock_read_count=128;'); UTL_FILE.NEW_LINE(l_file ); dbms_stats.gather_schema_stats(ownname=>'CXPSADM', options=>'LIST STALE',objlist=> mystaleobjs); FOR i IN 1 .. mystaleobjs.count LOOP SELECT A.OWNER,A.SEGMENT_NAME,A.BYTES/1024/1024 MB into TOWNER,TNAME,SIZEMB FROM DBA_SEGMENTS A, DBA_TABLES B WHERE A.OWNER = B.OWNER AND A.SEGMENT_NAME = B.TABLE_NAME AND A.OWNER = mystaleobjs(i).ownname AND A.SEGMENT_TYPE = 'TABLE' AND A.SEGMENT_NAME = mystaleobjs(i).objname; if TOWNER is not null then insert into STALESTATS values(TOWNER,TNAME,SIZEMB); end if; END LOOP; FOR i IN C1 LOOP UTL_FILE.PUT_LINE(l_file,'REM --- ANALYZE OF TABLE ------>'||i.objname||' OF SIZE '||i.sizemb||' MB' ); UTL_FILE.PUT_LINE(l_file,'exec DBMS_STATS.GATHER_TABLE_STATS('||''''||i.owner||''''||','||''''||i.objname||''''||', DEGREE=>12, estimate_percent=>100,CASCADE=>TRUE,method_opt=>''FOR ALL COLUMNS SIZE REPEAT'');'); utl_file.new_line(l_file ); END LOOP; UTL_FILE.PUT_LINE(l_file,'SPOOL OFF'); UTL_FILE.PUT_LINE(l_file,'EXIT'); UTL_FILE.FCLOSE(l_file); execute immediate 'truncate table STALESTATS'; END; /********************************************DBMS_SHARED_POOL*********************************** col Object_Name for a45 SELECT substr(owner,1,10)||'.'||substr(name,1,35) "Object_Name", ' Type: '||substr(type,1,12)|| ' size: '||sharable_mem || ' execs: '||executions|| ' loads: '||loads|| ' Kept: '||kept FROM v$db_object_cache WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE') AND executions > 0 and OWNER='DBTRACK' ORDER BY executions desc, loads desc, sharable_mem desc; ********************************************FLASHBACK TABLE*********************************** SELECT TIMESTAMP_TO_SCN(sysdate) FROM oms.contact_master; select TIMESTAMP_TO_SCN(sysdate -60/1440) from oms.contact_master; flashback table oms.contact_master to scn 8381691680101; **************************** segment that experienced the most changes *************************************** set lines 150 pages 5000 col object_name for a30 break on snap_time SELECT to_char(begin_interval_time,'YYYY_MM_DD') snap_time, dhsso.object_name, sum(db_block_changes_delta) db_block_changes_delta FROM dba_hist_seg_stat dhss, dba_hist_seg_stat_obj dhsso, dba_hist_snapshot dhs WHERE dhs.snap_id = dhss.snap_id AND dhs.instance_number = dhss.instance_number AND dhss.obj# = dhsso.obj# AND dhss.dataobj# = dhsso.dataobj# AND begin_interval_time BETWEEN to_date('2012_03_26 09','YYYY_MM_DD HH24') AND to_date('2012_03_28 16','YYYY_MM_DD HH24') AND dhsso.owner='&ownername' GROUP BY to_char(begin_interval_time,'YYYY_MM_DD'),dhsso.object_name having sum(db_block_changes_delta) > 1000000 order by 1,3; ***************************segment growth ******************************************* column "Percent of Total Disk Usage" justify right format 999.99 column "Space Used (MB)" justify right format 9,999,999.99 column "Total Object Size (MB)" justify right format 9,999,999.99 set linesize 150 set pages 80 set feedback off select * from (select to_char(end_interval_time, 'MM/DD/YY') mydate, sum(space_used_delta) / 1024 / 1024 "Space used (MB)", avg(c.bytes) / 1024 / 1024 "Total Object Size (MB)", round(sum(space_used_delta) / sum(c.bytes) * 100, 2) "Percent of Total Disk Usage" from dba_hist_snapshot sn, dba_hist_seg_stat a, dba_objects b, dba_segments c where begin_interval_time > trunc(sysdate) - &days_back and sn.snap_id = a.snap_id and b.object_id = a.obj# and b.owner = c.owner and b.object_name = c.segment_name and c.segment_name = '&segment_name' group by to_char(end_interval_time, 'MM/DD/YY')) order by to_date(mydate, 'MM/DD/YY'); **************************** Partition Adviser *************************************** select a.object_name, sum(b.EXECUTIONS) from v$sqlarea b, v$sql_plan a where a.hash_value = b.hash_value and object_name in (select index_name from dba_indexes where table_name = '&tname' ) group by a.object_name order by 2; ************************************find database is 32/64 bit************************** SQL> select distinct(length(addr)*4) "Word Size" from v$process; If 64 Bit then output will be: Word Size ---------- 64 If 32 Bit the output will be : Word Size ---------- 32 For 32 Bit the ADDR column type in v$process will be RAW(4) and for 64 Bit the clomn type will be RAW(8) ************************************************************************************************************ Use below queries to find archived time difference between Production and standby database. Query 1 col "Time LAG MM:SS" for a20 select a.sequence#, a.completion_time "HYD Archived Time", b.completion_time "JPR Archived Time", to_char(to_date('00:00','MI:SS')+(b.completion_time-a.completion_time),'MI:SS') "Time LAG MM:SS" from (select sequence#, completion_time from v$archived_log where dest_id=1 and archived='YES' and trunc(completion_time)>=trunc(sysdate-1)) a, (select sequence#, completion_time from v$archived_log where dest_id=2 and archived='YES' and trunc(completion_time)>=trunc(sysdate-1)) b where a.sequence#=b.sequence# and b.completion_time>a.completion_time order by 1; Query 2 col "Time LAG MM:SS" for a20 select a.sequence#, a.completion_time "HYD Archived Time", b.completion_time "JPR Archived Time", floor((((b.completion_time-a.completion_time)*24*60*60) - floor(((b.completion_time-a.completion_time)*24*60*60)/3600)*3600)/60) ||':'|| round((((b.completion_time-a.completion_time)*24*60*60) - floor(((b.completion_time-a.completion_time)*24*60*60)/3600)*3600 - (floor((((b.completion_time-a.completion_time)*24*60*60) - floor(((b.completion_time-a.completion_time)*24*60*60)/3600)*3600)/60)*60) )) "Time LAG MM:SS" from (select sequence#, completion_time from v$archived_log where dest_id=1 and archived='YES' and trunc(completion_time)>=trunc(sysdate-1)) a, (select sequence#, completion_time from v$archived_log where dest_id=2 and archived='YES' and trunc(completion_time)>=trunc(sysdate-1)) b where a.sequence#=b.sequence# and b.completion_time>a.completion_time order by 1; **********************************************Package ******************************** lslpp -L bos.adt.base bos.adt.lib bos.adt.libm bos.perf.libperfstat bos.perf.perfstat \ bos.perf.proctools rsct.basic.rte rsct.compat.clients.rte xlC.aix61.rte xlC.rte ********************************************************** High Physical reads ******************************************************* SELECT sql_text, disk_reads FROM (SELECT sql_text, buffer_gets, disk_reads, sorts, cpu_time/1000000 cpu, rows_processed, elapsed_time FROM v$sqlstats ORDER BY disk_reads DESC) WHERE rownum <= 5; SELECT schema, sql_text, disk_reads, round(cpu,2) FROM (SELECT s.parsing_schema_name schema, t.sql_id, t.sql_text, t.disk_reads, t.sorts, t.cpu_time/1000000 cpu, t.rows_processed, t.elapsed_time FROM v$sqlstats t join v$sql s on(t.sql_id = s.sql_id) WHERE parsing_schema_name = 'SCOTT' ORDER BY disk_reads DESC) WHERE rownum <= 5; ********************************************************** SQL Monitoring ********************************************************** SELECT sid, buffer_gets, disk_reads, round(cpu_time/1000000,1) cpu_seconds FROM v$sql_monitor WHERE SID=100 AND status = 'EXECUTING'; SELECT * FROM ( SELECT sid, buffer_gets, disk_reads, round(cpu_time/1000000,1) cpu_seconds FROM v$sql_monitor ORDER BY cpu_time desc) WHERE rownum <= 5; SELECT * FROM ( SELECT sql_id, to_char(sql_exec_start,'yyyy-mm-dd:hh24:mi:ss') sql_exec_start, sql_exec_id, sum(buffer_gets) buffer_gets, sum(disk_reads) disk_reads, round(sum(cpu_time/1000000),1) cpu_secs FROM v$sql_monitor WHERE sql_id = 'fcg00hyh7qbpz' GROUP BY sql_id, sql_exec_start, sql_exec_id ORDER BY 6 desc) WHERE rownum <= 5; SELECT sql_id, sql_exec_start, sql_exec_id, px_server# px#, disk_reads, cpu_time/1000000 cpu_secs, buffer_gets FROM v$sql_monitor WHERE status = 'EXECUTING' ORDER BY px_server#; SELECT sql_id,sql_exec_start, sql_exec_id, sum(buffer_gets) buffer_gets, sum(disk_reads) disk_reads, round(sum(cpu_time/1000000),1) cpu_seconds FROM v$sql_monitor WHERE sql_id = '0gzf8010xdasr' GROUP BY sql_id, sql_exec_start, sql_exec_id; SELECT sql_id, sum(buffer_gets) buffer_gets, sum(disk_reads) disk_reads, round(sum(cpu_time/1000000),1) cpu_seconds FROM v$sql_monitor WHERE sql_id = '0gzf8010xdasr' GROUP BY sql_id; *********************************************** Report SQL Monitor using dbms_sqltune ****************************************** column operation format a25 column plan_line_id format 9999 heading 'LINE' column plan_options format a10 heading 'OPTIONS' column status format a10 column output_rows heading 'ROWS' break on sid on sql_id on status SELECT sid, sql_id, status, plan_line_id, plan_operation || ' ' || plan_options operation, output_rows FROM v$sql_plan_monitor WHERE status not like '%DONE%' ORDER BY 1,4; set pages 9999 set long 1000000 SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id=> '36bdwxutr5n75',type=>'HTML') FROM dual; SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR (sql_id=>'36bdwxutr5n75',event_detail=>'NO',report_level=>'BASIC') FROM dual; ************************************** Statement soft parsing using variable in execute immediate ******************************* BEGIN FOR i IN 100..206 LOOP execute immediate 'UPDATE employees SET salary=salary*1.03 WHERE employee_id = :empno' USING i; END LOOP; COMMIT; END; *************************************************** Get UNDO segments details *************************************************** select sum(d.bytes) "undo" from v$datafile d, v$tablespace t, dba_tablespaces s where s.contents = 'UNDO' and s.status = 'ONLINE' and t.name = s.tablespace_name and d.ts# = t.ts#; select max(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC" FROM v$undostat; select to_char(begin_time,'hh24:mi:ss') BEGIN_TIME, to_char(end_time,'hh24:mi:ss') END_TIME, maxquerylen,nospaceerrcnt,tuned_undoretention from v$undostat; select s.sql_text from v$sql s, v$undostat u where u.maxqueryid=s.sql_id; select s.sid, t.name, s.value from v$sesstat s, v$statname t where s.statistic# = t.statistic# and t.name = 'undo change vector size' order by s.value desc; select sql.sql_text sql_text, t.USED_UREC Records, t.USED_UBLK Blocks, (t.USED_UBLK*8192/1024) KBytes from v$transaction t, v$session s, v$sql sql where t.addr = s.taddr and s.sql_id = sql.sql_id and s.username ='&USERNAME'; ************************************************ Temp segment details and sort usage ********************************************* select * from (select a.tablespace_name, sum(a.bytes/1024/1024) allocated_mb from dba_temp_files a where a.tablespace_name = upper('&&temp_tsname') group by a.tablespace_name) x, (select sum(b.bytes_used/1024/1024) used_mb, sum(b.bytes_free/1024/1024) free_mb from v$temp_space_header b where b.tablespace_name=upper('&&temp_tsname') group by b.tablespace_name); select s.sid || ',' || s.serial# sid_serial, s.username, o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace, o.sqladdr address, h.hash_value, h.sql_text from v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t where o.session_addr = s.saddr and o.sqladdr = h.address (+) and o.tablespace = t.tablespace_name order by s.sid;select s.sid || ',' || s.serial# sid_serial, s.username, s.osuser, p.spid, s.module,s.program, sum (o.blocks) * t.block_size / 1024 / 1024 mb_used, o.tablespace, count(*) sorts from v$sort_usage o, v$session s, dba_tablespaces t, v$process p where o.session_addr = s.saddr and s.paddr = p.addr and o.tablespace = t.tablespace_name group by s.sid, s.serial#, s.username, s.osuser, p.spid, s.module, s.program, t.block_size, o.tablespace order by sid_serial; *********************************************** Open cursor and session cached cursor ********************************************* select a.value, s.username,s.sid,s.serial#,s.program,s.inst_id from gv$sesstat a,gv$statname b,gv$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name='opened cursors current' select saddr, sid, user_name, address,hash_value,sql_id, sql_text from gv$open_cursor where sid in (select sid from v$open_cursor group by sid having count(*) > &threshold); select sql_id,substr(sql_text,1,50) sql_text, count(*) from gv$open_cursor where sid=81 group by sql_id,substr(sql_text,1,50) order by sql_id; select max(value) from v$sesstat where statistic# in (select statistic# from v$statname where name = 'session cursor cache count'); select a.value,s.username,s.sid,s.serial# from v$sesstat a, v$statname b,v$session s where a.statistic#=b.statistic# and s.sid=a.sid and b.name='session cursor cache count'; ********************************************** script to identify filesystem space usage ******************************************* #!/bin/bash mntlist="/orahome /oraredo1 /oraarch1 /ora01 /oradump01 /" for ml in $mntlist do echo $ml usedSpc=$(df -h $ml | awk '{print $5}' | grep -v capacity | cut -d "%" -f1 -) BOX=$(uname -a | awk '{print $2}') # case $usedSpc in [0-9]) arcStat="relax, lots of disk space: $usedSpc" ;; [1-7][0-9]) arcStat="disk space okay: $usedSpc" ;; [8][0-9]) arcStat="space getting low: $usedSpc" ;; [9][0-9]) arcStat="warning, running out of space: $usedSpc" echo $arcStat $ml | mailx -s "space on: $BOX" dkuhn@oracle.com ;; [1][0][0]) arcStat="update resume, no space left: $usedSpc" echo $arcStat $ml | mailx -s "space on: $BOX" dkuhn@oracle.com ;; *) arcStat="huh?: $usedSpc" esac # BOX=$(uname -a | awk '{print $2}') echo $arcStat # done # exit 0 ***************************************************** Sequences that have less cache size************************************** select sequence_owner, sequence_name, cache_size, order_flag, last_number from dba_sequences where (order_flag = 'Y' or cache_size < 20) and last_number > 10000 order by last_number desc; ****************************************************** Process Details from SPID *********************************************** SELECT 'USERNAME : ' || s.username || CHR(10) || 'SCHEMA : ' || s.schemaname || CHR(10) || 'OSUSER : ' || s.osuser || CHR(10) || 'PROGRAM : ' || s.program || CHR(10) || 'SPID : ' || p.spid || CHR(10) || 'SID : ' || s.sid || CHR(10) || 'SERIAL# : ' || s.serial# || CHR(10) || 'KILL STRING: ' || '''' || s.sid || ',' || s.serial# || '''' || CHR(10) || 'MACHINE : ' || s.machine || CHR(10) || 'TYPE : ' || s.type || CHR(10) || 'TERMINAL : ' || s.terminal || CHR(10) || 'SQL ID : ' || q.sql_id || CHR(10) || 'SQL TEXT : ' || q.sql_text FROM v$session s ,v$process p ,v$sql q WHERE s.paddr = p.addr AND p.spid = '&&PID_FROM_OS' AND s.sql_id = q.sql_id(+); ************************************************* Session Wait events and Wait class ********************************************* select metric_name, value from v$sysmetric where metric_name in ('Database CPU Time Ratio', 'Database Wait Time Ratio') and intsize_csec = (select max(INTSIZE_CSEC) from V$SYSMETRIC); select ash.user_id, u.username, s.sql_text, sum(ash.wait_time + ash.time_waited) ttl_wait_time from v$active_session_history ash, v$sqlarea s, dba_users u where ash.sample_time between sysdate - 60/2880 and sysdate and ash.sql_id = s.sql_id and ash.user_id = u.user_id group by ash.user_id,s.sql_text, u.username order by ttl_wait_time; select wait_class, name from v$event_name where name LIKE 'enq%' and wait_class <> 'Other' order by wait_class; select a.event, a.total_waits, a.time_waited, a.average_wait from v$system_event a, v$event_name b, v$system_wait_class c where a.event_id=b.event_id and b.wait_class#=c.wait_class# and c.wait_class in ('Application','Concurrency') order by average_wait desc; select a.sid, a.event, a.total_waits, a.time_waited, a.average_wait from v$session_event a, v$session b where time_waited > 0 and a.sid=b.sid and b.username is not NULL and a.event='enq: TX - row lock contention'; select event, sum(wait_time + time_waited) total_wait_time from v$active_session_history where sample_time between sysdate ñ 30/2880 and sysdate group by event order by total_wait_time desc select s.sid, s.username, sum(a.wait_time + a.time_waited) total_wait_time from v$active_session_history a, v$session s where a.sample_time between sysdate ñ 30/2880 and sysdate and a.session_id=s.sid group by s.sid, s.username order by total_wait_time desc; select a.current_obj#, o.object_name, o.object_type, a.event, sum(a.wait_time + a.time_waited) total_wait_time from v$active_session_history a, dba_objects d where a.sample_time between sysdate ñ 30/2880 and sysdate and a.current_obj# = d.object_id group by a.current_obj#, d.object_name, d.object_type, a.event order by total_wait_time; select a.user_id,u.username,s.sql_text, sum(a.wait_time + a.time_waited) total_wait_time from v$active_session_history a, v$sqlarea s, dba_users u where a.sample_time between sysdate ñ 30/2880 and sysdate and a.sql_id = s.sql_id and a.user_id = u.user_id group by a.user_id,s.sql_text, u.username; select wait_class, event, time_waited / 100 time_secs from v$system_event e where e.wait_class <> 'Idle' AND time_waited > 0 union select 'Time Model', stat_name NAME, round ((value / 1000000), 2) time_secs from v$sys_time_model where stat_name NOT IN ('background elapsed time', 'background cpu time') order by 3 desc; select sid, username, event, blocking_session, seconds_in_wait, wait_time from v$session where state in ('WAITING'); select wait_class_id, wait_class, total_waits, time_waited from v$session_wait_class where sid = <SID>; select event, total_waits, time_waited from v$system_event e, v$event_name n where n.event_id = e.event_id and e.wait_class_id = 4217450380; select event, sum(P3), sum(seconds_in_wait) seconds_in_wait from v$session_wait where event like 'latch%' group by event; select wait_class, event, time_waited / 100 time_secs from v$system_event e where e.wait_class <> 'Idle' AND time_waited > 0 union select 'Time Model', stat_name NAME, round ((value / 1000000), 2) time_secs from v$sys_time_model where stat_name not in ('background elapsed time', 'background cpu time') order by 3 desc; SELECT s.event, sum(s.wait_time + s.time_waited) total_wait FROM v$active_session_history s WHERE s.sample_time between sysdate-1/24/4 AND sysdate GROUP BY s.event ORDER BY 2 desc; column username format a12 column module format a30 SELECT * FROM ( SELECT s.username, s.module, s.sid, s.serial#, count(*) FROM v$active_session_history h, v$session s WHERE h.session_id = s.sid AND h.session_serial# = s.serial# AND session_state= 'ON CPU' AND sample_time > sysdate - interval '15' minute GROUP BY s.username, s.module, s.sid, s.serial# ORDER BY count(*) desc ) where rownum <= 5; SELECT * FROM ( SELECT o.object_name, o.object_type, s.event, SUM(s.wait_time + s.time_waited) total_waited FROM v$active_session_history s, dba_objects o WHERE s.sample_time between sysdate - 1/24/4 and sysdate AND s.current_obj# = o.object_id GROUP BY o.object_name, o.object_type, s.event ORDER BY 4 desc ) WHERE rownum <= 5; SELECT * FROM ( SELECT u.username, h.module, h.session_id sid, h.session_serial# serial#, count(*) FROM dba_hist_active_sess_history h, dba_users u WHERE h.user_id = u.user_id AND session_state= 'ON CPU' AND (sample_time between to_date('2011-05-15:00:00:00','yyyy-mm-dd:hh24:mi:ss') AND to_date('2011-05-15:23:59:59','yyyy-mm-dd:hh24:mi:ss')) AND u.username != 'SYS' GROUP BY u.username, h.module, h.session_id, h.session_serial# ORDER BY count(*) desc ) where rownum <= 5; SELECT * FROM ( SELECT o.object_name, o.object_type, s.event, SUM(s.wait_time + s.time_waited) total_waited FROM dba_hist_active_sess_history s, dba_objects o WHERE s.sample_time between to_date('2011-05-15:00:00:00','yyyy-mm-dd:hh24:mi:ss') AND to_date('2011-05-15:23:59:59','yyyy-mm-dd:hh24:mi:ss') AND s.current_obj# = o.object_id GROUP BY o.object_name, o.object_type, s.event ORDER BY 4 desc ) WHERE rownum <= 5; ************************************************* Segment details using block ID ************************************************* select relative_fno, owner, segment_name, segment_type from dba_extents where file_id = &file and &block between block_id and block_id + blocks - 1; ******************************************************* Locking details ********************************************************** select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ; select decode(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type from v$lock where (id1, id2, type) in (select id1, id2, type from v$lock where request>0) order by id1, request; select lpad(' ',decode(l.xidusn,0,3,0)) || l.oracle_username "User", o.owner, o.object_name, o.object_type from v$locked_object l, dba_objects o where l.object_id = o.object_id order by o.object_id, 1 desc; select to_char(h.sample_time, 'HH24:MI:SS') TIME,h.session_id, decode(h.session_state, 'WAITING' ,h.event, h.session_state) STATE, h.sql_id, h.blocking_session BLOCKER from v$active_session_history h, dba_users u where u.user_id = h.user_id and h.sample_time > SYSTIMESTAMP-(2/1440); select sample_time, event, wait_time from v$active_session_history where session_id = 81 and session_serial# = 422; select sql_text, application_wait_time from v$sql where sql_id in ( select sql_id from v$active_session_history where sample_time = '08-MAR-11 05.00.52.00 PM' and session_id = 68 and session_serial# = 422); ******************************************** Tables not having index on their foreign key ******************************************* select * from ( select c.table_name, co.column_name, co.position column_position from user_constraints c, user_cons_columns co where c.constraint_name = co.constraint_name and c.constraint_type = 'R' minus select ui.table_name, uic.column_name, uic.column_position from user_indexes ui, user_ind_columns uic where ui.index_name = uic.index_name ) order by table_name, column_position; select a.constraint_name cons_name ,a.table_name tab_name ,b.column_name cons_column ,nvl(c.column_name,'***No Index***') ind_column from user_constraints a join user_cons_columns b on a.constraint_name = b.constraint_name left outer join user_ind_columns c on b.column_name = c.column_name and b.table_name = c.table_name where constraint_type = 'R' order by 2,1; ************************************************************* PGA issue *************************************************************** select optimal_count, round(optimal_count*100/total, 2) optimal_perc, onepass_count, round(onepass_count*100/total, 2) onepass_perc, multipass_count, round(multipass_count*100/total, 2) multipass_perc from (select decode(sum(total_executions), 0, 1, sum(total_executions)) total, sum(OPTIMAL_EXECUTIONS) optimal_count, sum(ONEPASS_EXECUTIONS) onepass_count, sum(MULTIPASSES_EXECUTIONS) multipass_count from v$sql_workarea_histogram where low_optimal_size > (64*1024)); select low_optimal_size/1024 low, (high_optimal_size+1)/1024 high, optimal_executions, onepass_executions, multipasses_executions from v$sql_workarea_histogram where total_executions !=0; select name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage from (SELECT name, value cnt, (sum(value) over ()) total from V$SYSSTAT where name like 'workarea exec%'); ********************************************************** Index Monitoring ************************************************************* select io.name, t.name, decode(bitand(i.flags, 65536), 0, 'NO', 'YES'), decode(bitand(ou.flags, 1), 0, 'NO', 'YES'), ou.start_monitoring, ou.end_monitoring from sys.obj$ io ,sys.obj$ t ,sys.ind$ i ,sys.object_usage ou where i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# = i.bo#; *********************************************************** Segment Advisor ************************************************************* SELECT 'Task Name : ' || f.task_name || CHR(10) || 'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) || 'Segment Name : ' || o.attr2 || CHR(10) || 'Segment Type : ' || o.type || CHR(10) || 'Partition Name : ' || o.attr3 || CHR(10) || 'Message : ' || f.message || CHR(10) || 'More Info : ' || f.more_info || CHR(10) || '------------------------------------------------------' Advice FROM dba_advisor_findings f ,dba_advisor_objects o ,dba_advisor_executions e WHERE o.task_id = f.task_id AND o.object_id = f.object_id AND f.task_id = e.task_id AND e. execution_start > sysdate - 1 AND e.advisor_name = 'Segment Advisor' ORDER BY f.task_name; SELECT 'Segment Advice --------------------------'|| chr(10) || 'TABLESPACE_NAME : ' || tablespace_name || chr(10) || 'SEGMENT_OWNER : ' || segment_owner || chr(10) || 'SEGMENT_NAME : ' || segment_name || chr(10) || 'ALLOCATED_SPACE : ' || allocated_space || chr(10) || 'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10) || 'RECOMMENDATIONS : ' || recommendations || chr(10) || 'SOLUTION 1 : ' || c1 || chr(10) || 'SOLUTION 2 : ' || c2 || chr(10) || 'SOLUTION 3 : ' || c3 Advice FROM TABLE(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE')); select segments_processed ,end_time from dba_auto_segadv_summary order by end_time; DECLARE my_task_id number; obj_id number; my_task_name varchar2(100); my_task_desc varchar2(500); BEGIN my_task_name := 'F_REGS Advice'; my_task_desc := 'Manual Segment Advisor Run'; --------- -- Step 1 --------- dbms_advisor.create_task ( advisor_name => 'Segment Advisor', task_id => my_task_id, task_name => my_task_name, task_desc => my_task_desc); --------- -- Step 2 --------- dbms_advisor.create_object ( task_name => my_task_name, object_type => 'TABLE', attr1 => 'MV_MAINT', attr2 => 'F_REGS', attr3 => NULL, attr4 => NULL, attr5 => NULL, object_id => obj_id); --------- -- Step 3 --------- dbms_advisor.set_task_parameter( task_name => my_task_name, parameter => 'recommend_all', value => 'TRUE'); --------- -- Step 4 --------- dbms_advisor.execute_task(my_task_name); END; / SELECT 'Segment Advice --------------------------'|| chr(10) || 'TABLESPACE_NAME : ' || tablespace_name || chr(10) || 'SEGMENT_OWNER : ' || segment_owner || chr(10) || 'SEGMENT_NAME : ' || segment_name || chr(10) || 'ALLOCATED_SPACE : ' || allocated_space || chr(10) || 'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10) || 'RECOMMENDATIONS : ' || recommendations || chr(10) || 'SOLUTION 1 : ' || c1 || chr(10) || 'SOLUTION 2 : ' || c2 || chr(10) || 'SOLUTION 3 : ' || c3 Advice FROM TABLE(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE')); SELECT 'Task Name : ' || f.task_name || chr(10) || 'Segment Name : ' || o.attr2 || chr(10) || 'Segment Type : ' || o.type || chr(10) || 'Partition Name : ' || o.attr3 || chr(10) || 'Message : ' || f.message || chr(10) || 'More Info : ' || f.more_info TASK_ADVICE FROM dba_advisor_findings f ,dba_advisor_objects o WHERE o.task_id = f.task_id AND o.object_id = f.object_id AND f.task_name like 'F_REGS Advice' ORDER BY f.task_name; *********************************************************** Create database *********************************************************** CREATE DATABASE O11R2 MAXLOGFILES 16 MAXLOGMEMBERS 4 MAXDATAFILES 1024 MAXINSTANCES 1 MAXLOGHISTORY 680 CHARACTER SET AL32UTF8 DATAFILE '/ora01/dbfile/O11R2/system01.dbf' SIZE 500M REUSE EXTENT MANAGEMENT LOCAL UNDO TABLESPACE undotbs1 DATAFILE '/ora02/dbfile/O11R2/undotbs01.dbf' SIZE 800M SYSAUX DATAFILE '/ora03/dbfile/O11R2/sysaux01.dbf' SIZE 500M DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/ora02/dbfile/O11R2/temp01.dbf' SIZE 500M DEFAULT TABLESPACE USERS DATAFILE '/ora01/dbfile/O11R2/users01.dbf' SIZE 50M LOGFILE GROUP 1 ('/ora01/oraredo/O11R2/redo01a.rdo', '/ora02/oraredo/O11R2/redo01b.rdo') SIZE 200M, GROUP 2 ('/ora01/oraredo/O11R2/redo02a.rdo', '/ora02/oraredo/O11R2/redo02b.rdo') SIZE 200M, GROUP 3 ('/ora01/oraredo/O11R2/redo03a.rdo', '/ora02/oraredo/O11R2/redo03b.rdo') SIZE 200M USER sys IDENTIFIED BY topfoo USER system IDENTIFIED BY topsecrectfoo; ******************************************************** System Statistics history *********************************************************** SELECT n1 AS sreadtim, n2 AS mreadtim, n3 AS cpuspeed, n11 AS mbrc FROM sys.aux_stats_history WHERE c4 = 'CPU_SERIO' ORDER BY statid; SELECT n1 AS maxthr, n2 AS slavethr FROM sys.aux_stats_history WHERE c4 = 'PARIO' ORDER BY statid; ********************************************************* Column low/high value ************************************************************** COLUMN low_value FORMAT 9999 COLUMN high_value FORMAT 9999 SELECT utl_raw.cast_to_number(low_value) AS low_value, utl_raw.cast_to_number(high_value) AS high_value FROM dba_tab_col_statistics WHERE owner='SCOTT' AND table_name = 'T' AND column_name = 'VAL1'; DECLARE l_low_value dba_tab_col_statistics.low_value%TYPE; l_high_value dba_tab_col_statistics.high_value%TYPE; l_val1 t.val1%TYPE; BEGIN SELECT low_value, high_value INTO l_low_value, l_high_value FROM dba_tab_col_statistics WHERE owner='SCOTT' AND table_name = 'T' AND column_name = 'VAL1'; dbms_stats.convert_raw_value(l_low_value, l_val1); dbms_output.put_line('low_value: ' || l_val1); dbms_stats.convert_raw_value(l_high_value, l_val1); dbms_output.put_line('high_value: ' || l_val1); END; / *********************************************************** Clustering Factor *********************************************************** CREATE OR REPLACE FUNCTION clustering_factor ( p_owner IN VARCHAR2, p_table_name IN VARCHAR2, p_column_name IN VARCHAR2 ) RETURN NUMBER IS l_cursor SYS_REFCURSOR; l_clustering_factor BINARY_INTEGER := 0; l_block_nr BINARY_INTEGER := 0; l_previous_block_nr BINARY_INTEGER := 0; l_file_nr BINARY_INTEGER := 0; l_previous_file_nr BINARY_INTEGER := 0; BEGIN OPEN l_cursor FOR 'SELECT dbms_rowid.rowid_block_number(rowid) block_nr, '|| ' dbms_rowid.rowid_to_absolute_fno(rowid, '''|| p_owner||''','''|| p_table_name||''') file_nr '|| 'FROM '||p_owner||'.'||p_table_name||' '|| 'WHERE '||p_column_name||' IS NOT NULL '|| 'ORDER BY ' || p_column_name; LOOP FETCH l_cursor INTO l_block_nr, l_file_nr; EXIT WHEN l_cursor%NOTFOUND; IF (l_previous_block_nr <> l_block_nr OR l_previous_file_nr <> l_file_nr) THEN l_clustering_factor := l_clustering_factor + 1; END IF; l_previous_block_nr := l_block_nr; l_previous_file_nr := l_file_nr; END LOOP; CLOSE l_cursor; RETURN l_clustering_factor; END; / SELECT index_name, clustering_factor(user, table_name, column_name) AS clust_factor FROM dba_ind_columns WHERE owner='SCOTT' AND table_name = 'T'; ********************************************************************************************************************* crsctl stat res -t -init ***************************************switch in windows ************************************************** To Set the /3GB Startup Switch in Windows Server 2003 1. Right-click My Computer and select Properties. The System Properties dialog box will appear. 2. Click the Advanced tab. 3. In the Startup and Recovery area, click Settings. The Startup and Recovery dialog box will appear. 4. In the System startup area, click Edit. This will open the Windows boot.ini file in Notepad. 5. In the [Operating Systems] section, add the following switches to the end of the startup line that includes the /fastdetect switch: /3GB 6. Save the changes and close Notepad. 7. Click OK two times to close the open dialog boxes, and then restart the computer for the change to take effect. ***************************************SNAPSHOT STANDBY*************************************** *.db_recovery_file_dest_size=75G *.db_recovery_file_dest='/data/RMAN/standby' *.db_flashback_retention_target=1440 set trim on trims on; SHUTDOWN IMMEDIATE; STARTUP MOUNT; prompt "OPENING SNAPSHOT STANDBY" ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; prompt "Opening Database" ALTER DATABASE OPEN; SELECT flashback_on FROM v\$database; SELECT name from v\$restore_point; select name , open_mode , controlfile_type from v\$database; SHUTDG$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<SHUTDG SHUTDOWN IMMEDIATE; STARTUP MOUNT; prompt "CONVERTING TO PHYSICAL STANDBY" ALTER DATABASE CONVERT TO PHYSICAL STANDBY; SHUTDOWN IMMEDIATE; STARTUP MOUNT; prompt "STARTING RECOVERY" recover managed standby database disconnect from session delay 20; SELECT flashback_on FROM v\$database; select name , open_mode , controlfile_type from v\$database; SHUTDG ########################## load time ################################### col stat_name for a10 col node for 999999 with p1 as (select snap_id,instance_number node,stat_name, round((value - Lag(value,1) over (order by snap_id))/100/60/60) dbhrs from dba_hist_sysstat where snap_id between 6542 and 6560 and stat_name in ('DB time')) select * from p1 order by 1,2; ########################### IO LATENCY ############################## with s1 as (select /*+ parallel (X 10) */ snap_id,round(sum(Singleblkrds)/1000000,1) megreads, round(sum(singleblkrdtim)*10) ms from dba_hist_filestatxs X where snap_id>7705 group by snap_id), s2 as (select snap_id,megreads - Lag(Megreads,1) over (order by snap_id) Totrds, ms- Lag (Ms,1) over (order by snap_id) Tot_Ms from s1) select snap_id,totrds Megards,round(Tot_Ms/Totrds/1000000,1) "Latency(Ms)" from s2 where Totrds>1; ################################################################################### ps -e -o pcpu,pid,user,tty,args|sort -n -k 1 -r|head ps -e -o pmem,pid,user,tty,args|sort -n -k 1 -r|head1) fra_space_check.sql( Flash recovery area and DR sync) set lines 200; select (SPACE_LIMIT/1024/1024/1024)SPACE_LIMIT_gb, (SPACE_USED/1024/1024/1024)SPACE_USED_gb, (SPACE_RECLAIMABLE/1024/1024/1024)SPACE_RECLAIMABLE_gb, ((SPACE_LIMIT/1024/1024/1024)-(SPACE_USED/1024/1024/1024)+(SPACE_RECLAIMABLE/1024/1024/1024)) AVAILABLE_SPACE, ROUND((SPACE_USED-SPACE_RECLAIMABLE)/SPACE_LIMIT*100,1)"PERCENT_FULL_OF_100%" FROM V$RECOVERY_FILE_DEST; select thread#,max(sequence#) from v$archived_log group by thread#; select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#; 2)max_tablespace_size.sql(Tablespace space check) set lines 200 pages 300; select TABLESPACE_NAME,sum(BYTES)/1024/1024/1024,sum(MAXBYTES)/1024/1024/1024,sum(BYTES)/1024/1024/1024*100/(sum (MAXBYTES)/1024/1024/1024) "Used%" from dba_data_files where AUTOEXTENSIBLE='YES' group by TABLESPACE_NAME order by 4; 3)longops_session.sql( long running operations in Database) set linesize 180 col opname for a30 col username for a15 SELECT SID, SERIAL#, username,to_char(start_time, 'dd Mon, yyyy hh24:mi:ss') as start_time, opname, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) COMPLETE FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK order by 1; 4)temp_space_check.sql(For Temporary tablespace space check) col name for a20 SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "ExtManag", TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes, 0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)", TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY'; 5)events_details.sql(Wait Events monitoring) set pages 300; set lines 200; col event for a50; col username for a10; select s.sid ,username,status ,s.module,s.sql_id,w.event,w.SECONDS_IN_WAIT,w.STATE from v$session_wait w,v$session s where w.sid=s.sid and w.EVENT <> 'SQL*Net message from client' and username is not null and w.event <> 'Streams AQ: waiting for messages in the queue'; 6)log_switches_info.sql(Log switches in a day) set lines 250; set pages 200; select to_char(first_time,'DD-MON-YYYY') day, to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00", to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01", to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02", to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03", to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04", to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05", to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06", to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07", to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08", to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09", to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10", to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11", to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12", to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13", to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14", to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15", to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16", to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17", to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18", to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19", to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20", to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21", to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "22", to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23" from v$log_history group by to_char(first_time,'DD- MON-YYYY') order by 1; 7)logon_sesion.sql( Logon time for a session in Database) select sid,serial#,to_char(logon_time,'DD-MON-YYYY:HH24:MI:SS'),program,module from v$session where sid=&1; 8)sessinfo_db.sql( Session information in Database) select sid,serial#,program from v$session where sid=&1; 9)plan_query.sql( To get execution plan display of a query by providing sql_id from v$sql) set lines 200; set pages 1000; select * from table(dbms_xplan.display_cursor('&1')); 10)sync_prod_db.sql(To check max sequence number in the primary database) select thread#,max(sequence#) from v$archived_log group by thread#; 11)tablespace_space.sql( To get tablespace details type and space usage in Database) set linesize 180 set pagesize 100 col "Name" for a30 SELECT d.tablespace_name "Name", d.contents "Type", d.status "Status",TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D90') "Total Size (M)", TO_CHAR(NVL(NVL(f.bytes, 0),0)/1024/1024, '99G999G990D90') "Free (MB)", TO_CHAR(NVL((NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Free %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) order by "Free %"; 12)drsync_check.sql( Check the applied and unapplied archives on the Standby Database side) select sequence# from v$archived_log where applied='NO'; select max(sequence#) from v$archived_log where applied='YES'; exit; / 13)invalids.sql( To count the invalids objects in the Database) select count(*) from dba_objects where status='INVALID'; 14) lock_session.sql( To find the locking session details in the Database) set linesize 180 set pagesize 10000 col sql_fulltext for a100 select s1.username || '@'|| s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ; 15) Temp_usage.sql( To find the temporary tablespace usage details) SELECT A.tablespace_name tablespace, D.mb_total, SUM(A.used_blocks * D.block_size) / 1024/1024 mb_used, D.mb_total - SUM(A.used_blocks * D.block_size) / 1024/1024 mb_free FROM v$sort_segment A, (SELECT B.name, C.block_size, SUM(C.bytes) / 1024/1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts# = C.ts# GROUP BY B.name, C.block_size) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total; 16) master_db_hc.sql( Master Health check script for Database,creates DBHEALTH.html report) set feedback off; set markup html on spool on; spool DBHEALTH.html; set termout off; prompt ************ DAILY HEALTH CHECK SCRIPT DB************ prompt ************ SCRIPT FOR DB: RAFI ALVI ************ prompt prompt**---------------Time-----------------------------** select sysdate from dual; prompt**---------------Database Details-----------------------------** prompt SELECT DBID "DATABASE_ID", NAME "DB_NAME", LOG_MODE, OPEN_MODE, RESETLOGS_TIME FROM V$DATABASE; SELECT instance_name, status, to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time" FROM sys.v_$instance; column "Host Name" format a15; column "Host Address" format a15; SELECT UTL_INADDR.GET_HOST_ADDRESS "Host Address", UTL_INADDR.GET_HOST_NAME "Host Name" FROM DUAL; SELECT BANNER "VERSION" FROM V$VERSION; col "Database Size" format a15; col "Free space" format a15; select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size", round(free.p / 1024 / 1024/1024) || ' GB' "Free space" from (select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log) used, (select sum(bytes) as p from dba_free_space) free group by free.p; prompt**---------------SGA Component Size------------------------------** set line 200; select pool, m_bytes from ( select pool, to_char( trunc(sum(bytes)/1024/1024,2), '99999.99' ) as M_bytes from v$sgastat where pool is not null group by pool union select name as pool, to_char( trunc(bytes/1024/1024,3), '99999.99' ) as M_bytes from v$sgastat where pool is null order by 2 desc ) UNION ALL select 'TOTAL' as pool, to_char( trunc(sum(bytes)/1024/1024,3), '99999.99' ) from v$sgastat; prompt**---------------ASM SPACE DETAILS------------------------------** select NAME,TOTAL_MB/1024 "Total",FREE_MB/1024 "Free",REQUIRED_MIRROR_FREE_MB/1024 "REQFORMIRROR",USABLE_FILE_MB/1024 "Usable" from v$asm_diskgroup; prompt**---------------FRA USAGE and FREE SPACE------------------------------** select (SPACE_LIMIT/1024/1024/1024)SPACE_LIMIT_gb, (SPACE_USED/1024/1024/1024)SPACE_USED_gb, (SPACE_RECLAIMABLE/1024/1024/1024)SPACE_RECLAIMABLE_gb, ((SPACE_LIMIT/1024/1024/1024)-(SPACE_USED/1024/1024/1024)+(SPACE_RECLAIMABLE/1024/1024/1024)) AVIALABLE_SPACE, ROUND((SPACE_USED-SPACE_RECLAIMABLE)/SPACE_LIMIT*100,1)"PERCENT_FULL_OF_100%" FROM V$RECOVERY_FILE_DEST; prompt**--------------Monitoring Objects Created within 2 days---------------------** select count(1) from user_objects where CREATED >= sysdate - 2; prompt**--------------Counting Invalid object in Database---------------------------** Select owner, object_type, count(*) from dba_objects where status='INVALID' group by owner, object_type; prompt**---------------Track Redolog Generation-------------------------------------** select trunc(completion_time) logdate, count(*) logswitch, round((sum(blocks*block_size) / 1024 / 1024)) "REDO PER DAY (MB)" from v$archived_log group by trunc(completion_time) order by 1; prompt**--------------Monitor DB Corruption--------------------** set line 200; SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name, d.STATUS, r.ERROR, r.CHANGE#, r.TIME FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t WHERE t.TS# = d.TS# AND d.FILE# = r.FILE#; prompt**---------------Tablespace Information--------------------------------------** set pages 200; select df.tablespace_name "Tablespace", totalusedspace "Used MB", (df.totalspace - tu.totalusedspace) "Free MB", df.totalspace "Total MB", round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct.Free" from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df, (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name order by "Pct.Free"; spool off; set markup html off; master_db_check.sh #!/bin/sh export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1 export GRID_HOME=/u01/app/11.2.0/grid export PATH=$PATH:$ORACLE_HOME/bin:.:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin:$GRID_HOME/bin export TNS_ADMIN=$GRID_HOME/network/admin export ORA_NLS10=$ORACLE_HOME/nls/data/9idata export ORACLE_SID=TESTDB1 DBHEALTH="$(date +%d%m%y)" mkdir /home/oracle/DBA/dailyreports/$DBHEALTH cd /home/oracle/DBA/dailyreports/$DBHEALTH sqlplus / as sysdba <<EOF @/home/oracle/DBA/scripts/dailyhc.sql exit; EOF rm -f $DBHEALTH.html mv DBHEALTH.html $DBHEALTH.html 18)arch_del.sh( Archive log deletion script) #!/bin/sh export ORACLE_BASE=/u01/app/oracle ORACLE_HOME="/u01/app/oracle/db" export ORACLE_HOME export GRID_HOME=/u01/app/11.2.0/grid ORACLE_SID="TESTDB" export ORACLE_SID ORA_NLS10="/u01/app/oracle/db/nls/data/9idata" export ORA_NLS10 PATH="/u01/app/oracle/db/bin:/usr/ccs/bin:/usr/sbin:/u01/app/oracle/db/jdk/jre/bin:$PATH:." export PATH LINK_CNTRL="" export LINK_CNTRL TNS_ADMIN="/u01/app/oracle/db/network/admin/TESTDB_host01" export TNS_ADMIN cd home/oracle/DBA/logs/archdel rman target / log="delarch_$(date +%d%m%y_%H%M%S).log" <<HERE delete noprompt archivelog all completed before 'sysdate - 4'; crosscheck archivelog all; list expired archivelog all; HERE 19) locks.sql (locks on objects) SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode FROM dba_objects o, gv$locked_object l WHERE o.object_id = l.object_id;
