ALL_UPDATABLE_COLUMNS Not Synchronized With Data Pump Import (Doc ID 2961498.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

When importing with Data Pump Import a view with a join and viewing the columns that can be updated, all columns are considered UPDATABLE=NO.

Whereas if we drop and recreate the view from SQL*Plus some columns are set to NO and others to YES, finally the property is as it should be.

If we perform an export

expdp /@PDBTEST directory=EXPORT schemas=TESTVIEW flashback_time=systimestamp dumpfile=expdp_TESTVIEW_TEST.dmp logfile=expdp_TESTVIEW_TEST.log

Import TESTVIEW again

impdp /@PDBTEST directory=EXPORT schemas=TESTVIEW dumpfile=expdp_TESTVIEW_TEST.dmp logfile=impdp_TESTVIEW_IOATST.log

Then execute the following query:

select * from ALL_UPDATABLE_COLUMNS where owner='TESTVIEW' and table_name='TESTTABLE';

indicates that all columns are UPDATABLE to NO, which is not correct.

To resynchronize the properties of the columns you have to delete and recreate the view.

Why does the view created by IMPDP not synchronize these properties correctly?

Changes

 If a DDL operation occurs on one of the tables referenced in the FROM clause of a view the enables or disables that Join View from being updatable.

The change is not instantly reflected in the ALL_UPDATABLE_COLUMNS view.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.