Oracle database Import and export issues

Scenario Description:

1, do a schema from uat to PRD migration, UAT Environment has SYS user, PRD environment without SYS user, due to restrictions, no way to use EXPDP/IMPDP, had to choose Exp/imp command;

2, UAT and PRD environment TNS has been clear, assuming that the UAT environment TNS configuration is: TNS_UAT,PRD environment configuration is: TNS_PRD;

3, PRD Environment DB user has been built (other people built to provide), and the UAT environment is the same, assuming that all are user/pwd;

4, UAT and PRD environment, user users of the table space name is different, assuming UAT environment table space is: TBSP_UAT,PRD environment for TBSP_PRD;

The following two workarounds are available when the table space for the two user is not directly exp/imp (try it):

Method One:

1. Use the EXP command to export the user data file Schema_user_bak.dmp from the UAT library in the following format:

EXP user/[email protected]_uat buffer=64000 file=d:\schema_user_bak.dmp owner=user (parameters added on demand)

2, use notepad++ and other text tools to open the file, find out the table space information, the Schema_user_bak.dmp file uat environment tablespace name Tbsp_uat all replace PRD corresponding tablespace name TBSP_PRD, Save the modified file as Schema_user.dmp;

3. Then use the IMP command to import the Schema_user.dmp file to the user users of the PRD environment in the following format:

IMP user/[email protected]_prd buffer=64000 file=d:\schema_user.dmp fromuser=user touser=user

4, OK, method one is completed.

However, assuming that the user users of the UAT environment has a large amount of data, the exported DMP files are usually 3GB, 4GB or even larger, at this time can not be notepad++ or UE and other editors directly to edit the replacement table space information, the method does not work, so there is a method two.

Method Two:

1. Modify the UAT environment User user's tablespace name is the table space name for the PRD environment: (Remember to back up before modifying)

ALTER tablespace tbsp_uat RENAME to TBSP_PRD

2. Export UAT Environment User:

EXP user/[email protected]_uat buffer=64000 file=d:\schema_user.dmp owner=user

3. Import the exported DMP files to the user users of the PRD environment:

IMP user/[email protected]_prd buffer=64000 file=d:\schema_user.dmp fromuser=user touser=user

4, restore UAT Environment User user's table space name:

ALTER tablespace TBSP_PRD RENAME to Tbsp_uat

5, OK, method two completed.

These two methods are effective, but there may be special circumstances, as to what the special situation is, it is not good to say. Special case special treatment, so we have to have a lot of small partners to study.

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

First, the problem description:

Environment: Oracle 11g Client, Windows 64bit, C #;

Description: User UserA The default tablespace is tablespace_a, export the data table backup file using the EXP command. DMP, when importing with the Imp command, the discovery data is still automatically imported into the system table space, which is not imported into the desired tablespace_a tablespace. When IMP is imported, it is not valid even if TABLESPACES=TABLESPACE_A is specified, and the data is imported into the system table space.

Second, Reason:

IMP imports the DMP file based on the source table space when the DMP was exported, i.e. the DMP file exported from the system table space, imported only by default into the table space system at the time of export. This is because exp, when exporting DMP, contains not only the script statement that exported the data table, but also the tablespace information at the time it was exported, and when the IMP was imported, it was imported as the target tablespace according to the tablespace information in DMP. So, even if the default tablespace for user UserA is tablespace_a, if the source table space of DMP is the system tablespace, the user UserA imports into the system table space automatically when using IMP to import and explicitly specify the tablespace.

The above situation often occurs on different computers, such as the backup restore, such as user userb data table is present in the system, he exp when the source table space generated DMP file is the system When UserA is restored with this DMP, it is imported directly into the system table space instead of his own default tablespace_a tablespace.

In a nutshell: Which source tablespace is the exp export, and when IMP is imported, it is automatically imported into the source table space.

(If the source table space does not exist on the target database, it may be an error: The table space does not exist, and when the source table space does not exist, specify the table space you want to import when the IMP is not present). This will be left to you to verify it yourself)

Third, the solution of the idea:

Idea 1: Export with import constraints;

That is, try to do export import between the same tablespace. (Other ideas are finally converted to this step.) ）

Idea 2: Migrate the tables in the other tablespace to the default table space;

The stupidest way to think of it was to re-create the table in the default Tablespace tablespace_a, and then delete the table that the user UserA in the system table space.

Idea 3: Modify the user UserA in the system table space limit (also many users to provide methods);

1. revoke Unlimited tablespace from usera;//revoke UserA Unlimited Permissions
3. alter user UserA quota 0 on system;//The quota of users in the SYSTEM table space to 0
5. alter user UserA quota Unlimited on tablespace_a;//set on users in tablespace_a table space quotas are not restricted

Idea 4: Modify the table space of the data table in the system table space for the user UserA, and change to its default tablespace tablespace_a;

For example, there is a td_users table in user UserA, and now the table space is system, and its table space is modified to tablespace_a.

As for how to modify the Tablespace, one method is to use the following SQL statement:

1. ALTER TABLE td_users move tablespace tablespace_a//First step: Move table td_users to table space tablesapce_a
2. alter index td_users_id rebuild tablespace tablespace_a//Second step: Table space resolution to modify the index of the table :

   --1, sys user login reclaim User A's unlimited tablespace permissions
   Revoke unlimited tablespace from A;

   --2, specifying a user's permissions on table space B
   Alter user A quota unlimited on B;

   --3, re-import the data.

Oracle database Import and export issues