Oracle Database exp IMP exported as a user instance (GO)

Http://database.51cto.com/art/201004/196538.htm

The following article mainly introduces the Oracle database exp IMP to export the import related instance according to the relevant user, the first step is to export all objects about Test1 This user from a Windows Server A, then import to the Test2 user on Linux server B. (It is known that the Test1 password is test1passwd or exported with the system user)

2.B Test2 user does not exist on the machine, or Test2 user already exists in two cases (user presence is relatively complex)

If the Test2 user already exists (the data is useless, can be deleted), cascade Delete the user and all objects (may encounter someone is connecting, delete the situation method reference below), re-create the account and empower.

3. Give the appropriate permissions

Operation Steps:

1. Export the data file from A to the specified directory (the directory name itself, as long as you can find the row, and the user name is not related)

1. Sqlplus/nolog
2. Conn/as SYSDBA
3. Exp test1/test1passwd owner=Test1 file=D:\files\Test1.dmp

2. View the user default tablespace on a machine to create the same tablespace when importing

1. SQL> select Username,default_tablespace from dba_users where username =' TEST1 ';
2. USERNAME Default_tablespace
3. TEST1 CMIS

3. View the table space used by the user

1. SQL> select DISTINCT owner, tablespace_name from Dba_extents where the owner like ' TEST1 ';
2. OWNER Tablespace_name
3. TEST1 XSL
4. TEST1 CMIS

4. View the data file for the tablespace to create the appropriate size data file on B.

2. SQL> select File_name,tablespace_name from Dba_data_files where Tablespace_name in (' CMIS ', ' XSL ');
4. file_name BYTES tablespace
6. D:oracleproduct10.2.0oradatacmisdbcmis 8728346624 CMIS
8. D:oracleproduct10.2.0oradatacmisdbcmis01.ora 8204058624 CMIS
10. D:oracleproduct10.2.0oradatacmisdbcmis02.ora 4194304000 CMIS
12. D:oracleproduct10.2.0oradatacmisdbcmis03.ora 4194304000 CMIS
14. D:oracleproduct10.2.0oradatacmisdbcmis04.ora 4194304000 CMIS
16. D:oracleproduct10.2.0oradatacmisdbcmis05.ora 4194304000 CMIS
18. D:oracleproduct10.2.0oradatacmisdbcmis06.ora 4194304000 CMIS
20. D:oracleproduct10.2.0oradatacmisdbxsl.ora 4194304000 XSL
22. D:oracleproduct10.2.0oradatacmisdbxsl01.ora 4194304000 XSL
24. D:oracleproduct10.2.0oradatacmisdbxsl02.ora 4194304000 XSL

5. Check the table space of machine B to see if there is cmis,xsl

1. Select name from V$tablespace where name in (' XSL ', ' CMIS ');

Not found, the description does not have this two tablespace and needs to be created.

6. The server to which you are importing data does not have a xsl,cmis tablespace. Create

1. Create tablespace xsl logging datafile
   '/OPT/ORACLE/PRODUCT/10.2.0/ORADATA/XSL.DBF '
   Size 15000M extent management local;
2. Create tablespace cmis Logging datafile
   '/opt/oracle/product/10.2.0/oradata/cmis.dbf ' size 37000M extent management local;

7. Find whether the user already exists on Server B

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

Next there are two cases, if not present then follow the [one] method, if present according to [II]

Create user

1. Create user Test2 identified by test2passwd default tablespace cmis temporary tablespace temp profile default;

If the user exists

1. Drop user Test2 cascade;

(Delete users and all objects owned by them)

At this point, if the user is connected, the drop will go wrong and the user's session must be killed before dropping

1. 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 statement above is to build the statement that kills the TEST2 user session) such as:

1. ' Altersystemkillsession ' | | sid| | ', ' | | serial| | " IMMEDIATE; '
2. Alter system kill session ' 129,3570 ' immediate;
3. Alter system kill session ' 131,2 ' immediate;
4. Alter system kill session ' 133,572 ' immediate;
5. Alter system kill session ' 135,1456 ' immediate;
6. Alter system kill session ' 136,487 ' immediate;
7. Alter system kill session ' 138,302 ' immediate;
8. Alter system kill session ' 139,366 ' immediate;

Copy these statements and paste them into the sqlplus to kill the Test2 session.

1. Create user Test2 identified by test2passwd default
   Tablespace cmis Temporary tablespace temp profile default;

(Create User)

1. Grant Connect,resource to Test2;

Authorized

8. Copy the file from the a machine to the B machine. If you copy it, put it in the TMP directory./tmp/test1.dmp

9. Finally import data on a machine by user

Be careful to exit sqlplus when executing IMP, execute imp under the Linux shell

1. [Email protected] ~]$ imp test2/test2passwd fromuser
   =Test1 touser =test2 file=D:\xsldb.  DMP log =app/oracle/file/log/dev_pmodoc.log;

The above related content is the Oracle database exp IMP by the user Export Import instance Introduction, hope you can have some gains.

Oracle Database exp IMP exported as a user instance (GO)