My suggestion would be to break the process into separate steps, with the
initial import doing the API equivalent of "INCLUDE=table/table"... this
simply creates the tables, minus any constraints/indexes/etc. You can then
alter the table to fix any applicable sequence references, followed by a
second import using "EXCLUDE=table/table".
On Mon, Jul 26, 2021 at 5:03 AM 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
