Thinking Out Loud

May 19, 2020

Rename Table During DataPump Import

Filed under: 18c,DataPump — mdinh @ 11:07 pm

At source perform export for tables=SCOTT.SALES, SCOTT.ORDERS

$ expdp \"/ as sysdba\" directory=DATA_PUMP_DIR tables=SCOTT.SALES,SCOTT.ORDERS  dumpfile=SCOTT.dmp logfile=SCOTT.log

Preference is to use parameter file vs having to take into consideration and dealing with slash.

Import will rename table SALES to SALES_BACKUP and ORDERS to ORDERS_BACKUP for SCOTT schema.

$ cat impdp_table.par
userid="/ as sysdba"
directory=DATA_PUMP_DIR
dumpfile=SCOTT.dmp
remap_table=SCOTT.SALES:SALES_BACKUP
remap_table=SCOTT.ORDERS:ORDERS_BACKUP
table_exists_action=SKIP
$ impdp parfile=impdp_table.par

Import: Release 18.0.0.0.0 - Production on Tue May 19 11:37:51 2020
Version 18.6.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA parfile=impdp_table.par

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."ORDERS_BACKUP"  11.02 GB 84493879 rows
. . imported "SCOTT"."SALES_BACKUP"   44.60 MB  366568 rows

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

ORA-31684: Object type INDEX:"SCOTT"."ORDERS_PK" already exists
ORA-31684: Object type INDEX:"SCOTT"."ORDERS_INDX2" already exists
ORA-31684: Object type INDEX:"SCOTT"."ORDERS_INDX1" already exists
ORA-31684: Object type INDEX:"SCOTT"."SALES_PK" already exists
ORA-31684: Object type INDEX:"SCOTT"."SALES_INDX1" already exists

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

ORA-31684: Object type CONSTRAINT:"SCOTT"."ORDERS_PK" already exists
ORA-31684: Object type CONSTRAINT:"SCOTT"."SALES_PK" already exists

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "SYS"."SYS_IMPORT_FULL_01" completed with 7 error(s) at Tue May 19 11:39:43 2020 elapsed 0 00:01:50

If the intention is to TRUNCATE or MERGE SCOTT.SALES AND SCOTT.ORDERS using BACKUP, then errors can be ignored.

If not, then failed constraints and indexes will need to be created accordingly.

Oracle Data Pump Import – REMAP_TABLE

 

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Create a free website or blog at WordPress.com.

%d bloggers like this: