{"id":3464,"date":"2025-05-20T11:04:55","date_gmt":"2025-05-20T11:04:55","guid":{"rendered":"https:\/\/w3buddy.com\/?p=3464"},"modified":"2026-01-15T12:44:19","modified_gmt":"2026-01-15T07:14:19","slug":"how-to-perform-tablespace-level-export-import-between-two-oracle-databases","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/how-to-perform-tablespace-level-export-import-between-two-oracle-databases\/","title":{"rendered":"How to Perform Tablespace-Level Export\/Import Between Two Oracle Databases"},"content":{"rendered":"\n

\ud83d\udd39 Step 1: Create OS-Level Directories (Source & Target)<\/h3>\n\n\n\n

On Source Server<\/strong> (192.168.10.165)<\/h4>\n\n\n\n
sudo mkdir -p \/u02\/dpdump\/practice
sudo chown oracle:oinstall \/u02\/dpdump\/practice
sudo chmod 777 \/u02\/dpdump\/practice<\/pre>\n\n\n\n
On Target Server<\/strong> (192.168.10.175)<\/h4>\n\n\n\n
sudo mkdir -p \/u02\/dpdump\/practice
sudo chown oracle:oinstall \/u02\/dpdump\/practice
sudo chmod 755 \/u02\/dpdump\/practice<\/pre>\n\n\n\n
\ud83d\udd39 Step 2: Create Logical Directory (Both Source & Target)<\/h3>\n\n\n\n
sqlplus \/ as sysdba

CREATE OR REPLACE DIRECTORY practice_dir AS '\/u02\/dpdump\/practice';
GRANT READ, WRITE ON DIRECTORY practice_dir TO system;<\/pre>\n\n\n\n
\ud83d\udd39 Step 3: Create Tablespace (Source & Target)<\/h3>\n\n\n\n
CREATE TABLESPACE practice_tbs 
DATAFILE '\/u02\/oradata\/ORCL\/practice_tbs01.dbf' 
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;<\/pre>\n\n\n\n
\ud83d\udd39 Step 4: Create User & Objects (Source Only)<\/h3>\n\n\n\n
CREATE TABLESPACE practice_tbs 
DATAFILE '\/u02\/oradata\/ORCL\/practice_tbs01.dbf' 
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;<\/pre>\n\n\n\n
Login as testuser<\/h4>\n\n\n\n
sqlplus testuser\/test123@ORCL<\/pre>\n\n\n\n
Create Table and Data<\/h4>\n\n\n\n
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;<\/pre>\n\n\n\n
Create Index<\/h4>\n\n\n\n
CREATE INDEX idx_emp_name ON emp(emp_name);<\/pre>\n\n\n\n
Create Function<\/h4>\n\n\n\n
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;
\/<\/pre>\n\n\n\n
\ud83d\udd39 Step 5: Pre-Export Checks (Source)<\/h3>\n\n\n\n
    \n
  • Check if tablespace exists:<\/li>\n<\/ul>\n\n\n\n
    SELECT tablespace_name FROM dba_tablespaces;<\/pre>\n\n\n\n
      \n
    • List objects in the tablespace:<\/li>\n<\/ul>\n\n\n\n
      SELECT table_name FROM dba_tables WHERE tablespace_name='PRACTICE_TBS';<\/pre>\n\n\n\n
        \n
      • Confirm directory object access:<\/li>\n<\/ul>\n\n\n\n
        SELECT * FROM dba_directories WHERE directory_name = 'PRACTICE_DIR';<\/pre>\n\n\n\n
        \ud83d\udd39 Step 6: Perform Export (Source Server)<\/h3>\n\n\n\n
        expdp system\/000000 DIRECTORY=practice_dir DUMPFILE=practice_exp.dmp LOGFILE=practice_exp.log TABLESPACES=practice_tbs<\/pre>\n\n\n\n
        \ud83d\udcc4 Sample Export Log Snippet<\/strong><\/p>\n\n\n\n
        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<\/pre>\n\n\n\n
        \ud83d\udd39 Step 7: Copy Dump File to Target<\/h3>\n\n\n\n
        scp \/u02\/dpdump\/practice\/practice_exp.dmp oracle@192.168.10.175:\/u02\/dpdump\/practice\/<\/pre>\n\n\n\n
        \ud83d\udd39 Step 8: Pre-Import Preparation (Target)<\/h3>\n\n\n\n
          \n
        • Ensure dump file exists:<\/li>\n<\/ul>\n\n\n\n
          ls -lh \/u02\/dpdump\/practice\/<\/pre>\n\n\n\n
            \n
          • Tablespace should already be created (see Step 3)<\/li>\n\n\n\n
          • Create user:<\/li>\n<\/ul>\n\n\n\n
            CREATE USER testuser IDENTIFIED BY test123 
            DEFAULT TABLESPACE practice_tbs 
            QUOTA UNLIMITED ON practice_tbs;

            GRANT CONNECT, RESOURCE TO testuser;<\/pre>\n\n\n\n
            \ud83d\udd39 Step 9: Perform Import (Target Server)<\/h3>\n\n\n\n
            impdp system\/000000 DIRECTORY=practice_dir DUMPFILE=practice_exp.dmp LOGFILE=practice_imp.log TABLESPACES=practice_tbs REMAP_SCHEMA=testuser:testuser<\/pre>\n\n\n\n
            \ud83d\udcc4 Sample Import Log Snippet<\/strong><\/p>\n\n\n\n
            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<\/pre>\n\n\n\n
            \ud83d\udd39 Step 10: Post-Import Verification (Target)<\/h3>\n\n\n\n
            sqlplus testuser\/test123@ORCL<\/pre>\n\n\n\n
              \n
            • Verify table and data:<\/li>\n<\/ul>\n\n\n\n
              SELECT * FROM emp;<\/pre>\n\n\n\n
                \n
              • Test function:<\/li>\n<\/ul>\n\n\n\n
                SELECT get_salary(1) FROM dual;<\/pre>\n\n\n\n
                \ud83d\udccc Conclusion<\/h2>\n\n\n\n
                Performing a tablespace-level export\/import between Oracle databases<\/strong> 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
                \ud83d\udd39 Step 1: Create OS-Level Directories (Source & Target) On Source Server (192.168.10.165) sudo mkdir -p \/u02\/dpdump\/practicesudo chown oracle:oinstall \/u02\/dpdump\/practicesudo chmod 777 \/u02\/dpdump\/practice On Target Server (192.168.10.175) sudo mkdir -p \/u02\/dpdump\/practicesudo chown oracle:oinstall \/u02\/dpdump\/practicesudo chmod 755 \/u02\/dpdump\/practice \ud83d\udd39 Step 2: Create Logical Directory (Both Source & Target) sqlplus \/ as sysdbaCREATE OR REPLACE DIRECTORY practice_dir […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-3464","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/3464","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=3464"}],"version-history":[{"count":3,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/3464\/revisions"}],"predecessor-version":[{"id":3468,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/3464\/revisions\/3468"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=3464"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=3464"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=3464"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}