Tablespace Level- External Tables

A tablespace-level export/import operates on objects contained within specific tablespaces. This example exports all objects contained in the USERS tablespace:

$ expdp mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=tbsp.dmp tablespaces=users

You can initiate a tablespace-level import by using a full export but specifying the TABLESPACES parameter:

$ impdp mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=full.dmp tablespaces=users

A tablespace-level import will attempt to create any tables and indexes within the tablespace. The import doesn’t try to re-create the tablespaces themselves. Since PDB database will have their own tablespaces, this might be an easy level to use for PDB exports.

Export Tablespace Metadata

Sometimes you may be required to replicate an environment, replicating a production environment into a testing environment. One of the first tasks is to replicate the tablespaces.

To this end, you can use Data Pump to pull out just the DDL required to re-create the tablespaces for an environment:

$ expdp mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=inv.dmp full=y include=tablespace

The FULL parameter instructs Data Pump to export everything in the database. However, when used with INCLUDE, Data Pump exports only the objects specified with that command.

In this combination, only metadata regarding tablespaces are exported; no data within the data files is included with the export. You could add the parameter and value of CONTENT=METADATA_ONLY to the INCLUDE command, but this would be redundant.

Now, you can use the SQLFILE parameter to view the DDL associated with the tablespaces that were exported:

$ impdp mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=inv.dmp sqlfile=tbsp.sql

When you use the SQLFILE parameter, nothing is imported. In this example, the prior command only creates a file named tbsp.sql, containing SQL statements pertaining to tablespaces.

You can modify the DDL and run it in the destination database environment; or if nothing needs to change, you can directly use the dump file by importing tablespaces into the destination database.