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

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>

A Simple Example Using COMPUTE SUM OF in SQL*Plus

I needed some SQL to show the time spent on idle events in an Oracle 11.2 database with a grand total at the end. I wrote this as shown below. The SQL*Plus syntax at the start is taken from the Oracle documentation but I wanted to record it so I would have my own worked example for future use: 

SQL> column dummy noprint;

SQL> compute sum of seconds_waited on dummy;

SQL> break on dummy;

SQL> select null dummy, event idle_event,

  2  round(time_waited/100) seconds_waited

  3  from v$system_event

  4  where wait_class = 'Idle'

  5  and round(time_waited/100) > 0

  6  order by seconds_waited

  7  /

 

IDLE_EVENT                                                       SECONDS_WAITED

---------------------------------------------------------------- --------------

PX Deq: Parse Reply                                                           1

SGA: MMAN sleep for component shrink                                          1

single-task message                                                           5

PX Deq: Execution Msg                                                         7

JOX Jit Process Sleep                                                       108

PL/SQL lock timer                                                           260

PX Idle Wait                                                               6000

jobq slave wait                                                           11025

VKRM Idle                                                                 14400

Streams AQ: waiting for time management or cleanup tasks                  75954

Space Manager: slave idle wait                                            77450

smon timer                                                                77459

pmon timer                                                                77657

Streams AQ: waiting for messages in the queue                             77671

Streams AQ: qmn slave idle wait                                           77729

Streams AQ: qmn coordinator idle wait                                     77740

DIAG idle wait                                                           155195

pipe get                                                                 241269

rdbms ipc message                                                       1319628

SQL*Net message from client                                            10756842

                                                                 --------------

                                                                       13046401

 

20 rows selected.

 

SQL>