Warning: Creating default object from empty value in /home/customer/www/gavinsoorma.com/public_html/wp-content/themes/specular/admin/inc/class.redux_filesystem.php on line 29
Scripts | Oracle DBA – Tips and Techniques
News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

ASH and AWR Performance Tuning Scripts

  • Posted by Gavin Soorma
  • On November 16, 2012
  • 7 Comments
Listed below are some SQL queries which I find particularly useful for performance tuning. These are based on the Active Session History V$ View to get a current perspective of performance and the DBA_HIST_* AWR history tables for obtaining performance data pertaining to a period of time in the past. I would like to add […]
Read More
 10

DBUPGDIAG script to check integrity of database before 11g upgrade

  • Posted by Arjun Raja
  • On August 20, 2009
  • 0 Comments
Script to check integrity of a 9i or 10g database before upgrade to 11g. Run this script connected as sysdba in the 9i or 10g database. NAME: DBUPGDIAG.SQL This script can be downloaded from Oracle Metalink note 556610.1 — PURPOSE: — This script is intended to provide a user friendly output to diagonise — the […]
Read More
 

Script – Check RMAN Backup Status

  • Posted by Arjun Raja
  • On July 28, 2009
  • 2 Comments
Scripts to check backup status and timings of database backups – This script will be run in the database, not the catalog. Login as sysdba – This script will report on all backups – full, incremental and archivelog backups – col STATUS format a9 col hrs format 999.99 select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'mm/dd/yy hh24:mi') start_time, […]
Read More
 1

Script – Sessions with high physical reads

  • Posted by Gavin Soorma
  • On July 9, 2009
  • 0 Comments
set linesize 120 col os_user format a10 col username format a15 col pid format 9999999999 PROMPT SESSIONS SORTED BY PHYSICAL READS PROMPT select OSUSER os_user,username, PROCESS pid, ses.SID sid, SERIAL#, PHYSICAL_READS, BLOCK_CHANGES from v$session ses, v$sess_io sio where ses.SID = sio.SID and username is not null and status='ACTIVE' order by PHYSICAL_READS;
Read More
 

Script – Monitor Flashback Logs

  • Posted by Gavin Soorma
  • On July 9, 2009
  • 0 Comments
PROMPT How Far Back Can We Flashback To (Time)? PROMPT select to_char(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time" from v$flashback_database_log; PROMPT PROMPT How Far Back Can We Flashback To (SCN)? PROMPT col oldest_flashback_scn format 99999999999999999999999999 select oldest_flashback_scn from v$flashback_database_log; PROMPT PROMPT Flashback Area Usage SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE; PROMPT col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999 […]
Read More
 

Script – Database structure and file location

  • Posted by Gavin Soorma
  • On July 7, 2009
  • 0 Comments
set pagesize 500 set linesize 130 Prompt Prompt Control Files Location >>>> col name format a60 heading "Control Files" select name from sys.v_$controlfile / Prompt Prompt Redo Log File Locations >>>> Prompt col Grp format 9999 col member format a50 heading "Online REDO Logs" col File# format 9999 col name format a50 heading "Online REDO […]
Read More
 

Script – Tablespace free space and fragmentation

  • Posted by Gavin Soorma
  • On July 7, 2009
  • 1 Comments
set linesize 150 column tablespace_name format a20 heading 'Tablespace' column sumb format 999,999,999 column extents format 9999 column bytes format 999,999,999,999 column largest format 999,999,999,999 column Tot_Size format 999,999 Heading 'Total| Size(Mb)' column Tot_Free format 999,999,999 heading 'Total Free(MB)' column Pct_Free format 999.99 heading '% Free' column Chunks_Free format 9999 heading 'No Of Ext.' column […]
Read More
 1

Data Guard Switchover Unix shell script

  • Posted by Gavin Soorma
  • On July 6, 2009
  • 1 Comments
The following Unix shell scripts can be used to automate the Data Guard Switchover process of a physical standby database. It is very important that the scripts are run in the correct order and on right machine. These scripts are based on a few customisations. The *.sh scripts are located in a directory called “/var/opt/oracle/dataguard” […]
Read More
 

Script – Latch Contention (top 5 latches)

  • Posted by Gavin Soorma
  • On July 6, 2009
  • 0 Comments
This script will display the top 5 latches with the most sleeps. Script can be changed to even sort the display on misses instead. set linesize 120 col name format a30 select * from (select name, gets,misses, sleeps from v$latch order by sleeps desc) where rownum < 6;
Read More
 

Script – Top SQL (Physical Reads)

  • Posted by Gavin Soorma
  • On July 6, 2009
  • 0 Comments
This script will list the top 5 SQL statements sorted by the most number of physical reads set serverout on size 1000000 set feedback off declare top5 number; text1 varchar2(4000); x number; len1 number; Cursor c1 is select disk_reads,substr(sql_text,1,4000) from v$sqlarea order by disk_reads desc; begin dbms_output.put_line('Reads'||' '||' Text'); dbms_output.put_line ('-----'||' '||'----------------------------------------------------'); dbms_output.put_line(' '); open […]
Read More
 

Script – Top SQL (Buffer Gets)

  • Posted by Gavin Soorma
  • On July 6, 2009
  • 0 Comments
This script will list the top 5 SQL statements sorted by the most number of buffer gets or logical reads set serverout on size 1000000 declare top5 number; text1 varchar2(4000); x number; len1 number; Cursor c1 is select buffer_gets,substr(sql_text,1,4000) from v$sqlarea order by buffer_gets desc; begin dbms_output.put_line('Reads'||' '||' Text'); dbms_output.put_line ('-----'||' '||'---------------------------------------------------'); dbms_output.put_line(' '); open […]
Read More
 

Script – Segments with highest I/O activity

  • Posted by Gavin Soorma
  • On July 6, 2009
  • 0 Comments
This script will list the top 10 segments in the database that have the most number of physical reads against them. Script can also be changed to query on 'physical writes' instead. set pagesize 200 setlinesize 120 col segment_name format a20 col owner format a10 select segment_name,object_type,total_physical_reads from ( select owner||'.'||object_name as segment_name,object_type, value as […]
Read More
 1

Script – List status of all submitted DBMS jobs

  • Posted by Gavin Soorma
  • On July 3, 2009
  • 0 Comments
set pagesize 100 set linesize 120 ttitle - center 'Submitted DBMS Jobs' skip 2 col job format 99999 heading 'job#' col subu format a10 heading 'Submitter' trunc col lsd format a5 heading 'Last|Ok|Date' col lst format a5 heading 'Last|Ok|Time' col nrd format a5 heading 'Next|Run|Date' col nrt format a5 heading 'Next|Run|Time' col fail format 999 […]
Read More
 

Script – What Wait Events Are Sessions Waiting On

  • Posted by Gavin Soorma
  • On July 2, 2009
  • 0 Comments
set linesize 120 col username format a10 col event format a30   select sid, serial#,username, event, seconds_in_wait, wait_time from v$session where state = 'WAITING' and wait_class != 'Idle' order by event ;
Read More
 

Script – Sessions Waiting On A Particular Wait Event

  • Posted by Gavin Soorma
  • On July 2, 2009
  • 0 Comments
SELECT count(*), event FROM v$session_wait WHERE wait_time = 0 AND event NOT IN ('smon timer','pipe get','wakeup time manager', 'pmon timer','rdbms ipc message', 'SQL*Net message from client') GROUP BY event ORDER BY 1 DESC;
Read More
 

Script – Datafiles with highest I/O activity

  • Posted by Gavin Soorma
  • On July 1, 2009
  • 0 Comments
col name format a40 set linesize 140 select * from ( select name,phyrds, phywrts,readtim,writetim from v$filestat a, v$datafile b where a.file#=b.file# order by readtim desc) where rownum <6;
Read More
 

Script – Find SQL being executed by a OS Process ID (PID)

  • Posted by Gavin Soorma
  • On July 1, 2009
  • 1 Comments
prompt "Please Enter The UNIX Process ID" set pagesize 50000 set linesize 30000 set long 500000 set head off select s.username su, substr(sa.sql_text,1,540) txt from v$process p, v$session s, v$sqlarea sa where p.addr=s.paddr and s.username is not null and s.sql_address=sa.address(+) and s.sql_hash_value=sa.hash_value(+) and spid=&SPID;
Read More
 

Script – List SQL being executed by a particular SID

  • Posted by Gavin Soorma
  • On July 1, 2009
  • 0 Comments
col sql_text format a100 heading "Current SQL" select q.sql_text from v$session s , v$sql q WHERE s.sql_address = q.address and s.sql_hash_value + DECODE (SIGN(s.sql_hash_value), -1, POWER( 2, 32), 0) = q.hash_value AND s.sid=&1;
Read More
 

Script – Monitor sessions with high Physical Reads

  • Posted by Gavin Soorma
  • On July 1, 2009
  • 0 Comments
set linesize 120 col osuser format a10 col username format 10 select OSUSER osuser, username, PROCESS pid, ses.SID sid, SERIAL#, PHYSICAL_READS, BLOCK_CHANGES from v$session ses, v$sess_io sio where ses.SID = sio.SID order by PHYSICAL_READS;
Read More
 

Script – Query the RMAN catalog to list backup completion status

  • Posted by Gavin Soorma
  • On July 1, 2009
  • 0 Comments
Note – run this query connected as the owner of the RMAN catalog set lines 80 set pages 250 ttitle "Daily Backup........" select DB NAME,dbid,NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP, NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP from ( select a.name DB,dbid, decode(b.bck_type,'D',max(b.completion_time),'I', max(b.completion_time)) BACKUPTYPE_db, decode(b.bck_type,'L',max(b.completion_time)) BACKUPTYPE_arch from rc_database a,bs b where a.db_key=b.db_key and b.bck_type is not null and b.bs_key not in(Select […]
Read More