How to Perform Tablespace-Level Export/Import Within the Same Oracle Database

Migrating a tablespace within the same Oracle DB instance can be useful for backup testing, development scenarios, or cloning environments. This step-by-step guide covers creating a dedicated tablespace, user setup, object creation, export/import operations, and verification.

🧱 PART 1: Setup – Tablespace and User Creation

🔍 Check Existing Tablespaces and Datafiles

SELECT tablespace_name, file_name FROM dba_data_files;

➕ Create a New Tablespace

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

✅ Verify the Tablespace

SELECT tablespace_name 
FROM dba_tablespaces 
WHERE tablespace_name = 'GALAXY';

SELECT tablespace_name, SUM(bytes)/1024/1024 AS size_mb 
FROM dba_data_files 
WHERE tablespace_name = 'GALAXY' 
GROUP BY tablespace_name;

SELECT file_name 
FROM dba_data_files 
WHERE tablespace_name = 'GALAXY';

👤 Create User with Default Tablespace

CREATE USER dev_user IDENTIFIED BY "P@ssw0rd" 
DEFAULT TABLESPACE galaxy 
QUOTA UNLIMITED ON galaxy;

🔐 Grant Necessary Privileges

GRANT CONNECT, RESOURCE, CREATE SESSION, CREATE TABLE,CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER,CREATE SEQUENCE, CREATE SYNONYM TO dev_user;

📋 Check User Details

SELECT username, default_tablespace 
FROM dba_users 
WHERE username = 'DEV_USER';

📦 Check User Tablespace Usage

SELECT owner, SUM(bytes)/1024/1024 AS size_mb 
FROM dba_segments 
WHERE owner = 'DEV_USER' 
GROUP BY owner;

🔧 PART 2: Login as DEV_USER and Create Schema Objects

🔑 Connect

CONNECT dev_user/"P@ssw0rd";

📂 Create and Populate Table

CREATE TABLE project_data ( id NUMBER, name VARCHAR2(100) );

BEGIN 
  FOR i IN 1..10 LOOP 
    INSERT INTO project_data VALUES (i, 'Item_' || i); 
  END LOOP; 
  COMMIT; 
END; 
/

⚙️ Create Schema Objects

CREATE INDEX idx_proj_id ON project_data(id);

CREATE VIEW view_proj AS SELECT * FROM project_data WHERE id <= 5;

CREATE SEQUENCE seq_proj START WITH 11 INCREMENT BY 1;

CREATE SYNONYM syn_proj FOR project_data;

CREATE OR REPLACE PROCEDURE add_data IS 
BEGIN 
  INSERT INTO project_data VALUES (seq_proj.NEXTVAL, 'Auto_Item'); 
  COMMIT; 
END; 
/

CREATE OR REPLACE FUNCTION fetch_name(p_id NUMBER) RETURN VARCHAR2 IS 
  v_name VARCHAR2(100); 
BEGIN 
  SELECT name INTO v_name FROM project_data WHERE id = p_id; 
  RETURN v_name; 
END; 
/

CREATE OR REPLACE TRIGGER trg_upper_name 
BEFORE INSERT ON project_data 
FOR EACH ROW 
BEGIN 
  :NEW.name := UPPER(:NEW.name); 
END; 
/

🗂️ PART 3: Setup OS and Logical Directories

🧱 Create OS Directories

mkdir -p /u02/dump/export
mkdir -p /u02/dump/import

chmod 777 /u02/dump/export
chmod 777 /u02/dump/import

📁 Create Oracle Logical Directories

CREATE OR REPLACE DIRECTORY exp_dir AS '/u02/dump/export';
GRANT READ, WRITE ON DIRECTORY exp_dir TO dev_user;

CREATE OR REPLACE DIRECTORY imp_dir AS '/u02/dump/import';
GRANT READ, WRITE ON DIRECTORY imp_dir TO dev_user;

💾 PART 4: Perform Tablespace Export

expdp dev_user/"P@ssw0rd" DIRECTORY=exp_dir DUMPFILE=galaxy_exp.dmp LOGFILE=galaxy_exp.log TABLESPACES=galaxy

📄 Export Log Sample

Export: Release 19.0.0.0.0 - Production on Mon May 20 10:15:30 2025
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Starting "DEV_USER"."SYS_EXPORT_TABLESPACE_01":
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . exported "DEV_USER"."PROJECT_DATA"               6.015 KB     10 rows
Master table "DEV_USER"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEV_USER.SYS_EXPORT_TABLESPACE_01 is:
  /u02/dump/export/galaxy_exp.dmp
Job "DEV_USER"."SYS_EXPORT_TABLESPACE_01" successfully completed

🧹 PART 5: Optional Cleanup Before Import

❌ Drop User (If Re-importing Cleanly)

DROP USER dev_user CASCADE;

🔁 Recreate User and Objects

Repeat PART 1 and PART 2 if necessary.

📤 PART 6: Copy Dump File & Run Import

🗂️ Copy Dump to Import Directory

cp /u02/dump/export/galaxy_exp.dmp /u02/dump/import/

$ cp /u02/dump/export/galaxy_exp.dmp /u02/dump/import/
# No error output means file copied successfully

📥 Run Import Command

impdp dev_user/"P@ssw0rd" DIRECTORY=imp_dir DUMPFILE=galaxy_exp.dmp LOGFILE=galaxy_imp.log TABLESPACES=galaxy TABLE_EXISTS_ACTION=REPLACE

📄 Import Log Sample

Import: Release 19.0.0.0.0 - Production on Mon May 20 10:20:42 2025
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Master table "DEV_USER"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "DEV_USER"."SYS_IMPORT_TABLESPACE_01":
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "DEV_USER"."PROJECT_DATA"              6.015 KB     10 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . imported "DEV_USER"."IDX_PROJ_ID"
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . imported "DEV_USER"."TRG_UPPER_NAME"
Processing object type TABLE_EXPORT/TABLE/PROCEDURE/PROCEDURE
. . imported "DEV_USER"."ADD_DATA"
Processing object type TABLE_EXPORT/TABLE/FUNCTION/FUNCTION
. . imported "DEV_USER"."FETCH_NAME"
Processing object type TABLE_EXPORT/TABLE/VIEW
. . imported "DEV_USER"."VIEW_PROJ"
Processing object type TABLE_EXPORT/TABLE/SEQUENCE/SEQUENCE
. . imported "DEV_USER"."SEQ_PROJ"
Processing object type TABLE_EXPORT/TABLE/SYNONYM
. . imported "DEV_USER"."SYN_PROJ"
Job "DEV_USER"."SYS_IMPORT_TABLESPACE_01" successfully completed at Mon May 20 10:20:56 2025

✅ PART 7: Post-Import Validation

📦 Check Imported Objects

SELECT object_name, object_type FROM user_objects;

🔍 Verify Data

SELECT * FROM project_data;

⚙️ Test Procedure and Function

EXEC add_data;
SELECT fetch_name(1) FROM dual;

🔁 Verify Trigger Functionality

INSERT INTO project_data (id, name) VALUES (100, 'test');
SELECT * FROM project_data WHERE id = 100;

🏁 Conclusion

This guide walks you through a full tablespace-level export/import within the same Oracle DB instance using Data Pump. The process is ideal for internal migrations, backups, or test environment setups.

Pro Tip: Always validate objects, permissions, and data integrity after import to avoid unexpected runtime issues.