Thursday, July 23, 2015

ORA-39001, ORA-39000 and ORA-39142

I exported 3 large tables from an Oracle 11.2.0.4.0 database then tried to import them into an Oracle 11.1.0.6.0 database but the impdp failed as follows:

PQECOG3 /database/DB_exports/andrew > impdp / parfile=params
 
Import: Release 11.1.0.6.0 - 64bit Production on Wednesday, 22 July, 2015 11:37:44
 
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 3.1 in dump file "/database/DB_exports/andrew/andrew.dmp"
 
PQECOG3 /database/DB_exports/andrew >

You get these errors when you try to import a dump file produced by a newer version of Oracle. I added the version parameter at the end of the expdp parameter file. Incidentally, in case it isn't obvious, I have called both my parameter files params. However, they are on different servers and contain different parameters:

GBDMVDP1 /cisdpt/gbdmvdp1/datapump > cat params
content=all
directory=datapump
dumpfile=andrew.dmp
logfile=andrew.log
schemas=dmv
include=table:"in('S0141_BPD','D296_1234','D81_894')"
version=11.1.0.6.0
GBDMVDP1 /cisdpt/gbdmvdp1/datapump >

Then I reran the expdp and this produced a dumpfile which would be suitable for an Oracle 11.1.0.6.0 database. When I tried the impdp again, it worked:

PQECOG3 /database/DB_exports/andrew > impdp / parfile=params
 
Import: Release 11.1.0.6.0 - 64bit Production on Wednesday, 22 July, 2015 15:27:55
 
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ELEC_ORACLE"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ELEC_ORACLE"."SYS_IMPORT_FULL_01":  /******** parfile=params
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SRCE"."D296_1234"                          8.964 GB 289960470 rows
. . imported "SRCE"."D81_894"                            8.215 GB 267028514 rows
. . imported "SRCE"."S0141_BPD"                          2.384 GB 25849859 rows
Etc
Etc

Friday, July 10, 2015

Virtual Columns

Oracle 11g allows you to create virtual columns in a table. Values are not stored for these columns, Oracle calculates them at runtime. You can see the expression used to generate the value in the DATA_DEFAULT column of the USER_TAB_COLUMNS view.

As you might expect, you cannot UPDATE virtual columns directly. If you try, you get an ORA-54017.
 
It isn’t quite so obvious but you cannot use a virtual column to generate the value of another virtual column either. If you try, you get an ORA-54012
 
SQL> create table tab1
  2  (col1 number,
  3   col2 number,
  4   col3 number generated always
  5   as (col1 + col2) virtual)
  6  /
 
Table created.
 
SQL> insert into tab1(col1,col2) values(1,2)
  2  /
 
1 row created.
 
SQL> select * from tab1
  2  /
 
      COL1       COL2       COL3
---------- ---------- ----------
         1          2          3
 
SQL> column column_name format a15
SQL> column data_default format a15
SQL> select column_name, data_default
  2  from user_tab_columns
  3  where table_name = 'TAB1'
  4  /
 
COLUMN_NAME     DATA_DEFAULT
--------------- ---------------
COL1
COL2
COL3            "COL1"+"COL2"
 
SQL> update tab1 set col3 = 4
  2  /
update tab1 set col3 = 4
       *
ERROR at line 1:
ORA-54017: UPDATE operation disallowed on virtual
columns
 
SQL> drop table tab1
  2  /
 
Table dropped.
 
SQL> create table tab1
  2  (col1 number,
  3   col2 number,
  4   col3 number generated always
  5   as (col1 + col2) virtual,
  6   col4 number generated always
  7   as (col1 + col3) virtual)
  8  /
col3 number generated always
*
ERROR at line 4:
ORA-54012: virtual column is referenced in a column
expression
 
SQL>