User-level Oracle Import and Export

Test Import and Export today
It is understood that if imp AND exp are used for import and export, as long as Oracle tools of the same local version are used, there is no problem with importing and exporting different versions of parts. For example, if 10g is used to export the dmp with 10g exp, copy it to another place, and then import it to the 11g Database with 11g imp, there is no problem.
I did this test today. At least there is no problem with table import and export.
Windows 10 Gb dmp is exported to linux 11goracle.
SELECT * FROM DBA_SYS_PRIVS where GRANTEE = 'usertest'
Imp usertest/usertest file =/u01/oracle/22.dmp fromuser = usertest touser = usertest
However, because user-level import and export is used, users must be created in the new database.
Create user usertest identified by usertest;
Grant create session to usertest;
Grant connect, resource to usertest;

This user imports the data and finds that the user's permissions have not been followed. The permissions must be redefined.

In addition, you must consider whether the user already exists in the tablespace and import/export database.

######################################## ######################################## #########

Attach an online instance:

Import instances after user export. The task requirements are as follows:

◆ 1. Export all objects of Test1 user from windows Server A and import them to Test2 user on linux Server B. (The Test1 password is Test1passwd or can be exported using the system user)

◆ 2. The Test2 user does not exist on the B server, or the Test2 user already exists in two cases (the user is relatively complicated)

--------- If the Test2 user already exists (the data is useless and can be deleted), cascade the user and all objects to delete the user (if someone is connected or cannot be deleted, refer to the following method ), create an account and grant permissions.

◆ 3. grant appropriate Permissions

Procedure:

◆ 1. Export the data file from A to the specified directory (the directory name is defined by yourself, as long as you can find it by yourself, 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.0oradatacmisdb000001. ORA 4194304000 XSL

D: oracleproduct10.2.0oradatacmisdb000002. 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 15000 M extent management local;

Create tablespace cmis logging datafile '/opt/oracle/product/10.2.0/oradata/cmis. dbf' size 37000 M 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,

[1] create a user

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

[2] if a user exists

Drop user Test2 cascade; (delete users and all their objects)

# 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 is used to create a statement to kill the session of the Test2 user.) For example:

'Altersystemkillsession ''' | SID | ',' | SERIAL # | ''' IMMEDIATE ;'

--------------------------------------------------------------------------------

Alter system kill session '2017 0' immediate;

Alter system kill session '2017, 2' immediate;

Alter system kill session '20140901' immediate;

Alter system kill session '2017 6' immediate;

Alter system kill session '20140901' immediate;

Alter system kill session '20140901' immediate;

Alter system kill session '20140901' 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 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;