How to Perform Tablespace-Level Export/Import Between Two Oracle Databases

🔹 Step 1: Create OS-Level Directories (Source & Target)

On Source Server (192.168.10.165)

sudo mkdir -p /u02/dpdump/practice
sudo chown oracle:oinstall /u02/dpdump/practice
sudo chmod 777 /u02/dpdump/practice

On Target Server (192.168.10.175)

sudo mkdir -p /u02/dpdump/practice
sudo chown oracle:oinstall /u02/dpdump/practice
sudo chmod 755 /u02/dpdump/practice

🔹 Step 2: Create Logical Directory (Both Source & Target)

sqlplus / as sysdba

CREATE OR REPLACE DIRECTORY practice_dir AS '/u02/dpdump/practice';
GRANT READ, WRITE ON DIRECTORY practice_dir TO system;

🔹 Step 3: Create Tablespace (Source & Target)

CREATE TABLESPACE practice_tbs 
DATAFILE '/u02/oradata/ORCL/practice_tbs01.dbf' 
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

🔹 Step 4: Create User & Objects (Source Only)

CREATE TABLESPACE practice_tbs 
DATAFILE '/u02/oradata/ORCL/practice_tbs01.dbf' 
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

Login as testuser

sqlplus testuser/test123@ORCL

Create Table and Data

CREATE TABLE emp (
  emp_id NUMBER PRIMARY KEY,
  emp_name VARCHAR2(100),
  salary NUMBER
);

INSERT INTO emp VALUES (1, 'Amit', 50000);
INSERT INTO emp VALUES (2, 'Neha', 65000);
COMMIT;

Create Index

CREATE INDEX idx_emp_name ON emp(emp_name);

Create Function

CREATE OR REPLACE FUNCTION get_salary(p_id NUMBER) RETURN NUMBER IS
  v_sal NUMBER;
BEGIN
  SELECT salary INTO v_sal FROM emp WHERE emp_id = p_id;
  RETURN v_sal;
END;
/

🔹 Step 5: Pre-Export Checks (Source)

• Check if tablespace exists:

SELECT tablespace_name FROM dba_tablespaces;

• List objects in the tablespace:

SELECT table_name FROM dba_tables WHERE tablespace_name='PRACTICE_TBS';

• Confirm directory object access:

SELECT * FROM dba_directories WHERE directory_name = 'PRACTICE_DIR';

🔹 Step 6: Perform Export (Source Server)

expdp system/000000 DIRECTORY=practice_dir DUMPFILE=practice_exp.dmp LOGFILE=practice_exp.log TABLESPACES=practice_tbs

📄 Sample Export Log Snippet

Connected to: Oracle Database 19c Enterprise Edition...
Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01": ...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . exported "TESTUSER"."EMP"                              8.296 KB
Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded

🔹 Step 7: Copy Dump File to Target

scp /u02/dpdump/practice/practice_exp.dmp oracle@192.168.10.175:/u02/dpdump/practice/

🔹 Step 8: Pre-Import Preparation (Target)

• Ensure dump file exists:

ls -lh /u02/dpdump/practice/

• Tablespace should already be created (see Step 3)
• Create user:

CREATE USER testuser IDENTIFIED BY test123 
DEFAULT TABLESPACE practice_tbs 
QUOTA UNLIMITED ON practice_tbs;

GRANT CONNECT, RESOURCE TO testuser;

🔹 Step 9: Perform Import (Target Server)

impdp system/000000 DIRECTORY=practice_dir DUMPFILE=practice_exp.dmp LOGFILE=practice_imp.log TABLESPACES=practice_tbs REMAP_SCHEMA=testuser:testuser

📄 Sample Import Log Snippet

Connected to: Oracle Database 19c Enterprise Edition...
Master table "SYSTEM"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Import completed - 1 table, 1 index, 1 function

🔹 Step 10: Post-Import Verification (Target)

sqlplus testuser/test123@ORCL

• Verify table and data:

SELECT * FROM emp;

• Test function:

SELECT get_salary(1) FROM dual;

📌 Conclusion

Performing a tablespace-level export/import between Oracle databases is straightforward when done step-by-step with checks at each stage. This method is ideal for migrating specific application data while preserving schema boundaries and object dependencies.