4.8 DUMPDDL

Not valid for MySQL.

Use the DUMPDDL command to view the data in the Oracle GoldenGate DDL history table if the trigger-based DDL capture is in use. This information is the same information that is used by the Extract process. It is stored in proprietary format, but can be exported in human-readable form to the screen or to a series of SQL tables that can be queried by using regular SQL.

DUMPDDL always dumps all of the records in the DDL history table. Use SQL queries or search redirected standard output to view information about particular objects and the operations you are interested in. Because the history table contains large amounts of data, only the first 4000 bytes (approximately) of a DDL statement are displayed in order to maintain efficient performance. The format of the metadata is string based. It is fully escaped and supports table and column names in their native character set.

Because the information is historical data that is provided by the DDL before trigger, it reflects the state of an object before a DDL change. Consequently, there will not be any data for CREATE operations.

Note:

The default name of the before trigger is GGS_DDL_TRIGGER_BEFORE.

Before using DUMPDDL, log into the database as the owner of the history table by using the DBLOGIN command.

The basic DUMPDDL command outputs metadata to the following tables.

Table 4-2 DUMPDDL Tables

Table	Description
GGS_DDL_OBJECTS	Information about the objects for which DDL operations are being synchronized. SEQNO is the primary key. All of the other tables listed here contain a SEQNO column that is the foreign key to GGS_DDL_OBJECTS.
GGS_DDL_COLUMNS	Information about the columns of the objects involved in DDL synchronization.
GGS_DDL_LOG_GROUPS	Information about the supplemental log groups involved in DDL synchronization.
GGS_DDL_PARTITIONS	Information about the partitions for objects involved in DDL synchronization.
GGS_DDL_PRIMARY_KEYS	Information about the primary keys of the objects involved in DDL synchronization.

The SEQNO column is the DDL sequence number that is listed in the Extract and Replicat report files. It also can be obtained by querying the DDL history table (default name is GGS_DDL_HIST).

All of these tables are owned by the schema that was designated as the Oracle GoldenGate DDL schema during the installation of the DDL objects. To view the structure of these tables, use the DESC command in SQL*Plus.

Syntax

DUMPDDL [SHOW]

SHOW

Dumps the information contained in the history table to the screen in standard output format. No output tables are produced. All records in the DDL history table are shown.