Lists all locked objects for whole RAC

COLUMN owner FORMAT A20 COLUMN username FORMAT A20 COLUMN object_owner FORMAT A20 COLUMN object_name FORMAT A30 COLUMN locked_mode FORMAT A15 SELECT b.inst_id, b.session_id AS sid, NVL(b.oracle_username, ‘(oracle)’) AS username, a.owner AS object_owner, a.object_name, Decode(b.locked_mode, 0, ‘None’, 1, ‘Null (NULL)’, 2, ‘Row-S (SS)’, 3, ‘Row-X (SX)’, 4, ‘Share (S)’, 5, ‘S/Row-X (SSX)’, 6, ‘Exclusive (X)’, b.locked_mode) […]

Read More

FIND THE ARCHIVE LAG BETWEEN PRIMARY AND STANDBY

select LOG_ARCHIVED-LOG_APPLIED “LOG_GAP” from (SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’), (SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’);

Read More

LAST SEQUENCE RECEIVED AND LAST SEQUENCE APPLIED

SQL> SELECT al.thrd “Thread”, almax “Last Seq Received”, lhmax “Last Seq Applied” FROM (select thread# thrd, MAX(sequence#) almax FROM v$archived_log WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax FROM v$log_history WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd;  Thread Last Seq Received Last Seq […]

Read More

CHECK THE MESSAGES/ERRORS IN STANDBY DATABASE

set pagesize 2000 set lines 2000 col MESSAGE for a90 select message,timestamp from V$DATAGUARD_STATUS where timestamp > sysdate – 1/6; MESSAGE TIMESTAMP —————————————————————————————— ——— RFS[48]: No standby redo logfiles created 05-AUG-15 Media Recovery Log /uv1010/arch/MRSX/arch_MRSX_779539386_1_49481.log 05-AUG-15 Media Recovery Waiting for thread 1 sequence 49482 (in transit) 05-AUG-15 RFS[48]: No standby redo logfiles created 05-AUG-15 Media […]

Read More

CHECK THE NUMBER OF ARCHIVES GETTING GENERATING ON HOURLY BASIS:

SELECT TO_CHAR(TRUNC(FIRST_TIME),’Mon DD’) “DG Date”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’00’,1,0)),’9999′) “12AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’01’,1,0)),’9999′) “01AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’02’,1,0)),’9999′) “02AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’03’,1,0)),’9999′) “03AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’04’,1,0)),’9999′) “04AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’05’,1,0)),’9999′) “05AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’06’,1,0)),’9999′) “06AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’07’,1,0)),’9999′) “07AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’08’,1,0)),’9999′) “08AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’09’,1,0)),’9999′) “09AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’10’,1,0)),’9999′) “10AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’11’,1,0)),’9999′) “11AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’12’,1,0)),’9999′) “12PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’13’,1,0)),’9999′) “1PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’14’,1,0)),’9999′) “2PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’15’,1,0)),’9999′) “3PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’16’,1,0)),’9999′) “4PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’17’,1,0)),’9999′) “5PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’18’,1,0)),’9999′) “6PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’19’,1,0)),’9999′) “7PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’20’,1,0)),’9999′) “8PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’21’,1,0)),’9999′) “9PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’22’,1,0)),’9999′) “10PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’23’,1,0)),’9999′) “11PM” FROM V$LOG_HISTORY […]

Read More

Size of Oracle Database

Read More

Display the columns related to redaction in Oracle 12c

Read More

Number of archived redo by day

SELECT TRUNC(first_time) AS day, ROUND(SUM(blocks * block_size)/1024/1024/1024,2) size_gb FROM v$archived_log WHERE TRUNC(first_time) >= TRUNC(SYSDATE) – &1 GROUP BY TRUNC(first_time) ORDER BY TRUNC(first_time);

Read More

Shows differences in table structures

Read More