How to Import & Export from Tablespace Level in Oracle

In Oracle databases, a tablespace is a storage unit that holds related structures and objects in the same place. A tablespace contains one or more datafiles; datafiles can belong to one database only. Therefore, to associate datafiles with another database, or to move them, you must export and import them from one database to another.

Instructions

Export

1. Write down the names of the tablespaces that you wish to export. Confirm that no other administrators or database users are working on those files for the duration of the export process.

2. Set the tablespaces that you want to export to Read-Only by running the following command:

   ALTER_TABLESPACE your_tablepace READ ONLY;

   Replace “your_tablespace” with the name of the tablespace that you want to export. Run the command for each tablespace.

3. Run the following command to create a transportable tablespace file, ready to import into another database:

   EXP TRANSPORT_TABLESPACE=y TABLESPACES=(your_tablespace1,your_tablespace2) TRIGGERS=y CONSTRAINTS=n GRANTS=n FILE=your_file.dmp

   Replace “your_file” with a descriptive name for the export file and add the names of the other tablespaces you want to export, separated by commas.

4. Set your permissions to SYSDBA, when Oracle prompts you to do so:

   CONNECT SYS/your_password AS SYSDBA

   You can replace “SYS” with any other administrative user account to which you have access.

5. Export the file:

   EXP TRANSPORT_TABLESPACE=y TABLESPACES=(your_tablespace1,your_tablespace2) TTS_FULL_CHECK=Y FILE=your_file.dmp

Import

1. Copy and paste the following command into Oracle:

   IMP TRANSPORT_TABLESPACE=y FILE=your_file.dmp DATAFILES=('/db/your_datafile1','/db/your_datafile2') TABLESPACES=(your_tablespace1,your_tablespace2) TTS_OWNERS=(old_user1,old_user2) FROMUSER=(old_user1,old_user2) TOUSER=(new_user1,newuser2)

   Edit the text by replacing “your_file.dmp” with the name of the file to be imported, “your_datafile” with the paths to the datafiles within the tablespaces, “old_user” with the name of the tablespace's previous owner, and “new_user” to the name of its new owner. Separate the names of multiple datafiles, tablespaces and user names with commas, then run the command.

2. Connect as SYS when prompted:

   CONNECT SYS/your_password AS SYSDBA

3. Switch the tablespace back to Read-Write mode if necessary:

   ALTER_TABLESPACE your_tablespace READ WRITE

References

• Oracle Documentation: Managing Tablespaces