ORA-39166 – When Trying To Export The Data From A View

When i am trying to export table i am facing below error.
Error
====
ORA-39166: Object APEX_050100.xxxxxx  was not found or could not be exported or imported.
ORA-31655: no data or metadata objects selected for job.
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.

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

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
  • January 23, 2019 | 18 views
  • Comments