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

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_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
>
>
>

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