Oracle Database exp imp instances exported by users

The following article describes how to export and import the Oracle database exp imp to related instances by users. The first step is to export all objects of the user Test1 from A windows server, then import it to the Test2 user on linux Server B. Test1 password is known as Test1passwd or can be exported using the system user)

2. The Test2 user does not exist on the machine B, or the Test2 user already exists. The user is relatively complicated)

If the Test2 user already has data that is useless, you can delete it.) cascading deletion of users and all objects may encounter a connection. For details about how to delete a user that cannot be deleted, refer to the following section ), create an account and grant permissions.

3. grant appropriate Permissions

Procedure:

1. Export the data file from A to the specified directory name and define the directory name as long as you can find it. It has nothing to do with the user name)

 
 

  
  
sqlplus /nolog   
  
  
conn / as sysdba   
  
  
exp Test1/Test1passwd owner=Test1 file=D:\files\Test1.dmp   
 
 

2. view the user's default tablespace on machine A to create the same tablespace during import.

 
 

  
  
SQL> select username,default_tablespace from dba_users where username ='TEST1';   
  
  
USERNAME DEFAULT_TABLESPACE   
  
  
TEST1 CMIS   
 
 

3. view the tablespace used by the user

 
 

  
  
SQL> select DISTINCT owner ,tablespace_name from dba_extents where owner like 'TEST1';   
  
  
OWNER TABLESPACE_NAME   
  
  
TEST1 XSL   
  
  
TEST1 CMIS   
 
 

4. view the data files corresponding to the tablespace so that appropriate data files can be created on B.

 
 

  
  
SQL> select file_name,tablespace_name from dba_data_files where tablespace_name in ('CMIS','XSL');   
  
  
FILE_NAME BYTES TABLESPACE   
  
  
D:ORACLEPRODUCT10.2.0ORADATACMISDBCMIS 8728346624 CMIS   
  
  
D:ORACLEPRODUCT10.2.0ORADATACMISDBCMIS01.ORA 8204058624 CMIS   
  
  
D:ORACLEPRODUCT10.2.0ORADATACMISDBCMIS02.ORA 4194304000 CMIS   
  
  
D:ORACLEPRODUCT10.2.0ORADATACMISDBCMIS03.ORA 4194304000 CMIS   
  
  
D:ORACLEPRODUCT10.2.0ORADATACMISDBCMIS04.ORA 4194304000 CMIS   
  
  
D:ORACLEPRODUCT10.2.0ORADATACMISDBCMIS05.ORA 4194304000 CMIS   
  
  
D:ORACLEPRODUCT10.2.0ORADATACMISDBCMIS06.ORA 4194304000 CMIS   
  
  
D:ORACLEPRODUCT10.2.0ORADATACMISDBXSL.ORA 4194304000 XSL   
  
  
D:ORACLEPRODUCT10.2.0ORADATACMISDBXSL01.ORA 4194304000 XSL   
  
  
D:ORACLEPRODUCT10.2.0ORADATACMISDBXSL02.ORA 4194304000 XSL   
  
  
 
 
 

5. Check the tablespace of machine B to see if CMIS and XSL exist.

 
 

  
  
select name from v$tablespace where name in ('XSL','CMIS');  
 
 

If the two tablespaces are not found, they must be created.

6. The server to import data does not have the xsl or cmis tablespace. Create

 
 

  
  
create tablespace xsl logging datafile 
'/opt/oracle/product/10.2.0/oradata/xsl.dbf' 
size 15000M extent management local;   
  
  
create tablespace cmis logging datafile 
'/opt/oracle/product/10.2.0/oradata/cmis.dbf' size 37000M extent management local;   
 
 

7. Check whether the user already exists on server B.

 
 

  
  
SQL> select username from dba_users where username='TEST2';  
 
 

In the following two cases, if the [1] method does not exist, if the [2] method is used,

Create user

 
 

  
  
create user Test2 identified by Test2passwd default tablespace cmis temporary tablespace temp profile default;  
 
 

If the user exists

 
 

  
  
drop user Test2 cascade;  
 
 

Delete users and all objects they own)

In this case, if the user is connected, the drop operation will fail. You must first kill the user's session and then drop the user

 
 

  
  
SELECT 'alter system kill session '''
||SID||','||SERIAL||''' immediate;
' FROM V$SESSION WHERE USERNAME='TEST2';  
 
 

If the user is connecting, build the command and kill it)

The preceding statement creates a statement to kill the session of the Test2 user. For example:

 
 

  
  
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL||'''IMMEDIATE;'  
  
  
alter system kill session '129,3570' immediate;   
  
  
alter system kill session '131,2' immediate;   
  
  
alter system kill session '133,572' immediate;   
  
  
alter system kill session '135,1456' immediate;   
  
  
alter system kill session '136,487' immediate;   
  
  
alter system kill session '138,302' immediate;   
  
  
alter system kill session '139,366' immediate;   
 
 

Copy these statements and paste them into sqlplus for execution to kill the session of test2.

 
 

  
  
create user Test2 identified by Test2passwd default 
tablespace cmis temporary tablespace temp profile default; 
 
 

Create a user)

 
 

  
  
grant connect,resource to Test2;  
 
 

Authorization)

8. copy the file from machine A to machine B. If you copy the file to the tmp directory/tmp/Test1.dmp

9. Finally, import data by user on machine

Be sure to exit sqlplus when executing imp and execute imp in linux shell.

 
 

  
  
[oracle@test2 ~]$ imp Test2/Test2passwd fromuser 
=Test1 touser =test2 file=D:\xsldb.DMP log =app/oracle/file/log/DEV_PMODOC.log;  
 
 

The above content is an introduction to the Oracle database exp imp import instance by user export. I hope you will get some benefits.