Datapump Does not Export Permissions on Objects Owned by SYS

This post was sponsored by Technimove 

I was reminded recently that Datapump does not export permissions on objects owned by SYS so I decided to write a post about it for my blog. It was tested on an Oracle 11.2.0.1 database. First I created a user called USER1:

SQL> conn / as sysdba
Connected.
SQL> create user user1
  2  identified by user1
  3  /

User created.

SQL> grant create session to user1
  2  /

Grant succeeded.

SQL>

I logged in as USER1 and showed that it did not have execute permission on SYS.DBMS_LOCK:

SQL> conn user1/user1
Connected.
SQL> exec dbms_lock.sleep(1);
BEGIN dbms_lock.sleep(1); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_LOCK' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> 

I granted permission then logged in as USER1 again to check it had worked:

SQL> conn / as sysdba
Connected.
SQL> grant execute on dbms_lock to user1
  2  /

Grant succeeded.

SQL> conn user1/user1
Connected.
SQL> exec dbms_lock.sleep(1);

PL/SQL procedure successfully completed.

SQL>

I used expdp to export the schema with the following parameters:

content=all
directory=andrews_datapump_dir
dumpfile=andrew.dmp
logfile=andrew_exp.log
schemas=user1

I used impdp to import the dumpfile into a different schema with the following parameters:

content=all
directory=andrews_datapump_dir
dumpfile=andrew.dmp
logfile=andrew_imp.log
remap_schema=user1:user2 

…but when I logged in as USER2, it did not have execute permission on SYS.DBMS_LOCK:

SQL> conn user2/user1
Connected.
SQL> exec dbms_lock.sleep(1);
BEGIN dbms_lock.sleep(1); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_LOCK' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL>

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

ORA-39095

I kicked off an expdp with the following parameters. I set the filesize parameter but forgot to set the dumpfile parameter to match:

content=all

directory=data_pump_dir

dumpfile=andrew.dmp

exclude=table:"='DOCUMENTIMAGE'"

filesize=1000000000

full=y          

logfile=andrew.log

The job produced 1 file with the filesize I had specified:

D:\oracle\11.2.0\admin\AKTPROD\dpdump>dir andrew.dmp

Volume in drive D is Datas

Volume Serial Number is ECE8-9011

 

Directory of D:\oracle\11.2.0\admin\AKTPROD\dpdump

 

05/15/2015  12:20 PM       999,997,440 ANDREW.DMP

               1 File(s)    999,997,440 bytes

               0 Dir(s)  414,961,401,856 bytes free

 

D:\oracle\11.2.0\admin\AKTPROD\dpdump>

Then it failed with an ORA-39095:

ORA-39095: Dump file space has been exhausted: Unable to allocate 4096 bytes

Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at 12:20:13

I changed the dumpfile parameter as follows and reran the expdp:

content=all

directory=data_pump_dir

dumpfile=andrew%u.dmp

exclude=table:"='DOCUMENTIMAGE'"

filesize=1000000000

full=y

logfile=andrew.log

The %u was replaced by the numbers 01 through 99 inclusive as required and the expdp worked, producing 88 files:

D:\oracle\11.2.0\admin\AKTPROD\dpdump>dir andrew*.dmp

Volume in drive D is Datas

Volume Serial Number is ECE8-9011

 

Directory of D:\oracle\11.2.0\admin\AKTPROD\dpdump

 

05/15/2015  01:07 PM       999,997,440 ANDREW01.DMP

05/15/2015  12:59 PM       999,997,440 ANDREW02.DMP

05/15/2015  01:01 PM       999,997,440 ANDREW03.DMP

05/15/2015  01:01 PM       999,997,440 ANDREW04.DMP

05/15/2015  01:01 PM       999,997,440 ANDREW05.DMP

05/15/2015  01:02 PM       999,997,440 ANDREW06.DMP

05/15/2015  01:02 PM       999,997,440 ANDREW07.DMP

05/15/2015  01:02 PM       999,997,440 ANDREW08.DMP

05/15/2015  01:02 PM       999,997,440 ANDREW09.DMP

05/15/2015  01:03 PM       999,997,440 ANDREW10.DMP

Etc.

05/15/2015  01:32 PM       999,997,440 ANDREW80.DMP

05/15/2015  01:33 PM       999,997,440 ANDREW81.DMP

05/15/2015  01:33 PM       999,997,440 ANDREW82.DMP

05/15/2015  01:33 PM       999,997,440 ANDREW83.DMP

05/15/2015  01:34 PM       999,997,440 ANDREW84.DMP

05/15/2015  01:34 PM       999,997,440 ANDREW85.DMP

05/15/2015  01:35 PM       999,997,440 ANDREW86.DMP

05/15/2015  01:36 PM       999,997,440 ANDREW87.DMP

05/15/2015  01:38 PM       829,038,592 ANDREW88.DMP

              88 File(s) 87,828,815,872 bytes

               0 Dir(s)  326,559,186,944 bytes free

 

D:\oracle\11.2.0\admin\AKTPROD\dpdump>

I will try to look at what happens if you need more than 99 files in a future post.

How I Copied Triggers from one Database to Another

(This happened on Oracle 11.2.) In an earlier example, I started to look at doing a full expdp and impdp. I'm not sure why, but the Data Pump import produced a number of errors. There were well over 200 like this: 

ORA-39112: Dependent object type TRIGGER:"SRCE"."ADDRESS_BIU" skipped, base object type TABLE:"SRCE"."ADDRESS" creation failed 

This message suggested that the SRCE.ADDRESS table had not been created in the output database but, in fact, it had: 

SQL> desc srce.address

Name                       Null?    Type

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

UPDATE_COUNT               NOT NULL NUMBER

ADDRESS_ID                 NOT NULL NUMBER

SUB_PREMISES                        VARCHAR2(30)

PREMISES_NAME                       VARCHAR2(30)

PO_BOX                              VARCHAR2(30)

PREMISES_NUMBER                     VARCHAR2(10)

DEP_THOROUGHFARE                    VARCHAR2(30)

THOROUGHFARE                        VARCHAR2(30)

DOUB_DEP_LOCALITY                   VARCHAR2(30)

DEP_LOCALITY                        VARCHAR2(30)

POST_TOWN                           VARCHAR2(30)

COUNTY                              VARCHAR2(30)

POSTCODE                            VARCHAR2(8)

USES                                NUMBER

LAST_UPD_BY                         VARCHAR2(8)

LAST_UPD_TIME                       DATE

COUNTRY                             VARCHAR2(30)

BUILDING_GROUP_NAME                 VARCHAR2(30)

TCN                                 NUMBER 

However, none of the triggers had been created so my output database was over 200 triggers short, which, as you might expect, stopped it working properly.

 

I tried to extract DDL from the input database to create the triggers as follows but this failed too:

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM -

> (DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);

 

PL/SQL procedure successfully completed.

 

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM -

> (DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

 

PL/SQL procedure successfully completed.

 

SQL> select dbms_metadata.get_ddl

  2  ('TRIGGER','ADDRESS_BIU','SRCE') from dual

  3  /

ERROR:

ORA-03113: end-of-file on communication channel

Process ID: 13899

Session ID: 95 Serial number: 10069

 

no rows selected

 

SQL> conn / as sysdba

Connected.

SQL> select open_mode from v$database

  2  /

 

OPEN_MODE

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

READ WRITE

 

1 row selected.

 

SQL>

 

I wrote the following script and ran it against the input database:

 

col trigger_body newline

col slash newline

set feedback off

set pages 0

set lines 200

set long 100000

set longchunksize 200

set trimspool on

spool create_triggers.sql

select 'create or replace trigger '||

description,

trigger_body,

'/' slash

from dba_triggers

where owner = 'SRCE'

/

spool off

 

This is part of the SPOOL file:

 

create or replace trigger srce.ptr_product_pk_bi

  BEFORE INSERT ON ptr_product

  FOR EACH ROW

DECLARE

  -- local variables here

BEGIN

  IF :new.product_id IS NULL THEN

    SELECT ptr_product_id_seq.nextval

    INTO   :new.product_id

    FROM   DUAL;

  END IF;

END ptr_product;

/

 

… and when I ran it against the output database, it worked like this:

 

SQL> l

  1  create or replace trigger srce.ptr_product_pk_bi

  2    BEFORE INSERT ON ptr_product

  3    FOR EACH ROW

  4  DECLARE

  5    -- local variables here

  6  BEGIN

  7    IF :new.product_id IS NULL THEN

  8      SELECT ptr_product_id_seq.nextval

  9      INTO   :new.product_id

 10      FROM   DUAL;

 11    END IF;

 12* END ptr_product;

SQL> /

 

Trigger created.

 

SQL>

The SQL in the SPOOL file created the other 200+ missing triggers in the same way. When I get a moment, I will try to understand what caused the problems I had with impdp and dbms_metadata.get_ddl.

impdp REMAP_DATAFILE Parameter

I was asked to copy one database into another, which I usually do by copying the datafiles then recreating the control file. However, on this occasion, although both databases were on Oracle 11.2.0.4, the machine hosting the input database was X86 whereas the machine hosting the output database was Sparc. Data Pump seemed to be a better option.

 

I created the output database using dbca, which set up the SYSTEM, SYSAUX, TEMP, UNDOTBS1 and USERS tablespaces.

 

The datafiles on the input database had names beginning with /database/PQEPERF but the output database needed to have filenames starting with /cogdbase/pqecog2. When I do a full expdp / impdp, I precreate the tablespaces in the output database if the file systems have different names. In the past, I have extracted the tablespace creation DDL from the input database. Then I have done a global edit on the resultant SQL to change the file names to match the file system name(s) used by the output database. I could not find my SQL to do this so I decided to try something else.

 

I ran the following SQL on the input database to extract the datafile names. I'm sorry the printout is so small and has a mixture of fonts but if you copy and paste it into a text file, it should look better:

SQL> @remap_datafile

SQL> set echo on

SQL> spool remap_datafile

SQL> set feedback off

SQL> set lines 200

SQL> set pages 0

SQL> set trimspool on

SQL> select 'REMAP_DATAFILE="'''||file_name||''':''+'||file_name||'''"'

  2  from dba_data_files

  3  where tablespace_name not in

  4  ('SYSAUX','SYSTEM','UNDOTBS1','USERS')

  5  /

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/costing_data1.dbf':'+/database/PQEPERF/pqe_data/costing_data1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/costing_idx1.dbf':'+/database/PQEPERF/pqe_data/costing_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/costing_idx2.dbf':'+/database/PQEPERF/pqe_data/costing_idx2.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/datemove.dbf':'+/database/PQEPERF/pqe_data/datemove.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/event_monitor_data.dbf':'+/database/PQEPERF/pqe_data/event_monitor_data.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/event_monitor_index.dbf':'+/database/PQEPERF/pqe_data/event_monitor_index.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/mislog_data.dbf':'+/database/PQEPERF/pqe_data/mislog_data.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_a.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_b.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_c.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_d.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_e.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_f.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_f.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_g.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_g.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_h.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_h.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_i.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_i.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_idx1_a.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_idx1_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/slow_growth.dbf':'+/database/PQEPERF/pqe_data/slow_growth.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/slow_growth_idx1.dbf':'+/database/PQEPERF/pqe_data/slow_growth_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/stand.dbf':'+/database/PQEPERF/pqe_data/stand.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/stand_idx1.dbf':'+/database/PQEPERF/pqe_data/stand_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/static.dbf':'+/database/PQEPERF/pqe_data/static.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/static_idx1.dbf':'+/database/PQEPERF/pqe_data/static_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/tfe_indexes.dbf':'+/database/PQEPERF/pqe_data/tfe_indexes.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/transient.dbf':'+/database/PQEPERF/pqe_data/transient.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cai_data.dbf':'+/database/PQEPERF/pqe_data/cai_data.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_idx1_b.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_idx1_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/transient_idx1.dbf':'+/database/PQEPERF/pqe_data/transient_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/mtdconsumption_a.dbf':'+/database/PQEPERF/pqe_data/mtdconsumption_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_j.dbf':'+/database/PQEPERF/pqe_data/rapid_growth_j.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_a.dbf':'+/database/PQEPERF/pqe_data/cons_part_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_b.dbf':'+/database/PQEPERF/pqe_data/cons_part_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_c.dbf':'+/database/PQEPERF/pqe_data/cons_part_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_a.dbf':'+/database/PQEPERF/pqe_data/cons_part_idx_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_a.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_b.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_c.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_d.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_a.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_idx_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_d.dbf':'+/database/PQEPERF/pqe_data/cons_part_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_e.dbf':'+/database/PQEPERF/pqe_data/cons_part_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_e.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_b.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_idx_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_c.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_idx_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_d.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_idx_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_e.dbf':'+/database/PQEPERF/pqe_data/cons_arch_part_idx_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_b.dbf':'+/database/PQEPERF/pqe_data/cons_part_idx_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_c.dbf':'+/database/PQEPERF/pqe_data/cons_part_idx_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_d.dbf':'+/database/PQEPERF/pqe_data/cons_part_idx_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_e.dbf':'+/database/PQEPERF/pqe_data/cons_part_idx_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/mislog_data_1.dbf':'+/database/PQEPERF/pqe_data/mislog_data_1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/mislog_data_2.dbf':'+/database/PQEPERF/pqe_data/mislog_data_2.dbf'"

SQL> spool off

SQL>

Then I opened the SPOOL file with vi and ran the following global edit:

:%s?+/database/PQEPERF?/cogdbase/pqecog2?g

I then appended the SPOOL file onto the parfile I was going to use for the impdp so it looked like this:

CONTENT=ALL

DIRECTORY=ANDREWS_DATAPUMP

DUMPFILE=pqeperf.dmp

FULL=Y

LOGFILE=pqeperf.imp.log

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/costing_data1.dbf':'/cogdbase/pqecog2/pqe_data/costing_data1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/costing_idx1.dbf':'/cogdbase/pqecog2/pqe_data/costing_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/costing_idx2.dbf':'/cogdbase/pqecog2/pqe_data/costing_idx2.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/datemove.dbf':'/cogdbase/pqecog2/pqe_data/datemove.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/event_monitor_data.dbf':'/cogdbase/pqecog2/pqe_data/event_monitor_data.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/event_monitor_index.dbf':'/cogdbase/pqecog2/pqe_data/event_monitor_index.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/mislog_data.dbf':'/cogdbase/pqecog2/pqe_data/mislog_data.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_a.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_b.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_c.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_d.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_e.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_f.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_f.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_g.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_g.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_h.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_h.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_i.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_i.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_idx1_a.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_idx1_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/slow_growth.dbf':'/cogdbase/pqecog2/pqe_data/slow_growth.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/slow_growth_idx1.dbf':'/cogdbase/pqecog2/pqe_data/slow_growth_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/stand.dbf':'/cogdbase/pqecog2/pqe_data/stand.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/stand_idx1.dbf':'/cogdbase/pqecog2/pqe_data/stand_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/static.dbf':'/cogdbase/pqecog2/pqe_data/static.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/static_idx1.dbf':'/cogdbase/pqecog2/pqe_data/static_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/tfe_indexes.dbf':'/cogdbase/pqecog2/pqe_data/tfe_indexes.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/transient.dbf':'/cogdbase/pqecog2/pqe_data/transient.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cai_data.dbf':'/cogdbase/pqecog2/pqe_data/cai_data.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_idx1_b.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_idx1_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/transient_idx1.dbf':'/cogdbase/pqecog2/pqe_data/transient_idx1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/mtdconsumption_a.dbf':'/cogdbase/pqecog2/pqe_data/mtdconsumption_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/rapid_growth_j.dbf':'/cogdbase/pqecog2/pqe_data/rapid_growth_j.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_a.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_b.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_c.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_a.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_idx_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_a.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_b.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_c.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_d.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_a.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_idx_a.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_d.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_e.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_e.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_b.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_idx_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_c.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_idx_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_d.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_idx_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_arch_part_idx_e.dbf':'/cogdbase/pqecog2/pqe_data/cons_arch_part_idx_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_b.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_idx_b.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_c.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_idx_c.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_d.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_idx_d.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/cons_part_idx_e.dbf':'/cogdbase/pqecog2/pqe_data/cons_part_idx_e.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/mislog_data_1.dbf':'/cogdbase/pqecog2/pqe_data/mislog_data_1.dbf'"

REMAP_DATAFILE="'/database/PQEPERF/pqe_data/mislog_data_2.dbf':'/cogdbase/pqecog2/pqe_data/mislog_data_2.dbf'"

I kicked off the impdp. It has been running for an hour or so but will not finish for a long time. However, looking at the output database, several tablespaces have already been created and the REMAP_DATAFILE parameters seem to be working as expected: 

SQL> l

  1  select tablespace_name, file_name

  2  from dba_data_files

  3* order by 1,2

SQL> /

 

TABLESPACE_NAME      FILE_NAME

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

COSTING_DATA         /cogdbase/pqecog2/pqe_data/costing_data1.dbf

COSTING_IDX1         /cogdbase/pqecog2/pqe_data/costing_idx1.dbf

COSTING_IDX1         /cogdbase/pqecog2/pqe_data/costing_idx2.dbf

DATEMOVE             /cogdbase/pqecog2/pqe_data/datemove.dbf

EVENT_MONITOR_DATA   /cogdbase/pqecog2/pqe_data/event_monitor_data.dbf

EVENT_MONITOR_INDEX  /cogdbase/pqecog2/pqe_data/event_monitor_index.dbf

MISLOG_DATA          /cogdbase/pqecog2/pqe_data/mislog_data.dbf

MISLOG_DATA          /cogdbase/pqecog2/pqe_data/mislog_data_1.dbf

MISLOG_DATA          /cogdbase/pqecog2/pqe_data/mislog_data_2.dbf

SYSAUX               /cogdbase/pqecog2/pqe_system/sysaux01.dbf

SYSTEM               /cogdbase/pqecog2/pqe_system/system01.dbf

UNDOTBS1             /cogdbase/pqecog2/pqe_system/undotbs01.dbf

USERS                /cogdbase/pqecog2/pqe_system/users01.dbf

 

13 rows selected.

 

SQL>

6th December 2016:
Checked for relevance.
Shared on LinkedIn.

ORA-39083 and ORA-00439

I created a table with deferred segment creation in an Oracle 11.2.0.1.0 Enterprise Edition database:

 

SQL> create table andrew.tab1 (col1 number)

  2  segment creation deferred;

 

Table created.

 

SQL>

 

Then I created the following Data Pump parameter file:

 

C:\Users\AJ0294094>more datapump1.params

content=all

directory=data_pump_dir

dumpfile=andrew.dmp

logfile=andrew.log1

tables=andrew.tab1

 

C:\Users\AJ0294094>

 

… and used Data Pump to export the table I had just created:

 

C:\Users\AJ0294094>expdp system/secret_password1 parfile=datapump1.params

 

Export: Release 11.2.0.1.0 - Production on Fri Aug 30 18:07:35 2013

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit

Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** parfile=datapump1.params

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 0 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "ANDREW"."TAB1"                                 0 KB       0 rows

Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

  D:\ORACLE\11.2.0\ADMIN\MDMDEV1\DPDUMP\ANDREW.DMP

Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 18:07:40

 

C:\Users\AJ0294094>

 

I copied the dump file (andrew.dmp) from the source database’s Data Pump directory to the target database’s Data Pump directory. I assume you know how to do this yourself. Then I pointed my ORACLE_SID to the target database, which was set up using Oracle 11.2.0.1.0 Standard Edition, and created a new file of Data Pump parameters as follows:

 

C:\Users\AJ0294094>more datapump2.params

content=all

directory=data_pump_dir

dumpfile=andrew.dmp

logfile=andrew.log2

 

C:\Users\AJ0294094>

 

When I tried to import the table, the job failed as Standard Edition does not support deferred segment creation:

 

C:\Users\AJ0294094>impdp system/secret_password2 parfile=datapump2.params

 

Import: Release 11.2.0.1.0 - Production on Fri Aug 30 18:12:08 2013

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** parfile=datapump2.params

Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-39083: Object type TABLE:"ANDREW"."TAB1" failed to create with error:

ORA-00439: feature not enabled: Deferred Segment Creation

Failing sql is:

CREATE TABLE "ANDREW"."TAB1" ("COL1" NUMBER) SEGMENT CREATION DEFERRED PCTFREE 1

0 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS"

 

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 18:12:11

 

 

C:\Users\AJ0294094>

 

I created a third Data Pump parameter file as shown below using the sqlfile parameter:

 

C:\Users\AJ0294094>more datapump3.params

content=metadata_only

directory=data_pump_dir

dumpfile=andrew.dmp

include=table

logfile=andrew.log3

sqlfile=andrew.sql

 

C:\Users\AJ0294094>

 

Then I ran the Data Pump import again:

 

C:\Users\AJ0294094>impdp system/secret_password2 parfile=datapump3.params

 

Import: Release 11.2.0.1.0 - Production on Fri Aug 30 18:13:41 2013

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_SQL_FILE_FULL_01":  system/******** parfile=datapump3.params

Processing object type TABLE_EXPORT/TABLE/TABLE

Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at 18:13:43

 

C:\Users\AJ0294094>

 

This created a file of SQL in the Data Pump directory to precreate the table. Its contents were as follows. Note that the table is to be precreated with segment creation deferred:

 

-- CONNECT SYSTEM

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

-- new object type path: TABLE_EXPORT/TABLE/TABLE

CREATE TABLE "ANDREW"."TAB1"

   (  "COL1" NUMBER

   ) SEGMENT CREATION DEFERRED

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  TABLESPACE "USERS" ;

 

I amended the SQL to create the table with segment creation immediate and then I was able to run it. If you have this problem, and your file contains several create table statements, you should make this change using a global replacement command:

 

C:\Users\AJ0294094>sqlplus system/secret_password2

 

SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 30 18:19:09 2013

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

 

SQL> -- CONNECT SYSTEM

SQL> ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

 

Session altered.

 

SQL> ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

 

Session altered.

 

SQL> ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

 

Session altered.

 

SQL> ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

 

Session altered.

 

SQL> ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

 

Session altered.

 

SQL> ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

 

Session altered.

 

SQL> -- new object type path: TABLE_EXPORT/TABLE/TABLE

SQL> CREATE TABLE "ANDREW"."TAB1"

  2     (       "COL1" NUMBER

  3     ) SEGMENT CREATION IMMEDIATE

  4    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  5    TABLESPACE "USERS" ;

 

Table created.

 

SQL>

 

Finally, I created another parameter file and imported the data into the table. Of course in this case it was not necessary as there was only one table and it was empty. However, I am showing this step because if you have to do this, your dump file may well contain a mix of tables, some with data and some without.

 

C:\Users\AJ0294094>more datapump4.params

content=data_only

directory=data_pump_dir

dumpfile=andrew.dmp

 

C:\Users\AJ0294094>impdp system/secret_password2 parfile=datapump4.params

 

Import: Release 11.2.0.1.0 - Production on Fri Aug 30 18:32:14 2013

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** parfile=datapump4.params

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "ANDREW"."TAB1"                                 0 KB       0 rows

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 18:32:18

 

C:\Users\AJ0294094>