Loading....

Script – Monitor Flashback Logs

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

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

Redo Log File Switches – By hour of the day

prompt prompt “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′)”08″, to_char(sum(decode(to_char(first_time,’HH24′),’09’,1,0)),’000′)”09″, to_char(sum(decode(to_char(first_time,’HH24′),’10’,1,0)),’000′)”10″, to_char(sum(decode(to_char(first_time,’HH24′),’11’,1,0)),’000′)”11″, to_char(sum(decode(to_char(first_time,’HH24′),’12’,1,0)),’000′)”12″, to_char(sum(decode(to_char(first_time,’HH24′),’13’,1,0)),’000′)”13″, to_char(sum(decode(to_char(first_time,’HH24′),’14’,1,0)),’000′)”14″, to_char(sum(decode(to_char(first_time,’HH24′),’15’,1,0)),’000′)”15″, to_char(sum(decode(to_char(first_time,’HH24′),’16’,1,0)),’000′)”16″, to_char(sum(decode(to_char(first_time,’HH24′),’17’,1,0)),’000′)”17″, to_char(sum(decode(to_char(first_time,’HH24′),’18’,1,0)),’000′)”18″ from v$log_history WHERE TRUNC(FIRST_TIME) > TRUNC(SYSDATE) – 7 group by to_char(first_time,’DD/MON’); prompt prompt Prompt “Evening ……..” prompt select to_char(first_time,’DD/MON’) day, to_char(sum(decode(to_char(first_time,’HH24′),’19’,1,0)),’000′)”19″, to_char(sum(decode(to_char(first_time,’HH24′),’20’,1,0)),’000′)”20″, to_char(sum(decode(to_char(first_time,’HH24′),’21’,1,0)),’000′)”21″, to_char(sum(decode(to_char(first_time,’HH24′),’22’,1,0)),’000′)”22″, to_char(sum(decode(to_char(first_time,’HH24′),’23’,1,0)),’000′)”23″, to_char(sum(decode(to_char(first_time,’HH24′),’00’,1,0)),’000′) “00”, to_char(sum(decode(to_char(first_time,’HH24′),’01’,1,0)),’000′)”01″, to_char(sum(decode(to_char(first_time,’HH24′),’02’,1,0)),’000′)”02″, to_char(sum(decode(to_char(first_time,’HH24′),’03’,1,0)),’000′)”03″, to_char(sum(decode(to_char(first_time,’HH24′),’04’,1,0)),’000′)”04″, to_char(sum(decode(to_char(first_time,’HH24′),’05’,1,0)),’000′)”05″, to_char(sum(decode(to_char(first_time,’HH24′),’06’,1,0)),’000′)”06″ from v$log_history WHERE […]

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 ?