Monthly Archives: March 2013

Find LOB object parent table

Some times you find that there are some BIG lob segments and you don’t know which table they belong to…

Here’s the solution:

SELECT TABLE_NAME, column_name
FROM   user_lobs
WHERE  segment_name = (SELECT object_name
                       FROM   user_objects
                       WHERE  object_name = UPPER('&object_name')
                       AND    object_type = 'LOB');

in MacLochlainns Weblog

I hope it was useful!!!
Cheers

Recover GAP on Logical Standby with errors on apply

  • Verify logstandby background processes
SELECT PID, TYPE, STATUS, HIGH_SCN FROM V$LOGSTDBY;
  • Start logstandby apply processes
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
  • If the processes stop, verify the logstandby events and generate the script to skip the transaction if necessary

NOTE: If it’s your production environment, please check with application development team the transactions with error.

set lines 200 pages 200
col status for a30
col script for a50
SELECT to_char(EVENT_TIME,'dd-mm-yyyy hh24:mi:ss'), XIDUSN, XIDSLT, XIDSQN, STATUS, STATUS_CODE,
'EXEC DBMS_LOGSTDBY.SKIP_TRANSACTION ('||XIDUSN||','||XIDSLT||','||XIDSQN||');' "SCRIPT"
FROM DBA_LOGSTDBY_EVENTS WHERE EVENT_TIME = (SELECT MAX(EVENT_TIME) FROM DBA_LOGSTDBY_EVENTS);
  • Skip Transaction
EXEC DBMS_LOGSTDBY.SKIP_TRANSACTION (XIDUSN=>10,XIDSLT=>40,XIDSQN=>36784);

The script above is generated on logstandby events query.

I hope it was useful!!!
Cheers

Optimization WordPress Plugins & Solutions by W3 EDGE