Qualifying Database Object Names

Object names must be fully qualified in the parameter file. This means that every name specification must be qualified, not only those supplied as input to Oracle GoldenGate parameter syntax, but also names in a SQL procedure or query that is supplied as SQLEXEC input, names in user exit input, and all other input supplied in the parameter file.

Oracle GoldenGate supports two-part and three-part object names, as appropriate for the database.

Two-part Names

Most databases require only two-part names to be specified, in the following format:

owner.object

For example: HR.EMP

Where:

owner is a schema or database, depending on how the database defines a logical namespace that contains database objects. object is a table or other supported database object.

The databases for which Oracle GoldenGate supports two-part names are as follows, shown with their appropriate two-part naming convention:

  • Db2 for i: schema.object and library/file(member)

  • Db2 LUW: schema.object

  • Db2 on z/OS: schema.object

  • MySQL: database.object

  • Oracle Database (non-CDB databases): schema.object

  • SQL Server: schema.object

  • Teradata: database.object

Three-part Names

Oracle GoldenGate supports three-part names for Oracle container database, only incase of using downstream Extract. However, Oracle GoldenGate supports only per-PDB Extract for Oracle database, in general.

Three-part names are required to capture from a source Oracle container database because one Extract group can capture from more than one container. Thus, the name of the container, as well as the schema, must be specified for each object or objects in an Extract TABLE statement.

Specify a three-part Oracle CDB name as follows:

container.schema.object

For example: PDBEAST.HR.EMP

Applying Data from Multiple Containers or Catalogs

To apply data captured from multiple source containers or catalogs to a target Oracle container database, both three- and two-part names are required. In the MAP portion of the MAP statement, each source object must be associated with a container or catalog, just as it was in the TABLE statement. This enables you (and Replicat) to properly map data from multiple source containers or catalogs to the appropriate target objects. In the TARGET portion of the MAP statement, however, only two-part names are required. This is because Replicat can connect to only one target container or catalog at a time, and schema.owner is a sufficient qualifier. Multiple Replicat groups are required to support multiple target containers or catalogs. Specify the target container or catalog with the TARGETDB parameter.

Specifying a Default Container or Catalog

You can use the SOURCECATALOG parameter to specify a default catalog for any subsequent TABLE, MAP, (or Oracle SEQUENCE) specifications in the parameter file.

The following example shows the use of SOURCECATALOG to specify the default Oracle PDB named pdbeast for region and jobs objects, and the default PDB named pdbwest for appraisal objects. The objects in pdbeast are specified with a fully qualified three-part name, which does not require a default catalog to be specified. 

TABLE pdbeast.hr.emp*; 
SOURCECATALOG pdbeast 
TABLE region.country*; 
TABLE jobs.desg*; 
SOURCECATALOG pdbwest 
TABLE appraisal.sal*;