Importing Oracle backup data to other tablespaces (only setting the default tablespace is invalid)

Importing Oracle backup data to other tablespaces (only setting the default tablespace is invalid)

When importing an Oracle backup data file (*. dmp) to another database user, you can use the following methods to replace the tablespace.

Assume that the exported user name EXP_USER tablespace is EXP_TSPACE.

Import Username: IMP_USER tablespace: IMP_TSPACE

1. Set the default tablespace and permissions for importing users.

A) The table creation statement is as follows:

Create user IMP_USER

SQL code

Identified by "password"

Default tablespace IMP_TSPACE

Temporary tablespace TEMP (temporary tablespace)

Identified by "password"

Default tablespace IMP_TSPACE

Temporary tablespace TEMP (temporary tablespace)

B) the problem still cannot be solved by setting the tablespace. You will find that when you use the imp command to import data, the imported table is still created on the EXP_TSPACE tablespace.

The reason is: The imported database also has the EXP_TSPACE tablespace, and IMP_SPACE also has the permission to use this tablespace.

The possible cause is that the IMP_USER permission contains the unlimited tablespace permission. (Role resource and dba have this permission ).

I. the user already exists and does not change other Permissions

A) do not change the original role or permissions. only remove the unlimited tablespace permissions.

Command: revoke unlimited tablespace from IMP_USER;

B) add the IMP_TSPACE permission.

Command: alter user qcui quota unlimited on IMP_USER;

Ii. Create a user with the minimum Permissions

A) The following is a reference of the developer's minimum permissions and roles:

Roles: connect, exp_full_database, and imp_full_database

Permission: debug connect session (used for debugging stored procedures, not required)

A) command statement reference:

SQL code

Create user IMP_USER

Identified by "password"

Default tablespace IMP_TSPACE

Temporary tablespace TEMP

Profile DEFAULT

Quota unlimited on IMP_TSPACE;

-- Grant/Revoke role privileges

Grant connect to IMP_USER;

Grant exp_full_database to IMP_USER;

Grant imp_full_database to IMP_USER;

-- Grant/Revoke system privileges

Grant debug connect session to IMP_USER;

Create user IMP_USER

Identified by "password"

Default tablespace IMP_TSPACE

Temporary tablespace TEMP

Profile DEFAULT

Quota unlimited on IMP_TSPACE;

-- Grant/Revoke role privileges

Grant connect to IMP_USER;

Grant exp_full_database to IMP_USER;

Grant imp_full_database to IMP_USER;

-- Grant/Revoke system privileges

Grant debug connect session to IMP_USER;

2. directly modify the dmp file (not recommended)

A) Use UE and other text tools to open and view the DMP file content. Except for some data or commands with binary garbled characters, General create table and insert statements are in plain text.

B) modify the table space name in the table creation statement in batches. That is, replace tablespace EXP_TSPACE with tablespace IMP_TSPACE.