# S230STC.def: Collects Streams Configuration Information # $Id: S230STC.def,v 1.16 2008/02/25 17:32:55 mschenke Exp $ # ARCS: $Header: /home/cvs/repository/RDA_4/src/scripting/lib/modules/S230STC.def,v 1.16 2008/02/25 17:32:55 mschenke Exp $ # # Change History # 20080225 MSC Change the copyright notice. =head1 NAME S230STC - Collects Streams Configuration Information =head1 DESCRIPTION This module collects Streams configuration-related information (for Oracle 9i Release 2 or later). The following reports can be generated and are regrouped under C. =cut if !getSetting('DATABASE_INSTALLED') return echo tput('bold'),'Processing STC module ...',tput('off') # Initialization var $TOC = '%TOC%' var $TOP = '[[#Top][Back to top]]' toc '1:Streams Configuration' # Load the common macros run DBinfo() # Obtain the database version var $ORACLE_VERSION = get_db_version() if !match($ORACLE_VERSION,'^(92|10|11)') {report not_applicable write 'The Streams Configuration module can only be executed on 9i Release 2 \ or higher.' toc '2:[[',getFile(),'][rda_report][Not Applicable]]' return } =head2 no_privileges - Insufficient Privileges This module needs to be run as a SYSDBA user. =cut if !getSetting('SQL_SYSDBA') {report no_privileges write 'The Streams Configuration module requires you to connect as a SYSDBA \ user. This can be setup during the DB module setup.' toc '2:[[',getFile(),'][rda_report][Insufficient Privileges]]' return } =head2 db - General Information Provides the information from different C views. =cut debug ' Inside the STC module, gathering general information' report db var $TTL = '---+!! General Information' if match($ORACLE_VERSION,'92') {var @TTL = ('',\ '---+ Database Information',\ '---+ Instance Information',\ '---+ Registry Information',\ '---+ NLS Information',\ '---+ Global Name',\ '---+ Key Init.ora Parameters',\ '---+ Streams Queues',\ '---+ Messages in Buffer Queue') var @HDR = ('',\ '| *DB Id*|*DB Name*|*Min Log*|*Pk Log*|*Ui Log*|\ *Force Log*|*Resetlogs Time*|*Log Mode*| *Archive Change#*|\ *Open Mode*|*Database Role*| *Archivelog Change#*|',\ '| *Instance*|*Name*|*Host*|*Version*|\ *Startup Time*|*Status*|*Parallel*|*Archiver*|\ *Logins*|*Instance Role*|*Shutdown*|*Active State*|',\ '|*Comp id*|*Component*|*Version*|*Status*|*Modified*|*Schema*|',\ '|*Parameter*|*Value*|',\ '|*Global Name*|',\ '|*Name*|*Value*|*Description*|',\ '|*Owner*|*Name*|*Queue Table*| *Owner Instance*|\ *Enqueue Enabled*|*Dequeue Enabled*|*User Comment*|',\ '|*Owner*|*Queue Name*|*Buffer Count*|') set $sql {SELECT '| ' || " dbid || '|' || " name || ' |' || " supplemental_log_data_min || ' |' || " supplemental_log_data_pk || ' |' || " supplemental_log_data_ui || ' |' || " force_logging || ' |' || " TO_CHAR(resetlogs_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " log_mode || ' | ' || " archive_change# || '|' || " open_mode || ' |' || " database_role || ' | ' || " archivelog_change# || '|' " FROM v$database; "PROMPT ___Macro_separator(2)___ "SELECT '| ' || " instance_number || '|' || " instance_name || ' |' || " host_name || ' |' || " version || ' |' || " TO_CHAR(startup_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " status || ' |' || " parallel || ' |' || " archiver || ' |' || " logins || ' |' || " instance_role || ' |' || " shutdown_pending || ' |' || " active_state || ' |' " FROM gv$instance; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " comp_id || ' |' || " comp_name || ' |' || " version || ' |' || " status || ' |' || " modified || ' |' || " schema || ' |' " FROM dba_registry; "PROMPT ___Macro_separator(4)___ "SELECT '|' || " parameter || ' |' || " value || ' |' " FROM nls_database_parameters; "PROMPT ___Macro_separator(5)___ "SELECT '|' || " global_name || ' |' " FROM global_name; "PROMPT ___Macro_separator(6)___ "SELECT '|' || " name || ' |' || " value || ' |' || " description || ' |' " FROM v$parameter " WHERE LOWER(name) IN ('aq_tm_processes','archive_lag_target','compatible', " 'global_names','job_queue_processes', " 'log_parallelism','logmnr_max_persistent_sessions', " 'parallel_max_servers','processes','sessions', " 'sga_max_size','shared_pool_size', " 'transaction_auditing','_first_spare_parameter', " '_kghdsidx_count'); "PROMPT ___Macro_separator(7)___ "SELECT '|' || " q.owner || ' |' || " q.name || ' |' || " t.queue_table || ' | ' || " t.owner_instance || '|' || " q.enqueue_enabled || ' |' || " q.dequeue_enabled || ' |' || " q.user_comment || ' |' " FROM dba_queues q,dba_queue_tables t " WHERE t.object_type = 'SYS.ANYDATA' " AND q.queue_table = t.queue_table " AND q.owner = t.owner; "PROMPT ___Macro_separator(8)___ "SELECT '|' || " q.owner || ' |' || " q.name || ' |' || " x.bufqm_nmsg || ' |' " FROM dba_queues q,x$bufqm x " WHERE x.bufqm_qid=q.qid; } } elsif match($ORACLE_VERSION,'101') {var @TTL = ('',\ '---+ Database Information',\ '---+ Instance Information',\ '---+ Registry Information',\ '---+ NLS Information',\ '---+ Global Name',\ '---+ Key Init.ora Parameters',\ '---+ Streams Queues',\ '---+ Messages in Buffer Queue') var @HDR = ('',\ '| *DB Id*|*DB Name*|*Min Log*|*Pk Log*|*Ui Log*|\ *FK Log*|*All Log*|*Force Log*|*Resetlogs Time*|*Log Mode*| \ *Archive Change#*|*Open Mode*|*Database Role*| \ *Archivelog Change#*|*Current Scn*|*Platform Id*|\ *Platform Name*|',\ '| *Instance*|*Name*|*Host*|*Version*|*Startup Time*|*Status*|\ *Parallel*|*Archiver*|*Logins*|*Instance Role*|*Shutdown*|\ *Active State*|',\ '|*Comp id*|*Component*|*Version*|*Status*|*Modified*|*Schema*|',\ '|*Parameter*|*Value*|',\ '|*Global Name*|',\ '|*Name*|*Value*|*Description*|',\ '|*Owner*|*Name*|*Queue Table*|*Enqueue Enabled*|\ *Dequeue Enabled*| *Primary Instance*| *Secondary Instance*| \ *Owner Instance*|*User Comment*|',\ '|*Queue*|*Startup Time*| *Msg Count*| *Spilled Msgs*| \ *Total Msgs*| *Total Spilled Msgs*|') set $sql {SELECT '| ' || " dbid || '|' || " name || ' |' || " supplemental_log_data_min || ' |' || " supplemental_log_data_pk || ' |' || " supplemental_log_data_ui || ' |' || " supplemental_log_data_fk || ' |' || " supplemental_log_data_all || ' |' || " force_logging || ' |' || " TO_CHAR(resetlogs_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " log_mode || ' | ' || " archive_change# || '|' || " open_mode || ' |' || " database_role || ' | ' || " archivelog_change# || '| ' || " current_scn || '| ' || " platform_id || '|' || " platform_name || ' |' " FROM v$database; "PROMPT ___Macro_separator(2)___ "SELECT '| ' || " instance_number || '|' || " instance_name || ' |' || " host_name || ' |' || " version || ' |' || " TO_CHAR(startup_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " status || ' |' || " parallel || ' |' || " archiver || ' |' || " logins || ' |' || " instance_role || ' |' || " shutdown_pending || ' |' || " active_state || ' |' " FROM gv$instance; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " comp_id || ' |' || " comp_name || ' |' || " version || ' |' || " status || ' |' || " modified || ' |' || " schema || ' |' " FROM dba_registry; "PROMPT ___Macro_separator(4)___ "SELECT '|' || " parameter || ' |' || " value || ' |' " FROM nls_database_parameters; "PROMPT ___Macro_separator(5)___ "SELECT '|' || " global_name || ' |' " FROM global_name; "PROMPT ___Macro_separator(6)___ "SELECT '|' || " name || ' |' || " value || ' |' || " description || ' |' " FROM v$parameter " WHERE LOWER(name) IN ('aq_tm_processes','archive_lag_target','compatible', " 'global_names','job_queue_processes', " 'log_parallelism','logmnr_max_persistent_sessions', " 'parallel_max_servers','processes', " 'sessions','shared_pool_size', " 'sga_max_size','streams_pool_size','_kghdsidx_count'); "PROMPT ___Macro_separator(7)___ "SELECT '|' || " q.owner || ' |' || " q.name || ' |' || " t.queue_table || ' |' || " q.enqueue_enabled || ' |' || " q.dequeue_enabled || ' |' || " t.primary_instance || ' |' || " t.secondary_instance || ' |' || " t.owner_instance || ' |' || " q.user_comment || ' |' " FROM dba_queues q,dba_queue_tables t " WHERE t.object_type = 'SYS.ANYDATA' " AND q.queue_table = t.queue_table " AND q.owner = t.owner; "PROMPT ___Macro_separator(8)___ "SELECT '|' || " queue_schema || '.' || queue_name || ' |' || " TO_CHAR(startup_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " num_msgs || '| ' || " spill_msgs || '| ' || " cnum_msgs || '| ' || " cspill_msgs || '|' " FROM gv$buffered_queues; } } elsif match($ORACLE_VERSION,'^(102|11)') {var @TTL = ('',\ '---+ Database Information',\ '---+ Instance Information',\ '---+ Registry Information',\ '---+ NLS Information',\ '---+ Global Name',\ '---+ Key Init.ora Parameters',\ '---+ Streams Queues') var @HDR = ('',\ '| *DB Id*|*DB Name*|*Min Log*|*Pk Log*|*Ui Log*|\ *FK Log*|*All Log*|*Force Log*|*Resetlogs Time*|*Log Mode*| \ *Archive Change#*|*Open Mode*|*Database Role*| \ *Archivelog Change#*| *Current Scn*|*Platform Id*|\ *Platform Name*|',\ '| *Instance*|*Name*|*Host*|*Version*|*Startup Time*|*Status*|\ *Parallel*|*Archiver*|*Logins*|*Instance Role*|*Shutdown*|\ *Active State*|',\ '|*Comp id*|*Component*|*Version*|*Status*|*Modified*|*Schema*|',\ '|*Parameter*|*Value*|',\ '|*Global Name*|',\ '|*Name*|*Value*|*Description*|',\ '|*Owner*|*Name*|*Queue Table*|*Enqueue Enabled*|\ *Dequeue Enabled*| *Primary Instance*| *Secondary Instance*| \ *Owner Instance*|*Network Name*|*User Comment*|') set $sql {SELECT '| ' || " dbid || '|' || " name || ' |' || " supplemental_log_data_min || ' |' || " supplemental_log_data_pk || ' |' || " supplemental_log_data_ui || ' |' || " supplemental_log_data_fk || ' |' || " supplemental_log_data_all || ' |' || " force_logging || ' |' || " TO_CHAR(resetlogs_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " log_mode || ' | ' || " archive_change# || '|' || " open_mode || ' |' || " database_role || ' | ' || " archivelog_change# || '| ' || " current_scn || '| ' || " platform_id || '|' || " platform_name || ' |' " FROM v$database; "PROMPT ___Macro_separator(2)___ "SELECT '| ' || " instance_number || '|' || " instance_name || ' |' || " host_name || ' |' || " version || ' |' || " TO_CHAR(startup_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " status || ' |' || " parallel || ' |' || " archiver || ' |' || " logins || ' |' || " instance_role || ' |' || " shutdown_pending || ' |' || " active_state || ' |' " FROM gv$instance; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " comp_id || ' |' || " comp_name || ' |' || " version || ' |' || " status || ' |' || " modified || ' |' || " schema || ' |' " FROM dba_registry; "PROMPT ___Macro_separator(4)___ "SELECT '|' || " parameter || ' |' || " value || ' |' " FROM nls_database_parameters; "PROMPT ___Macro_separator(5)___ "SELECT '|' || " global_name || ' |' " FROM global_name; "PROMPT ___Macro_separator(6)___ "SELECT '|' || " name || ' |' || " value || ' |' || " description || ' |' " FROM v$parameter " WHERE LOWER(name) IN ('aq_tm_processes','archive_lag_target', " 'job_queue_processes','_job_queue_interval', " 'shared_pool_size','sga_max_size','sga_target', " 'streams_pool_size','global_names','compatible', " 'log_parallelism','logmnr_max_persistent_sessions', " 'parallel_max_servers','processes','sessions'); "PROMPT ___Macro_separator(7)___ "SELECT '|' || " q.owner || ' |' || " q.name || ' |' || " t.queue_table || ' |' || " q.enqueue_enabled || ' |' || " q.dequeue_enabled || ' | ' || " t.primary_instance || '| ' || " t.secondary_instance || '| ' || " t.owner_instance || '|' || " network_name || ' |' || " q.user_comment || ' |' " FROM dba_queues q,dba_queue_tables t " WHERE t.object_type = 'SYS.ANYDATA' " AND q.queue_table = t.queue_table " AND q.owner = t.owner; } } if $sql {call separator(1) call writeSql($sql,2) call separator(0,'General Information') } =head2 capture_stats - Capture Process Information Provides information about the capture processes configured in the database. For each capture process, the streams name and associated queue name is presented along with the rule set name. =cut debug ' Inside the STC module, gathering capture process information' report capture_stats var $TTL = '---+!! Capture Process Information' if match($ORACLE_VERSION,'92') {var @TTL = ('',\ '---+ Capture Processes in Database',\ '---+ Capture Process Parameters',\ '---+ Streams Capture Rules-DBMS_STREAMS_ADM Package',\ '---+ Streams Table Subsetting Rules',\ '---+ Capture Rules by Rule Set',\ '---+ Capture Rule Transformations by Rule Set',\ '---+ Tables Prepared for Capture',\ '---+ Tables with Supplemental Logging',\ '---+ Table Level Supplemental Log Groups',\ '---+ Supplementally Logged Columns',\ '---+ Capture Statistics 1',\ '---+ Capture Statistics 2') var @HDR = ('',\ '|*Capture Name*|*Queue Owner*|*Queue Name*|*Rule Set Owner*|\ *Rule Set Name*|*Status*| *Start Scn*| *Captured Scn*| \ *Applied Scn*|',\ '|*Capture Name*|*Parameter*|*Value*|*Set By User*|',\ '|*Name*|*Object*|*Source Database*|*Type*|*Tagged Lcr*|*Rule*|',\ '|*Name*|*Object*|*Type*|*Rule*|*Dml Condition*|\ *Subsettng Operation*|',\ '|*Name*|*Rule Set*|*Rule Name*|*Condition*|',\ '|*Rule Set*|*Rule Name*|*Action Context Name*|\ *Action Context Value*|',\ '|*Table Owner*|*Table Name*| *Scn*|*Timestamp*|',\ '|*Owner*|*Table Name*|',\ '|*Object*|*Log Group Name*|*Always*|',\ '|*Object*|*Log Group Name*|*Column*| *Position*|',\ '|*Process Name*|*Capture Name*| *Sid*| *Serial No*|*State*|\ *Startup Time*| *Messages Captured*| *Messages Enqueued*|',\ '|*Capture Name*|*Current Time*|*Capture Time*| *Message No*|\ *Msg Create Time*| *Enqueue Msg No*|*Enqeue Msg Create Time*|\ *Enqueue Time*|') set $sql {SELECT '|' || " capture_name || ' |' || " queue_owner || ' |' || " queue_name || ' |' || " rule_set_owner || ' |' || " rule_set_name || ' |' || " status || ' | ' || " start_scn || '| ' || " captured_scn || '| ' || " applied_scn || '|' " FROM dba_capture; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " capture_name || ' |' || " parameter || ' |' || " value || ' |' || " set_by_user || ' |' " FROM dba_capture_parameters; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " streams_name || ' |' || " table_name || ' |' || " source_database || ' |' || " rule_type || ' TABLE RULE' || ' |' || " include_tagged_lcr || ' |' || " rule_owner || '.' || rule_name || ' |' " FROM dba_streams_table_rules " WHERE streams_type = 'CAPTURE' "UNION "SELECT '|' || " streams_name || ' |' || " schema_name || ' |' || " source_database || ' |' || " rule_type || ' SCHEMA RULE' || ' |' || " include_tagged_lcr || ' |' || " rule_owner || '.' || rule_name || ' |' " FROM dba_streams_schema_rules " WHERE streams_type = 'CAPTURE' "UNION "SELECT '|' || " streams_name || ' |' || " ' ' || ' |' || " source_database || ' |' || " rule_type || ' GLOBAL RULE' || ' |' || " include_tagged_lcr || ' |' || " rule_owner || '.' || rule_name || ' |' " FROM dba_streams_GLOBAL_rules " WHERE streams_type = 'CAPTURE'; "PROMPT ___Macro_separator(4)___ "SELECT '|' || " table_owner ||'.' || table_name || ' |' || " rule_type || ' TABLE RULE' || ' |' || " source_database || ' |' || " rule_owner || '.' || rule_name || ' |' || " dml_condition || ' |' || " subsetting_operation || ' |' " FROM dba_streams_table_rules " WHERE streams_type = 'CAPTURE' " AND (dml_condition IS NOT NULL OR subsetting_operation IS NOT NULL); "PROMPT ___Macro_separator(5)___ "SET long 65536 "SELECT '[[[' || CHR(10) || '|' || " c.capture_name || ' |' || " rsr.rule_set_owner || '.' || rsr.rule_set_name || ' |' || " rsr.rule_owner || '.' || rsr.rule_name || ' |' || " REPLACE(REPLACE(REPLACE(LTRIM(r.rule_condition), " '|','|'), " '<','<'), " '>','>') || '|' || " CHR(10) || ']]]' " FROM dba_rule_set_rules rsr,dba_rules r,dba_capture c " WHERE rsr.rule_name = r.rule_name " AND rsr.rule_owner = r.rule_owner " AND rsr.rule_set_owner = c.rule_set_owner " AND rsr.rule_set_name = c.rule_set_name " AND rsr.rule_set_name IN " (SELECT rule_set_name " FROM dba_capture) " ORDER BY rsr.rule_set_owner,rsr.rule_set_name; "PROMPT ___Macro_separator(6)___ "SELECT '|' || " rsr.rule_set_owner || '.' || rsr.rule_set_name || ' |' || " rsr.rule_owner || '.' || rsr.rule_name || ' |' || " ac.nvn_name || ' |' || " ac.nvn_value.accessvarchar2() || ' |' " FROM dba_rule_set_rules rsr,dba_rules r, " TABLE(r.rule_action_context.actx_list) ac "WHERE rsr.rule_name = r.rule_name " AND rsr.rule_owner = r.rule_owner " AND rule_set_name IN " (SELECT rule_set_name " FROM dba_capture) " ORDER BY rsr.rule_set_owner,rsr.rule_set_name; "PROMPT ___Macro_separator(7)___ "SELECT '|' || " table_owner || ' |' || " table_name || ' | ' || " scn || '|' || " timestamp || ' |' " FROM dba_capture_prepared_tables; "PROMPT ___Macro_separator(8)___ "SELECT '|' || " owner || ' |' || " table_name || ' |' " FROM dba_log_groups; "PROMPT ___Macro_separator(9)___ "SELECT '|' || " owner || '.' || table_name || ' |' || " log_group_name || ' |' || " DECODE(always,'ALWAYS','Unconditional',NULL,'Conditional') || ' |' " FROM dba_log_groups; "PROMPT ___Macro_separator(10)___ "SELECT '|' || " owner || '.' || table_name || ' |' || " log_group_name || ' |' || " column_name || ' | ' || " position || '|' " FROM dba_log_group_columns; "PROMPT ___Macro_separator(11)___ "SELECT '|' || " SUBSTR(s.program,INSTR(s.program,'(') + 1,4) || ' |' || " c.capture_name || ' | ' || " c.sid || '| ' || " c.serial# || '|' || " c.state || ' |' || " TO_CHAR(c.startup_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " c.total_messages_captured || '| ' || " c.total_messages_enqueued || '|' " FROM gv$streams_capture c,gv$session s " WHERE c.sid = s.sid " AND c.serial# = s.serial#; "PROMPT ___Macro_separator(12)___ "SELECT '|' || " capture_name || ' |' || " TO_CHAR(SYSDATE,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " capture_time || ' | ' || " capture_message_number || '|' || " TO_CHAR(capture_message_create_time, " 'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " enqueue_message_number || '|' || " TO_CHAR(enqueue_message_create_time, " 'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(enqueue_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM gv$streams_capture c; } } if match($ORACLE_VERSION,'101') {var @TTL = ('',\ '---+ Capture Processes in Database',\ '---+ Capture Process Source Information',\ '---+ Capture Process Parameters',\ '---+ Streams Capture Rules-DBMS_STREAMS_ADM Package',\ '---+ Streams Table Subsetting Rules',\ '---+ Capture Rules by Rule Set',\ '---+ Capture Rule Transformations by Rule Set',\ '---+ Registered Log Files for Capture',\ '---+ Capture Extra Attributes',\ '---+ Tables Prepared for Capture',\ '---+ Schemas Prepared for Capture',\ '---+ Database Prepared for Capture',\ '---+ Tables With Supplemental Logging',\ '---+ Table Level Supplemental Log Groups',\ '---+ Supplementally Logged Columns',\ '---+ Capture Statistics 1',\ '---+ Capture Statistics 2',\ '---+ Capture Statistics 3',\ '---+ Logminer Statistics',\ '---+ Buffered Publishers',\ '---+ Messaging Clients in Database',\ '---+ Messaging Client Notifications') var @HDR = ('',\ '|*Queue*|*Capture Type*|*Status*|*Rsn*|*Negative Rsn*|*Version*|\ *Logfile Assgn*|*Error No*|*Status Change Time*|*Error Msg*|',\ '|*Capture Name*|*Capture Type*|*Source Db*| *First Scn*| \ *Start Scn*| *Captured Scn*| *Applied Scn*| \ *Required Checkpoint Scn*| *Max Checkpoint Scn*| \ *Source Dbid*| *Source Resetlogs Scn*| \ *Source Resetlogs Time*| *Logminer Id*|',\ '|*Capture Name*|*Parameter*|*Value*|*Set By User*|',\ '|*Name*|*Object*|*Rule Set Type*|*Source Db*|*Type*|\ *Include Tagged Lcr*|*Rule*|',\ '|*Name*|*Object*|*Type*|*Rule*|*Dml Condition*|\ *Subsetting Operation*|',\ '|*Capture Name*|*Rule Set*|*Rule Name*|*Condition*|',\ '|*Rule Set*|*Rule Name*|*Action Context Name*|\ *Action Context Value*|',\ '|*Consumer Name*|*Source Db*|*Seq*|*Name*|*First Scn*|\ *First Time*|*Modified Time*|*Dictionary Begin*|\ *Dictionary End*|',\ '|*Capture Name*|*Attribute Name*|*Include*|',\ '|*Table Owner*|*Table Name*| *Scn*|*Timestamp*|',\ '|*Schema Name*|*Timestamp*|',\ '|*Timestamp*|',\ '|*Owner*|*Table Name*|',\ '|*Object*|*Log Group Name*|*Always*|',\ '|*Object*|*Log Group Name*|*Column*| *Position*|',\ '|*Process Name*|*Capture Name*| *Sid*| *Serial No*|*State*|\ *Startup Time*| *Messages Captured*| *Messages Enqueued*|',\ '|*Capture Name*|*Current Time*|*Capture Process Ts*| \ *Message No*|*Msg Create Time*|*Enqueue Time*| \ *Enqueue Msg No*|*Enqeue Msg Create Time*| *Available Msg No*| \ *Available Msg Create Time*|',\ '|*Capture Name*| *Total Prefilter Discarded*| \ *Total Prefilter Kept*| *Total Prefilter Evaluations*| \ *Undecided*| *Total Full Evaluations*|',\ '|*Capture Name*|*Name*|*Value*|',\ '| *Inst Id*| *Queue Id*|*Queue Schema*|*Queue Name*|\ *Sender Name*|*Sender Address*|*Sender Protocol*| *Num Msgs*| \ *Cnum Msgs*| *Last Enqueued Msg*|',\ '|*Streams Name*|*Queue Owner*|*Queue Name*|*Rsn*|*Rsna*|',\ '|*Streams Name*|*Queue Owner*|*Queue Name*|*Notification Type*|\ *Notification Action*|') set $sql {SELECT '|' || " capture_name || ' |' || " queue_owner || '.' || queue_name || ' |' || " capture_type || ' |' || " status || ' |' || " rule_set_owner || '.' || rule_set_name || ' |' || " negative_rule_set_owner || '.' || negative_rule_set_name || ' |' || " version || ' |' || " logfile_assignment || ' | ' || " error_number || '|' || " TO_CHAR(status_change_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " error_message || ' |' " FROM dba_capture; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " capture_name || ' |' || " capture_type || ' |' || " source_database || ' | ' || " first_scn || '| ' || " start_scn || '| ' || " captured_scn || '| ' || " applied_scn || '| ' || " required_checkpoint_scn || '| ' || " max_checkpoint_scn || '| ' || " source_dbid || '| ' || " source_resetlogs_scn || '| ' || " source_resetlogs_time || '| ' || " logminer_id || '|' " FROM dba_capture; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " capture_name || ' |' || " parameter || ' |' || " value || ' |' || " set_by_user || ' |' " FROM dba_capture_parameters " ORDER BY capture_name,parameter; "PROMPT ___Macro_separator(4)___ "SELECT '|' || " streams_name || ' |' || " schema_name || '.' || object_name || ' |' || " rule_set_type || ' |' || " source_database || ' |' || " streams_rule_type || ' ' || rule_type || ' |' || " include_tagged_lcr || ' |' || " rule_owner || '.' || rule_name || ' |' " FROM dba_streams_rules " WHERE streams_type = 'CAPTURE' " ORDER BY streams_name,object_name,source_database,rule_set_type,rule_name; "PROMPT ___Macro_separator(5)___ "SELECT '|' || " streams_name || ' |' || " schema_name || '.' || object_name || ' |' || " rule_type || ' TABLE RULE' || ' |' || " rule_owner || '.' || rule_name || ' |' || " dml_condition || ' |' || " subsetting_operation || ' |' " FROM dba_streams_rules " WHERE streams_type = 'CAPTURE' " AND (dml_condition IS NOT NULL OR subsetting_operation IS NOT NULL); "PROMPT ___Macro_separator(6)___ "SET long 65536 "SELECT '[[[' || CHR(10) || '|' || " c.capture_name || ' |' || " rsr.rule_set_owner || '.' || rsr.rule_set_name || ' |' || " rsr.rule_owner || '.' || rsr.rule_name || ' |' || " REPLACE(REPLACE(REPLACE(LTRIM(r.rule_condition), " '|','|'), " '<','<'), " '>','>') || '|' || " CHR(10) || ']]]' " FROM dba_rule_set_rules rsr,dba_rules r,dba_capture c " WHERE rsr.rule_name = r.rule_name " AND rsr.rule_owner = r.rule_owner " AND rsr.rule_set_owner = c.rule_set_owner " AND rsr.rule_set_name = c.rule_set_name " AND rsr.rule_set_name IN ( " SELECT rule_set_name " FROM dba_capture) " ORDER BY rsr.rule_set_owner,rsr.rule_set_name; "PROMPT ___Macro_separator(7)___ "SELECT '|' || " rsr.rule_set_owner || '.' || rsr.rule_set_name || ' |' || " rsr.rule_owner || '.' || rsr.rule_name || ' |' || " ac.nvn_name || ' |' || " ac.nvn_value.accessvarchar2() || ' |' " FROM dba_rule_set_rules rsr,dba_rules r, " TABLE(r.rule_action_context.actx_list) ac " WHERE rsr.rule_name = r.rule_name " AND rsr.rule_owner = r.rule_owner " AND rule_set_name IN " (SELECT rule_set_name " FROM dba_capture) " ORDER BY rsr.rule_set_owner,rsr.rule_set_name; "PROMPT ___Macro_separator(8)___ "SELECT '|' || " r.consumer_name || ' |' || " r.source_database || ' | ' || " r.sequence# || '|' || " r.name || ' | ' || " r.first_scn || '|' || " TO_CHAR(r.first_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(r.modified_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " r.dictionary_begin || ' |' || " r.dictionary_end || ' |' " FROM dba_registered_archived_log r,dba_capture c " WHERE r.consumer_name = c.capture_name " ORDER BY r.source_database,r.consumer_name,r.first_scn; "PROMPT ___Macro_separator(9)___ "SELECT '|' || " capture_name || ' |' || " attribute_name || ' |' || " include || ' |' " FROM dba_capture_extra_attributes " ORDER BY capture_name; "PROMPT ___Macro_separator(10)___ "SELECT '|' || " table_owner || ' |' || " table_name || ' | ' || " scn || '|' || " timestamp || ' |' " FROM dba_capture_prepared_tables " ORDER BY table_owner,table_name; "PROMPT ___Macro_separator(11)___ "SELECT '|' || " schema_name || ' |' || " timestamp || ' |' " FROM dba_capture_prepared_schemas " ORDER BY schema_name; "PROMPT ___Macro_separator(12)___ "SELECT '|' || " timestamp || ' |' " FROM dba_capture_prepared_database; "PROMPT ___Macro_separator(13)___ "SELECT '|' || " owner || ' |' || " table_name || ' |' " FROM dba_log_groups; "PROMPT ___Macro_separator(14)___ "SELECT '|' || " owner || '.' || table_name || ' |' || " log_group_name || ' |' || " DECODE(always,'ALWAYS','Unconditional',NULL,'Conditional') || ' |' " FROM dba_log_groups; "PROMPT ___Macro_separator(15)___ "SELECT '|' || " owner || '.' || table_name || ' |' || " log_group_name || ' |' || " column_name || ' | ' || " position || '|' " FROM dba_log_group_columns; "PROMPT ___Macro_separator(16)___ "SELECT '|' || " SUBSTR(s.program,INSTR(s.program,'(') + 1,4) || ' |' || " c.capture_name || ' | ' || " c.sid || '| ' || " c.serial# || '|' || " c.state || ' |' || " TO_CHAR(c.startup_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " c.total_messages_captured || '| ' || " c.total_messages_enqueued || '|' " FROM gv$streams_capture c,gv$session s " WHERE c.sid = s.sid " AND c.serial# = s.serial#; "PROMPT ___Macro_separator(17)___ "SELECT '|' || " capture_name || ' |' || " TO_CHAR(SYSDATE,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " capture_time || ' | ' || " capture_message_number || '|' || " TO_CHAR(capture_message_create_time, " 'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " enqueue_time || ' | ' || " TO_CHAR(enqueue_time, " 'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " enqueue_message_number || '|' || " TO_CHAR(enqueue_message_create_time, " 'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " available_message_number || '|' || " TO_CHAR(available_message_create_time, " 'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM gv$streams_capture; "PROMPT ___Macro_separator(18)___ "SELECT '|' || " capture_name || ' | ' || " total_prefilter_discarded || '| ' || " total_prefilter_kept || '| ' || " total_prefilter_evaluations || '| ' || " (total_prefilter_evaluations - " (total_prefilter_kept + total_prefilter_discarded)) || '| ' || " total_full_evaluations || '|' " FROM gv$streams_capture; "PROMPT ___Macro_separator(19)___ "SELECT '|' || " c.capture_name || ' |' || " name || ' |' || " value || ' |' " FROM gv$streams_capture c,gv$logmnr_stats l " WHERE c.logminer_id = l.session_id " AND name IN ('bytes paged out', " 'bytes of redo processed', " 'microsecs spent in pageout'); "PROMPT ___Macro_separator(20)___ "SELECT '| ' || " inst_id || '| ' || " queue_id || '|' || " queue_schema || ' |' || " queue_name || ' |' || " sender_name || ' |' || " sender_address || ' |' || " sender_protocol || ' | ' || " num_msgs || '| ' || " cnum_msgs || '| ' || " last_enqueued_msg || '|' " FROM gv$buffered_publishers; "PROMPT ___Macro_separator(21)___ "SELECT '|' || " streams_name || ' |' || " queue_owner || ' |' || " queue_name || ' |' || " rule_set_name || ' |' || " negative_rule_set_name || ' |' " FROM dba_streams_message_consumers " ORDER BY queue_owner,queue_name,streams_name; "PROMPT ___Macro_separator(22)___ "SELECT '|' || " streams_name || ' |' || " queue_owner || ' |' || " queue_name || ' |' || " notification_type || ' |' || " notification_action || ' |' " FROM dba_streams_message_consumers " WHERE notification_type IS NOT NULL " ORDER BY queue_owner,queue_name,streams_name; } } if match($ORACLE_VERSION,'^(102|11)') {var @TTL = ('',\ '---+ Capture Processes in Database',\ '---+ Capture Process Source Information',\ '---+ Capture Process Parameters',\ '---+ Streams Capture Rules-DBMS_STREAMS_ADM Package',\ '---+ Streams Table Subsetting Rules',\ '---+ Capture Rules by Rule Set',\ '---+ Capture Rule Transformations by Rule Set',\ '---+ Registered Log Files for Capture',\ '---+ Capture Extra Attributes',\ '---+ Tables Prepared for Capture',\ '---+ Schemas Prepared for Capture',\ '---+ Database Prepared for Capture',\ '---+ Tables With Supplemental Logging',\ '---+ Table Level Supplemental Log Groups',\ '---+ Supplementally Logged Columns',\ '---+ Capture Statistics 1',\ '---+ Capture Statistics 2',\ '---+ Capture Statistics 3',\ '---+ Capture Statistics 4',\ '---+ Logminer Statistics',\ '---+ Buffered Publishers',\ '---+ Messaging Clients in Database',\ '---+ Messaging Client Notifications') var @HDR = ('',\ '|*Capture Name*|*Queue*|*Capture Type*|*Status*|*Rsn*|\ *Negative Rsn*|*Checkpoint Retention Time*|*Version*|\ *Logfile Assgn*|*Error No*|*Status Change Time*|*Error Msg*|',\ '|*Capture Name*|*Capture Type*|*Source Db*| *First Scn*| \ *Start Scn*| *Captured Scn*| *Applied Scn*| *Last Enqued Scn*| \ *Required Checkpoint Scn*| *Max Checkpoint Scn*| \ *Source Dbid*| *Source Resetlogs Scn*| \ *Source Resetlogs Time*| *Logminer Id*|',\ '|*Capture Name*|*Parameter*|*Value*|*Set By User*|',\ '|*Name*|*Object*|*Rule Set Type*|*Source Db*|*Type*|\ *Include Tagged Lcr*|*Rule*|',\ '|*Name*|*Object*|*Type*|*Rule*|*Dml Condition*|\ *Subsetting Operation*|',\ '|*Capture Name*|*Rule Set*|*Rule Name*|*Condition*|',\ '|*Rule Set*|*Rule Owner*|*Rule Name*|*Transform Type*|\ *From Schema Name*|*To Schema Name*|*From Table Name*|\ *To Table Name*|*Schema Name*|*Table Name*|*From Column Name*|\ *To Column Name*|*Column Name*|*Column Value*|*Column Type*|\ *Column Function*|*Value Type*|*User Function Name*|\ *Subsetting Operation*|*Dml Condition*|*Declarative Type*|\ *Precedence*|*Step Number*|',\ '|*Consumer Name*|*Source Db*| *Seq*|*Name*| *First Scn*|\ *First Time*| *Net Scn*|*Next Time*|*Modified Time*|\ *Dictionary Begin*|*Dictionary End*|*Purgeable*|',\ '|*Capture Name*|*Attribute Name*|*Include*|',\ '|*Table Owner*|*Table Name*| *Scn*|*Timestamp*|*Pk Log*|\ *Ui Log*|*Fk Log*|*All Log*|',\ '|*Schema Name*|*Timestamp*|*Pk Log*|*Ui Log*|*Fk Log*|\ *All Log*|',\ '|*Timestamp*|*Pk Log*|*Ui Log*|*Fk Log*|*All Log*|',\ '|*Owner*|*Table Name*|',\ '|*Object*|*Log Group Name*|*Log Group Type*|*Always*|',\ '|*Object*|*Log Group Name*|*Column*| *Position*|\ *Logging Property*|',\ '|*Process Name*|*Capture Name*|*Startup Time*| *Sid*| \ *Serial No*|*State*|*State Changed Time*| \ *Messages Captured*| *Messages Enqueued*| *Messgaes Created*|',\ '|*Capture Name*|*Current Time*|*Capture Process TS*| \ *Message No*|*Msg Create Time*|*Enqueue Time*| \ *Enqueue Msg No*|*Enqeue Msg Create Time*| *Available Msg No*| \ *Available Msg Create Time*|',\ '|*Capture Name*| *Total Prefilter Discarded*| \ *Total Prefilter Kept*| *Total Prefilter Evaluations*| \ *Undecided*| *Total Full Evaluations*|',\ '|*Capture Name*|*Elapsed Capture*|*Elapsed Rule*|\ *Elapsed Enqueue*|*Elapsed Lcr*|*Elapsed Redo*|\ *Elapsed Pause*| *Total Msgs Created*| \ *Total Full Evaluations*|',\ '|*Capture Name*|*Name*|*Value*|',\ '|*Inst Id*|*Queue Id*|*Queue Schema*|*Queue Name*|*Sender Name*|\ *Sender Address*|*Sender Protocol*| *Num Msgs*| *Cnum Msgs*| \ *Last Enqueued Msg*| *Unbrowsed Msgs*| *Overspilled Msgs*|\ *Memory Usage*|*Publisher State*|',\ '|*Streams Name*|*Queue Owner*|*Queue Name*|*Rsn*|*Rsna*|',\ '|*Streams Name*|*Queue Owner*|*Queue Name*|*Notification Type*|\ *Notification Action*|') set $sql {SET serveroutput on size 1000000 "SELECT '|' || " capture_name || ' |' || " queue_owner || '.' || queue_name || ' |' || " capture_type || ' |' || " status || ' |' || " rule_set_owner || '.' || rule_set_name || ' |' || " negative_rule_set_owner || '.' || negative_rule_set_name || ' |' || " checkpoint_retention_time || ' |' || " version || ' |' || " logfile_assignment || ' | ' || " error_number || '|' || " TO_CHAR(status_change_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " error_message || ' |' " FROM dba_capture; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " capture_name || ' |' || " capture_type || ' |' || " source_database || ' | ' || " first_scn || '| ' || " start_scn || '| ' || " captured_scn || '| ' || " applied_scn || '| ' || " last_enqueued_scn || '| ' || " required_checkpoint_scn || '| ' || " max_checkpoint_scn || '| ' || " source_dbid || '| ' || " source_resetlogs_scn || '| ' || " source_resetlogs_time || '| ' || " logminer_id || '|' " FROM dba_capture; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " capture_name || ' |' || " parameter || ' |' || " value || ' |' || " set_by_user || ' |' " FROM dba_capture_parameters " ORDER BY capture_name,parameter; "PROMPT ___Macro_separator(4)___ "SELECT '|' || " streams_name || ' |' || " schema_name || '.' || object_name || ' |' || " rule_set_type || ' |' || " source_database || ' |' || " streams_rule_type || ' ' || rule_type || ' |' || " include_tagged_lcr || ' |' || " rule_owner || '.' || rule_name || ' |' " FROM dba_streams_rules " WHERE streams_type = 'CAPTURE' " ORDER BY streams_name,object_name,source_database,rule_set_type,rule_name; "PROMPT ___Macro_separator(5)___ "SELECT '|' || " streams_name || ' |' || " schema_name || '.' || object_name || ' |' || " rule_type || ' TABLE RULE' || ' |' || " rule_owner || '.' || rule_name || ' |' || " dml_condition || ' |' || " subsetting_operation || ' |' " FROM dba_streams_rules " WHERE streams_type = 'CAPTURE' " AND (dml_condition IS NOT NULL OR subsetting_operation IS NOT NULL); "PROMPT ___Macro_separator(6)___ "SET long 65536 "SELECT '[[[' || CHR(10) || '|' || " c.capture_name || ' |' || " rsr.rule_set_owner || '.' || rsr.rule_set_name || ' |' || " rsr.rule_owner || '.' || rsr.rule_name || ' |' || " REPLACE(REPLACE(REPLACE(LTRIM(r.rule_condition), " '|','|'), " '<','<'), " '>','>') || '|' || " CHR(10) || ']]]' " FROM dba_rule_set_rules rsr,dba_rules r,dba_capture c " WHERE rsr.rule_name = r.rule_name " AND rsr.rule_owner = r.rule_owner " AND rsr.rule_set_owner = c.rule_set_owner " AND rsr.rule_set_name = c.rule_set_name " AND rsr.rule_set_name IN " (SELECT rule_set_name " FROM dba_capture) " ORDER BY rsr.rule_set_owner,rsr.rule_set_name; "PROMPT ___Macro_separator(7)___ "DECLARE " CURSOR c_apply IS " SELECT rsr.rule_set_owner, " rsr.rule_set_name, " r.rule_owner, " r.rule_name, " r.transform_type, " r.from_schema_name, " r.to_schema_name, " r.from_table_name, " r.to_table_name, " r.schema_name, " r.table_name, " r.from_column_name, " r.to_column_name, " r.column_name, " r.column_value, " r.column_type, " r.column_function, " r.value_type, " r.user_function_name, " r.subsetting_operation, " r.dml_condition, " r.declarative_type, " r.precedence, " r.step_number " FROM dba_rule_set_rules rsr,dba_streams_transformations r " WHERE r.rule_name (+)= rsr.rule_name " AND r.rule_owner (+)= rsr.rule_owner " AND rule_set_name IN " (SELECT rule_set_name " FROM dba_capture) " ORDER BY rsr.rule_set_owner,rsr.rule_set_name,r.rule_owner,r.rule_name, " r.transform_type DESC,r.step_number,r.precedence; " l_str VARCHAR2(100); " l_int PLS_INTEGER; "BEGIN " FOR r_rec IN c_apply LOOP " BEGIN " l_int := r_rec.column_value.getvarchar2(l_str); " EXCEPTION " WHEN OTHERS THEN " l_str := ''; " END; " dbms_output.put('|' || " r_rec.rule_set_owner ||'.'|| r_rec.rule_set_name || ' |' || " r_rec.rule_owner || ' |' || " r_rec.rule_name || ' |' || " r_rec.transform_type || ' |' || " r_rec.from_schema_name || ' |' || " r_rec.to_schema_name || ' |' || " r_rec.from_table_name || ' |' || " r_rec.to_table_name || ' |' || " r_rec.schema_name || ' |' || " r_rec.table_name || ' |' ); " dbms_output.put(r_rec.from_column_name || ' |' || " r_rec.to_column_name || ' |' || " r_rec.column_name || ' |' ); " dbms_output.put(l_str || ' |' || " r_rec.column_type || ' |' || " r_rec.column_function || ' |' || " r_rec.value_type || ' |' || " r_rec.user_function_name || ' |' || " r_rec.subsetting_operation || ' |' || " r_rec.dml_condition || ' |' || " r_rec.declarative_type || ' |' || " r_rec.precedence || ' |' || " r_rec.step_number || ' |'); " dbms_output.new_line; " END LOOP; "END; "/ "PROMPT ___Macro_separator(8)___ "SELECT '|' || " r.consumer_name || ' |' || " r.source_database || ' | ' || " r.sequence# || '|' || " r.name || ' | ' || " r.first_scn || '|' || " TO_CHAR(r.first_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " r.next_scn || '|' || " TO_CHAR(r.next_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(r.modified_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " r.dictionary_begin || ' |' || " r.dictionary_end || ' |' || " r.purgeable || ' |' " FROM dba_registered_archived_log r,dba_capture c "WHERE r.consumer_name = c.capture_name " ORDER BY r.source_database,r.consumer_name,r.first_scn; "PROMPT ___Macro_separator(9)___ "SELECT '|' || " capture_name || ' |' || " attribute_name || ' |' || " include || ' |' " FROM dba_capture_extra_attributes " ORDER BY capture_name; "PROMPT ___Macro_separator(10)___ "SELECT '|' || " table_owner || ' |' || " table_name || ' | ' || " scn || '|' || " timestamp || ' |' || " supplemental_log_data_pk || ' |' || " supplemental_log_data_ui || ' |' || " supplemental_log_data_fk || ' |' || " supplemental_log_data_all || ' |' " FROM dba_capture_prepared_tables " ORDER BY table_owner,table_name; "PROMPT ___Macro_separator(11)___ "SELECT '|' || " schema_name || ' |' || " timestamp || ' |' || " supplemental_log_data_pk || ' |' || " supplemental_log_data_ui || ' |' || " supplemental_log_data_fk || ' |' || " supplemental_log_data_all || ' |' " FROM dba_capture_prepared_schemas " ORDER BY schema_name; "PROMPT ___Macro_separator(12)___ "SELECT '|' || " timestamp || ' |' || " supplemental_log_data_pk || ' |' || " supplemental_log_data_ui || ' |' || " supplemental_log_data_fk || ' |' || " supplemental_log_data_all || ' |' " FROM dba_capture_prepared_database; "PROMPT ___Macro_separator(13)___ "SELECT '|' || " owner || ' |' || " table_name || ' |' " FROM dba_log_groups; "PROMPT ___Macro_separator(14)___ "SELECT '|' || " owner || '.' || table_name || ' |' || " log_group_name || ' |' || " log_group_type || ' |' || " DECODE(always,'ALWAYS','Unconditional', " 'CONDITIONAL','Conditional', " NULL,'Conditional') || ' |' " FROM dba_log_groups; "PROMPT ___Macro_separator(15)___ "SELECT '|' || " owner || '.' || table_name || ' |' || " log_group_name || ' |' || " column_name || ' | ' || " position || '|' || " logging_property || ' |' " FROM dba_log_group_columns; "PROMPT ___Macro_separator(16)___ "SELECT '|' || " SUBSTR(s.program,INSTR(s.program,'(') + 1,4) || ' |' || " c.capture_name || ' |' || " TO_CHAR(c.startup_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " c.sid || '| ' || " c.serial# || '|' || " c.state || ' |' || " TO_CHAR(c.state_changed_time,'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " c.total_messages_captured || '| ' || " c.total_messages_enqueued || '| ' || " total_messages_created || '|' " FROM gv$streams_capture c,gv$session s " WHERE c.sid = s.sid " AND c.serial# = s.serial#; "PROMPT ___Macro_separator(17)___ "SELECT '|' || " capture_name || ' |' || " TO_CHAR(SYSDATE,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " capture_time || ' | ' || " capture_message_number || '|' || " TO_CHAR(capture_message_create_time, " 'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(enqueue_time,'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " enqueue_message_number || '|' || " TO_CHAR(enqueue_message_create_time, " 'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " available_message_number || '|' || " TO_CHAR(available_message_create_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM gv$streams_capture; "PROMPT ___Macro_separator(18)___ "SELECT '|' || " capture_name || ' |' || " total_prefilter_discarded || ' | ' || " total_prefilter_kept || '| ' || " total_prefilter_evaluations || '| ' || " (total_prefilter_evaluations - " (total_prefilter_kept + total_prefilter_discarded)) || '| ' || " total_full_evaluations || '|' " FROM gv$streams_capture; "PROMPT ___Macro_separator(19)___ "SELECT '|' || " capture_name || ' | ' || " elapsed_capture_time || '| ' || " elapsed_rule_time || '| ' || " elapsed_enqueue_time || '| ' || " elapsed_lcr_time || '| ' || " elapsed_redo_wait_time || '| ' || " elapsed_pause_time || '| ' || " total_messages_created || '| ' || " total_full_evaluations || '|' " FROM gv$streams_capture; "PROMPT ___Macro_separator(20)___ "SELECT '|' || " c.capture_name || ' |' || " name || ' |' || " value || ' |' " FROM gv$streams_capture c,gv$logmnr_stats l " WHERE c.logminer_id = l.session_id " AND name IN ('bytes paged out', " 'bytes of redo processed', " 'microsecs spent in pageout'); "PROMPT ___Macro_separator(21)___ "SELECT '| ' || " inst_id || '| ' || " queue_id || '|' || " queue_schema || ' |' || " queue_name || ' |' || " sender_name || ' |' || " sender_address || ' |' || " sender_protocol || ' | ' || " num_msgs || '| ' || " cnum_msgs || '| ' || " last_enqueued_msg || '| ' || " unbrowsed_msgs || '| ' || " overspilled_msgs || '| ' || " memory_usage || '|' || " publisher_state || ' |' " FROM gv$buffered_publishers; "PROMPT ___Macro_separator(22)___ "SELECT '|' || " streams_name || ' |' || " queue_owner || ' |' || " queue_name || ' |' || " rule_set_name || ' |' || " negative_rule_set_name || ' |' " FROM dba_streams_message_consumers " ORDER BY queue_owner,queue_name,streams_name; "PROMPT ___Macro_separator(23)___ "SELECT '|' || " streams_name || ' |' || " queue_owner || ' |' || " queue_name || ' |' || " notification_type || ' |' || " notification_action || ' |' " FROM dba_streams_message_consumers " WHERE notification_type IS NOT NULL " ORDER BY queue_owner,queue_name,streams_name; } } if $sql {call separator(1) call writeSql($sql,2) call separator(0,'Capture Process Information') } =head2 prop - Propagation Information Identifies the names of the propagations and their endpoints. The source queue and destination queues are identified by their global name/database link. =cut debug ' Inside the STC module, gathering propagation information' report prop var $TTL = '---+!! Propagation Information' if match($ORACLE_VERSION,'92') {var @TTL = ('',\ '---+ Propagations in Database',\ '---+ Propagation Rulesets in Database',\ '---+ Streams Propagation Rules Configured with DBMS_STREAMS_ADM \ Package',\ '---+ Streams Table Subsetting Rules',\ '---+ Propagation Rules by Ruleset',\ '---+ Propagation Rule Transformations by Ruleset',\ '---+ Schedule for Propagation',\ '---+ Schedule for each Propagation',\ '---+ Events and Bytes for Propagation') var @HDR = ('',\ '|*Propagation Name*|*Source Queue*|*Destination Queue*|',\ '|*Propagation Name*|*Rule Set Owner*|*Rule Set Name*|',\ '|*Propagation Name*|*Object*|*Source Database*|*Type*|\ *Include Tagged Lcr*|*Rule*|',\ '|*Name*|*Object*|*Type*|*Rule*|*Dml Condition*|\ *Subsetting Operation*|',\ '|*Rule Set*|*Rule Name*|*Condition*|',\ '|*Rule Set*|*Rule Name*| *Action Context Name*|\ *Action Context Value*|',\ '|*Propagation Name*|*Start Date*|*Prop Window*|*Next Time*| \ *Latency*|*Schedule Disabled*|*Process Name*| *Failure*|\ *Last Error Msg*|',\ '|*Propagation Name*|*Last Run Date*|*Current Start Date*|\ *Next Run Date*|*Last Error Date*|',\ '|*Propagation Name*|*Total Time*| *Total No*| *Total Bytes*|') set $sql {SELECT '|' || " p.propagation_name || ' |' || " p.source_queue_owner || '.' || p.source_queue_name " || '@' || g.global_name || ' |' || " p.destination_queue_owner || '.' || p.destination_queue_name " || '@' || p.destination_dblink || ' |' " FROM dba_propagation p,global_name g; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " propagation_name || ' |' || " rule_set_owner || ' |' || " rule_set_name || ' |' " FROM dba_propagation; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " streams_name || ' |' || " table_name || ' |' || " source_database || ' |' || " rule_type || ' TABLE RULE' || ' |' || " include_tagged_lcr || ' |' || " rule_owner || '.' || rule_name || ' |' " FROM dba_streams_table_rules " WHERE streams_type = 'PROPAGATION' "UNION "SELECT '|' || " streams_name || ' |' || " schema_name || ' |' || " source_database || ' |' || " rule_type || ' SCHEMA RULE' || ' |' || " include_tagged_lcr || ' |' || " rule_owner || '.' || rule_name || ' |' " FROM dba_streams_schema_rules " WHERE streams_type = 'PROPAGATION' "UNION "SELECT '|' || " streams_name || ' |' || " ' ' || ' |' || " source_database || ' |' || " rule_type || ' GLOBAL RULE' || ' |' || " include_tagged_lcr || ' |' || " rule_owner || '.' || rule_name || ' |' " FROM dba_streams_GLOBAL_rules " WHERE streams_type = 'PROPAGATION'; "PROMPT ___Macro_separator(4)___ "SELECT '|' || " streams_name || ' |' || " table_owner || '.' || table_name || ' |' || " rule_type || ' TABLE RULE' || ' |' || " rule_owner || '.' || rule_name || ' |' || " dml_condition || ' |' || " subsetting_operation || ' |' " FROM dba_streams_table_rules " WHERE streams_type = 'PROPAGATION' " AND (dml_condition IS NOT NULL OR subsetting_operation IS NOT NULL); "PROMPT ___Macro_separator(5)___ "SET long 65536 "SELECT '[[[' || CHR(10) || '|' || " rsr.rule_set_owner || '.' || rsr.rule_set_name || ' |' || " rsr.rule_owner || '.' || rsr.rule_name || ' |' || " REPLACE(REPLACE(REPLACE(LTRIM(r.rule_condition), " '|','|'), " '<','<'), " '>','>') || '|' || " CHR(10) || ']]]' " FROM dba_rule_set_rules rsr,dba_rules r " WHERE rsr.rule_name = r.rule_name " AND rsr.rule_owner = r.rule_owner " AND rule_set_name IN " (SELECT rule_set_name " FROM dba_propagation) " ORDER BY rsr.rule_set_owner,rsr.rule_set_name; "PROMPT ___Macro_separator(6)___ "SELECT '|' || " rsr.rule_set_owner || '.' || rsr.rule_set_name || ' |' || " rsr.rule_owner || '.' || rsr.rule_name || ' |' || " ac.nvn_name || ' |' || " ac.nvn_value.accessvarchar2() || ' |' " FROM dba_rule_set_rules rsr,dba_rules r, " TABLE(r.rule_action_context.actx_list) ac " WHERE rsr.rule_name = r.rule_name " AND rsr.rule_owner = r.rule_owner " AND rule_set_name IN " (SELECT rule_set_name " FROM dba_propagation) " ORDER BY rsr.rule_set_owner,rsr.rule_set_name; "PROMPT ___Macro_separator(7)___ "SELECT '|' || " p.propagation_name || ' |' || " TO_CHAR(s.start_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " s.propagation_window || ' |' || " s.next_time || ' | ' || " s.latency || '|' || " DECODE(s.schedule_disabled,'Y','Disabled','N','Enabled') || ' |' || " s.process_name || ' | ' || " s.failures || '|' || " REPLACE(s.last_error_msg,CHR(10),'%BR%') || ' |' " FROM dba_queue_schedules s,dba_propagation p " WHERE p.destination_dblink = s.destination " AND s.schema = p.source_queue_owner " AND s.qname = p.source_queue_name; "PROMPT ___Macro_separator(8)___ "SELECT '|' || " p.propagation_name || ' |' || " TO_CHAR(s.last_run_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(s.current_start_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(s.next_run_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(s.last_error_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM dba_queue_schedules s,dba_propagation p " WHERE p.destination_dblink = s.destination " AND s.schema = p.source_queue_owner " AND s.qname = p.source_queue_name; "PROMPT ___Macro_separator(9)___ "SELECT '|' || " p.propagation_name || ' | ' || " s.total_time || '| ' || " s.total_number || '| ' || " s.total_bytes || '|' " FROM dba_queue_schedules s,dba_propagation p " WHERE p.destination_dblink = s.destination " AND s.schema = p.source_queue_owner " AND s.qname = p.source_queue_name; } } elsif match($ORACLE_VERSION,'101') {var @TTL = ('',\ '---+ Propagations in Database',\ '---+ Propagation Rulesets in Database',\ '---+ Streams Propagation Rules Configured with DBMS_STREAMS_ADM \ Package',\ '---+ Streams Table Subsetting Rules',\ '---+ Propagation Rules by Ruleset',\ '---+ Propagation Rule Transformations by Ruleset',\ '---+ Schedule for Propagation 1',\ '---+ Schedule for Propagation 2',\ '---+ Events and Bytes for Propagation',\ '---+ Propagation Sender Statistics',\ '---+ Propagation Receiver Statistics',\ '---+ Buffered Subscribers') var @HDR = ('',\ '|*Propagation Name*|*Source Queue*|*Destination Queue*|',\ '|*Propagation Name*|*Positive*|*Negative*|',\ '|*Name*|*Object*|*Rule Set Type*|*Source Database*|*Type*|\ *Include Tagged Lcr*|*Rule*|',\ '|*Name*|*Object*|*Type*|*Rule*|*Dml Condition*|\ *Subsetting Operation*|',\ '|*Rule Set*|*Rule Name*|*Condition*|',\ '|*Rule Set*|*Rule Name*| *Action Context Name*|\ *Action Context Value*|',\ '|*Propagation Name*|*Start Date*|*Prop Window*|*Next Time*| \ *Latency*|*Schedule Disabled*|*Process Name*| *Total Bytes*| \ *Failure*| *Last Error Msg*|',\ '|*Propagation Name*|*Last Run Date*|*Current Start Date*|\ *Next Run Date*|*Last Error Date*|',\ '|*Propagation Name*|*Schedule Status*| *Instance*| *Total No*| \ *Total Bytes*|',\ '|*Propagation Name*| *Total No*| *Total Bytes*| \ *High Water Mark*| *Acknowledgement*| \ *Elapsed Dequeue Time/100*| *Elapsed Pickle Time/100*| \ *Elapsed Propagation Time/100*|',\ '|*Source Db*|*Source Queue*|*Startup Time*| *High Water Mark*| \ *Acknowledgement*| *Elapsed Unpickle Time/100*| \ *Elapsed Rule Time/100*| *Elapsed Enqueue Time/100*|',\ '| *Inst Id*| *Queue Id*|*Queue Schema*|*Queue Name*| \ *Subscriber Id*|*Subscriber Name*|*Subscriber Address*|\ *Protocol*|*Subscriber Type*|*Startup Time*| \ *Last Browsed Seq*| *Last Browsed Num*| *Last Dequeued Seq*| \ *Last Dequeued Num*| *Current Enq Seq*| *Num Msgs*| \ *Cnum Msgs*| *Total Dequeued Msg*| *Total Spilled Msg*|') set $sql {SELECT '|' || " p.propagation_name || ' |' || " p.source_queue_owner || '.' || p.source_queue_name " || '@' || g.global_name || ' |' || " p.destination_queue_owner || '.' || p.destination_queue_name " || '@' || p.destination_dblink || ' |' " FROM dba_propagation p,global_name g; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " propagation_name || ' |' || " rule_set_owner || '.' || rule_set_name || ' |' || " negative_rule_set_owner || '.' || negative_rule_set_name || ' |' " FROM dba_propagation; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " streams_name || ' |' || " schema_name || '.' || object_name || ' |' || " rule_set_type || ' |' || " source_database || ' |' || " streams_rule_type || ' ' || rule_type || ' |' || " include_tagged_lcr || ' |' || " rule_owner || '.' || rule_name || ' |' " FROM dba_streams_rules " WHERE streams_type = 'PROPAGATION' " ORDER BY streams_name,object_name,source_database,rule_set_type,rule_name; "PROMPT ___Macro_separator(4)___ "SELECT '|' || " streams_name || ' |' || " schema_name || '.' || object_name || ' |' || " rule_type || ' TABLE RULE' || ' |' || " rule_owner || '.' || rule_name || ' |' || " dml_condition || ' |' || " subsetting_operation || ' |' " FROM dba_streams_rules " WHERE streams_type = 'PROPAGATION' " AND (dml_condition IS NOT NULL OR subsetting_operation IS NOT NULL); "PROMPT ___Macro_separator(5)___ "SET long 65536 "SELECT '[[[' || CHR(10) || '|' || " rsr.rule_set_owner || '.' || rsr.rule_set_name || ' |' || " rsr.rule_owner || '.' || rsr.rule_name || ' |' || " REPLACE(REPLACE(REPLACE(LTRIM(r.rule_condition), " '|','|'), " '<','<'), " '>','>') || '|' || " CHR(10) || ']]]' " FROM dba_rule_set_rules rsr,dba_rules r " WHERE rsr.rule_name = r.rule_name " AND rsr.rule_owner = r.rule_owner " AND rule_set_name IN " (SELECT rule_set_name " FROM dba_propagation) " ORDER BY rsr.rule_set_owner,rsr.rule_set_name; "PROMPT ___Macro_separator(6)___ "SELECT '|' || " rsr.rule_set_owner || '.' || rsr.rule_set_name || ' |' || " rsr.rule_owner || '.' || rsr.rule_name || ' |' || " ac.nvn_name || ' |' || " ac.nvn_value.accessvarchar2() || ' |' " FROM dba_rule_set_rules rsr,dba_rules r, " TABLE(r.rule_action_context.actx_list) ac " WHERE rsr.rule_name = r.rule_name " AND rsr.rule_owner = r.rule_owner " AND rule_set_name IN " (SELECT rule_set_name " FROM dba_propagation) " ORDER BY rsr.rule_set_owner,rsr.rule_set_name; "PROMPT ___Macro_separator(7)___ "SELECT '|' || " p.propagation_name || ' |' || " TO_CHAR(s.start_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " s.propagation_window || ' |' || " s.next_time || ' | ' || " s.latency || '|' || " DECODE(s.schedule_disabled,'Y','Disabled','N','Enabled') || ' |' || " s.process_name || ' | ' || " s.total_bytes || '| ' || " s.failures || '|' || " REPLACE(s.last_error_msg,CHR(10),'%BR%') || ' |' " FROM dba_queue_schedules s,dba_propagation p " WHERE p.destination_dblink = s.destination " AND s.schema = p.source_queue_owner " AND s.qname = p.source_queue_name; "PROMPT ___Macro_separator(8)___ "SELECT '|' || " p.propagation_name || ' |' || " TO_CHAR(s.last_run_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(s.current_start_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(s.next_run_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(s.last_error_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM dba_queue_schedules s,dba_propagation p " WHERE p.destination_dblink = s.destination " AND s.schema = p.source_queue_owner " AND s.qname = p.source_queue_name; "PROMPT ___Macro_separator(9)___ "SELECT '|' || " p.propagation_name || ' |' || " DECODE(q.schedule_disabled,'Y','Disabled','N','Enabled') || ' | ' || " q.instance || '| ' || " q.total_number || '| ' || " q.total_bytes || '|' " FROM dba_propagation p,dba_queue_schedules q " WHERE p.destination_dblink = q.destination " AND q.schema = p.source_queue_owner " AND q.qname = p.source_queue_name " ORDER BY p.propagation_name; "PROMPT ___Macro_separator(10)___ "SELECT '|' || " p.propagation_name || ' | ' || " s.total_msgs || '| ' || " s.total_bytes || '| ' || " s.high_water_mark || '| ' || " s.acknowledgement || '| ' || " s.elapsed_dequeue_time / 100 || '| ' || " s.elapsed_pickle_time / 100 || '| ' || " s.elapsed_propagation_time / 100 || '|' " FROM gv$propagation_sender s,dba_propagation p " WHERE p.destination_dblink = s.dblink " AND s.queue_schema = p.source_queue_owner " AND s.queue_name = p.source_queue_name; "PROMPT ___Macro_separator(11)___ "SELECT '|' || " src_dbname || ' |' || " src_queue_name || ' |' || " TO_CHAR(startup_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " high_water_mark || '| ' || " acknowledgement || '| ' || " elapsed_unpickle_time / 100 || '| ' || " elapsed_rule_time / 100 || '| ' || " elapsed_enqueue_time / 100 || '|' " FROM gv$propagation_receiver; "PROMPT ___Macro_separator(12)___ "SELECT '| ' || " inst_id || '| ' || " queue_id || '|' || " queue_schema || ' |' || " queue_name || ' | ' || " subscriber_id || '|' || " subscriber_name || ' |' || " subscriber_address || ' |' || " protocol || ' |' || " subscriber_type || ' |' || " TO_CHAR(startup_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " last_browsed_seq || '| ' || " last_browsed_num || '| ' || " last_dequeued_seq || '| ' || " last_dequeued_num || '| ' || " current_enq_seq || '| ' || " num_msgs || '| ' || " cnum_msgs || '| ' || " total_dequeued_msg || '| ' || " total_spilled_msg || '|' " FROM gv$buffered_subscribers; } } elsif match($ORACLE_VERSION,'^(102|11)') {var @TTL = ('',\ '---+ Propagations in Database',\ '---+ Propagation Rulesets in Database',\ '---+ Streams Propagation Rules Configured with DBMS_STREAMS_ADM \ Package',\ '---+ Streams Table Subsetting Rules',\ '---+ Propagation Rules by Ruleset',\ '---+ Propagation Rule Transformations by Ruleset',\ '---+ Schedule for Propagation 1',\ '---+ Schedule for Propagation 2',\ '---+ Events and Bytes for Propagation',\ '---+ Propagation Sender Statistics',\ '---+ Propagation Receiver Statistics',\ '---+ Buffered Subscribers') var @HDR = ('',\ '|*Propagation Name*|*Source Queue*|*Destination Queue*|\ *Queue to Queue*|*Status*|*Error Date*|*Error Msg*|',\ '|*Propagation Name*|*Positive*|*Negative*|',\ '|*Name*|*Object*|*Rule Set Type*|*Source Database*|*Type*|\ *Include Tagged Lcr*|*Rule*|',\ '|*Name*|*Object*|*Type*|*Rule*|*Dml Condition*|\ *Subsetting Operation*|',\ '|*Rule Set*|*Rule Name*|*Condition*|',\ '|*Rule Set*|*Rule Owner*|*Rule Name*|*Transform Type*|\ *From Schema Name*|*To Schema Name*|*From Table Name*|\ *To Table Name*|*Schema Name*|*Table Name*|*From Column Name*|\ *To Column Name*|*Column Name*|*Column Value*|*Column Type*|\ *Column Function*|*Value Type*|*User Function Name*|\ *Subsetting Operation*|*Dml Condition*|*Declarative Type*|\ *Precedence*|*Step Number*|',\ '|*Propagation Name*|*Start Date*|*Prop Window*|*Next Time*| \ *Latency*|*Schedule Disabled*|*Process Name*| *Total Bytes*| \ *Failure*|*Msg Delivery Mode*|*Last Error Msg*|',\ '|*Propagation Name*|*Msg Delivery Mode*|*Last Run Date*|\ *Current Start Date*|*Next Run Date*|*Last Error Date*|',\ '|*Propagation Name*|*Msg Delivery Mode*|*Schedule Status*| \ *Instance*| *Total No*| *Total Bytes*| *Elapsed Dequeue Time*| \ *Elapsed Pickle Time*| *Total Time*|',\ '| *Queue Id*|*Queue Schema*|*Queue Name*|*DstQueue Schema*|\ *Dst Queue Name*|*Startup Time*|*Dblink*| *High Water Mark*| \ *Acknowledgement*|*Schedule Status*| *Total Msgs*| \ *Total Bytes*| *Elapsed Dequeue Time*| *Elapsed Pickle Time*| \ *Elapsed Propagation Time*| *Max Num Per Win*| *Max Size*|',\ '| *Inst Id*|*Src Queue Schema*|*Src Queue Name*|*Src Dbname*|\ *Dst Queue Schema*|*Dst Queue Name*|*Startup Time*| \ *High Water Mar *| *Acknowledgement*| *Total Msgs*| \ *Elapsed Unpickle Time*| *Elapsed Rule Time*| \ *Elapsed Enqueue Time*|',\ '| *Inst Id*| *Queue Id*|*Queue Schema*|*Queue Name*| \ *Subscriber Id*|*Subscriber Name*|*Subscriber Address*|\ *Protocol*|*Subscriber Type*|*Startup Time*| \ *Last Browsed Seq*| *Last Browsed Num*| *Last Dequeued Seq*| \ *Last Dequeued Num*| *Current Enq Seq*| *Num Msgs*| \ *Cnum Msgs*| *Total Dequeued Msg*| *Total Spilled Msg*| \ *Expired Msgs*|') set $sql {SET serveroutput on size 1000000 "SELECT '|' || " p.propagation_name || ' |' || " p.source_queue_owner || '.' || p.source_queue_name " || '@' || g.global_name || ' |' || " p.destination_queue_owner || '.' || p.destination_queue_name " || '@' || p.destination_dblink || ' |' || " p.queue_to_queue || ' |' || " p.status || ' |' || " p.error_date || ' |' || " p.error_message || ' |' " FROM dba_propagation p,global_name g; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " propagation_name || ' |' || " rule_set_owner || '.' || rule_set_name || ' |' || " negative_rule_set_owner || '.' || negative_rule_set_name || ' |' " FROM dba_propagation; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " streams_name || ' |' || " schema_name || '.' || object_name || ' |' || " rule_set_type || ' |' || " source_database || ' |' || " streams_rule_type || ' ' || rule_type || ' |' || " include_tagged_lcr || ' |' || " rule_owner || '.' || rule_name || ' |' " FROM dba_streams_rules " WHERE streams_type = 'PROPAGATION' " ORDER BY streams_name,object_name,source_database,rule_set_type,rule_name; "PROMPT ___Macro_separator(4)___ "SELECT '|' || " streams_name || ' |' || " schema_name || '.' || object_name || ' |' || " rule_type || ' TABLE RULE' || ' |' || " rule_owner || '.' || rule_name || ' |' || " dml_condition || ' |' || " subsetting_operation || ' |' " FROM dba_streams_rules " WHERE streams_type = 'PROPAGATION' " AND (dml_condition IS NOT NULL OR subsetting_operation IS NOT NULL); "PROMPT ___Macro_separator(5)___ "SET long 65536 "SELECT '[[[' || CHR(10) || '|' || " rsr.rule_set_owner || '.' || rsr.rule_set_name || ' |' || " rsr.rule_owner || '.' || rsr.rule_name || ' |' || " REPLACE(REPLACE(REPLACE(LTRIM(r.rule_condition), " '|','|'), " '<','<'), " '>','>') || '|' || " CHR(10) || ']]]' " FROM dba_rule_set_rules rsr,dba_rules r " WHERE rsr.rule_name = r.rule_name " AND rsr.rule_owner = r.rule_owner " AND rule_set_name IN " (SELECT rule_set_name " FROM dba_propagation) " ORDER BY rsr.rule_set_owner,rsr.rule_set_name; "PROMPT ___Macro_separator(6)___ "DECLARE " CURSOR c_apply IS " SELECT rsr.rule_set_owner, " rsr.rule_set_name, " r.rule_owner, " r.rule_name, " r.transform_type, " r.from_schema_name, " r.to_schema_name, " r.from_table_name, " r.to_table_name, " r.schema_name, " r.table_name, " r.from_column_name, " r.to_column_name, " r.column_name, " r.column_value, " r.column_type, " r.column_function, " r.value_type, " r.user_function_name, " r.subsetting_operation, " r.dml_condition, " r.declarative_type, " r.precedence, " r.step_number " FROM dba_rule_set_rules rsr,dba_streams_transformations r " WHERE r.rule_name = rsr.rule_name " AND r.rule_owner = rsr.rule_owner " AND rule_set_name IN " (SELECT rule_set_name " FROM dba_propagation) " ORDER BY rsr.rule_set_owner,rsr.rule_set_name,r.rule_owner, " r.rule_name,r.transform_type DESC,r.step_number,r.precedence; " l_str VARCHAR2(100); " l_int PLS_INTEGER; "BEGIN " FOR r_rec IN c_apply LOOP " BEGIN " l_int := r_rec.column_value.getvarchar2(l_str); " EXCEPTION " WHEN OTHERS THEN " l_str := ''; " END; " dbms_output.put('|' || " r_rec.rule_set_owner ||'.'|| r_rec.rule_set_name || ' |' || " r_rec.rule_owner || ' |' || " r_rec.rule_name || ' |' || " r_rec.transform_type || ' |' || " r_rec.from_schema_name || ' |' || " r_rec.to_schema_name || ' |' || " r_rec.from_table_name || ' |' || " r_rec.to_table_name || ' |' || " r_rec.schema_name || ' |' || " r_rec.table_name || ' |' ); " dbms_output.put(r_rec.from_column_name || ' |' || " r_rec.to_column_name || ' |' || " r_rec.column_name || ' |' ); " dbms_output.put(l_str || ' |' || " r_rec.column_type || ' |' || " r_rec.column_function || ' |' || " r_rec.value_type || ' |' || " r_rec.user_function_name || ' |' || " r_rec.subsetting_operation || ' |' || " r_rec.dml_condition || ' |' || " r_rec.declarative_type || ' |' || " r_rec.precedence || ' |' || " r_rec.step_number || ' |'); " dbms_output.new_line; " END LOOP; "END; "/ "PROMPT ___Macro_separator(7)___ "SELECT '|' || " p.propagation_name || ' |' || " TO_CHAR(s.start_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " s.propagation_window || ' |' || " s.next_time || ' | ' || " s.latency || '|' || " DECODE(s.schedule_disabled,'Y','Disabled','N','Enabled') || ' |' || " s.process_name || ' | ' || " s.total_bytes || '| ' || " s.failures || '|' || " s.message_delivery_mode || ' |' || " REPLACE(s.last_error_msg,CHR(10),'%BR%') || ' |' " FROM dba_queue_schedules s,dba_propagation p " WHERE p.destination_dblink = s.destination " AND s.schema = p.source_queue_owner " AND s.qname = p.source_queue_name " ORDER BY s.message_delivery_mode; "PROMPT ___Macro_separator(8)___ "SELECT '|' || " p.propagation_name || ' |' || " message_delivery_mode || ' |' || " TO_CHAR(s.last_run_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(s.current_start_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(s.next_run_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(s.last_error_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM dba_queue_schedules s,dba_propagation p " WHERE p.destination_dblink = s.destination " AND s.schema = p.source_queue_owner " AND s.qname = p.source_queue_name; "PROMPT ___Macro_separator(9)___ "SELECT '|' || " p.propagation_name || ' |' || " q.message_delivery_mode || ' |' || " DECODE(p.status,'DISABLED','Disabled','ENABLED','Enabled') || ' | ' || " q.instance || '| ' || " q.total_number || '| ' || " q.total_bytes || '| ' || " q.elapsed_dequeue_time / 100 || '| ' || " q.elapsed_pickle_time / 100 || '| ' || " q.total_time / 100 || '|' " FROM dba_propagation p,dba_queue_schedules q " WHERE p.destination_dblink = q.destination " AND q.schema = p.source_queue_owner " AND q.qname = p.source_queue_name " ORDER BY q.message_delivery_mode,p.propagation_name; "PROMPT ___Macro_separator(10)___ "SELECT '| ' || " queue_id || '|' || " queue_schema || ' |' || " queue_name || ' |' || " dst_queue_schema || ' |' || " dst_queue_name || ' |' || " TO_CHAR(startup_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " dblink || ' | ' || " high_water_mark || '| ' || " acknowledgement || '|' || " schedule_status || ' | ' || " total_msgs || '| ' || " total_bytes || '| ' || " elapsed_dequeue_time || '| ' || " elapsed_pickle_time || '| ' || " elapsed_propagation_time || '| ' || " max_num_per_win || '| ' || " max_size || '|' " FROM v$propagation_sender; "PROMPT ___Macro_separator(11)___ "SELECT '| ' || " inst_id || '|' || " src_queue_schema || ' |' || " src_queue_name || ' |' || " src_dbname || ' |' || " dst_queue_schema || ' |' || " dst_queue_name || ' |' || " TO_CHAR(startup_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " high_water_mark || '| ' || " acknowledgement || '| ' || " total_msgs || '| ' || " elapsed_unpickle_time || '| ' || " elapsed_rule_time || '| ' || " elapsed_enqueue_time || '|' " FROM gv$propagation_receiver; "PROMPT ___Macro_separator(12)___ "SELECT '| ' || " inst_id || '| ' || " queue_id || '|' || " queue_schema || ' |' || " queue_name || ' | ' || " subscriber_id || '|' || " subscriber_name || ' |' || " subscriber_address || ' |' || " protocol || ' |' || " subscriber_type || ' |' || " TO_CHAR(startup_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " last_browsed_seq || '| ' || " last_browsed_num || '| ' || " last_dequeued_seq || '| ' || " last_dequeued_num || '| ' || " current_enq_seq || '| ' || " num_msgs || '| ' || " cnum_msgs || '| ' || " total_dequeued_msg || '| ' || " total_spilled_msg || '| ' || " expired_msgs || '|' " FROM gv$buffered_subscribers; } } if $sql {call separator(1) call writeSql($sql,2) call separator(0,'Propagation Information') } =head2 apply - Apply Process Information Provides information about the apply processes configured in the database. For each apply process, the streams name and associated queue name is presented along with the rule set name. =cut debug ' Inside the STC module, gathering apply process information' report apply var $TTL = '---+!! Apply Process Information' if match($ORACLE_VERSION,'92') {var @TTL = ('',\ '---+ Apply Processes in Database',\ '---+ Apply Process Parameters',\ '---+ Streams Apply Rules - Dbms_Streams_Adm Package',\ '---+ Streams Table Subsetting Rules',\ '---+ Apply Rules by Ruleset',\ '---+ Apply Rule Transformation by Ruleset',\ '---+ Apply Handlers',\ '---+ Apply Dml Handlers',\ '---+ Dml Handler Status',\ '---+ Rule Transformation Status',\ '---+ Update Conflict Resolution Columns',\ '---+ Key Columns',\ '---+ Apply Reader Statistics 1',\ '---+ Apply Reader Statistics 2',\ '---+ Apply Coordinator Statistics 1',\ '---+ Apply Coordinator Statistics 2',\ '---+ Apply Server Statistics 1',\ '---+ Apply Server Statistics 2',\ '---+ Apply Progress',\ '---+ Error Queue',\ '---+ Instantiation Scn for Tables',\ '---+ Instantiation Scn for Schemas and Database',\ '---+ DBA_OBJECTS - Rules and Streams Processes') var @HDR = ('',\ '|*Apply Name*|*Queue*|*Apply Captured*|*Status*|*Apply User*|\ *Tag*|*Rule Set*|*Apply Database Link*|',\ '|*Apply Name*|*Parameter*|*Value*|*Set By User*|',\ '|*Name*|*Object*|*Source Database*|*Type*|*Include Tagged Lcr*|\ *Rule*|',\ '|*Name*|*Object*|*Type*|*Rule*|*Dml Condition*|\ *Subsetting Operation*|',\ '|*Rule Set*|*Rule Name*|*Condition*|',\ '|*Rule Set*|*Rule Name*|*Action Context Name*|\ *Action Context Value*|',\ '|*Apply Name*|*Msg Handler*|*DDL Handler*|',\ '|*Object*|*Operation*|*User Procedure*|*Typ*|*DB Link*|',\ '|*User Procedure*|*Status*|*Type*|*Created*|*Last DDL Time*|',\ '|*Action Context Value*|*Status*|*Type*|*Created*|\ *Last DDL Time*|',\ '|*Object*|*Method Name*|*Resolution Column*|*Column Name*|',\ '|*Object Owner*|*Object*|*Column*|*DB Link*|',\ '|*Apply Name*|*Apply Captured*|*Process Name*|*State*| \ *Total Messages Dequeued*| *Sga Used*|',\ '|*Apply Name*| *Latency*|*Creation*|*Last Dequeue*| \ *Dequeued Msg No*|',\ '|*Apply Name*|*Process*|*State*| *Recieved*| *Assigned*| \ *Applied*| *Errors*| *Wait Deps*| *Wait Commits*|',\ '|*Apply Name*|*Lwm Msg Ts*| *Lwm Msg Nbr*|*Lwm Updated*|\ *Hwm Msg Ts*| *Hwm Msg Nbr*|*Hwm Updated*|',\ '|*Apply Name*|*Process*|*Server*|*State*| *Assigned*| \ *Applied*| *Applied Msg No*|*Created*| *Msg Seq*|',\ '|*Apply Name*| *Server*| *Current Txn*| *Commit Scn*| \ *Dependent Txn*| *Dep Commit Scn*|',\ '|*Apply Name*| *Applied Msg No*| *Oldest Msg No*|*Apply Time*|\ *Created*|',\ '|*Apply Name*|*Source Database*| *Source Commit Scn*| *Msg No*| \ *Msg Count*|*Local Transaction Id*|*Error Msg*|',\ '|*Source Database*|*Object*|*DB Link*| *Ignore Scn*| \ *Instantiation Scn*|',\ '|*Source Database*|*Object*|*DB Link*| *Inst Scn*| \ *Global Flag*| *Spare1*|',\ '|*Object*| *Object Id*|*Object Type*|*Created*|*Last DDL Time*|\ *Status*|') set $sql {SELECT '|' || " apply_name || ' |' || " queue_owner || '.' || queue_name || ' |' || " DECODE(apply_captured,'YES','Captured','NO','User-Enqueued') || ' |' || " status || ' |' || " apply_user || ' |' || " apply_tag || ' |' || " rule_set_owner || '.' || rule_set_name || ' |' || " apply_database_link || ' |' " FROM dba_apply; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " apply_name || ' |' || " parameter || ' |' || " value || ' |' || " set_by_user || ' |' " FROM dba_apply_parameters " ORDER BY apply_name,parameter; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " streams_name || ' |' || " table_owner || '.' || table_name || ' |' || " source_database || ' |' || " rule_type || ' TABLE RULE' || ' |' || " include_tagged_lcr || ' |' || " rule_owner || '.' || rule_name || ' |' " FROM dba_streams_table_rules " WHERE streams_type = 'APPLY' "UNION "SELECT '|' || " streams_name || ' |' || " schema_name || ' |' || " source_database || ' |' || " rule_type || ' SCHEMA RULE' || ' |' || " include_tagged_lcr || ' |' || " rule_owner || '.' || rule_name || ' |' " FROM dba_streams_schema_rules " WHERE streams_type = 'APPLY' "UNION "SELECT '|' || " streams_name || ' |' || " ' ' || ' |' || " source_database || ' |' || " rule_type || ' GLOBAL RULE' || ' |' || " include_tagged_lcr || ' |' || " rule_owner || '.' || rule_name || ' |' " FROM dba_streams_global_rules " WHERE streams_type = 'APPLY'; "PROMPT ___Macro_separator(4)___ "SELECT '|' || " streams_name || ' |' || " table_owner || '.' || table_name || ' |' || " rule_type || ' TABLE RULE' || ' |' || " rule_owner || '.' || rule_name || ' |' || " dml_condition || ' |' || " subsetting_operation || ' |' " FROM dba_streams_table_rules " WHERE streams_type = 'APPLY' " AND (dml_condition IS NOT NULL OR subsetting_operation IS NOT NULL); "PROMPT ___Macro_separator(5)___ "SET long 65536 "SELECT '[[[' || CHR(10) || '|' || " rsr.rule_set_owner || '.' || rsr.rule_set_name || ' |' || " rsr.rule_owner || '.' || rsr.rule_name || ' |' || " REPLACE(REPLACE(REPLACE(LTRIM(r.rule_condition), " '|','|'), " '<','<'), " '>','>') || '|' || " CHR(10) || ']]]' " FROM dba_rule_set_rules rsr,dba_rules r " WHERE rsr.rule_name = r.rule_name " AND rsr.rule_owner = r.rule_owner " AND rule_set_name IN " (SELECT rule_set_name " FROM dba_apply) " ORDER BY rsr.rule_set_owner,rsr.rule_set_name; "PROMPT ___Macro_separator(6)___ "SELECT '|' || " rsr.rule_set_owner || '.' || rsr.rule_set_name || ' |' || " rsr.rule_owner || '.' || rsr.rule_name || ' |' || " ac.nvn_name || ' |' || " ac.nvn_value.accessvarchar2() || ' |' " FROM dba_rule_set_rules rsr,dba_rules r, " TABLE(r.rule_action_context.actx_list) ac " WHERE rsr.rule_name = r.rule_name " AND rsr.rule_owner = r.rule_owner " AND rule_set_name IN " (SELECT rule_set_name " FROM dba_apply) " ORDER BY rsr.rule_set_owner,rsr.rule_set_name; "PROMPT ___Macro_separator(7)___ "SELECT '|' || " apply_name || ' |' || " message_handler || ' |' || " ddl_handler || ' |' " FROM dba_apply " WHERE message_handler IS NOT NULL OR ddl_handler IS NOT NULL; "PROMPT ___Macro_separator(8)___ "SELECT '|' || " object_owner || '.' || object_name || ' |' || " SUBSTR(operation_name,1,1) || ' |' || " user_procedure || ' |' || " DECODE(error_handler,'Y','Error','N','DML','UNKNOWN') || ' |' || " apply_database_link || ' |' " FROM dba_apply_dml_handlers; "PROMPT ___Macro_separator(9)___ "SELECT '|' || " h.user_procedure || ' |' || " o.status || ' |' || " o.object_type || ' |' || " o.created || ' |' || " o.last_ddl_time || ' |' " FROM dba_objects o,dba_apply_dml_handlers h " WHERE o.owner = SUBSTR(h.user_procedure,1, " INSTR(h.user_procedure,'.',1,1) - 1) " AND o.object_name = SUBSTR(h.user_procedure, " INSTR(h.user_procedure,'.',-1,1) + 1); "PROMPT ___Macro_separator(10)___ "SELECT '|' || " ac.nvn_value.accessvarchar2() || ' |' || " o.status || ' |' || " o.object_type || ' |' || " o.created || ' |' || " o.last_ddl_time || ' |' " FROM dba_objects o,dba_rules r,TABLE(r.rule_action_context.actx_list) ac " WHERE o.owner = UPPER(SUBSTR(ac.nvn_value.accessvarchar2(),1, " INSTR(ac.nvn_value.accessvarchar2(),'.',1,1) - 1)) " AND o.object_name = UPPER(SUBSTR(ac.nvn_value.accessvarchar2(), " INSTR(ac.nvn_value.accessvarchar2(),'.',-1,1) + 1)); "PROMPT ___Macro_separator(11)___ "SELECT '|' || " object_owner || '.' || object_name || ' |' || " method_name || ' |' || " resolution_column || ' |' || " column_name || ' |' " FROM dba_apply_conflict_columns " ORDER BY object_owner,object_name; "PROMPT ___Macro_separator(12)___ "SELECT '|' || " object_owner || ' |' || " object_name || ' |' || " column_name || ' |' || " apply_database_link || ' |' " FROM dba_apply_key_columns; "PROMPT ___Macro_separator(13)___ "SELECT '|' || " ap.apply_name || ' |' || " DECODE(ap.apply_captured,'YES','Captured LCRS', " 'NO','User-Enqueued', " 'UNKNOWN') || ' |' || " SUBSTR(s.program,INSTR(s.program,'(') + 1,4) || ' |' || " r.state || ' | ' || " r.total_messages_dequeued || '| ' || " r.sga_used || '|' " FROM v$streams_apply_reader r,v$session s,dba_apply ap " WHERE r.sid = s.sid " AND r.serial# = s.serial# " AND r.apply_name = ap.apply_name; "PROMPT ___Macro_separator(14)___ "SELECT '|' || " apply_name || ' |' || " (dequeue_time-dequeued_message_create_time) * 86400 || ' |' || " TO_CHAR(dequeued_message_create_time, " 'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(dequeue_time,'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " dequeued_message_number || '|' " FROM v$streams_apply_reader; "PROMPT ___Macro_separator(15)___ "SELECT '|' || " ap.apply_name || ' |' || " SUBSTR(s.program,INSTR(s.program,'(') + 1,4) || ' |' || " c.state || ' | ' || " c.total_received || '| ' || " c.total_assigned || '| ' || " c.total_applied || '| ' || " c.total_errors || '| ' || " c.total_wait_deps || '| ' || " c.total_wait_commits || '|' " FROM gv$streams_apply_coordinator c,gv$session s,dba_apply ap " WHERE c.sid = s.sid " AND c.serial# = s.serial# " AND c.apply_name = ap.apply_name; "PROMPT ___Macro_separator(16)___ "SELECT '|' || " apply_name || ' |' || " TO_CHAR(lwm_message_create_time,'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " lwm_message_number || '|' || " TO_CHAR(lwm_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(hwm_message_create_time,'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " hwm_message_number || '|' || " TO_CHAR(hwm_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM gv$streams_apply_coordinator; "PROMPT ___Macro_separator(17)___ "SELECT '|' || " ap.apply_name || ' |' || " SUBSTR(s.program,INSTR(s.program,'(') + 1,4) || ' | ' || " a.server_id || '|' || " a.state || ' | ' || " a.total_assigned || '| ' || " a.total_messages_applied || '| ' || " a.applied_message_number || '|' || " TO_CHAR(a.applied_message_create_time, " 'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " a.message_sequence || '|' " FROM gv$streams_apply_server a,gv$session s,dba_apply ap " WHERE a.sid = s.sid " AND a.serial# = s.serial# " AND a.apply_name = ap.apply_name; "PROMPT ___Macro_separator(18)___ "SELECT '|' || " apply_name || ' | ' || " server_id || '|' || " xidusn || '.' || xidslt || '.' || xidsqn || ' | ' || " commitscn || '|' || " dep_xidusn || '.' || dep_xidslt || '.' || dep_xidsqn || ' | ' || " dep_commitscn || '|' " FROM gv$streams_apply_server " ORDER BY apply_name,server_id; "PROMPT ___Macro_separator(19)___ "SELECT '|' || " apply_name || ' | ' || " applied_message_number || '| ' || " oldest_message_number || '|' || " TO_CHAR(apply_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(applied_message_create_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM dba_apply_progress; "PROMPT ___Macro_separator(20)___ "SELECT '|' || " apply_name || ' |' || " source_database || ' | ' || " source_commit_scn || '| ' || " message_number || '| ' || " message_count || '|' || " local_transaction_id || ' |' || " SUBSTR(error_message,1,10) || ' |' " FROM dba_apply_error " ORDER BY apply_name,source_commit_scn; "PROMPT ___Macro_separator(21)___ "SELECT '|' || " source_database || ' |' || " source_object_owner || '.' || source_object_name || ' |' || " apply_database_link || ' | ' || " ignore_scn || '| ' || " instantiation_scn || '|' " FROM dba_apply_instantiated_objects " ORDER BY source_database,source_object_name; "PROMPT ___Macro_separator(22)___ "SELECT '|' || " source_db_name || ' |' || " name || ' |' || " dblink || ' | ' || " inst_scn || '| ' || " global_flag || '| ' || " spare1 || '|' " FROM sys.apply$_source_schema " ORDER BY source_db_name,name; "PROMPT ___Macro_separator(23)___ "SELECT '|' || " owner || '.' || object_name || ' | ' || " object_id || '|' || " object_type || ' |' || " created || ' |' || " last_ddl_time || ' |' || " status || ' |' " FROM dba_objects " WHERE object_type IN ('RULE','RULE SET','UNDEFINED'); } } elsif match($ORACLE_VERSION,'101') {var @TTL = ('',\ '---+ Apply Processes in Database',\ '---+ Apply Process Information',\ '---+ Apply Process Handlers',\ '---+ Apply Process Parameters',\ '---+ Streams Apply Rules Configured with DBMS_STREAMS_ADM \ Package',\ '---+ Streams Table Subsetting Rules',\ '---+ Apply Rules by RuleSet',\ '---+ Apply Rule Transformations by Rule Set',\ '---+ Apply Handlers',\ '---+ Apply Dml Handlers',\ '---+ DML Handler Status',\ '---+ Rule Transformation Status',\ '---+ Update Conflict Resolution Columns',\ '---+ Key Columns',\ '---+ Apply Reader Statistics 1',\ '---+ Apply Reader Statistics 2',\ '---+ Apply Coordinator Statistics 1',\ '---+ Apply Coordinator Statistics 2',\ '---+ Apply Server Statistics 1',\ '---+ Apply Server Statistics 2',\ '---+ Apply Server Statistics 3',\ '---+ Apply Server Statistics 4',\ '---+ Apply Server Statistics 5',\ '---+ Apply Progress',\ '---+ Error Queue',\ '---+ Instantiation Scns for Tables',\ '---+ Instantiation Scns for Schema and Database',\ '---+ DBA OBJECTS - Rules and Streams Processes') var @HDR = ('',\ '|*Apply Name*|*Queue*|*Apply Captured*|*Status*|*Apply User*|\ *Apply Tag*|*Rsn*|*Negative Rsn*|',\ '|*Apply Name*| *Max Applied Msg No*|*Status*|\ *Status Change Time*| *Error No*|*Error Msg*|',\ '|*Apply Name*|*Ddl Handler*|*Message Handler*|\ *Precommit Handler*|',\ '|*Apply Name*|*Parameter*|*Value*|*Set By User*|',\ '|*Name*|*Object*|*Rule Set Type*|*Source Db*|*Type*|\ *Include Tagged Lcr*|*Rule*|',\ '|*Name*|*Object*|*Type*|*Rule*|*Dml Condition*|\ *Subsetting Operation*|',\ '|*Rule Set*|*Rule Name*|*Condition*|',\ '|*Rule Set*|*Rule Name*|*Action Context Name*|\ *Action Context Value*|',\ '|*Apply Name*|*Msg Handler*|*DDL Handler*|',\ '|*Object*|*Operation*|*User Procedure*|*Typ*|*DB Link*|\ *Apply Name*|',\ '|*Object*|*Status*|*Object Type*|*Created*|*Last Ddl Time*|',\ '|*Action Context Value*|*Status*|*Object Type*|*Created*|\ *Last Ddl Time*|',\ '|*Object*|*Method Name*|*Resolution Column*|*Column Name*|',\ '|*Object Owner*|*Object*|*Column*|*DB Link*|',\ '|*Apply Name*|*Apply Captured*|*Process Name*|*State*|\ *Total Messages Dequeued*|*Sga Used*|*Oldest Scn Num*|',\ '|*Apply Name*| *Latency*|*Creation*| *Last Dequeue*| \ *Dequeued Msg No*| *Last Browse No*|',\ '|*Apply Name*|*Process*|*State*| *Recieved*| *Assigned*| \ *Applied*| *Errors*| *Total Ignored*| *Total Rollbacks*| \ *Wait Deps*| *Wait Commits*|',\ '|*Apply Name*|*Lwm Msg Ts*| *Lwm Msg Nbr*|*Lwm Updated*|\ *Hwm Msg Ts*| *Hwm Msg Nbr*|*Hwm Updated*|',\ '|*Apply Name*|*Process Name*| *Server*|*State*| *Assigned*| \ *Msg Applied*| *Applied Msg No*|*Applied Msg Create Time*| \ *Msg Seq*|',\ '| *Inst Id*|*Apply Name*| *Server Id*|*Sql Text*|',\ '| *Inst Id*|*Apply Name*| *Server Id*|*Event*| \ *Seconds In Wait*|',\ '| *Inst Id*|*Apply Name*| *Server Id*|*Event*| *Total Waits*| \ *Total Timeouts*| *Time Waited*| *Average Wait*| *Max Wait*|',\ '|*Apply Name*| *Server Id*|*Current Txn*| *Commitscn*|\ *Dependent Txn*| *Dep Commitscn*|',\ '|*Apply Name*|*Source Db*| *Applied Msg No*| *Oldest Msg No*|\ *Apply Time*|*Applied Msg Create Time*|',\ '|*Apply Name*|*Source Db*| *Source Commit Scn*| *Msg No*| \ *Msg Cnt*| *Local Transaction Id*|*Error Message*|',\ '|*Source Db*|*Object*| *Ignore Scn*| *Instantiation Scn*|\ *Db Link*|',\ '|*Source Db*|*Object*|*Dblink*| *Inst Scn*| *Global Flag*|',\ '|*Object*|*Object Id*|*Object Type*|*Created*|*Last Ddl Time*|\ *Status*|') set $sql {SELECT '|' || " apply_name || ' |' || " queue_owner || '.' || queue_name || ' |' || " DECODE(apply_captured,'YES','Captured','NO','User-Enqueued') || ' |' || " status || ' |' || " apply_user || ' |' || " apply_tag || ' |' || " rule_set_owner || '.' || rule_set_name || ' |' || " negative_rule_set_owner || '.' || negative_rule_set_name || ' |' " FROM dba_apply; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " apply_name || ' | ' || " max_applied_message_number || '|' || " status || ' |' || " TO_CHAR(status_change_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " error_number || '|' || " error_message || ' |' " FROM dba_apply; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " apply_name || ' |' || " ddl_handler || ' |' || " message_handler || ' |' || " precommit_handler || ' |' " FROM dba_apply; "PROMPT ___Macro_separator(4)___ "SELECT '|' || " apply_name || ' |' || " parameter || ' |' || " value || ' |' || " set_by_user || ' |' " FROM dba_apply_parameters; "PROMPT ___Macro_separator(5)___ "SELECT '|' || " streams_name || ' |' || " schema_name || '.' || object_name || ' |' || " rule_set_type || ' |' || " source_database || ' |' || " streams_rule_type || ' ' || rule_type || ' |' || " include_tagged_lcr || ' |' || " rule_owner||'.'||rule_name || ' |' " FROM dba_streams_rules " WHERE streams_type = 'APPLY' " ORDER BY streams_name,object_name,source_database,rule_set_type,rule_name; "PROMPT ___Macro_separator(6)___ "SELECT '|' || " streams_name || ' |' || " schema_name || '.' || object_name || ' |' || " rule_type || ' TABLE RULE' || ' |' || " rule_owner || '.' || rule_name || ' |' || " dml_condition || ' |' || " subsetting_operation || ' |' " FROM dba_streams_rules " WHERE streams_type = 'APPLY' " AND (dml_condition IS NOT NULL OR subsetting_operation IS NOT NULL); "PROMPT ___Macro_separator(7)___ "SET long 65536 "SELECT '[[[' || CHR(10) || '|' || " rsr.rule_set_owner || '.' || rsr.rule_set_name || ' |' || " rsr.rule_owner || '.' || rsr.rule_name || ' |' || " REPLACE(REPLACE(REPLACE(LTRIM(r.rule_condition), " '|','|'), " '<','<'), " '>','>') || '|' || " CHR(10) || ']]]' " FROM dba_rule_set_rules rsr,dba_rules r " WHERE rsr.rule_name = r.rule_name " AND rsr.rule_owner = r.rule_owner " AND rule_set_name IN " (SELECT rule_set_name " FROM dba_apply) " ORDER BY rsr.rule_set_owner,rsr.rule_set_name; "PROMPT ___Macro_separator(8)___ "SELECT '|' || " rsr.rule_set_owner || '.' || rsr.rule_set_name || ' |' || " rsr.rule_owner || '.' || rsr.rule_name || ' |' || " ac.nvn_name || ' |' || " ac.nvn_value.accessvarchar2() || ' |' " FROM dba_rule_set_rules rsr,dba_rules r, " TABLE(r.rule_action_context.actx_list) ac " WHERE rsr.rule_name = r.rule_name " AND rsr.rule_owner = r.rule_owner " AND rule_set_name IN " (SELECT rule_set_name " FROM dba_apply) " ORDER BY rsr.rule_set_owner,rsr.rule_set_name; "PROMPT ___Macro_separator(9)___ "SELECT '|' || " apply_name || ' |' || " message_handler || ' |' || " ddl_handler || ' |' " FROM dba_apply " WHERE message_handler IS NOT NULL OR ddl_handler IS NOT NULL; "PROMPT ___Macro_separator(10)___ "SELECT '|' || " object_owner || '.' || object_name || ' |' || " operation_name || ' |' || " user_procedure || ' |' || " DECODE(error_handler,'Y','Error','N','DML','UNKNOWN') || ' |' || " apply_database_link || ' |' || " apply_name || ' |' " FROM dba_apply_dml_handlers; "PROMPT ___Macro_separator(11)___ "SELECT '|' || " o.owner || '.' || o.object_name || ' |' || " o.status || ' |' || " o.object_type || ' |' || " o.created || ' |' || " o.last_ddl_time || ' |' " FROM dba_objects o, " (SELECT DISTINCT user_procedure " FROM dba_apply_dml_handlers " WHERE user_procedure IS NOT NULL) h " WHERE o.owner = REPLACE(SUBSTR(h.user_procedure,1, " INSTR(h.user_procedure,'.',1,1) - 1),'"',NULL) " AND o.object_name = REPLACE(SUBSTR(h.user_procedure, " INSTR(h.user_procedure,'.',-1,1) + 1),'"',NULL); "PROMPT ___Macro_separator(12)___ "SELECT '|' || " ac.owner || '.' || ac.object_name || ' |' || " o.status || ' |' || " o.object_type || ' |' || " o.created || ' |' || " o.last_ddl_time || ' |' " FROM dba_objects o, " (SELECT DISTINCT REPLACE(SUBSTR(transform_function_name,1, " INSTR(transform_function_name,'.',1,1)-1),'"') owner, " REPLACE(SUBSTR(transform_function_name, " INSTR(transform_function_name,'.',-1,1) + 1),'"') object_name " FROM dba_streams_transform_function ) ac " WHERE o.owner = ac.owner " AND o.object_name = ac.object_name; "PROMPT ___Macro_separator(13)___ "SELECT '|' || " object_owner || '.' || object_name || ' |' || " method_name || ' |' || " resolution_column || ' |' || " column_name || ' |' " FROM dba_apply_conflict_columns " ORDER BY object_owner,object_name; "PROMPT ___Macro_separator(14)___ "SELECT '|' || " object_owner || ' |' || " object_name || ' |' || " column_name || ' |' || " apply_database_link || ' |' " FROM dba_apply_key_columns; "PROMPT ___Macro_separator(15)___ "SELECT '|' || " ap.apply_name || ' |' || " DECODE(ap.apply_captured,'YES','Captured LCRS', " 'NO','User-Enqueued', " 'UNKNOWN') || ' |' || " SUBSTR(s.program,INSTR(s.program,'(') + 1,4) || ' |' || " r.state || ' | ' || " r.total_messages_dequeued || '| ' || " r.sga_used || '| ' || " oldest_scn_num || '|' " FROM gv$streams_apply_reader r,gv$session s,dba_apply ap " WHERE r.sid = s.sid " AND r.serial# = s.serial# " AND r.apply_name = ap.apply_name; "PROMPT ___Macro_separator(16)___ "SELECT '|' || " apply_name || ' |' || " (dequeue_time - dequeued_message_create_time) * 86400 || ' |' || " TO_CHAR(dequeued_message_create_time, " 'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(dequeue_time,'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " dequeued_message_number || '| ' || " last_browse_num || '|' " FROM gv$streams_apply_reader; "PROMPT ___Macro_separator(17)___ "SELECT '|' || " ap.apply_name || ' |' || " SUBSTR(s.program,INSTR(s.program,'(') + 1,4) || ' |' || " c.state || ' | ' || " c.total_received || '| ' || " c.total_assigned || '| ' || " c.total_applied || '| ' || " c.total_errors || '| ' || " c.total_ignored || '| ' || " c.total_rollbacks || '| ' || " c.total_wait_deps || '| ' || " c.total_wait_commits || '|' " FROM gv$streams_apply_coordinator c,gv$session s,dba_apply ap " WHERE c.sid = s.sid " AND c.serial# = s.serial# " AND c.apply_name = ap.apply_name; "PROMPT ___Macro_separator(18)___ "SELECT '|' || " apply_name || ' |' || " TO_CHAR(lwm_message_create_time,'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " lwm_message_number || '|' || " TO_CHAR(lwm_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(hwm_message_create_time,'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " hwm_message_number || '|' || " TO_CHAR(hwm_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM gv$streams_apply_coordinator; "PROMPT ___Macro_separator(19)___ "SELECT '|' || " ap.apply_name || ' |' || " SUBSTR(s.program,INSTR(s.program,'(') + 1,4) || ' | ' || " a.server_id || '|' || " a.state || ' | ' || " a.total_assigned || '| ' || " a.total_messages_applied || '| ' || " a.applied_message_number || '| ' || " TO_CHAR(a.applied_message_create_time, " 'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " a.message_sequence || '|' " FROM gv$streams_apply_server a,gv$session s,dba_apply ap " WHERE a.sid = s.sid " AND a.serial# = s.serial# " AND a.apply_name = ap.apply_name; "PROMPT ___Macro_separator(20)___ "SELECT '| ' || " a.inst_id || '|' || " a.apply_name || ' | ' || " a.server_id || '|' || " q.sql_text || ' |' " FROM gv$streams_apply_server a,gv$sqltext q,gv$session s " WHERE a.sid = s.sid " AND s.sql_hash_value = q.hash_value " AND s.sql_address = q.address " AND s.sql_id = q.sql_id " ORDER BY a.apply_name,a.server_id,q.piece; "PROMPT ___Macro_separator(21)___ "SELECT '| ' || " a.inst_id || '|' || " a.apply_name || ' | ' || " a.server_id || '|' || " w.event || ' | ' || " w.seconds_in_wait || '|' " FROM gv$streams_apply_server a,gv$session_wait w " WHERE a.sid = w.sid " ORDER BY a.apply_name,a.server_id; "PROMPT ___Macro_separator(22)___ "SELECT '| ' || " a.inst_id || '|' || " a.apply_name || ' | ' || " a.server_id || '|' || " e.event || ' | ' || " e.total_waits || '| ' || " e.total_timeouts || '| ' || " e.time_waited || '| ' || " e.average_wait || '| ' || " e.max_wait || '|' " FROM gv$streams_apply_server a,gv$session_event e " WHERE a.sid = e.sid " ORDER BY a.apply_name,a.server_id; "PROMPT ___Macro_separator(23)___ "SELECT '|' || " apply_name || ' | ' || " server_id || '|' || " xidusn || '.' || xidslt || '.' || xidsqn || ' | ' || " commitscn || '|' || " dep_xidusn || '.' || dep_xidslt || '.' || dep_xidsqn || ' | ' || " dep_commitscn || '|' " FROM gv$streams_apply_server " ORDER BY apply_name,server_id; "PROMPT ___Macro_separator(24)___ "SELECT '|' || " apply_name || ' |' || " source_database || ' | ' || " applied_message_number || '| ' || " oldest_message_number || '|' || " TO_CHAR(apply_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(applied_message_create_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM dba_apply_progress; "PROMPT ___Macro_separator(25)___ "SELECT '|' || " apply_name || ' |' || " source_database || ' | ' || " source_commit_scn || '| ' || " message_number || '| ' || " message_count || '|' || " local_transaction_id || ' |' || " SUBSTR(error_message,1,10) || ' |' " FROM dba_apply_error " ORDER BY apply_name,source_commit_scn; "PROMPT ___Macro_separator(26)___ "SELECT '|' || " source_database || ' |' || " source_object_owner || '.' || source_object_name || ' | ' || " ignore_scn || '| ' || " instantiation_scn || '| ' || " apply_database_link || ' |' " FROM dba_apply_instantiated_objects " ORDER BY source_database,source_object_name; "PROMPT ___Macro_separator(27)___ "SELECT '|' || " source_db_name || ' |' || " name || ' |' || " dblink || ' | ' || " inst_scn || '| ' || " DECODE(global_flag,0,'SCHEMA',1,'DATABASE') || '|' "FROM sys.apply$_source_schema " ORDER BY source_db_name,name; "PROMPT ___Macro_separator(28)___ "SELECT '|' || " owner ||'.' || object_name || ' | ' || " object_id || '|' || " object_type || ' |' || " created || ' |' || " last_ddl_time || ' |' || " status || ' |' "FROM dba_objects " WHERE object_type IN ('RULE','RULE SET','CAPTURE','APPLY') " ORDER BY object_type,object_name; } } elsif match($ORACLE_VERSION,'^(102|11)') {var @TTL = ('',\ '---+ Apply Processes in Database',\ '---+ Apply Process Information',\ '---+ Apply Process Handlers',\ '---+ Apply Process Parameters',\ '---+ Streams Apply Rules Configured with DBMS_STREAMS_ADM \ Package',\ '---+ Streams Table Subsetting Rules',\ '---+ Apply Rules by RuleSet',\ '---+ Apply Rule Transformations by Rule Set',\ '---+ Apply Handlers',\ '---+ Apply Dml Handlers',\ '---+ DML Handler Status',\ '---+ Rule Transformation Status',\ '---+ Apply Enqueue Directives',\ '---+ Apply Execute Directives',\ '---+ Conflict Detection Control',\ '---+ Update Conflict Resolution Columns',\ '---+ Key Columns',\ '---+ Object Dependencies Set for Apply',\ '---+ Value Dependencies Set for Apply',\ '---+ Apply Reader Statistics 1',\ '---+ Apply Reader Statistics 2',\ '---+ Apply Reader Statistics 3',\ '---+ Apply Spilled Transactions',\ '---+ Apply Coordinator Statistics 1',\ '---+ Apply Coordinator Statistics 2',\ '---+ Apply Coordinator Statistics 3',\ '---+ Apply Server Statistics 1',\ '---+ Apply Server Statistics 2',\ '---+ Apply Server Statistics 3',\ '---+ Apply Server Statistics 4',\ '---+ Apply Server Statistics 5',\ '---+ Open Streams Apply Transactions 1',\ '---+ Open Streams Apply Transactions 2',\ '---+ Apply Progress',\ '---+ Error Queue',\ '---+ Instantiation Scns for Tables',\ '---+ Instantiation Scns for Schema and Database',\ '---+ Dba Objects - Rules and Streams Processes',\ '---+ Recoverable Tables Populated by \ Maintain_{Table|Schema|Global} Scripts',\ '---+ Recoverable Error',\ '---+ Identify Script Blocks',\ '---+ Streams Buffered Queue History for Last Day',\ '---+ Streams Buffered Subscriber History for Last Day',\ '---+ Streams Capture History for Last Day',\ '---+ Streams Apply History for Last Day') var @HDR = ('',\ '|*Apply Name*|*Queue*|*Apply Captured*|*Status*|*Apply User*|\ *Apply Tag*|*Rsn*|*Negative Rsn*|',\ '|*Apply Name*| *Max Applied Msg No*|*Status*|\ *Status Change Time*| *Error No*|*Error Msg*|',\ '|*Apply Name*|*Ddl Handler*|*Message Handler*|\ *Precommit Handler*|',\ '|*Apply Name*|*Parameter*|*Value*|*Set By User*|',\ '|*Name*|*Object*|*Rule Set Type*|*Source Db*|*Type*|\ *Include Tagged Lcr*|*Rule*|',\ '|*Name*|*Object*|*Type*|*Rule*|*Dml Condition*|\ *Subsetting Operation*|',\ '|*Rule Set*|*Rule Name*|*Condition*|',\ '|*Rule Set*|*Rule Owner*|*Rule Name*|*Transform Type*|\ *From Schema Name*|*To Schema Name*|*From Table Name*|\ *To Table Name*|*Schema Name*|*Table Name*|*From Column Name*|\ *To Column Name*|*Column Name*|*Column Value*|*Column Type*|\ *Column Function*|*Value Type*|*User Function Name*|\ *Subsetting Operation*|*Dml Condition*|*Declarative Type*|\ *Precedence*|*Step Number*|',\ '|*Apply Name*|*Msg Handler*|*DDL Handler*|',\ '|*Object*|*Operation*|*User Procedure*|*Typ*|*DB Link*|\ *Apply Name*|*Assemble Lobs*|',\ '|*Object*|*Status*|*Object Type*|*Created*|*Last Ddl Time*|',\ '|*Object*|*Status*|*Object Type*|*Created*|*Last Ddl Time*|',\ '|*Rule Owner*|*Rule Name*|*Destination Queue Name*|',\ '|*Rule Owner*|*Rule Name*|*Execute Event*|',\ '|*Object Owner*|*Object Name*|*Column Name*|\ *Compare Old On Delete*|*Compare Old On Update*|*Db Link*|',\ '|*Object*|*Method*|*Resolution Column*|*Column Name*|*DB Link*|',\ '|*Object Owner*|*Object*|*Column*|*DB Link*|',\ '|*Object Owner*|*Object Name*|*Parent Object Owner*|\ *Parent Object Name*|',\ '|*Dependency Name*|*Object Owner*|*Object Name*|*Column Name*| \ *Column Position*|',\ '|*Apply Name*|*Apply Captured*|*Process Name*|*State*| \ *Total Msgs Dequeued*| *Total Msgs Spilled*| *Sga Used*| \ *Oldest Scn Num*|*Oldest Transaction Id*|',\ '|*Apply Name*| *Latency*|*Creation*|*Last Dequeue*| \ *Dequeued Msg No*| *Last Browse No*|',\ '|*Apply Name*| *Total Msgs Dequeued*| *Total Msgs Spilled*| \ *Elapsed Dequeue*| *Elapsed Schedule*| *Elapsed Spill*|',\ '|*Apply Name*|*Xidusn*|*Xidslt*|*Xidsqn*| *First Scn*| \ *Msg Count*|*First Msg Create Time*|*Spill Creation Time*|',\ '|*Apply Name*|*Process*|*State*| *Recieved*| *Assigned*|\ *Applied*| *Errors*| *Total Ignored*| *Total Rollbacks*| \ *Wait Deps*| *Wait Commits*|',\ '|*Apply Name*|*Lwm Msg Ts*| *Lwm Msg Nbr*|*Lwm Updated*|\ *Hwm Msg Ts*| *Hwm Msg Nbr*|*Hwm Updated*|',\ '|*Apply Name*| *Total Received*| *Total Assigned*| \ *Total Applied*| *Elapsed Schedule*| *Elapsed Idle*|',\ '|*Apply Name*|*Process Name*| *Server*|*State*| *Assigned*| \ *Msg Applied*| *Applied Msg No*|*Applied Msg Create Time*| \ *Msg Seq*| *Elapsed Dequeue Time*| *Elapsed Apply Time*|\ *Apply Time*|',\ '| *Inst Id*|*Apply Name*| *Server Id*|*Sql Text*|',\ '| *Inst Id*|*Apply Name*| *Server Id*|*Event*| \ *Seconds In Wait*|',\ '| *Inst Id*|*Apply Name*| *Server Id*|*Event*| *Total Waits*| \ *Total Timeouts*| *Time Waited*| *Average Wait*| *Max Wait*|',\ '|*Apply Name*| *Server Id*|*Current Txn*| *Commitscn*|\ *Dependent Txn*| *Dep Commitscn*|',\ '|*Streams Name*| *Open Transactions*| *Total LCRs*|',\ '| *Inst Id*|*Streams Name*|*Streams Type*|*Xidusn*|*Xidslt*|\ *Xidsqn*| *Cumulative Msg Cnt*| *Total Msg Cnt*|\ *First Msg Time*| *First Msg No*|*Last Msg Time*| \ *Last Msg No*|',\ '|*Apply Name*|*Source Db*| *Applied Msg No*| *Oldest Msg No*|\ *Apply Time*|*Applied Msg Create Time*| \ *Oldest Transaction Id*|',\ '|*Apply Name*|*Source Db*| *Source Commit Scn*| *Msg No*| \ *Msg Cnt*| *Local Transaction Id*|*Error Msg*|\ *Error Creation Time*|',\ '|*Source Db*|*Object*| *Ignore Scn*| *Instantiation Scn*|\ *Db Link*|',\ '|*Source Db*|*Object*|*Dblink*| *Inst Scn*| *Global Flag*|',\ '|*Object*| *Object Id*|*Object Type*|*Created*|*Last Ddl Time*|\ *Status*|',\ '|*Sysdate*|*Creation Time*|*Procedure*|*Status*|*Progress*| \ *Elapsed Seconds*| *Script Id*| *Current Step*|',\ '| *Script Id*| *Block Num*| *Error Number*|*Error Message*|\ *Error Creation Time*|',\ '| *Script Id*| *Block Num*|*Status*|*Forward Block Dblink*| \ *Forward Block*|',\ '|*Begin Interval Time*|*End Interval Time*| *Snap Id*| *Dbid*| \ *Instance Number*|*Queue Schema*|*Queue Name*|*Startup Time*| \ *Queue Id*| *Num Msgs*| *Spill Msgs*| *Cnum Msgs*| \ *Cspill Msgs*|',\ '|*Begin Interval Time*|*End Interval Time*| *Snap Id*| *Dbid*| \ *Instance Number*|*Queue Schema*|*Queue Name*| *Subscriber Id*|\ *Subscriber Name*|*Subscriber Address*|*Subscriber Type*|\ *Startup Time*| *Num Msgs*| *Cnum Msgs*| *Total Spilled Msg*|',\ '|*Begin Interval Time*|*End Interval Time*| *Snap Id*| *Dbid*| \ *Instance Number*|*Capture Name*|*Startup Time*| *Lag(Sec)*| \ *Total Messages Captured*| *Total Messages Enqueued*| \ *Elapsed Rule Time*| *Elapsed Enqueue Time*| \ *Elapsed Redo Wait Time*| *Elapsed Pause Time*|',\ '|*Begin Interval Time*|*End Interval Time*| *Snap Id*| *Dbid*| \ *Instance Number*|*Apply Name*|*Startup Time*| \ *Reader Total Messages Dequeued*| *Reader Lag*| \ *Coord Total Received*| *Coord Total Applied*| \ *Coord Total Rollbacks*| *Coord Total Wait Deps*| \ *Coord Total Wait Cmts*| *Coord Lwm Lag*| \ *Server Total Messages Applied*| \ *Server Elapsed Dequeue Time*| *Server Elapsed Apply Time*|') set $sql {SET serveroutput on size 1000000 "SELECT '|' || " apply_name || ' |' || " queue_owner || '.' || queue_name || ' |' || " DECODE(apply_captured,'YES','Captured','NO','User-Enqueued') || ' |' || " status || ' |' || " apply_user || ' |' || " apply_tag || ' |' || " rule_set_owner || '.' || rule_set_name || ' |' || " negative_rule_set_owner || '.' || negative_rule_set_name || ' |' " FROM dba_apply; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " apply_name || ' | ' || " max_applied_message_number || '|' || " status || ' |' || " TO_CHAR(status_change_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " error_number || '|' || " error_message || ' |' " FROM dba_apply; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " apply_name || ' |' || " ddl_handler || ' |' || " message_handler || ' |' || " precommit_handler || ' |' " FROM dba_apply; "PROMPT ___Macro_separator(4)___ "SELECT '|' || " apply_name || ' |' || " parameter || ' |' || " value || ' |' || " set_by_user || ' |' " FROM dba_apply_parameters; "PROMPT ___Macro_separator(5)___ "SELECT '|' || " streams_name || ' |' || " schema_name || '.' || object_name || ' |' || " rule_set_type || ' |' || " source_database || ' |' || " streams_rule_type || ' ' || rule_type || ' |' || " include_tagged_lcr || ' |' || " rule_owner||'.'||rule_name || ' |' " FROM dba_streams_rules " WHERE streams_type = 'APPLY' " ORDER BY streams_name,object_name,source_database,rule_set_type,rule_name; "PROMPT ___Macro_separator(6)___ "SELECT '|' || " streams_name || ' |' || " schema_name || '.' || object_name || ' |' || " rule_type || ' TABLE RULE' || ' |' || " rule_owner || '.' || rule_name || ' |' || " dml_condition || ' |' || " subsetting_operation || ' |' " FROM dba_streams_rules " WHERE streams_type = 'APPLY' " AND (dml_condition IS NOT NULL OR subsetting_operation IS NOT NULL); "PROMPT ___Macro_separator(7)___ "SET long 65536 "SELECT '[[[' || CHR(10) || '|' || " rsr.rule_set_owner || '.' || rsr.rule_set_name || ' |' || " rsr.rule_owner || '.' || rsr.rule_name || ' |' || " REPLACE(REPLACE(REPLACE(LTRIM(r.rule_condition), " '|','|'), " '<','<'), " '>','>') || '|' || " CHR(10) || ']]]' " FROM dba_rule_set_rules rsr,dba_rules r " WHERE rsr.rule_name = r.rule_name " AND rsr.rule_owner = r.rule_owner " AND rule_set_name IN " (SELECT rule_set_name " FROM dba_apply) " ORDER BY rsr.rule_set_owner,rsr.rule_set_name; "PROMPT ___Macro_separator(8)___ "DECLARE " CURSOR c_apply IS " SELECT rsr.rule_set_owner, " rsr.rule_set_name, " r.rule_owner, " r.rule_name, " r.transform_type, " r.from_schema_name, " r.to_schema_name, " r.from_table_name, " r.to_table_name, " r.schema_name, " r.table_name, " r.from_column_name, " r.to_column_name, " r.column_name, " r.column_value, " r.column_type, " r.column_function, " r.value_type, " r.user_function_name, " r.subsetting_operation, " r.dml_condition, " r.declarative_type, " r.precedence, " r.step_number " FROM dba_rule_set_rules rsr,dba_streams_transformations r " WHERE r.rule_name = rsr.rule_name " AND r.rule_owner = rsr.rule_owner " AND rule_set_name IN " (SELECT rule_set_name " FROM dba_capture) " ORDER BY rsr.rule_set_owner,rsr.rule_set_name,r.rule_owner, " r.rule_name,r.transform_type DESC,r.step_number,r.precedence; " l_str VARCHAR2(100); " l_int PLS_INTEGER; "BEGIN " FOR r_rec IN c_apply LOOP " BEGIN " l_int := r_rec.column_value.getvarchar2(l_str); " EXCEPTION " WHEN OTHERS THEN " l_str := ''; " END; " dbms_output.put('|' || " r_rec.rule_set_owner ||'.'|| r_rec.rule_set_name || ' |' || " r_rec.rule_owner || ' |' || " r_rec.rule_name || ' |' || " r_rec.transform_type || ' |' || " r_rec.from_schema_name || ' |' || " r_rec.to_schema_name || ' |' || " r_rec.from_table_name || ' |' || " r_rec.to_table_name || ' |' || " r_rec.schema_name || ' |' || " r_rec.table_name || ' |' ); " dbms_output.put(r_rec.from_column_name || ' |' || " r_rec.to_column_name || ' |' || " r_rec.column_name || ' |' ); " dbms_output.put(l_str || ' |' || " r_rec.column_type || ' |' || " r_rec.column_function || ' |' || " r_rec.value_type || ' |' || " r_rec.user_function_name || ' |' || " r_rec.subsetting_operation || ' |' || " r_rec.dml_condition || ' |' || " r_rec.declarative_type || ' |' || " r_rec.precedence || ' |' || " r_rec.step_number || ' |'); " dbms_output.new_line; " END LOOP; "END; "/ "PROMPT ___Macro_separator(9)___ "SELECT '|' || " apply_name || ' |' || " message_handler || ' |' || " ddl_handler || ' |' " FROM dba_apply " WHERE message_handler IS NOT NULL OR ddl_handler IS NOT NULL; "PROMPT ___Macro_separator(10)___ "SELECT '|' || " object_owner || '.' || object_name || ' |' || " operation_name || ' |' || " user_procedure || ' |' || " DECODE(error_handler,'Y','Error','N','DML','UNKNOWN') || ' |' || " apply_database_link || ' |' || " apply_name || ' |' || " assemble_lobs || ' |' " FROM dba_apply_dml_handlers; "PROMPT ___Macro_separator(11)___ "SELECT '|' || " o.owner || '.' || o.object_name || ' |' || " o.status || ' |' || " o.object_type || ' |' || " o.created || ' |' || " o.last_ddl_time || ' |' " FROM dba_objects o, " (SELECT DISTINCT user_procedure " FROM dba_apply_dml_handlers " WHERE user_procedure IS NOT NULL) h " WHERE o.owner = REPLACE(SUBSTR(h.user_procedure,1, " INSTR(h.user_procedure,'.',1,1) - 1),'"',NULL) " AND o.object_name = REPLACE(SUBSTR(h.user_procedure, " INSTR(h.user_procedure,'.',-1,1) + 1),'"',NULL); "PROMPT ___Macro_separator(12)___ "SELECT '|' || " ac.owner || '.' || ac.object_name || ' |' || " o.status || ' |' || " o.object_type || ' |' || " o.created || ' |' || " o.last_ddl_time || ' |' " FROM dba_objects o, " (SELECT DISTINCT REPLACE(SUBSTR(transform_function_name,1, " INSTR(transform_function_name,'.',1,1)-1),'"') owner, " REPLACE(SUBSTR(transform_function_name, " INSTR(transform_function_name,'.',-1,1) + 1),'"') object_name " FROM dba_streams_transform_function) ac " WHERE o.owner = ac.owner " AND o.object_name = ac.object_name; "PROMPT ___Macro_separator(13)___ "SELECT '|' || " rule_owner || ' |' || " rule_name || ' |' || " destination_queue_name || ' |' " FROM dba_apply_enqueue; "PROMPT ___Macro_separator(14)___ "SELECT '|' || " rule_owner || ' |' || " rule_name || ' |' || " execute_event || ' |' " FROM dba_apply_execute; "PROMPT ___Macro_separator(15)___ "SELECT '|' || " object_owner || ' |' || " object_name || ' |' || " column_name || ' |' || " compare_old_on_delete || ' |' || " compare_old_on_update || ' |' || " apply_database_link || ' |' " FROM dba_apply_table_columns; "PROMPT ___Macro_separator(16)___ "SELECT '|' || " object_owner || '.' || object_name || ' |' || " method_name || ' |' || " resolution_column || ' |' || " column_name || ' |' || " apply_database_link || ' |' " FROM dba_apply_conflict_columns " ORDER BY object_owner,object_name; "PROMPT ___Macro_separator(17)___ "SELECT '|' || " object_owner || ' |' || " object_name || ' |' || " column_name || ' |' || " apply_database_link || ' |' " FROM dba_apply_key_columns; "PROMPT ___Macro_separator(18)___ "SELECT '|' || " object_owner || ' |' || " object_name || ' |' || " parent_object_owner || ' |' || " parent_object_name || ' |' " FROM dba_apply_object_dependencies; "PROMPT ___Macro_separator(19)___ "SELECT '|' || " dependency_name || ' |' || " object_owner || ' |' || " object_name || ' |' || " column_name || ' | ' || " column_position || '|' " FROM dba_apply_value_dependencies; "PROMPT ___Macro_separator(20)___ "SELECT '|' || " ap.apply_name || ' |' || " DECODE(ap.apply_captured,'YES','Captured LCRS', " 'NO','User-Enqueued', " 'UNKNOWN') || ' |' || " SUBSTR(s.program,INSTR(s.program,'(') + 1,4) || ' |' || " r.state || ' | ' || " r.total_messages_dequeued || '| ' || " r.total_messages_spilled || '| ' || " r.sga_used || '| ' || " oldest_scn_num || '|' || " oldest_xidusn || '.' || oldest_xidslt || '.' || oldest_xidsqn || ' |' " FROM gv$streams_apply_reader r,gv$session s,dba_apply ap " WHERE r.sid = s.sid " AND r.serial# = s.serial# " AND r.apply_name = ap.apply_name; "PROMPT ___Macro_separator(21)___ "SELECT '|' || " apply_name || ' |' || " (dequeue_time-dequeued_message_create_time) * 86400 || ' |' || " TO_CHAR(dequeued_message_create_time, " 'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(dequeue_time,'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " dequeued_message_number || '| ' || " last_browse_num || '|' " FROM gv$streams_apply_reader; "PROMPT ___Macro_separator(22)___ "SELECT '|' || " apply_name || ' | ' || " total_messages_dequeued || '| ' || " total_messages_spilled || '| ' || " elapsed_dequeue_time || '| ' || " elapsed_schedule_time || '| ' || " elapsed_spill_time || '|' " FROM gv$streams_apply_reader; "PROMPT ___Macro_separator(23)___ "SELECT '|' || " apply_name || ' | ' || " xidusn || '| ' || " xidslt || '| ' || " xidsqn || '| ' || " first_scn || '| ' || " message_count || '|' || " TO_CHAR(first_message_create_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(spill_creation_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM dba_apply_spill_txn; "PROMPT ___Macro_separator(24)___ "SELECT '|' || " ap.apply_name || ' |' || " SUBSTR(s.program,INSTR(s.program,'(') + 1,4) || ' |' || " c.state || ' | ' || " c.total_received || '| ' || " c.total_assigned || '| ' || " c.total_applied || '| ' || " c.total_errors || '| ' || " c.total_ignored || '| ' || " c.total_rollbacks || '| ' || " c.total_wait_deps || '| ' || " c.total_wait_commits || '|' " FROM gv$streams_apply_coordinator c,gv$session s,dba_apply ap " WHERE c.sid = s.sid " AND c.serial# = s.serial# " AND c.apply_name = ap.apply_name; "PROMPT ___Macro_separator(25)___ "SELECT '|' || " apply_name || ' |' || " TO_CHAR(lwm_message_create_time,'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " lwm_message_number || '|' || " TO_CHAR(lwm_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(hwm_message_create_time,'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " hwm_message_number || '|' || " TO_CHAR(hwm_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM gv$streams_apply_coordinator; "PROMPT ___Macro_separator(26)___ "SELECT '|' || " apply_name || ' | ' || " total_received || '| ' || " total_assigned || '| ' || " total_applied || '| ' || " elapsed_schedule_time || '| ' || " elapsed_idle_time || '|' " FROM gv$streams_apply_coordinator; "PROMPT ___Macro_separator(27)___ "SELECT '|' || " ap.apply_name || ' |' || " SUBSTR(s.program,INSTR(s.program,'(') + 1,4) || ' | ' || " a.server_id || '|' || " a.state || ' | ' || " a.total_assigned || '| ' || " a.total_messages_applied || '| ' || " a.applied_message_number || '| ' || " TO_CHAR(a.applied_message_create_time, " 'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " a.message_sequence || '| ' || " a.elapsed_dequeue_time || '| ' || " a.elapsed_apply_time || '|' || " TO_CHAR(a.apply_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM gv$streams_apply_server a,gv$session s,dba_apply ap " WHERE a.sid = s.sid " AND a.serial# = s.serial# " AND a.apply_name = ap.apply_name " ORDER BY a.apply_name,a.server_id; "PROMPT ___Macro_separator(28)___ "SELECT '| ' || " a.inst_id || '|' || " a.apply_name || ' | ' || " a.server_id || '|' || " q.sql_text || ' |' " FROM gv$streams_apply_server a,gv$sqltext q,gv$session s " WHERE a.sid = s.sid " AND s.sql_hash_value = q.hash_value " AND s.sql_address = q.address " AND s.sql_id = q.sql_id " ORDER BY a.apply_name,a.server_id,q.piece; "PROMPT ___Macro_separator(29)___ "SELECT '| ' || " a.inst_id || '|' || " a.apply_name || ' | ' || " a.server_id || '|' || " w.event || ' | ' || " w.seconds_in_wait || '|' " FROM gv$streams_apply_server a,gv$session_wait w " WHERE a.sid = w.sid " ORDER BY a.apply_name,a.server_id; "PROMPT ___Macro_separator(30)___ "SELECT '| ' || " a.inst_id || '|' || " a.apply_name || ' | ' || " a.server_id || '|' || " e.event || ' | ' || " e.total_waits || '| ' || " e.total_timeouts || '| ' || " e.time_waited || '| ' || " e.average_wait || '| ' || " e.max_wait || '|' " FROM gv$streams_apply_server a,gv$session_event e " WHERE a.sid = e.sid " ORDER BY a.apply_name,a.server_id,e.time_waited desc; "PROMPT ___Macro_separator(31)___ "SELECT '|' || " apply_name || ' | ' || " server_id || '|' || " xidusn || '.' || xidslt || '.' || xidsqn || ' | ' || " commitscn || '|' || " dep_xidusn || '.' || dep_xidslt || '.' || dep_xidsqn || ' | ' || " dep_commitscn || '|' " FROM gv$streams_apply_server " ORDER BY apply_name,server_id; "PROMPT ___Macro_separator(32)___ "SELECT '|' || " streams_name || ' | ' || " COUNT(*) || '| ' || " SUM(cumulative_message_count) || '|' " FROM gv$streams_transaction " WHERE streams_type = 'APPLY' " GROUP BY streams_name; "PROMPT ___Macro_separator(33)___ "SELECT '| ' || " inst_id || '|' || " streams_name || ' |' || " streams_type || ' | ' || " xidusn || '| ' || " xidslt || '| ' || " xidsqn || '| ' || " cumulative_message_count || '| ' || " total_message_count || '|' || " first_message_time || ' | ' || " first_message_number || '|' || " last_message_time || ' | ' || " last_message_number || '|' " FROM gv$streams_transaction " WHERE streams_type = 'APPLY' " ORDER BY streams_name,first_message_number; "PROMPT ___Macro_separator(34)___ "SELECT '|' || " apply_name || ' |' || " source_database || ' | ' || " applied_message_number || '| ' || " oldest_message_number || '|' || " TO_CHAR(apply_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(applied_message_create_time, " 'DD-Mon-YYYY HH24:MI:SS') || ' |' || " oldest_transaction_id || ' |' " FROM dba_apply_progress; "PROMPT ___Macro_separator(35)___ "SELECT '|' || " apply_name || ' |' || " source_database || ' | ' || " source_commit_scn || '| ' || " message_number || '| ' || " message_count || '|' || " local_transaction_id || ' |' || " SUBSTR(error_message,1,10) || ' |' || " error_creation_time || ' |' " FROM dba_apply_error " ORDER BY apply_name,source_commit_scn; "PROMPT ___Macro_separator(36)___ "SELECT '|' || " source_database || ' |' || " source_object_owner || '.' || source_object_name || ' | ' || " ignore_scn || '| ' || " instantiation_scn || '|' || " apply_database_link || ' |' " FROM dba_apply_instantiated_objects " ORDER BY source_database,source_object_name; "PROMPT ___Macro_separator(37)___ "SELECT '|' || " source_db_name || ' |' || " name || ' |' || " dblink || ' | ' || " inst_scn || '|' || " DECODE(global_flag,0,'SCHEMA',1,'DATABASE') || ' |' " FROM sys.apply$_source_schema " ORDER BY source_db_name,name; "PROMPT ___Macro_separator(38)___ "SELECT '|' || " owner ||'.' || object_name || ' | ' || " object_id || '|' || " object_type || ' |' || " created || ' |' || " last_ddl_time || ' |' || " status || ' |' " FROM dba_objects " WHERE object_type IN ('RULE','RULE SET','CAPTURE','APPLY') " ORDER BY object_type,object_name; "PROMPT ___Macro_separator(39)___ "SELECT '|' || " TO_CHAR(SYSDATE,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(rs.creation_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " rs.invoking_package || '.' || rs.invoking_procedure || ' |' || " rs.status || ' | ' || " rs.done_block_num||' of '||rs.total_blocks||' Steps Completed' " || '| ' || " TO_NUMBER(SYSDATE - rs.creation_time) * 86400 || '|' || " rs.script_id || ' | ' || " rsb.forward_block || '|' " FROM dba_recoverable_script rs,dba_recoverable_script_blocks rsb " WHERE rs.script_id = rsb.script_id " AND rsb.block_num = rs.done_block_num + 1; "PROMPT ___Macro_separator(40)___ "SELECT '|' || " script_id || ' | ' || " block_num || '| ' || " error_number || '|' || " error_message || ' |' || " TO_CHAR(error_creation_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM dba_recoverable_script_errors; "PROMPT ___Macro_separator(41)___ "SELECT '|' || " script_id || ' | ' || " block_num || '|' || " status || ' |' || " forward_block_dblink || ' |' || " forward_block || ' |' " FROM dba_recoverable_script_blocks " ORDER BY script_id,block_num; "PROMPT ___Macro_separator(42)___ "SELECT '|' || " s.begin_interval_time || ' |' || " s.end_interval_time || ' | ' || " bq.snap_id || '| ' || " bq.dbid || '| ' || " bq.instance_number || '|' || " bq.queue_schema || ' |' || " bq.queue_name || ' |' || " TO_CHAR(bq.startup_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " bq.queue_id || '| ' || " bq.num_msgs || '| ' || " bq.spill_msgs || '| ' || " bq.cnum_msgs || '| ' || " bq.cspill_msgs || '|' " FROM dba_hist_buffered_queues bq,dba_hist_snapshot s " WHERE bq.snap_id = s.snap_id " AND s.end_interval_time >= systimestamp - 1 " ORDER BY bq.queue_schema,bq.queue_name,s.end_interval_time; "PROMPT ___Macro_separator(43)___ "SELECT '|' || " s.begin_interval_time || ' |' || " s.end_interval_time || ' | ' || " bs.snap_id || '| ' || " bs.dbid || '| ' || " bs.instance_number || '|' || " bs.queue_schema || ' |' || " bs.queue_name || ' | ' || " bs.subscriber_id || '|' || " bs.subscriber_name || ' |' || " bs.subscriber_address || ' |' || " bs.subscriber_type || ' |' || " TO_CHAR(bs.startup_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " bs.num_msgs || '| ' || " bs.cnum_msgs || '| ' || " bs.total_spilled_msg || '|' " FROM dba_hist_buffered_subscribers bs,dba_hist_snapshot s " WHERE bs.snap_id = s.snap_id " AND s.end_interval_time >= systimestamp - 1 " ORDER BY bs.queue_schema,bs.queue_name,bs.subscriber_id,s.end_interval_time; "PROMPT ___Macro_separator(44)___ "SELECT '|' || " s.begin_interval_time || ' |' || " s.end_interval_time || ' | ' || " sc.snap_id || '| ' || " sc.dbid || '| ' || " sc.instance_number || '|' || " sc.capture_name || ' |' || " TO_CHAR(sc.startup_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " sc.lag || '| ' || " sc.total_messages_captured || '| ' || " sc.total_messages_enqueued || '| ' || " sc.elapsed_rule_time || '| ' || " sc.elapsed_enqueue_time || '| ' || " sc.elapsed_redo_wait_time || '| ' || " sc.elapsed_pause_time || '|' " FROM dba_hist_streams_capture sc,dba_hist_snapshot s " WHERE sc.snap_id = s.snap_id " AND s.end_interval_time >= systimestamp - 1 " ORDER BY sc.capture_name,s.end_interval_time; "PROMPT ___Macro_separator(45)___ "SELECT '|' || " s.begin_interval_time || ' |' || " s.end_interval_time || ' | ' || " sa.snap_id || '| ' || " sa.dbid || '| ' || " sa.instance_number || '|' || " sa.apply_name || ' |' || " TO_CHAR(sa.startup_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " sa.reader_total_messages_dequeued || '| ' || " sa.reader_lag || '| ' || " sa.coord_total_received || '| ' || " sa.coord_total_applied || '| ' || " sa.coord_total_rollbacks || '| ' || " sa.coord_total_wait_deps || '| ' || " sa.coord_total_wait_cmts || '| ' || " sa.coord_lwm_lag || '| ' || " sa.server_total_messages_applied || '| ' || " sa.server_elapsed_dequeue_time || '| ' || " sa.server_elapsed_apply_time || '|' " FROM dba_hist_streams_apply_sum sa,dba_hist_snapshot s " WHERE sa.snap_id = s.snap_id " AND s.end_interval_time >= systimestamp - 1 " ORDER BY sa.apply_name,s.end_interval_time; } } if $sql {call separator(1) call writeSql($sql,2) call separator(0,'Apply Process Information') } =head2 rules - Rules Analysis Provides potential discrepancies in rule definitions. =cut debug ' Inside the STC module, analyzing rules' report rules var $TTL = '---+!! Rules Analysis' if match($ORACLE_VERSION,'92') {var @TTL = ('',\ '---+ Missing Rules in dba_rules',\ '---+ Extra Rules in dba_rules',\ '---+ Rule Conditions Do Not Match between Streams and Rules',\ '---+ Source Database Name Does Not Match for Capture or \ Propagation Rules',\ '---+ Global Rule for Capture Specified but Condition not \ Modified',\ '---+ No Rule Set Defined for Capture',\ '---+ Apply Rules with no Source Database Specified',\ '---+ Schema Rules for non Existant Schema',\ '---+ Table Rules for non Existant Object') var @HDR = ('',\ '|*Rule Owner*|*Rule Name*|',\ '|*Rule Owner*|*Rule Name*|',\ '|*Streams Type*|*Streams Name*|*Rule*|*Rule Condition*|',\ '|*Streams Type*|*Streams Name*|*Rule*|',\ '|*Streams Name*|*Rule*|',\ '|*Capture Name*|',\ '|*Streams Name*|*Rule*|*Object*|',\ '|*Streams Type*|*Streams Name*|*Rule*|*Action Context Name*|\ *Action Context Value*|',\ '|*Streams Type*|*Streams Name*|*Rule*|*Object*|') set $sql {SELECT '|' || " rule_owner || ' |' || " rule_name || ' |' " FROM (SELECT rule_owner, " rule_name, " rule_condition, " source_database, " schema_name table_name, " NULL, " rule_type, " streams_type, " streams_name " FROM dba_streams_schema_rules " UNION ALL " SELECT rule_owner, " rule_name, " rule_condition, " source_database, " 'GLOBAL_RULE', " 'GLOBAL_RULE', " rule_type, " streams_type, " streams_name " FROM dba_streams_global_rules) "MINUS "SELECT '|' || " rule_owner || ' |' || " rule_name || ' |' " FROM dba_rules; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " rule_owner || ' |' || " rule_name || ' |' " FROM dba_rules "MINUS "SELECT '|' || " rule_owner || ' |' || " rule_name || ' |' " FROM (SELECT rule_owner, " rule_name, " rule_condition, " source_database, " schema_name table_name, " NULL, " rule_type, " streams_type, " streams_name " FROM dba_streams_schema_rules " UNION ALL " SELECT rule_owner, " rule_name, " rule_condition, " source_database, " 'GLOBAL_RULE', " 'GLOBAL_RULE', " rule_type, " streams_type, " streams_name " FROM dba_streams_global_rules); "PROMPT ___Macro_separator(3)___ "SET long 65536 "SELECT '[[[' || CHR(10) || '|' || " s.streams_type || ' |' || " s.streams_name || ' |' || " r.rule_owner || '.' || r.rule_name || ' |' || " REPLACE(REPLACE(REPLACE(LTRIM(r.rule_condition), " '|','|'), " '<','<'), " '>','>') || '|' || " CHR(10) || ']]]' " FROM (SELECT rule_owner, " rule_name, " rule_condition, " source_database, " schema_name table_name, " NULL, " rule_type, " streams_type, " streams_name " FROM dba_streams_schema_rules " UNION ALL " SELECT rule_owner, " rule_name, " rule_condition, " source_database, " 'GLOBAL_RULE', " 'GLOBAL_RULE', " rule_type, " streams_type, " streams_name " FROM dba_streams_global_rules) s, " dba_rules r "WHERE r.rule_name = s.rule_name " AND r.rule_owner = s.rule_owner " AND s.rule_condition != dbms_lob.substr(r.rule_condition); "PROMPT ___Macro_separator(4)___ "SELECT '|' || " streams_type || ' |' || " streams_name || ' |' || " r.rule_owner || '.' || r.rule_name || ' |' " FROM (SELECT rule_owner, " rule_name, " rule_condition, " source_database, " schema_name table_name, " NULL, " rule_type, " streams_type, " streams_name " FROM dba_streams_schema_rules " UNION ALL " SELECT rule_owner, " rule_name, " rule_condition, " source_database, " 'GLOBAL_RULE', " 'GLOBAL_RULE', " rule_type, " streams_type, " streams_name " FROM dba_streams_global_rules) r " WHERE source_database IS NOT NULL " AND source_database != " (SELECT global_name " FROM global_name) " AND streams_type IN ('CAPTURE','PROPAGATION'); "PROMPT ___Macro_separator(5)___ "SELECT '|' || " streams_name || ' |' || " r.rule_owner || '.' || r.rule_name || ' |' " FROM (SELECT rule_owner, " rule_name, " rule_condition, " source_database, " schema_name table_name, " NULL, " rule_type, " streams_type, " streams_name " FROM dba_streams_schema_rules " UNION ALL " SELECT rule_owner, " rule_name, " rule_condition, " source_database, " 'GLOBAL_RULE', " 'GLOBAL_RULE', " rule_type, " streams_type, " streams_name " FROM dba_streams_global_rules) s, " dba_rules r " WHERE streams_type = 'CAPTURE' " AND table_name = 'GLOBAL_RULE' " AND r.rule_name = s.rule_name " AND r.rule_owner = s.rule_owner " AND s.rule_condition = dbms_lob.substr(r.rule_condition); "PROMPT ___Macro_separator(6)___ "SELECT '|' || " capture_name || ' |' " FROM dba_capture " WHERE rule_set_name IS NULL; "PROMPT ___Macro_separator(7)___ "SELECT '|' || " streams_name || ' |' || " s.rule_owner || '.' || s.rule_name || ' |' || " s.table_name || ' |' " FROM (SELECT rule_owner, " rule_name, " rule_condition, " source_database, " schema_name table_name, " NULL, " rule_type, " streams_type, " streams_name " FROM dba_streams_schema_rules " UNION ALL " SELECT rule_owner, " rule_name, " rule_condition, " source_database, " 'GLOBAL_RULE', " 'GLOBAL_RULE', " rule_type, " streams_type, " streams_name " FROM dba_streams_global_rules) s, " dba_rules r " WHERE s.streams_type = 'APPLY' " AND s.source_database IS NULL " AND r.rule_name = s.rule_name " AND r.rule_owner = s.rule_owner " AND s.rule_condition = dbms_lob.substr(r.rule_condition); "PROMPT ___Macro_separator(8)___ "SELECT '|' || " s.streams_type || ' |' || " s.streams_name || ' |' || " s.rule_owner || '.' || s.rule_name || ' |' || " ac.nvn_name || ' |' || " ac.nvn_value.accessvarchar2() || ' |' " FROM (SELECT rule_owner, " rule_name, " rule_condition, " source_database, " schema_name table_name, " NULL, " rule_type, " streams_type, " streams_name " FROM dba_streams_schema_rules " UNION ALL " SELECT rule_owner, " rule_name, " rule_condition, " source_database, " 'GLOBAL_RULE', " 'GLOBAL_RULE', " rule_type, " streams_type, " streams_name " FROM dba_streams_global_rules) s, " dba_rules r, " dba_users u, " TABLE(r.rule_action_context.actx_list) ac " WHERE s.table_name IS NULL " AND r.rule_owner = s.rule_owner " AND r.rule_name = s.rule_name " AND ac.nvn_value.accessvarchar2() IS NULL; "PROMPT ___Macro_separator(9)___ "SELECT '|' || " s.streams_type || ' |' || " streams_name || ' |' || " s.rule_owner || '.' || s.rule_name || ' |' || " s.table_name || ' |' || " ac.nvn_name || ' |' || " ac.nvn_value.accessvarchar2() || ' |' " FROM (SELECT rule_owner, " rule_name, " rule_condition, " source_database, " schema_name table_name, " NULL, " rule_type, " streams_type, " streams_name " FROM dba_streams_schema_rules " UNION ALL " SELECT rule_owner, " rule_name, " rule_condition, " source_database, " 'GLOBAL_RULE', " 'GLOBAL_RULE', " rule_type, " streams_type, " streams_name " FROM dba_streams_global_rules) s, " dba_rules r, " dba_objects o, " TABLE(r.rule_action_context.actx_list) ac " WHERE o.object_name = s.table_name " AND r.rule_owner = s.rule_owner " AND r.rule_name = s.rule_name " AND ac.nvn_value.accessvarchar2() IS NULL; } } elsif match($ORACLE_VERSION,'101') {var @TTL = ('',\ '---+ Missing Rules in dba_rules',\ '---+ Extra Rules in dba_rules',\ '---+ Rule Conditions Do Not Match between Streams and Rules',\ '---+ Source Database Name Does Not Match for Capture or \ Propagation Rules',\ '---+ Global Rule for Capture Specified but Condition not \ Modified',\ '---+ No Rule Set Defined for Capture',\ '---+ Apply Rules with no Source Database Specified',\ '---+ Schema Rules for non Existant Schema',\ '---+ Table Rules for non Existant Object',\ '---+ Overlapping Rules') var @HDR = ('',\ '|*Rule Owner*|*Rule Name*|',\ '|*Rule Owner*|*Rule Name*|',\ '|*Streams Type*|*Streams Name*|*Rule*|*Rule Condition*|',\ '|*Streams Type*|*Streams Name*|*Rule*|',\ '|*Streams Name*|*Rule*|',\ '|*Capture Name*|*Capture Type*|*Source Db*|',\ '|*Streams Name*|*Rule*|*Object*|',\ '|*Streams Type*|*Streams Name*|*Rule*|*Schema Name*|\ *Action Context Name*|*Action Context Value*|',\ '|*Streams Type*|*Streams Name*|*Rule*|*Object*|\ *Action Context Name*|*Action Context Value*|',\ '|*Streams Name*|*Streams Type*|*Rule Set Owner*|*Rule Set Name*|\ *Rule Owner*|*Rule Name*|*Streams Rule Type*|*Rule Owner*|\ *Rule Name*|*Streams Rule Type*|') set $sql {SELECT '|' || " rule_owner || ' |' || " rule_name || ' |' " FROM dba_streams_rules "MINUS "SELECT '|' || " rule_owner || ' |' || " rule_name || ' |' " FROM dba_rules; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " rule_owner || ' |' || " rule_name || ' |' " FROM dba_rules "MINUS "SELECT '|' || " rule_owner || ' |' || " rule_name || ' |' " FROM dba_streams_rules; "PROMPT ___Macro_separator(3)___ "SET long 65536 "SELECT '[[[' || CHR(10) || '|' || " s.streams_type || ' |' || " s.streams_name || ' |' || " r.rule_owner || '.' || r.rule_name || ' |' || " REPLACE(REPLACE(REPLACE(LTRIM(r.rule_condition), " '|','|'), " '<','<'), " '>','>') || '|' || " CHR(10) || ']]]' " FROM dba_streams_rules s,dba_rules r " WHERE r.rule_name = s.rule_name " AND r.rule_owner = s.rule_owner " AND dbms_lob.substr(s.rule_condition) != dbms_lob.substr(r.rule_condition); "PROMPT ___Macro_separator(4)___ "SELECT '|' || " streams_type || ' |' || " streams_name || ' |' || " r.rule_owner || '.' || r.rule_name || ' |' " FROM dba_streams_rules r " WHERE source_database IS NOT NULL " AND source_database != (SELECT global_name FROM global_name) " AND streams_type IN ('CAPTURE','PROPAGATION'); "PROMPT ___Macro_separator(5)___ "SELECT '|' || " streams_name || ' |' || " r.rule_owner || '.' || r.rule_name || ' |' " FROM dba_streams_rules s,dba_rules r " WHERE streams_type = 'CAPTURE' " AND rule_type = 'GLOBAL' " AND r.rule_name = s.rule_name " AND r.rule_owner = s.rule_owner " AND dbms_lob.substr(s.rule_condition) = dbms_lob.substr(r.rule_condition); "PROMPT ___Macro_separator(6)___ "SELECT '|' || " capture_name || ' |' || " capture_type || ' |' || " source_database || ' |' " FROM dba_capture " WHERE rule_set_name IS NULL " AND negative_rule_set_name IS NULL; "PROMPT ___Macro_separator(7)___ "SELECT '|' || " streams_name || ' |' || " s.rule_owner || '.' || s.rule_name || ' |' || " s.schema_name || '.' || s.object_name || ' |' " FROM dba_streams_rules s,dba_rules r " WHERE s.streams_type = 'APPLY' " AND s.source_database IS NULL " AND r.rule_name = s.rule_name " AND r.rule_owner = s.rule_owner " AND dbms_lob.substr(s.rule_condition) = dbms_lob.substr(r.rule_condition); "PROMPT ___Macro_separator(8)___ "SELECT '|' || " s.streams_type || ' |' || " s.streams_name || ' |' || " s.rule_owner || '.' || s.rule_name || ' |' || " s.schema_name || ' |' || " ac.nvn_name || ' |' || " ac.nvn_value.accessvarchar2() || ' |' " FROM dba_streams_rules s,dba_rules r,dba_users u, " TABLE(r.rule_action_context.actx_list) ac " WHERE s.schema_name IS NULL " AND u.username = s.schema_name " AND r.rule_owner = s.rule_owner " AND r.rule_name = s.rule_name " AND ac.nvn_value.accessvarchar2() IS NULL; "PROMPT ___Macro_separator(9)___ "SELECT '|' || " s.streams_type || ' |' || " streams_name || ' |' || " s.rule_owner || '.' || s.rule_name || ' |' || " s.schema_name || '.' || s.object_name || ' |' || " ac.nvn_name || ' |' || " ac.nvn_value.accessvarchar2() || ' |' " FROM dba_streams_rules s,dba_rules r,dba_objects o, " TABLE(r.rule_action_context.actx_list) ac " WHERE o.object_name = s.object_name " AND o.owner = s.schema_name " AND r.rule_owner = s.rule_owner " AND r.rule_name = s.rule_name " AND ac.nvn_value.accessvarchar2() IS NULL; "PROMPT ___Macro_separator(10)___ "SELECT '|' || " a.streams_name || ' |' || " a.streams_type || ' |' || " a.rule_set_owner || ' |' || " a.rule_set_name || ' |' || " a.rule_owner || ' |' || " a.rule_name || ' |' || " a.streams_rule_type || ' |' || " b.rule_owner || ' |' || " b.rule_name || ' |' || " b.streams_rule_type || ' |' " FROM dba_streams_rules a,dba_streams_rules b " WHERE a.rule_set_owner = b.rule_set_owner " AND a.rule_set_name = b.rule_set_name " AND a.streams_name = b.streams_name " AND a.streams_type = b.streams_type " AND a.rule_type = b.rule_type " AND (a.subsetting_operation IS NULL OR b.subsetting_operation IS NULL) " AND (a.rule_owner != b.rule_owner OR a.rule_name != b.rule_name) " AND ((a.streams_rule_type = 'GLOBAL' " AND b.streams_rule_type IN ('SCHEMA','TABLE') " AND a.schema_name = b.schema_name) " OR (a.streams_rule_type = 'SCHEMA' " AND b.streams_rule_type = 'TABLE' " AND a.schema_name = b.schema_name) " OR (a.streams_rule_type = 'TABLE' " AND b.streams_rule_type = 'TABLE' " AND a.schema_name = b.schema_name " AND a.object_name = b.object_name " AND a.rule_name < b.rule_name) " OR (a.streams_rule_type = 'SCHEMA' " AND b.streams_rule_type = 'SCHEMA' " AND a.schema_name = b.schema_name " AND a.rule_name < b.rule_name) " OR (a.streams_rule_type = 'GLOBAL' " AND b.streams_rule_type = 'GLOBAL' " AND a.rule_name < b.rule_name)) " ORDER BY a.rule_name; } } elsif match($ORACLE_VERSION,'^(102|11)') {var @TTL = ('',\ '---+ Missing Rules in dba_rules',\ '---+ Extra Rules in dba_rules',\ '---+ Rule Conditions Do not Match between Streams and Rules',\ '---+ Source Database Name Does not Match for Capture or \ Propagation Rules',\ '---+ Global Rule for Capture Specified but Condition not \ Modified',\ '---+ No Rule Set Defined for Capture',\ '---+ Apply Rules with no Source Database Specified',\ '---+ Schema Rules for non Existant Schema') var @HDR = ('',\ '|*Rule Owner*|*Rule Name*|',\ '|*Rule Owner*|*Rule Name*|',\ '|*Streams Type*|*Streams Name*|*Rule*|*Rule Condition*|',\ '|*Streams Type*|*Streams Name*|*Rule*|',\ '|*Streams Name*|*Rule*|',\ '|*Capture Name*|*Capture Type*|*Source Db*|',\ '|*Streams Name*|*Rule*|*Object*|',\ '|*Streams Type*|*Streams Name*|*Rule*|*Schema Name*|\ *Action Context Name*|*Action Context Value*|') set $sql {SELECT '|' || " rule_owner || ' |' || " rule_name || ' |' " FROM dba_streams_rules "MINUS "SELECT '|' || " rule_owner || ' |' || " rule_name || ' |' " FROM dba_rules; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " rule_owner || ' |' || " rule_name || ' |' " FROM dba_rules "MINUS "SELECT '|' || " rule_owner || ' |' || " rule_name || ' |' " FROM dba_streams_rules; "PROMPT ___Macro_separator(3)___ "SET long 65536 "SELECT '[[[' || CHR(10) || '|' || " s.streams_type || ' |' || " s.streams_name || ' |' || " r.rule_owner || '.' || r.rule_name || ' |' || " REPLACE(REPLACE(REPLACE(LTRIM(r.rule_condition), " '|','|'), " '<','<'), " '>','>') || '|' || " CHR(10) || ']]]' " FROM dba_streams_rules s,dba_rules r " WHERE r.rule_name = s.rule_name " AND r.rule_owner = s.rule_owner " AND dbms_lob.substr(s.rule_condition) != dbms_lob.substr(r.rule_condition); "PROMPT ___Macro_separator(4)___ "SELECT '|' || " streams_type || ' |' || " streams_name || ' |' || " r.rule_owner || '.' || r.rule_name || ' |' " FROM dba_streams_rules r " WHERE source_database IS NOT NULL " AND source_database != (SELECT global_name FROM global_name) " AND streams_type IN ('CAPTURE','PROPAGATION'); "PROMPT ___Macro_separator(5)___ "SELECT '|' || " streams_name || ' |' || " r.rule_owner || '.' || r.rule_name || ' |' " FROM dba_streams_rules s,dba_rules r " WHERE streams_type = 'CAPTURE' " AND rule_type = 'GLOBAL' " AND r.rule_name = s.rule_name " AND r.rule_owner = s.rule_owner " AND dbms_lob.substr(s.rule_condition) = dbms_lob.substr(r.rule_condition); "PROMPT ___Macro_separator(6)___ "SELECT '|' || " capture_name || ' |' || " capture_type || ' |' || " source_database || ' |' " FROM dba_capture " WHERE rule_set_name IS NULL " AND negative_rule_set_name IS NULL; "PROMPT ___Macro_separator(7)___ "SELECT '|' || " streams_name || ' |' || " s.rule_owner || '.' || s.rule_name || ' |' || " s.schema_name || '.' || s.object_name || ' |' " FROM dba_streams_rules s,dba_rules r " WHERE s.streams_type = 'APPLY' " AND s.source_database IS NULL " AND r.rule_name = s.rule_name " AND r.rule_owner = s.rule_owner " AND dbms_lob.substr(s.rule_condition) = dbms_lob.substr(r.rule_condition); "PROMPT ___Macro_separator(8)___ "SELECT '|' || " s.streams_type || ' |' || " s.streams_name || ' |' || " s.rule_owner || '.' || s.rule_name || ' |' || " s.schema_name || ' |' || " ac.nvn_name || ' |' || " ac.nvn_value.accessvarchar2() || ' |' " FROM dba_streams_rules s,dba_rules r,dba_users u, " TABLE(r.rule_action_context.actx_list) ac " WHERE s.schema_name IS NULL " AND u.username = s.schema_name " AND r.rule_owner = s.rule_owner " AND r.rule_name = s.rule_name " AND ac.nvn_value.accessvarchar2() IS NULL; } } if $sql {call separator(1) call writeSql($sql,2) call separator(0,'Rules Analysis') } =head2 objects - Object Information Provides constraints associated to tables that have been instantiated for apply. To get this information, the script creates a join between C and C. =cut debug ' Inside the STC module, gathering object information' report objects var $TTL = '---+!! Object Information' if match($ORACLE_VERSION,'92') {var @TTL = ('',\ '---+ Constraints on Tables Configured in Streams',\ '---+ Indexes on Tables',\ '---+ Unsupported Tables in Streams') var @HDR = ('',\ '|*Object*|*Constraint Name*|*Constraint Type*|*Status*|\ *Last Change*|*Search Condition*|*Ref Constraint*|',\ '|*Object*|*Table Type*|*Index Name*|*Index Type*|\ *Funcidx Status*|',\ '|*Owner*|*Table*|*Property*|*Triflag*|*Object Flags*|\ *Table Flags*|*Reason*|*Auto Filtered*|') set $sql {SET serverout on size 100000 "DECLARE " CURSOR c_cons IS " SELECT c.owner, " c.table_name, " c.constraint_name, " c.constraint_type, " status, " last_change, " search_condition, " r_owner, " r_constraint_name " FROM dba_constraints c,dba_apply_instantiated_objects p " WHERE c.owner = p.source_object_owner " AND c.table_name = p.source_object_name " AND c.constraint_type IN ('P','U','R','C') " AND constraint_name NOT LIKE 'SYS_IOT%' " ORDER BY c.owner,c.table_name; "BEGIN " FOR r_rec IN c_cons LOOP " dbms_output.put_line('|' || " r_rec.owner || '.' || r_rec.table_name || ' |' || " r_rec.constraint_name || ' |' || " r_rec.constraint_type || ' |' || " r_rec.status || ' |' || " r_rec.last_change || ' |' || " r_rec.search_condition || ' |' || " r_rec.r_owner || '.' || " r_rec.r_constraint_name || ' |'); " END LOOP; "END; "/ "PROMPT ___Macro_separator(2)___ "SELECT '|' || " table_owner || '.' || table_name || ' |' || " table_type || ' |' || " index_name || ' |' || " index_type || ' |' || " funcidx_status || ' |' " FROM dba_indexes " WHERE table_owner NOT IN ('SYS','SYSTEM','CTX','CTXSYS','XDB'); "PROMPT ___Macro_separator(3)___ "SELECT '|' || " u.name || ' |' || " o.name || ' | ' || " t.property || '| ' || " t.trigflag || '| ' || " o.flags || '| ' || " t.flags || '|' || " (CASE " WHEN ((BITAND(t.property,0x000003C0) != 0) OR " (BITAND(t.flags,0x30000000) != 0) OR " (BITAND(t.property,0x00040040) = 0x00040040) OR " (BITAND(t.property,0x00000840) = 0x00000840) OR " (BITAND(t.property,0x40) != 0 AND BITAND(t.flags,0x20000) != 0)) " THEN 'IOT' " WHEN BITAND(t.property,0x0001301F) != 0 " THEN 'column with user-defined type' " WHEN (EXISTS( " SELECT 1 " FROM sys.col$ c " WHERE t.obj# = c.obj# " AND ((BITAND(c.property,0x22) != 0) OR " (c.segcol# = 0) OR " (c.type# NOT IN (1,2,12,23,96,100,101,112,113,180,181, " 182,183,231)) OR " (c.type# = 112 AND c.charsetform = 2) OR " (c.type# = 112 AND c.charsetform = 1 " AND c.charsetid >= 800)))) " THEN 'unsupported column exists' " WHEN BITAND(t.property,0x00000001) != 0 " THEN 'object table' " WHEN BITAND(t.property,0x00020000) != 0 " THEN 'AQ queue table' " WHEN BITAND(t.property,0x00C00000) != 0 " THEN 'temporary table' " WHEN BITAND(t.property,0x08000000) != 0 " THEN 'sub object' " WHEN BITAND(t.property,0x80000000) != 0 " THEN 'external table' " WHEN BITAND(t.property,0x06000000) != 0 " THEN 'materialized view' " WHEN BITAND(t.property,0x00008000) != 0 " THEN 'FILE column exists' " WHEN (EXISTS( " SELECT 1 " FROM sys.mlog$ ml " WHERE ml.mowner = u.name AND ml.log = o.name)) " THEN 'materialized view log' " WHEN BITAND(t.flags,0x00040000) != 0 " THEN 'materalized view container table' " WHEN BITAND(t.trigflag,0x10000000) != 0 " THEN 'streams unsupported object' " WHEN BITAND(o.flags,0x10) != 0 " THEN 'domain index' " ELSE NULL END) || ' |' || " 'NO' || '|' " FROM sys.obj$ o,sys.user$ u,sys.tab$ t " WHERE t.obj# = o.obj# " AND o.owner# = u.user# " AND u.name NOT IN ('SYS','SYSTEM','CTXSYS') " AND BITAND(o.flags,0xA6) = 0 " AND ((BITAND(t.property,0x8EC3B3DF) != 0) OR " (BITAND(t.flags,0x30040000) != 0) OR " (BITAND(t.trigflag,0x10000000) != 0) OR " (BITAND(t.property,0x00040040) = 0x00040040) OR " (BITAND(t.property,0x00000840) = 0x00000840) OR " (BITAND(t.property,0x40) != 0 AND BITAND(t.flags,0x20000) != 0) OR " (EXISTS( " SELECT 1 FROM sys.col$ c " WHERE t.obj# = c.obj# " AND ((BITAND(c.property,0x22) != 0) OR " (c.segcol# = 0) OR " (c.type# NOT IN (1,2,12,23,96,112,113,180,181, " 182,183,231)) OR " (c.type# = 112 AND c.charsetform = 2) OR " (c.type# = 112 AND c.charsetform = 1 AND c.charsetid >= 800) " ))) OR " (EXISTS( " SELECT 1 " FROM sys.mlog$ ml " WHERE ml.mowner = u.name " AND ml.log = o.name) " ) " ); } var $sql = hex2dec($sql) } elsif match($ORACLE_VERSION,'101') {var @TTL = ('',\ '---+ Constraints on Tables Configured in Streams',\ '---+ Indexes on Tables',\ '---+ Unsupported Tables in Streams') var @HDR = ('',\ '|*Object*|*Constraint Name*|*Constraint Type*|*Status*|\ *Last Change*|*Search Condition*|*Ref Constraint*|',\ '|*Object*|*Table Type*|*Index Name*|*Index Type*|\ *Funcidx Status*|',\ '|*Owner*|*Table Name*|*Reason*|*Auto Filtered*|') set $sql {SET serverout on size 100000 "DECLARE " CURSOR c_cons IS " SELECT c.owner, " c.table_name, " c.constraint_name, " c.constraint_type, " status, " last_change, " search_condition, " r_owner, " r_constraint_name " FROM dba_constraints c,dba_apply_instantiated_objects p " WHERE c.owner = p.source_object_owner " AND c.table_name = p.source_object_name " AND c.constraint_type IN ('P','U','R','C') " AND constraint_name NOT LIKE 'SYS_IOT%' " ORDER BY c.owner,c.table_name; "BEGIN " FOR r_rec IN c_cons LOOP " dbms_output.put_line('|' || " r_rec.owner || '.' || r_rec.table_name || ' |' || " r_rec.constraint_name || ' |' || " r_rec.constraint_type || ' |' || " r_rec.status || ' |' || " r_rec.last_change || ' |' || " r_rec.search_condition || ' |' || " r_rec.r_owner || '.' || " r_rec.r_constraint_name || ' |'); " END LOOP; "END; "/ "PROMPT ___Macro_separator(2)___ "SELECT '|' || " table_owner || '.' || table_name || ' |' || " table_type || ' |' || " index_name || ' |' || " index_type || ' |' || " funcidx_status || ' |' " FROM dba_indexes " WHERE table_owner NOT IN ('SYS','SYSTEM','CTX','CTXSYS','XDB'); "PROMPT ___Macro_separator(3)___ "SELECT '|' || " owner || ' |' || " table_name || ' |' || " reason || ' |' || " auto_filtered || ' |' " FROM dba_streams_unsupported; } } elsif match($ORACLE_VERSION,'^(102|11)') {var @TTL = ('',\ '---+ Constraints on Tables Configured in Streams',\ '---+ Indexes on Tables',\ '---+ Unsupported Tables in Streams') var @HDR = ('',\ '|*Object*|*Constraint Name*|*Constraint Type*|*Status*|\ *Last Change*|*Search Condition*|*Ref Constraint*|',\ '|*Object*|*Table Type*|*Index Name*|*Index Type*|\ *Funcidx Status*|',\ '|*Owner*|*Table Name*|*Reason*|*Auto Filtered*|') set $sql {SET serverout on size 100000 "DECLARE " CURSOR c_cons IS " SELECT c.owner, " c.table_name, " c.constraint_name, " c.constraint_type, " status, " last_change, " search_condition, " r_owner, " r_constraint_name " FROM dba_constraints c,dba_apply_instantiated_objects p " WHERE c.owner = p.source_object_owner " AND c.table_name = p.source_object_name " AND c.constraint_type IN ('P','U','R','C') " AND constraint_name NOT LIKE 'SYS_IOT%' " ORDER BY c.owner,c.table_name; "BEGIN " FOR r_rec IN c_cons LOOP " dbms_output.put_line('|' || " r_rec.owner || '.' || r_rec.table_name || ' |' || " r_rec.constraint_name || ' |' || " r_rec.constraint_type || ' |' || " r_rec.status || ' |' || " r_rec.last_change || ' |' || " r_rec.search_condition || ' |' || " r_rec.r_owner || '.' || " r_rec.r_constraint_name || ' |'); " END LOOP; "END; "/ "PROMPT ___Macro_separator(2)___ "SELECT '|' || " table_owner || '.' || table_name || ' |' || " table_type || ' |' || " index_name || ' |' || " index_type || ' |' || " funcidx_status || ' |' " FROM dba_indexes i,dba_apply_instantiated_objects p " WHERE i.owner = p.source_object_owner " AND i.table_name = p.source_object_name " ORDER BY i.owner,i.table_name; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " owner || ' |' || " table_name || ' |' || " reason || ' |' || " auto_filtered || ' |' " FROM dba_streams_unsupported; } } if $sql {call separator(1) call writeSql($sql,2) call separator(0,'Object Information') } =head2 streams - Streams Related Information Provides a short summary of the capture, apply, and propagation processes created on the database. It shows the owner, name of the process, and rule set associated. It also shows all the rules defined on the system. =cut debug ' Inside the STC module, gathering streams related information' report streams var $TTL = '---+!! Streams Related Information' if match($ORACLE_VERSION,'92') {var @TTL = ('',\ '---+ Streams Dictionary Information',\ '---+ Apply Processes Defined on system',\ '---+ Propagations Defined on System',\ '---+ Stream Rules Defined on System',\ '---+ Logminer Database Map',\ '---+ Logminer Cache Objects',\ '---+ Intcol Verification',\ '---+ Jobs in Database',\ '---+ Agents',\ '---+ Agent Privileges',\ '---+ Current Long Running Transactions') var @HDR = ('',\ '|*Queue*|*Capture Name*| *Capture No*|*Rule Set*|',\ '|*Queue*|*Apply Name*| *Apply No*|*Rule Set*|',\ '|*Source Queue*|*Destination*|*Ruleset*|',\ '|*Streams name*|*Type*| *Number*|',\ '|*Global Name*| *Logmnr Uid*| *Flags*|',\ '| *Logmnr Uid*| *Count*|',\ '| *Logmnr Uid*| *Obj#*| *Objv#*| *Intcol#*|',\ '| *Job*|*Log User*|*Priv User*|*Schema User*|*Last Date*|\ *Last Sec*|*This Date*|*This Sec*|*Next Date*|*NextSec*| \ *Total Time*|*Broken*|*Interval*| *Failures*|*What*|*Nls Env*|\ *Misc Env*| *Instance*|',\ '|*Agent Name*|*Http Enabled*|*Smtp Enabled*|',\ '|*Agent Name*|*Db Username*|*Http Enabled*|*Smtp Enabled*|',\ '| *Sid*|*Xid*| *Run Length*|*Terminal*|*Program*|') set $sql {SELECT '|' || " queue_owner || '.' || queue_name || ' |' || " capture_name || ' | ' || " capture# || '|' || " ruleset_owner || '.' || ruleset_name || ' |' " FROM sys.streams$_capture_process; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " queue_owner || '.' || queue_name || ' |' || " apply_name || ' | ' || " apply# || '|' || " ruleset_owner || '.' || ruleset_name || ' |' " FROM sys.streams$_apply_process; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " source_queue_schema || '.' || source_queue || ' |' || " destination_queue_schema || '.' || destination_queue " || '@' || destination_dblink || ' |' || " ruleset_schema || '.' || ruleset || ' |' " FROM sys.streams$_propagation_process; "PROMPT ___Macro_separator(4)___ "SELECT '|' || " streams_name || ' |' || " streams_type || ' | ' || " COUNT(*) || '|' " FROM sys.streams$_rules " GROUP BY streams_name,streams_type; "PROMPT ___Macro_separator(5)___ "SELECT '|' || " global_name || ' | ' || " logmnr_uid || '| ' || " flags || '|' " FROM system.logmnrc_dbname_uid_map; "PROMPT ___Macro_separator(6)___ "SELECT '| ' || " logmnr_uid || '| ' || " COUNT(*) || '|' " FROM system.logmnrc_gtlo " GROUP BY logmnr_uid; "PROMPT ___Macro_separator(7)___ "SELECT '| ' || " logmnr_uid || '| ' || " obj# || '| ' || " objv# || '| ' || " intcol# || '|' " FROM system.logmnrc_gtcs " GROUP BY logmnr_uid,obj#,objv#,intcol# " HAVING COUNT(1) > 1 " ORDER BY logmnr_uid,obj#,objv#,intcol#; "PROMPT ___Macro_separator(8)___ "SELECT '| ' || " job || '|' || " log_user || ' |' || " priv_user || ' |' || " schema_user || ' |' || " TO_CHAR(last_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " last_sec || ' |' || " TO_CHAR(this_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " this_sec || ' |' || " TO_CHAR(next_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " next_sec || ' | ' || " ROUND(total_time,1) || '|' || " broken || ' |' || " interval || ' | ' || " failures || '|' || " what || ' |' || " nls_env || ' |' || " misc_env || ' | ' || " instance || '|' " FROM dba_jobs; "PROMPT ___Macro_separator(9)___ "SELECT '|' || " agent_name || ' |' || " http_enabled || ' |' || " smtp_enabled || ' |' " FROM dba_aq_agents; "PROMPT ___Macro_separator(10)___ "SELECT '|' || " agent_name || ' |' || " db_username || ' |' || " http_enabled || ' |' || " smtp_enabled || ' |' " FROM dba_aq_agent_privs; "PROMPT ___Macro_separator(11)___ "SELECT '|' || " sid || ',' || " serial# || ' |' || " xidusn || '.' || xidslot || '.' || xidsqn || ' | ' || " (SYSDATE - TO_DATE(start_time,'DD-Mon-YYYY HH24:MI:SS')) * 1440 " || '|' || " terminal || ' |' || " program || ' |' " FROM v$transaction t,v$session s " WHERE t.addr = s.taddr " AND (SYSDATE - TO_DATE(start_time,'DD-Mon-YYYY HH24:MI:SS') ) * 1440 > 20; } } elsif match($ORACLE_VERSION,'101') {var @TTL = ('',\ '---+ Streams Dictionary Information',\ '---+ Apply Processes Defined on System',\ '---+ Propagations Defined on System',\ '---+ Streams Rules Defined on System',\ '---+ Logminer Database Map 1',\ '---+ Logminer Database Map 2',\ '---+ Logminer Cache Objects',\ '---+ Intcol Verification',\ '---+ Jobs in Database',\ '---+ Agents',\ '---+ Agent Privileges',\ '---+ Current Long Running Transactions') var @HDR = ('',\ '|*Queue*|*Capture Name*| *Capture No*|*Rule Set*|*Rule Set2*|',\ '|*Queue*|*Apply Name*| *Apply No*|*Rule Set*|*Rule Set2*|',\ '|*Source Queue*|*Destination*|*Rule Set*|*Rule Set2*|',\ '|*Streams Name*|*Streams Type*| *Count*|',\ '|*Global Name*| *Logmnr Uid*| *Flags*|',\ '| *Logmnr Uid*| *Session#*|',\ '| *Logmnr Uid*| *Count*|',\ '| *Logmnr Uid*| *Obj#*| *Objv#*| *Intcol#*|',\ '| *Job*|*Log User*|*Priv User*|*Schema User*|*Last Date*|\ *Last Sec*|*This Date*|*This Sec*|*Next Date*|*NextSec*| \ *Total Time*|*Broken*|*Interval*| *Failures*|*What*|*Nls Env*|\ *Misc Env*| *Instance*|',\ '|*Agent Name*|*Http Enabled*|*Smtp Enabled*|',\ '|*Agent Name*|*Db Username*|*Http Enabled*|*Smtp Enabled*|',\ '| *Inst Id*| *Sid*|*Xid*| *Run Length*|*Terminal*|*Program*|') set $sql {SELECT '|' || " queue_owner || '.' || queue_name || ' |' || " capture_name || ' | ' || " capture# || '|' || " ruleset_owner || '.' || ruleset_name || ' |' || " negative_ruleset_owner || '.' || negative_ruleset_name || ' |' " FROM sys.streams$_capture_process; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " queue_owner || '.' || queue_name || ' |' || " apply_name || ' | ' || " apply# || '|' || " ruleset_owner || '.' || ruleset_name || ' |' || " negative_ruleset_owner || '.' || negative_ruleset_name || ' |' " FROM sys.streams$_apply_process; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " source_queue_schema || '.' || source_queue || ' |' || " destination_queue_schema || '.' || destination_queue " || '@' || destination_dblink || ' |' || " ruleset_schema || '.' || ruleset || ' |' || " negative_ruleset_schema || '.' || negative_ruleset || ' |' " FROM sys.streams$_propagation_process; "PROMPT ___Macro_separator(4)___ "SELECT '|' || " streams_name || ' |' || " streams_type || ' | ' || " COUNT(*) || '|' " FROM sys.streams$_rules " GROUP BY streams_name,streams_type; "PROMPT ___Macro_separator(5)___ "SELECT '|' || " global_name || ' | ' || " logmnr_uid || '| ' || " flags || '|' " FROM system.logmnrc_dbname_uid_map; "PROMPT ___Macro_separator(6)___ "SELECT '| ' || " logmnr_uid || '| ' || " session# || '|' " FROM system.logmnr_uid$; "PROMPT ___Macro_separator(7)___ "SELECT '| ' || " logmnr_uid || '| ' || " COUNT(*) || '|' " FROM system.logmnrc_gtlo " GROUP BY logmnr_uid; "PROMPT ___Macro_separator(8)___ "SELECT '| ' || " logmnr_uid || '| ' || " obj# || '| ' || " objv# || '| ' || " intcol# || '|' " FROM system.logmnrc_gtcs " GROUP BY logmnr_uid,obj#,objv#,intcol# " HAVING COUNT(1) > 1 " ORDER BY logmnr_uid,obj#,objv#,intcol#; "PROMPT ___Macro_separator(9)___ "SELECT '| ' || " job || '|' || " log_user || ' |' || " priv_user || ' |' || " schema_user || ' |' || " TO_CHAR(last_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " last_sec || ' |' || " TO_CHAR(this_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " this_sec || ' |' || " TO_CHAR(next_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " next_sec || ' | ' || " ROUND(total_time,1) || '|' || " broken || ' |' || " interval || ' | ' || " failures || '|' || " what || ' |' || " nls_env || ' |' || " misc_env || ' | ' || " instance || '|' " FROM dba_jobs; "PROMPT ___Macro_separator(10)___ "SELECT '|' || " agent_name || ' |' || " http_enabled || ' |' || " smtp_enabled || ' |' " FROM dba_aq_agents; "PROMPT ___Macro_separator(11)___ "SELECT '|' || " agent_name || ' |' || " db_username || ' |' || " http_enabled || ' |' || " smtp_enabled || ' |' " FROM dba_aq_agent_privs; "PROMPT ___Macro_separator(12)___ "SELECT '| ' || " t.inst_id || '|' || " sid || ',' || serial# || ' |' || " xidusn || '.' || xidslot || '.' || xidsqn || ' | ' || " (SYSDATE - start_date) * 1440 || '|' || " terminal || ' |' || " program || ' |' " FROM gv$transaction t,gv$session s " WHERE t.addr = s.taddr " AND (SYSDATE - start_date) * 1440 > 20; } } elsif match($ORACLE_VERSION,'^(102|11)') {var @TTL = ('',\ '---+ Streams Dictionary Information',\ '---+ Apply Processes Defined on System',\ '---+ Propagations Defined on System',\ '---+ Streams Rules Defined on System',\ '---+ Logminer Database Map 1',\ '---+ Logminer Database Map 2',\ '---+ Logminer Cache Objects',\ '---+ Intcol Verification',\ '---+ Jobs in Database',\ '---+ Agents',\ '---+ Agent Privileges',\ '---+ Current Long Running Transactions') var @HDR = ('',\ '|*Queue*|*Capture Name*| *Capture No*|*Rule Set*|*Rule Set2*|',\ '|*Queue*|*Apply Name*| *Apply No*|*Rule Set*|*Rule Set2*|',\ '|*Source Queue*|*Destination*|*Rule Set*|*Rule Set2*|',\ '|*Streams Name*|*Streams Type*|*Count*|',\ '|*Global Name*| *Logmnr Uid*| *Flags*|',\ '| *Logmnr Uid*| *Session#*|',\ '| *Logmnr Uid*| *Count*|',\ '| *Logmnr Uid*| *Obj#*| *Objv#*| *Intcol#*|',\ '| *Job*|*Log User*|*Priv User*|*Schema User*|*Last Date*|\ *Last Sec*|*This Date*|*This Sec*|*Next Date*|*NextSec*| \ *Total Time*|*Broken*|*Interval*| *Failures*|*What*|*Nls Env*|\ *Misc Env*| *Instance*|',\ '|*Agent Name*|*Http Enabled*|*Smtp Enabled*|',\ '|*Agent Name*|*Db Username*|*Http Enabled*|*Smtp Enabled*|',\ '| *Inst Id*| *Sid*|*Xid*| *Run Length*|*Terminal*|*Program*|') set $sql {SELECT '|' || " queue_owner || '.' || queue_name || ' |' || " capture_name || ' | ' || " capture# || '|' || " ruleset_owner || '.' || ruleset_name || ' |' || " negative_ruleset_owner || '.' || negative_ruleset_name || ' |' " FROM sys.streams$_capture_process; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " queue_owner || '.' || queue_name || ' |' || " apply_name || ' | ' || " apply# || '|' || " ruleset_owner || '.' || ruleset_name || ' |' || " negative_ruleset_owner || '.' || negative_ruleset_name || ' |' " FROM sys.streams$_apply_process; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " source_queue_schema || '.' || source_queue || ' |' || " destination_queue_schema || '.' || destination_queue " || '@' || destination_dblink || ' |' || " ruleset_schema || '.' || ruleset || ' |' || " negative_ruleset_schema || '.' || negative_ruleset || ' |' " FROM sys.streams$_propagation_process; "PROMPT ___Macro_separator(4)___ "SELECT '|' || " streams_name || ' |' || " streams_type || ' | ' || " COUNT(*) || '|' " FROM sys.streams$_rules " GROUP BY streams_name,streams_type; "PROMPT ___Macro_separator(5)___ "SELECT '|' || " global_name || ' | ' || " logmnr_uid || '| ' || " flags || '|' " FROM system.logmnrc_dbname_uid_map; "PROMPT ___Macro_separator(6)___ "SELECT '| ' || " logmnr_uid || '| ' || " session# || '|' " FROM system.logmnr_uid$; "PROMPT ___Macro_separator(7)___ "SELECT '| ' || " logmnr_uid || '| ' || " COUNT(*) || '|' " FROM system.logmnrc_gtlo " GROUP BY logmnr_uid; "PROMPT ___Macro_separator(8)___ "SELECT '| ' || " logmnr_uid || '| ' || " obj# || '| ' || " objv# || '| ' || " intcol# || '|' " FROM system.logmnrc_gtcs " GROUP BY logmnr_uid,obj#,objv#,intcol# " HAVING COUNT(1) > 1 " ORDER BY logmnr_uid,obj#,objv#,intcol#; "PROMPT ___Macro_separator(9)___ "SELECT '| ' || " job || '|' || " log_user || ' |' || " priv_user || ' |' || " schema_user || ' |' || " TO_CHAR(last_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " last_sec || ' |' || " TO_CHAR(this_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " this_sec || ' |' || " TO_CHAR(next_date,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " next_sec || ' | ' || " ROUND(total_time,1) || '|' || " broken || ' |' || " interval || ' | ' || " failures || '|' || " what || ' |' || " nls_env || ' |' || " misc_env || ' | ' || " instance || '|' " FROM dba_jobs; "PROMPT ___Macro_separator(10)___ "SELECT '|' || " agent_name || ' |' || " http_enabled || ' |' || " smtp_enabled || ' |' " FROM dba_aq_agents; "PROMPT ___Macro_separator(11)___ "SELECT '|' || " agent_name || ' |' || " db_username || ' |' || " http_enabled || ' |' || " smtp_enabled || ' |' " FROM dba_aq_agent_privs; "PROMPT ___Macro_separator(12)___ "SELECT '| ' || " t.inst_id || '|' || " sid || ',' || serial# || ' |' || " xidusn || '.' || xidslot || '.' || xidsqn || ' | ' || " (SYSDATE - start_date) * 1440 || '|' || " terminal || ' |' || " program || ' |' " FROM gv$transaction t,gv$session s " WHERE t.addr = s.taddr " AND (SYSDATE - start_date) * 1440 > 20; } } if $sql {call separator(1) call writeSql($sql,2) call separator(0,'Streams Related Information') } =head1 SEE ALSO See also L, L. =begin credits =over 10 =item RDA 4.4: Malcolm Mckellar, James Mullaney. =back =end credits =head1 COPYRIGHT NOTICE Copyright (c) 2002-2008, Oracle. All rights reserved. =head1 TRADEMARK NOTICE Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. =cut