Home » RDBMS Server » Server Utilities » IMPDP ERROR ORA-31693: on ORACLE VIRTUAL COLUMN with Not null (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production)
IMPDP ERROR ORA-31693: on ORACLE VIRTUAL COLUMN with Not null [message #588137] Fri, 21 June 2013 04:42 Go to next message
arulsaran
Messages: 26
Registered: May 2013
Location: bangalore
Junior Member
Hi Team,

I am having issue with IMPDP on ORACLE VIRTUAL COLUMNS.

I am having following table with Virtual column defined with Not null. Expdp is fine without any issue.

DDL :
------
CREATE TABLE alert_hist
(
alertky INTEGER NOT NULL,
alertcreatedttm TIMESTAMP(6) DEFAULT systimestamp NOT NULL,
alertcreatedt DATE GENERATED ALWAYS AS (To_date(Trunc("alertcreatedttm"))) VIRTUAL NOT NULL
)
/

When I do the import (IMPDP) it got failed with the following error.

. . imported "TESTSCHEMA"."VALART" 359.1 KB 4536 rows
ORA-31693: Table data object "TESTSCHEMA"."ALERT_HIST" failed to load/unload and is being skipped due to error:
ORA-39097: Data Pump job encountered unexpected error -1

After that I dropped the Virtual Not null column and recreated that column with Nullable.

DDL :
-----
alter table alert_hist drop column alertcreatedt;
alter table alert_hist add alertcreatedt DATE GENERATED ALWAYS AS (To_date(Trunc("alertcreatedttm"))) VIRTUAL;

After that I took the expdp and impdp , it went fine with out any issue.

Need help on this , why the import is failing with the virtual not null columns.

Re: IMPDP ERROR ORA-31693: on ORACLE VIRTUAL COLUMN with Not null [message #588158 is a reply to message #588137] Fri, 21 June 2013 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Maybe a bug, did you search on Metalink?
Or maybe expected, did you search in documentation?

Regards
Michel
Re: IMPDP ERROR ORA-31693: on ORACLE VIRTUAL COLUMN with Not null [message #588159 is a reply to message #588158] Fri, 21 June 2013 08:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Also don't you have an "ORA-54013: INSERT operation disallowed on virtual columns" error?

Regards
Michel
Re: IMPDP ERROR ORA-31693: on ORACLE VIRTUAL COLUMN with Not null [message #588160 is a reply to message #588159] Fri, 21 June 2013 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
From the 2, the problem is quite obvious.

If NOT NULL then the column must be provided on insert but it is not allowed to provide a value on insert for virtual column.

Regards
Michel
Re: IMPDP ERROR ORA-31693: on ORACLE VIRTUAL COLUMN with Not null [message #588222 is a reply to message #588160] Sun, 23 June 2013 23:36 Go to previous messageGo to next message
arulsaran
Messages: 26
Registered: May 2013
Location: bangalore
Junior Member
Michel Cadot @"ORA-54013: INSERT operation disallowed on virtual columns"

I did't get this error. Because,with virtual nullable column the IMPDP is going fine.
I did checked in the documentation, i could't find out any related document for virtual column with Not null.

I will raise it with Meta Link (I am waiting to get the access from my company).
Re: IMPDP ERROR ORA-31693: on ORACLE VIRTUAL COLUMN with Not null [message #588224 is a reply to message #588222] Mon, 24 June 2013 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I did't get this error. Because,with virtual nullable column the IMPDP is going fine.


Which was not the case in 11.1, due to a bug (another one).

Regards
Michel
Re: IMPDP ERROR ORA-31693: on ORACLE VIRTUAL COLUMN with Not null [message #588225 is a reply to message #588224] Mon, 24 June 2013 00:29 Go to previous messageGo to next message
arulsaran
Messages: 26
Registered: May 2013
Location: bangalore
Junior Member
ok.. Our's is 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
Re: IMPDP ERROR ORA-31693: on ORACLE VIRTUAL COLUMN with Not null [message #588227 is a reply to message #588225] Mon, 24 June 2013 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I saw it, just wanted to be sure it is fixed... Smile

Regards
Michel
Re: IMPDP ERROR ORA-31693: on ORACLE VIRTUAL COLUMN with Not null [message #665133 is a reply to message #588227] Tue, 22 August 2017 23:48 Go to previous messageGo to next message
Berny
Messages: 4
Registered: June 2009
Location: Munich
Junior Member
This is bug 18153657, fixed in 12.1.0.2 and higher versions.
See Metalink: 12c Import Virtual Column Defination Changes ORA-01442 (Doc ID 1983171.1)
Re: IMPDP ERROR ORA-31693: on ORACLE VIRTUAL COLUMN with Not null [message #665134 is a reply to message #665133] Wed, 23 August 2017 00:05 Go to previous message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Thanks for the information.

Previous Topic: Master child sequence load in sql loader
Next Topic: IMP-00010: not a valid export file, header failed verification
Goto Forum:
  


Current Time: Mon Dec 23 03:02:55 CST 2024