From: http://www.orafaq.com/node/871Some DBAs complain that Oracle's pre-10g job queue interface is lacking. Unlike cron or Windows Sche

Redo Log File Switches – By hour of the daypromptprompt "Morning .........."select to_char(first_time,'DD/MON') day,to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'000')"07",to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'000')"

Trigger to use with Data Guard to change service name<br />CREATE OR REPLACE TRIGGER manage_OCIservice<br />after startup on database<br />DECLARE<br />role VARCHAR(30);<br />BEGIN<br />SELECT DATABASE_ROLE INTO role FROM V$DATABASE;<br />IF role = ‘PRIMAR

Script- Track redo generation by dayselect trunc(completion_time) rundate,count(*) logswitch,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)"from v$archived_loggroup by trunc(completion_time)order by 1;

Query the RMAN catalog to check backup status<br />This script will query the RMAN catalog and report on the backup status of every database in the catalog.<br />  set lines 80set pages 250ttitle "Daily Backup........"select DB NAME,dbid,NVL(TO_CHA

Determing the optimal UNDO tablespace sizeSELECT 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)     

Monitor space used in ASM Disk GroupsSET LINESIZE 145SET PAGESIZE 9999SET VERIFY offCOLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'COLUMN block_si

Identify 'hot' objects when faced with 'Cache Buffers Chains Latch' wait eventselect count(*) child_count, sum(gets) sum_gets, sum(misses) sum_misses, sum(sleeps) sum_sleepsfrom v$latch_childrenwhere name = 'cache buffers c

Monitor the Flashback area space usageSELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;SELECT * FROM V$RECOVERY_FILE_DEST;col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999col round(space_used/1048576) heading "Space Used (MB)

List all tablespaces with free space

Check for dependant foreign keys before dropping Primary Key<br />SELECT A.owner foreign_owner, a.table_name foreign_table, b.owner primary_owner, b.table_name primary_tablefrom dba_constraints a, dba_constraints bwhere a.r_constraint_name = b.constrai

Script – Check RMAN Backup StatusScripts 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 -co

Check if block change tracking file is being usedselect file#, avg(datafile_blocks), avg(blocks_read), avg(blocks_read/datafile_blocks) * 100as "% read for backup"from v$backup_datafilewhere incremental_level > 0 and used_change_tracking = 'YES'gro

Using DBMS_SCHEDULER to run a UNIX shell script<br />CREATE A PROGRAM<br />begin<br />dbms_scheduler.create_program<br />(<br />program_name => ‘UPDATE_VERITAS’,<br />program_type => ‘EXECUTABLE’,<br />program_action => ‘/opt/oracle/scripts/blade08/update_

Recompiling Objects based on dependencies<br />In order to prevent you from the task from running a recompile script several times so as compile all objects that are dependant on other objects, we need to take into account the dependency of objects – this

Identify database SID based on OS Process IDcol sid format 999999col username format a20col osuser format a15select b.spid,a.sid, a.serial#,a.username, a.osuserfrom v$session a, v$process bwhere a.paddr= b.addrand b.spid='&spid'order by b.spi

Monitor Data Guard Log ShippingNote: This query needs to be run on the Primary database.SET PAGESIZE 124COL DB_NAME FORMAT A8COL HOSTNAME FORMAT A12COL LOG_ARCHIVED FORMAT 999999COL LOG_APPLIED FORMAT 999999COL LOG_GAP FORMAT 9999COL APPLIE

Script – Query the RMAN catalog to list backup completion status<br />Note – run this query connected as the owner of the RMAN catalogset lines 80set pages 250ttitle "Daily Backup........"select DB NAME,dbid,NVL(TO_CHAR(max(backuptype_db),'DD/M

Script – Latch Contention (top 5 latches)<br />This script will display the top 5 latches with the most sleeps.<br />Script can be changed to even sort the display on misses instead.set linesize 120col name format a30select * from (select name, get

Data Guard Switchover Unix shell script<br />The following Unix shell scripts can be used to automate the Data Guard Switchover process of a physical standby database.<br />It is very important that the scripts are run in the correct order and on right mac

Script – Tablespace free space and fragmentation 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

Identify active transactions in undo and rollback segmentscol o format a10col u format a10select osuser o, username u, sid,segment_name s, substr(sa.sql_text,1,200) txtfrom v$session s,v$transaction t,dba_rollback_segs r,v$sqlarea sawhere s

Monitor long running operations using v$session_longopsSELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) COMPLETEFROM V$SESSION_LONGOPSWHERETOTALWORK != 0AND SOFAR != TOTALWORKorder by 1;Note: the same query ca

Script – List all recent DDL modificationsset pagesize 200 colsep ' 'col Owner format a20col Object_Name format a30col Last_DDL format a20break on Ownerselect Owner, Object_typeObject_Name,Timestamp Last_DDLfrom DBA_OBJECTSwhere SysDate

Script – Temporary tablespace usage<br />– Listing of temp segments.–<br />SELECT A.tablespace_name tablespace, D.mb_total,<br />SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,<br />D.mb_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb

Script – List Objects being accessed by a particular SIDset pagesize 300select sid,serial#,username,status from v$sessionWHERE USERNAME IS NOT NULLorder by STATUS DESC;col sid format 999col owner format a10col object format a20col type format

Script – Monitor sessions with high Physical Readsset linesize 120col osuser format a10col username format 10selectOSUSER osuser,username,PROCESS pid,ses.SID sid,SERIAL#,PHYSICAL_READS,BLOCK_CHANGESfrom v$session ses,v$sess_io sio

Script – List SQL being executed by a particular SIDcol sql_text format a100 heading "Current SQL"select q.sql_textfrom v$session s, v$sql qWHERE s.sql_address = q.addressand s.sql_hash_value + DECODE(SIGN(s.sql_hash_value), -1, POWER( 2, 32),

Script – Find SQL being executed by a OS Process ID (PID)prompt "Please Enter The UNIX Process ID"set pagesize 50000set linesize 30000set long 500000set head offselects.username su,substr(sa.sql_text,1,540) txtfrom v$process p,v$session s

Script – Segments with highest I/O activityThis script will list the top 10 segments in the database that have the most number ofphysical reads against them.Script can also be changed to query on 'physical writes' instead.set pagesize 200setlines

Script – Top SQL (Buffer Gets)<br />This script will list the top 5 SQL statements sorted by the most number of buffer gets or logical readsset serverout on size 1000000declaretop5 number;text1 varchar2(4000);x number;len1 number;Cursor c1 is

Script – Sessions with high physical reads<br />set linesize 120<br />col os_user format a10<br />col username format a15<br />col pid format 9999999999<br />PROMPT sessions sorted BY physical reads<br />PROMPT<br />SELECT osuser  os_user,<br />       user

<br />                          *** Start of LogFile ***<br />  Oracle Database Upgrade Diagnostic Utility       07-01-2008 12:17:30<br />===============<br />Database Uptime<br />===============<br />15:16 30-JUN-08<br />=================<br />Database Wo

Locked Sessions and Locked ObjectsPROMPT Blocked and Blocker Sessionscol blocker_sid format 99999999999col blocked_sid format 99999999999col min_blocked format 99999999999col request format 9999999select /*+ ORDERED */blocker.sid blocker_sid

Identify database idle sessionsset linesize 140col username format a15col idle format a15col program format a30PROMPT Enter the number of minutes for which the sessions should have been idle:PROMPTselectsid,username,status,to_char(logon

Script – Datafiles with highest I/O activitycol name format a40set linesize 140select * from (select name,phyrds, phywrts,readtim,writetimfrom v$filestat a, v$datafile bwhere a.file#=b.file#order by readtim desc) where rownum <6;

Script – Sessions Waiting On A Particular Wait EventSELECT count(*), eventFROM v$session_waitWHERE wait_time = 0AND event NOT IN ('smon timer','pipe get','wakeup time manager','pmon timer','rdbms ipc message', 'SQL*Net message from client')GROUP

Script – What Wait Events Are Sessions Waiting Onset linesize 120col username format a10col event format a30 select sid, serial#,username, event,seconds_in_wait, wait_timefrom v$session where state = 'WAITING'and wait_class != 'Idle'order b

Script – List status of all submitted DBMS jobsset pagesize 100set linesize 120ttitle - center 'Submitted DBMS Jobs' skip 2col job format 99999 heading 'job#'col subu format a10 heading 'Submitter' trunccol lsd format a5 heading '

Script – Top SQL (Physical Reads)<br />This script will list the top 5 SQL statements sorted by the most number of physical readsset serverout on size 1000000<br />set feedback off<br />declare<br />top5 number;<br />text1 varchar2(4000);<br />x