1 Understanding What’s Supported

This chapter contains support information for Oracle GoldenGate on Oracle Database.

Topics:

1.1 Details of Support for Oracle Data Types and Objects

This topic describes data types, objects and operations that are supported by Oracle GoldenGate.

Within the database, you can use the Dictionary view DBA_GOLDENGATE_SUPPORT_MODE to get information about supported objects. There are different types for replication support:
  • Support by Capturing from Redo

  • Procedural Replication Support

Most data types are supported (SUPPORT_MODE=FULL), which imply that Oracle GoldenGate captures the changes out of the redo. In some unique cases, the information cannot be captured, but the information can be fetched with a connection to the database (SUPPORT_MODE=ID KEY).

From Oracle GoldenGate 21c (21.1.0) onward, DML on tables that are not supported will be automatically skipped when DBA_GOLDENGATE_SUPPORT_MODE.SUPPORT_MODE= NONE is set. However, DDLs for these objects are still captured based on the DDL INCLUDE/EXCLUDE settings. See Supported Objects and Operations in Oracle DDL for DDL support.

Tables supported with ID KEY require a connection to the source database or an ADG Standby database for fetching to support those tables. If using downstream Extract, with NOUSERID you must specify a FETCHUSERID or FETCHUSERIDALIAS connection.

Other changes can be replicated with Procedural Replication (SUPPORT_MODE=PLSQL) that requires additional parameter setting of Extract. See About Procedural Replication for details. In the unlikely case that there is no native support, no support by fetching and no procedural replication support, there is no Oracle GoldenGate support.

To know more information about capture modes, see Deciding Which Capture Method to Use..

Detailed support information for Oracle data types, objects, and operations starts with Details of Support for Objects and Operations in Oracle DML.

Extract Redo Support:

The following data types allow capturing directly from the redo logs and do not require any fetching. If used in a downstream mining configuration, the NOUSERID parameter may be used.
  • NUMBER, BINARY FLOAT, BINARY DOUBLE, and (logical) UROWID

  • DATE and TIMESTAMP

  • CHAR, VARCHAR2, LONG, NCHAR, and NVARCHAR2

  • RAW, LONG RAW, CLOB, NCLOB, BLOB, SECUREFILE, BASICFILE, and BFILE (LOB size limited to 2GB)

  • XML columns stored as CLOB, Binary and Object-Relational (OR)

  • XMLType columns and XMLType tables stored as XML CLOB, XML Object Relational, and XML Binary

  • Native JSON datatype identified by the DTYJSON code.

  • UDTs (user-defined or abstract data types) on BYTE semantics with source database compatibility 12.0.0.0.0 or higher

  • ANYDATA data type with source database compatibility 12.0.0.0.0 or higher

  • Hierarchy-enabled tables are managed by the Oracle XML database repository with source database compatibility 12.2.0.0.0 or higher and enabled procedural replication

  • REF types with source database compatibility 12.2.0.0.0 or higher

  • DICOM with source database compatibility 12.0.0.0.0 or higher

  • SDO _TOPO_GEOMETRY, SDO_GEORASTER, or ST_GEOMETRY with source database compatibility 12.2.0.0.0 or higher and enabled procedural replication

  • Identity columns with source database compatibility 18.1.0.0.0 or higher

  • SDO_RDF_TRIPLE_S with source database compatibility 19.1.0.0.0 or higher

Datatypes Fetched from the Database

Data types listed here are not readable in the redo logs and must be fetched by the Extract process during it's processing. The method for fetching these records is controlled by the use of the FETCHOPTIONS parameter.

It is recommended that the database that is generating the redo data is the same database that Oracle GoldenGate uses to fetch the data. However, if this is not possible, an Active Data Guard Standby database open for read-only can also be used as the fetch database.

SECUREFILE LOBs

  • Modified with DBMS_LOB.FRAGMENT_* procedures

  • NOLOGGING LOBs

  • Deduplicated LOBs with a source database release less than 12gR2

UDTs that contain following data types:

  • TIMESTAMP WITH TIMEZONE, TIMESTAMP WITH LOCAL TIMEZONE

  • INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND

  • BINARY FLOAT, BINARY DOUBLE

  • BFILE

Object tables contain the following attributes:
  • Nested table

  • SDO_TOP_GEOMETRY

  • SDO_GEORASTER

Additional Considerations

  • NUMBER can be up to the maximum size permitted by Oracle. The support of the range and precision for floating-point numbers depends on the host machine. In general, the precision is accurate to 16 significant digits, but you should review the database documentation to determine the expected approximations. Oracle GoldenGate rounds or truncates values that exceed the supported precision.

  • Non-logical UROWID columns will be identified by Extract. A warning message is generated in the report file. The column information is not part of the trail record. All other supported datatypes of the record are part of the trail record and are replicated.

  • TIMESTAMP WITH TIME ZONE as TZR (region ID) for initial loads, SQLEXEC or operations where the column can only be fetched from the database. In those cases, the region ID is converted to a time offset by the source database when the column is selected. Replicat applies the timestamp as date and time data into the target database with a time offset value.

  • VARCHAR expansion from 4K to 32K (extended or long VARCHAR)
    • 32K long columns cannot be used as row identifiers:
      • Columns as part of a key or unique index

      • Columns in a KEYCOLS clause of the TABLE or MAP parameter.

    • 32K long columns as resolution columns in a CDR (conflict resolution and detection)

    • If an extended VARCHAR column is part of unique index or constraint, then direct path inserts to this table may cause Replicat to abend with a warning. Verify that the extended VARCHAR caused the abend by checking ALL_INDEXES or ALL_IND_COLUMNS for a unique index or ALL_CONS_COLUMNS or ALL_CONSTRAINTS for a unique constraint. Once you determine that an extended VARCHAR, you can temporarily drop the index or disable the constraint:
      • Unique Index: DROP INDEX index_name;

      • Unique Constraint: ALTER TABLE table_name MODIFY CONSTRAINT constraint_name DISABLE;

  • BFILE column are replicating the locator. The file on the server file system outside of the database and is not replicated
  • Multi-byte character data: The source and target databases must be logically identical in terms of schema definition for the tables and sequences being replicated. Transformation, filtering, and other manipulation cannot be used.

  • The character sets between the two databases must be one of the following:

    • Identical on the source and on the target

    • Equivalent, which is not the same character set but containing the same set of characters

    • Target is a superset of the source

    Multi-byte data can be used in any semantics: bytes or characters.

  • UDTs can have different source and target schemas. UDTs, including values inside object columns or rows, cannot be used within filtering criteria in TABLE or MAP statements, or as input or output for the Oracle GoldenGate column-conversion functions, SQLEXEC, or other built-in data manipulation tools. Support is only provided for like-to-like Oracle source and targets.

    To fully support object tables created using the CREATE TABLE as SELECT (CTAS) statement, Extract must be configured to capture DML from the CTAS statement. Oracle object table can be mapped to a non-Oracle object table in a supported target database.

  • XML column type cannot be used for filtering and manipulation. You can map the XML representation of an object to a character column by means of a COLMAP clause in a TABLE or MAP statement.

    Oracle recommends the AL32UTF8 character set as the database character set when working with XML data. This ensures the correct conversion by Oracle GoldenGate from source to target. With DDL replication enabled, Oracle GoldenGate replicates the CTAS statement and allows it to select the data from the underlying target tables. OIDs are preserved if TRANLOGOPTIONS GETCTASDML parameter is set. For XMLType tables, the row object IDs must match between source and target.

  • For JSON datatype, DTYJSON is stored in the binary JSON format for query and space efficiency as well as transportability between platforms. A column with JSON data as text is declared using any of the text data types (VARCHAR2, CLOB) and the IS JSON constraint. JSON datatype is supported by Oracle GoldenGate Extract, and Replicat processes along with XStream Out, XStream In processes. JSON support limits the inline text JSON to 4K to prevent Replicat from abending.

    By default Extract writes native JSON columns in text format but using binary_json_format parameter forces to write in native format. So, this paramater must not be set for VARCHAR2, NVARVAR2, CLOB, NCLOB. The parameter is not set by default. If you are only replicating from Oracle to Oracle you can set the parameter and gain a bit of performance. Also the Column manipulation functions like str are supported only for text JSON.

  • It is recommended that de-duplication is removed for LOB data types on the target database. If DEDUPLICATION is left enabled, it causes severe performance impact on the apply side.

SQLEXEC Limitations

There might be a few cases where replication support exists, but there are limitations of processing such as in case of using SQLEXEC. The following table lists these limitations:

Datatypes Supported By SQLEXEC Support Limitations

NUMBER, BINARY FLOAT, BINARY DOUBLE UROWID

Special cases of:
  • XML types

  • UDTs

  • Object tables

  • Collections or nested tables

(N)CHAR, (N) VARCHAR2 LONG, RAW, LONG RAW (N)CLOB, CLOB, BLOB, SECUREFILE, BASICFILE and BFILE

Not supported

XML columns, XMLType

Not supported

Native JSON datatype

VARCHAR2, NVARCHAR2, CLOB, NCLOB not supported with the Extract parameter binary_json_format.

UDT

Not supported

ANYDATA

Not supported

Hierarchy-enabled tables

Not supported

RET Types

Not supported

DICOM

Not supported

SDO_TOPO_GEOMETRY, SDO_GEORASTER

Not supported

Identity columns

Not supported

SDO_RDF_TRIPLE_S

Not supported

Note:

SECUREFILE LOBs updated using DBMS_LOG.FRAGMENT or SECUREFILE LOBs that are set to NOLOGGING are fetched instead of read from the redo.

Note:

Any datatype not listed in the table is fully supported by SQLEXEC with the same limitations as the regular product.

1.1.1 Non-Supported Oracle Data Types

Oracle GoldenGate does not support the following data types.

  • Time offset values outside the range of +12:00 and -12:00..Oracle GoldenGate supports time offset values between +12:00 and -12:00.

  • Tables that only contain a single column and that column one of the following:

    • UDT

    • LOB (CLOB, NCLOB, BLOB, BFILE)

    • XMLType column

    • VARCHAR2 (MAX) where the data is greater than 32KB

  • Tables with LOB, UDT, XML, or XMLType column without one of the following:

    • Primary Key

    • Scalar columns with a unique constraint or unique index

    Table where the combination of all scalar columns do not guarantee uniqueness are unsupported.

  • Tables with the following XML characteristics:
    • Tables with a primary key constraint made up of XML attributes

    • XMLType tables with a primary key based on an object identifier (PKOID).

    • XMLType tables, where the row object identifiers (OID) do not match between source and target

    • XMLType tables created by an empty CTAS statement.

    • XML schema-based XMLType tables and columns where changes are made to the XML schema (XML schemas must be registered on source and target databases with the dbms_xml package).

    • The maximum length for the entire SET value of an update to an XMLType larger than 32K, including the new content plus other operators and XQuery bind values.

    • SQL*Loader direct-path insert for XML-Binary and XML-OR.

  • Tables with following UDT characteristics:

    • UDTs that contain CFILE or OPAQUE (except of XMLType)

    • UDTs with CHAR and VARCHAR attributes that contain binary or unprintable characters

    • UDTs using the RMTTASK parameter

  • UDTs and nested tables with following condition:

    • Nested table UDTs with CHAR, NVARCHAR2 or NCLOB attributes.

    • Nested tables with CLOB, BLOB, extended (32k) VARCHAR2 or RAW attributes in UDTs.

    • Nested table columns/attributes that are part of any other UDT.

  • When data in a nested table is updated, the row that contains the nested table must be updated at the same time. Otherwise there is no support.

  • When VARRAYS and nested tables are fetched, the entire contents of the column are fetched each time, not just the changes. Otherwise there is no support.

  • Object table contains the following attributes:

    • Nested table

    • SDO_TOPO_GEOMETRY

    • SDO_GEORASTER

See additional exclusions in Details of Support for Oracle Data Types and Objects.

1.2 Details of Support for Different Oracle Editions

This topic describes the different editions of Oracle database supported with the current Oracle GoldenGate release.

Oracle Database Express Edition (XE) is supported for delivery only and does not support any of the integrated features such as integrated Replicat or parallel Replicat in integrated mode.

Oracle Database Standard Edition 2 (SE2) is supported, with the following limitations:
  • Integration with RMAN (LOGRETENTION parameter) does not work because RMAN isn't supported on SE2.

  • Extract, integrated Replicat, and parallel Replicat in integrated mode are limited to a single thread.

Oracle Database Enterprise Edition (EE) has full Oracle GoldenGate functionality.

Oracle Database Personal Edition (PE) is supported for delivery only, and does not support any of the integrated features such as integrated or parallel Replicat in integrated mode.

1.3 Details of Support for Objects and Operations in Oracle DML

This section outlines the Oracle objects and operations that Oracle GoldenGatesupports for the capture and replication of DML operations.

Supported Objects and Operations in Oracle DML

Topics:

1.3.1 Multitenant Container Database

Oracle GoldenGate captures from, and delivers to, a multitenant container database, see Configuring Oracle GoldenGate in a Multitenant Container Database.

1.3.2 Tables, Views, and Materialized Views

Oracle GoldenGate supports the following DML operations made to regular tables, index-organized tables, clustered tables, and materialized views.

  • INSERT

  • UPDATE

  • DELETE

  • Associated transaction control operations

Tip:

You can use the DBA_GOLDENGATE_SUPPORT_MODE data dictionary view to display information about the level of Oracle GoldenGate capture process support for the tables in your database. The PLSQL value of DBA_GOLDENGATE_SUPPORT_MODE indicates that the table is supported natively, but requires procedural supplemental logging. For more information, see the DBA_GOLDENGATE_SUPPORT_MODE. If you need to display all tables that have no primary and no non-null unique indexes, you can use the DBA_GOLDENGATE_NOT_UNIQUE. For more information, see DBA_GOLDENGATE_NOT_UNIQUE.

1.3.2.1 Limitations of Support for Regular Tables

These limitations apply to Extract.

  • Oracle GoldenGate supports tables that contain any number of rows.

  • A row can be up to 4 MB in length. If Oracle GoldenGate is configured to include both the before and after image of a column in its processing scope, the 4 MB maximum length applies to the total length of the full before image plus the length of the after image. For example, if there are UPDATE operations on columns that are being used as a row identifier, the before and after images are processed and cannot exceed 4 MB in total. Before and after images are also required for columns that are not row identifiers but are used as comparison columns in conflict detection and resolution (CDR). Character columns that allow for more than 4 KB of data, such as a CLOB, only have the first 4 KB of data stored in-row and contribute to the 4MB maximum row length. Binary columns that allow for more than 4kb of data, such as a BLOB the first 8 KB of data is stored in-row and contributes to the 4MB maximum row length.

  • Oracle GoldenGate supports the maximum number of columns per table that is supported by the database.

  • Oracle GoldenGate supports the maximum column size that is supported by the database.

  • Oracle GoldenGate supports tables that contain only one column, except when the column contains one of the following data types:

    • LOB

    • LONG

    • LONG VARCHAR

    • Nested table

    • User Defined Type (UDT)

    • VARRAY

    • XMLType

  • Set DBOPTIONS ALLOWUNUSEDCOLUMN before you replicate from and to tables with unused columns.

  • Oracle GoldenGate supports tables with these partitioning attributes:

    • Range partitioning

    • Hash Partitioning Interval Partitioning

    • Composite Partitioning

    • Virtual Column-Based Partitioning

    • Reference Partitioning

    • List Partitioning

  • Oracle GoldenGate supports tables with virtual columns, but does not capture change data for these columns or apply change data to them: The database does not write virtual columns to the transaction log, and the Oracle Database does not permit DML on virtual columns. For the same reason, initial load data cannot be applied to a virtual column. You can map the data from virtual columns to non-virtual target columns.

  • Oracle GoldenGate will not consider unique/index with virtual columns.

  • Oracle GoldenGate supports replication to and from Oracle Exadata. To support Exadata Hybrid Columnar Compression, the source database compatibility must be set to 11.2.0.0.0 or higher.

  • Oracle GoldenGate supports Transparent Data Encryption (TDE).

  • Oracle GoldenGate supports TRUNCATE statements as part of its DDL replication support, or as standalone functionality that is independent of the DDL support.

  • Oracle GoldenGate supports the capture of direct-load INSERT, with the exception of SQL*Loader direct-path insert for XML Binary and XML Object Relational. Supplemental logging must be enabled, and the database must be in archive log mode. The following direct-load methods are supported.

    • /*+ APPEND */ hint

    • /*+ PARALLEL */ hint (Not supported for RAC in classic capture mode)

    • SQLLDR with DIRECT=TRUE

  • Oracle GoldenGate fully supports capture from compressed objects for Extract.

  • Oracle GoldenGate supports XA and PDML distributed transactions in integrated capture mode.

  • Oracle GoldenGate supports DML operations on tables with FLASHBACK ARCHIVE enabled. However, Oracle GoldenGate does not support DDL that creates tables with the FLASHBACK ARCHIVE clause or DDL that creates, alters, or deletes the flashback data archive itself.

1.3.2.2 Limitations of Support for Views

These limitations apply to Extract.

  • Oracle GoldenGate supports capture from a view when Extract is in initial-load mode (capturing directly from the source view, not the redo log).

  • Oracle GoldenGate does not capture change data from a view, but it supports capture from the underlying tables of a view.

  • Oracle GoldenGate can replicate to a view as long as the view is inherently updateable. The structures of the source tables and a target view must be identical.

1.3.2.3 Limitations of Support for Materialized Views

Materialized views are supported by Extract with the following limitations.

  • Materialized views created WITH ROWID are not supported.

  • The materialized view log can be created WITH ROWID.

  • The source table must have a primary key.

  • Truncates of materialized views are not supported. You can use a DELETE FROM statement.

  • DML (but not DDL) from a full refresh of a materialized view is supported. If DDL support for this feature is required, open an Oracle GoldenGate support case.

  • For Replicat the Create MV command must include the FOR UPDATE clause

  • Either materialized views can be replicated or the underlying base table(s), but not both.

1.3.2.4 Limitations of Support for Clustered Tables

Indexed clusters are supported by Extract while hash clusters are not supported.

1.3.3 System Partitioning

System partitioning is an Oracle database feature that allows a table to be created with named partitions. A system partitioned table is not maintained by the database. Each DML must specify the partition where the row is to reside. Integrated Extract using Integrated Dictionary and all configurations of Replicat support system partitioning as of Oracle GoldenGate 12c (12.3.0.1). Each trail file record header pertaining to a system partitioned table includes the partition name. From Oracle GoldenGate 21c onward, a Partition Name Record (PNR) is generated for system partitioned tables, if it is included in the PARTITION parameter.

See PARTITION | PARTITIONEXCLUDE in the Reference for Oracle GoldenGate.

1.3.4 Sequences and Identity Columns

  • Identity columns are supported from Oracle database 18c onward and requires Extract, Parallel Replicat in Integrated mode, or Integrated Replicat.

  • Oracle GoldenGate supports the replication of sequence values and identity columns in a unidirectional and active-passive high-availability configuration.

  • Oracle GoldenGate ensures that the target sequence values will always be higher than those of the source (or equal to them, if the cache is zero).

1.3.4.1 Limitations of Support for Sequences

These limitations apply to Extract.

  • Oracle GoldenGate does not support the replication of sequence values in an active-active bi-directional configuration.

  • The cache size and the increment interval of the source and target sequences must be identical. The cache can be any size, including 0 (NOCACHE).

  • The sequence can be set to cycle or not cycle, but the source and target databases must be set the same way.

  • Tables with default sequence columns are excluded from replication for Extract.

1.3.5 Non-supported Objects and Operations in Oracle DML

The following are additional Oracle objects or operations that are not supported by Extract:

  • REF  are supported natively for compatibility with Oracle Database 12.2 and higher, but not primary-key based REFs (PKREFs)

  • Sequence values in an active-active bi-directional configuration

  • Database Replay

  • Tables created as EXTERNAL

1.4 Details of Support for Objects and Operations in Oracle DDL

This topic outlines the Oracle objects and operation types that Oracle GoldenGate supports for the capture and replication of DDL operations.

Topics:

1.4.1 Supported Objects and Operations in Oracle DDL

DDL capture support is integrated into the database logmining server. You must set the database parameter compatibility to 11.2.0.4.0. Extract supports DDL that includes password-based column encryption, such as:

  • CREATE TABLE t1 (a number, b varchar2(32) ENCRYPT IDENTIFIED BY my_password);

  • ALTER TABLE t1 ADD COLUMN c varchar2(64) ENCRYPT IDENTIFIED BY my_password;

The following additional statements apply to Extract with respect to DDL support.

  • All Oracle GoldenGate topology configurations are supported for Oracle DDL replication.

  • Active-active (bi-directional) replication of Oracle DDL is supported between two (and only two) databases that contain identical metadata.

  • Oracle GoldenGate supports DDL on the following objects:

    • clusters

    • directories

    • functions

    • indexes

    • packages

    • procedure

    • tables

    • tablespaces

    • roles

    • sequences

    • synonyms

    • triggers

    • types

    • views

    • materialized views

    • users

    • invisible columns

  • Oracle Edition-Based Redefinition (EBR) database replication of Oracle DDL is supported for Extract for the following Oracle Database objects:

    • functions

    • library

    • packages (specification and body)

    • procedure

    • synonyms

    • types (specification and body)

    • views

  • From Oracle GoldenGate 21c onward, DDLs that are greater than 4 MB in size will be provided replication support.

  • Oracle GoldenGate supports Global Temporary Tables (GTT) DDL operations to be visible to Extract so that they can be replicated. You must set the DDLOPTIONS parameter to enable this operation because it is not set by default.

  • Oracle GoldenGate supports dictionary for use with NOUSERID and TRANLOGOPTIONS GETCTASDML. This means that Extract receives object metadata from the LogMiner dictionary without querying the dictionary objects. Oracle GoldenGate uses the dictionary automatically when the source database compatibility parameter is greater than or equal to 11.2.0.4.

    When using dictionary and trail format in the Oracle GoldenGate release 12.2.x, Extract requires the Logminer patch to be applied on the mining database if the Oracle Database release is earlier than 12.1.0.2.

  • Oracle GoldenGate supports replication of invisible columns in Extract. Trail format release 12.2 is required. Replicat must specify the MAPINVISIBLECOLUMNS parameter or explicitly map to invisible columns in the COLMAP clause of the MAP parameter.

    If SOURCEDEFS or TARGETDEFS is used, the metadata format of a definition file for Oracle tables must be compatible with the trail format. Metadata format 12.2 is compatible with trail format 12.2, and metadata format earlier than 12.2 is compatible with trail format earlier than 12.2. To specify the metadata format of a definition file, use the FORMAT RELEASE option of the DEFSFILE parameter when the definition file is generated in DEFGEN.

  • DDL statements to create a namespace context (CREATE CONTEXT) are captured by Extract and applied by Replicat.

  • Extract in pump mode supports the following DDL options:

    • DDL INCLUDE ALL

    • DDL EXCLUDE ALL

    • DDL EXCLUDE OBJNAME

    The SOURCECATALOG and ALLCATALOG option of DDL EXCLUDE is also supported.

    If no DDL parameter is specified, then all DDLs are written to trail. If DDL EXCLUDE OBJNAME is specified and the object owner is does not match an exclusion rule, then it is written to the trail.

  • Starting with Oracle database 21c, the following DDL is available to support blocking of DML/DDL changes that are not replicated by Oracle GoldenGate:

    ALTER DATABASE [ENABLE | DISABLE] goldengate blocking mode;
    When Oracle GoldenGate blocking mode is enabled, DMLs that use support_mode NONE in tables and execute unsupported Oracle PL/SQL statements will fail with the following error:
    ORA-26981: "operation was unsupported during Oracle GoldenGate blocking mode"
    For Oracle database 21c, the following features cause a table to have support_mode NONE in Oracle GoldenGate:
    • BFILE as an attribute of ADT column, or typed table

    • Table with no scalars

    • OLAP AW$ table

    • Sharded queue table

    • Sorted Hash Cluster Table

    • Primary key constraint on ADT attribute in relational table

    • Primary key/unique key constraint on long raw/varchar (over 4000 bytes)

    • V$DATABASE column, Goldengate_Blocking_Mode can be queried to determine the current blocking mode status.

  • The following operations are supported for partition related DDLs and partition maintenance operations

    • Drop partition:

      If a partition is recreated with the same name, then it will get a new object number. The internal caches are cleared to minimize space consumption when a drop partition DDL is processed.

    • Truncate partition:

      Partition name and object number stays the same. Base table object version stays the same.

    • Rename partition:

      The partition object number stays the same but gets a new name. The base table's object version gets bumped. In memory name cache will get invalidated upon seeing this DDL and repopulated upon the next DML. The cache, which stores if a given partition object number is interesting or not will also need to be reevaluated as a the new partition name may switch from filtered to not filtered or vice versa.

    • Exchange partition:

      Exchanges data in a partition with that in a table or vice versa. The obj# of the partition being exchanged does not change. Dataobj# does change but is not used by Extract. The partition itself still belongs to the same table.

    • Merge partition:

      Merges one or more partitions into a new partition. The DDL creates the new partition and drops the partitions from which it was merged. In memory caches should be cleared to save space and the user should ensure proper filter rules for the newly created partition.

    • Split partition:

      The partition being split keeps its original name and object number and new partition is created for the split data. The user must ensure partition filter rules are correct for the newly created partition.

    • Coalesce partition:

      Reduces the number of partitions in a hash partitioned table. The specific partition that is coalesced is selected by the database, and is dropped after its contents have been redistributed. The remaining partitions keep their same name and object number. The internal caches should be cleared to minimize space consumption.

    • Modify partition:

      Modifies default and real attributes of partitions, apart from adding or dropping of values for list partitions. All modifications leave the partitions name and object number intact.

    • Move partition:

      Partition data is moved to a new tablespace. Partition name and number remain the same.

    • Redef table:

      dbms_redefinition can be used to partition a table through the use of an interim table. The partitions are created on the interim table and after the finish_redef operation, the tables swap names. The partitions created on the interim table keep their names and object numbers when the tables are swapped. The Extract filter cache, needs to be reevaluated upon finish_redef as the partitions now belong to the base table. The user must ensure proper filter rules.

    • Redef partition:

      When redefining a table, the partitions follow from the original table to the interim table. For example, consider the case where the original table has partitions, which live in the USER tablespace, and the interim table is created with no partitions and the table lives in the NEW tablespace. In this case, after the finish_redef operation, when the tables are swapped the partition still lives in the USER tablespace. Redef partition allows a partition to be moved to the interim table's NEW tablespace. The partition retains its name and object number.

    • System generated partition names:

      When partitions are created automatically for hash partitions and operations such as split partition, the partition name is in the form of SYS_P sequence value. Similarly, subpartitions are of the form SYS_SUBP sequence value. It is recommended that the partition is renamed before excepting DML to conform to filter rules.

1.4.2 Non-supported Objects and Operations in Oracle DDL

Here's a list of non-supported objects and operations in Oracle DDL.

Topics:

1.4.2.1 Excluded Objects

The following names or name prefixes are considered Oracle-reserved and must be excluded from the Oracle GoldenGate DDL configuration. Oracle GoldenGate will ignore objects that contain these names.

Excluded schemas:

  "ANONYMOUS", // HTTP access to XDB
  "APPQOSSYS", // QOS system user
  "AUDSYS", // audit super user
  "BI", // Business Intelligence
  "CTXSYS", // Text
  "DBSNMP", // SNMP agent for OEM
  "DIP", // Directory Integration Platform
  "DMSYS", // Data Mining
  "DVF", // Database Vault
  "DVSYS", // Database Vault
  "EXDSYS", // External ODCI System User
  "EXFSYS", // Expression Filter
  "GSMADMIN_INTERNAL", // Global Service Manager
  "GSMCATUSER", // Global Service Manager
  "GSMUSER", // Global Service Manager
  "LBACSYS", // Label Security
  "MDSYS", // Spatial
  "MGMT_VIEW", // OEM Database Control
  "MDDATA",
  "MTSSYS", // MS Transaction Server
  "ODM", // Data Mining
  "ODM_MTR", // Data Mining Repository
  "OJVMSYS", // Java Policy SRO Schema
  "OLAPSYS", // OLAP catalogs
  "ORACLE_OCM", // Oracle Configuration Manager User
  "ORDDATA", // Intermedia
  "ORDPLUGINS", // Intermedia
  "ORDSYS", // Intermedia
  "OUTLN", // Outlines (Plan Stability)
  "SI_INFORMTN_SCHEMA", // SQL/MM Still Image
  "SPATIAL_CSW_ADMIN", // Spatial Catalog Services for Web
  "SPATIAL_CSW_ADMIN_USR",
  "SPATIAL_WFS_ADMIN", // Spatial Web Feature Service
  "SPATIAL_WFS_ADMIN_USR",
  "SYS",
  "SYSBACKUP",
  "SYSDG",
  "SYSKM",
  "SYSMAN", // Adminstrator OEM
  "SYSTEM",
  "TSMSYS", // Transparent Session Migration
  "WKPROXY", // Ultrasearch
  "WKSYS", // Ultrasearch
  "WK_TEST",
  "WMSYS", // Workspace Manager
  "XDB", // XML DB
  "XS$NULL",
  "XTISYS", // Time Index

Special schemas:

  "AURORA$JIS$UTILITY$", // JSERV
  "AURORA$ORB$UNAUTHENTICATED", // JSERV
  "DSSYS", // Dynamic Services Secured Web Service
  "OSE$HTTP$ADMIN", // JSERV
  "PERFSTAT", // STATSPACK
  "REPADMIN",
  "TRACESVR" // Trace server for OEM

Excluded tables (the * wildcard indicates any schema or any character):

  "*.AQ$*", // advanced queues
  "*.DR$*$*", // oracle text
  "*.M*_*$$", // Spatial index
  "*.MLOG$*", // materialized views
  "*.OGGQT$*",
  "*.OGG$*", // AQ OGG queue table
  "*.ET$*", // Data Pump external tables
  "*.RUPD$*", // materialized views
  "*.SYS_C*", // constraints
  "*.MDR*_*$", // Spatial Sequence and Table
  "*.SYS_IMPORT_TABLE*",
  "*.CMP*$*", // space management, rdbms >= 12.1
  "*.DBMS_TABCOMP_TEMP_*", // space management, rdbms < 12.1
  "*.MDXT_*$*" // Spatial extended statistics tables
1.4.2.2 Other Non-supported DDL

Oracle GoldenGate does not support the following:

  • DDL on nested tables.

  • DDL on identity columns.

  • ALTER DATABASE and ALTER SYSTEM (these are not considered to be DDL) Using dictionary, you can replicate ALTER DATABASE DEFAULT EDITION and ALTER PLUGGABLE DATABASE DEFAULT EDITION. All other ALTER [PLUGABLE] DATABASE commands are ignored.

  • DDL on a standby database.

  • Database link DDL.

  • DDL that creates tables with the FLASHBACK ARCHIVE clause and DDL that creates, alters, or deletes the flashback data archive itself. DML on tables with FLASHBACK ARCHIVE is supported.

  • Some DDL will generate system generated object names. The names of system generated objects may not always be the same between two different databases. So, DDL operations on objects with system generated names should only be done if the name is exactly the same on the target.