Table refresh is a common task that is quite often asked to perform by an Oracle Database Admin. The reason to perform this task often comes as a request from the application team.
Whenever there is a need arises to test or perform some activity on the data, the data needs to be copied from a higher environment (PROD) to a lower environment( DEV, QA, TEST, etc). Whenever there is a need for defragmenting any objects in the database we can also perform this activity.
Pre-checks before performing Table Level Export-Import:
Before performing import in the target database, we can perform the following pre-checks to avoid any last-minute error :
Check the space utilization of table(s) before export and prior import, check whether the table(s) exists or not in the target database, required space should be available for import in the target database.
Check the tablespaces associated with the source table so that we can compare the same in the target database. If required we can create the tablespace or use the parameter REMAP_TABLESPACE if we want to import table in a different tablespace other than the source table’s tablespace.
If the table already exists in the target database, we can use the parameter TABLE_EXISTS_ACTION in impdp or drop the object before starting the import.
Note down the object and its status and count prior to export and post-import.
After Import
Validate the import log and /row object count should be the same as export.
Check all objects and their status after import. If any invalid object please recompile it.
Whenever there is a need arises to test or perform some activity on the data, the data needs to be copied from a higher environment (PROD) to a lower environment( DEV, QA, TEST, etc). Whenever there is a need for defragmenting any objects in the database we can also perform this activity.
Before performing import in the target database, we can perform the following pre-checks to avoid any last-minute error :
Check the space utilization of table(s) before export and prior import, check whether the table(s) exists or not in the target database, required space should be available for import in the target database.
Check the tablespaces associated with the source table so that we can compare the same in the target database. If required we can create the tablespace or use the parameter REMAP_TABLESPACE if we want to import table in a different tablespace other than the source table’s tablespace.
If the table already exists in the target database, we can use the parameter TABLE_EXISTS_ACTION in impdp or drop the object before starting the import.
Note down the object and its status and count prior to export and post-import.
Validate the import log and /row object count should be the same as export.
Check all objects and their status after import. If any invalid object please recompile it.