| Oracle® Streams Concepts and Administration 10g Release 1 (10.1) Part Number B10727-01 |
|
|
View PDF |
| Oracle® Streams Concepts and Administration 10g Release 1 (10.1) Part Number B10727-01 |
|
|
View PDF |
This chapter provides information about the static data dictionary views and dynamic performance views related to Streams. You can use these views to monitor your Streams environment. This chapter also illustrates example queries that you may want to use to monitor your Streams environment.
This chapter contains these topics:
|
Note: The Streams tool in the Oracle Enterprise Manager Console is also an excellent way to monitor a Streams environment. See the online help for the Streams tool for more information. |
See Also:
|
The following table lists the Streams static data dictionary views.
| ALL_ Views | DBA_ Views | USER_ Views |
|---|---|---|
|
N/A | ||
|
N/A | ||
|
N/A | ||
|
N/A | ||
|
N/A | ||
|
N/A | ||
|
N/A |
N/A | |
|
N/A |
N/A | |
|
N/A |
N/A | |
|
N/A | ||
|
N/A | ||
|
N/A | ||
|
N/A | ||
|
N/A | ||
|
N/A | ||
|
N/A | ||
|
N/A | ||
|
N/A | ||
|
N/A | ||
|
N/A | ||
|
N/A |
N/A | |
|
N/A |
N/A | |
|
N/A | ||
|
N/A | ||
|
N/A | ||
|
N/A | ||
|
N/A | ||
|
N/A | ||
|
N/A | ||
|
N/A |
The following list includes the Streams dynamic performance views
V$BUFFERED_QUEUESV$BUFFERED_PUBLISHERSV$BUFFERED_SUBSCRIBERSV$RULEV$RULE_SETV$RULE_SET_AGGREGATE_STATSV$STREAMS_APPLY_COORDINATORV$STREAMS_APPLY_READERV$STREAMS_APPLY_SERVERV$STREAMS_CAPTURE
The following sections contain queries that you can run to list Streams administrators and other users who allow access to remote Streams administrators:
| See Also:
PL/SQL Packages and Types Reference for more information about configuring Streams administrators and other Streams users using the |
You optionally can grant privileges to a local Streams administrator by running the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_STREAMS_AUTH package. The DBA_STREAMS_ADMINISTRATOR data dictionary view only contains the local Streams administrators created with the grant_privileges parameter set to true when the GRANT_ADMIN_PRIVILEGE procedure was run for the user. If you created a Streams administrator using generated scripts and set the grant_privileges parameter to false when the GRANT_ADMIN_PRIVILEGE procedure was run for the user, then the DBA_STREAMS_ADMINISTRATOR data dictionary view does not list the user as a Streams administrator.
To list the local Streams administrators created with the grant_privileges parameter set to true when running the GRANT_ADMIN_PRIVILEGE procedure, run the following query:
COLUMN USERNAME HEADING 'Local Streams Administrator' FORMAT A30 SELECT USERNAME FROM DBA_STREAMS_ADMINISTRATOR WHERE LOCAL_PRIVILEGES = 'YES';
Your output looks similar to the following:
Local Streams Administrator ------------------------------ STRMADMIN
The GRANT_ADMIN_PRIVILEGE may not have been run on a user who is a Streams administrator. Such administrators are not returned by the query in this section. Also, you may change the privileges for the users listed after the GRANT_ADMIN_PRIVILEGE procedure has been run for them. The DBA_STREAMS_ADMINISTRATOR view does not track these changes unless they are performed by the DBMS_STREAMS_AUTH package. For example, you may revoke the privileges granted by the GRANT_ADMIN_PRIVILEGE procedure for a particular user using the REVOKE SQL statement, but this user would be listed when you query the DBA_STREAMS_ADMINISTRATOR view.
Oracle Corporation recommends using the REVOKE_ADMIN_PRIVILEGE procedure to revoke privileges from a user. When you revoke privileges from a user using this procedure, the user is removed from the DBA_STREAMS_ADMINISTRATOR view.
You can configure a user to allow access to remote Streams administrators by running the GRANT_REMOTE_ADMIN_ACCESS procedure in the DBMS_STREAMS_AUTH package. Such a user allows the remote Streams administrator to perform administrative actions in the local database using a database link.
Typically, you configure such a user at a local source database if a downstream capture process captures changes originating at the local source database. The Streams administrator at a downstream capture database administers the source database using this connection.
To list the users who allow to remote Streams administrators, run the following query:
COLUMN USERNAME HEADING 'Users Who Allow Remote Access' FORMAT A30 SELECT USERNAME FROM DBA_STREAMS_ADMINISTRATOR WHERE ACCESS_FROM_REMOTE = 'YES';
Your output looks similar to the following:
Users Who Allow Remote Access ------------------------------ STRMREMOTE
The following sections contain queries that you can run to display information about a capture process:
You can display the following general information about each capture process in a database by running the query in this section:
ENABLED, DISABLED, or ABORTEDTo display this general information about each capture process in a database, run the following query:
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A15 COLUMN QUEUE_NAME HEADING 'Capture|Process|Queue' FORMAT A15 COLUMN RULE_SET_NAME HEADING 'Positive|Rule Set' FORMAT A15 COLUMN NEGATIVE_RULE_SET_NAME HEADING 'Negative|Rule Set' FORMAT A15 COLUMN STATUS HEADING 'Capture|Process|Status' FORMAT A15 SELECT CAPTURE_NAME, QUEUE_NAME, RULE_SET_NAME, NEGATIVE_RULE_SET_NAME, STATUS FROM DBA_CAPTURE;
Your output looks similar to the following:
Capture Capture Capture Process Process Positive Negative Process Name Queue Rule Set Rule Set Status --------------- --------------- --------------- --------------- --------------- STRM01_CAPTURE STRM01_QUEUE RULESET$_25 RULESET$_36 ENABLED
If the status of a capture process is ABORTED, then you can query the ERROR_NUMBER and ERROR_MESSAGE columns in the DBA_CAPTURE data dictionary view to determine the error.
| See Also:
"Is the Capture Process Enabled?" for an example query that shows the error number and error message if a capture process is aborted |
The query in this section displays the following general information about each capture process in a database:
cnnn)INITIALIZING, WAITING FOR DICTONARY REDO, DICTIONARY INITIALIZATION, MINING, LOADING, CAPTURING CHANGES, WAITING FOR REDO, EVALUATING RULE, CREATING LCR, ENQUEUING MESSAGE, PAUSED FOR FLOW CONTROL, or SHUTTING DOWNTo display this information for each capture process in a database, run the following query:
COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A10 COLUMN PROCESS_NAME HEADING 'Capture|Process|Number' FORMAT A7 COLUMN SID HEADING 'Session|ID' FORMAT 9999 COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 9999 COLUMN STATE HEADING 'State' FORMAT A27 COLUMN TOTAL_MESSAGES_CAPTURED HEADING 'Redo|Entries|Scanned' FORMAT 9999999 COLUMN TOTAL_MESSAGES_ENQUEUED HEADING 'Total|LCRs|Enqueued' FORMAT 999999 SELECT c.CAPTURE_NAME, SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME, c.SID, c.SERIAL#, c.STATE, c.TOTAL_MESSAGES_CAPTURED, c.TOTAL_MESSAGES_ENQUEUED FROM V$STREAMS_CAPTURE c, V$SESSION s WHERE c.SID = s.SID AND c.SERIAL# = s.SERIAL#;
Your output looks similar to the following:
Capture Session Redo Total Capture Process Session Serial Entries LCRs Name Number ID Number State Scanned Enqueued ---------- ------- ------- ------- --------------------------- -------- -------- CAPTURE C001 15 9 CAPTURING CHANGES 14276 51
The number of redo entries scanned may be higher than the number of DML and DDL redo entries captured by a capture process. Only DML and DDL redo entries that are captured by a capture process are enqueued into the capture process queue. Also, the total LCRs enqueued includes LCRs that contain transaction control statements. These row LCRs contain directives such as COMMIT and ROLLBACK. Therefore, the total LCRs enqueued is a number higher than the number of row changes and DDL changes enqueued by a capture process.
See Also:
|
A downstream capture is a capture process runs on a database other than the source database. You can display the following general information about each downstream capture process in a database by running the query in this section:
ENABLED, DISABLED, or ABORTEDTo display this information about each downstream capture process in a database, run the following query:
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A15 COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A15 COLUMN QUEUE_NAME HEADING 'Capture|Process|Queue' FORMAT A15 COLUMN STATUS HEADING 'Capture|Process|Status' FORMAT A15 COLUMN USE_DATABASE_LINK HEADING 'Uses|Database|Link?' FORMAT A8 SELECT CAPTURE_NAME, SOURCE_DATABASE, QUEUE_NAME, STATUS, USE_DATABASE_LINK FROM DBA_CAPTURE WHERE CAPTURE_TYPE = 'DOWNSTREAM';
Your output looks similar to the following:
Capture Capture Capture Uses Process Source Process Process Database Name Database Queue Status Link? --------------- --------------- --------------- --------------- -------- STRM03_CAPTURE DBS1.NET STRM03_QUEUE ENABLED YES
In this case, the source database for the capture process is dbs1.net, but the local database running the capture process is not dbs1.net. Also, the capture process returned by this query uses a database link to the source database to perform administrative actions. The database link name is the same as the global name of the source database, which is dbs1.net in this case.
If the status of a capture process is ABORTED, then you can query the ERROR_NUMBER and ERROR_MESSAGE columns in the DBA_CAPTURE data dictionary view to determine the error.
See Also:
|
You can display information about the archived redo log files that are registered for each capture process in a database by running the query in this section. This query displays information about these files for both local and downstream capture processes.
The query displays the following information for each registered archived redo log file:
To display this information about each registered archive redo log file in a database, run the following query:
COLUMN CONSUMER_NAME HEADING 'Capture|Process|Name' FORMAT A15 COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A10 COLUMN SEQUENCE# HEADING 'Sequence|Number' FORMAT 99999 COLUMN NAME HEADING 'Archived Redo Log|File Name' FORMAT A20 COLUMN DICTIONARY_BEGIN HEADING 'Dictionary|Build|Begin' FORMAT A10 COLUMN DICTIONARY_END HEADING 'Dictionary|Build|End' FORMAT A10 SELECT r.CONSUMER_NAME, r.SOURCE_DATABASE, r.SEQUENCE#, r.NAME, r.DICTIONARY_BEGIN, r.DICTIONARY_END FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c WHERE r.CONSUMER_NAME = c.CAPTURE_NAME;
Your output looks similar to the following:
Capture Dictionary Dictionary Process Source Sequence Archived Redo Log Build Build Name Database Number File Name Begin End --------------- ---------- -------- -------------------- ---------- ---------- STRM02_CAPTURE DBS2.NET 15 /orc/dbs/log/arch2_1 NO NO _15_478347508.arc STRM02_CAPTURE DBS2.NET 16 /orc/dbs/log/arch2_1 NO NO _16_478347508.arc STRM03_CAPTURE DBS1.NET 45 /remote_logs/arch1_1 YES YES _45_478347335.arc STRM03_CAPTURE DBS1.NET 46 /remote_logs/arch1_1 NO NO _46_478347335.arc STRM03_CAPTURE DBS1.NET 47 /remote_logs/arch1_1 NO NO _47_478347335.arc
Assume that this query was run at the dbs2.net database, and that strm02_capture is a local capture process, while strm03_capture is a downstream capture process. The source database for the strm03_capture downstream capture process is dbs1.net. This query shows the that there are two registered archived redo log files for strm02_capture and three registered archived redo log files for strm02_capture, and this query shows the name and location of each of these files in the local site's file system.
A capture process needs the redo log file that includes the required checkpoint SCN, and all subsequent redo log files. You can query the REQUIRED_CHECKPOINT_SCN column in the DBA_CAPTURE data dictionary view to determine the required checkpoint SCN for a capture process. Redo log files prior to the redo log file that contains the required checkpoint SCN are no longer needed by the capture process. These redo log files may be stored offline if they are no longer needed for any other purpose. If you reset the start SCN for a capture process to a lower value in the future, then these redo log files may be needed.
See Also:
|
The DBA_LOGMNR_PURGED_LOG data dictionary view lists the redo log files that will never be needed by any capture process at the local database. These redo log files may be removed without affecting any existing capture process at the local database.
To display the redo log files that are no longer needed by any capture process, run the following query:
SELECT * FROM DBA_LOGMNR_PURGED_LOG;
Your output looks similar to the following:
FILE_NAME -------------------------------------------------------------------- /private1/ARCHIVE_LOGS/1_6_262829418.dbf
You can display information about the SCN values for archived redo log files that are registered for each capture process in a database by running the query in this section. This query displays information the SCN values for these files for both local and downstream capture processes.
The query displays the following information for each registered archived redo log file:
To display this information about each registered archive redo log file in a database, run the following query:
COLUMN CONSUMER_NAME HEADING 'Capture|Process|Name' FORMAT A15 COLUMN NAME HEADING 'Archived Redo Log|File Name' FORMAT A35 COLUMN FIRST_SCN HEADING 'First SCN' FORMAT 99999999999 COLUMN NEXT_SCN HEADING 'Next SCN' FORMAT 99999999999 SELECT r.CONSUMER_NAME, r.NAME, r.FIRST_SCN, r.NEXT_SCN FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c WHERE r.CONSUMER_NAME = c.CAPTURE_NAME;
Your output looks similar to the following:
Capture Process Archived Redo Log Name File Name First SCN Next SCN --------------- -------------------- ------------ ------------ CAPTURE /private1/ARCHIVE_LO 202088 202112 GS/1_3_502628294.dbf CAPTURE /private1/ARCHIVE_LO 202112 203389 GS/1_4_502628294.dbf CAPTURE /private1/ARCHIVE_LO 203389 230382 GS/1_5_502628294.dbf CAPTURE /private1/ARCHIVE_LO 230382 235590 GS/1_6_502628294.dbf CAPTURE /private1/ARCHIVE_LO 235590 256147 GS/1_7_502628294.dbf
For a local capture process, the last archived redo entry available is the last entry from the online redo log flushed to an archived log file. For a downstream capture process, the last archived redo entry available is the redo entry with the most recent SCN in the last archived log file added to the LogMiner session used by the capture process.
You can display the following information about the last redo entry that was made available to each capture process by running the query in this section:
The information displayed by this query is valid only for an enabled capture process.
Run the following query to display this information for each capture process:
COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A20 COLUMN LOGMINER_ID HEADING 'LogMiner ID' FORMAT 9999 COLUMN AVAILABLE_MESSAGE_NUMBER HEADING 'Last Redo SCN' FORMAT 9999999999 COLUMN AVAILABLE_MESSAGE_CREATE_TIME HEADING 'Time of|Last Redo SCN' SELECT CAPTURE_NAME, LOGMINER_ID, AVAILABLE_MESSAGE_NUMBER, TO_CHAR(AVAILABLE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') AVAILABLE_MESSAGE_CREATE_TIME FROM V$STREAMS_CAPTURE;
Your output looks similar to the following:
Capture Time of Name LogMiner ID Last Redo SCN Last Redo SCN -------------------- ----------- ------------- ----------------- STREAMS_CAPTURE 1 322953 11:33:20 10/16/03
The following query displays the current setting for each capture process parameter for each capture process in a database:
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A15 COLUMN PARAMETER HEADING 'Parameter' FORMAT A20 COLUMN VALUE HEADING 'Value' FORMAT A20 COLUMN SET_BY_USER HEADING 'Set by User?' FORMAT A20 SELECT CAPTURE_NAME, PARAMETER, VALUE, SET_BY_USER FROM DBA_CAPTURE_PARAMETERS;
Your output looks similar to the following:
Capture Process Name Parameter Value Set by User? --------------- -------------------- -------------------- -------------------- CAPTURE DISABLE_ON_LIMIT N NO CAPTURE MAXIMUM_SCN INFINITE NO CAPTURE MESSAGE_LIMIT INFINITE NO CAPTURE PARALLELISM 3 YES CAPTURE STARTUP_SECONDS 0 NO CAPTURE TIME_LIMIT INFINITE NO CAPTURE TRACE_LEVEL 0 NO CAPTURE WRITE_ALERT_LOG Y NO
You can use the INCLUDE_EXTRA_ATTRIBUTE procedure in the DBMS_CAPTURE_ADM package to instruct a capture process to capture one or more extra attributes from the redo log. The following query displays the extra attributes included in the LCRs captured by each capture process in the local database:
COLUMN CAPTURE_NAME HEADING 'Capture Process' FORMAT A20 COLUMN ATTRIBUTE_NAME HEADING 'Attribute Name' FORMAT A15 COLUMN INCLUDE HEADING 'Include Attribute in LCRs?' FORMAT A30 SELECT CAPTURE_NAME, ATTRIBUTE_NAME, INCLUDE FROM DBA_CAPTURE_EXTRA_ATTRIBUTES ORDER BY CAPTURE_NAME;
Your output looks similar to the following:
Capture Process Attribute Name Include Attribute in LCRs? -------------------- --------------- ------------------------------ STREAMS_CAPTURE ROW_ID NO STREAMS_CAPTURE SERIAL# NO STREAMS_CAPTURE SESSION# NO STREAMS_CAPTURE THREAD# NO STREAMS_CAPTURE TX_NAME YES STREAMS_CAPTURE USERNAME NO
Based on this output, the capture process named streams_capture includes the transaction name (tx_name) in the LCRs that it captures, but this capture process does not include any other extra attributes in the LCRs that it captures.
See Also:
|
The applied system change number (SCN) for a capture process is the SCN of the most recent event dequeued by the relevant apply processes. All changes below this applied SCN have been dequeued by all apply processes that apply changes captured by the capture process.
To display the applied SCN for all of the capture processes in a database, run the following query:
COLUMN CAPTURE_NAME HEADING 'Capture Process Name' FORMAT A30 COLUMN APPLIED_SCN HEADING 'Applied SCN' FORMAT 99999999999 SELECT CAPTURE_NAME, APPLIED_SCN FROM DBA_CAPTURE;
Your output looks similar to the following:
Capture Process Name Applied SCN ------------------------------ ----------- CAPTURE_EMP 177154
You can find the following information about each capture process by running the query in this section:
The information displayed by this query is valid only for an enabled capture process.
Run the following query to determine the redo scanning latency for each capture process:
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A10 COLUMN LATENCY_SECONDS HEADING 'Latency|in|Seconds' FORMAT 999999 COLUMN LAST_STATUS HEADING 'Seconds Since|Last Status' FORMAT 999999 COLUMN CAPTURE_TIME HEADING 'Current|Process|Time' COLUMN CREATE_TIME HEADING 'Event|Creation Time' FORMAT 999999 SELECT CAPTURE_NAME, ((SYSDATE - CAPTURE_MESSAGE_CREATE_TIME)*86400) LATENCY_SECONDS, ((SYSDATE - CAPTURE_TIME)*86400) LAST_STATUS, TO_CHAR(CAPTURE_TIME, 'HH24:MI:SS MM/DD/YY') CAPTURE_TIME, TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIME FROM V$STREAMS_CAPTURE;
Your output looks similar to the following:
Capture Latency Current Process in Seconds Since Process Event Name Seconds Last Status Time Creation Time ---------- ------- ------------- ----------------- ----------------- CAPTURE 4 4 12:04:13 03/01/02 12:04:13 03/01/02
The "Latency in Seconds" returned by this query is the difference between the current time (SYSDATE) and the "Event Creation Time." The "Seconds Since Last Status" returned by this query is the difference between the current time (SYSDATE) and the "Current Process Time."
You can find the following information about each capture process by running the query in this section:
The information displayed by this query is valid only for an enabled capture process.
Run the following query to determine the event capturing latency for each capture process:
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A10 COLUMN LATENCY_SECONDS HEADING 'Latency|in|Seconds' FORMAT 999999 COLUMN CREATE_TIME HEADING 'Event Creation|Time' FORMAT A20 COLUMN ENQUEUE_TIME HEADING 'Enqueue Time' FORMAT A20 COLUMN ENQUEUE_MESSAGE_NUMBER HEADING 'Message|Number' FORMAT 999999 SELECT CAPTURE_NAME, (ENQUEUE_TIME-ENQUEUE_MESSAGE_CREATE_TIME)*86400 LATENCY_SECONDS, TO_CHAR(ENQUEUE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIME, TO_CHAR(ENQUEUE_TIME, 'HH24:MI:SS MM/DD/YY') ENQUEUE_TIME, ENQUEUE_MESSAGE_NUMBER FROM V$STREAMS_CAPTURE;
Your output looks similar to the following:
Capture Latency Process in Event Creation Message Name Seconds Time Enqueue Time Number ---------- ------- -------------------- -------------------- ------- CAPTURE 0 10:56:51 03/01/02 10:56:51 03/01/02 253962
The "Latency in Seconds" returned by this query is the difference between the "Enqueue Time" and the "Event Creation Time."
You can display the following information about rule evaluation for each capture process by running the query in this section:
The information displayed by this query is valid only for an enabled capture process.
Run the following query to display this information for each capture process:
COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A15 COLUMN TOTAL_PREFILTER_DISCARDED HEADING 'Prefilter|Events|Discarded' FORMAT 9999999999 COLUMN TOTAL_PREFILTER_KEPT HEADING 'Prefilter|Events|Kept' FORMAT 9999999999 COLUMN TOTAL_PREFILTER_EVALUATIONS HEADING 'Prefilter|Evaluations' FORMAT 9999999999 COLUMN UNDECIDED HEADING 'Undecided|After|Prefilter' FORMAT 9999999999 COLUMN TOTAL_FULL_EVALUATIONS HEADING 'Full|Evaluations' FORMAT 9999999999 SELECT CAPTURE_NAME, TOTAL_PREFILTER_DISCARDED, TOTAL_PREFILTER_KEPT, TOTAL_PREFILTER_EVALUATIONS, (TOTAL_PREFILTER_EVALUATIONS - (TOTAL_PREFILTER_KEPT + TOTAL_PREFILTER_DISCARDED)) UNDECIDED, TOTAL_FULL_EVALUATIONS FROM V$STREAMS_CAPTURE;
Your output looks similar to the following:
Prefilter Prefilter Undecided Capture Events Events Prefilter After Full Name Discarded Kept Evaluations Prefilter Evaluations --------------- ----------- ----------- ----------- ----------- ----------- STREAMS_CAPTURE 68485 0 68570 85 27
The total number of prefilter evaluations equals the sum of the prefilter events discarded, the prefilter events kept, and the undecided events.
The following sections contain queries that you can run to display information about a SYS.AnyData queue:
To display all of the SYS.AnyData queues in a database, run the following query:
COLUMN OWNER HEADING 'Owner' FORMAT A10 COLUMN NAME HEADING 'Queue Name' FORMAT A28 COLUMN QUEUE_TABLE HEADING 'Queue Table' FORMAT A22 COLUMN USER_COMMENT HEADING 'Comment' FORMAT A15 SELECT q.OWNER, q.NAME, t.QUEUE_TABLE, 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;
Your output looks similar to the following:
Owner Queue Name Queue Table Comment ---------- ---------------------------- ---------------------- --------------- SYS AQ$_SCHEDULER$_JOBQTAB_E SCHEDULER$_JOBQTAB exception queue SYS SCHEDULER$_JOBQ SCHEDULER$_JOBQTAB Scheduler job q ueue SYS AQ$_DIR$EVENT_TABLE_E DIR$EVENT_TABLE exception queue SYS DIR$EVENT_QUEUE DIR$EVENT_TABLE SYS AQ$_DIR$CLUSTER_DIR_TABLE_E DIR$CLUSTER_DIR_TABLE exception queue SYS DIR$CLUSTER_DIR_QUEUE DIR$CLUSTER_DIR_TABLE STRMADMIN AQ$_STREAMS_QUEUE_TABLE_E STREAMS_QUEUE_TABLE exception queue STRMADMIN STREAMS_QUEUE STREAMS_QUEUE_TABLE
An exception queue is created automatically when you create a SYS.AnyData queue.
You can view the messaging clients in a database by querying the DBA_STREAMS_MESSAGE_CONSUMERS data dictionary view. The query in this section displays the following information about each messaging client:
Run the following query to view this information about messaging clients:
COLUMN STREAMS_NAME HEADING 'Messaging|Client' FORMAT A25 COLUMN QUEUE_OWNER HEADING 'Queue Owner' FORMAT A10 COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A18 COLUMN RULE_SET_NAME HEADING 'Positive|Rule Set' FORMAT A11 COLUMN NEGATIVE_RULE_SET_NAME HEADING 'Negative|Rule Set' FORMAT A11 SELECT STREAMS_NAME, QUEUE_OWNER, QUEUE_NAME, RULE_SET_NAME, NEGATIVE_RULE_SET_NAME FROM DBA_STREAMS_MESSAGE_CONSUMERS;
Your output looks similar to the following:
Messaging Positive Negative Client Queue Owne Queue Name Rule Set Rule Set ------------------------- ---------- ------------------ ----------- ----------- SCHEDULER_PICKUP SYS SCHEDULER$_JOBQ RULESET$_8 SCHEDULER_COORDINATOR SYS SCHEDULER$_JOBQ RULESET$_4 HR STRMADMIN STREAMS_QUEUE RULESET$_15
| See Also:
Chapter 3, "Streams Staging and Propagation" for more information about messaging clients |
You can configure a message notification to send a notification when a message that can be dequeued by a messaging client is enqueued into a queue. The notification can be sent to an email address, to an HTTP URL, or to a PL/SQL procedure. Run the following query to view the message notifications configured in a database:
COLUMN STREAMS_NAME HEADING 'Messaging|Client' FORMAT A10 COLUMN QUEUE_OWNER HEADING 'Queue|Owner' FORMAT A5 COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A20 COLUMN NOTIFICATION_TYPE HEADING 'Notification|Type' FORMAT A15 COLUMN NOTIFICATION_ACTION HEADING 'Notification|Action' FORMAT A25 SELECT STREAMS_NAME, QUEUE_OWNER, QUEUE_NAME, NOTIFICATION_TYPE, NOTIFICATION_ACTION FROM DBA_STREAMS_MESSAGE_CONSUMERS WHERE NOTIFICATION_TYPE IS NOT NULL;
Your output looks similar to the following:
Messaging Queue Notification Notification Client Owner Queue Name Type Action ---------- ----- -------------------- --------------- ------------------------- OE OE NOTIFICATION_QUEUE MAIL mary.smith@mycompany.com
To determine the consumer for each user-enqueued event in a queue, query AQ$queue_table_name in the queue owner's schema, where queue_table_name is the name of the queue table. For example, to find the consumers of the user-enqueued events in the oe_q_table_any queue table, run the following query:
COLUMN MSG_ID HEADING 'Message ID' FORMAT 9999 COLUMN MSG_STATE HEADING 'Message State' FORMAT A13 COLUMN CONSUMER_NAME HEADING 'Consumer' FORMAT A30 SELECT MSG_ID, MSG_STATE, CONSUMER_NAME FROM AQ$OE_Q_TABLE_ANY;
Your output looks similar to the following:
Message ID Message State Consumer -------------------------------- ------------- ------------------------------ B79AC412AE6E08CAE034080020AE3E0A PROCESSED OE B79AC412AE6F08CAE034080020AE3E0A PROCESSED OE B79AC412AE7008CAE034080020AE3E0A PROCESSED OE
| See Also:
Oracle Streams Advanced Queuing User's Guide and Reference for an example that enqueues messages into a |
In a SYS.AnyData queue, to view the contents of a payload that is encapsulated within a SYS.AnyData payload, you query the queue table using the Accessdata_type static functions of the SYS.AnyData type, where data_type is the type of payload to view.
| See Also:
"Wrapping User Message Payloads in a SYS.AnyData Wrapper and Enqueuing Them" for an example that enqueues the events shown in the queries in this section into a |
For example, to view the contents of payload of type NUMBER in a queue with a queue table named oe_queue_table, run the following query as the queue owner:
SELECT qt.user_data.AccessNumber() "Numbers in Queue" FROM strmadmin.oe_q_table_any qt;
Your output looks similar to the following:
Numbers in Queue ---------------- 16
Similarly, to view the contents of a payload of type VARCHAR2 in a queue with a queue table named oe_q_table_any, run the following query:
SELECT qt.user_data.AccessVarchar2() "Varchar2s in Queue" FROM strmadmin.oe_q_table_any qt;
Your output looks similar to the following:
Varchar2s in Queue -------------------------------------------------------------------------------- Chemicals - SW
To view the contents of a user-defined datatype, you query the queue table using a custom function that you create. For example, to view the contents of a payload of oe.cust_address_typ, connect as the Streams administrator and create a function similar to the following:
CONNECT oe/oe CREATE OR REPLACE FUNCTION oe.view_cust_address_typ( in_any IN SYS.AnyData) RETURN oe.cust_address_typ IS address oe.cust_address_typ; num_var NUMBER; BEGIN IF (in_any.GetTypeName() = 'OE.CUST_ADDRESS_TYP') THEN num_var := in_any.GetObject(address); RETURN address; ELSE RETURN NULL; END IF; END; / GRANT EXECUTE ON oe.view_cust_address_typ TO strmadmin; GRANT EXECUTE ON oe.cust_address_typ TO strmadmin;
Query the queue table using the function, as in the following example:
CONNECT strmadmin/strmadminpw SELECT oe.view_cust_address_typ(qt.user_data) "Customer Addresses" FROM strmadmin.oe_q_table_any qt WHERE qt.user_data.GetTypeName() = 'OE.CUST_ADDRESS_TYP';
Your output looks similar to the following:
Customer Addresses(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID -------------------------------------------------------------------------------- CUST_ADDRESS_TYP('1646 Brazil Blvd', '361168', 'Chennai', 'Tam', 'IN')
The following sections contain queries that you can run to display information about propagations and propagation jobs:
You can determine the source queue and destination queue for each propagation by querying the DBA_PROPAGATION data dictionary view. This view contains information about each propagation whose source queue is at the local database.
For example, the following query displays the following information for a propagation named dbs1_to_dbs2:
COLUMN 'Source Queue' FORMAT A35 COLUMN 'Destination Queue' FORMAT A35 SELECT p.SOURCE_QUEUE_OWNER ||'.'|| p.SOURCE_QUEUE_NAME ||'@'|| g.GLOBAL_NAME "Source Queue", p.DESTINATION_QUEUE_OWNER ||'.'|| p.DESTINATION_QUEUE_NAME ||'@'|| p.DESTINATION_DBLINK "Destination Queue" FROM DBA_PROPAGATION p, GLOBAL_NAME g;
Your output looks similar to the following:
Source Queue Destination Queue ----------------------------------- ----------------------------------- STRMADMIN.STREAMS_QUEUE@DBS1.NET STRMADMIN.STREAMS_QUEUE@DBS2.NET STRMADMIN.STRM02_QUEUE@DBS1.NET STRMADMIN.STRM02_QUEUE@DBS2.NET
The following query displays the following information for each propagation:
To display this general information about each propagation in a database, run the following query:
COLUMN PROPAGATION_NAME HEADING 'Propagation|Name' FORMAT A20 COLUMN RULE_SET_OWNER HEADING 'Positive|Rule Set|Owner' FORMAT A10 COLUMN RULE_SET_NAME HEADING 'Positive Rule|Set Name' FORMAT A15 COLUMN NEGATIVE_RULE_SET_OWNER HEADING 'Negative|Rule Set|Owner' FORMAT A10 COLUMN NEGATIVE_RULE_SET_NAME HEADING 'Negative Rule|Set Name' FORMAT A15 SELECT PROPAGATION_NAME, RULE_SET_OWNER, RULE_SET_NAME, NEGATIVE_RULE_SET_OWNER, NEGATIVE_RULE_SET_NAME FROM DBA_PROPAGATION;
Your output looks similar to the following:
Positive Negative Propagation Rule Set Positive Rule Rule Set Negative Rule Name Owner Set Name Owner Set Name -------------------- ---------- --------------- ---------- --------------- STRM01_PROPAGATION STRMADMIN RULESET$_22 STRMADMIN RULESET$_31
The query in this section displays the following information about the propagation schedule for a propagation job used by a propagation named dbs1_to_dbs2:
Run this query at the database that contains the source queue:
COLUMN START_DATE HEADING 'Start Date' COLUMN PROPAGATION_WINDOW HEADING 'Duration|in Seconds' FORMAT 99999 COLUMN NEXT_TIME HEADING 'Next|Time' FORMAT A8 COLUMN LATENCY HEADING 'Latency|in Seconds' FORMAT 99999 COLUMN SCHEDULE_DISABLED HEADING 'Status' FORMAT A8 COLUMN PROCESS_NAME HEADING 'Process' FORMAT A8 COLUMN FAILURES HEADING 'Number of|Failures' FORMAT 99 SELECT TO_CHAR(s.START_DATE, 'HH24:MI:SS MM/DD/YY') START_DATE, s.PROPAGATION_WINDOW, s.NEXT_TIME, s.LATENCY, DECODE(s.SCHEDULE_DISABLED, 'Y', 'Disabled', 'N', 'Enabled') SCHEDULE_DISABLED, s.PROCESS_NAME, s.FAILURES FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p WHERE p.PROPAGATION_NAME = 'DBS1_TO_DBS2' AND p.DESTINATION_DBLINK = s.DESTINATION AND s.SCHEMA = p.SOURCE_QUEUE_OWNER AND s.QNAME = p.SOURCE_QUEUE_NAME;
Your output looks similar to the following:
Duration Next Latency Number of Start Date in Seconds Time in Seconds Status Process Failures ----------------- ---------- -------- ---------- -------- -------- --------- 15:23:40 03/02/02 5 Enabled J002 0
This propagation job uses the default schedule for a Streams propagation job. That is, the duration and next time are both NULL, and the latency is five seconds. When the duration is NULL, the job propagates changes without restarting automatically. When the next time is NULL, the propagation job is running currently.
See Also:
|
All propagation jobs from a source queue that share the same database link have a single propagation schedule. The query in this section displays the following information for each propagation:
Run the following query to display this information for each propagation with a source queue at the local database:
COLUMN PROPAGATION_NAME HEADING 'Propagation|Name' FORMAT A20 COLUMN TOTAL_TIME HEADING 'Total Time|Executing|in Seconds' FORMAT 999999 COLUMN TOTAL_NUMBER HEADING 'Total Events|Propagated' FORMAT 999999999 COLUMN TOTAL_BYTES HEADING 'Total Bytes|Propagated' FORMAT 9999999999999 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;
Your output looks similar to the following:
Total Time Propagation Executing Total Events Total Bytes Name in Seconds Propagated Propagated -------------------- ---------- ------------ -------------- MULT3_TO_MULT1 351 872 875252 MULT3_TO_MULT2 596 872 875252
| See Also:
Oracle Streams Advanced Queuing User's Guide and Reference and Oracle Database Reference for more information about the |
The following sections contain queries that you can run to display information about an apply process:
You can determine the following information for each apply process in a database by running the query in this section:
ENABLED, DISABLED, or ABORTEDTo display this general information about each apply process in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A15 COLUMN QUEUE_NAME HEADING 'Apply|Process|Queue' FORMAT A15 COLUMN RULE_SET_NAME HEADING 'Positive|Rule Set' FORMAT A15 COLUMN NEGATIVE_RULE_SET_NAME HEADING 'Negative|Rule Set' FORMAT A15 COLUMN STATUS HEADING 'Apply|Process|Status' FORMAT A15 SELECT APPLY_NAME, QUEUE_NAME, RULE_SET_NAME, NEGATIVE_RULE_SET_NAME, STATUS FROM DBA_APPLY;
Your output looks similar to the following:
Apply Apply Apply Process Process Positive Negative Process Name Queue Rule Set Rule Set Status --------------- --------------- --------------- --------------- --------------- STRM01_APPLY STRM01_QUEUE RULESET$_36 ENABLED APPLY_EMP STREAMS_QUEUE RULESET$_16 DISABLED APPLY STREAMS_QUEUE RULESET$_21 RULESET$_23 ENABLED
If the status of an apply process is ABORTED, then you can query the ERROR_NUMBER and ERROR_MESSAGE columns in the DBA_APPLY data dictionary view to determine the error.
| See Also:
"Checking for Apply Errors" to check for apply errors if the apply process status is |
You can display the following general information about each apply process in a database by running the query in this section:
To display this general information about each apply process in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20 COLUMN APPLY_CAPTURED HEADING 'Type of Events Applied' FORMAT A15 COLUMN APPLY_USER HEADING 'Apply User' FORMAT A30 SELECT APPLY_NAME, DECODE(APPLY_CAPTURED, 'YES', 'Captured', 'NO', 'User-Enqueued') APPLY_CAPTURED, APPLY_USER FROM DBA_APPLY;
Your output looks similar to the following:
Apply Process Name Type of Events Apply User -------------------- --------------- ------------------------------ STRM01_APPLY Captured STRMADMIN APPLY_OE User-Enqueued STRMADMIN APPLY Captured HR
The following query displays the current setting for each apply process parameter for each apply process in a database:
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15 COLUMN PARAMETER HEADING 'Parameter' FORMAT A20 COLUMN VALUE HEADING 'Value' FORMAT A20 COLUMN SET_BY_USER HEADING 'Set by User?' FORMAT A20 SELECT APPLY_NAME, PARAMETER, VALUE, SET_BY_USER FROM DBA_APPLY_PARAMETERS;
Your output looks similar to the following:
Apply Process Name Parameter Value Set by User? --------------- -------------------- -------------------- -------------------- STRM01_APPLY COMMIT_SERIALIZATION FULL NO STRM01_APPLY DISABLE_ON_ERROR Y YES STRM01_APPLY DISABLE_ON_LIMIT N NO STRM01_APPLY MAXIMUM_SCN INFINITE NO STRM01_APPLY PARALLELISM 1 NO STRM01_APPLY STARTUP_SECONDS 0 NO STRM01_APPLY TIME_LIMIT INFINITE NO STRM01_APPLY TRACE_LEVEL 0 NO STRM01_APPLY TRANSACTION_LIMIT INFINITE NO STRM01_APPLY WRITE_ALERT_LOG Y NO
This section contains queries that display information about apply process message handlers and error handlers.
See Also:
|
When you specify a local error handler using the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package at a destination database, you either can specify that the handler runs for a specific apply process or that the handler is a general handler that runs for all apply processes in the database that apply changes locally when an error is raised by an apply process. A specific error handler takes precedence over a generic error handler. An error handler is run for a specified operation on a specific table.
To display the error handler for each apply process that applies changes locally in a database, run the following query:
COLUMN OBJECT_OWNER HEADING 'Table|Owner' FORMAT A5 COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A10 COLUMN OPERATION_NAME HEADING 'Operation' FORMAT A10 COLUMN USER_PROCEDURE HEADING 'Handler Procedure' FORMAT A30 COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15 SELECT OBJECT_OWNER, OBJECT_NAME, OPERATION_NAME, USER_PROCEDURE, APPLY_NAME FROM DBA_APPLY_DML_HANDLERS WHERE ERROR_HANDLER = 'Y' ORDER BY OBJECT_OWNER, OBJECT_NAME;
Your output looks similar to the following:
Table Apply Process Owner Table Name Operation Handler Procedure Name ----- ---------- ---------- ------------------------------ -------------- HR REGIONS INSERT "STRMADMIN"."ERRORS_PKG"."REGI ONS_PK_ERROR"
Because Apply Process Name is NULL for the strmadmin.errors_pkg.regions_pk_error error handler, this handler is a general handler that runs for all of the local apply processes.
To display each message handler in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20 COLUMN MESSAGE_HANDLER HEADING 'Message Handler' FORMAT A20 SELECT APPLY_NAME, MESSAGE_HANDLER FROM DBA_APPLY WHERE MESSAGE_HANDLER IS NOT NULL;
Your output looks similar to the following:
Apply Process Name Message Handler -------------------- -------------------- STRM03_APPLY "HR"."MES_PROC"
To display each precommit handler in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20 COLUMN PRECOMMIT_HANDLER HEADING 'Precommit Handler' FORMAT A30 COLUMN APPLY_CAPTURED HEADING 'Type of|Events|Applied' FORMAT A15 SELECT APPLY_NAME, PRECOMMIT_HANDLER, DECODE(APPLY_CAPTURED, 'YES', 'Captured', 'NO', 'User-Enqueued') APPLY_CAPTURED FROM DBA_APPLY WHERE PRECOMMIT_HANDLER IS NOT NULL;
Your output looks similar to the following:
Type of Events Apply Process Name Precommit Handler Applied -------------------- ------------------------------ --------------- STRM01_APPLY "STRMADMIN"."HISTORY_COMMIT" Captured
The reader server for an apply process dequeues events from the queue. The reader server is a parallel execution server that computes dependencies between LCRs and assembles events into transactions. The reader server then returns the assembled transactions to the coordinator, which assigns them to idle apply servers.
The query in this section displays the following information about the reader server for each apply process:
IDLE, DEQUEUE MESSAGES, or SCHEDULE MESSAGESThe information displayed by this query is valid only for an enabled apply process.
Run the following query to display this information for each apply process:
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17 COLUMN APPLY_CAPTURED HEADING 'Apply Type' FORMAT A22 COLUMN PROCESS_NAME HEADING 'Process|Name' FORMAT A7 COLUMN STATE HEADING 'State' FORMAT A17 COLUMN TOTAL_MESSAGES_DEQUEUED HEADING 'Total Events|Dequeued' FORMAT 99999999 SELECT r.APPLY_NAME, DECODE(ap.APPLY_CAPTURED, 'YES','Captured LCRS', 'NO','User-enqueued messages','UNKNOWN') APPLY_CAPTURED, SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME, r.STATE, r.TOTAL_MESSAGES_DEQUEUED 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;
Your output looks similar to the following:
Apply Process Process Total Events Name Apply Type Name State Dequeued ----------------- ---------------------- ------- ----------------- ------------ APPLY_FROM_MULT2 Captured LCRS P000 DEQUEUE MESSAGES 3803 APPLY_FROM_MULT1 Captured LCRS P001 DEQUEUE MESSAGES 2754
The query in this section displays the following information about the last event dequeued by each apply process:
The information displayed by this query is valid only for an enabled apply process.
Run the following query to display this information for each apply process:
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17 COLUMN LATENCY HEADING 'Latency|in|Seconds' FORMAT 9999 COLUMN CREATION HEADING 'Event Creation' FORMAT A17 COLUMN LAST_DEQUEUE HEADING 'Last Dequeue Time' FORMAT A20 COLUMN DEQUEUED_MESSAGE_NUMBER HEADING 'Dequeued|Message Number' FORMAT 999999 SELECT APPLY_NAME, (DEQUEUE_TIME-DEQUEUED_MESSAGE_CREATE_TIME)*86400 LATENCY, TO_CHAR(DEQUEUED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') CREATION, TO_CHAR(DEQUEUE_TIME,'HH24:MI:SS MM/DD/YY') LAST_DEQUEUE, DEQUEUED_MESSAGE_NUMBER FROM V$STREAMS_APPLY_READER;
Your output looks similar to the following:
Latency Apply Process in Dequeued Name Seconds Event Creation Last Dequeue Time Message Number ----------------- ------- ----------------- -------------------- -------------- APPLY_FROM_MULT1 36 10:56:51 02/27/03 10:57:27 02/27/03 253962 APPLY_FROM_MULT2 18 13:13:04 02/28/03 13:13:22 02/28/03 633043
A coordinator process gets transactions from the reader server and passes these transactions to apply servers. The coordinator process name is apnn, where nn is a coordinator process number.
The query in this section displays the following information about the coordinator process for each apply process:
apnn)INITIALIZING, APPLYING, SHUTTING DOWN CLEANLY, or ABORTINGThe information displayed by this query is valid only for an enabled apply process.
Run the following query to display this information for each apply process:
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17 COLUMN PROCESS_NAME HEADING 'Coordinator|Process|Name' FORMAT A11 COLUMN SID HEADING 'Session|ID' FORMAT 9999 COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 9999 COLUMN STATE HEADING 'State' FORMAT A21 SELECT c.APPLY_NAME, SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME, c.SID, c.SERIAL#, c.STATE FROM V$STREAMS_APPLY_COORDINATOR c, V$SESSION s WHERE c.SID = s.SID AND c.SERIAL# = s.SERIAL#;
Your output looks similar to the following:
Coordinator Session Apply Process Process Session Serial Name Name ID Number State ----------------- ----------- ------- ------- --------------------- APPLY_FROM_MULT1 A001 16 1 APPLYING APPLY_FROM_MULT2 A002 18 1 APPLYING
The query in this section displays the following information about the transactions received, applied, and being applied by each apply process:
The information displayed by this query is valid only for an enabled apply process.
For example, to display this information for an apply process named apply, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A25 COLUMN TOTAL_RECEIVED HEADING 'Total|Trans|Received' FORMAT 99999999 COLUMN TOTAL_APPLIED HEADING 'Total|Trans|Applied' FORMAT 99999999 COLUMN TOTAL_ERRORS HEADING 'Total|Apply|Errors' FORMAT 9999 COLUMN BEING_APPLIED HEADING 'Total|Trans Being|Applied' FORMAT 99999999 COLUMN TOTAL_IGNORED HEADING 'Total|Trans|Ignored' FORMAT 99999999 SELECT APPLY_NAME, TOTAL_RECEIVED, TOTAL_APPLIED, TOTAL_ERRORS, (TOTAL_ASSIGNED - (TOTAL_ROLLBACKS + TOTAL_APPLIED)) BEING_APPLIED, TOTAL_IGNORED FROM V$STREAMS_APPLY_COORDINATOR;
Your output looks similar to the following:
Total Total Total Total Total Trans Trans Apply Trans Being Trans Apply Process Name Received Applied Errors Applied Ignored ------------------------- --------- --------- ------ ----------- --------- APPLY_FROM_MULT1 81 73 2 6 0 APPLY_FROM_MULT2 114 96 0 14 4
This section contains two different queries that show the capture to apply latency for a particular event. That is, for captured events, these queries show the amount of time between when the event was created at a source database and when the event was applied by the apply process. One query uses the V$STREAMS_APPLY_COORDINATOR dynamic performance view, while the other uses the DBA_APPLY_PROGRESS static data dictionary view.
|
Note: These queries assume that the apply process applies captured events, not user-enqueued events. |
The following are the major differences between these two queries:
V$STREAMS_APPLY_COORDINATOR view, while the apply process can be enabled or disabled when you run the query on the DBA_APPLY_PROGRESS view.V$STREAMS_APPLY_COORDINATOR view may show the latency for a more recent transaction than the query on the DBA_APPLY_PROGRESS view.Both queries display the following information about an event applied by each apply process:
Run the following query to display the capture to apply latency using the V$STREAMS_APPLY_COORDINATOR view for an event for each apply process:
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17 COLUMN 'Latency in Seconds' FORMAT 999999 COLUMN 'Event Creation' FORMAT A17 COLUMN 'Apply Time' FORMAT A17 COLUMN HWM_MESSAGE_NUMBER HEADING 'Applied|Message|Number' FORMAT 999999 SELECT APPLY_NAME, (HWM_TIME-HWM_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds", TO_CHAR(HWM_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') "Event Creation", TO_CHAR(HWM_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time", HWM_MESSAGE_NUMBER FROM V$STREAMS_APPLY_COORDINATOR;
Your output looks similar to the following:
Applied Apply Process Message Name Latency in Seconds Event Creation Apply Time Number ----------------- ------------------ ----------------- ----------------- ------- APPLY_FROM_MULT1 781 14:05:29 02/28/03 14:18:30 02/28/03 638609 APPLY_FROM_MULT2 381 13:13:04 02/28/03 13:19:25 02/28/03 633043
Run the following query to display the capture to apply latency using the DBA_APPLY_PROGRESS view for an event for each apply process:
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17 COLUMN 'Latency in Seconds' FORMAT 999999 COLUMN 'Event Creation' FORMAT A17 COLUMN 'Apply Time' FORMAT A17 COLUMN APPLIED_MESSAGE_NUMBER HEADING 'Applied|Message|Number' FORMAT 999999 SELECT APPLY_NAME, (APPLY_TIME-APPLIED_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds", TO_CHAR(APPLIED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') "Event Creation", TO_CHAR(APPLY_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time", APPLIED_MESSAGE_NUMBER FROM DBA_APPLY_PROGRESS;
Your output looks similar to the following:
Applied Apply Process Message Name Latency in Seconds Event Creation Apply Time Number ----------------- ------------------ ----------------- ----------------- ------- APPLY_FROM_MULT1 219 14:05:23 02/28/03 14:09:02 02/28/03 638607 APPLY_FROM_MULT2 2641 12:29:21 02/28/03 13:13:22 02/28/03 617393
An apply process can use one or more apply servers that apply LCRs to database objects as DML statements or DDL statements or pass the LCRs to their appropriate handlers. For non-LCR messages, the apply servers pass the events to the message handler. Each apply server is a parallel execution server.
The query in this section displays the following information about the apply servers for each apply process:
IDLE, RECORD LOW-WATERMARK, ADD PARTITION, DROP PARTITION, EXECUTE TRANSACTION, WAIT COMMIT, WAIT DEPENDENCY, WAIT FOR NEXT CHUNK, or TRANSACTION CLEANUP. See V$STREAMS_APPLY_SERVER in the Oracle Database Reference for more information about these states.The information displayed by this query is valid only for an enabled apply process.
Run the following query to display information about the apply servers for each apply process:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A22 COLUMN PROCESS_NAME HEADING 'Process Name' FORMAT A12 COLUMN STATE HEADING 'State' FORMAT A17 COLUMN TOTAL_ASSIGNED HEADING 'Total|Transactions|Assigned' FORMAT 99999999 COLUMN TOTAL_MESSAGES_APPLIED HEADING 'Total|Events|Applied' FORMAT 99999999 SELECT r.APPLY_NAME, SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME, r.STATE, r.TOTAL_ASSIGNED, r.TOTAL_MESSAGES_APPLIED FROM V$STREAMS_APPLY_SERVER R, V$SESSION S WHERE r.SID = s.SID AND r.SERIAL# = s.SERIAL# ORDER BY r.SERVER_ID;
Your output looks similar to the following:
Total Total Transactions Events Apply Process Name Process Name State Assigned Applied ---------------------- ------------ ----------------- ------------ --------- APPLY P001 IDLE 94 2141 APPLY P002 IDLE 12 276 APPLY P003 IDLE 0 0
In some environments, an apply process may not use all of the apply servers available to it. For example, apply process parallelism may be set to five, but only three apply servers are ever used by the apply process. In this case, the effective apply parallelism is three.
The following query displays the effective apply parallelism for an apply process named apply:
SELECT COUNT(SERVER_ID) "Effective Parallelism" FROM V$STREAMS_APPLY_SERVER WHERE APPLY_NAME = 'APPLY' AND TOTAL_MESSAGES_APPLIED > 0;
Your output looks similar to the following:
Effective Parallelism --------------------- 2
This query returned two for the effective parallelism. If parallelism is set to three for the apply process named apply, then one apply server has not been used since the last time the apply process was started.
You can display the total number of events applied by each apply server by running the following query:
COLUMN SERVER_ID HEADING 'Apply Server ID' FORMAT 99 COLUMN TOTAL_MESSAGES_APPLIED HEADING 'Total Events Applied' FORMAT 999999 SELECT SERVER_ID, TOTAL_MESSAGES_APPLIED FROM V$STREAMS_APPLY_SERVER WHERE APPLY_NAME = 'APPLY' ORDER BY SERVER_ID;
Your output looks similar to the following:
Apply Server ID Total Events Applied --------------- -------------------- 1 2141 2 276 3 0
In this case, apply server 3 has not been used by the apply process since it was last started. If the parallelism setting for an apply process is higher than the effective parallelism for the apply process, then consider lowering the parallelism setting.
You can specify a destination queue for a rule using the SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package. If an apply process has such a rule in its positive rule set, and an event satisfies the rule, then the apply process enqueues the event into the destination queue.
To view destination queue settings for rules, run the following query:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A15 COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A15 COLUMN DESTINATION_QUEUE_NAME HEADING 'Destination Queue' FORMAT A30 SELECT RULE_OWNER, RULE_NAME, DESTINATION_QUEUE_NAME FROM DBA_APPLY_ENQUEUE;
Your output looks similar to the following:
Rule Owner Rule Name Destination Queue --------------- --------------- ------------------------------ STRMADMIN DEPARTMENTS17 "STRMADMIN"."STREAMS_QUEUE"
You can specify an execution directive for a rule using the SET_EXECUTE procedure in the DBMS_APPLY_ADM package. An execution directive controls whether an event that satisfies the specified rule is executed by an apply process. If an apply process has a rule in its positive rule set with NO for its execution directive, and an event satisfies the rule, then the apply process does not execute the event and does not send the event to any apply handler.
To view each rule with NO for its execution directive, run the following query:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A20 COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A20 SELECT RULE_OWNER, RULE_NAME FROM DBA_APPLY_EXECUTE WHERE EXECUTE_EVENT = 'NO';
Your output looks similar to the following:
Rule Owner Rule Name -------------------- -------------------- STRMADMIN DEPARTMENTS18
To check for apply errors, run the following query:
COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A10 COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A10 COLUMN LOCAL_TRANSACTION_ID HEADING 'Local|Transaction|ID' FORMAT A11 COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 99999999 COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A20 COLUMN MESSAGE_COUNT HEADING 'Events in|Error|Transaction' FORMAT 99999999 SELECT APPLY_NAME, SOURCE_DATABASE, LOCAL_TRANSACTION_ID, ERROR_NUMBER, ERROR_MESSAGE, MESSAGE_COUNT FROM DBA_APPLY_ERROR;
If there are any apply errors, then your output looks similar to the following:
Apply Local Events in Process Source Transaction Error Name Database ID Error Number Error Message Transaction ---------- ---------- ----------- ------------ -------------------- ----------- APPLY_FROM MULT3.NET 1.62.948 1403 ORA-01403: no data f 1 _MULT3 ound APPLY_FROM MULT2.NET 1.54.948 1403 ORA-01403: no data f 1 _MULT2 ound
If there are apply errors, then you can either try to reexecute the transactions that encountered the errors, or you can delete the transactions. If you want to reexecute a transaction that encountered an error, then first correct the condition that caused the transaction to raise an error.
If you want to delete a transaction that encountered an error, then you may need to resynchronize data manually if you are sharing data between multiple databases. Remember to set an appropriate session tag, if necessary, when you resynchronize data manually.
See Also:
|
This section contains SQL scripts that you can use to display detailed information about the error transactions in the error queue in a database. These scripts are designed to display information about LCR events, but you can extend them to display information about any non-LCR events used in your environment as well.
To use these scripts, complete the following steps:
The user who creates and runs the print_errors and print_transaction procedures described in the following sections must be granted explicit SELECT privilege on the DBA_APPLY_ERROR data dictionary view. This privilege cannot be granted through a role. Running the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_STREAMS_AUTH package on a user grants this privilege to the user.
To grant this privilege to a user directly, complete the following steps:
SELECT privilege on the DBA_APPLY_ERROR data dictionary view to the appropriate user. For example, to grant this privilege to the strmadmin user, run the following statement:
GRANT SELECT ON DBA_APPLY_ERROR TO strmadmin;
EXECUTE privilege on the DBMS_APPLY_ADM package. For example, to grant this privilege to the strmadmin user, run the following statement:
GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin;
The following procedure prints the value in a specified SYS.AnyData object for some selected datatypes. You may add more datatypes to this procedure if you wish.
CREATE OR REPLACE PROCEDURE print_any(data IN SYS.AnyData) IS tn VARCHAR2(61); str VARCHAR2(4000); chr VARCHAR2(1000); num NUMBER; dat DATE; rw RAW(4000); res NUMBER; BEGIN IF data IS NULL THEN DBMS_OUTPUT.PUT_LINE('NULL value'); RETURN; END IF; tn := data.GETTYPENAME(); IF tn = 'SYS.VARCHAR2' THEN res := data.GETVARCHAR2(str); DBMS_OUTPUT.PUT_LINE(SUBSTR(str,0,253)); ELSIF tn = 'SYS.CHAR' then res := data.GETCHAR(chr); DBMS_OUTPUT.PUT_LINE(SUBSTR(chr,0,253)); ELSIF tn = 'SYS.VARCHAR' THEN res := data.GETVARCHAR(chr); DBMS_OUTPUT.PUT_LINE(chr); ELSIF tn = 'SYS.NUMBER' THEN res := data.GETNUMBER(num); DBMS_OUTPUT.PUT_LINE(num); ELSIF tn = 'SYS.DATE' THEN res := data.GETDATE(dat); DBMS_OUTPUT.PUT_LINE(dat); ELSIF tn = 'SYS.RAW' THEN -- res := data.GETRAW(rw); -- DBMS_OUTPUT.PUT_LINE(SUBSTR(DBMS_LOB.SUBSTR(rw),0,253)); DBMS_OUTPUT.PUT_LINE('BLOB Value'); ELSIF tn = 'SYS.BLOB' THEN DBMS_OUTPUT.PUT_LINE('BLOB Found'); ELSE DBMS_OUTPUT.PUT_LINE('typename is ' || tn); END IF; END print_any; /
The following procedure prints a specified LCR. It calls the print_any procedure created in "Create a Procedure That Prints the Value in a SYS.AnyData Object".
CREATE OR REPLACE PROCEDURE print_lcr(lcr IN SYS.ANYDATA) IS typenm VARCHAR2(61); ddllcr SYS.LCR$_DDL_RECORD; proclcr SYS.LCR$_PROCEDURE_RECORD; rowlcr SYS.LCR$_ROW_RECORD; res NUMBER; newlist SYS.LCR$_ROW_LIST; oldlist SYS.LCR$_ROW_LIST; ddl_text CLOB; ext_attr SYS.AnyData; BEGIN typenm := lcr.GETTYPENAME(); DBMS_OUTPUT.PUT_LINE('type name: ' || typenm); IF (typenm = 'SYS.LCR$_DDL_RECORD') THEN res := lcr.GETOBJECT(ddllcr); DBMS_OUTPUT.PUT_LINE('source database: ' || ddllcr.GET_SOURCE_DATABASE_NAME); DBMS_OUTPUT.PUT_LINE('owner: ' || ddllcr.GET_OBJECT_OWNER); DBMS_OUTPUT.PUT_LINE('object: ' || ddllcr.GET_OBJECT_NAME); DBMS_OUTPUT.PUT_LINE('is tag null: ' || ddllcr.IS_NULL_TAG); DBMS_LOB.CREATETEMPORARY(ddl_text, true); ddllcr.GET_DDL_TEXT(ddl_text); DBMS_OUTPUT.PUT_LINE('ddl: ' || ddl_text); -- Print extra attributes in DDL LCR ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('serial#'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('serial#: ' || ext_attr.ACCESSNUMBER()); END IF; ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('session#'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('session#: ' || ext_attr.ACCESSNUMBER()); END IF; ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('thread#'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('thread#: ' || ext_attr.ACCESSNUMBER()); END IF; ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('tx_name'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('transaction name: ' || ext_attr.ACCESSVARCHAR2()); END IF; ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('username'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('username: ' || ext_attr.ACCESSVARCHAR2()); END IF; DBMS_LOB.FREETEMPORARY(ddl_text); ELSIF (typenm = 'SYS.LCR$_ROW_RECORD') THEN res := lcr.GETOBJECT(rowlcr); DBMS_OUTPUT.PUT_LINE('source database: ' || rowlcr.GET_SOURCE_DATABASE_NAME); DBMS_OUTPUT.PUT_LINE('owner: ' || rowlcr.GET_OBJECT_OWNER); DBMS_OUTPUT.PUT_LINE('object: ' || rowlcr.GET_OBJECT_NAME); DBMS_OUTPUT.PUT_LINE('is tag null: ' || rowlcr.IS_NULL_TAG); DBMS_OUTPUT.PUT_LINE('command_type: ' || rowlcr.GET_COMMAND_TYPE); oldlist := rowlcr.GET_VALUES('old'); FOR i IN 1..oldlist.COUNT LOOP IF oldlist(i) IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('old(' || i || '): ' || oldlist(i).column_name); print_any(oldlist(i).data); END IF; END LOOP; newlist := rowlcr.GET_VALUES('new', 'n'); FOR i in 1..newlist.count LOOP IF newlist(i) IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('new(' || i || '): ' || newlist(i).column_name); print_any(newlist(i).data); END IF; END LOOP; -- Print extra attributes in row LCR ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('row_id'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('row_id: ' || ext_attr.ACCESSUROWID()); END IF; ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('serial#'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('serial#: ' || ext_attr.ACCESSNUMBER()); END IF; ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('session#'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('session#: ' || ext_attr.ACCESSNUMBER()); END IF; ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('thread#'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('thread#: ' || ext_attr.ACCESSNUMBER()); END IF; ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('tx_name'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('transaction name: ' || ext_attr.ACCESSVARCHAR2()); END IF; ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('username'); IF (ext_attr IS NOT NULL) THEN DBMS_OUTPUT.PUT_LINE('username: ' || ext_attr.ACCESSVARCHAR2()); END IF; ELSE DBMS_OUTPUT.PUT_LINE('Non-LCR Message with type ' || typenm); END IF; END print_lcr; /
The following procedure prints all of the LCRs in all of the error queues. It calls the print_lcr procedure created in "Create a Procedure That Prints a Specified LCR".
CREATE OR REPLACE PROCEDURE print_errors IS CURSOR c IS SELECT LOCAL_TRANSACTION_ID, SOURCE_DATABASE, MESSAGE_NUMBER, MESSAGE_COUNT, ERROR_NUMBER, ERROR_MESSAGE FROM DBA_APPLY_ERROR ORDER BY SOURCE_DATABASE, SOURCE_COMMIT_SCN; i NUMBER; txnid VARCHAR2(30); source VARCHAR2(128); msgno NUMBER; msgcnt NUMBER; errnum NUMBER := 0; errno NUMBER; errmsg VARCHAR2(255); lcr SYS.AnyData; r NUMBER; BEGIN FOR r IN c LOOP errnum := errnum + 1; msgcnt := r.MESSAGE_COUNT; txnid := r.LOCAL_TRANSACTION_ID; source := r.SOURCE_DATABASE; msgno := r.MESSAGE_NUMBER; errno := r.ERROR_NUMBER; errmsg := r.ERROR_MESSAGE; DBMS_OUTPUT.PUT_LINE('*************************************************'); DBMS_OUTPUT.PUT_LINE('----- ERROR #' || errnum); DBMS_OUTPUT.PUT_LINE('----- Local Transaction ID: ' || txnid); DBMS_OUTPUT.PUT_LINE('----- Source Database: ' || source); DBMS_OUTPUT.PUT_LINE('----Error in Message: '|| msgno); DBMS_OUTPUT.PUT_LINE('----Error Number: '||errno); DBMS_OUTPUT.PUT_LINE('----Message Text: '||errmsg); FOR i IN 1..msgcnt LOOP DBMS_OUTPUT.PUT_LINE('--message: ' || i); lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(i, txnid); print_lcr(lcr); END LOOP; END LOOP; END print_errors; /
To run this procedure after you create it, enter the following:
SET SERVEROUTPUT ON SIZE 1000000 EXEC print_errors
The following procedure prints all the LCRs in the error queue for a particular transaction. It calls the print_lcr procedure created in "Create a Procedure That Prints a Specified LCR".
CREATE OR REPLACE PROCEDURE print_transaction(ltxnid IN VARCHAR2) IS i NUMBER; txnid VARCHAR2(30); source VARCHAR2(128); msgno NUMBER; msgcnt NUMBER; errno NUMBER; errmsg VARCHAR2(128); lcr SYS.ANYDATA; BEGIN SELECT LOCAL_TRANSACTION_ID, SOURCE_DATABASE, MESSAGE_NUMBER, MESSAGE_COUNT, ERROR_NUMBER, ERROR_MESSAGE INTO txnid, source, msgno, msgcnt, errno, errmsg FROM DBA_APPLY_ERROR WHERE LOCAL_TRANSACTION_ID = ltxnid; DBMS_OUTPUT.PUT_LINE('----- Local Transaction ID: ' || txnid); DBMS_OUTPUT.PUT_LINE('----- Source Database: ' || source); DBMS_OUTPUT.PUT_LINE('----Error in Message: '|| msgno); DBMS_OUTPUT.PUT_LINE('----Error Number: '||errno); DBMS_OUTPUT.PUT_LINE('----Message Text: '||errmsg); FOR i IN 1..msgcnt LOOP DBMS_OUTPUT.PUT_LINE('--message: ' || i); lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(i, txnid); -- gets the LCR print_lcr(lcr); END LOOP; END print_transaction; /
To run this procedure after you create it, pass it the local transaction identifier of a error transaction. For example, if the local transaction identifier is 1.17.2485, then enter the following:
SET SERVEROUTPUT ON SIZE 1000000 EXEC print_transaction('1.17.2485')
The following sections contain queries that you can run to display information about rules and rule-based transformations:
See Also:
|
Streams rules are rules created using the DBMS_STREAMS_ADM package or the Streams tool in the Oracle Enterprise Manager Console. Streams rules in the rule sets for a Streams client determine the behavior of the Streams client. Streams clients include capture processes, propagations, apply processes, and messaging clients. The rule sets for a Streams client also may contain rules created using the DBMS_RULE_ADM package, and these rules also determine the behavior of the Streams client.
For example, if a rule in the positive rule set for a capture process evaluates to TRUE for DML changes to the hr.employees table, then the capture process captures DML changes to this table. However, if a rule in the negative rule set for a capture process evaluates to TRUE for DML changes to the hr.employees table, then the capture process discards DML changes to this table.
You query the following data dictionary views to display all rules in the rule sets for Streams clients, including Streams rules and rules created using the DBMS_RULE_ADM package:
In addition, these two views display the current rule condition for each rule and whether the rule condition has been modified.
The query in this section displays the following information about all of the rules used by Streams clients in a database:
Run the following query to display this information:
COLUMN STREAMS_NAME HEADING 'Streams|Name' FORMAT A14 COLUMN STREAMS_TYPE HEADING 'Streams|Type' FORMAT A11 COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A12 COLUMN RULE_SET_TYPE HEADING 'Rule Set|Type' FORMAT A8 COLUMN STREAMS_RULE_TYPE HEADING 'Streams|Rule|Level' FORMAT A7 COLUMN SCHEMA_NAME HEADING 'Schema|Name' FORMAT A6 COLUMN OBJECT_NAME HEADING 'Object|Name' FORMAT A11 COLUMN RULE_TYPE HEADING 'Rule|Type' FORMAT A4 SELECT STREAMS_NAME, STREAMS_TYPE, RULE_NAME, RULE_SET_TYPE, STREAMS_RULE_TYPE, SCHEMA_NAME, OBJECT_NAME, RULE_TYPE FROM DBA_STREAMS_RULES;
Your output looks similar to the following:
Streams Streams Streams Rule Rule Set Rule Schema Object Rule Name Type Name Type Level Name Name Type -------------- ----------- ------------ -------- ------- ------ ----------- ---- STRM01_CAPTURE CAPTURE JOBS4 POSITIVE TABLE HR JOBS DML STRM01_CAPTURE CAPTURE JOBS5 POSITIVE TABLE HR JOBS DDL DBS1_TO_DBS2 PROPAGATION HR18 POSITIVE SCHEMA HR DDL DBS1_TO_DBS2 PROPAGATION HR17 POSITIVE SCHEMA HR DML APPLY APPLY HR20 POSITIVE SCHEMA HR DML APPLY APPLY JOB_HISTORY2 NEGATIVE TABLE HR JOB_HISTORY DML OE DEQUEUE RULE$_28 POSITIVE
This output provides the following information about the rules used by Streams clients in the database:
jobs4 and the DDL rule jobs5 are both table rules for the hr.jobs table in the positive rule set for the capture process strm01_capture.hr17 and the DDL rule hr18 are both schema rules for the hr schema in the positive rule set for the propagation dbs1_to_dbs2.hr20 is a schema rule for the hr schema in the positive rule set for the apply process apply.job_history2 is a table rule for the hr schema in the negative rule set for the apply process apply.rule$_28 is a messaging rule in the positive rule set for the messaging client oe.The ALL_STREAMS_RULES and DBA_STREAMS_RULES views also contain information about the rule sets used by a Streams client, the current and original rule condition for Streams rules, whether the rule condition has been changed, the subsetting operation and DML condition for each Streams subset rule, the source database specified for each Streams rule, and information about the message type and message variable for Streams messaging rules.
The following data dictionary views also display Streams rules:
ALL_STREAMS_GLOBAL_RULESDBA_STREAMS_GLOBAL_RULESALL_STREAMS_MESSAGE_RULESDBA_STREAMS_MESSAGE_RULESALL_STREAMS_SCHEMA_RULESDBA_STREAMS_SCHEMA_RULESALL_STREAMS_TABLE_RULESDBA_STREAMS_TABLE_RULESThese views display Streams rules only. They do not display any manual modifications to these rules made by the DBMS_RULE_ADM package, nor do they display rules created using the DBMS_RULE_ADM package. In addition, these views can display the original rule condition for each rule only. They do not display the current rule condition for a rule if the rule condition was modified after the rule was created.
To determine which rules are in a rule set used by a particular Streams client, you can query the DBA_STREAMS_RULES data dictionary view. For example, suppose a database is running an apply process named strm01_apply. The following sections describe how to determine the rules in the positive rule set and negative rule set for this apply process.
The following query displays all of the rules in the positive rule set for an apply process named strm01_apply:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A10 COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A12 COLUMN STREAMS_RULE_TYPE HEADING 'Streams|Rule|Level' FORMAT A7 COLUMN SCHEMA_NAME HEADING 'Schema|Name' FORMAT A6 COLUMN OBJECT_NAME HEADING 'Object|Name' FORMAT A11 COLUMN RULE_TYPE HEADING 'Rule|Type' FORMAT A4 COLUMN SOURCE_DATABASE HEADING 'Source' FORMAT A10 COLUMN INCLUDE_TAGGED_LCR HEADING 'Apply|Tagged|LCRs?' FORMAT A9 SELECT RULE_OWNER, RULE_NAME, STREAMS_RULE_TYPE, SCHEMA_NAME, OBJECT_NAME, RULE_TYPE, SOURCE_DATABASE, INCLUDE_TAGGED_LCR FROM DBA_STREAMS_RULES WHERE STREAMS_NAME = 'STRM01_APPLY' AND RULE_SET_TYPE = 'POSITIVE';
If this query returns any rows, then the apply process applies LCRs containing changes that evaluate to true for the rules.
Your output looks similar to the following:
Streams Apply Rule Rule Schema Object Rule Tagged Rule Owner Name Level Name Name Type Source LCRs? ---------- --------------- ------- ------ ----------- ---- ---------- --------- STRMADMIN HR20 SCHEMA HR DML DBS1.NET NO STRMADMIN HR21 SCHEMA HR DDL DBS1.NET NO
Assuming the rule conditions for the Streams rules returned by this query have not been modified, these results show that the apply process applies LCRs containing DML changes and DDL changes to the hr schema that originated at the dbs1.net database. The rules in the positive rule set that instruct the apply process to apply these LCRs are owned by the strmadmin user and are named hr20 and hr21. Also, the apply process applies an LCR that satisfies one of these rules only if the tag in the LCR is NULL.
If the rule condition for a Streams rule has been modified, then you must check the rule's current rule condition to determine the effect of the rule on a Streams client. Streams rules whose rule condition has been modified have NO for the SAME_RULE_CONDITION column.
The following query displays all of the rules in the negative rule set for an apply process named strm01_apply:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A10 COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A15 COLUMN STREAMS_RULE_TYPE HEADING 'Streams|Rule|Level' FORMAT A7 COLUMN SCHEMA_NAME HEADING 'Schema|Name' FORMAT A6 COLUMN OBJECT_NAME HEADING 'Object|Name' FORMAT A11 COLUMN RULE_TYPE HEADING 'Rule|Type' FORMAT A4 COLUMN SOURCE_DATABASE HEADING 'Source' FORMAT A10 COLUMN INCLUDE_TAGGED_LCR HEADING 'Apply|Tagged|LCRs?' FORMAT A9 SELECT RULE_OWNER, RULE_NAME, STREAMS_RULE_TYPE, SCHEMA_NAME, OBJECT_NAME, RULE_TYPE, SOURCE_DATABASE, INCLUDE_TAGGED_LCR FROM DBA_STREAMS_RULES WHERE STREAMS_NAME = 'APPLY' AND RULE_SET_TYPE = 'NEGATIVE';
If this query returns any rows, then the apply process discards LCRs containing changes that evaluate to true for the rules.
Your output looks similar to the following:
Streams Apply Rule Rule Schema Object Rule Tagged Rule Owner Name Level Name Name Type Source LCRs? ---------- --------------- ------- ------ ----------- ---- ---------- --------- STRMADMIN JOB_HISTORY22 TABLE HR JOB_HISTORY DML DBS1.NET YES STRMADMIN JOB_HISTORY23 TABLE HR JOB_HISTORY DDL DBS1.NET YES
Assuming the rule conditions for the Streams rules returned by this query have not been modified, these results show that the apply process discards LCRs containing DML changes and DDL changes to the hr.job_history table that originated at the dbs1.net database. The rules in the negative rule set that instruct the apply process to discard these LCRs are owned by the strmadmin user and are named job_history22 and job_history23. Also, the apply process discards an LCR that satisfies one of these rules regardless of the value of the tag in the LCR.
If the rule condition for a Streams rule has been modified, then you must check the rule's current rule condition to determine the effect of the rule on a Streams client. Streams rules whose rule condition has been modified have NO for the SAME_RULE_CONDITION column.
If you know the name of a rule, then you can display its rule condition. For example, consider the rule returned by the query in "Displaying the Streams Rules Used by a Specific Streams Client". The name of the rule is hr1, and you can display its condition by running the following query:
SET LONG 8000 SET PAGES 8000 SELECT RULE_CONDITION "Current Rule Condition" FROM DBA_STREAMS_RULES WHERE RULE_NAME = 'HR1' AND RULE_OWNER = 'STRMADMIN';
Your output looks similar to the following:
Current Rule Condition ----------------------------------------------------------------- (:dml.get_object_owner() = 'HR' and :dml.is_null_tag() = 'Y' and :dml.get_source_database_name() = 'DBS1.NET' )
It is possible to modify the rule condition of a Streams rule. These modifications may change the behavior of the Streams clients using the Streams rule. In addition, some modifications may degrade rule evaluation performance.
The following query displays the rule name, the original rule condition, and the current rule condition for each Streams rule whose condition has been modified:
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A12 COLUMN ORIGINAL_RULE_CONDITION HEADING 'Original Rule Condition' FORMAT A33 COLUMN RULE_CONDITION HEADING 'Current Rule Condition' FORMAT A33 SET LONG 8000 SET PAGES 8000 SELECT RULE_NAME, ORIGINAL_RULE_CONDITION, RULE_CONDITION FROM DBA_STREAMS_RULES WHERE SAME_RULE_CONDITION = 'NO';
Your output looks similar to the following:
Rule Name Original Rule Condition Current Rule Condition ------------ --------------------------------- --------------------------------- HR20 ((:dml.get_object_owner() = 'HR') ((:dml.get_object_owner() = 'HR') and :dml.is_null_tag() = 'Y' ) and :dml.is_null_tag() = 'Y' and :dml.get_object_name() != 'JOB_H ISTORY')
In this example, the output shows that the condition of the hr20 rule has been modified. Originally, this schema rule evaluated to true for all changes to the hr schema. The current modified condition for this rule evaluates to true for all changes to the hr schema, except for DML changes to the hr.job_history table.
The following query displays the default evaluation context for each rule set in a database:
COLUMN RULE_SET_OWNER HEADING 'Rule Set|Owner' FORMAT A10 COLUMN RULE_SET_NAME HEADING 'Rule Set Name' FORMAT A20 COLUMN RULE_SET_EVAL_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A12 COLUMN RULE_SET_EVAL_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A30 SELECT RULE_SET_OWNER, RULE_SET_NAME, RULE_SET_EVAL_CONTEXT_OWNER, RULE_SET_EVAL_CONTEXT_NAME FROM DBA_RULE_SETS;
Your output looks similar to the following:
Rule Set Eval Context Owner Rule Set Name Owner Eval Context Name ---------- -------------------- ------------ ------------------------------ STRMADMIN RULESET$_2 SYS STREAMS$_EVALUATION_CONTEXT STRMADMIN STRM02_QUEUE_R STRMADMIN AQ$_STRM02_QUEUE_TABLE_V STRMADMIN APPLY_OE_RS STRMADMIN OE_EVAL_CONTEXT STRMADMIN OE_QUEUE_R STRMADMIN AQ$_OE_QUEUE_TABLE_V STRMADMIN AQ$_1_RE STRMADMIN AQ$_OE_QUEUE_TABLE_V SUPPORT RS SUPPORT EVALCTX OE NOTIFICATION_QUEUE_R OE AQ$_NOTIFICATION_QUEUE_TABLE_V
The following query displays information about the tables used by an evaluation context named evalctx, which is owned by the support user:
COLUMN TABLE_ALIAS HEADING 'Table Alias' FORMAT A20 COLUMN TABLE_NAME HEADING 'Table Name' FORMAT A40 SELECT TABLE_ALIAS, TABLE_NAME FROM DBA_EVALUATION_CONTEXT_TABLES WHERE EVALUATION_CONTEXT_OWNER = 'SUPPORT' AND EVALUATION_CONTEXT_NAME = 'EVALCTX';
Your output looks similar to the following:
Table Alias Table Name -------------------- ---------------------------------------- PROB problems
The following query displays information about the variables used by an evaluation context named evalctx, which is owned by the support user:
COLUMN VARIABLE_NAME HEADING 'Variable Name' FORMAT A15 COLUMN VARIABLE_TYPE HEADING 'Variable Type' FORMAT A15 COLUMN VARIABLE_VALUE_FUNCTION HEADING 'Variable Value|Function' FORMAT A20 COLUMN VARIABLE_METHOD_FUNCTION HEADING 'Variable Method|Function' FORMAT A20 SELECT VARIABLE_NAME, VARIABLE_TYPE, VARIABLE_VALUE_FUNCTION, VARIABLE_METHOD_FUNCTION FROM DBA_EVALUATION_CONTEXT_VARS WHERE EVALUATION_CONTEXT_OWNER = 'SUPPORT' AND EVALUATION_CONTEXT_NAME = 'EVALCTX';
Your output looks similar to the following:
Variable Value Variable Method Variable Name Variable Type Function Function --------------- --------------- -------------------- -------------------- CURRENT_TIME DATE timefunc
The query in this section displays the following information about all of the rules in a rule set:
ADD_RULE procedure when the rule is added to a rule set, then it inherits the evaluation context of the rule setFor example, to display this information for each rule in a rule set named oe_queue_r that is owned by the user strmadmin, run the following query:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A10 COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A20 COLUMN RULE_EVALUATION_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A27 COLUMN RULE_EVALUATION_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A11 SELECT R.RULE_OWNER, R.RULE_NAME, R.RULE_EVALUATION_CONTEXT_NAME, R.RULE_EVALUATION_CONTEXT_OWNER FROM DBA_RULES R, DBA_RULE_SET_RULES RS WHERE RS.RULE_SET_OWNER = 'STRMADMIN' AND RS.RULE_SET_NAME = 'OE_QUEUE_R' AND RS.RULE_NAME = R.RULE_NAME AND RS.RULE_OWNER = R.RULE_OWNER;
Your output looks similar to the following:
Eval Contex Rule Owner Rule Name Eval Context Name Owner ---------- -------------------- --------------------------- ----------- STRMADMIN HR1 STREAMS$_EVALUATION_CONTEXT SYS STRMADMIN APPLY_LCRS STREAMS$_EVALUATION_CONTEXT SYS STRMADMIN OE_QUEUE$3 STRMADMIN APPLY_ACTION
The following query displays the condition for each rule in a rule set named hr_queue_r that is owned by the user strmadmin:
SET LONGCHUNKSIZE 4000 SET LONG 4000 COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A15 COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A15 COLUMN RULE_CONDITION HEADING 'Rule Condition' FORMAT A45 SELECT R.RULE_OWNER, R.RULE_NAME, R.RULE_CONDITION FROM DBA_RULES R, DBA_RULE_SET_RULES RS WHERE RS.RULE_SET_OWNER = 'STRMADMIN' AND RS.RULE_SET_NAME = 'HR_QUEUE_R' AND RS.RULE_NAME = R.RULE_NAME AND RS.RULE_OWNER = R.RULE_OWNER;
Your output looks similar to the following:
Rule Owner Rule Name Rule Condition --------------- --------------- --------------------------------------------- STRMADMIN APPLY_ACTION hr.get_hr_action(tab.user_data) = 'APPLY' STRMADMIN APPLY_LCRS :dml.get_object_owner() = 'HR' AND (:dml.get _object_name() = 'DEPARTMENTS' OR :dml.get_object_name() = 'EMPLOYEES') STRMADMIN HR_QUEUE$3 hr.get_hr_action(tab.user_data) != 'APPLY'
To list each rule in a database that contains a specified pattern in its condition, you can query the DBMS_RULES data dictionary view and use the DBMS_LOB.INSTR function to search for the pattern in the rule conditions. For example, the following query lists each rule that contains the pattern 'HR' in its condition:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A30 COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A30 SELECT RULE_OWNER, RULE_NAME FROM DBA_RULES WHERE DBMS_LOB.INSTR(RULE_CONDITION, 'HR', 1, 1) > 0;
Your output looks similar to the following:
Rule Owner Rule Name ------------------------------ ------------------------------ STRMADMIN DEPARTMENTS4 STRMADMIN DEPARTMENTS5 STRMADMIN DEPARTMENTS6
A rule-based transformation is any modification to an event that results when a rule in a positive rule set evaluates to TRUE. You specify a PL/SQL function that performs the modification.
The following query displays each rule-based transformation specified in a database:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A20 COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A20 COLUMN TRANSFORM_FUNCTION_NAME HEADING 'Transformation Function' FORMAT A30 SELECT RULE_OWNER, RULE_NAME, TRANSFORM_FUNCTION_NAME FROM DBA_STREAMS_TRANSFORM_FUNCTION;
Your output looks similar to the following:
Rule Owner Rule Name Transformation Function -------------------- -------------------- ------------------------------ STRMADMIN DEPARTMENTS17 hr.executive_to_management STRMADMIN DEPARTMENTS18 hr.executive_to_management STRMADMIN DEPARTMENTS19 hr.executive_to_management
You can query the V$RULE_SET_AGGREGATE_STATS dynamic performance view to display statistics for all rule set evaluations since the database instance last started.
The query in this section contains the following information about rule set evaluations:
TRUE rules returned to the rules engine clientsMAYBE rules returned to the rules engine clientsRun the following query to display this information:
COLUMN NAME HEADING 'Name of Statistic' FORMAT A55 COLUMN VALUE HEADING 'Value' FORMAT 999999999 SELECT NAME, VALUE FROM V$RULE_SET_AGGREGATE_STATS;
Your output looks similar to the following:
Name of Statistic Value ------------------------------------------------------- ---------- rule set evaluations (all) 5584 rule set evaluations (first_hit) 5584 rule set evaluations (simple_rules_only) 3675 rule set evaluations (SQL free) 5584 rule set evaluation time (CPU) 179 rule set evaluation time (elapsed) 1053 rule set SQL executions 0 rule set conditions processed 11551 rule set true rules 10 rule set maybe rules 328 rule set user function calls (variable value function) 182 rule set user function calls (variable method function) 12794 rule set user function calls (evaluation function) 3857
|
Note: A centisecond is one-hundredth of a second. Therefore, this output shows 1.79 seconds of CPU time and 10.53 seconds of elapsed time. |
You can query the V$RULE_SET dynamic performance view to display general information about rule set evaluations since the database instance last started. The query in this section contains the following information about each rule set in a database:
TRUE rules returned to the rules engine clients using the rule set since the database instance last startedMAYBE rules returned to the rules engine clients using the rule set since the database instance last startedRun the following query to display this information for each rule set in the database:
COLUMN OWNER HEADING 'Rule Set|Owner' FORMAT A9 COLUMN NAME HEADING 'Rule Set|Name' FORMAT A11 COLUMN EVALUATIONS HEADING 'Total|Evaluations' FORMAT 999999 COLUMN SQL_EXECUTIONS HEADING 'SQL|Executions' FORMAT 999999 COLUMN SQL_FREE_EVALUATIONS HEADING 'SQL Free|Evaluations' FORMAT 999999 COLUMN TRUE_RULES HEADING 'True|Rules' FORMAT 999999 COLUMN MAYBE_RULES HEADING 'Maybe|Rules' FORMAT 999999 SELECT OWNER, NAME, EVALUATIONS, SQL_EXECUTIONS, SQL_FREE_EVALUATIONS, TRUE_RULES, MAYBE_RULES FROM V$RULE_SET;
Your output looks similar to the following:
Rule Set Rule Set Total SQL SQL Free True Maybe Owner Name Evaluations Executions Evaluations Rules Rules --------- ----------- ----------- ---------- ----------- ------- ------- STRMADMIN RULESET$_18 403 0 403 0 200 STRMADMIN RULESET$_9 3454 0 3454 5 64
You can query the V$RULE_SET dynamic performance view to determine the resources used by evaluation of a rule set since the database instance last started. If a rule set was evaluated more than one time since the database instance last started, then some statistics are cumulative, including statistics for the amount of CPU time, evaluation time, and shared memory bytes used.
The query in this section contains the following information about each rule set in a database:
Run the following query to display this information for each rule set in the database:
COLUMN OWNER HEADING 'Rule Set|Owner' FORMAT A15 COLUMN NAME HEADING 'Rule Set Name' FORMAT A15 COLUMN CPU_SECONDS HEADING 'Seconds|of CPU|Time' FORMAT 999999.999 COLUMN ELAPSED_SECONDS HEADING 'Seconds of|Evaluation|Time' FORMAT 999999.999 COLUMN SHARABLE_MEM HEADING 'Bytes|of Shared|Memory' FORMAT 999999999 SELECT OWNER, NAME, (CPU_TIME/100) CPU_SECONDS, (ELAPSED_TIME/100) ELAPSED_SECONDS, SHARABLE_MEM FROM V$RULE_SET;
Your output looks similar to the following:
Seconds Seconds of Bytes Rule Set of CPU Evaluation of Shared Owner Rule Set Name Time Time Memory --------------- --------------- ----------- ----------- ---------- STRMADMIN RULESET$_18 .840 8.550 444497 STRMADMIN RULESET$_9 .700 1.750 444496
You can query the V$RULE dynamic performance view to display evaluation statistics for a particular rule since the database instance last started. The query in this section contains the following information about each rule set in a database:
TRUE since the database instance last startedMAYBE since the database instance last startedFor example, run the following query to display this information for the locations25 rule in the strmadmin schema:
COLUMN TRUE_RULES HEADING 'True Evaluations' FORMAT 999999 COLUMN MAYBE_RULES HEADING 'Maybe Evaluations' FORMAT 999999 COLUMN SQL_EVALUATIONS HEADING 'SQL Evaluations' FORMAT 999999 SELECT TRUE_HITS, MAYBE_HITS, SQL_EVALUATIONS FROM V$RULE WHERE RULE_OWNER = 'STRMADMIN' AND RULE_NAME = 'LOCATIONS25';
The queries in the following sections show Streams compatibility for tables in the local database:
A database object is not compatible with Streams if a capture process cannot capture changes to the object. The query in this section displays the following information about objects that are not compatible with Streams:
AUTO_FILTERED column)If capture processes automatically filter out changes to an object, then the rules sets used by the capture processes do not need to filter them out explicitly. For example, capture processes automatically filter out changes to materialized view logs. However, if changes to incompatible objects are not filtered out automatically, then the rule sets used by each capture process must filter them out to avoid errors.
For example, if the rule sets for a capture process instruct the capture process to capture all of the changes made to a certain schema, but the query in this section shows that one object in this schema is not compatible with Streams, and that changes to the object are not filtered out automatically, then you can add a rule to the negative rule set for the capture process to filter out changes to the incompatible object.
The AUTO_FILTERED column only pertains to capture processes. Apply processes do not automatically filter out LCRs that encapsulate changes to objects that are not compatible with Streams, even if the AUTO_FILTERED column is YES for the object. Such changes may result in apply errors if they are dequeued by an apply process.
Run the following query to list the objects in the local database that are not compatible with Streams:
COLUMN OWNER HEADING 'Object|Owner' FORMAT A8 COLUMN TABLE_NAME HEADING 'Object Name' FORMAT A30 COLUMN REASON HEADING 'Reason' FORMAT A30 COLUMN AUTO_FILTERED HEADING 'Auto|Filtered?' FORMAT A9 SELECT OWNER, TABLE_NAME, REASON, AUTO_FILTERED FROM DBA_STREAMS_UNSUPPORTED;
Your output looks similar to the following:
Object Auto Owner Object Name Reason Filtered? -------- ------------------------------ ------------------------------ --------- HR MLOG$_COUNTRIES materialized view log YES HR MLOG$_DEPARTMENTS materialized view log YES HR MLOG$_EMPLOYEES materialized view log YES HR MLOG$_JOBS materialized view log YES HR MLOG$_JOB_HISTORY materialized view log YES HR MLOG$_LOCATIONS materialized view log YES HR MLOG$_REGIONS materialized view log YES IX AQ$_ORDERS_QUEUETABLE_G IOT with overflow NO IX AQ$_ORDERS_QUEUETABLE_H unsupported column exists NO IX AQ$_ORDERS_QUEUETABLE_I unsupported column exists NO IX AQ$_ORDERS_QUEUETABLE_S AQ queue table NO IX AQ$_ORDERS_QUEUETABLE_T AQ queue table NO IX ORDERS_QUEUETABLE column with user-defined type NO OE CATEGORIES_TAB column with user-defined type NO OE CUSTOMERS column with user-defined type NO OE PRODUCT_REF_LIST_NESTEDTAB column with user-defined type NO OE SUBCATEGORY_REF_LIST_NESTEDTAB column with user-defined type NO OE WAREHOUSES column with user-defined type NO PM ONLINE_MEDIA column with user-defined type NO PM PRINT_MEDIA column with user-defined type NO PM TEXTDOCS_NESTEDTAB column with user-defined type NO SH MVIEW$_EXCEPTIONS unsupported column exists NO SH SALES_TRANSACTIONS_EXT external table NO
Notice that the AUTO_FILTERED column is YES for the oe.mlog$_orders materialized view log. Each capture process automatically filters out changes to this object, even if the rules sets for a capture process instruct the capture process to capture changes to the object.
Because the AUTO_FILTERED column is NO for the other objects listed in the example output, capture processes do not filter out changes to these objects automatically. If a capture process attempts to process LCRs for these unsupported objects, then the capture process raises an error. However, you can avoid these errors by configuring rules sets that instruct the capture process not to capture changes to these unsupported objects.
See Also:
|
The query in this section displays the following information about database objects that have become compatible with Streams in a recent release of Oracle:
Run the following query to display this information for the local database:
COLUMN OWNER HEADING 'Owner' FORMAT A10 COLUMN TABLE_NAME HEADING 'Object Name' FORMAT A20 COLUMN REASON HEADING 'Reason' FORMAT A30 COLUMN COMPATIBLE HEADING 'Compatible' FORMAT A10 SELECT OWNER, TABLE_NAME, REASON, COMPATIBLE FROM DBA_STREAMS_NEWLY_SUPPORTED;
Your output looks similar to the following:
Owner Object Name Reason Compatible ---------- -------------------- ------------------------------ ---------- HR COUNTRIES IOT 10.1 OUTLN OL$ unsupported column exists 10.1 SH CAL_MONTH_SALES_MV unsupported column exists 10.1 SH FWEEK_PSCAT_SALES_MV unsupported column exists 10.1 SH PLAN_TABLE unsupported column exists 10.1
The COMPATIBLE column shows the minimum database compatibility for Streams to support the object. If the local database compatibility is equal to or higher than the value in the COMPATIBLE column for an object, then capture processes and apply processes can process changes to the object successfully. You control the compatibility of an Oracle database using the COMPATIBLE initialization parameter.
If your Streams environment includes databases that are running different versions of the Oracle database, then you can configure rules that use the GET_COMPATIBLE member function for LCRs to filter out LCRs that are not compatible with particular databases. These rules may be added to the rule sets of capture processes, propagations, and apply processes to filter out incompatible LCRs wherever necessary in a stream.
See Also:
|
You can use the Statspack package to monitor performance statistics related to Streams. The most current instructions and information on installing and using the Statspack package are contained in the spdoc.txt file installed with your database. Refer to that file for Statspack information. On Unix systems, the file is located in the ORACLE_HOME/rdbms/admin directory. On Windows systems, the file is located in the ORACLE_HOME\rdbms\admin directory.
|
 Copyright © 2002, 2003 Oracle Corporation All Rights Reserved. |
|