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

Data pump error ORA-39149 how to do, in view of this problem, this article introduces in detail the corresponding analysis and solutions, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

This error occurred when exporting in NETWORK_LINK mode.

The detailed error messages are as follows:

Bash-3.00$ expdp yangtk/yangtk directory=d_temp dumpfile=jiangsu.dp network_link=test113 logfile=jiangsu.log tables=cat_org

Export: Release 11.1.0.6.0-64bit Production on Tuesday, September 16, 2008 17:08:22

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

Connect to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-64bit Production

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

ORA-31631: permissions are required

ORA-39149: cannot link authorized users to unauthorized users

Check Oracle's error manual:

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

Cause: A Data Pump job initiated be a user with EXPORT_FULL_DATABASE/IMPORT_FULL_DATABASE roles specified a network link that did not correspond to a user with equivalent roles on the remote database.

Action: Specify a network link that maps users to identically privileged users in the remote database.

The error description is relatively clear, but this error is difficult to understand. Can't a user with large permissions export a user with less permissions through the database chain?

Of course, after understanding the cause of this mistake, the problem can be easily solved. Create a new user locally and do not authorize the EXP_FULL_DATABASE/IMP_FULL_DATABASE role to export:

Bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0-Production on Tuesday September 16 16:53:48 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connect to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-64bit Production

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

SQL > col grantee format A15

SQL > col granted_role format A15

SQL > select grantee, granted_role from dba_role_privs

2 where grantee = 'YANGTK'

GRANTEE GRANTED_ROLE

--

YANGTK CONNECT

YANGTK RESOURCE

YANGTK DBA

SQL > drop user test cascade

User has been deleted.

SQL > create user test identified by test

2 default tablespace users

3 quota unlimited on users

The user has been created.

SQL > grant connect to test

Authorization successful.

SQL > grant create table, create database link to test

Authorization successful.

SQL > grant read, write on directory d_temp to test

Authorization successful.

SQL > conn test/test

Connected.

SQL > create database link test113 connect to jiangsu identified by jiangsu

2 using '172.0.2.113Universe

The database link has been created.

SQL > select * from global_name@test113

GLOBAL_NAME

TEST

SQL > exit

From Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-64bit Production

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

Using this user, you perform the export:

Bash-3.00$ expdp yangtk/yangtk directory=d_temp dumpfile=jiangsu.dp network_link=test113 logfile=jiangsu.log tables=cat_org

Export: Release 11.1.0.6.0-64bit Production on Tuesday, September 16, 2008 17:08:22

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

Connect to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-64bit Production

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

ORA-31631: permissions are required

ORA-39149: cannot link authorized users to unauthorized users

Bash-3.00$ expdp test/test directory=d_temp dumpfile=jiangsu.dp network_link=test113 logfile=jiangsu.log tables=cat_org

Export: Release 11.1.0.6.0-64bit Production on Tuesday, September 16, 2008 17:09:10

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

Connect to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-64bit Production

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

Start "TEST". "SYS_EXPORT_TABLE_01": test/* directory=d_temp dumpfile=jiangsu.dp network_link=test113 logfile=jiangsu.log tables=cat_org

Estimation is being made using the BLOCKS method.

Dealing with object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimate using the BLOCKS method: 6 MB

Dealing with object type TABLE_EXPORT/TABLE/TABLE

Dealing with object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Dealing with object type TABLE_EXPORT/TABLE/INDEX/INDEX

Dealing with object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Dealing with object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Dealing with object type TABLE_EXPORT/TABLE/COMMENT

Dealing with object type TABLE_EXPORT/TABLE/TRIGGER

Dealing with object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . Exported "JIANGSU". "CAT_ORG" 4.390 MB 31827 lines

The primary table "TEST". "SYS_EXPORT_TABLE_01" has been successfully loaded / unloaded

*

The dump file set for TEST.SYS_EXPORT_TABLE_01 is:

/ data/jiangsu.dp

Assignment "TEST". "SYS_EXPORT_TABLE_01" completed successfully at 17:09:57

If you can modify the remote user, it is even easier to grant the remote user the EXP_FULL_DATABASE role:

-bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0-Production on Tue Sep 16 17:16:27 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:

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

With the Partitioning and Data Mining options

SQL > select global_name from global_name

GLOBAL_NAME

TEST

SQL > grant exp_full_database to jiangsu

Grant succeeded.

Let's use yangtk to perform the export again:

Bash-3.00$ expdp yangtk/yangtk directory=d_temp dumpfile=jiangsu1.dp network_link=test113 logfile=jiangsu.log tables=cat_org

Export: Release 11.1.0.6.0-64bit Production on Tuesday, September 16, 2008 17:19:25

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

Connect to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-64bit Production

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

Start "YANGTK". "SYS_EXPORT_TABLE_01": yangtk/* directory=d_temp dumpfile=jiangsu1.dp network_link=test113 logfile=jiangsu.log tables=cat_org

Estimation is being made using the BLOCKS method.

Dealing with object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimate using the BLOCKS method: 6 MB

Dealing with object type TABLE_EXPORT/TABLE/TABLE

Dealing with object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Dealing with object type TABLE_EXPORT/TABLE/INDEX/INDEX

Dealing with object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Dealing with object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Dealing with object type TABLE_EXPORT/TABLE/COMMENT

Dealing with object type TABLE_EXPORT/TABLE/TRIGGER

Dealing with object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . Exported "JIANGSU". "CAT_ORG" 4.390 MB 31827 lines

The primary table "YANGTK". "SYS_EXPORT_TABLE_01" has been successfully loaded / unloaded

*

The dump file set for YANGTK.SYS_EXPORT_TABLE_01 is:

/ data/jiangsu1.dp

Assignment "YANGTK". "SYS_EXPORT_TABLE_01" completed successfully at 17:19:45

The problem is easy to solve, but I don't understand why Oracle can't deal with such a simple problem. And crucially, Oracle doesn't seem to treat this issue as a bug.

This is the answer to the question about how to report the wrong ORA-39149 in the data pump. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about 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.