Using exp-imp to migrate different oracle tablespaces

Exp-imp for migration of different oracle tablespace Method 1: -- 1. Modify the tablespace name (account: sys/oracleas SYSDBA logon)

alter tablespace CICHR rename to CICHR_BAK;alter tablespace USERS rename to CICHR;

 

-- 2. Export the user expcichr-20110804/cichr-20110804@db_192.168.196.232 file = cichr-232-20110804.dmplog = cichr-232-20110804-exp.log statistics = none -- the dmp file tablespace should now be CICHR -- 3. Change the tablespace name back (account: sys/oracleas SYSDBA logon)

alter tablespace CICHR rename to USERS;    alter tablespace CICHR_BAK rename to CICHR;

 

-- 4. import to the new user cichr_test (the default tablespace is CICHR, the temporary tablespace is TEMP, connect \ dba \ resource permission, unlimited tablespace) impcichr_test/cichr_test@db_192.168.196.232 file = c: \ cichr-232-20110804.dmp full = ylog = c: \ cichr_test-20110804-imp.log or: impcichr_test/cichr_test@db_192.168.196.232 file = c: \ cichr-232-20110804.dmpfromuser = cichr-20110804 touser = cichr_test tablespace = CICHRlog = c: \ cichr_test-20110804-imp.log so far all data has been migrated to the new tablespace CICHR. -------------------------------------- Method 2: 1. Log On Through PL/SQL, cichr-20110804/cichr-20110804, Export the database table structure (Tools-Export User Object), Export all User objects to D: \ structs. SQL; 2. Use EditPlus to open structs. SQL Replace the table space name as a whole (USERS with CICHR), replace the username as a whole (cichr-20110804 with cichr_test); 3. Tools-Export Tables, Export D:/data. de (PL/SQL Developer)-All table data; 4. Create the user cichr_test (the default tablespace is CICHR, the temporary tablespace is TEMP, connect \ dba \ resource permission, and unlimited tablespace ); 5. Comma Nd Window, execute the command: SQL> @ D: structs. press enter to create a database table structure (the tablespace is CICHR). 6. Import data from the data table, Tools-Import Tables, and select the data file D:/data.. So far, all the data has been migrated to the new tablespace CICHR. ----------------------------------------- Method 3: -- 1. Modify the tablespace name (account: sys/oracleas SYSDBA logon)

alter tablespace CICHR rename to CICHR_BAK;alter tablespace USERS rename to CICHR;

 

-- 2. Log On Through PL/SQL, cichr_test/cichr_test, Tools-Export Tables, and Export D:/data. dmp (oracle Export)-All table structures and table data. The tablespace to which the table data belongs should be CICHR. -- 3. Change the tablespace name back (account: sys/oracleas SYSDBA logon)

alter tablespace CICHR rename to USERS;alter tablespace CICHR_BAK rename to CICHR;

 

-- 4. Log On Through PL/SQL, cichr_test/cichr_test, 1), delete all Drop tables; 2), Tools-Import Tables, Import D:/data. dmp (oracle Export)-All table structures and table data. So far, all the data has been migrated to the new tablespace CICHR.