Archive

Archive for the ‘SQL’ Category

show snapshot dependency between master and slave

May 25, 2011 Leave a comment

–qmlogs.sql
–RUN AT MASTER SITE
COL LOG_OWNER FORMAT A8
COL OWNER FORMAT A10
COL MASTER FORMAT A25
COL NAME FORMAT A32
COL LOG_TABLE FORMAT A30
COL SNAPSHOT_SITE FORMAT A24
SELECT UNIQUE
*
FROM
((SELECT
OWNER
,NAME
,SNAPSHOT_SITE
,CAN_USE_LOG
,REFRESH_METHOD
, LOG_OWNER
,LOG_TABLE
,ROWIDS
,PRIMARY_KEY
,CURRENT_SNAPSHOTS
,A.SNAPSHOT_ID SID
FROM user_snapshot_logs A
, user_REGISTERED_SNAPSHOTS B
WHERE B.SNAPSHOT_ID = A.SNAPSHOT_ID(+)
)
UNION
(SELECT
OWNER
,NAME
,SNAPSHOT_SITE
,CAN_USE_LOG
,REFRESH_METHOD
, LOG_OWNER
,LOG_TABLE
,ROWIDS
,PRIMARY_KEY
,CURRENT_SNAPSHOTS
,A.SNAPSHOT_ID SID
FROM user_snapshot_logs A
, user_REGISTERED_SNAPSHOTS B
WHERE A.SNAPSHOT_ID = B.SNAPSHOT_ID(+))
ORDER BY 1,2,3)
/

Categories: SQL