Can I use EXPDP to export 9i library data using Dblink?

EXPDP/IMPDP is 10g launched the Import and Export tool, 9i is not the tool, someone proposed to use 10g EXPDP, through the Dblink export 9i data, at first blush feel is so, but actually do not, we experiment explained.

1. Log in to a 10g/11g server and first create a link tlink connected to the 9i Library test:

Sql> CREATE DATABASE link Tlink connect to test identified by test using ' test ';D atabase link created.

2. Edit the EXPDP exported configuration file:

Userid=user/pwd-native database user Directory=expdp_dir-directory path dumpfile=testtkt.dumplogfile= used by EXPDP Testtkt.logtables=test.t_r-the 9i library table T_rnetwork_link=tlinkto Export-link to the 9i library Dblink

3. Execution EXPDP:

[Email protected] expdp_dir]$ EXPDP parfile=tlink.parexport:release 11.2.0.1.0-production on Mon may 13:35:57 2015C Opyright (c) 1982, Oracle and/or its affiliates.  All rights reserved. Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit productionwith The partitioning, OLAP, Dat A Mining and Real application testing optionsora-39006:internal errorora-39065:unexpected Master process exception in DI SPATCHORA-00904: "SYS". " KUPM$MCP "." Get_endianness ": Invalid Identifierora-39097:data Pump job encountered unexpected error-904

A series of Ora errors are prompted.

EXPDP Unlike exp, he is to convert the export into an Oracle internal job task, one can prove that is using EXP export with CTRL + C interrupt, this time the overall export process interruption, if using EXPDP Export with CTRL + C interrupt, the export process is not interrupted, Since it has been transformed into a task for Oracle, which is irrelevant to the client, after exiting the interactive mode, it will enter the Export command line mode, with status, start, stop and other commands available.

From the above error can be seen ORA-00904: "SYS". " KUPM$MCP "." Get_endianness ": Invalid identifier, error description for 904:

[Email protected] expdp_dir]$ oerr ora 90400904, 00000, "%s:invalid identifier"//*cause://*action:

The description is not to find the corresponding identifier, and then see what SYS.KUPM$MCP is probably:

Sql> DESC sys. KUPM$MCP; PROCEDURE close_jobfunction DISPATCH RETURNS kupc$_message Argument Name Type in/out D Efault?           -------------------------------------------------------------------REQUEST kupc$_message Inprocedure file_log_message Argument Name Type in/out Default?                  -------------------------------------------------------------------Errcode Number                In PARAMETER1 VARCHAR2 in DEFAULT PARAMETER2 VARCHAR2                     In default PARAMETER3 VARCHAR2 in default PARAMETER4 VARCHAR2 in default PARAMETER5 VARCHAR2 in default PARAMETER     6 VARCHAR2 in DEFAULT PARAMETER7 VARCHAR2 in DEFAULT PARAMETER8 VARCHAR2 in defaultprocedure file_to_worker Argument Name Type in/out Default?           -------------------------------------------------------------------MESSAGE kupc$_message InchFUNCTION get_endianness RETURNS numberPROCEDURE MAIN Argument Name Type in/out Default?                -------------------------------------------------------------------job_name VARCHAR2  In Job_owner VARCHAR2 in Restart_job Binary_integer in                  Debug_info binary_integer inprocedure mainloopprocedure set_debug Argument Name Type in/out Default?          -------------------------------------------------------------------debug_flags Binary_integer Infunction validate_expression RETURNS number Argument Name Type in/out Default?                -------------------------------------------------------------------Expr_value VARCHAR2 Inch

He actually contains a number of functions,Get_endianness is one of them (from the name, seemingly to get "size-end information"), apparently did not find the corresponding function, forget where to see the description of the function( If any man remembers, can reply to the next), he does not exist in 9i, can prove that the EXPDP export process is required to call this function, but this function is not defined in 9i, so 9i is not the use of EXPDP.

4. We'll look at dblink. Export 10g or later libraries

Sql> CREATE DATABASE link Ldplink connect to puser identified by Puser_pwd using ' link_10g ';D atabase link created.

5. Edit the EXPDP configuration file:

[Email protected] expdp_dir]$ VI Link_10g.par
Userid=user/pwd
directory=expdp_dirdumpfile=link_10g.dumplogfile=link_10g.logtables=puser.l_rnetwork_link=link_10g

6. Execution EXPDP:

[[email protected] expdp_dir]$ EXPDP parfile=link_10g.par export:release 11.2.0.1.0-production on Mon 18 13:40:45 2 015Copyright (c) 1982, and/or, Oracle, and its affiliates.  All rights reserved. Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit productionwith The partitioning, OLAP, Dat A Mining and Real application testing optionsora-31631:privileges is Requiredora-39149:cannot link privileged user to n on-privileged User

This time again prompted ORA-31631 and ORA-39149 two errors.

[Email protected] expdp_dir]$ oerr ora 3163131631, 00000, "privileges is required"//*cause: the  necessary Privileg ES is not available-operations such//          as:restarting a job on behalf of another owner, using a device//as          a Member of the dump file set, or ommiting a directory//object associated with any of the          various output files.          Refer to all following error messages for additional information.//*action:select a different job to restart, try A different operation, or//contact          A database administrator to acquire the needed privileges.

The prompt is missing permissions.

[Email protected] expdp_dir]$ oerr ora 3914939149, 00000, "cannot link privileged user to non-privileged user"//*cause:< C0/>a Data Pump job initiated be A user with//          export_full_database/import_full_database roles specified a//          NETW Ork link that does not correspond to a user with//          equivalent roles on the remote database.//*action:specify a networ K Link, maps users to identically privileged//          users in the remote database.

This error message is more explicit, prompting the user to use Dblink and having the export_full_database/import_full_database role, and the user at the far end does not have the appropriate role permissions. A little bit around, simply speaking, that's where I use the EXPDP database user, which has DBA authority, and therefore hasexport_full_database/import_full_database role permissions, but the remote database user puser is just a normal user, without this permission, so there is this hint error.

Solution 1:

Settings in the remote library:

Sql> Grant Exp_full_database to Puser; Grant succeeded.

Execute EXPDP again:

[[email protected] expdp_dir]$ EXPDP parfile=link_10g.par export:release 11.2.0.1.0-production on Mon 18 13:51  : 2015Copyright (c) 1982, Oracle and/or its affiliates. All rights reserved. Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit productionwith The partitioning, OLAP, Dat A Mining and Real application testing optionsstarting "USER". Sys_export_table_01 ": user/******** parfile=link_10g.par Estimate in progress using BLOCKS method ... Processing object type Table_export/table/table_datatotal estimation using BLOCKS method:64 kbprocessing Object Type TABL E_export/table/tableprocessing Object Type Table_export/table/index/indexprocessing Object Type table_export/table/ Constraint/constraintprocessing Object Type Table_export/table/index/statistics/index_statisticsprocessing Object Type Table_export/table/statistics/table_statistics. . Exported "Puser". " L_r "20.49 KB rowsmaster table" Bisal "." Sys_export_Table_01 "Successfully loaded/unloaded************************************************************************** Dump file set for USER. SYS_EXPORT_TABLE_01 is:/home/oracle11g/expdp_dir/link_10g.dumpjob "USER". " Sys_export_table_01 "successfully completed at 13:51:56

Solution 2:

Yangchang's old blog post (http://blog.itpub.net/4227/viewspace-448665/) illustrates this issue. Another way to do this is to create a new local user that does not grant the export_full_database/import_full_database role.

Summary :

1.9i is not support EXPDP, even from 10g EXPDP use Dblink connect to 9i, it is not possible, from the above experiment can be seen, some EXPDP use the function is not defined in 9i.

2. EXPDP export using the Dblink method requires that either the local user does not have a export_full_database/import_full_database role, or the local user has both roles, but at this point the remote user is required to have Export_full_database the role, or you will be prompted for both ORA-31631 and ORA-39149 errors.

Can I use EXPDP to export 9i library data using Dblink?