Introduction
In a previous post, we have explained about Oracle tablespace export using the export data pump utility (expdp). In this post, we will add more details to provide information on some restrictions and tips using the tablespace export data pump option.
Oracle does not export tablespace metadata
If you unload the tablespace using the tablespace export mode, Oracle does not export the metadata about the tablespace even if the expdp was run by a user with the DATAPUMP_EXP_FULL_DATABASE role. This is unlike a schema mode export which exports even the schema metadata (schema creation definition, system and role grants to the schema etc.) if you perform such an export using a database user with the DATAPUMP_EXP_FULL_DATABASE role. But there is a workaround to obtain the tablespace metadata as well using the DBMS_METADATA package.
For example, if you want to perform a tablespace export data pump operation on HRTS tablespace, you can save its metadata (tablespace creation definitions) first using the below command.
select dbms_metadata.get_ddl('TABLESPACE','HRTS') from dual;
(Tip: This functions returns output as CLOB datatype, so if you use in SQL*Plus , it may truncate the returned output. One option is to use a bigger output buffer option such as "set long 100000")
Oracle does not export PL/SQL procedures dependent on tables in the tablespace
Tablespace export data pump operation only unloads tables only. Though Oracle mentions in the utilities guide that the table's dependent objects are also unloaded, this seems to be only for indexes. If you have a procedure which refers these tables in the tablespace, they are not exported. This could be because procedures are stored in the SYSTEM tablespace and not in the tablespace in question that is being exported out of the database.
In a previous post, we have explained about Oracle tablespace export using the export data pump utility (expdp). In this post, we will add more details to provide information on some restrictions and tips using the tablespace export data pump option.
Oracle does not export tablespace metadata
If you unload the tablespace using the tablespace export mode, Oracle does not export the metadata about the tablespace even if the expdp was run by a user with the DATAPUMP_EXP_FULL_DATABASE role. This is unlike a schema mode export which exports even the schema metadata (schema creation definition, system and role grants to the schema etc.) if you perform such an export using a database user with the DATAPUMP_EXP_FULL_DATABASE role. But there is a workaround to obtain the tablespace metadata as well using the DBMS_METADATA package.
For example, if you want to perform a tablespace export data pump operation on HRTS tablespace, you can save its metadata (tablespace creation definitions) first using the below command.
select dbms_metadata.get_ddl('TABLESPACE','HRTS') from dual;
(Tip: This functions returns output as CLOB datatype, so if you use in SQL*Plus , it may truncate the returned output. One option is to use a bigger output buffer option such as "set long 100000")
Oracle does not export PL/SQL procedures dependent on tables in the tablespace
Tablespace export data pump operation only unloads tables only. Though Oracle mentions in the utilities guide that the table's dependent objects are also unloaded, this seems to be only for indexes. If you have a procedure which refers these tables in the tablespace, they are not exported. This could be because procedures are stored in the SYSTEM tablespace and not in the tablespace in question that is being exported out of the database.
Oracle expects tablespaces created already during import data pump operation.
Because Oracle does not export tablespace metadata (tablespace creation definitions), you have to create the tablespaces with adequate size (atleast equal to the source tablespace size) in your destination database. If your plan is to use different tablespace names, you can use the REMAP_TABLESPACE option in the import data pump (impdp) command. The below example import data pump (impdp) command demonstrates how to import the EMP table only into a HRTS_NEW tablespace in the destination database.
impdp tables=EMP remap_tablespace=(HRTS:HRTS_NEW) dumpfile=DATA_PUMP_DIR:ts_dumps_%U.dmp logfile=DATA_PUMP_DIR:impdp_orders_log.log
Conclusion
Use the tablespace export mode if your sole aim is to only export all tables and their dependent indexes in a tablespace. A related use case for a tablespace export is for taking a backup of tables and indexes in the tablespace prior to dropping the tablespace. The examples used in this post are tested on a 11g R2 Oracle database and should work on 12c R1 and 11g R1 databases as well.
No comments:
Post a Comment