Is it possible to export Tablespace from one database to another? Yes, but only the database administrator can export Tablespaces, or in other words, only those who have the SYSDBA permission. Let's assume now that we have a database called ORCL that contains a Tablespace called TEST that we want to transfer to another database called OBAY.

But before starting the export process, it is necessary to note that the Files Data files that belong to the Tablespace are backed up by the operating system, or by the ASM Command if the database is stored in the ASM.

Steps:-

1- Put the TEST TABLESPACE in the READ ONLY mode to ensure that the data is not modified during the export process.

ALTER TABLESPACE TEST READ ONLY;

2- Export the information related to the TEST TABLESPACE (Metadata) using the TRANSPORT_TABLESPACE=Y option, and the SYSDBA permission must be used during the export process.

C:\>EXP 'SYSTEM/SYS AS SYSDBA' FILE=D:\EXPORT\TEST.DMP TABLESPACES=TEST TRANSPORT_TABLESPACE=Y;

3- Through the operating system, we make copies of the Datafiles file or files that belong to the Test Tablespace, which is here a single file called TEST, from the ORCL database to the new path of the OBAY database, because the export process does not export the Datafiles.

C:\>COPY
D:\oracle\product\10.1.0\oradata\ORCL\TEST.DBF
D:\oracle\product\10.1.0\oradata\OBAY\TEST.DBF

4- Let's import the data we exported previously.

C:\>IMP 'SYSTEM/SYS AS SYSDBA'
FILE=D:\EXPORT\TEST.DMP TABLESPACES=TEST
TRANSPORT_TABLESPACE=Y
DATAFILES=D:\oracle\product\10.1.0\oradata\OBAY\
TEST.DBF

5- The TEST Tablespace in the ORCL database can be returned to the READ WRITE state, and the TEST Tablespace can be queried in the OBAY database to ensure that it has been Imported.

ALTER TABLESPACE TEST READ WRITE;