EXP/IMP use different tablespaces

Implementation of different tablespaces for exported and imported objects

If the entire tablespace is exported and imported, you can directly use the deletable tablespace feature. The specific steps are as follows:

1. Check the tablespace to be exported first: Check whether all objects in the tablespace are in this tablespace. If some objects are in another tablespace, The tablespace cannot be exported.

Detected by sys. dbms_tts.transport_set_check

Begin SYS. dbms_tts.transport_set_check ('bgmapdata', true); end;

After execution, check whether the transport_set_violations table has records. If yes, it indicates that the objects exist in other tablespaces.

Select * From SYS. transport_set_violations;

Summary: If the table has a record, you must repeat the objects indicated in the table in the current tablespace; otherwise, the ora-29341 is prompted: The deliverable set is not self-contained.

After the detection is successful, perform Step 2

2. Place the tablespace in read-only status

 

Alter tablespace bgmapdata read only;

3. Execute the Export Operation

 

 

Exp transport_tablespace = y tablespaces = (bgmapdata) file = D: \ dd. dmp

After the command is executed, enter sys/change_on_install @ FD as sysdba.

Note: 1) run the following command to export the writable tablespace: SYS user is required; otherwise, the following message is displayed:

EXP-00044: You must be connected as 'sysdb' to perform point-in-time recovery or transfer of tablespaces
EXP-00000: Export termination failed

2) sys/change_on_install @ FD as sysdba the user name and password cannot be directly written in the exp Export Statement.

Exp userid = \ 'sys/change_on_install @ FD as sysdba \ 'transport_tablespace = y tablespaces = (bgmapdata) file = D: \ dd. dmp

The prompt LRM-00112: The parameter 'userid' cannot have more than one value

4. Copy the DMP file to the machine to be imported, for example, on disk D.

5. Execute Import

IMP file = D: \ dd. dmp transport_tablespace = y datafiles =/home/oradba/ff. DBF

You will also be prompted to enter the user name.

 

However, if you only want to export a few tables with different tablespaces, perform the following steps:

1. Export

Exp username/password @ Sid file = D: \ XX. dmp tables = (Table1, table2)

 

2. Copy the DMP file to the target machine.

3. Create the table to be imported on the database to be imported.

You can use the create table script on the source database to export and then modify the tablespace.

4. Execute import and ignore = y during import.

IMP username/password @ Sid fromuser = username touser = username file = D: \ XX. dmp ignore = y