Post

1 follower Follow
0
Avatar

Is there a way to skip DDL (exclude from replication) Global Temp Tables?

Question

Our systems load a bunch of data into global temp tables often doing CTAS, sometimes normal create table and then inserts into them. I need DDL replicated for all objects except for the Global Temp tables.

Best practice guidelines usually state that data written to the temp tablespace should not be replicated as it's throw away data.

Is there a setting to essentially bypass all global temp tables? I can't unprepare each GTT because the code creates them dynamically, meaning they have a different name every time.

If there is a way to do this?

Answer

The EXCLUDE CREATE TABLE option might satisfy your requirements as outlined in the following:

https://dbvisit.atlassian.net/wiki/display/ugd7/Full+Command-Line+Reference

EXCLUDE CREATE TABLE

Maintain the list of tables/table name patterns that should not be automatically prepared when created on source, although DDL is enabled and whole schema is prepared. The main use case for this feature is to skip various temporary tables that should not be replicated. Use the % character as a wildcard. Underscore (_) is not treated as a special character.

The commands add a new rule; if an existing rule of opposite meaning already exists, it is deleted instead. The matching starts with the oldest one and goes on till the newest rule added; if a reordering is needed, clear the list and start again. The SHOW option lists the existing rules using the very same syntax, so the list can be easily edited and then executed to APPLY the changes..

This command requires a restart of the MINE process.

In suggesting this we are guessing that your temp table names follow some naming pattern, and are not completely random. If so, then this should be a viable option. Especially as EXCLUDE CREATE TABLE supports the % wildcard.

Mike Donovan Answered

Please sign in to leave a comment.

1 comment

0
Avatar

Exclusion of Global Temporary Tables is a feature found in Release 2.7.11.4899 and higher. For these newer versions of Dbvisit Replicate this workaround is no longer advised.

Donna Zehl 0 votes
Comment actions Permalink