Search code examples
sqloracle-databaseregexp-replace

Can I use DBMS_METADATA to strip off the SUPPLEMENTAL LOG GROUP from a DDL?

I would like to obtain the DDL of a table and create another one from it. Currently I have something like this:

 v_stmt := 'select dbms_metadata.get_ddl(''TABLE'', ''' || original_table || ''') from dual';
 execute immediate v_stmt into v_stmt ;
 v_stmt := REGEXP_REPLACE(v_stmt, '(' || original_table || ')', new_table, 1, 1);
 execute immediate v_stmt;

The current table has SUPPLEMENTAL LOG GROUP generated. I do not want this, or rather when I try to create I face the below error.

ORA-30567: name already used by an existing log group

How do I get rid of the SUPPLEMENTAL LOG GROUP? Is it possible to use something like below or there is some other solution?

dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PARTITIONING',false);

I think I can also use REGEXP_REPLACE but I don't know how to go about it either.

For reference the generated statement contains something like below:

...
 SUPPLEMENTAL LOG GROUP "SCHEM_TAB0123434" ("CUSTOMERKEY", "VALID", "VERSION", "WHENMODIFIED") ALWAYS, 
...
Solution

  • I believe SET_TRANSFORM_PARAM won't help here.
    You can try with REGEXP_REPLACE:

    `v_stmt := regexp_replace(v_stmt, ',\s*supplemental\s+log\s+group\s+"[^"]+"\s*\("[^"]+"(,\s*"[^"]+")*\)\s*always\s*', '',1, 1,'i');`