DDLOPTIONS
Valid For
Extract and Replicat
Description
Use the DDLOPTIONS
parameter to configure aspects of DDL processing other than filtering and string substitution. You can use multiple DDLOPTIONS
statements, but using one is recommended. If using multiple DDLOPTIONS
statements, make each of them unique so that one does not override the other. Multiple DDLOPTIONS
statements are executed in the order listed in the parameter file.
Default
See the argument descriptions
Syntax
DDLOPTIONS [, DEFAULTUSERPASSWORD password [algorithm [ENCRYPTKEY DEFAULT | ENCRYPTKEY key_name] [, CAPTUREGLOBALTEMPTABLE ] [, DEFAULTUSERPASSWORDALIAS alias [DOMAINdomain
] ] [, EXCLUDETAG [tag
| + | NULL] [, IGNOREMAPPING [, INCLUDETAG tag | +] [, MAPDERIVED | NOMAPDERIVED] [, MAPSCHEMAS] [, MAPSESSIONSCHEMA source_schema TARGET target_schema] [, NLSLENGTHSEMANTICS CHAR | BYTE | DEFAULT] [, NOAPPLYGLOBALTEMPTABLE] [, NOTAG] [, PASSWORD algorithm ENCRYPTKEY {key_name | DEFAULT}] [, REMOVECOMMENTS {BEFORE | AFTER}] [, REPLICATEPASSWORD | NOREPLICATEPASSWORD] [, REPORT | NOREPORT] [, UPDATEMETADATA] [, USEPASSWORDVERIFIERLEVEL {10|11}] [, _USEOWNERFORSESSION]
-
DEFAULTUSERPASSWORD
password
[
algorithm
ENCRYPTKEY {
key_name
| DEFAULT}]
-
Valid for Replicat. (Oracle only)
Can be used instead of the
DEFAULTUSERPASSWORDALIAS
option if an Oracle GoldenGate credential store is not being used. Specifies a different password for a replicated{CREATE | ALTER} USER
name
IDENTIFIED BY
password
statement from the one used in the source statement. Replicat will replace the placeholder that Extract writes to the trail with the specified password. When usingDEFAULTUSERPASSWORD
, use theNOREPLICATEPASSWORD
option ofDDLOPTIONS
for Extract.DEFAULTUSERPASSWORD
password
without options specifies a clear-text password. If the password is case-sensitive, type it that way.Note:
Replication of CREATE | ALTER PROFILE will fail as the profile/password verification function must exist in the SYS schema. To replicate these DDLs successfully, password verification function must be created manually on both source/target(s) since DDL to SYS schema is excluded.Use the following options if the password was encrypted with the
ENCRYPT PASSWORD
command:-
algorithm
-
Specifies the encryption algorithm that was used to encrypt the password with the
ENCRYPT PASSWORD
command:AES128
, orAES192
,AES256
. -
ENCRYPTKEY
key_name
-
Specifies the logical name of a user-created encryption key in the
ENCKEYS
lookup file. Use ifENCRYPT PASSWORD
was used with theKEYNAME
key_name
option, and specify the same key name. -
ENCRYPTKEY DEFAULT
-
Directs Oracle GoldenGate to use a random key. Use if
ENCRYPT PASSWORD
was used with theKEYNAME
DEFAULT
option.
-
-
CAPTUREGLOBALTEMPTABLE
-
Valid for Oracle
Allows Global Temporary Tables (GTT) DDLs to be visible to Extract so that they can be replicated. By default, GTT DDLs are not visible to Extract so using
CAPTUREGLOBALTEMPTABLE
you can set Extract to include GTT DDLs that then can be filtered by the DDL statement and if passed, written to the trail.The GTT DDLs that are present in the trail will be filtered and executed by the Replicat if they pass the filtering criteria.
For trigger-version of Extract, this option is set to false regardless of whether the table is GTT or not.
-
DEFAULTUSERPASSWORDALIAS
alias
[DOMAIN
domain
]
-
Valid for Replicat. (Oracle only)
Can be used instead of the
DEFAULTUSERPASSWORD
option if an Oracle GoldenGate credential store is being used. Specifies the alias of a credential whose password replaces the one in theIDENTIFIED BY
clause of a replicatedCREATE USER
orALTER USER
statement. The alias is resolved to the encrypted password in the Oracle GoldenGate credential store. Replicat replaces the placeholder that Extract writes to the trail with the resolved password before applying the DDL to the target.When using
DEFAULTUSERPASSWORDALIAS
, use theNOREPLICATEPASSWORD
option ofDDLOPTIONS
for Extract.-
alias
-
Specifies the alias of the credential whose password will be used for the replacement password. This credential must exist in the Oracle GoldenGate credential store. If you are not sure what alias to use, you can inspect the content of the credential store by issuing the
INFO CREDENTIALSTORE
command. -
DOMAIN
domain
-
Specifies the domain that is assigned to the specified user in the credential store.
-
-
[EXCLUDETAG
[tag
| + | NULL]
-
Note:
Starting with Oracle GoldenGate 23ai, the syntax for using the
EXCLUDETAG
andINCLUDETAG
option has been enhanced and would no longer use theGETREPLICATES
,IGNOREREPLICATES
,GETAPPLOPS
, andIGNOREAPPLOPS
options.Use
EXCLUDETAG
tag
to direct the Extract process to ignore the individual records that are tagged with the specified redo tag. Compare with older versions, new trail file contains tag tokens, which would not introduce problems for older trail readers.Use
EXCLUDETAG +
to direct the Extract process to ignore the individual records that are tagged with any redo tag.The
EXCLUDETAG
is used to exclude changes that were earlier tagged either by Replicat using theDBOPTIONS SET TAG
option or within the Oracle database session using thedbms_xstream.set_tag
procedure. - Example
- The following are examples of how to use tag specifiers with
EXCLUDETAG
. -
INCLUDETAG [tag | +]
-
Valid for integrated Extract.
Use
INCLUDETAG
tag
to include specific changes trail files. The tag value can be up to 2000 hexadecimal digits (0-9 A-F).When specifying both
EXCLUDETAG
andINCLUDETAG
parameters with theDDLOPTIONS
command, theEXCLUDETAG
should come first.Example:
To include all tagged changes:
DDLOPTIONS INCLUDETAG +
To include specific tagged changes:
DDLOPTIONS INCLUDETAG 00
-
IGNOREMAPPING
-
Valid for Replicat. Disables the evaluation of name mapping that determines whether DDL is of
MAPPED
orUNMAPPED
scope. This option improves performance in like-to-like DDL replication configurations, where source and target schema names and object names match, and where mapping functions are therefore unnecessary. WithIGNOREMAPPING
enabled,MAPPED
orUNMAPPED
scope cannot be determined, so all DDL statements are treated asOTHER
scope. Do not use this parameter when source schemas and object names are mapped to different schema and object names on the target. -
MAPDERIVED | NOMAPDERIVED
-
Valid for Replicat (Oracle). Controls how derived object names are mapped.
-
MAPDERIVED
-
If a
MAP
statement exists for the derived object, the name is mapped to the name specified in thatTARGET
clause. Otherwise, the name is mapped to the name specified in theTARGET
clause of theMAP
statement that contains the base object.MAPDERIVED
is the default. -
NOMAPDERIVED
-
Prevents name mapping.
NOMAPDERIVED
overrides any explicitMAP
statements that contain the name of the derived object.
For more information about how derived objects are handled during DDL replication, see the How Oracle GoldenGate Handles Derived Object Names.
-
-
MAPSCHEMAS
-
Valid for Replicat (Oracle and Teradata). Use only when
MAPSESSIONSCHEMA
is used.-
MAPSESSIONSCHEMA
establishes a source-target mapping for session schemas and is used for objects whose schemas are not qualified in the DDL. -
MAPSCHEMAS
maps objects that do have qualified schemas in the source DDL, but which do not qualify for mapping withMAP
, to the same session-schema mapping as inMAPSESSIONSCHEMA
. Examples of such objects are the OracleCREATE TABLE AS SELECT
statement, which contains a derived object in theAS SELECT
clause, or the TeradataCREATE REPLICATION RULESET
statement.
This mapping takes place after the mapping that is specified in the
MAP
statement.As an example, suppose the following DDL statement is issued on a source Oracle database:
create table a.t as select from b.t;
Suppose the
MAP
statement on the target is as follows:MAP a.*, TARGET c.*; DDLOPTIONS MAPSESSIONSCHEMA b, TARGET b1, MAPSCHEMAS
As a result of this mapping, Replicat issues the following DDL statement on the target:
create table c.t as select from b1.t;
-
The base table gets mapped according to the
TARGET
clause (to schemac
). -
The qualified derived object (table
t
inSELECT FROM
) gets mapped according toMAPSESSIONSCHEMA
(to schemab1
) becauseMAPSCHEMAS
is present.
Without
MAPSCHEMAS
, the derived object would get mapped to schemac
(as specified in theTARGET
clause), becauseMAPSESSIONSCHEMA
alone only maps unqualified objects. -
-
MAPSESSIONSCHEMA
source_schema
TARGET
target_schema
-
Valid for Replicat (Oracle only). Enables a source session schema to be mapped to (transformed to) a different session schema on the target.
-
source_schema
is the session schema that is set withALTER SESSION set CURRENT_SCHEMA
on the source. -
target_schema
is the session schema that is set withALTER SESSION set CURRENT_SCHEMA
on the target.
Wildcards are not supported. You can use multiple
MAPSESSIONSCHEMA
parameters to map different schemas.MAPSESSIONSCHEMA
overrides any mapping of schema names that is based on master or derived object namesSee the example at the end of this topic for usage.
See also
MAPSCHEMAS
. -
-
NLSLENGTHSEMANTICS CHAR | BYTE | DEFAULT]
-
Valid for Replicat (Oracle only).
Allows Replicat to override the
NLS_LENGTH_SEMANTICS
Oracle database session parameter setting that is part of the DDL replication trail file record. For example, if the DDL is run in a database session withNLS_LENGTH_SEMANTICS
parameter value asBYTE
, then by default, before Replicat applies the DDL, it will set the database session parameterNLS_LENGTH_SEMANTICS
toBYTE
. To override this, you can setNLSLENGTHSEMANTICS
parameter toCHAR
, and before the DDL is applied, theNLS_LENGTH_SEMANTICS
session parameter is set toCHAR
. -
NOAPPLYGLOBALTEMPTABLE
-
Prevents Replicat from applying Global Temporary Tables (GTT). If this option is not specified, then Replicat will always apply DDLs to Global Temporary Tables (GTT) from the trail.
-
NOTAG
-
Valid for Replicat
Prevents the tagging of DDL that is applied by Replicat with a redo tag (either the default tag '00' or one set with the
DBOPTIONS
parameter with theSETTAG
option).The default tag value used by the Replicat is 00, and it can be manually changed by using the
DBOPTIONS SETTAG
parameter. SeeDBOPTIONS
. -
PASSWORD
algorithm
ENCRYPTKEY {
key_name
| DEFAULT}
-
Valid for Extract (Oracle only)
Directs Extract to encrypt all passwords in source DDL before writing the DDL to the trail.
-
REMOVECOMMENTS {BEFORE | AFTER}
-
(Optional) Valid for Extract and Replicat (Oracle only). Controls whether or not comments are removed from the DDL operation. By default, comments are not removed, so that they can be used for string substitution with the
DDLSUBST
parameter. See "DDLSUBST" for more information.-
REMOVECOMMENTS BEFORE
-
Removes comments before the DDL operation is processed by Extract or Replicat. They will not be available for string substitution.
-
REMOVECOMMENTS AFTER
-
Removes comments after they are used for string substitution. This is the default behavior if
REMOVECOMMENTS
is not specified.
-
-
REPLICATEPASSWORD | NOREPLICATEPASSWORD
-
Valid for Extract (Oracle only). Applies to the password in a
{CREATE | ALTER} USER
user
IDENTIFIED BY
password
command.-
By default (
REPLICATEPASSWORD
), Oracle GoldenGate uses the source password in the targetCREATE
orALTER
statement. -
To prevent the source password from being sent to the target, use
NOREPLICATEPASSWORD
.
When using
NOREPLICATEPASSWORD
, specify a password for the target DDL statement by using aDDLOPTIONS
statement with theDEFAULTUSERPASSWORD
orDEFAULTUSERPASSWORDALIAS
option in the Replicat parameter file. -
-
REPORT | NOREPORT
-
Valid for Extract and Replicat (Oracle and Teradata). Controls whether or not expanded DDL processing information is written to the report file. The default of
NOREPORT
reports basic DDL statistics.REPORT
adds the parameters being used and a step-by-step history of the operations that were processed. -
UPDATEMETADATA
-
Valid for Replicat (Oracle only). Use in an active-active bi-directional configuration. This parameter notifies Replicat on the system where DDL originated that this DDL was propagated to the other system, and that Replicat should now update its object metadata cache to match the new metadata. This keeps Replicat's metadata cache synchronized with the current metadata of the local database.
-
USEPASSWORDVERIFIERLEVEL {10|11}
-
Only valid in an Oracle to Oracle configuration. Checks if the password verifier being sent in a DDL
CREATE USER
statement requires modifying. The reason for this check is because Oracle has different password verifiers, depending on the database version:-
10g: A weak verifier kept in
user$.password
. -
11g: The SHA-1 verifier.
-
12c: The SHA-2 and HTTP digest verifiers.
The SHA-1, SHA-2 and HTTP verifiers are captured in
user$.spare4
in the format of:'S:<SHA-1-verifier>;H:<http-verifier>;T:<SHA-2-verifier>'
. Integrated Extract returns the following DDL in 12c for create user DDL statements:-
In 12.0.1.0 it returns:
CREATE USER
username
IDENTIFIED BY VALUES
'S:SHA-1
;H:
http
;weak
'. -
In 12.0.2.0 and later it returns:
CREATE USER
username
IDENTIFIED BY VALUES
'S:SHA-1
;H:
http
;T:
SHA-2
;
weak
'.
If Replicat runs against Oracle 12c, these forms of
CREATE USER
are handled at the RDBMS level, but if Replicat runs against Oracle 10g or 11, these forms are not handled by the RDBMS. Oracle 10g only accepts the weak verifier, whereas Oracle 11g only accepts theS:
SHA-1
and weak verifiers.To allow the
CREATE USER
DDL generated for an Extract connected to Oracle 12c to work with a Replicat connected to Oracle 10g or 11g, this parameter can be used to filter out the unwanted verifiers, as follows:-
If
USEPASSWORDVERIFIERLEVEL
is set to 10, everything except the weak verifier is filtered out of theCREATE USER
DDL verification string. -
If
USEPASSWORDVERIFIERLEVEL
is set to 11, everything except the S:SHA-1
and weak verifiers is filtered out of theCREATE USER
DDL verification string.
-
Examples
- Example 1
-
The following shows how
MAPSESSIONSCHEMA
works to allow mapping of a source session schema to another schema on the target.Assume the following DDL capture and mapping configurations in Extract and Replicat:
Extract:
DDL INCLUDE OBJNAME SRC.* INCLUDE OBJNAME SRC1.* TABLE SRC.*; TABLE SRC1.*; DDL INCLUDE OBJNAME SRC.* INCLUDE OBJNAME SRC1.* TABLE SRC.*; TABLE SRC1.*;
Replicat:
DDLOPTIONS MAPSESSIONSCHEMA SRC TARGET DST DDLOPTIONS MAPSESSIONSCHEMA SRC1 TARGET DST1 MAP SRC.*, TARGET DST.*; MAP SRC1.*, TARGET DST1.*; DDL INCLUDE OBJNAME DST.* INCLUDE OBJNAME DST1.*
Assume that the following DDL statements are issued by the logged-in user on the source:
ALTER SESSION SET CURRENT_SCHEMA=SRC; CREATE TABLE tab (X NUMBER); CREATE TABLE SRC1.tab (X NUMBER) AS SELECT * FROM tab;
Replicat will perform the DDL as follows (explanations precede each code segment):
-- Set session to DST, because SRC.* is mapped to DST.* in MAP statement. ALTER SESION SET CURRENT_SCHEMA=DST; -- Create the first TAB table in the DST schema, using the DST session schema. CREATE TABLE DST.tab (X NUMBER); -- Restore Replicat schema. ALTER SESSION SET CURRENT_SCHEMA=REPUSER -- Set session schema to DST, per MAPSESSIONSCHEMA, so that AS SELECT succeeds. ALTER SESION SET CURRENT_SCHEMA=DST; -- Create the DST1.TAB table AS SELECT * FROM the first table (DST.TAB). CREATE TABLE DST1.tab (X NUMBER) AS SELECT * FROM tab; -- Restore Replicat schema. ALTER SESSION SET CURRENT_SCHEMA=REPUSER
Without
MAPSESSIONSCHEMA
, theSELECT * FROM TAB
would attempt to select from a non-existentSRC.TAB
table and fail. The default is to apply the source schema to unqualified objects in a target DDL statement. The DDL statement in that case would look as follows and would fail:-- Set session to DST, because SRC.* is mapped to DST.* in MAP statement. ALTER SESION SET CURRENT_SCHEMA=DST; -- Create the first TAB table in the DST schema, using the DST session schema. CREATE TABLE DST.tab (X NUMBER); -- Restore Replicat schema. ALTER SESSION SET CURRENT_SCHEMA=REPUSER -- Set session schema to SRC, because TAB in the AS SELECT is unqualified-- and SRC is the source session schema. ALTER SESION SET CURRENT_SCHEMA=SRC; -- Create DST1.TAB AS SELECT * from SRC.TAB (SRC=current session schema). CREATE TABLE DST1.tab (X NUMBER) AS SELECT * FROM tab; -- SRC.TAB does not exist. -- Abend with an error unless the error is handled by a DDLERROR statement.
- Example 2
-
The following shows how to use
DEFAULTUSERPASSWORDALIAS
to specify a different password for a replicated{CREATE | ALTER} USER
name
IDENTIFIED BY
password
statement from the one used in the source statement. In this example, the aliasddlalias
is in thetarget
domain in the credential store.DDLOPTIONS DEFAULTUSERPASSWORDALIAS ddlalias DOMAIN target