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: 59416
Registered: March 2007
Location: Nanterre, France, http://...
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: 59416
Registered: March 2007
Location: Nanterre, France, http://...
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: 59416
Registered: March 2007
Location: Nanterre, France, http://...
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: 59416
Registered: March 2007
Location: Nanterre, France, http://...
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 message
Michel Cadot
Messages: 59416
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I saw it, just wanted to be sure it is fixed... Smile

Regards
Michel
Previous Topic: Import from 11g to 10g results in strange errors
Next Topic: want to perform impdp command for specific function
Goto Forum:
  


Current Time: Wed Oct 22 08:55:44 CDT 2014

Total time taken to generate the page: 0.05527 seconds