Loading....

Script – Temporary tablespace usage

— 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

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

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

Identify active transactions in undo and rollback segments

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

Locked Sessions and Locked Objects

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

Identify database idle sessions

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; Please follow […]

List all tablespaces with free space < 10%

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

Monitor the Flashback area space usage

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; Please follow and like us:

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

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

Monitor space used in ASM Disk Groups

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

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

Query the RMAN catalog to check backup status

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

Using DBMS_SCHEDULER to run a UNIX shell script

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

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?