Some Admin Queries with CDC


sqlplus /nolog
connect / as sysdba (or connect sys/password@SID as sysdba)

set linesize 1000
set pagesize 1000

Which CHANGE TABLES (of which SOURCE TABLES) are present in which CHANGE SET?
SELECT CHANGE_TABLE_NAME, CHANGE_SET_NAME, SOURCE_TABLE_NAME FROM CHANGE_TABLES;

SELECT CHANGE_TABLE_SCHEMA, CHANGE_TABLE_NAME, CHANGE_SET_NAME, SOURCE_SCHEMA_NAME, SOURCE_TABLE_NAME FROM CHANGE_TABLES;

Which SUBCRIBER VIEWS, CHANGE SETS AND SUBSCRIPTIONS are present?
SELECT SOURCE_SCHEMA_NAME, SOURCE_TABLE_NAME, VIEW_NAME, CHANGE_SET_NAME, SUBSCRIPTION_NAME FROM DBA_SUBSCRIBED_TABLES;

Find some info on RSID$
SELECT OPERATION$,CSCN$,RSID$,TIMESTAMP$ from SUBSCRIBER_VIEW;

OTHER QUERIES:
SELECT * FROM DBA_SOURCE_TABLES;

SELECT SET_NAME,CHANGE_SOURCE_NAME,BEGIN_SCN,END_SCN,CAPTURE_ENABLED,PURGING,QUEUE_NAME FROM CHANGE_SETS;

SELECT OWNER, QUEUE_TABLE, TYPE, OBJECT_TYPE, RECIPIENTS FROM DBA_QUEUE_TABLES;

SELECT SET_NAME,STATUS,EARLIEST_SCN,LATEST_SCN,to_char(LAST_PURGED, 'DD-MM-YYYY;HH24:MI'), to_char(LAST_EXTENDED, 'DD-MM-YYYY;HH24:MI'),SUBSCRIPTION_NAME
FROM DBA_SUBSCRIPTIONS; 
-- is a Subscription defined?

SELECT * FROM DBA_PUBLISHED_COLUMNS; -- is there a Publication defined and with which columns?

SELECT PROPAGATION_SOURCE_NAME, PROPAGATION_NAME, STAGING_DATABASE, DESTINATION_QUEUE FROM CHANGE_PROPAGATIONS;

SELECT tablespace_name, force_logging FROM dba_tablespaces;

SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all, force_logging FROM gv$database;

SELECT owner, name, QUEUE_TABLE, ENQUEUE_ENABLED, DEQUEUE_ENABLED FROM dba_queues;

SELECT capture_name, total_messages_captured, total_messages_enqueued, elapsed_enqueue_time FROM dba_hist_streams_capture;

SELECT apply_name, reader_total_messages_dequeued, reader_lag, server_total_messages_applied FROM dba_hist_streams_apply_sum;

SELECT table_name, scn, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all FROM dba_capture_prepared_tables;

SELECT table_name, scn, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all FROM dba_capture_prepared_tables;

SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual; -- If you want to know the current SCN

SELECT EQ_NAME,EQ_TYPE,TOTAL_WAIT#,FAILED_REQ#,CUM_WAIT_TIME,REQ_DESCRIPTION FROM V_$ENQUEUE_STATISTICS WHERE CUM_WAIT_TIME>0 ;

SELECT set_name,capture_name,queue_name,queue_table_name,capture_enabled FROM cdc_change_sets$;

SELECT set_name,capture_name,capture_enabled FROM cdc_change_sets$;

SELECT set_name, CAPTURE_ENABLED, BEGIN_SCN, END_SCN,LOWEST_SCN,CAPTURE_ERROR FROM cdc_change_sets$;

SELECT set_name, change_source_name, capture_enabled, stop_on_ddl, publisher FROM change_sets;

SELECT subscription_name, handle, set_name, username, earliest_scn, description FROM cdc_subscribers$;

SELECT username FROM dba_users u, streams$_privileged_user s WHERE u.user_id = s.user#;

SELECT cap.CAPTURE_NAME, cap.FIRST_SCN, cap.APPLIED_SCN, cap.REQUIRED_CHECKPOINT_SCN FROM DBA_CAPTURE cap, CHANGE_SETS cset WHERE cset.SET_NAME = 'CDC_DEMO_SET' AND cap.CAPTURE_NAME = cset.CAPTURE_NAME;

SELECT r.SOURCE_DATABASE,r.SEQUENCE#,r.NAME,r.DICTIONARY_BEGIN,r.DICTIONARY_END FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c WHERE c.CAPTURE_NAME = 'CDC$C_CHANGE_SET_ALBERT' AND r.CONSUMER_NAME = c.CAPTURE_NAME;

SELECT CONSUMER_NAME,PURGEABLE,THREAD#, FIRST_SCN,NEXT_SCN, SEQUENCE# FROM DBA_REGISTERED_ARCHIVED_LOG

SELECT * FROM dba_streams_administrator;

SELECT CHANGE_SET_NAME, SOURCE_TABLE_NAME from DBA_SUBSCRIBED_TABLES;

SELECT SUBSCRIPTION_NAME, SET_NAME, USERNAME, CREATED, STATUS FROM DBA_SUBSCRIPTIONS;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: