Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Table restore from RMAN backup fails on constraint creation with REMAP being used.

cptkirkshJun 27 2024

I have a Oracle 19 DB that I am testing table restores from RMAN backups. I am using this code.

                 recover table "HR".REGIONS OF PLUGGABLE DATABASE 'pdb'
                 until time "to_date('06/26/2024 08:00:00','mm/dd/yyyy hh24:mi:ss')"
                 auxiliary destination '+DATA'
                 REMAP TABLE 'HR'.'REGIONS':'REGIONS_NEW';

I get this error.

Performing import of tables...
   IMPDP> Master table "SYS"."TSPITR_IMP_fyrs_soEh" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_fyrs_soEh":
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> ORA-39083: Object type TABLE:"HR"."REGIONS_NEW" failed to create with error:
ORA-02264: name already used by an existing constraint

Failing sql is:
CREATE TABLE "HR"."REGIONS_NEW" ("REGION_ID" NUMBER CONSTRAINT "REGION_ID_NN" NOT NULL ENABLE, "REGION_NAME" VARCHAR2(25 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"

I am not sure why this is happening. According to the documentation the remap should not allow the indexes and constraints to be restored. How do I exclude them in my recover table statement

When you use the REMAP TABLE clause, named constraints and indexes cannot be imported.

Comments
Post Details
Added on Jun 27 2024
3 comments
455 views