Oracle | DBMS_GOLDENGATE

Oracle DBMS_GOLDENGATE_ADM Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose Provides interfaces to configure automatic conflict detection and resolution in an Oracle GoldenGate configuration that replicates tables between Oracle databases. When more than one replica of a table allows changes to the table, a conflict can occur when a change is made to the same row in two different databases at nearly the same time. Oracle GoldenGate replicates changes using row logical change records (LCRs). It detects a conflict by comparing the old values in the row LCR with the current values of the corresponding table row identified by the key columns. If any column value does not match, then there is a conflict. After a conflict is detected, Oracle GoldenGate can resolve the conflict by overwriting values in the row with some values from the row LCR, ignoring the values in the row LCR, or computing a delta to update the row values.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
use_custom_handlers_none	BINARY_INTEGER	0
use_custom_handlers_all	BINARY_INTEGER	1

Dependencies

ALL_GG_AUTO_CDR_TABLES	DBMS_LOGREP_UTIL
ALL_TAB_IDENTITY_COLS	DBMS_STANDARD
DBA_SUPPLEMENTAL_LOGGING	DBMS_SYS_ERROR
DBMS_APPLY_ADM	DBMS_UTILITY
DBMS_ASSERT	DBMS_XSTREAM_ADM_UTL
DBMS_GOLDENGATE_ADM_INTERNAL	DBMS_XSTREAM_GG_INTERNAL
DBMS_GOLDENGATE_ADM_INT_IVOK	V_$PARAMETER
DBMS_ISCHED

Documented Yes: Packages and Types Reference

First Available 12.2

Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE, OGG_APPLY, OGG_CAPTURE and OGG_SHARED_CAPTURE roles.

Security on this package can be controlled either by granting EXECUTE on this package to selected users or roles or by granting EXECUTE_CATALOG_ROLE to selected users or roles.

If subprograms in the package are run from within a stored procedure, then the user who runs the subprograms must be granted EXECUTE privilege on the package directly. It cannot be granted through a role. An Oracle GoldenGate administrator must be configured at each Oracle database in the table�s replication environment, and Oracle GoldenGate must be configured to replicate the table at each Oracle database. You can configure an Oracle GoldenGate administrator using the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_GOLDENGATE_ADM package.

Source {ORACLE_HOME}/rdbms/admin/dbmsgg.sql

Subprograms

ADD_AUTO_CDR	DISABLE_DICTIONARY_BUILD_JOB
ADD_AUTO_CDR_COLUMN_GROUP	DROP_DICTIONARY_BUILD_JOB
ADD_AUTO_CDR_DELTA_RES	ENABLE_DICTIONARY_BUILD_JOB
ADD_DICTIONARY_BUILD_JOB	INSERT_PROCREP_EXCLUSION_OBJ
ALTER_AUTO_CDR	PURGE_TOMBSTONES
ALTER_AUTO_CDR_COLUMN_GROUP	REMOVE_AUTO_CDR
AUTO_DICTIONARY_BUILD	REMOVE_AUTO_CDR_COLUMN_GROUP
DELETE_PROCREP_EXCLUSION_OBJ	REMOVE_AUTO_CDR_DELTA_RES
GG_PROCEDURE_REPLICATION_ON	UPDATE_IDENTITY_COLUMN_HWM

ADD_AUTO_CDR (new 23ai overload)

Configure GoldenGate automatic conflict detection and resolution at the table level

Overload 1 dbms_goldengate_adm.add_auto_cdr( schema_name IN VARCHAR2, table_name IN VARCHAR2, resolution_granularity IN VARCHAR2 DEFAULT 'ROW', -- alt. COLUMN existing_data_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, tombstone_deletes IN BOOLEAN DEFAULT TRUE, fetchcols IN BOOLEAN DEFAULT TRUE, record_conflicts IN BOOLEAN DEFAULT FALSE, use_custom_handlers IN BINARY_INTEGER DEFAULT 0 additional_options IN BINARY_INTEGER);

exec dbms_goldengate_adm.alter_auto_cdr_column_group('UWCLASS', 'SERVERS', 'COLUMN', NULL, TRUE, TRUE, TRUE);

Overload 2 dbms_goldengate_adm.add_auto_cdr( schema_name IN VARCHAR2, table_name IN VARCHAR2, resolution_granularity IN VARCHAR2 DEFAULT 'ROW', -- alt. COLUMN existing_data_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, tombstone_deletes IN BOOLEAN DEFAULT TRUE, fetchcols IN BOOLEAN DEFAULT TRUE, record_conflicts IN BOOLEAN DEFAULT FALSE, use_custom_handlers IN BINARY_INTEGER DEFAULT 0);

TBD

ADD_AUTO_CDR_COLUMN_GROUP

Adds a column group and configures GoldenGate automatic conflict detection and resolution for the column group dbms_goldengate_adm.add_auto_cdr_column_group( schema_name IN VARCHAR2, table_name IN VARCHAR2, column_list IN VARCHAR2, column_group_name IN VARCHAR2 DEFAULT NULL, existing_data_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL);

exec dbms_goldengate_adm.alter_auto_cdr_column_group('UWCLASS', 'SERVERS', 'LATITUDE,LONGITUDE', 'POSITION_COLGRP');

ADD_AUTO_CDR_DELTA_RES

Configures CDR and delta resolution for the column dbms_goldengate_adm.add_auto_cdr_delta_res( schema_name IN VARCHAR2, table_name IN VARCHAR2, column_name IN VARCHAR2);

exec dbms_goldengate_adm.alter_auto_cdr_delta_res('UWCLASS', 'SERVERS', 'STATUS');

ADD_DICTIONARY_BUILD_JOB (new 23ai)

Undocumented dbms_goldengate_adm.add_dictionary_build_job;

TBD

ALTER_AUTO_CDR (new 23ai overload)

Alter automatic conflict detection and resolution at the table level

Overload 1 dbms_goldengate_adm.alter_auto_cdr( schema_name IN VARCHAR2, table_name IN VARCHAR2, tombstone_deletes IN BOOLEAN DEFAULT NULL, fetchcols IN BOOLEAN DEFAULT NULL, record_conflicts IN BOOLEAN DEFAULT NULL, use_custom_handlers IN BINARY_INTEGER DEFAULT NULL, additional_options IN BINARY_INTEGER);

exec dbms_goldengate_adm.alter_auto_cdr('UWCLASS', 'SERVERS', tombstone_deletes=>TRUE);

Overload 2 dbms_goldengate_adm.alter_auto_cdr( schema_name IN VARCHAR2, table_name IN VARCHAR2, tombstone_deletes IN BOOLEAN DEFAULT NULL, fetchcols IN BOOLEAN DEFAULT NULL, record_conflicts IN BOOLEAN DEFAULT NULL, use_custom_handlers IN BINARY_INTEGER DEFAULT NULL);

TBD

ALTER_AUTO_CDR_COLUMN_GROUP

Alters a column group for automatic conflict detection and resolution dbms_goldengate_adm.alter_auto_cdr_column_group( schema_name IN VARCHAR2, table_name IN VARCHAR2, column_group_name IN VARCHAR2, add_column_list IN VARCHAR2, remove_column_list IN VARCHAR2);

exec dbms_goldengate_adm.alter_auto_cdr_column_group('UWCLASS', 'SERVERS', 'COORDINATES', 'LATITUDE,LONGITUDE', '');

AUTO_DICTIONARY_BUILD (new 23ai)

Undocumented dbms_goldengate_adm.auto_dictionary_build;

TBD

DELETE_PROCREP_EXCLUSION_OBJ

Deletes a database object from the exclusion list for GoldenGate procedural replication dbms_goldengate_adm.DELETE_PROCREP_EXCLUSION_OBJ( package_owner IN VARCHAR2 DEFAULT NULL, package_name IN VARCHAR2 DEFAULT NULL, object_owner IN VARCHAR2 DEFAULT NULL, object_name IN VARCHAR2 DEFAULT NULL);

exec dbms_goldengate_adm.delete_procrep_exclusion_obj('UWCLASS', 'TESTPKG');

DISABLE_DICTIONARY_BUILD_JOB (new 23ai)

Undocumented dbms_goldengate_adm.disable_dictionary_build_job;

TBD

DROP_DICTIONARY_BUILD_JOB (new 23ai)

Undocumented dbms_goldengate_adm.drop_dictionary_build_job;

TBD

ENABLE_DICTIONARY_BUILD_JOB (new 23ai)

Undocumented dbms_goldengate_adm.enable_dictionary_build_job;

TBD

GG_PROCEDURE_REPLICATION_ON

Returns 1 if GoldenGate procedural replication is enabled: 0 if disabled dbms_goldengate_adm.gg_procedure_replication_on RETURN NUMBER;

BEGIN

        IF dbms_goldengate_adm.gg_procedure_replication_on = 1 THEN

          dbms_output.put_line('Replication Is On');

        ELSE

          dbms_output.put_line('Replication Is Off');

        END IF;

      END;

      /

INSERT_PROCREP_EXCLUSION_OBJ

Inserts a database object into the exclusion list for procedural replication dbms_goldengate_adm.insert_procrep_exclusion_obj( package_owner IN VARCHAR2 DEFAULT NULL, package_name IN VARCHAR2 DEFAULT NULL, object_owner IN VARCHAR2 DEFAULT NULL, object_name IN VARCHAR2 DEFAULT NULL);

-- Oracle advises that this procedure only be run under the direction of Oracle Support

      

      exec dbms_goldengate_adm.insert_procrep_exclusion_obj('SYS',  'DBMS_REDEFINITION', 'UWCLASS', 'SERVERS');

PURGE_TOMBSTONES

Purges rows that were deleted before the specified timestamp from the tombstone table dbms_goldengate_adm.purge_tombstones(purge_timestamp IN TIMESTAMP WITH TIME ZONE);

exec dbms_goldengate_adm.purge_tombstones(STANDARD.SYS_AT_TIME_ZONE(SYSTIMESTAMP, 'US/Pacific')-1);

REMOVE_AUTO_CDR

Remove automatic CDR configuration dbms_goldengate_adm.remove_auto_cdr( schema_name IN VARCHAR2, table_name IN VARCHAR2);

exec dbms_goldengate_adm.remove_auto_cdr('UWCLASS', 'SERVERS');

REMOVE_AUTO_CDR_COLUMN_GROUP

Removes a column group configured for automatic conflict detection and resolution dbms_goldengate_adm.remove_auto_cdr_column_group( schema_name IN VARCHAR2, table_name IN VARCHAR2, column_group_name IN VARCHAR2);

exec dbms_goldengate_adm.remove_auto_cdr_column_group('UWCLASS', 'SERVERS', 'POSITION_COLGRP');

REMOVE_AUTO_CDR_DELTA_RES

Remove automatic CDR delta resolution for a column dbms_goldengate_adm.remove_auto_cdr_delta_res( schema_name IN VARCHAR2, table_name IN VARCHAR2, column_name IN VARCHAR2);

exec dbms_goldengate_adm.remove_auto_cdr_delta_res('UWCLASS', 'SERVERS', 'STATUS');

UPDATE_IDENTITY_COLUMN_HWM

Updates the HighWaterMark for all instance Identity column sequence objects dbms_goldengate_adm.update_identity_column_hwm;

exec dbms_goldengate_adm.update_identity_column_hwm;