My Oracle Support Banner

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

Last updated on OCTOBER 06, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 19.17.0.0.0 and later
Information in this document applies to any platform.

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


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