Re: datapump import schema and default values taken from a sequence

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_at_freelists.org> 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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
"In the beginning the Universe was created. This has made a lot of people
very angry and been widely regarded as a bad move." -Douglas Adams

--
http://www.freelists.org/webpage/oracle-l