【MOS】 EXPDP - ORA-39166 (Object Was Not Found) (文档 ID 1640392.1)

EXPDP - ORA-39166 (Object Was Not Found) When Trying To Export The Data From A View (文档 ID 1640392.1)

In this Document

Symptoms
Cause
Solution
References


APPLIES TO:

Oracle Database - Standard Edition - Version 10.1.0.2 to 12.1.0.2 [Release 10.1 to 12.1]
Oracle Database - Personal Edition - Version 10.1.0.2 to 12.1.0.2 [Release 10.1 to 12.1]
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.1.0.2 [Release 10.1 to 12.1]
Enterprise Manager for Oracle Database - Version 10.1.0.2 to 12.1.0.5.0 [Release 10.1 to 12.1]
Information in this document applies to any platform.

SYMPTOMS

1. You have a user who has created a view, e.g.:

connect / as sysdba
create user tc identified by tc default tablespace users
   temporary tablespace temp quota unlimited on users;
grant create session, create table, create view to tc;

create directory my_dir as '/tmp/expdp';
grant read, write on directory my_dir to system;

connect tc/tc
create table tc.my_tab1 (nr number, txt varchar2(10));
insert into tc.my_tab1 values (1,'Line 1');
insert into tc.my_tab1 values (2,'Line 2');
[Insert code here. Use 'Paste from Word' to retain layout.]
 
create table tc.my_tab2 (nr number, col2 number, col3 varchar2(10));
insert into tc.my_tab2 values (1,1,'c3_1');
insert into tc.my_tab2 values (2,2,'c3_2');
commit;
create view my_view (nr, txt, col3) as
   select t1.nr, t1.txt, t2.col3 
     from tc.my_tab1 t1, tc.my_tab2 t2
    where t1.nr=t2.nr;
select * from my_view;

          NR TXT        COL3
------------ ---------- ----------
           1 Line 1     c3_1
           2 Line 2     c3_2

2. When you export the view with DataPump, then only the DDL of the view is exported, e.g.:

$ expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_vw.dmp LOGFILE=expdp_vw.log SCHEMAS=tc INCLUDE=view:\"\=\'MY_VIEW\'\"

Export: Release 11.2.0.4.0 - Production on Thu Mar 27 09:43:13 2014
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 "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** DIRECTORY=my_dir DUMPFILE=expdp_vw.dmp LOGFILE=expdp_vw.log SCHEMAS=tc INCLUDE=view:"='MY_VIEW'"
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /tmp/expdp/EXPDP_VW.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Mar 27 09:43:24 2014 elapsed 0 00:00:10

3. When you try to export the data that is represented by the view by specifying the TABLES parameter, the following errors are reported:

$ expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_vw.dmp LOGFILE=expdp_vw.log TABLES=tc.my_view

Export: Release 11.2.0.4.0 - Production on Thu Mar 27 09:47:34 2014
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 "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** DIRECTORY=my_dir DUMPFILE=expdp_vw.dmp LOGFILE=expdp_vw.log TABLES=tc.my_view
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-39166: Object TC.MY_VIEW was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYSTEM"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at Thu Mar 27 09:47:38 2014 elapsed 0 00:00:03

 

CAUSE

The Export Data Pump parameter TABLES cannot be used to export the data that is represented with a VIEW.
 

SOLUTION

1. Upgrade to Oracle12c where you can specify the Export Data Pump parameter VIEWS_AS_TABLES (to export a table with the same columns as the view and with row data fetched from the view. Data Pump also exports objects dependent on the view, such as grants and constraints).
Note that this is a new feature in 12c which does not exist in 11g. Example:

$ expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_vw.dmp LOGFILE=expdp_vw.log VIEWS_AS_TABLES=tc.my_view

Export: Release 12.1.0.1.0 - Production on Thu Mar 27 08:38:07 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** DIRECTORY=my_dir DUMPFILE=expdp_vw.dmp LOGFILE=expdp_vw.log VIEWS_AS_TABLES=tc.my_view
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 16 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "TC"."MY_VIEW"                              5.906 KB       2 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /tmp/expdp/expdp_vw.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Thu Mar 27 08:38:29 2014 elapsed 0 00:00:17

-- or --

2. Create a fixed table in the source database which contains the rows that you want to export (the rows from the view), e.g.

create table tc.my_view_tab as select * from tc.my_view;

and export the data from the temporary fixed table, e.g.:

$ expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_vw.dmp LOGFILE=expdp_vw.log TABLES=tc.my_view_tab

-- or --

3. Export all source tables (all rows) and all other related objects, e.g.:

$ expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_vw.dmp LOGFILE=expdp_vw.log SCHEMAS=tc INCLUDE=table:\"IN\(\'MY_TAB1\',\'MY_TAB2\'\)\" INCLUDE=view:\"\=\'MY_VIEW\'\"

 

REFERENCES

NOTE:341733.1  - Export/Import DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects

About Me

...............................................................................................................................

本文来自于MOS转载文章(文档 ID 1640392.1)

小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群:230161599     微信群:私聊

联系我请加QQ好友(642808185),注明添加缘由

版权所有,欢迎分享本文,转载请保留出处

...............................................................................................................................

手机长按下图识别二维码或微信客户端扫描下边的二维码来关注小麦苗的微信公众号:xiaomaimiaolhr,免费学习最实用的数据库技术。

wpsF8C8.tmp

 

ico_mailme_02.png

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-2130834/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/26736162/viewspace-2130834/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值