Skip to Main Content
  • Questions

  • Expdb views_as_tables failes after view recreated, bug or feature?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: February 13, 2019 - 6:36 pm UTC

Last updated: February 15, 2019 - 1:57 am UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

This has been giving me fits.

I’m running 12.2.0.1 with no maintenance.


In the test case below I create a view TEST_VIEW with three columns A,B,C returning one row. Both select from the view and export of the view work as expected.

I then recreate the view using the same name but with only two columns, A,B (removing C.) Select works as expected, returning two columns, but export fails complaining about column C which was removed from the view!


(My real export problem runs on a host that I don’t have access to, so I could not review the DB log, just left scratching my head as to why things did not work!)

(Note the views_as_tables parm for expdp.)




SQL> connect john/ Connected.
SQL> create view test_view as select 1 a, 1 b, 1 c from dual;
View created.
SQL> select * from test_view;
A B C
---------- ---------- ----------
1 1 1
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


C:\Users\johnlow>expdp john/ directory=data_pump_dir dumpfile=x views_as_tables=test_view reuse_dumpfiles=y
Export: Release 12.2.0.1.0 - Production on Wed Feb 13 10:44:23 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "JOHN"."SYS_EXPORT_TABLE_01": john/******** directory=data_pump_dir dumpfile=x views_as_tables=test_view reuse_dumpfiles=y
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "JOHN"."TEST_VIEW" 5.898 KB 1 rows
Master table "JOHN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JOHN.SYS_EXPORT_TABLE_01 is:
D:\ORACLE\ADMIN\ORCL\DPDUMP\X.DMP
Job "JOHN"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 13 10:44:28 2019 elapsed 0 00:00:04


C:\Users\johnlow>sqlplus john/
SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 13 10:44:51 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Wed Feb 13 2019 10:44:23 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> create or replace view test_view as select 1 a, 1 b from dual;
View created.
SQL> select * from test_view;
A B
---------- ----------
1 1
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
C:\Users\johnlow>expdp john/ directory=data_pump_dir dumpfile=x views_as_tables=test_view reuse_dumpfiles=y
Export: Release 12.2.0.1.0 - Production on Wed Feb 13 10:45:53 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "JOHN"."SYS_EXPORT_TABLE_01": john/******** directory=data_pump_dir dumpfile=x views_as_tables=test_view reuse_dumpfiles=y
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
ORA-31693: Table data object "JOHN"."TEST_VIEW" failed to load/unload and is being skipped due to error:
ORA-00904: "C": invalid identifier
Master table "JOHN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JOHN.SYS_EXPORT_TABLE_01 is:
D:\ORACLE\ADMIN\ORCL\DPDUMP\X.DMP
Job "JOHN"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Wed Feb 13 10:45:58 2019 elapsed 0 00:00:04

and Connor said...

Yeah that looks like a bug to me.

We're leaving an object hanging around

SQL> select object_name , object_id from obj
  2  where created > sysdate - 1;

OBJECT_NAME                               OBJECT_ID
---------------------------------------- ----------
TEST_VIEW                                    159666 <====
KU$VAT_159666 <===                           159769


Drop that table and your second export will be fine.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

always look twice

Racer I., February 14, 2019 - 8:45 am UTC

Hi,

Just out of curiosity I checked MetaLink and found this referenced (although this is only about import):

https://docs.oracle.com/database/121/SUTIL/GUID-DAB87784-6D0A-4CB7-A16F-DC3969133C88.htm#BEGIN

> table_name--The name of a table to serve as the source of the metadata for the imported view.
By default Data Pump automatically creates a temporary "template table" with the same columns
and data types as the view, but no rows.
> SELECT * FROM user_tab_comments WHERE table_name LIKE 'KU$VAT%';

also another bug (28763360, export-performance not using parallel) with part of the
internal export statement showing something of whats going on here :

DIAGNOSTIC ANALYSIS
===================
-- only one worker it's active
-- the external table it's only create by one worker and with parallel 1
 
 ) ORGANIZATION EXTERNAL
   ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY "DATAPUMP_INTERFACE_DIR" ACCESS
PARAMETERS ( DEBUG = (3 , 33489664) DATAPUMP INTERNAL TABLE
"HR"."SAD_ZXDJ_FILTRE" TEMPLATE_TABLE "KU$VAT_12090450" JOB (
"HR","SYS_EXPORT_TABLE_01",57) WORKERID 1 PARALLEL 1 VERSION '12.2.0.1.0'
LOCATION ('bogus.dat') )  PARALLEL 1 REJECT LIMIT UNLIMITED
"TOTVAL", "DATEDR", "REPORI", "CODEDR", "CDAXES", "DRINSO", "VALEUR",
"USAGEP", "NUMTRT", "NUMBUL", "TIMEST"
   FROM "HR"."SAD_ZXDJ_FILTRE" AS OF SCN 10605310241 KU$
 
WORKAROUND?
===========
No

Strange that the template table is needed. The information should be available in user_tab_cols for views too (and be up to date there)

regards,
Connor McDonald
February 15, 2019 - 1:57 am UTC

Strange that the template table is needed.


Agreed.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.