Script – List all recent DDL modifications

Posted by Gavin Soorma
On July 1, 2009
0 Comments

set pagesize 200 colsep ' ' col Owner format a20 col Object_Name format a30 col Last_DDL format a20 break on Owner select Owner, Object_type Object_Name, Timestamp Last_DDL from DBA_OBJECTS where SysDate - TO_DATE(TimeStamp,'YYYY-MM-DD:HH24:MI:SS') < 1 order by Owner, Object_Name /

Posted by Gavin Soorma
On July 1, 2009
0 Comments

set pagesize 300 select sid,serial#,username,status from v$session WHERE USERNAME IS NOT NULL order by STATUS DESC; col sid format 999 col owner format a10 col object format a20 col type format a10 set linesize 300 /* ENTER THE SID TO CHECK */ select sid,owner,object,type from v$access where sid = &sid order by owner,type ;

Posted by Arjun Raja
On June 30, 2009
3 Comments

— Listing of temp segments.— 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 […]

Monitor long running operations using v$session_longops

Posted by Gavin Soorma
On June 30, 2009
0 Comments

SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) COMPLETE FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK order by 1; Note: the same query can be used to monitor RMAN backup status SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != […]

Monitor Data Guard Log Shipping

Posted by Gavin Soorma
On June 30, 2009
0 Comments

Note: This query needs to be run on the Primary database. SET PAGESIZE 124 COL DB_NAME FORMAT A8 COL HOSTNAME FORMAT A12 COL LOG_ARCHIVED FORMAT 999999 COL LOG_APPLIED FORMAT 999999 COL LOG_GAP FORMAT 9999 COL APPLIED_TIME FORMAT A12 SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP FROM ( SELECT NAME DB_NAME FROM V$DATABASE ), ( SELECT […]

Posted by Gavin Soorma
On June 29, 2009
0 Comments

col o format a10 col u format a10 select osuser o, username u, sid, segment_name s, substr(sa.sql_text,1,200) txt from v$session s, v$transaction t, dba_rollback_segs r, v$sqlarea sa where s.taddr=t.addr and t.xidusn=r.segment_id(+) and s.sql_address=sa.address(+) And substr(sa.sql_text,1,200) is not null order by 3; col name format a8 col username format a8 col osuser format a8 col start_time […]

Posted by Gavin Soorma
On June 29, 2009
0 Comments

PROMPT Blocked and Blocker Sessions col blocker_sid format 99999999999 col blocked_sid format 99999999999 col min_blocked format 99999999999 col request format 9999999 select /*+ ORDERED */ blocker.sid blocker_sid , blocked.sid blocked_sid , TRUNC(blocked.ctime/60) min_blocked , blocked.request from (select * from v$lock where block != 0 and type = 'TX') blocker , v$lock blocked where blocked.type='TX' and […]

Posted by Gavin Soorma
On June 29, 2009
0 Comments

set linesize 140 col username format a15 col idle format a15 col program format a30 PROMPT Enter the number of minutes for which the sessions should have been idle: PROMPT select sid,username,status, to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON", floor(last_call_et/3600)||':'|| floor(mod(last_call_et,3600)/60)||':'|| mod(mod(last_call_et,3600),60) "IDLE", program from v$session where type='USER' and (LAST_CALL_ET / 60) > &minutes order by last_call_et;

Posted by Gavin Soorma
On June 29, 2009
0 Comments

col sid format 999999 col username format a20 col osuser format a15 select b.spid,a.sid, a.serial#,a.username, a.osuser from v$session a, v$process b where a.paddr= b.addr and b.spid='&spid' order by b.spid;

Identify OS process ID based on database SID

Posted by Gavin Soorma
On June 29, 2009
0 Comments

col sid format 999999 col username format a20 col osuser format a15 select a.sid, a.serial#,a.username, a.osuser, b.spid from v$session a, v$process b where a.paddr= b.addr and a.sid='&sid' order by a.sid;

List all tablespaces with free space < 10%

Posted by Gavin Soorma
On June 29, 2009
0 Comments

set pagesize 300 set linesize 100 column tablespace_name format a15 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(Kb)' column Pct_Free format 999.99 heading '% Free' column Max_Free format 999,999,999 heading 'Max […]

Posted by Gavin Soorma
On June 29, 2009
0 Comments

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE; SELECT * FROM V$RECOVERY_FILE_DEST; col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999 col round(space_used/1048576) heading "Space Used (MB)" format 99999 col name format a40 select name, round(space_limit/1048576),round(space_used/1048576) from v$RECOVERY_FILE_DEST;

Posted by Gavin Soorma
On June 29, 2009
0 Comments

select count(*) child_count, sum(gets) sum_gets, sum(misses) sum_misses, sum(sleeps) sum_sleeps from v$latch_children where name = 'cache buffers chains'; select p1 "File #". p2 "Block #" from v$session_wait where event ='cache buffer chains'; select owner, segment_name, segment_type from dba_extents where file_id = &P1 and &P2 between block_id and block_id + blocks -1; col object_name format a20 col […]

Posted by Gavin Soorma
On June 29, 2009
2 Comments

SET LINESIZE 145 SET PAGESIZE 9999 SET VERIFY off COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name' COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size' COLUMN block_size FORMAT 99,999 HEAD 'Block|Size' COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size' COLUMN state FORMAT a11 HEAD 'State' COLUMN type FORMAT a6 HEAD 'Type' COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)' […]

Determing the optimal UNDO tablespace size

Posted by Gavin Soorma
On June 24, 2009
0 Comments

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 […]

Posted by Gavin Soorma
On June 23, 2009
1 Comments

This script will query the RMAN catalog and report on the backup status of every database in the 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 […]

Posted by Gavin Soorma
On June 20, 2009
2 Comments

select trunc(completion_time) rundate ,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;

Posted by Gavin Soorma
On June 20, 2009
0 Comments

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 is maintained by the view DEPENDENCY$ in the SYS schema. This script was taken by me from […]

Posted by Gavin Soorma
On June 18, 2009
1 Comments

CREATE A PROGRAM begin dbms_scheduler.create_program ( program_name => ‘UPDATE_VERITAS’, program_type => ‘EXECUTABLE’, program_action => ‘/opt/oracle/scripts/blade08/update_veritas.sh’, enabled => TRUE, comments => ‘Check the Time’ ); end; / CREATE A SCHEDULE begin dbms_scheduler.create_schedule ( schedule_name => ‘EVERY_6_HOURS’, repeat_interval => ‘FREQ=HOURLY; INTERVAL=6?, comments => ‘Every 6 Hours’ ); end; / CREATE A JOB begin dbms_scheduler.create_job […]

Posted by Gavin Soorma
On June 18, 2009
0 Comments

CREATE OR REPLACE TRIGGER manage_OCIservice after startup on database DECLARE role VARCHAR(30); BEGIN SELECT DATABASE_ROLE INTO role FROM V$DATABASE; IF role = ‘PRIMARY’ THEN DBMS_SERVICE.START_SERVICE(‘apex_dg’); ELSE DBMS_SERVICE.STOP_SERVICE(‘apex_dg’); END IF; END; /

Page 2 of 3123

News

Script – List all recent DDL modifications

Script – List Objects being accessed by a particular SID

Script – Temporary tablespace usage

Monitor long running operations using v$session_longops

Monitor Data Guard Log Shipping

Identify active transactions in undo and rollback segments

Locked Sessions and Locked Objects

Identify database idle sessions

Identify database SID based on OS Process ID

Identify OS process ID based on database SID

List all tablespaces with free space < 10%

Monitor the Flashback area space usage

Identify 'hot' objects when faced with 'Cache Buffers Chains Latch' wait event

Monitor space used in ASM Disk Groups

Determing the optimal UNDO tablespace size

Query the RMAN catalog to check backup status

Script- Track redo generation by day

Recompiling Objects based on dependencies

Using DBMS_SCHEDULER to run a UNIX shell script

Trigger to use with Data Guard to change service name