Last week we discussed about overcoming the ORA-39034 Table TABLE_DATA: X does not exist, when importing data with the REMAP_TABLE option. There is another challenge when importing a table with the REMAP_TABLE option, and that is creating the indexes and triggers.
If the original table already exists in the database, then, when you import with remap_table option, the import will try to import the original indexes, but not on the new table name, but instead on the original table.
Unfortunately there is no remap_index option! It would be great if that existed!
Let me put this into context, so it is easier to see and understand the challenge:
I want to export the table HR.EMPLOYEES and import it either in another database, or the same database as the HR.EMPLOYEES_COPY. I also want to create the same indexes, triggers on the new table EMPLOYEES_COPY, as the indexes that already exists on the EMPLOYEES table. The indexes and triggers, will have to have a different name, as the original name is already taken.
In this example, if we check, we see that the HR.EMPLOYEES table has the following indexes:
select index_name from dba_indexes
where table_name='EMPLOYEES' and owner='HR';
EMP_DEPARTMENT_IX
EMP_EMP_ID_PK
EMP_JOB_IX
EMP_MANAGER_IX
EMP_NAME_IX
During the import, when we use the remap_table option, Oracle only remaps the table, and not the associated indexes and triggers.
On the import, Oracle will create the HR.EMPLOYEES_COPY table, and load the data. But it will try to create the same indexes and triggers on the HR.EMPLOYEES table, the source table, and not the new table.
This will result in errors on the import, INDEX already exists.
ORA-31684: Object type INDEX:"HR"."EMP_DEPARTMENT_IX" already exists
ORA-31684: Object type INDEX:"HR"."EMP_JOB_IX" already exists
ORA-31684: Object type INDEX:"HR"."EMP_MANAGER_IX" already exists
ORA-31684: Object type INDEX:"HR"."EMP_EMP_ID_PK" already exists
ORA-31684: Object type INDEX:"HR"."EMP_NAME_IX" already exists
…
ORA-31684: Object type TRIGGER:"HR"."SECURE_EMPLOYEES" already exists
ORA-31684: Object type TRIGGER:"HR"."UPDATE_JOB_HISTORY" already exists
To overcome this challenge, there are two ways that I can think of, and both of them have 5 steps.
Method #1 :
Step 1 – export table HR.EMPLOYEES
Step 2 – run the import using remap_table, and exclude indexes and triggers
Step 3 – run the import again, with the sqlfile option to create a script for the indexes and triggers
Step 4 – edit the script file, and update the create index statements, and create trigger statements with new unique names
Step 5 – run the script file to create the indexes.
Method #2:
Step 1 – export table HR.EMPLOYEES
Step 2 – get the DDL for indexes and triggers using dbms_metadata.get_ddl. save these to a script.
Step 3 – edit the DDL script, change the table name to be the new table, and update the index names and trigger names
Step 4 – run the import using remap_table, and exclude indexes and triggers
Step 5 – run the script from Step 3, to create the indexes.
This sounds all good, but let’s it see it in action!
Method #1
Step 1 – export table HR.EMPLOYEES
The parameter file for the export looks like this: expdp_hr.employees.par
DIRECTORY=DATA_PUMP_DIR
REUSE_DUMPFILES=YES
DUMPFILE=expdp_hr.employees.dmp
LOGFILE=expdp_hr.employees.log
TABLES=hr.employees
EXCLUDE=CONSTRAINT
--Run the export of the table:
expdp parfile=expdp_hr.employees.par
Step 2 – run the import, with the REMAP_TABLE option:
The parameter file for the import looks like this: impdp_hr.employees_copy.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=expdp_hr.employees.dmp
LOGFILE=impdp_hr.employees_copy.log
TABLES=hr.employees
REMAP_TABLE=hr.employees:employees_copy
--Run the import of the table:
impdp parfile=impdp_hr.employees_copy.par
Step 3 – run the import again, with the SQLFILE option
The parameter file for the import looks like this: impdp_hr.employees_copy_ddl.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=expdp_hr.employees.dmp
LOGFILE=impdp_hr.employees_copy_ddl.log
TABLES=hr.employees
REMAP_TABLE=hr.employees:employees_copy
INCLUDE=INDEX, TRIGGER
SQLFILE=hr.employees_copy.sql
--Run the import to create the sql file
impdp parfile=impdp_hr.employees_copy_ddl.par
Step 4 – edit the script file
Since the remap_table option was used, the table names will not have to be updated in the script. However the index and trigger names will need to be updated to unique names.
Edit the file hr.employees_copy.sql, which will be found in the DIRECTORY object DATA_PUMP_DIR.
Here is a sample of the content of the file:
…
CREATE INDEX "HR"."EMP_DEPARTMENT_IX" ON "HR"."EMPLOYEES_COPY" ("DEPARTMENT_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
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 "SYSAUX" PARALLEL;
…
Once you update the file, save it.
Step 5 – run the script file to create the indexes
@hr.employees_copy.sql
Method #2
The steps in this method are similar to Method#1, with the exception of running dbms_metadata.get_ddl to extract the indexes and triggers.
For the steps that are identical, the same parameter files can be used as in Method #1.
Step 1 – export table HR.EMPLOYEES: use the par file expdp_hr.employees.par
Step 2 – get the DDL for indexes and triggers using dbms_metadata.get_ddl
SET LONG 20000
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT DBMS_METADATA.get_ddl ('INDEX', index_name, owner)
FROM dba_indexes
WHERE owner = 'HR'
AND table_name = 'EMPLOYEES';
SELECT DBMS_METADATA.get_ddl ('TRIGGER', trigger_name, owner)
FROM dba_triggers
WHERE owner = 'HR'
AND table_name = 'EMPLOYEES';
Save the output to a file called hr_employees_copy.sql
Step 3 – edit the DDL script. Make changes to the table name and the index and trigger names. save the file
CREATE UNIQUE INDEX "HR"."EMP_COPY_EMAIL_UK" ON "HR"."EMPLOYEES_COPY" ("EMAIL")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
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 "SYSAUX" ;
Step 4 – run the import using remap_table, and exclude indexes and triggers: use the par file impdp_hr.employees_copy.par
Step 5 – run the script from Step 3, to create the indexes.
@hr_employees_copy.sql
And now you are done!
You have now two methods to create the indexes and triggers on a table that you import with the remap_table option! You choose which method you prefer.
