Shulou(Shulou.com)05/31 Report--

This article is about how impdp+network link skips expdp and imports directly into the target library. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The impdp command has a special purpose to migrate a user of a database to a user of a database on another machine. If the target user does not exist, you can also create the user accordingly and quickly migrate the user on library A to library B.

Let's take a look at the command format:

Execute the command under the B library: (target library, need to go to the database where the data is imported)

Impdp username/passwd schema=userA remap_schema=userA:userB remap_tablespace=tbsA:tbsB network_link=dblink_name

Description: Userid: system is recommended for Username/passwd users.

Remap_schema: userA:userB . Database user mapping. If the same as the user, this parameter is omitted

Remap_tablespace: tbsA:tbsB . Default tablespace mapping.

Schemas: userA . Must be the specified user in dblink. It is not recommended to specify.

Directory: in this mode, this parameter specifies the path to the log file. If not specified, the path defaults to data_pump_dir.

Network_link: the dblink created on library B that connects to library A.

But there are several prerequisites:

1. Username: the database user for this operation is recommended to be system. If it is another user, a user with dba permission is required to execute it.

2. Dblink: must be able to connect to the database user on the corresponding library.

3. Advantages: just no longer import the data after export, but import the data directly from the source database to the destination database.

4. If schema An is exported from the original library and db_link is built on schema A, the schema A user of the original library must have exp_full_database privileges or an error will be reported:

With the Partitioning, OLAP and Data Mining options ORA-31631: privileges are required ORA-39149: cannot link privileged user to non-privileged user

5. This operation is the most convenient tool for data migration in the local area network, but it may also be the slowest tool.

6. At the same time, you can also use this method to guide tablespaces, separate tables, etc. Tablespaces = xxx_tbs. ...

3. When establishing a db_link to the source end on the target database, it can be created for system users, so that you can export and import full database data or tablespace data.

7. When you create a db_link for a user A, you need to give that user An exp_full_database permission to export the schema data.

8. Pay attention to the existence of table data in the target database during the import process, which can be handled by table_exists_action.

Experiment one

Source database: 192.168.56.20 hostname:slient

Target library: 192.168.56.12 hostname:wang

1. Target library operation:

1. Create a tns connection string:

[oracle@wang admin] $pwd

/ u01/app/oracle/product/11.2.0/db_1/network/admin

[oracle@wang admin] $

[oracle@wang admin] $cat tnsnames.ora

# tnsnames.ora Network Configuration File: / u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

DBDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = wang) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = DBdb)

)

)

20 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.56.20) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test)

)

)

[oracle@wang admin] $tnsping 20

TNS Ping Utility for Linux: Version 11.2.0.4.0-Production on 05-AUG-2017 23:51:59

Copyright (c) 1997, 2013, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.56.20) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test)

OK (0 msec)

[oracle@wang admin] $

Note: check whether the source database and target database are listening.

two。 Create a dblink for the source library

SQL > create public database link test connect to scott identified by tiger using '20'

Database link created.

Verify:

SQL > set lines 200 pages 999

SQL > col db_link for A10

SQL > col host for A10

SQL > select OWNER,DB_LINK,USERNAME,HOST,CREATED from dba_db_links

OWNER DB_LINK USERNAME HOST CREATED

PUBLIC TEST SCOTT 20 06-AUG-17

Note: create a public dblink

3. Create an imported user:

SQL > create user hh identified by hh account unlock

User created.

2. The source database grants export permissions to the corresponding export user:

SQL > grant EXP_FULL_DATABASE to scott

Grant succeeded.

Third, the target library carries on the import job:

[oracle@wang admin] $impdp system/oracle schemas=scott remap_schema=scott:hh network_link=test

Import: Release 11.2.0.4.0-Production on Sun Aug 6 00:54:06 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

FLASHBACK automatically enabled to preserve database integrity.

Starting "SYSTEM". "SYS_IMPORT_SCHEMA_01": system/* schemas=scott remap_schema=scott:hh network_link=test

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 256 KB

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER: "HH" 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

. . Imported "HH". "DBXL" 13 rows

. . Imported "HH". "DEPT" 4 rows

. . Imported "HH". "EMP" 14 rows

. . Imported "HH". "SALGRADE" 5 rows

. . Imported "HH". "BONUS" 0 rows

. . Imported "HH". "TEST": "P1" 0 rows

. . Imported "HH". "TEST": "P2" 0 rows

. . Imported "HH". "TEST": "P3" 0 rows

. . Imported "HH". "TEST": "P4" 0 rows

. . Imported "HH". "TEST123" 0 rows

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/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM". "SYS_IMPORT_SCHEMA_01" completed with 1 error (s) at Sun Aug 6 00:54:23 2017 elapsed 0 00:00:15

[oracle@wang admin] $

Fourth, verification:

SQL > conn hh/hh

Connected.

SQL > select * from tab

TNAME TABTYPE CLUSTERID

BONUS TABLE

DBXL TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

TEST TABLE

TEST123 TABLE

7 rows selected.

Experiment two

Target library: 192.168.56.12 hostname:wang db_name:DBdb service_names:service1,service2,service3

Source database: 192.168.56.11 hostnamerhel db_name:orcl service_names:orcl

1. Target library operation:

-- tns configured to the source database

[oracle@wang admin] $cat tnsnames.ora

# tnsnames.ora Network Configuration File: / u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

Orcl =

(DESCRIPTION =

(LOAD_BALANCE = yes)

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.56.11) (PORT = 1521))

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

[oracle@wang admin] $

-- View the data under the user to be imported

SQL > conn hr/hr

Connected.

SQL > select * from tab

TNAME TABTYPE CLUSTERID

COUNTRIES TABLE

DEPARTMENTS TABLE

EMPLOYEES TABLE

JOBS TABLE

JOB_HISTORY TABLE

LOCATIONS TABLE

REGIONS TABLE

7 rows selected.

-- create the dblink of the hr user of the source database:

SQL > create database link ol connect to hr identified by hr using 'orcl'

Database link created.

SQL > select * from dba_db_links

OWNER DB_LINK USERNAME HOST CREATED

-

SYS FTLINK SCOTT 20 13-AUG-17

HR ORA HR orcl 08-DEC-17

SYS OL HR orcl 09-DEC-17

2. Source-database check:

-- check the test table to export

SQL > conn hr/hr

Connected.

SQL > select * from tab

TNAME TABTYPE CLUSTERID

COUNTRIES TABLE

DEPARTMENTS TABLE

EMPLOYEES TABLE

JOBS TABLE

JOB_HISTORY TABLE

LOCATIONS TABLE

REGIONS TABLE

SYNO TABLE

8 rows selected.

SQL > select count (*) from syno

COUNT (*)

-

thirty-five

-create an index

SQL > create index idx_object_id on syno (object_id)

Index created.

SQL >

3. The target library performs the import:

[oracle@wang ~] $impdp\'/ as sysdba\ 'tables=hr.SYNO network_link=ol

Import: Release 11.2.0.4.0-Production on Sat Dec 9 08:54:17 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-31631: privileges are required

ORA-39149: cannot link privileged user to non-privileged user

[oracle@wang ~] $

Error: the exp_full_database permission of the user to be exported by the source database needs to be granted.

-- Source database authorization:

SQL > conn / as sysdba

Connected.

SQL > grant EXP_FULL_DATABASE to hr

Grant succeeded.

-- perform the import in the target library again:

[oracle@wang ~] $impdp\'/ as sysdba\ 'tables=hr.SYNO network_link=ol

Import: Release 11.2.0.4.0-Production on Sat Dec 9 09:03:34 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS". "SYS_IMPORT_TABLE_01": "/ * AS SYSDBA" tables=hr.SYNO network_link=ol

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . Imported "HR". "SYNO" 35 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

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

Job "SYS". "SYS_IMPORT_TABLE_01" successfully completed at Sat Dec 9 09:03:51 2017 elapsed 0 00:00:16

[oracle@wang ~] $

It is found that index statistics have been imported.

Fourth, verification:

-- Target library verification:

SQL > conn / as sysdba

Connected.

SQL > set lines 200

SQL > select owner,index_name,table_owner,table_name,status from dba_indexes where table_name='SYNO'

OWNER INDEX_NAME TABLE_OWNER TABLE_NAME STATUS

HR IDX_OBJECT_ID HR SYNO VALID

SQL > conn hr/hr

Connected.

SQL > select * from tab

TNAME TABTYPE CLUSTERID

COUNTRIES TABLE

DEPARTMENTS TABLE

EMPLOYEES TABLE

JOBS TABLE

JOB_HISTORY TABLE

LOCATIONS TABLE

REGIONS TABLE

SYNO TABLE

8 rows selected.

SQL > select count (*) from syno

COUNT (*)

-

thirty-five

Experiment 3

Reference experiment 2 of source database and target database

Task: import tables an and b under the scott user under the source database to the hr user in the target database, and the table space of the table is mapped to TS_XXF and the table space of the index is mapped to TST

First, source database operation, simulate test tables an and b

SQL > conn scott/tiger

Connected.

SQL > select * from tab

TNAME TABTYPE CLUSTERID

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

SQL > create table an as select * from emp

Table created.

SQL > create table b as select * from user_objects

Table created.

SQL > create index idx_deptno on a (deptno)

Index created.

SQL > create index idx_obj_id on b (object_id)

Index created.

SQL > select count (*) from a

COUNT (*)

-

fourteen

SQL > select count (*) from b

COUNT (*)

-

eight

SQL > conn / as sysdba

Connected.

SQL > col SEGMENT_NAME for A15

SQL > select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES from dba_segments where SEGMENT_NAME in ('Achilles Magazine')

OWNER SEGMENT_NAME TABLESPACE_NAME BYTES

-

SCOTT B USERS 65536

SCOTT A USERS 65536

SQL > select OWNER,INDEX_NAME,TABLESPACE_NAME,STATUS from dba_indexes where TABLE_OWNER='SCOTT' and table_name in ('Achilles Magazine')

OWNER INDEX_NAME TABLESPACE_NAME STATUS

--

SCOTT IDX_DEPTNO USERS VALID

SCOTT IDX_OBJ_ID USERS VALID

2. Query the target database:

SQL > col name for A70

SQL > set lines 200 pages 999

SQL > select f.file#

2 t.name tablespace

3 f.name

4 trunc (f.bytes / 1048576, 2) size_mb

5 to_char (f.creation_time, 'yyyy-mm-dd') creation_time

6 status

7 from v$datafile f, v$tablespace t

8 where f.ts# = t.ts#

9 order by f.creation_time

FILE# TABLESPACE NAME SIZE_MB CREATION_T STATUS

-

1 SYSTEM / u01/app/oracle/oradata/DBdb/system01.dbf 2800 2013-08-24 SYSTEM

2 SYSAUX / u01/app/oracle/oradata/DBdb/sysaux01.dbf 710 2013-08-24 ONLINE

4 USERS / u01/app/oracle/oradata/DBdb/users01.dbf 3466.25 2013-08-24 ONLINE

3 UNDOTBS1 / u01/app/oracle/oradata/DBdb/undotbs01.dbf 2585 2013-08-24 ONLINE

5 EXAMPLE / u01/app/oracle/oradata/DBdb/example01.dbf 338.75 2017-04-27 ONLINE

6 TS_XXF / u01/app/oracle/fast_recovery_area/DBDB/newback/ts_xxf_01.dbf 10 2017-12-07 ONLINE

7 TST / u01/app/oracle/oradata/DBdb/tst.dbf 20 2017-12-09 ONLINE

7 rows selected.

SQL > select df.tablespace_name "tablespace name"

2 totalspace "Total Space M"

3 freespace "residual space M"

4 round ((1-freespace / totalspace) * 100,2) "usage%"

5 from (select tablespace_name, round (sum (bytes) / 1024 / 1024) totalspace

6 from dba_data_files

7 group by tablespace_name) df

8 (select tablespace_name, round (sum (bytes) / 1024 / 1024) freespace

9 from dba_free_space

10 group by tablespace_name) fs

11 where df.tablespace_name = fs.tablespace_name

Table space name total space M remaining space M utilization%

TS_XXF 10 9 10

TST 20 19 5

SYSAUX 710 41 94.23

UNDOTBS1 2585 2118 18.07

USERS 3466 3435. 89

SYSTEM 2800 2027 27.61

EXAMPLE 339 29 91.45

7 rows selected.

-- create the dblink of the target database for the source database scott

SQL > create database link sc connect to scott identified by tiger using 'orcl'

Database link created.

SQL > select * from dba_db_links

OWNER DB_LINK USERNAME HOST CREATED

-

SYS FTLINK SCOTT 20 13-AUG-17

HR ORA HR orcl 08-DEC-17

SYS OL HR orcl 09-DEC-17

SYS SC SCOTT orcl 09-DEC-17

3. The target library performs the import:

Import tables (exclude, excluding indexes)

Impdp\'/ as sysdba\ 'remap_schema=scott:hr REMAP_TABLESPACE=USERS:TS_XXF exclude=index table_exists_action=TRUNCATE TABLES=scott.a,scott.b network_link=sc

Import only indexes (include)

Impdp\'/ as sysdba\ 'remap_schema=scott:hr REMAP_TABLESPACE=USERS:TST include=index TABLES=scott.a,scott.b network_link=sc

[oracle@wang ~] $impdp\'/ as sysdba\ 'remap_schema=scott:hr REMAP_TABLESPACE=USERS:TS_XXF exclude=index table_exists_action=TRUNCATE TABLES=scott.a,scott.b network_link=sc

Import: Release 11.2.0.4.0-Production on Sat Dec 9 10:32:50 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-31631: privileges are required

ORA-39149: cannot link privileged user to non-privileged user

[oracle@wang ~] $

If an error is reported, you need to grant exp_full_database permission to the source database scott user.

-- Source database authorization operation:

SQL > conn / as sysdba

Connected.

SQL > grant EXP_FULL_DATABASE to scott

Grant succeeded.

SQL >

-- perform the import operation in the target library again:

[oracle@wang ~] $impdp\'/ as sysdba\ 'remap_schema=scott:hr REMAP_TABLESPACE=USERS:TS_XXF exclude=index table_exists_action=TRUNCATE TABLES=scott.a,scott.b network_link=sc

Import: Release 11.2.0.4.0-Production on Sat Dec 9 10:37:37 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS". "SYS_IMPORT_TABLE_01": "/ * AS SYSDBA" remap_schema=scott:hr REMAP_TABLESPACE=USERS:TS_XXF exclude=index table_exists_action=TRUNCATE TABLES=scott.a,scott.b network_link=sc

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 128 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . Imported "HR". "A" 14 rows

. . Imported "HR". "B" 8 rows

Job "SYS". "SYS_IMPORT_TABLE_01" successfully completed at Sat Dec 9 10:37:50 2017 elapsed 0 00:00:12

[oracle@wang ~] $

[oracle@wang ~] $impdp\'/ as sysdba\ 'remap_schema=scott:hr REMAP_TABLESPACE=USERS:TST include=index TABLES=scott.a,scott.b network_link=sc

Import: Release 11.2.0.4.0-Production on Sat Dec 9 10:39:07 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS". "SYS_IMPORT_TABLE_01": "/ * AS SYSDBA" remap_schema=scott:hr REMAP_TABLESPACE=USERS:TST include=index TABLES=scott.a,scott.b network_link=sc

Estimate in progress using BLOCKS method...

Total estimation using BLOCKS method: 0 KB

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

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

Job "SYS". "SYS_IMPORT_TABLE_01" successfully completed at Sat Dec 9 10:39:12 2017 elapsed 0 00:00:05

[oracle@wang ~] $

IV. Verification of the target library:

SQL > col SEGMENT_NAME for A15

SQL > select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES from dba_segments where SEGMENT_NAME in ('Achilles Magazine')

OWNER SEGMENT_NAME TABLESPACE_NAME BYTES

-

HR B TS_XXF 65536

HR A TS_XXF 65536

SQL > select OWNER,INDEX_NAME,TABLESPACE_NAME,STATUS from dba_indexes where table_name in ('Achilles Magazine')

OWNER INDEX_NAME TABLESPACE_NAME STATUS

--

HR IDX_DEPTNO TST VALID

HR IDX_OBJ_ID TST VALID

SQL >

SQL > conn hr/hr

Connected.

SQL > select * from tab

TNAME TABTYPE CLUSTERID

A TABLE

B TABLE

COUNTRIES TABLE

DEPARTMENTS TABLE

EMPLOYEES TABLE

JOBS TABLE

JOB_HISTORY TABLE

LOCATIONS TABLE

REGIONS TABLE

9 rows selected.

SQL > select count (*) from a

COUNT (*)

-

fourteen

SQL > select count (*) from b

COUNT (*)

-

eight

Thank you for reading! On "how impdp+network link skips expdp directly into the target library" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

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.