I'm running Oracle 12.2 and want to copy the table with its dependent objects to a new schema and new name using DBMS_METADATA as below:
h_read := DBMS_METADATA.OPEN('TABLE_EXPORT');
DBMS_METADATA.SET_FILTER(h_read,'SCHEMA','FRAMEWORK');
DBMS_METADATA.SET_FILTER(h_read,'NAME',l_Table_Name);
...
h_modify := DBMS_METADATA.ADD_TRANSFORM(h_write,'MODIFY');
DBMS_METADATA.SET_REMAP_PARAM(h_modify, 'REMAP_SCHEMA', 'FRAMEWORK', l_new_Schema);
DBMS_METADATA.SET_REMAP_PARAM(h_modify, 'REMAP_NAME', l_parsed_old_name, l_new_Name);
All good, except I get duplicates on Primary Key constraint and its index. This is because the Primary Key constraint is bound to table's definition (together with all NOT NULL constraints). I can exclude Primary Key constraint and its index based on their names (%_PK naming convention):
DBMS_METADATA.SET_FILTER(h_read,'EXCLUDE_NAME_EXPR','='''||l_Table_Name||'_PK''', 'INDEX');
DBMS_METADATA.SET_FILTER(h_read,'EXCLUDE_NAME_EXPR','='''||l_Table_Name||'_PK''', 'CONSTRAINT');
... but then:
l_parsed_items := sys.ku$_parsed_items();
DBMS_METADATA.FETCH_XML_CLOB(h_read, l_XML, l_parsed_items, l_Object_Type );
.. does not touch the Primary Key constraint within the table, so I cannot add REMAP_NAME to it. If I add:
DBMS_METADATA.SET_TRANSFORM_PARAM(h_ddl, 'CONSTRAINTS', FALSE);
... then all NOT NULL constraints are gone too - bummer.
Is there a way to skip Primary Key constraint ONLY from the table definition in DBMS_METADATA's XML?