Shulou(Shulou.com)06/02 Report--

It is well known that the FROMUSER and TOUSER parameters of the IMP tool enable the migration of data from one user to another. How can the same functionality be reflected in IMPPDP tools?

The answer is: use the REMAP_SCHEMA parameter of IMPPDP to implement it.

A brief demonstration for reference.

Task: migrate data from sec users to secooler users.

1. Confirm the table and data under sec and secooler users respectively

1) under sec users, there is a T table with 24360 rows of data.

Sys@ora10g > conn sec/sec

Connected.

Sec@ora10g > select * from tab

TNAME TABTYPE CLUSTERID

T TABLE

Sec@ora10g > select count (*) from t

COUNT (*)

-

24360

2) confirm that the secooler user does not include Table T

Secooler@ora10g > conn secooler/secooler

Connected.

Secooler@ora10g > select * from tab

No rows selected

two。 Create a directory object expdp_dir

Sys@ora10g > create or replace directory expdp_dir as'/ expdp'

Directory created.

3. Authorize read and write access to the directory object expdp_dir to sec and secooler users

Sys@ora10g > grant read,write on directory expdp_dir to sec

Grant succeeded.

Sys@ora10g > grant read,write on directory expdp_dir to secooler

Grant succeeded.

4. Generate backup files for sec

Ora10g@secDB / expdp$ expdp sec/sec directory=expdp_dir dumpfile= `date + "% Y%m%d%H%M%S" `_ sec.dmp logfile= `date + "% Y%m%d%H%M%S"` _ sec.log

Export: Release 10.2.0.3.0-64bit Production on Thursday, 01 April, 2010 10:29:17

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production

With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

Starting "SEC". "SYS_EXPORT_SCHEMA_01": sec/* directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec.log

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 3 MB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

. . Exported "SEC". "T" 2.259 MB 24360 rows

Master table "SEC". "SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

*

Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is:

/ expdp/20100401102917_sec.dmp

Job "SEC". "SYS_EXPORT_SCHEMA_01" successfully completed at 10:29:20

The backup file information generated is as follows:

Ora10g@secDB / expdp$ ls-l * sec.dmp

-rw-r- 1 oracle oinstall 2.5m Apr 11029 20100401102917_sec.dmp

5. Using REMAP_SCHEMA parameter of IMPDP to realize data import of secooler user

Ora10g@secDB / expdp$ impdp secooler/secooler directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log REMAP_SCHEMA=sec:secooler

Import: Release 10.2.0.3.0-64bit Production on Thursday, 01 April, 2010 10:32:10

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production

With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

Master table "SECOOLER". "SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SECOOLER". "SYS_IMPORT_FULL_01": secooler/* directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log REMAP_SCHEMA=sec:secooler

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER: "SECOOLER" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . Imported "SECOOLER". "T" 2.259 MB 24360 rows

Job "SECOOLER". "SYS_IMPORT_FULL_01" completed with 1 error (s) at 10:32:12

OK, move in mission complete.

6. Confirm the final migration results

Connect to the secooler user to confirm that the T table and the data in it have been imported.

Sec@ora10g > conn secooler/secooler

Connected.

Secooler@ora10g > select * from tab

TNAME TABTYPE CLUSTERID

T TABLE

Secooler@ora10g > select count (*) from t

COUNT (*)

-

24360

OK, got it.

7. Further reference materials

The best reference is the official documentation of Oracle. The reference link is as follows:

Http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_import.htm#sthref340

For easy reference, a copy of copy is here:

REMAP_SCHEMA

Default: none

Purpose

Loads all objects from the source schema into a target schema.

Syntax and Description

REMAP_SCHEMA=source_schema:target_schema

Multiple REMAP_SCHEMA lines can be specified, but the source schema must be different for each one. However, different source schemas can map to the same target schema. The mapping may not be 100 percent complete, because there are certain schema references that Import is not capable of finding. For example, Import will not find schema references × × ded within the body of definitions of types, views, procedures, and packages.

If the schema you are remapping to does not already exist, the import operation creates it, provided the dump file set contains the necessary CREATE USER metadata for the source schema and you are importing with enough privileges. For example, the following Export commands would create the dump file sets with the necessary metadata to create a schema, because the user SYSTEM has the necessary privileges:

> expdp SYSTEM/password SCHEMAS=hr

> expdp SYSTEM/password FULL=y

If your dump file set does not contain the metadata necessary to create a schema, or if you do not have privileges, then the target schema must be created before the import operation is performed. This is because the unprivileged dump files do not contain the necessary information for the import to create the schema automatically.

If the import operation does create the schema, then after the import is complete, you must assign it a valid password in order to connect to it. The SQL statement to do this, which requires privileges, is:

SQL > ALTER USER [schema_name] IDENTIFIED BY [new_pswd]

Restrictions

Unprivileged users can perform. Schema remaps only if their schema is the target schema of the remap. (Privileged users can perform. Unrestricted schema remaps.)

For example, SCOTT can remap his BLAKE's objects to SCOTT, but SCOTT cannot remap SCOTT's objects to BLAKE.

Example

Suppose that you execute the following Export and Import commands to remap the hr schema into the scott schema:

> expdp SYSTEM/password SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp

> impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp

REMAP_SCHEMA=hr:scott

In this example, if user scott already exists before the import, then the Import REMAP_SCHEMA command will add objects from the hr schema into the existing scott schema. You can connect to the scott schema after the import by using the existing password (without resetting it).

If user scott does not exist before you execute the import operation, Import automatically creates it with an unusable password. This is possible because the dump file, hr.dmp, was created by SYSTEM, which has the privileges necessary to create a dump file that contains the metadata needed to create a schema. However, you cannot connect to scott on completion of the import, unless you reset the password for scott on the target database after the import completes.

8. Summary

In the process of moving from the EXP backup tool to the EXPDP tool, there are many changes that must be paid attention to to prevent inefficiency caused by misuse.

EXPDP is the first choice in terms of functionality and efficiency (it needs to be used on the server side).

Good luck.

Secooler

10.04.01

-- The End--

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.