Edit D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\catlsby.sql
Rem Rem $Header: rdbms/admin/catlsby.sql /main/97 2009/05/14 11:03:23 svivian Exp $ Rem Rem catlsby.sql Rem Rem Copyright (c) 2000, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem catlsby.sql - Logical Standby tables and views Rem Rem DESCRIPTION Rem This file implements the following: Rem Tables: Rem logstdby$parameters Rem logstdby$events Rem logstdby$apply_progress Rem logstdby$apply_milestone Rem logstdby$event_options Rem logstdby$scn Rem logstdby$skip_transaction Rem logstdby$skip Rem logstdby$skip_support Rem logstdby$eds_tables Rem Rem NOTES Rem Must be run when connected to SYS or INTERNAL Rem Rem MODIFIED (MM/DD/YY) Rem svivian 04/22/09 - refine dba_logstdby_eds_supported Rem dvoss 04/16/09 - skip indexes belong in sysaux Rem dvoss 04/08/09 - bug 8235260 - skip indexes Rem svivian 03/26/09 - add EDS infrastructure Rem jkundu 02/17/09 - logstdby$events.spare1 records start_scn of the Rem txn (bug 8260837) Rem dvoss 02/05/09 - logstdby$events.event_time should be not null Rem dvoss 02/04/09 - add indexes to logstdby$events Rem preilly 11/14/08 - Bug 7630082: Check for SecureFiles with Dedup option Rem bpwang 09/19/08 - 11.2 supports SecureFiles Rem rlong 09/25/08 - Rem nkgopal 08/11/08 - Bug 6830207: Add Alter Database Link changes Rem rlong 08/07/08 - Rem myalavar 07/07/08 - Rem svivian 06/10/08 - bug 6487578: add JAVA to logstdby$skip_support Rem jkundu 04/23/08 - dba_logstdby_log update for APPLIED column Rem myalavar 04/08/08 - add orddata(bug 6759944) to logstdby skip Rem rmacnico 03/25/08 - Bug 2931832: support ODCI Rem svivian 03/19/08 - add blocks, block_size to dba_logstdby_log Rem rmacnico 02/25/08 - Add 11.2 redo compat to supported view Rem tchorma 02/08/08 - Remove compression from unsupported views Rem dsemler 02/06/08 - Add APPQOSSYS user to exclusion list Rem rmacnico 11/26/07 - bug 6528315: support edition in 11.2 Rem rmacnico 09/14/07 - bug 6406689: unsupported DMLs Rem ineall 07/23/07 - Bug 5889516: Disqualify function based index in Rem dba_logstdby_not_unique Rem rmacnico 06/14/07 - lrg 3015662: add logstdby$ tabs to noexp$ Rem rmacnico 05/24/07 - bug 5666482: map primary scn Rem rmacnico 05/01/07 - bug 6019939: flashback archive support Rem sslim 03/27/07 - Bug 5947235: SBP and Processed SCNs in history Rem table Rem rmacnico 03/26/07 - bug 5496852: validate skip on user ddls Rem rmacnico 04/11/07 - lrg 2916540: iot overflow tables Rem rmacnico 04/04/07 - bug 5971328: increase col width for plsql skip Rem rmacnico 03/12/07 - bug 5906232: virtual column primary key Rem rmacnico 02/05/07 - bug 5726264: xml store as OR marked sys maint Rem rmacnico 01/24/07 - bug 5790970: xml store as csx (binary xml) Rem jmzhang 12/20/06 - 5700499: skip table with securefile column Rem abrown 10/02/06 - Hierarchically enabled XML tables unsupported in Rem V11 Rem dvoss 10/12/06 - skip XS$NULL Rem rmacnico 09/11/06 - bug 5472731: system, reference partitioned tables Rem rmacnico 09/01/06 - lrg 2531243: required synonym Rem rmacnico 08/17/06 - 5172550: include AQ in unsupported view Rem dvoss 08/01/06 - add xml typed table support Rem mtao 07/07/06 - proj 17789: dba_logstdby_log dont show dummy log Rem dkapoor 06/16/06 - add ORACLE_OCM in LOGSTDBY30498SKIP_SUPPORT Rem rmacnico 04/20/06 - Add kernal PL/SQL support Rem preilly 05/23/06 - Fix UNSUPPORTED view for schema based XML CLOB Rem smangala 05/22/06 - project17789: extend parameters table Rem ineall 03/21/06 - 4601343: Modify view logstdby_support to Rem avoid ORA-01425 Rem rmacnico 03/02/06 - 3584308: handle change in redo compat in lsby Rem rmacnico 03/03/06 - 5074345: fix cdef$ flags check Rem rmacnico 11/08/05 - cleanup skipped schemas (dglsms) Rem sslim 05/26/05 - Reveal corruption state in dba_logstdby_log Rem rmacnico 05/19/05 - Update skip_support categories Rem jmzhang 03/23/05 - change default ts for parameter table Rem jmzhang 03/29/05 - update dba_logstdby_unsupported Rem jmzhang 08/26/04 - remove logstdby_status Rem - remove logstdby_thread Rem jmzhang 08/17/04 - add logstdby_status Rem add logstdby_thread Rem clei 06/10/04 - disallow encrypted columns Rem ajadams 06/15/04 - add index to logstdby events table Rem rgupta 04/23/04 - create tables in SYSAUX tablespace Rem ajadams 05/13/04 - add logstdby_transaction Rem jmzhang 05/05/04 - add timestamp to apply_milestone Rem jnesheiw 03/11/04 - fix LOGSTDBY_PROGRESS view to show correct Rem thread# for RAC Rem mcusson 01/15/04 - LogMiner 10g IOT support Rem jnesheiw 12/18/03 - Re-enable partition check Rem raguzman 11/12/03 - use dba_server_registry not dba_registry Rem raguzman 10/29/03 - add list of schema names to skip Rem raguzman 09/24/03 - fix bit check for table_compression Rem jmzhang 09/11/03 - fix newest_scn in dba_logstdby_progress Rem raguzman 08/28/03 - add column to logstdby_support to support new Rem view logstdby_unsupported_tables for GUI Rem jnesheiw 08/28/03 - DBA_LOGSTDBY_PARAMETERS only displays type < 2 Rem jmzhang 07/28/03 - fix logstdby_support by adding s.ts# Rem gkulkarn 07/09/03 - IOT with mapping table is supported Rem jnesheiw 05/19/03 - increase objname size in logstdby$scn Rem raguzman 05/27/03 - support view are missing object tables Rem raguzman 05/31/03 - real time apply and views Rem smangala 05/05/03 - fix bug#2691312: ignore gaps for newest_scn Rem narora 03/19/03 - bug 2842797: default value of fetchlwm_scn Rem narora 01/13/03 - add fetchlwm_scn to apply_milestone Rem raguzman 12/19/02 - add logstdby_support internal use view Rem sslim 12/02/02 - lrg 1112873: should not drop tables Rem raguzman 11/18/02 - Simply supported queries Rem rguzman 07/19/02 - update views for data type support Rem rguzman 07/19/02 - do not drop tables, needed for upgrades Rem rguzman 10/25/02 - Fix PARAMETERS view and UNSUPPORTED attributes Rem jmzhang 10/10/02 - modify the comments of logstdby$parameters Rem rguzman 10/11/02 - Attributes column for DBA_LOGSTDBY_UNSUPPORTED Rem jmzhang 09/23/02 - Update system.logstdby$scn Rem rguzman 07/07/02 - DBA_LOGSTDBY_PROGRESS must work on RAC Rem rguzman 10/01/02 - skip using like feature Rem sslim 09/26/02 - Log Stream History Table Rem jmzhang 08/12/02 - UPdate DBA_LOGSTDBY_PROGRESS Rem jmzhang 08/12/02 - Update DBA_LOGSTDBY_LOG Rem gviswana 01/29/02 - CREATE OR REPLACE SYNONYM Rem narora 01/17/02 - milestone.spare1 = oldest scn, Rem - spare2=primary syncpoint scn Rem rguzman 01/24/02 - Modify UNSUPPORTED view, no ADTs Rem cfreiwal 11/14/01 - move logstby views to catlsby.sql Rem rguzman 10/12/01 - New columns for logstdby$paramters. Rem narora 09/21/01 - remove logstdby_coordinator/slave Rem dcassine 08/27/01 - Rem rguzman 09/12/01 - PROGRESS view to report better progress Rem dcassine 08/27/01 - LOGSTDBY$APPLY_MILESTONE.PROCESSED_SCN Rem jnesheiw 08/02/01 - skip_transaction spare1 name change. Rem rguzman 05/18/01 - Fix skip default. Rem rguzman 05/17/01 - No Long/Lob support for Alpha kit. Rem sslim 05/11/01 - Drop tables before creating them Rem jdavison 10/12/00 - Change varchar sizes to 2000. Rem narora 08/01/00 - make apply progress a partitioned table Rem rguzman 08/11/00 - Views: synonyms, snapshot logs & functional index Rem narora 06/20/00 - grant select on v$logstdby_coordinator, Rem - v$logstdby_apply Rem rguzman 05/26/00 - Add views Rem rguzman 04/11/00 - Created Rem -- This is needed so that SYS can later grant select_catalog to the views. grant select any table to sys with admin option / create table system.logstdby$parameters ( name varchar2(30), /* The name of the parameter */ value varchar2(2000), /* The value of the parameter */ type number, /* null = internal, 1 = persistent, 2 = sessional */ scn number, /* null or meaningful scn */ spare1 number, /* Future expansion */ spare2 number, /* Future expansion */ spare3 varchar2(2000) /* Future expansion */ ) tablespace SYSTEM / create table system.logstdby$events ( event_time timestamp not null, /* The timetamp the event took effect */ current_scn number, /* The change vector SCN for the change */ commit_scn number, /* SCN of commit record for failed transaction */ xidusn number, /* Trans id component of a failed transaction */ xidslt number, /* Trans id component of a failed transaction */ xidsqn number, /* Trans id component of a failed transaction */ errval number, /* Error number */ event varchar2(2000), /* first 2000 characters of statement */ full_event clob, /* The complete statement */ error varchar2(2000), /* error text associated with failure */ spare1 number, /* 11.2 (start_scn of the failed txn) */ spare2 number, /* Future expansion */ spare3 varchar2(2000) /* Future expansion */ ) LOB (full_event) STORE AS (TABLESPACE SYSAUX CACHE PCTVERSION 0 CHUNK 16k STORAGE (INITIAL 16K NEXT 16K)) TABLESPACE SYSAUX LOGGING / create index system.logstdby$events_ind on system.logstdby$events (event_time asc) tablespace SYSAUX LOGGING; create index system.logstdby$events_ind_scn on system.logstdby$events (commit_scn asc) tablespace SYSAUX LOGGING; create index system.logstdby$events_ind_xid on system.logstdby$events (xidusn, xidslt, xidsqn asc) tablespace SYSAUX LOGGING; -- Turns off partition check -- alter session set events '14524 trace name context forever, level 1'; create table system.logstdby$apply_progress ( xidusn number, /* Trans id component of an applied transaction */ xidslt number, /* Trans id component of an applied transaction */ xidsqn number, /* Trans id component of an applied transaction */ commit_scn number, /* SCN of commit record for applied transaction */ commit_time date, /* The timestamp corresponding to the commit scn */ spare1 number, /* Future expansion */ spare2 number, /* Future expansion */ spare3 varchar2(2000) /* Future expansion */ ) tablespace SYSAUX partition by range (commit_scn) (partition P0 values less than (0)) / -- Turns on partition check -- alter session set events '14524 trace name context off'; create table system.logstdby$apply_milestone ( session_id number not null, /* Log miner session id */ commit_scn number not null, /* low-water mark */ commit_time date, /* low-water mark time*/ synch_scn number not null, /* Synch-point SCN. */ epoch number not null, /* Incarnation number for apply engine */ processed_scn number not null, /* all comp txn<processed_scn are applied */ processed_time date, /*timestamp corresponding to process_scn */ fetchlwm_scn number default(0) not null, /* maximum SCN ever fetched */ spare1 number, /* oldest_scn */ spare2 number, /* primary syncpoint scn */ spare3 varchar2(2000) /* Future expansion */ ) tablespace SYSAUX / Rem Logical Instantiation, beginning scn for each table. create table system.logstdby$scn ( obj# number, objname varchar2(4000), schema varchar2(30), type varchar2(20), scn number, spare1 number, /* Future expansion */ spare2 number, /* Future expansion */ spare3 varchar2(2000) /* Future expansion */ ) tablespace SYSAUX / Rem Logical flashback scn for equivalent primary scn create table system.logstdby$flashback_scn ( primary_scn number not null primary key, primary_time date, standby_scn number, standby_time date, spare1 number, spare2 number, spare3 date ) tablespace SYSAUX / Rem TODO remove obsolete table create table system.logstdby$plsql ( session_id number, /* Id of session issuing the command */ start_finish number, /* Boolean, 0 = 1st record, 1 = last record */ call_text clob, /* Text of call to pl/sql routine. */ spare1 number, /* Future expansion */ spare2 number, /* Future expansion */ spare3 varchar2(2000) /* Future expansion */ ) tablespace SYSAUX / create table system.logstdby$skip_transaction ( xidusn number, /* Trans id component of an applied transaction */ xidslt number, /* Trans id component of an applied transaction */ xidsqn number, /* Trans id component of an applied transaction */ active number, /* Boolean to indicate current or active */ commit_scn number, /* SCN at which tx commited at */ spare2 number, /* Future expansion */ spare3 varchar2(2000) /* Future expansion */ ) tablespace SYSAUX / create table system.logstdby$skip ( error number, /* Should statement or error be skipped */ statement_opt varchar2(30), /* name from audit_actions or */ /* logstdby$skip_support */ schema varchar2(30), /* schema name for object being skipped */ name varchar2(65), /* name of object or pack.proc skipped */ use_like number, /* 0 = exact match, 1 = like, 2 = like with escape */ esc varchar2(1), /* Escape character if using like */ proc varchar2(98), /* schema.package.proc to call for skip */ active number, /* not used */ spare1 number, /* 1 if internally generated, null if user */ spare2 number, /* Future expansion */ spare3 varchar2(2000) /* Future expansion */ ) tablespace SYSAUX / create index system.logstdby$skip_idx1 on system.logstdby$skip (use_like, schema, name) tablespace SYSAUX LOGGING; create index system.logstdby$skip_idx2 on system.logstdby$skip (statement_opt) tablespace SYSAUX LOGGING; Rem Statement auditting options for objects encoded here for skip support create table system.logstdby$skip_support ( action number not null, /* number as seen in sys.audit_actions */ /* reserving actions 0 & -1 for internal skip schema list */ name varchar2(30) not null, /* action to skip or schema */ reg smallint, /* from dbms_registry */ spare1 number, /* Future expansion */ spare2 number, /* Future expansion */ spare3 varchar2(2000) /* Future expansion */ ) tablespace SYSAUX / /* previously we dropped and recreated to control contents */ delete from system.logstdby$skip_support; insert into system.logstdby$skip_support /* INSERT */ (action, name, reg) values (2, 'DML', 0); insert into system.logstdby$skip_support /* UPDATE */ (action, name, reg) values (6, 'DML', 0); insert into system.logstdby$skip_support /* DELETE */ (action, name, reg) values (7, 'DML', 0); /* SCHEMA_DDL & NONSCHEMA_DDL determined by null/non-null owner and name */ insert into system.logstdby$skip_support /* CREATE CLUSTER */ (action, name, reg) values (4, 'CLUSTER', 0); insert into system.logstdby$skip_support /* ALTER CLUSTER */ (action, name, reg) values (5, 'CLUSTER', 0); insert into system.logstdby$skip_support /* DROP CLUSTER */ (action, name, reg) values (8, 'CLUSTER', 0); insert into system.logstdby$skip_support /* TRUNCATE CLUSTER */ (action, name, reg) values (86, 'CLUSTER', 0); insert into system.logstdby$skip_support /* CREATE CONTEXT */ (action, name, reg) values (177, 'CONTEXT', 0); insert into system.logstdby$skip_support /* DROP CONTEXT */ (action, name, reg) values (178, 'CONTEXT', 0); insert into system.logstdby$skip_support /* CREATE DATABASE LINK */ (action, name, reg) values (32, 'DATABASE LINK', 0); insert into system.logstdby$skip_support /* DROP DATABASE LINK */ (action, name, reg) values (33, 'DATABASE LINK', 0); insert into system.logstdby$skip_support /* ALTER DATABASE LINK */ (action, name, reg) values (225, 'DATABASE LINK', 0); insert into system.logstdby$skip_support /* CREATE DIMENSION */ (action, name, reg) values (174, 'DIMENSION', 0); insert into system.logstdby$skip_support /* ALTER DIMENSION */ (action, name, reg) values (175, 'DIMENSION', 0); insert into system.logstdby$skip_support /* DROP DIMENSION */ (action, name, reg) values (176, 'DIMENSION', 0); insert into system.logstdby$skip_support /* CREATE DIRECTORY */ (action, name, reg) values (157, 'DIRECTORY', 0); insert into system.logstdby$skip_support /* DROP DIRECTORY */ (action, name, reg) values (158, 'DIRECTORY', 0); insert into system.logstdby$skip_support /* CREATE INDEX */ (action, name, reg) values (9, 'INDEX', 0); insert into system.logstdby$skip_support /* ALTER INDEX */ (action, name, reg) values (11, 'INDEX', 0); insert into system.logstdby$skip_support /* DROP INDEX */ (action, name, reg) values (10, 'INDEX', 0); insert into system.logstdby$skip_support /* CREATE PROCEDURE */ (action, name, reg) values (24, 'PROCEDURE', 0); insert into system.logstdby$skip_support /* ALTER PROCEDURE */ (action, name, reg) values (25, 'PROCEDURE', 0); insert into system.logstdby$skip_support /* DROP PROCEDURE */ (action, name, reg) values (68, 'PROCEDURE', 0); insert into system.logstdby$skip_support /* CREATE FUNCTION */ (action, name, reg) values (91, 'PROCEDURE', 0); insert into system.logstdby$skip_support /* ALTER FUNCTION */ (action, name, reg) values (92, 'PROCEDURE', 0); insert into system.logstdby$skip_support /* DROP FUNCTION */ (action, name, reg) values (93, 'PROCEDURE', 0); insert into system.logstdby$skip_support /* CREATE PACKAGE */ (action, name, reg) values (94, 'PROCEDURE', 0); insert into system.logstdby$skip_support /* ALTER PACKAGE */ (action, name, reg) values (95, 'PROCEDURE', 0); insert into system.logstdby$skip_support /* DROP PACKAGE */ (action, name, reg) values (96, 'PROCEDURE', 0); insert into system.logstdby$skip_support /* CREATE PACKAGE BODY */ (action, name, reg) values (97, 'PROCEDURE', 0); insert into system.logstdby$skip_support /* ALTER PACKAGE BODY */ (action, name, reg) values (98, 'PROCEDURE', 0); insert into system.logstdby$skip_support /* DROP PACKAGE BODY */ (action, name, reg) values (99, 'PROCEDURE', 0); insert into system.logstdby$skip_support /* CREATE LIBRARY */ (action, name, reg) values (159, 'PROCEDURE', 0); insert into system.logstdby$skip_support /* ALTER LIBRARY */ (action, name, reg) values (196, 'PROCEDURE', 0); insert into system.logstdby$skip_support /* DROP LIBRARY */ (action, name, reg) values (84, 'PROCEDURE', 0); insert into system.logstdby$skip_support /* CREATE PROFILE */ (action, name, reg) values (65, 'PROFILE', 0); insert into system.logstdby$skip_support /* ALTER PROFILE */ (action, name, reg) values (67, 'PROFILE', 0); insert into system.logstdby$skip_support /* DROP PROFILE */ (action, name, reg) values (66, 'PROFILE', 0); insert into system.logstdby$skip_support /* CREATE ROLE */ (action, name, reg) values (52, 'ROLE', 0); insert into system.logstdby$skip_support /* ALTER ROLE */ (action, name, reg) values (79, 'ROLE', 0); insert into system.logstdby$skip_support /* DROP ROLE */ (action, name, reg) values (54, 'ROLE', 0); insert into system.logstdby$skip_support /* SET ROLE */ (action, name, reg) values (55, 'ROLE', 0); insert into system.logstdby$skip_support /* CREATE ROLLBACK SEGMENT */ (action, name, reg) values (36, 'ROLLBACK STATEMENT', 0); insert into system.logstdby$skip_support /* ALTER ROLLBACK SEGMENT */ (action, name, reg) values (37, 'ROLLBACK STATEMENT', 0); insert into system.logstdby$skip_support /* DROP ROLLBACK SEGMENT */ (action, name, reg) values (38, 'ROLLBACK STATEMENT', 0); insert into system.logstdby$skip_support /* CREATE SEQUENCE */ (action, name, reg) values (13, 'SEQUENCE', 0); insert into system.logstdby$skip_support /* ALTER SEQUENCE */ (action, name, reg) values (14, 'SEQUENCE', 0); insert into system.logstdby$skip_support /* DROP SEQUENCE */ (action, name, reg) values (16, 'SEQUENCE', 0); insert into system.logstdby$skip_support /* CREATE SYNONYM */ (action, name, reg) values (19, 'SYNONYM', 0); insert into system.logstdby$skip_support /* DROP SYNONYM */ (action, name, reg) values (20, 'SYNONYM', 0); insert into system.logstdby$skip_support /* CREATE PUBLIC SYNONYM */ (action, name, reg) values (110, 'SYNONYM', 0); insert into system.logstdby$skip_support /* DROP PUBLIC SYNONYM */ (action, name, reg) values (111, 'SYNONYM', 0); insert into system.logstdby$skip_support /* CREATE TABLE */ (action, name, reg) values (1, 'TABLE', 0); insert into system.logstdby$skip_support /* ALTER TABLE */ (action, name, reg) values (15, 'TABLE', 0); insert into system.logstdby$skip_support /* DROP TABLE */ (action, name, reg) values (12, 'TABLE', 0); insert into system.logstdby$skip_support /* TRUNCATE TABLE */ (action, name, reg) values (85, 'TABLE', 0); /* COMMENT ON TABLE included */ insert into system.logstdby$skip_support /* CREATE TABLESPACE */ (action, name, reg) values (39, 'TABLESPACE', 0); insert into system.logstdby$skip_support /* ALTER TABLESPACE */ (action, name, reg) values (40, 'TABLESPACE', 0); insert into system.logstdby$skip_support /* DROP TABLESPACE */ (action, name, reg) values (41, 'TABLESPACE', 0); insert into system.logstdby$skip_support /* CREATE TRIGGER */ (action, name, reg) values (59, 'TRIGGER', 0); insert into system.logstdby$skip_support /* ALTER TRIGGER */ (action, name, reg) values (60, 'TRIGGER', 0); insert into system.logstdby$skip_support /* DROP TRIGGER */ (action, name, reg) values (61, 'TRIGGER', 0); insert into system.logstdby$skip_support /* ENABLE TRIGGER */ (action, name, reg) values (118, 'TRIGGER', 0); insert into system.logstdby$skip_support /* DISABLE TRIGGER */ (action, name, reg) values (119, 'TRIGGER', 0); insert into system.logstdby$skip_support /* ENABLE ALL TRIGGERS */ (action, name, reg) values (120, 'TRIGGER', 0); insert into system.logstdby$skip_support /* DISABLE ALL TRIGGERS */ (action, name, reg) values (121, 'TRIGGER', 0); insert into system.logstdby$skip_support /* CREATE TYPE */ (action, name, reg) values (77, 'TYPE', 0); insert into system.logstdby$skip_support /* DROP TYPE */ (action, name, reg) values (78, 'TYPE', 0); insert into system.logstdby$skip_support /* ALTER TYPE */ (action, name, reg) values (80, 'TYPE', 0); insert into system.logstdby$skip_support /* CREATE TYPE BODY */ (action, name, reg) values (81, 'TYPE', 0); insert into system.logstdby$skip_support /* ALTER TYPE BODY */ (action, name, reg) values (82, 'TYPE', 0); insert into system.logstdby$skip_support /* DROP TYPE BODY */ (action, name, reg) values (83, 'TYPE', 0); insert into system.logstdby$skip_support /* CREATE USER */ (action, name, reg) values (51, 'USER', 0); insert into system.logstdby$skip_support /* ALTER USER */ (action, name, reg) values (43, 'USER', 0); insert into system.logstdby$skip_support /* DROP USER */ (action, name, reg) values (53, 'USER', 0); insert into system.logstdby$skip_support /* CREATE VIEW */ (action, name, reg) values (21, 'VIEW', 0); insert into system.logstdby$skip_support /* DROP VIEW */ (action, name, reg) values (22, 'VIEW', 0); insert into system.logstdby$skip_support /* GRANT */ (action, name, reg) values (17, 'GRANT', 0); insert into system.logstdby$skip_support /* REVOKE */ (action, name, reg) values (18, 'REVOKE', 0); insert into system.logstdby$skip_support /* AUDIT */ (action, name, reg) values (30, 'AUDIT', 0); insert into system.logstdby$skip_support /* NOAUDIT */ (action, name, reg) values (31, 'AUDIT', 0); insert into system.logstdby$skip_support /* CREATE EDITION */ (action, name, reg) values (212, 'EDITION', 0); insert into system.logstdby$skip_support /* ALTER EDITION */ (action, name, reg) values (213, 'EDITION', 0); insert into system.logstdby$skip_support /* DROP EDITION */ (action, name, reg) values (214, 'EDITION', 0); insert into system.logstdby$skip_support /* CREATE JAVA */ (action, name, reg) values (160, 'JAVA', 0); insert into system.logstdby$skip_support /* ALTER JAVA */ (action, name, reg) values (161, 'JAVA', 0); insert into system.logstdby$skip_support /* DROP JAVA */ (action, name, reg) values (162, 'JAVA', 0); -- These placeholders do not correspond to valid octdef's insert into system.logstdby$skip_support /* EXECUTE PROCEDURE */ (action, name, reg) values (1000000, 'PL/SQL', 0); insert into system.logstdby$skip_support /* DDL in EXECUTE PROCEDURE */ (action, name, reg) values (1000001, 'PL/SQL_DDL', 0); commit; Rem Rem List of schemas that ship with database Rem This list should match select username from dba_users on a shiphome. Rem action = 0 means we will skip acitivity in that schema Rem action = -1 means we will not skip acitivity in that schema Rem reg = 0 means we already know about this internal schema Rem reg = 1 means schema was registered by dbms_registry.loading Rem insert into system.logstdby$skip_support /* Sample Schema */ (action, name, reg) values (-1, 'ADAMS', 0); insert into system.logstdby$skip_support /* HTTP access to XDB */ (action, name, reg) values (0, 'ANONYMOUS', 0); insert into system.logstdby$skip_support /* QOS system user */ (action, name, reg) values (0, 'APPQOSSYS', 0); insert into system.logstdby$skip_support /* Business Intelligence */ (action, name, reg) values (0, 'BI', 0); insert into system.logstdby$skip_support /* Sample Schema */ (action, name, reg) values (-1, 'BLAKE', 0); insert into system.logstdby$skip_support /* Sample Schema */ (action, name, reg) values (-1, 'CLARK', 0); insert into system.logstdby$skip_support /* Text */ (action, name, reg) values (0, 'CTXSYS', 0); insert into system.logstdby$skip_support /* Directory Integration Platform */ (action, name, reg) values (0, 'DIP', 0); insert into system.logstdby$skip_support /* SNMP agent for OEM */ (action, name, reg) values (0, 'DBSNMP', 0); insert into system.logstdby$skip_support /* Data Mining */ (action, name, reg) values (0, 'DMSYS', 0); insert into system.logstdby$skip_support /* External ODCI System User */ (action, name, reg) values (0, 'EXDSYS', 0); insert into system.logstdby$skip_support /* Expression Filter */ (action, name, reg) values (0, 'EXFSYS', 0); insert into system.logstdby$skip_support /* Sample Schema */ (action, name, reg) values (-1, 'HR', 0); insert into system.logstdby$skip_support /* Sample Schema */ (action, name, reg) values (-1, 'IX', 0); insert into system.logstdby$skip_support /* Sample Schema */ (action, name, reg) values (-1, 'JONES', 0); insert into system.logstdby$skip_support /* Label Security */ (action, name, reg) values (0, 'LBACSYS', 0); insert into system.logstdby$skip_support /* Spatial user data */ (action, name, reg) values (-1, 'MDDATA', 0); insert into system.logstdby$skip_support /* Spatial */ (action, name, reg) values (0, 'MDSYS', 0); insert into system.logstdby$skip_support /* OEM Database Control */ (action, name, reg) values (0, 'MGMT_VIEW', 0); insert into system.logstdby$skip_support /* MS Transaction Server */ (action, name, reg) values (0, 'MTSSYS', 0); insert into system.logstdby$skip_support /* Data Mining */ (action, name, reg) values (0, 'ODM', 0); insert into system.logstdby$skip_support /* Data Mining Repository */ (action, name, reg) values (0, 'ODM_MTR', 0); insert into system.logstdby$skip_support /* Sample Schema */ (action, name, reg) values (-1, 'OE', 0); insert into system.logstdby$skip_support /* OLAP catalogs */ (action, name, reg) values (0, 'OLAPSYS', 0); insert into system.logstdby$skip_support /* Oracle Configuration Manager User*/ (action, name, reg) values (0, 'ORACLE_OCM', 0); insert into system.logstdby$skip_support /* Intermedia */ (action, name, reg) values (0, 'ORDDATA', 0); insert into system.logstdby$skip_support /* Intermedia */ (action, name, reg) values (0, 'ORDPLUGINS', 0); insert into system.logstdby$skip_support /* Intermedia */ (action, name, reg) values (0, 'ORDSYS', 0); insert into system.logstdby$skip_support /* Outlines (Plan Stability) */ (action, name, reg) values (0, 'OUTLN', 0); insert into system.logstdby$skip_support /* Sample Schema */ (action, name, reg) values (-1, 'PM', 0); insert into system.logstdby$skip_support /* Sample Schema */ (action, name, reg) values (-1, 'SCOTT', 0); insert into system.logstdby$skip_support /* SQL/MM Still Image */ (action, name, reg) values (0, 'SI_INFORMTN_SCHEMA', 0); insert into system.logstdby$skip_support /* Sample Schema */ (action, name, reg) values (-1, 'SH', 0); insert into system.logstdby$skip_support (action, name, reg) values (0, 'SYS', 0); insert into system.logstdby$skip_support (action, name, reg) values (0, 'SYSTEM', 0); insert into system.logstdby$skip_support /* Adminstrator OEM */ (action, name, reg) values (0, 'SYSMAN', 0); insert into system.logstdby$skip_support /* Transparent Session Migration */ (action, name, reg) values (0, 'TSMSYS', 0); insert into system.logstdby$skip_support /* Ultrasearch */ (action, name, reg) values (0, 'WKPROXY', 0); insert into system.logstdby$skip_support /* Ultrasearch */ (action, name, reg) values (0, 'WKSYS', 0); insert into system.logstdby$skip_support (action, name, reg) values (0, 'WK_TEST', 0); insert into system.logstdby$skip_support /* Workspace Manager */ (action, name, reg) values (0, 'WMSYS', 0); insert into system.logstdby$skip_support /* XML DB */ (action, name, reg) values (0, 'XDB', 0); insert into system.logstdby$skip_support (action, name, reg) values (0, 'XS$NULL', 0); insert into system.logstdby$skip_support /* Time Index */ (action, name, reg) values (0, 'XTISYS', 0); commit; create unique index system.logstdby$skip_ind on system.logstdby$skip_support (name, action) tablespace SYSAUX; insert into system.logstdby$skip_support (action, name, reg) select distinct 0, d.schema, 1 from dba_server_registry d where not exists (select name from system.logstdby$skip_support s where d.schema = s.name and s.action in (-1,0)); commit; Rem Maintains history of log streams processed. create table system.logstdby$history ( stream_sequence# number, /* Stream identifier */ lmnr_sid number, /* LogMiner session id */ dbid number, /* DBID */ first_change# number, /* Starting scn for this stream */ last_change# number, /* Last scn for this stream */ source number, /* Stream info source */ status number, /* Processing status */ first_time date, /* Time corresponding to first_change# */ last_time date, /* Time corresponding to last_change# */ dgname varchar2(255), /* Dataguard name string */ spare1 number, /* standby became primary scn */ spare2 number, /* processed scn */ spare3 varchar2(2000) /* Future expansion */ ) tablespace SYSAUX / Rem Rem EDS support Rem create table system.logstdby$eds_tables ( owner varchar2(30), /* table owner */ table_name varchar2(30), /* base table name */ shadow_table_name varchar2(30), /* shadow table name */ base_trigger_name varchar2(30), /* base table trigger name */ shadow_trigger_name varchar2(30), /* shadow table trigger name */ dblink varchar2(255), /* dblink name */ flags number, /* flags */ state varchar2(255), /* BEGUN, COMPLETE */ objv number, /* local object version of base table */ obj# number, /* local object# of base table */ sobj# number, /* local object# of shadow table */ ctime timestamp, /* timestamp when table support added */ spare1 number, /* spare number field */ spare2 varchar2(255), /* spare varchar field */ spare3 number, /* spare number field */ constraint logstdby$eds_tables_pkey primary key (owner, table_name) ) tablespace SYSAUX / Rem Rem Create views over the metadata tables. Rem --------------------------------------------------------------------- -- LOGSTDBY_SUPPORT View for internal use only -- This view makes up the basis for a number of queries made by logical -- standby to make decisions about what tables to support. This view along -- with the dba_logstdby_unsupported view must be modified when ever the -- collection of data types or table support changes. If you make a change -- here, you'll almost certainly need a change there. All the tables and -- sequences are displayed here, but only those with generated_sby == 1 -- will be maintained by logical standby. -- -- this view is a union of two views: -- logstdby_support_stab - supported tables -- logstdby_support_seq - supported sequences --------------------------------------------------------------------- -- LOGSTDBY_SUPPORT_TAB_10_1 -- This view encapsulates 10.1 compatibility support -- gensby: 1 supported, -- -1 internal so not supported -- 0 user data not supported because of features -- current_sby: 1 if lsby bit set in tab$ else 0 -- create or replace view logstdby_support_tab_10_1 as select u.name owner, o.name name, o.type#, o.obj#, decode(bitand(t.flags, 1073741824), 1073741824, 1, 0) current_sby, (case /* The following are tables that are system maintained */ when ( exists (select 1 from system.logstdby$skip_support s where s.name = u.name and action = 0)) or bitand(o.flags, 2 /* temporary object */ + 16 /* secondary object */ + 32 /* in-memory temp table */ + 128 /* dropped table (RecycleBin) */ ) != 0 or bitand(t.flags, 262144 /* 0x00040000 Summary Container Table, MV */ + 134217728 /* 0x08000000 in-memory temporary table */ + 536870912 /* 0x20000000 Mapping Tab for Phys rowid of IOT */ ) != 0 or bitand(t.property, 512 /* 0x00000200 iot OVeRflow segment */ + 8192 /* 0x00002000 nested table */ + 4194304 /* 0x00400000 global temporary table */ + 8388608 /* 0x00800000 session-specific temporary table */ + 33554432 /* 0x02000000 Read Only Materialized View */ + 67108864 /* 0x04000000 Materialized View table */ + 134217728 /* 0x08000000 Is a Sub object */ + 2147483648 /* 0x80000000 eXternal TaBle */ ) != 0 or bitand(t.trigflag, 536870912 /* 0x20000000 DDLs autofiltered */ ) != 0 or exists (select 1 from sys.mlog$ ml /* MVLOG table */ where ml.mowner = u.name and ml.log = o.name) or exists (select 1 from sys.secobj$ so /* ODCI storage table */ where o.obj# = so.secobj#) then -1 /* The following tables are user visible tables that we choose to * skip because of some unsupported attribute of the table or column */ when bitand(t.property, 262208) = 262208 /* 0x40+0x40000 IOT + user LOB */ or bitand(t.property, 2112) = 2112 /* 0x40+0x800 IOT + internal LOB */ or /* IOT with "Row Movement" */ (bitand(t.property, 64) = 64 and bitand(t.flags, 131072) = 131072) or bitand(t.trigflag, 65536 /* 0X10000 Table has encrypted columns */ ) != 0 or /* Compression */ (bitand(nvl(s.spare1,0), 2048) = 2048 and bitand(t.property, 32) != 32) or o.oid$ is not null or bitand(t.property, 1 /* 0x00000001 typed table */ + 2 /* 0x00000002 has ADT columns */ + 4 /* 0x00000004 has nested-TABLE columns */ + 8 /* 0x00000008 has REF columns */ + 16 /* 0x00000010 has array columns */ + 128 /* 0x00000080 IOT with row overflow */ + 256 /* 0x00000100 IOT with row clustering */ + 32768 /* 0x00008000 has FILE columns */ + 131072 /* 0x00020000 table is used as an AQ queue table */ ) != 0 or (bitand(t.property, 32) = 32) /* Partitioned */ and exists (select 1 from partobj$ po where po.obj#=o.obj# and (po.parttype in (3, /* System partitioned */ 5))) /* Reference partitioned */ or exists (select 1 from sys.col$ c where t.obj# = c.obj# and bitand(c.property, 32) != 32 /* Not hidden */ and (c.type# not in ( 1, /* VARCHAR2 */ 2, /* NUMBER */ 8, /* LONG */ 12, /* DATE */ 24, /* LONG RAW */ 96, /* CHAR */ 100, /* BINARY FLOAT */ 101, /* BINARY DOUBLE */ 112, /* CLOB and NCLOB */ 113, /* BLOB */ 180, /* TIMESTAMP (..) */ 181, /* TIMESTAMP(..) WITH TIME ZONE */ 182, /* INTERVAL YEAR(..) TO MONTH */ 183, /* INTERVAL DAY(..) TO SECOND(..) */ 231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */ and (c.type# != 23 /* RAW not RAW OID */ or (c.type# = 23 and bitand(c.property, 2) = 2))) ----------------------------------------- or (c.type# in (8,24,112,113) and 0 = (select count(*) from sys.col$ c2 where t.obj# = c2.obj# and bitand(c2.property, 32) != 32 /* Not hidden */ and (c2.type# in ( 1, /* VARCHAR2 */ 2, /* NUMBER */ 12, /* DATE */ 23, /* RAW */ 96, /* CHAR */ 100, /* BINARY FLOAT */ 101, /* BINARY DOUBLE */ 180, /* TIMESTAMP (..) */ 181, /* TIMESTAMP(..) WITH TIME ZONE */ 182, /* INTERVAL YEAR(..) TO MONTH */ 183, /* INTERVAL DAY(..) TO SECOND(..) */ 231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */ )))) ----------------------------------------- then 0 else 1 end) gensby from sys.obj$ o, sys.user$ u, sys.tab$ t, sys.seg$ s where o.owner# = u.user# and o.obj# = t.obj# and t.file# = s.file# (+) and t.block# = s.block# (+) and t.ts# = s.ts# (+) and t.obj# = o.obj# / --------------------------------------------------------------------- -- LOGSTDBY_SUPPORT_TAB_10_2 -- This view encapsulates 10.2 compatibility support -- gensby: 1 supported, -- -1 internal so not supported -- 0 user data not supported because of features -- current_sby: 1 if lsby bit set in tab$ else 0 -- create or replace view logstdby_support_tab_10_2 as select u.name owner, o.name name, o.type#, o.obj#, decode(bitand(t.flags, 1073741824), 1073741824, 1, 0) current_sby, (case /* The following are tables that are system maintained */ when ( exists (select 1 from system.logstdby$skip_support s where s.name = u.name and action = 0)) or bitand(o.flags, 2 /* temporary object */ + 16 /* secondary object */ + 32 /* in-memory temp table */ + 128 /* dropped table (RecycleBin) */ ) != 0 or bitand(t.flags, 262144 /* 0x00040000 Summary Container Table, MV */ + 134217728 /* 0x08000000 in-memory temporary table */ + 536870912 /* 0x20000000 Mapping Tab for Phys rowid of IOT */ ) != 0 or bitand(t.property, 512 /* 0x00000200 iot OVeRflow segment */ + 8192 /* 0x00002000 nested table */ + 4194304 /* 0x00400000 global temporary table */ + 8388608 /* 0x00800000 session-specific temporary table */ + 33554432 /* 0x02000000 Read Only Materialized View */ + 67108864 /* 0x04000000 Materialized View table */ + 134217728 /* 0x08000000 Is a Sub object */ + 2147483648 /* 0x80000000 eXternal TaBle */ ) != 0 or bitand(t.trigflag, 536870912 /* 0x20000000 DDLs autofiltered */ ) != 0 or exists (select 1 from sys.mlog$ ml /* MVLOG table */ where ml.mowner = u.name and ml.log = o.name) or exists (select 1 from sys.secobj$ so /* ODCI storage table */ where o.obj# = so.secobj#) then -1 /* The following tables are user visible tables that we choose to * skip because of some unsupported attribute of the table or column */ when bitand(t.trigflag, 65536 /* 0X10000 Table has encrypted columns */ ) != 0 or /* Compression */ (bitand(nvl(s.spare1,0), 2048) = 2048 and bitand(t.property, 32) != 32) or o.oid$ is not null or bitand(t.property, /* The following column properties are not checked in the * common section because they are reflected in the column * definitions and we want to see just those columns */ 1 /* 0x00000001 typed table */ + 2 /* 0x00000002 has ADT columns */ + 4 /* 0x00000004 has nested-TABLE columns */ + 8 /* 0x00000008 has REF columns */ + 16 /* 0x00000010 has array columns */ + 32768 /* 0x00008000 has FILE columns */ + 131072 /* 0x00020000 table is used as an AQ queue table */ ) != 0 or (bitand(t.property, 32) = 32) /* Partitioned */ and exists (select 1 from partobj$ po where po.obj#=o.obj# and (po.parttype in (3, /* System partitioned */ 5))) /* Reference partitioned */ or exists (select 1 from sys.col$ c where t.obj# = c.obj# and bitand(c.property, 32) != 32 /* Not hidden */ and (c.type# not in ( 1, /* VARCHAR2 */ 2, /* NUMBER */ 8, /* LONG */ 12, /* DATE */ 24, /* LONG RAW */ 96, /* CHAR */ 100, /* BINARY FLOAT */ 101, /* BINARY DOUBLE */ 112, /* CLOB and NCLOB */ 113, /* BLOB */ 180, /* TIMESTAMP (..) */ 181, /* TIMESTAMP(..) WITH TIME ZONE */ 182, /* INTERVAL YEAR(..) TO MONTH */ 183, /* INTERVAL DAY(..) TO SECOND(..) */ 231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */ and (c.type# != 23 /* RAW not RAW OID */ or (c.type# = 23 and bitand(c.property, 2) = 2))) ----------------------------------------- or (c.type# in (8,24,112,113) and 0 = (select count(*) from sys.col$ c2 where t.obj# = c2.obj# and bitand(c2.property, 32) != 32 /* Not hidden */ and (c2.type# in ( 1, /* VARCHAR2 */ 2, /* NUMBER */ 12, /* DATE */ 23, /* RAW */ 96, /* CHAR */ 100, /* BINARY FLOAT */ 101, /* BINARY DOUBLE */ 180, /* TIMESTAMP (..) */ 181, /* TIMESTAMP(..) WITH TIME ZONE */ 182, /* INTERVAL YEAR(..) TO MONTH */ 183, /* INTERVAL DAY(..) TO SECOND(..) */ 231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */ )))) ----------------------------------------- then 0 else 1 end) gensby from sys.obj$ o, sys.user$ u, sys.tab$ t, sys.seg$ s where o.owner# = u.user# and o.obj# = t.obj# and t.file# = s.file# (+) and t.block# = s.block# (+) and t.ts# = s.ts# (+) and t.obj# = o.obj# / ----------------------------------------------------------------------- -- LOGSTDBY_SUPPORT_11LOB -- This is a help view for logstdby_support_11_1. Eventually, we want to -- move all the logic here to logstdby_support_11_1 inline. -- NOTE: -- THis view indicates whether a lob is a securefile or has at least one -- securefile partition. The dedupsecurefile indicates that the securefile -- has the deduplicte option enabled. ------------------------------------------------------------------------ create or replace view logstdby_support_11lob as select lb.obj#, lb.lobj#, lb.col#, (case when (bitand(lb.property, 4) = 4) /* the lob colum is partitioned */ then case when (exists /* composite partitioned lob */ (select 1 from sys.lobfrag$ lf1, sys.lobcomppart$ cm where lb.lobj# = cm.lobj# and cm.partobj# = lf1.parentobj# and bitand(lf1.fragpro, 2048) = 2048) or exists /* regular partitioned lob */ (select 1 from sys.lobfrag$ lf2 where lb.lobj# = lf2.parentobj# and bitand(lf2.fragpro, 2048) = 2048)) then 1 else 0 end else /* non-partitioned lob */ case when bitand(lb.property, 2048) = 2048 /* this is a securefile column */ then 1 else 0 end end) securefile, (case when (bitand(lb.property, 4) = 4) /* the lob colum is partitioned */ then case when (exists /* composite partitioned lob */ (select 1 from sys.lobfrag$ lf1, sys.lobcomppart$ cm where lb.lobj# = cm.lobj# and cm.partobj# = lf1.parentobj# and bitand(lf1.fragflags, 65536 /* 0x10000 = Sharing: LOB level */ + 131072 /* 0x20000 = Sharing: Object level */ + 262144 /* 0x40000 = Sharing: Validate */ ) != 0) /* this is a dedup securefile */ or exists /* regular partitioned lob */ (select 1 from sys.lobfrag$ lf2 where lb.lobj# = lf2.parentobj# and bitand(lf2.fragflags, 65536 /* 0x10000 = Sharing: LOB level */ + 131072 /* 0x20000 = Sharing: Object level */ + 262144 /* 0x40000 = Sharing: Validate */ ) != 0)) /* this is a dedup securefile */ then 1 else 0 end else /* non-partitioned lob */ case when bitand(lb.property, 2048) = 2048 and bitand(lb.flags, 65536 /* 0x10000 = Sharing: LOB level */ + 131072 /* 0x20000 = Sharing: Object level */ + 262144 /* 0x40000 = Sharing: Validate */ ) != 0 /* this is a dedup securefile */ then 1 else 0 end end) dedupsecurefile from sys.lob$ lb / --------------------------------------------------------------------- -- LOGSTDBY_SUPPORT_TAB_11_2 -- This view encapsulates 11.2 compatibility support -- gensby: 1 supported, -- -1 internal so not supported -- 0 user data not supported because of features -- current_sby: 1 if lsby bit set in tab$ else 0 -- create or replace view logstdby_support_tab_11_2 as select u.name owner, o.name name, o.type#, o.obj#, decode(bitand(t.flags, 1073741824), 1073741824, 1, 0) current_sby, (case /* The following are tables that are system maintained */ when ( exists (select 1 from system.logstdby$skip_support s where s.name = u.name and action = 0)) or bitand(o.flags, 2 /* temporary object */ + 16 /* secondary object */ + 32 /* in-memory temp table */ + 128 /* dropped table (RecycleBin) */ ) != 0 or bitand(t.flags, 262144 /* 0x00040000 Summary Container Table, MV */ + 134217728 /* 0x08000000 in-memory temporary table */ + 536870912 /* 0x20000000 Mapping Tab for Phys rowid of IOT */ ) != 0 or bitand(t.property, 512 /* 0x00000200 iot OVeRflow segment */ + 8192 /* 0x00002000 nested table */ + 4194304 /* 0x00400000 global temporary table */ + 8388608 /* 0x00800000 session-specific temporary table */ + 33554432 /* 0x02000000 Read Only Materialized View */ + 67108864 /* 0x04000000 Materialized View table */ + 134217728 /* 0x08000000 Is a Sub object */ + 2147483648 /* 0x80000000 eXternal TaBle */ + 4294967296 /* 0x100000000 Cube */ + 8589934592 /* 0x200000000 FBA Internal */ ) != 0 or bitand(t.trigflag, 536870912 /* 0x20000000 DDLs autofiltered */ ) != 0 or exists (select 1 from sys.mlog$ ml /* MVLOG table */ where ml.mowner = u.name and ml.log = o.name) or exists (select 1 from sys.secobj$ so /* ODCI storage table */ where o.obj# = so.secobj#) then -1 /* The following tables are user visible tables that we choose to * skip because of some unsupported attribute of the table or column */ when (bitand(t.property, 1 ) = 1 /* 0x00000001 typed table */ AND not exists /* Only XML Typed Tables Are Supported */ (select 1 from sys.col$ cc, sys.opqtype$ opq where cc.name = 'SYS_NC_ROWINFO$' and cc.type# = 58 and opq.obj# = cc.obj# and opq.intcol# = cc.intcol# and opq.type = 1 and cc.obj# = t.obj# and bitand(opq.flags,4) = 4 /* stored as lob */ and bitand(opq.flags,64) = 0 /* not stored as binary */ and bitand(opq.flags,512) = 0 )) /* not hierarch enab */ or (bitand(t.property, 32) = 32) /* Partitioned */ and exists (select 1 from partobj$ po where po.obj#=o.obj# and (po.parttype in (3, /* System partitioned */ 5))) /* Reference partitioned */ or bitand(t.property, /* This clause is only for performance; they could be excluded by the column datatype checks below */ 4 /* 0x00000004 has nested-TABLE columns */ + 8 /* 0x00000008 has REF columns */ + 16 /* 0x00000010 has array columns */ + 32768 /* 0x00008000 has FILE columns */ + 131072 /* 0x00020000 table is used as an AQ queue table */ ) != 0 ----------------------------------------- /* unsupp view joins col$, here we subquery it */ or exists (select 1 from sys.col$ c where t.obj# = c.obj# ----------------------------------------- /* ignore any hidden columns in this subquery */ and bitand(c.property, 32) != 32 /* Not hidden */ ----------------------------------------- /* table has an unsupported datatype */ and ((c.type# not in ( 1, /* VARCHAR2 */ 2, /* NUMBER */ 8, /* LONG */ 12, /* DATE */ 24, /* LONG RAW */ 96, /* CHAR */ 100, /* BINARY FLOAT */ 101, /* BINARY DOUBLE */ 112, /* CLOB and NCLOB */ 113, /* BLOB */ 180, /* TIMESTAMP (..) */ 181, /* TIMESTAMP(..) WITH TIME ZONE */ 182, /* INTERVAL YEAR(..) TO MONTH */ 183, /* INTERVAL DAY(..) TO SECOND(..) */ 231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */ and (c.type# != 23 /* RAW not RAW OID */ or (c.type# = 23 and bitand(c.property, 2) = 2)) and (c.type# != 58 /* OPAQUE */ or (c.type# = 58 /* XMLTYPE as CLOB */ and not exists (select 1 from opqtype$ opq where opq.type=1 and bitand(opq.flags, 4) = 4 and bitand(opq.flags,64) = 0 and bitand(opq.flags,512) = 0 and opq.obj#=c.obj# and opq.intcol#=c.intcol#)))) ----------------------------------------- /* table doesn't have at least one scalar column */ or (c.type# in (8,24,112,113) and 0 = (select count(*) from sys.col$ c2 where t.obj# = c2.obj# and bitand(c2.property, 32) != 32 /* Not hidden */ and (c2.type# in ( 1, /* VARCHAR2 */ 2, /* NUMBER */ 12, /* DATE */ 23, /* RAW */ 96, /* CHAR */ 100, /* BINARY FLOAT */ 101, /* BINARY DOUBLE */ 180, /* TIMESTAMP (..) */ 181, /* TIMESTAMP(..) WITH TIME ZONE */ 182, /* INTERVAL YEAR(..) TO MONTH */ 183, /* INTERVAL DAY(..) TO SECOND(..) */ 231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */ or (c2.type# = 58 /* XMLTYPE as CLOB */ and exists (select 1 from opqtype$ o where o.type=1 and (bitand(o.flags, 4) = 4) and bitand(o.flags,512) = 0 /* not hier enab */ and o.obj#=c2.obj# ))))) ----------------------------------------- /* table has a dedup securefile column */ or (c.type# in (112, 113) and exists (select 1 from logstdby_support_11lob lb where lb.obj# = o.obj# and lb.col# = c.col# and dedupsecurefile = 1)) ----------------------------------------- /* table has a virtual column candidate key */ or (bitand(c.property, 65544) != 0 /* Virtual Column */ and bitand(c.property, 256) = 0 /* Sys Generated */ and exists (select 1 from icol$ ic, ind$ i where ic.bo# = t.obj# and ic.col# = c.col# and i.bo# = t.obj# and i.obj# = ic.obj# and bitand(i.property, 1) = 1))) /* Unique Index */ ) /* end col$ exists subquery */ ---------------------------------------------- then 0 else 1 end) gensby from sys.obj$ o, sys.user$ u, sys.tab$ t, sys.seg$ s where o.owner# = u.user# and o.obj# = t.obj# and t.file# = s.file# (+) and t.block# = s.block# (+) and t.ts# = s.ts# (+) and t.obj# = o.obj# / --------------------------------------------------------------------- -- LOGSTDBY_SUPPORT_TAB_11_1 -- This view encapsulates 11.1 compatibility support -- gensby: 1 supported, -- -1 internal so not supported -- 0 user data not supported because of features -- current_sby: 1 if lsby bit set in tab$ else 0 -- create or replace view logstdby_support_tab_11_1 as select u.name owner, o.name name, o.type#, o.obj#, decode(bitand(t.flags, 1073741824), 1073741824, 1, 0) current_sby, (case /* The following are tables that are system maintained */ when ( exists (select 1 from system.logstdby$skip_support s where s.name = u.name and action = 0)) or bitand(o.flags, 2 /* temporary object */ + 16 /* secondary object */ + 32 /* in-memory temp table */ + 128 /* dropped table (RecycleBin) */ ) != 0 or bitand(t.flags, 262144 /* 0x00040000 Summary Container Table, MV */ + 134217728 /* 0x08000000 in-memory temporary table */ + 536870912 /* 0x20000000 Mapping Tab for Phys rowid of IOT */ ) != 0 or bitand(t.property, 512 /* 0x00000200 iot OVeRflow segment */ + 8192 /* 0x00002000 nested table */ + 4194304 /* 0x00400000 global temporary table */ + 8388608 /* 0x00800000 session-specific temporary table */ + 33554432 /* 0x02000000 Read Only Materialized View */ + 67108864 /* 0x04000000 Materialized View table */ + 134217728 /* 0x08000000 Is a Sub object */ + 2147483648 /* 0x80000000 eXternal TaBle */ + 4294967296 /* 0x100000000 Cube */ + 8589934592 /* 0x200000000 FBA Internal */ ) != 0 or bitand(t.trigflag, 536870912 /* 0x20000000 DDLs autofiltered */ ) != 0 or exists (select 1 from sys.mlog$ ml /* MVLOG table */ where ml.mowner = u.name and ml.log = o.name) or exists (select 1 from sys.secobj$ so /* ODCI storage table */ where o.obj# = so.secobj#) then -1 /* The following tables are user visible tables that we choose to * skip because of some unsupported attribute of the table or column */ when (bitand(t.property, 1 ) = 1 /* 0x00000001 typed table */ AND not exists /* Only XML Typed Tables Are Supported */ (select 1 from sys.col$ cc, sys.opqtype$ opq where cc.name = 'SYS_NC_ROWINFO$' and cc.type# = 58 and opq.obj# = cc.obj# and opq.intcol# = cc.intcol# and opq.type = 1 and cc.obj# = t.obj# and bitand(opq.flags,4) = 4 /* stored as lob */ and bitand(opq.flags,64) = 0 /* not stored as binary */ and bitand(opq.flags,512) = 0 )) /* not hierarch enab */ or (bitand(nvl(s.spare1,0), 2048) = 2048 /* Compression */ and bitand(t.property, 32) != 32) or (bitand(t.property, 32) = 32) /* Partitioned */ and exists (select 1 from partobj$ po where po.obj#=o.obj# and (po.parttype in (3, /* System partitioned */ 5))) /* Reference partitioned */ or bitand(t.property, /* This clause is only for performance; they could be excluded by the column datatype checks below */ 4 /* 0x00000004 has nested-TABLE columns */ + 8 /* 0x00000008 has REF columns */ + 16 /* 0x00000010 has array columns */ + 32768 /* 0x00008000 has FILE columns */ + 131072 /* 0x00020000 table is used as an AQ queue table */ ) != 0 ----------------------------------------- /* unsupp view joins col$, here we subquery it */ or exists (select 1 from sys.col$ c where t.obj# = c.obj# ----------------------------------------- /* ignore any hidden columns in this subquery */ and bitand(c.property, 32) != 32 /* Not hidden */ ----------------------------------------- /* table has an unsupported datatype */ and ((c.type# not in ( 1, /* VARCHAR2 */ 2, /* NUMBER */ 8, /* LONG */ 12, /* DATE */ 24, /* LONG RAW */ 96, /* CHAR */ 100, /* BINARY FLOAT */ 101, /* BINARY DOUBLE */ 112, /* CLOB and NCLOB */ 113, /* BLOB */ 180, /* TIMESTAMP (..) */ 181, /* TIMESTAMP(..) WITH TIME ZONE */ 182, /* INTERVAL YEAR(..) TO MONTH */ 183, /* INTERVAL DAY(..) TO SECOND(..) */ 231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */ and (c.type# != 23 /* RAW not RAW OID */ or (c.type# = 23 and bitand(c.property, 2) = 2)) and (c.type# != 58 /* OPAQUE */ or (c.type# = 58 /* XMLTYPE as CLOB */ and not exists (select 1 from opqtype$ opq where opq.type=1 and bitand(opq.flags, 4) = 4 and bitand(opq.flags,64) = 0 and bitand(opq.flags,512) = 0 and opq.obj#=c.obj# and opq.intcol#=c.intcol#)))) ----------------------------------------- /* table doesn't have at least one scalar column */ or (c.type# in (8,24,112,113) and 0 = (select count(*) from sys.col$ c2 where t.obj# = c2.obj# and bitand(c2.property, 32) != 32 /* Not hidden */ and (c2.type# in ( 1, /* VARCHAR2 */ 2, /* NUMBER */ 12, /* DATE */ 23, /* RAW */ 96, /* CHAR */ 100, /* BINARY FLOAT */ 101, /* BINARY DOUBLE */ 180, /* TIMESTAMP (..) */ 181, /* TIMESTAMP(..) WITH TIME ZONE */ 182, /* INTERVAL YEAR(..) TO MONTH */ 183, /* INTERVAL DAY(..) TO SECOND(..) */ 231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */ or (c2.type# = 58 /* XMLTYPE as CLOB */ and exists (select 1 from opqtype$ o where o.type=1 and (bitand(o.flags, 4) = 4) and bitand(o.flags,512) = 0 /* not hier enab */ and o.obj#=c2.obj# ))))) ----------------------------------------- /* table has a securefile column */ or (c.type# in (112, 113) and exists (select 1 from logstdby_support_11lob lb where lb.obj# = o.obj# and lb.col# = c.col# and securefile = 1)) ----------------------------------------- /* table has a virtual column candidate key */ or (bitand(c.property, 65544) != 0 /* Virtual Column */ and bitand(c.property, 256) = 0 /* Sys Generated */ and exists (select 1 from icol$ ic, ind$ i where ic.bo# = t.obj# and ic.col# = c.col# and i.bo# = t.obj# and i.obj# = ic.obj# and bitand(i.property, 1) = 1))) /* Unique Index */ ) /* end col$ exists subquery */ ---------------------------------------------- then 0 else 1 end) gensby from sys.obj$ o, sys.user$ u, sys.tab$ t, sys.seg$ s where o.owner# = u.user# and o.obj# = t.obj# and t.file# = s.file# (+) and t.block# = s.block# (+) and t.ts# = s.ts# (+) and t.obj# = o.obj# / --------------------------------------------------------------------- -- LOGSTDBY$TABF -- This table function implements the redo compatibility check -- for logstdby support dependant on database role -- -- must drop dependant table before create or replace drop type logstdby$srecs; create or replace type logstdby$srec is object ( OWNER VARCHAR2(30), NAME VARCHAR2(30), TYPE# NUMBER, OBJ# NUMBER, CURRENT_SBY NUMBER, GENSBY NUMBER) / create or replace type logstdby$srecs is table of logstdby$srec / create or replace function logstdby$tabf (request in varchar2 default null) return logstdby$srecs pipelined parallel_enable is lsby_object_row logstdby$srec := logstdby$srec('','',0,0,0,0); type lsby_tcur is ref cursor return logstdby_support_tab_10_1%ROWTYPE; dataset lsby_tcur; lsby_query_row dataset%ROWTYPE; cmpat varchar2(4); role varchar2(16); begin if request is null then select database_role into role from sys.v$database; else role := request; end if; if role = 'PRIMARY' then select substr(value,1,4) into cmpat from sys.v$parameter where name = 'compatible'; else select nvl( (select substr(s.redo_compat,1,4) from system.logstdby$parameters p, system.logmnr_session$ s where p.name = 'LMNR_SID' and p.value = s.session#), (select substr(value,1,4) from sys.v$parameter where name = 'compatible')) into cmpat from dual; end if; case cmpat when '10.0' then open dataset for select * from logstdby_support_tab_10_1; when '10.1' then open dataset for select * from logstdby_support_tab_10_1; when '10.2' then open dataset for select * from logstdby_support_tab_10_2; when '11.0' then open dataset for select * from logstdby_support_tab_11_1; when '11.1' then open dataset for select * from logstdby_support_tab_11_1; when '11.2' then open dataset for select * from logstdby_support_tab_11_2; else open dataset for select * from logstdby_support_tab_10_1; end case; loop fetch dataset into lsby_query_row; exit when dataset%NOTFOUND; lsby_object_row.owner := lsby_query_row.owner; lsby_object_row.name := lsby_query_row.name ; lsby_object_row.type# := lsby_query_row.type#; lsby_object_row.obj# := lsby_query_row.obj# ; lsby_object_row.current_sby := lsby_query_row.current_sby; lsby_object_row.gensby := lsby_query_row.gensby; pipe row (lsby_object_row); end loop; close dataset; exception when others then if dataset%ISOPEN then close dataset; end if; end; / grant execute on logstdby$tabf to select_catalog_role / --------------------------------------------------------------------- -- LOGSTDBY_SUPPORT_SEQ -- set of sequences with indicator for lsby support -- full_sby: not used for sequences use bogus constant -- current_sby: true if lsby bit set in seq$ -- generated_sby: if internal schema then 0 else 1 -- create or replace view logstdby_support_seq as select u.name owner, o.name name, o.type#, o.obj#, decode(bitand(s.flags, 8), 8, 1, 0) current_sby, nvl((select 0 from system.logstdby$skip_support ss where ss.name = u.name and action = 0), 1) gensby from obj$ o, user$ u, seq$ s where o.owner# = u.user# and o.obj# = s.obj# / --------------------------------------------------------------------- -- LOGSTDBY_SUPPORT -- filter tables with DML skip rules from logstdby_support_tab -- full_sby: 1 supported, -1 internal not supported -- 0 not supported because of features -- current_sby: true if lsby bit set in tab$ -- generated_sby: true if supported and no DML skip rule -- sequence nextval is treated as DML for skip -- create or replace view logstdby_support as select owner, name, type#, obj#, gensby full_sby, current_sby, (case when decode(gensby, 1, 1, 0) = 1 and not exists (select 1 from system.logstdby$skip s where statement_opt = 'DML' and error is null and 1 = case use_like when 0 then case when l.owner = s.schema and l.name = s.name then 1 else 0 end when 1 then case when l.owner like s.schema and l.name like s.name then 1 else 0 end when 2 then case when l.owner like s.schema escape esc and l.name like s.name escape esc then 1 else 0 end else 0 end) then 1 else 0 end) generated_sby from (select * from logstdby_support_seq union all select * from table(logstdby$tabf)) l / --------------------------------------------------------------------- -- LOGSTDBY_UNSUPPORTED_TABLES -- This undocumented view is created for the Data Guard GUI so that it -- can get a list of tables that are not supported. They could use -- the dba_logstdby_unsupported view, but the query is expensive, mostly -- because it's column based not table based. -- create or replace view dba_logstdby_unsupported_table as select owner, name table_name from table(sys.logstdby$tabf) where gensby = 0 / grant select on dba_logstdby_unsupported_table to select_catalog_role / create or replace public synonym logstdby_unsupported_tables for sys.dba_logstdby_unsupported_table / create or replace public synonym dba_logstdby_unsupported_table for sys.dba_logstdby_unsupported_table / comment on table dba_logstdby_unsupported_table is 'List of all the data tables that are not supported by Logical Standby' / comment on column dba_logstdby_unsupported_table.owner is 'Schema name of unsupported table' / comment on column dba_logstdby_unsupported_table.table_name is 'Table name of unsupported table' / --------------------------------------------------------------------- -- DBA_LOGSTDBY_UNSUPPORTED -- This documented view displays all the unsupported columns. -- The view is used by OEM if the users wishes to drill down on -- the list of table returned by the faster logstdby_unsupported_tables. -- This view is slower becuase of the join to col$ and filtering -- by column rather than by table -- -- The top level view simply queries the redo compatibility table -- function and decodes the datatype to text form (which is common -- for all compatibilities) --------------------------------------------------------------------- -- LOGSTDBY_UNSUPPORT_TAB_11_2 -- This view encapsulates the rules for support of 11.2 redo -- create or replace view logstdby_unsupport_tab_11_2 as select u.name owner, o.name table_name, c.name column_name, c.scale, c.precision#, c.charsetform, c.type#, (case when bitand(t.flags, 536870912) = 536870912 then 'Mapping table for physical rowid of IOT' when bitand(t.property, 131072) = 131072 then 'AQ queue table' when (bitand(t.property, 1 ) = 1 /* 0x00000001 typed table */ AND not exists /* Only XML Typed Tables Are Supported */ (select 1 from sys.col$ cc, sys.opqtype$ opq where cc.name = 'SYS_NC_ROWINFO$' and cc.type# = 58 and opq.obj# = cc.obj# and opq.intcol# = cc.intcol# and opq.type = 1 and cc.obj# = t.obj# and bitand(opq.flags,4) = 4 /* stored as lob */ and bitand(opq.flags,64) = 0 /* not stored as binary */ and bitand(opq.flags,512) = 0 )) /* not hierarch enab */ then 'Object Table' when bitand(c.property, 65544) != 0 then 'Unsupported Virtual Column' else null end) attributes, (case /* The following are tables that are system maintained */ when bitand(o.flags, 2 /* temporary object */ + 16 /* secondary object */ + 32 /* in-memory temp table */ + 128 /* dropped table (RecycleBin) */ ) != 0 or bitand(t.flags, 262144 /* 0x00040000 Summary Container Table, MV */ + 134217728 /* 0x08000000 in-memory temporary table */ + 536870912 /* 0x20000000 Mapping Tab for Phys rowid of IOT */ ) != 0 or bitand(t.property, 512 /* 0x00000200 iot OVeRflow segment */ + 8192 /* 0x00002000 nested table */ + 4194304 /* 0x00400000 global temporary table */ + 8388608 /* 0x00800000 session-specific temporary table */ + 33554432 /* 0x02000000 Read Only Materialized View */ + 67108864 /* 0x04000000 Materialized View table */ + 134217728 /* 0x08000000 Is a Sub object */ + 2147483648 /* 0x80000000 eXternal TaBle */ + 4294967296 /* 0x100000000 Cube */ + 8589934592 /* 0x200000000 FBA Internal */ ) != 0 or bitand(t.trigflag, 536870912 /* 0x20000000 DDLs autofiltered */ ) != 0 or exists /* MVLOG table */ (select 1 from sys.mlog$ ml where ml.mowner = u.name and ml.log = o.name) or exists (select 1 from sys.secobj$ so /* ODCI storage table */ where o.obj# = so.secobj#) then -1 /* The following tables are data tables in internal schemata * * that are not secondary objects */ when (exists (select 1 from system.logstdby$skip_support s where s.name = u.name and action = 0)) then -2 /* The following tables are user visible tables that we choose to * * skip because of some unsupported attribute of the table or column */ when (bitand(t.property, 1) = 1 /* 0x00000001 typed table */ AND not exists /* Only XML Typed Tables Are Supported */ (select 1 from sys.col$ cc, sys.opqtype$ opq where cc.name = 'SYS_NC_ROWINFO$' and cc.type# = 58 and opq.obj# = cc.obj# and opq.intcol# = cc.intcol# and opq.type = 1 and cc.obj# = t.obj# and bitand(opq.flags,4) = 4 /* stored as lob */ and bitand(opq.flags,64) = 0 /* not stored as binary */ and bitand(opq.flags,512) = 0 )) /* not hierarch enab */ or bitand(t.property, 131072 /* 0x00020000 table is used as an AQ queue table */ ) != 0 or (bitand(t.property, 32) = 32) and exists (select 1 from partobj$ po where po.obj#=o.obj# and (po.parttype in (3, /* System partitioned */ 5))) /* Reference partitioned */ or (c.type# not in ( 1, /* VARCHAR2 */ 2, /* NUMBER */ 8, /* LONG */ 12, /* DATE */ 24, /* LONG RAW */ 96, /* CHAR */ 100, /* BINARY FLOAT */ 101, /* BINARY DOUBLE */ 112, /* CLOB and NCLOB */ 113, /* BLOB */ 180, /* TIMESTAMP (..) */ 181, /* TIMESTAMP(..) WITH TIME ZONE */ 182, /* INTERVAL YEAR(..) TO MONTH */ 183, /* INTERVAL DAY(..) TO SECOND(..) */ 231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */ and (c.type# != 23 /* RAW not RAW OID */ or (c.type# = 23 and bitand(c.property, 2) = 2)) and (c.type# != 58 /* OPAQUE */ or (c.type# = 58 /* XMLTYPE as CLOB */ and not exists (select 1 from opqtype$ opq where opq.type=1 and bitand(opq.flags, 4) = 4 /* stored as lob */ and bitand(opq.flags,64) = 0 /* not stored as binary */ and bitand(opq.flags,512) = 0 /* not hierarch enab */ and opq.obj#=c.obj# and opq.intcol#=c.intcol#)))) ---------------------------------------------------------- /* longs must have a scalar column to use as the id key */ or (c.type# in (8,24,112,113) and 0 = (select count(*) from sys.col$ c2 where t.obj# = c2.obj# and bitand(c2.property, 32) != 32 /* Not hidden */ and (c2.type# in ( 1, /* VARCHAR2 */ 2, /* NUMBER */ 12, /* DATE */ 23, /* RAW */ 96, /* CHAR */ 100, /* BINARY FLOAT */ 101, /* BINARY DOUBLE */ 180, /* TIMESTAMP (..) */ 181, /* TIMESTAMP(..) WITH TIME ZONE */ 182, /* INTERVAL YEAR(..) TO MONTH */ 183, /* INTERVAL DAY(..) TO SECOND(..) */ 231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */ or (c2.type# = 58 /* XMLTYPE as CLOB */ and exists (select 1 from opqtype$ opq where opq.type=1 and bitand(opq.flags, 4) = 4 and bitand(opq.flags,64) = 0 and bitand(opq.flags,512) = 0 and opq.obj#=c2.obj# ))))) ---------------------------------------------------------- /* we don't support dedup securefile */ or (c.type# in (112, 113) and exists (select 1 from logstdby_support_11lob lb where lb.obj# = o.obj# and lb.col# = c.col# and dedupsecurefile = 1)) ---------------------------------------------------------- /* we don't support virtual column candidate key */ or (bitand(c.property, 65544) != 0 /* Virtual Column */ and bitand(c.property, 32) != 32 /* Not hidden */ and exists (select 1 from icol$ ic, ind$ i where ic.bo# = t.obj# and ic.col# = c.col# and i.bo# = t.obj# and i.obj# = ic.obj# and bitand(i.property, 1) = 1)) /* Unique Index */ ---------------------------------------------------------- then 0 else 1 end) gensby from sys.obj$ o, sys.user$ u, sys.tab$ t, sys.seg$ s, sys.col$ c where o.owner# = u.user# and o.obj# = t.obj# and o.obj# = c.obj# and t.file# = s.file# (+) and t.ts# = s.ts# (+) and t.block# = s.block# (+) and t.obj# = o.obj# and bitand(c.property, 32) != 32 /* Not hidden */ / --------------------------------------------------------------------- -- LOGSTDBY_UNSUPPORT_TAB_11_1 -- This view encapsulates the rules for support of 11.1 redo -- create or replace view logstdby_unsupport_tab_11_1 as select u.name owner, o.name table_name, c.name column_name, c.scale, c.precision#, c.charsetform, c.type#, (case when bitand(t.flags, 536870912) = 536870912 then 'Mapping table for physical rowid of IOT' when bitand(nvl(s.spare1, 0), 2048) = 2048 then 'Table Compression' when bitand(t.property, 131072) = 131072 then 'AQ queue table' when (bitand(t.property, 1 ) = 1 /* 0x00000001 typed table */ AND not exists /* Only XML Typed Tables Are Supported */ (select 1 from sys.col$ cc, sys.opqtype$ opq where cc.name = 'SYS_NC_ROWINFO$' and cc.type# = 58 and opq.obj# = cc.obj# and opq.intcol# = cc.intcol# and opq.type = 1 and cc.obj# = t.obj# and bitand(opq.flags,4) = 4 /* stored as lob */ and bitand(opq.flags,64) = 0 /* not stored as binary */ and bitand(opq.flags,512) = 0 )) /* not hierarch enab */ then 'Object Table' when bitand(c.property, 65544) != 0 then 'Unsupported Virtual Column' else null end) attributes, (case /* The following are tables that are system maintained */ when bitand(o.flags, 2 /* temporary object */ + 16 /* secondary object */ + 32 /* in-memory temp table */ + 128 /* dropped table (RecycleBin) */ ) != 0 or bitand(t.flags, 262144 /* 0x00040000 Summary Container Table, MV */ + 134217728 /* 0x08000000 in-memory temporary table */ + 536870912 /* 0x20000000 Mapping Tab for Phys rowid of IOT */ ) != 0 or bitand(t.property, 512 /* 0x00000200 iot OVeRflow segment */ + 8192 /* 0x00002000 nested table */ + 4194304 /* 0x00400000 global temporary table */ + 8388608 /* 0x00800000 session-specific temporary table */ + 33554432 /* 0x02000000 Read Only Materialized View */ + 67108864 /* 0x04000000 Materialized View table */ + 134217728 /* 0x08000000 Is a Sub object */ + 2147483648 /* 0x80000000 eXternal TaBle */ + 4294967296 /* 0x100000000 Cube */ + 8589934592 /* 0x200000000 FBA Internal */ ) != 0 or bitand(t.trigflag, 536870912 /* 0x20000000 DDLs autofiltered */ ) != 0 or exists /* MVLOG table */ (select 1 from sys.mlog$ ml where ml.mowner = u.name and ml.log = o.name) or exists (select 1 from sys.secobj$ so /* ODCI storage table */ where o.obj# = so.secobj#) then -1 /* The following tables are data tables in internal schemata * * that are not secondary objects */ when (exists (select 1 from system.logstdby$skip_support s where s.name = u.name and action = 0)) then -2 /* The following tables are user visible tables that we choose to * * skip because of some unsupported attribute of the table or column */ when (bitand(t.property, 1) = 1 /* 0x00000001 typed table */ AND not exists /* Only XML Typed Tables Are Supported */ (select 1 from sys.col$ cc, sys.opqtype$ opq where cc.name = 'SYS_NC_ROWINFO$' and cc.type# = 58 and opq.obj# = cc.obj# and opq.intcol# = cc.intcol# and opq.type = 1 and cc.obj# = t.obj# and bitand(opq.flags,4) = 4 /* stored as lob */ and bitand(opq.flags,64) = 0 /* not stored as binary */ and bitand(opq.flags,512) = 0 )) /* not hierarch enab */ or bitand(t.property, 131072 /* 0x00020000 table is used as an AQ queue table */ ) != 0 or (bitand(nvl(s.spare1,0), 2048) = 2048 /* Compression */ and bitand(t.property, 32) != 32) or (bitand(t.property, 32) = 32) and exists (select 1 from partobj$ po where po.obj#=o.obj# and (po.parttype in (3, /* System partitioned */ 5))) /* Reference partitioned */ or (c.type# not in ( 1, /* VARCHAR2 */ 2, /* NUMBER */ 8, /* LONG */ 12, /* DATE */ 24, /* LONG RAW */ 96, /* CHAR */ 100, /* BINARY FLOAT */ 101, /* BINARY DOUBLE */ 112, /* CLOB and NCLOB */ 113, /* BLOB */ 180, /* TIMESTAMP (..) */ 181, /* TIMESTAMP(..) WITH TIME ZONE */ 182, /* INTERVAL YEAR(..) TO MONTH */ 183, /* INTERVAL DAY(..) TO SECOND(..) */ 231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */ and (c.type# != 23 /* RAW not RAW OID */ or (c.type# = 23 and bitand(c.property, 2) = 2)) and (c.type# != 58 /* OPAQUE */ or (c.type# = 58 /* XMLTYPE as CLOB */ and not exists (select 1 from opqtype$ opq where opq.type=1 and bitand(opq.flags, 4) = 4 /* stored as lob */ and bitand(opq.flags,64) = 0 /* not stored as binary */ and bitand(opq.flags,512) = 0 /* not hierarch enab */ and opq.obj#=c.obj# and opq.intcol#=c.intcol#)))) ---------------------------------------------------------- /* longs must have a scalar column to use as the id key */ or (c.type# in (8,24,112,113) and 0 = (select count(*) from sys.col$ c2 where t.obj# = c2.obj# and bitand(c2.property, 32) != 32 /* Not hidden */ and (c2.type# in ( 1, /* VARCHAR2 */ 2, /* NUMBER */ 12, /* DATE */ 23, /* RAW */ 96, /* CHAR */ 100, /* BINARY FLOAT */ 101, /* BINARY DOUBLE */ 180, /* TIMESTAMP (..) */ 181, /* TIMESTAMP(..) WITH TIME ZONE */ 182, /* INTERVAL YEAR(..) TO MONTH */ 183, /* INTERVAL DAY(..) TO SECOND(..) */ 231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */ or (c2.type# = 58 /* XMLTYPE as CLOB */ and exists (select 1 from opqtype$ opq where opq.type=1 and bitand(opq.flags, 4) = 4 and bitand(opq.flags,64) = 0 and bitand(opq.flags,512) = 0 and opq.obj#=c2.obj# ))))) ---------------------------------------------------------- /* we don't support securefile */ or (c.type# in (112, 113) and exists (select 1 from logstdby_support_11lob lb where lb.obj# = o.obj# and lb.col# = c.col# and securefile = 1)) ---------------------------------------------------------- /* we don't support virtual column candidate key */ or (bitand(c.property, 65544) != 0 /* Virtual Column */ and bitand(c.property, 32) != 32 /* Not hidden */ and exists (select 1 from icol$ ic, ind$ i where ic.bo# = t.obj# and ic.col# = c.col# and i.bo# = t.obj# and i.obj# = ic.obj# and bitand(i.property, 1) = 1)) /* Unique Index */ ---------------------------------------------------------- then 0 else 1 end) gensby from sys.obj$ o, sys.user$ u, sys.tab$ t, sys.seg$ s, sys.col$ c where o.owner# = u.user# and o.obj# = t.obj# and o.obj# = c.obj# and t.file# = s.file# (+) and t.ts# = s.ts# (+) and t.block# = s.block# (+) and t.obj# = o.obj# and bitand(c.property, 32) != 32 /* Not hidden */ / -- LOGSTDBY_UNSUPPORT_TAB_10_2 -- This view encapsulates the rules for support of 10.2 redo -- create or replace view logstdby_unsupport_tab_10_2 as select u.name owner, o.name table_name, c.name column_name, c.scale, c.precision#, c.charsetform, c.type#, (case when bitand(t.flags, 536870912) = 536870912 then 'Mapping table for physical rowid of IOT' when bitand(nvl(s.spare1, 0), 2048) = 2048 then 'Table Compression' when bitand(t.property, 1) = 1 then 'Object Table' when bitand(c.property, 67108864) = 67108864 /* 0X4000000 */ then 'Encrypted Column' when bitand(t.property, 131072) = 131072 then 'AQ queue table' else null end) attributes, (case /* The following are tables that are system maintained */ when bitand(o.flags, 2 /* temporary object */ + 16 /* secondary object */ + 32 /* in-memory temp table */ + 128 /* dropped table (RecycleBin) */ ) != 0 or bitand(t.flags, 262144 /* 0x00040000 Summary Container Table, MV */ + 134217728 /* 0x08000000 in-memory temporary table */ + 536870912 /* 0x20000000 Mapping Tab for Phys rowid of IOT */ ) != 0 or bitand(t.property, 512 /* 0x00000200 iot OVeRflow segment */ + 8192 /* 0x00002000 nested table */ + 131072 /* 0x00020000 table is used as an AQ queue table */ + 4194304 /* 0x00400000 global temporary table */ + 8388608 /* 0x00800000 session-specific temporary table */ + 33554432 /* 0x02000000 Read Only Materialized View */ + 67108864 /* 0x04000000 Materialized View table */ + 134217728 /* 0x08000000 Is a Sub object */ + 2147483648 /* 0x80000000 eXternal TaBle */ ) != 0 or bitand(t.trigflag, 536870912 /* 0x20000000 DDLs autofiltered */ ) != 0 or exists /* MVLOG table */ (select 1 from sys.mlog$ ml where ml.mowner = u.name and ml.log = o.name) or exists (select 1 from sys.secobj$ so /* ODCI storage table */ where o.obj# = so.secobj#) then -1 /* The following tables are data tables in internal schemata * * that are not secondary objects */ when (exists (select 1 from system.logstdby$skip_support s where s.name = u.name and action = 0)) then -2 /* The following tables are user visible tables that we choose to * skip because of some unsupported attribute of the table or column */ when bitand(t.property, 1 /* 0x00000001 typed table */ + 131072 /* 0x00020000 table is used as an AQ queue table */ ) != 0 or (bitand(t.property, 32) = 32) and exists (select 1 from partobj$ po where po.obj#=o.obj# and (po.parttype in (3, /* System partitioned */ 5))) /* Reference partitioned */ or bitand(t.trigflag, 65536 /* 0X10000 Table has encrypted columns */ ) != 0 or /* Compression */ (bitand(nvl(s.spare1,0), 2048) = 2048 and bitand(t.property, 32) != 32) or o.oid$ is not null or (c.type# not in ( 1, /* VARCHAR2 */ 2, /* NUMBER */ 8, /* LONG */ 12, /* DATE */ 24, /* LONG RAW */ 96, /* CHAR */ 100, /* BINARY FLOAT */ 101, /* BINARY DOUBLE */ 112, /* CLOB and NCLOB */ 113, /* BLOB */ 180, /* TIMESTAMP (..) */ 181, /* TIMESTAMP(..) WITH TIME ZONE */ 182, /* INTERVAL YEAR(..) TO MONTH */ 183, /* INTERVAL DAY(..) TO SECOND(..) */ 231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */ and (c.type# != 23 /* RAW not RAW OID */ or (c.type# = 23 and bitand(c.property, 2) = 2))) ---------------------------------------------------------- /* longs must have a scalar column to use as the id key */ or (c.type# in (8,24,112,113) and 0 = (select count(*) from sys.col$ c2 where t.obj# = c2.obj# and bitand(c2.property, 32) != 32 /* Not hidden */ and (c2.type# in ( 1, /* VARCHAR2 */ 2, /* NUMBER */ 12, /* DATE */ 23, /* RAW */ 96, /* CHAR */ 100, /* BINARY FLOAT */ 101, /* BINARY DOUBLE */ 180, /* TIMESTAMP (..) */ 181, /* TIMESTAMP(..) WITH TIME ZONE */ 182, /* INTERVAL YEAR(..) TO MONTH */ 183, /* INTERVAL DAY(..) TO SECOND(..) */ 231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */ ))) ---------------------------------------------------------- then 0 else 1 end) gensby from sys.obj$ o, sys.user$ u, sys.tab$ t, sys.seg$ s, sys.col$ c where o.owner# = u.user# and o.obj# = t.obj# and o.obj# = c.obj# and t.file# = s.file# (+) and t.ts# = s.ts# (+) and t.block# = s.block# (+) and t.obj# = o.obj# and bitand(c.property, 32) != 32 /* Not hidden */ / --------------------------------------------------------------------- -- LOGSTDBY_UNSUPPORT_TAB_10_1 -- This view encapsulates the rules for support of 10.1 redo -- create or replace view logstdby_unsupport_tab_10_1 as select u.name owner, o.name table_name, c.name column_name, c.scale, c.precision#, c.charsetform, c.type#, (case when bitand(t.property, 128) = 128 then 'IOT with Overflow' when bitand(t.property, 262208) = 262208 then 'IOT with LOB' /* user lob */ when bitand(t.flags, 536870912) = 536870912 then 'Mapping table for physical rowid of IOT' when bitand(t.property, 2112) = 2112 then 'IOT with LOB' /* internal lob */ when (bitand(t.property, 64) = 64 and bitand(t.flags, 131072) = 131072) then 'IOT with row movement' when bitand(nvl(s.spare1,0), 2048) = 2048 then 'Table Compression' when bitand(t.property, 1) = 1 then 'Object Table' /* typed table/object table */ when bitand(t.property, 131072) = 131072 then 'AQ queue table' else null end) attributes, (case /* The following are tables that are system maintained */ when bitand(o.flags, 2 /* temporary object */ + 16 /* secondary object */ + 32 /* in-memory temp table */ + 128 /* dropped table (RecycleBin) */ ) != 0 or bitand(t.flags, 262144 /* 0x00040000 Summary Container Table, MV */ + 134217728 /* 0x08000000 in-memory temporary table */ + 536870912 /* 0x20000000 Mapping Tab for Phys rowid of IOT */ ) != 0 or bitand(t.property, 512 /* 0x00000200 iot OVeRflow segment */ + 8192 /* 0x00002000 nested table */ + 4194304 /* 0x00400000 global temporary table */ + 8388608 /* 0x00800000 session-specific temporary table */ + 33554432 /* 0x02000000 Read Only Materialized View */ + 67108864 /* 0x04000000 Materialized View table */ + 134217728 /* 0x08000000 Is a Sub object */ + 2147483648 /* 0x80000000 eXternal TaBle */ ) != 0 or bitand(t.trigflag, 536870912 /* 0x20000000 DDLs autofiltered */ ) != 0 or exists /* MVLOG table */ (select 1 from sys.mlog$ ml where ml.mowner = u.name and ml.log = o.name) or exists (select 1 from sys.secobj$ so /* ODCI storage table */ where o.obj# = so.secobj#) then -1 /* The following tables are data tables in internal schemata * * that are not secondary objects */ when (exists (select 1 from system.logstdby$skip_support s where s.name = u.name and action = 0)) then -2 /* The following tables are user visible tables that we choose to * skip because of some unsupported attribute of the table or column */ when bitand(t.property, 1 /* 0x00000001 typed table */ + 128 /* 0x00000080 IOT2 with row overflow */ + 256 /* 0x00000100 IOT with row clustering */ + 131072 /* 0x00020000 table is used as an AQ queue table */ ) != 0 or bitand(t.property, 262208) = 262208 /* 0x40+0x40000 IOT + user LOB */ or bitand(t.property, 2112) = 2112 /* 0x40+0x800 IOT + internal LOB */ or /* IOT with "Row Movement" */ (bitand(t.property, 64) = 64 and bitand(t.flags, 131072) = 131072) or (bitand(t.property, 32) = 32) and exists (select 1 from partobj$ po where po.obj#=o.obj# and (po.parttype in (3, /* System partitioned */ 5))) /* Reference partitioned */ or /* Compression */ (bitand(nvl(s.spare1,0), 2048) = 2048 and bitand(t.property, 32) != 32) or o.oid$ is not null or (c.type# not in ( 1, /* VARCHAR2 */ 2, /* NUMBER */ 8, /* LONG */ 12, /* DATE */ 24, /* LONG RAW */ 96, /* CHAR */ 100, /* BINARY FLOAT */ 101, /* BINARY DOUBLE */ 112, /* CLOB and NCLOB */ 113, /* BLOB */ 180, /* TIMESTAMP (..) */ 181, /* TIMESTAMP(..) WITH TIME ZONE */ 182, /* INTERVAL YEAR(..) TO MONTH */ 183, /* INTERVAL DAY(..) TO SECOND(..) */ 231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */ and (c.type# != 23 /* RAW not RAW OID */ or (c.type# = 23 and bitand(c.property, 2) = 2))) ---------------------------------------------------------- /* longs must have a scalar column to use as the id key */ or (c.type# in (8,24,112,113) and 0 = (select count(*) from sys.col$ c2 where t.obj# = c2.obj# and bitand(c2.property, 32) != 32 /* Not hidden */ and (c2.type# in ( 1, /* VARCHAR2 */ 2, /* NUMBER */ 12, /* DATE */ 23, /* RAW */ 96, /* CHAR */ 100, /* BINARY FLOAT */ 101, /* BINARY DOUBLE */ 180, /* TIMESTAMP (..) */ 181, /* TIMESTAMP(..) WITH TIME ZONE */ 182, /* INTERVAL YEAR(..) TO MONTH */ 183, /* INTERVAL DAY(..) TO SECOND(..) */ 231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */ ))) ---------------------------------------------------------- then 0 else 1 end) gensby from sys.obj$ o, sys.user$ u, sys.tab$ t, sys.seg$ s, sys.col$ c where o.owner# = u.user# and o.obj# = t.obj# and o.obj# = c.obj# and t.file# = s.file# (+) and t.ts# = s.ts# (+) and t.block# = s.block# (+) and t.obj# = o.obj# and bitand(c.property, 32) != 32 /* Not hidden */ / -- must drop dependant table before create or replace drop type logstdby$urecs; create or replace type logstdby$urec is object ( OWNER VARCHAR2(30), TABLE_NAME VARCHAR2(30), COLUMN_NAME VARCHAR2(30), ATTRIBUTES VARCHAR2(39), TYPE# NUMBER, SCALE NUMBER, PRECISION# NUMBER, CHARSETFORM NUMBER, GENSBY NUMBER) / create or replace type logstdby$urecs is table of logstdby$urec / create or replace function logstdby$utabf (request in varchar2 default null) return logstdby$urecs pipelined parallel_enable is lsby_object_row logstdby$urec := logstdby$urec('','','','',0,0,0,0,0); type lsby_tcur is ref cursor return logstdby_unsupport_tab_10_2%ROWTYPE; dataset lsby_tcur; lsby_query_row dataset%ROWTYPE; cmpat varchar2(4); role varchar2(16); begin if request is null then select database_role into role from sys.v$database; else role := request; end if; if role = 'PRIMARY' then select substr(value,1,4) into cmpat from sys.v$parameter where name = 'compatible'; else select nvl( (select substr(s.redo_compat,1,4) from system.logstdby$parameters p, system.logmnr_session$ s where p.name = 'LMNR_SID' and p.value = s.session#), (select substr(value,1,4) from sys.v$parameter where name = 'compatible')) into cmpat from dual; end if; case cmpat when '10.0' then open dataset for select * from logstdby_unsupport_tab_10_1; when '10.1' then open dataset for select * from logstdby_unsupport_tab_10_1; when '10.2' then open dataset for select * from logstdby_unsupport_tab_10_2; when '11.0' then open dataset for select * from logstdby_unsupport_tab_11_1; when '11.1' then open dataset for select * from logstdby_unsupport_tab_11_1; when '11.2' then open dataset for select * from logstdby_unsupport_tab_11_2; else open dataset for select * from logstdby_unsupport_tab_10_1; end case; loop fetch dataset into lsby_query_row; exit when dataset%NOTFOUND; if lsby_query_row.gensby = 0 or lsby_query_row.gensby = -2 then lsby_object_row.owner := lsby_query_row.owner; lsby_object_row.table_name := lsby_query_row.table_name; lsby_object_row.column_name := lsby_query_row.column_name; lsby_object_row.attributes := lsby_query_row.attributes; lsby_object_row.type# := lsby_query_row.type#; lsby_object_row.scale := lsby_query_row.scale; lsby_object_row.precision# := lsby_query_row.precision#; lsby_object_row.charsetform := lsby_query_row.charsetform; lsby_object_row.gensby := lsby_query_row.gensby; pipe row (lsby_object_row); end if; end loop; close dataset; exception when others then if dataset%ISOPEN then close dataset; end if; end; / grant execute on logstdby$utabf to select_catalog_role / create or replace view dba_logstdby_unsupported as select owner, table_name, column_name, attributes, substr(decode(type#, 1, decode(charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(scale, null, decode(precision#, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, decode(charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 58, 'OPAQUE', 69, 'ROWID', 96, decode(charsetform, 2, 'NCHAR', 'CHAR'), 100, 'BINARY_FLOAT', 101, 'BINARY_DOUBLE', 105, 'MLSLABEL', 106, 'MLSLABEL', 110, 'REF', 111, 'REF', 112, decode(charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 121, 'OBJECT', 122, 'NESTED TABLE', 123, 'VARRAY', 178, 'TIME(' ||scale|| ')', 179, 'TIME(' ||scale|| ')' || ' WITH TIME ZONE', 180, 'TIMESTAMP(' ||scale|| ')', 181, 'TIMESTAMP(' ||scale|| ')' || ' WITH TIME ZONE', 231, 'TIMESTAMP(' ||scale|| ')' || ' WITH LOCAL TIME ZONE', 182, 'INTERVAL YEAR(' ||precision#||') TO MONTH', 183, 'INTERVAL DAY(' ||precision#||') TO SECOND(' || scale || ')', 208, 'UROWID', 'UNDEFINED'),1,32) data_type from table (logstdby$utabf) where gensby = 0 / create or replace public synonym dba_logstdby_unsupported for dba_logstdby_unsupported / grant select on dba_logstdby_unsupported to select_catalog_role / comment on table dba_logstdby_unsupported is 'List of all the columns that are not supported by Logical Standby' / comment on column dba_logstdby_unsupported.owner is 'Schema name of unsupported column' / comment on column dba_logstdby_unsupported.table_name is 'Table name of unsupported column' / comment on column dba_logstdby_unsupported.column_name is 'Column name of unsupported column' / comment on column dba_logstdby_unsupported.data_type is 'Datatype of unsupported column' / comment on column dba_logstdby_unsupported.attributes is 'If not a data type issue, gives the reason why the table is unsupported' / --------------------------------------------------------------------- -- DBA_LOGSTDBY_NOT_UNIQUE -- We do not supplementally log longs and virtual columns. -- This view identifies tables that have no usable candidate key -- and which also have columns which are not supp logged. -- There is a chance that we could update the wrong row on the standby -- The bad_column attribute shows for each table: -- 'Y' - the table has a candidate key or it has no non-supplogged cols -- 'N' - one or more columns cannot be predicated from the redo data -- and it has no candidate key; DBA should add rely constraint! --------------------------------------------------------------------- create or replace view dba_logstdby_not_unique as select owner, name table_name, decode((select count(c.obj#) from sys.col$ c where c.obj# = l.obj# and (c.type# in (8, /* LONG */ 24, /* LONG RAW */ 112, /* CLOB */ 113))), /* BLOB */ 0, 'N', 'Y') bad_column from table(logstdby$tabf) l where gensby = 1 and type# = 2 and not exists /* not null unique key */ (select null from ind$ i, icol$ ic, col$ c where i.bo# = l.obj# and ic.obj# = i.obj# and c.col# = ic.col# and c.obj# = i.bo# and c.null$ > 0 /* not null */ and i.type# = 1 /* Btree */ and bitand(i.property, 1) = 1 /* Unique */ and i.intcols = i.cols /* no virtual columns */ ) and not exists /* primary key constraint */ (select null /* defer bit 0x1: deferrable */ from cdef$ cd /* bit 0x4: sys validated */ where cd.obj# = l.obj# /* bit 0x20: rely */ and cd.type# = 2 and bitand(cd.defer, 37) in (4, 32, 36)) / create or replace public synonym dba_logstdby_not_unique for dba_logstdby_not_unique / grant select on dba_logstdby_not_unique to select_catalog_role / comment on table dba_logstdby_not_unique is 'List of all the tables with out primary or unique key not null constraints' / comment on column dba_logstdby_not_unique.owner is 'Schema name of the non-unique table' / comment on column dba_logstdby_not_unique.table_name is 'Table name of the non-unique table' / comment on column dba_logstdby_not_unique.bad_column is 'Indicates that the table has a column not useful in the where clause' / create or replace view dba_logstdby_parameters as select name, value, unit, setting, dynamic from x$dglparam where visible=1 / create or replace public synonym dba_logstdby_parameters for dba_logstdby_parameters / grant select on dba_logstdby_parameters to select_catalog_role / comment on table dba_logstdby_parameters is 'Miscellaneous options and settings for Logical Standby' / comment on column dba_logstdby_parameters.name is 'Name of the parameter' / comment on column dba_logstdby_parameters.value is 'Optional value of the parameter' / -- DBA_LOGSTDBY_PROGRESS view -- Just break things down to understand them. -- First, the logstdby_log view is just an aid so we can include v$standby_log -- information in our views. So it combines logs in logmnr_log$ with -- v$standby_log logs. -- Second, the dba_logstdby_progress view is just a collection of subqueries. -- There are three important columns that are computed in the base in-line -- view X. These are APPLIED_SCN, READ_SCN (past tense), and NEWEST_SCN. -- Once these are computed, they are used as the source to compute all the -- other columns in the view. create or replace view logstdby_log as select first_change#, next_change#, sequence#, thread#, first_time, next_time from system.logmnr_log$ where session# = (select value from system.logstdby$parameters where name = 'LMNR_SID') /* comment */ union select first_change#, (last_change# + 1) next_change#, sequence#, thread#, first_time, last_time next_time from v$standby_log where status = 'ACTIVE' / create or replace view dba_logstdby_progress as select applied_scn, /* thread# derived from applied_scn */ (select min(thread#) from logstdby_log where sequence# = (select max(sequence#) from logstdby_log l where applied_scn >= first_change# and applied_scn <= next_change#) and applied_scn >= first_change# and applied_scn <= next_change#) applied_thread#, /* sequence# derived from applied_scn */ (select max(sequence#) from logstdby_log l where applied_scn >= first_change# and applied_scn <= next_change#) applied_sequence#, /* estimated time derived from applied_scn */ (select max(first_time + ((next_time - first_time) / (next_change# - first_change#) * (applied_scn - first_change#))) from logstdby_log l where applied_scn >= first_change# and applied_scn <= next_change#) applied_time, read_scn, /* thread# derived from read_scn */ (select min(thread#) from logstdby_log where sequence# = (select max(sequence#) from logstdby_log l where read_scn >= first_change# and read_scn <= next_change#) and read_scn >= first_change# and read_scn <= next_change#) read_thread#, /* sequence# derived from read_scn */ (select max(sequence#) from logstdby_log l where read_scn >= first_change# and read_scn <= next_change#) read_sequence#, /* estimated time derived from read_scn */ (select min(first_time + ((next_time - first_time) / (next_change# - first_change#) * (read_scn - first_change#))) from logstdby_log l where read_scn >= first_change# and read_scn <= next_change#) read_time, newest_scn, /* thread# derived from newest_scn */ (select min(thread#) from logstdby_log where sequence# = (select max(sequence#) from logstdby_log l where newest_scn >= first_change# and newest_scn <= next_change#) and newest_scn >= first_change# and newest_scn <= next_change#) newest_thread#, /* sequence# derived from newest_scn */ (select max(sequence#) from logstdby_log l where newest_scn >= first_change# and newest_scn <= next_change#) newest_sequence#, /* estimated time derived from newest_scn */ (select max(first_time + ((next_time - first_time) / (next_change# - first_change#) * (newest_scn - first_change#))) from logstdby_log l where newest_scn >= first_change# and newest_scn <= next_change#) newest_time from /* in-line view to calculate relavent scn values */ (select /* APPLIED_SCN */ greatest(nvl((select max(a.processed_scn) - 1 from system.logstdby$apply_milestone a),0), nvl((select max(a.commit_scn) from system.logstdby$apply_milestone a),0), sx.start_scn) applied_scn, /* READ_SCN */ greatest(nvl(sx.spill_scn,1), sx.start_scn) read_scn, /* NEWEST_SCN */ nvl((select max(next_change#)-1 from logstdby_log), sx.start_scn) newest_scn from system.logmnr_session$ sx where sx.session# = (select value from system.logstdby$parameters where name = 'LMNR_SID')) x / create or replace public synonym dba_logstdby_progress for dba_logstdby_progress / -- This must be done in catproc, since that's where logmnr tables are created -- grant select on dba_logstdby_progress to select_catalog_role -- / comment on table dba_logstdby_progress is 'List the SCN values describing read and apply progress' / comment on column dba_logstdby_progress.applied_scn is 'All transactions with a commit SCN <= this value have been applied' / comment on column dba_logstdby_progress.applied_thread# is 'Thread number for a log containing the applied_scn' / comment on column dba_logstdby_progress.applied_sequence# is 'Sequence number for a log containing the applied_scn' / comment on column dba_logstdby_progress.applied_time is 'Estimate of the time the applied_scn was generated' / comment on column dba_logstdby_progress.read_scn is 'All log data less than this SCN has been preserved in the database' / comment on column dba_logstdby_progress.read_thread# is 'Thread number for a log containing the read_scn' / comment on column dba_logstdby_progress.read_sequence# is 'Sequence number for a log containing the read_scn' / comment on column dba_logstdby_progress.read_time is 'Estimate of the time the read_scn was generated' / comment on column dba_logstdby_progress.newest_scn is 'The highest SCN that could be applied given the existing logs' / comment on column dba_logstdby_progress.newest_thread# is 'Thread number for a log containing the newest_scn' / comment on column dba_logstdby_progress.applied_sequence# is 'Sequence number for a log containing the newest_scn' / comment on column dba_logstdby_progress.newest_time is 'Estimate of the time the newest_scn was generated' / -- Logmnr tables aren't created yet so FORCE was necessary -- -- (don't list dummy entries) create or replace force view dba_logstdby_log as select thread#, resetlogs_change#, reset_timestamp resetlogs_id, sequence#, first_change#, next_change#, first_time, next_time, file_name, timestamp, dict_begin, dict_end, (case when l.next_change# <= p.read_scn then 'YES' when ((bitand(l.contents, 16) = 16) and (bitand(l.status, 4) = 0)) then 'FETCHING' when ((bitand(l.contents, 16) = 16) and (bitand(l.status, 4) = 4)) then 'CORRUPT' when l.first_change# < p.applied_scn then 'CURRENT' else 'NO' end) applied, blocks, block_size from system.logmnr_log$ l, dba_logstdby_progress p where session# = (select value from system.logstdby$parameters where name = 'LMNR_SID') and (flags is NULL or bitand(l.flags,16) = 0) / create or replace public synonym dba_logstdby_log for dba_logstdby_log / -- This must be done in catproc, since that's where logmnr tables are created -- grant select on dba_logstdby_log to select_catalog_role -- / comment on table dba_logstdby_log is 'List the information about received logs from the primary' / comment on column dba_logstdby_log.thread# is 'Redo thread number' / comment on column dba_logstdby_log.sequence# is 'Redo log sequence number' / comment on column dba_logstdby_log.first_change# is 'First change# in the archived log' / comment on column dba_logstdby_log.next_change# is 'First change in the next log' / comment on column dba_logstdby_log.first_time is 'Timestamp of the first change' / comment on column dba_logstdby_log.next_time is 'Timestamp of the next change' / comment on column dba_logstdby_log.file_name is 'Archived log file name' / comment on column dba_logstdby_log.timestamp is 'Time when the archiving completed' / comment on column dba_logstdby_log.dict_begin is 'Contains beginning of Log Miner Dictionary' / comment on column dba_logstdby_log.dict_end is 'Contains end of Log Miner Dictionary' / comment on column dba_logstdby_log.applied is 'Indicates apply progress through log stream' / comment on column dba_logstdby_log.blocks is 'Indicates the number of blocks in the log' / comment on column dba_logstdby_log.block_size is 'Indicates the size of each block in the log' / create or replace view dba_logstdby_skip_transaction as select xidusn, xidslt, xidsqn from system.logstdby$skip_transaction / create or replace public synonym dba_logstdby_skip_transaction for dba_logstdby_skip_transaction / grant select on dba_logstdby_skip_transaction to select_catalog_role / comment on table dba_logstdby_skip_transaction is 'List the transactions to be skipped' / comment on column dba_logstdby_skip_transaction.xidusn is 'Transaction id, component 1 of 3' / comment on column dba_logstdby_skip_transaction.xidslt is 'Transaction id, component 2 of 3' / comment on column dba_logstdby_skip_transaction.xidsqn is 'Transaction id, component 3 of 3' / create or replace view dba_logstdby_skip as select decode(error, 1, 'Y', 'N') error, statement_opt, schema owner, name, decode(use_like, 0, 'N', 'Y') use_like, esc, proc from system.logstdby$skip union all select 'N' error, 'INTERNAL SCHEMA' statement_opt, u.username owner, '%' name, 'N' use_like, null esc, null proc from dba_users u, system.logstdby$skip_support s where u.username = s.name and s.action = 0 / create or replace public synonym dba_logstdby_skip for dba_logstdby_skip / grant select on dba_logstdby_skip to select_catalog_role / comment on table dba_logstdby_skip is 'List the skip settings choosen' / comment on column dba_logstdby_skip.error is 'Does this skip setting only apply to failed attempts' / comment on column dba_logstdby_skip.statement_opt is 'The statement option choosen to skip' / comment on column dba_logstdby_skip.owner is 'Schema name under which this skip option should be applied' / comment on column dba_logstdby_skip.name is 'Object name under which this skip option should be applied' / comment on column dba_logstdby_skip.use_like is 'Use SQL wildcard search when matching names' / comment on column dba_logstdby_skip.esc is 'The escape character used when performing wildcard matches.' / comment on column dba_logstdby_skip.proc is 'The stored procedure to call for this skip setting. DDL only' / create or replace view dba_logstdby_events as select cast(event_time as date) event_time, event_time event_timestamp, spare1 as start_scn, current_scn, commit_scn, xidusn, xidslt, xidsqn, full_event event, errval status_code, error status from system.logstdby$events / create or replace public synonym dba_logstdby_events for dba_logstdby_events / grant select on dba_logstdby_events to select_catalog_role / comment on table dba_logstdby_events is 'Information on why logical standby events' / comment on column dba_logstdby_events.event_time is 'Time the event took place' / comment on column dba_logstdby_events.start_scn is 'SCN at which the transaction started' / comment on column dba_logstdby_events.current_scn is 'Change vector SCN for the change' / comment on column dba_logstdby_events.commit_scn is 'SCN for the commit record of the transaction' / comment on column dba_logstdby_events.xidusn is 'Transaction id, part 1 of 3' / comment on column dba_logstdby_events.xidslt is 'Transaction id, part 2 of 3' / comment on column dba_logstdby_events.xidsqn is 'Transaction id, part 3 of 3' / comment on column dba_logstdby_events.event is 'A SQL statement or other text describing the event' / comment on column dba_logstdby_events.status is 'A text string describing the event' / comment on column dba_logstdby_events.status_code is 'A number describing the event' / create or replace view dba_logstdby_history as select stream_sequence#, decode(status, 1, 'Past', 2, 'Immediate Past', 3, 'Current', 4, 'Immediate Future', 5, 'Future', 6, 'Canceled', 7, 'Invalid') status, decode(source, 1, 'Rfs', 2, 'User', 3, 'Synch', 4, 'Redo') source, dbid, first_change#, last_change#, first_time, last_time, dgname, spare1 merge_change#, spare2 processed_change# from system.logstdby$history / create or replace public synonym dba_logstdby_history for dba_logstdby_history / grant select on dba_logstdby_history to select_catalog_role / comment on table dba_logstdby_history is 'Information on processed, active, and pending log streams' / comment on column dba_logstdby_history.stream_sequence# is 'Log Stream Identifier' / comment on column dba_logstdby_history.status is 'The processing status of this log stream' / comment on column dba_logstdby_history.dbid is 'The dbid of the logfile provider' / comment on column dba_logstdby_history.first_change# is 'The starting scn for this log stream' / comment on column dba_logstdby_history.last_change# is 'The scn of the last committed transaction' / comment on column dba_logstdby_history.first_time is 'The time associated with first_change#' / comment on column dba_logstdby_history.last_time is 'The time associated with last_change#' / comment on column dba_logstdby_history.dgname is 'The Dataguard name' / comment on column dba_logstdby_history.merge_change# is 'The scn up to and including which was consistent during terminal apply' / comment on column dba_logstdby_history.processed_change# is 'The scn up to which all transactions have been processed' / create or replace view dba_logstdby_eds_tables as select owner, table_name, ctime from system.logstdby$eds_tables / grant select on dba_logstdby_eds_tables to select_catalog_role / create or replace public synonym dba_logstdby_eds_tables for sys.dba_logstdby_eds_tables / comment on table dba_logstdby_eds_tables is 'List of all tables that have EDS-based replication for Logical Standby' / comment on column dba_logstdby_eds_tables.owner is 'Schema name of supported table' / comment on column dba_logstdby_eds_tables.table_name is 'Table name of supported table' / comment on column dba_logstdby_eds_tables.ctime is 'Time that table had EDS added' / Rem View showing all tables that could be supported by EDS interface create or replace view dba_logstdby_eds_supported as select s.owner, s.table_name from user$ u, obj$ o, tab$ t, (select owner, table_name from (select owner,table_name, sum(decode(data_type, 'SDO_GEOMETRY', 1, 0)) sdo, sum(decode(data_type, 'SDO_GEOMETRY', 0, 1)) non_sdo from dba_tab_columns where owner not in (select name from system.logstdby$skip_support where action=0) and data_type != 'NUMBER' and data_type != 'VARCHAR2' and data_type != 'RAW' and data_type != 'DATE' and data_type != 'FLOAT' and data_type != 'INTEGER' and data_type != 'CHAR' group by owner, table_name) where sdo != 0 and non_sdo = 0) s where s.owner = u.name and u.user# = o.owner# and o.name = s.table_name and o.type# = 2 and o.obj# = t.obj# and bitand(t.property, 1) = 0 / grant select on dba_logstdby_eds_supported to select_catalog_role / create or replace public synonym dba_logstdby_eds_supported for sys.dba_logstdby_eds_supported / comment on table dba_logstdby_eds_supported is 'List of all tables that could have EDS-based replication for Logical Standby' / comment on column dba_logstdby_eds_tables.owner is 'Schema name of supportable table' / comment on column dba_logstdby_eds_tables.table_name is 'Table name of supportable table' / Rem Fix (Virtual) Views create or replace view v_$logstdby as select * from v$logstdby; create or replace public synonym v$logstdby for v_$logstdby; grant select on v_$logstdby to select_catalog_role; create or replace view v_$logstdby_stats as select * from v$logstdby_stats; create or replace public synonym v$logstdby_stats for v_$logstdby_stats; grant select on v_$logstdby_stats to select_catalog_role; create or replace view v_$logstdby_transaction as select * from v$logstdby_transaction; create or replace public synonym v$logstdby_transaction for v_$logstdby_transaction; grant select on v_$logstdby_transaction to select_catalog_role; create or replace view v_$logstdby_progress as select * from v$logstdby_progress; create or replace public synonym v$logstdby_progress for v_$logstdby_progress; grant select on v_$logstdby_progress to select_catalog_role; create or replace view v_$logstdby_process as select * from v$logstdby_process; create or replace public synonym v$logstdby_process for v_$logstdby_process; grant select on v_$logstdby_process to select_catalog_role; create or replace view v_$logstdby_state as select * from v$logstdby_state; create or replace public synonym v$logstdby_state for v_$logstdby_state; grant select on v_$logstdby_state to select_catalog_role; Rem Create synonyms for the global fixed views create or replace view gv_$logstdby as select * from gv$logstdby; create or replace public synonym gv$logstdby for gv_$logstdby; grant select on gv_$logstdby to select_catalog_role; create or replace view gv_$logstdby_stats as select * from gv$logstdby_stats; create or replace public synonym gv$logstdby_stats for gv_$logstdby_stats; grant select on gv_$logstdby_stats to select_catalog_role; create or replace view gv_$logstdby_transaction as select * from gv$logstdby_transaction; create or replace public synonym gv$logstdby_transaction for gv_$logstdby_transaction; grant select on gv_$logstdby_transaction to select_catalog_role; create or replace view gv_$logstdby_progress as select * from gv$logstdby_progress; create or replace public synonym gv$logstdby_progress for gv_$logstdby_progress; grant select on gv_$logstdby_progress to select_catalog_role; create or replace view gv_$logstdby_process as select * from gv$logstdby_process; create or replace public synonym gv$logstdby_process for gv_$logstdby_process; grant select on gv_$logstdby_process to select_catalog_role; create or replace view gv_$logstdby_state as select * from gv$logstdby_state; create or replace public synonym gv$logstdby_state for gv_$logstdby_state; grant select on gv_$logstdby_state to select_catalog_role; Rem Populate NOEXP$ to ensure logical standby metadata is not exported delete from sys.noexp$ where name like 'LOGSTDBY$%'; insert into sys.noexp$ select u.name, o.name, o.type# from sys.obj$ o, sys.user$ u where o.type# = 2 and o.owner# = u.user# and u.name = 'SYSTEM' and o.name like 'LOGSTDBY$%'; commit;
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de