Since you're on 18c has anyone suggested using an identity column - which
does exactly the same sort of "default on null sequence" approach, but does
it through a system generated sequence and doesn't reference a sequence
name in the dbms_metadata.get_ddl output, so will (presumably) create the
correct sequence on the import.
Regards
Jonathan Lewis
On Mon, 26 Jul 2021 at 11:03, Thomas Kellerer <dmarc-noreply@xxxxxxxxxxxxx>
wrote:
Hello,
we are using the DataPump API to clone schemas for testing purposes.
However, DataPump fails to import tables that use "DEFAULT ON NULL" based
on a sequence, as it does not remap the owner of the sequence reference in
the table's ddl.
For example: as the user THOMAS I run the following statements:
create sequence my_table_id_seq;
create table my_table
(
id number default on null my_table_id_seq.nextval
);
This stores "THOMAS"."MY_TABLE_ID_SEQ"."NEXTVAL" as the "default value"
for the column ID.
If we use dbms_datapump.copy_schema() to clone the schema THOMAS to the
schema ARTHUR using the remap_schema option for METADATA_REMAP(), then
DataPump will run the following statements:
CREATE SEQUENCE "ARTHUR"."MY_TABLE_ID_SEQ";
CREATE TABLE "ARTHUR"."MY_TABLE"
( "ID" NUMBER DEFAULT ON NULL "THOMAS"."MY_TABLE_ID_SEQ"."NEXTVAL"
NOT NULL ENABLE
)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;
Obviously the above will fail on a database where the user "THOMAS"
doesn't exist.
And it wouldn't work even if THOMAS did exist, because of missing
privileges. .
So my question is:
Is there any way to tell DataPump to replace the reference to
"THOMAS"."MY_TABLE_ID_SEQ" with the target schema:
"ARTHUR"."MY_TABLE_ID_SEQ"?
Or is there any other way to clone a schema so that the above situation
would be resolved?
(In the long run, we might use PDBs instead of schemas, but for now this
is not an option).
We are using Oracle 18c.
Regards
Thomas Kellerer
--
//www.freelists.org/webpage/oracle-l
