Thinking Out Loud

August 17, 2013

Datapump REMAP_TABLE and Statistics Feature or Bug ???

Filed under: 11g,oracle — mdinh @ 3:53 pm

Got a request from a friend:

One more thing , maybe for next post . I’ve noticed that during table_remap ie remap_table=gg.t:t1 there are no stats on t1 even if t has them and are included in dmp file . Bug or feature ?
Regards
Greg

What do you think? Is it a feature or a bug?

expdp_schema.par

directory=DATA_PUMP_DIR
userid='/ as sysdba'
metrics=Y
dumpfile=hr.dmp
tables=HR.EMPLOYEES
reuse_dumpfiles=Y

impdp_schema.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=hr.dmp
REMAP_SCHEMA=HR:DINH
REMAP_TABLE=EMPLOYEES:EMPS
TABLE_EXISTS_ACTION=REPLACE

expdp parfile=expdp_schema.par

Export: Release 11.2.0.3.0 - Production on Sat Aug 17 07:51:51 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA parfile=expdp_schema.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
     Completed 1 TABLE objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 2 OBJECT_GRANT objects in 1 seconds
Processing object type TABLE_EXPORT/TABLE/COMMENT
     Completed 12 COMMENT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
     Completed 6 INDEX objects in 1 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 4 CONSTRAINT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 6 INDEX_STATISTICS objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 3 REF_CONSTRAINT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/TRIGGER
     Completed 2 TRIGGER objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     Completed 1 TABLE_STATISTICS objects in 0 seconds
. . exported "HR"."EMPLOYEES"                            16.80 KB     107 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/pdb01/dpdump/hr.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 22:51:56

impdp parfile=impdp_schema.par

Import: Release 11.2.0.3.0 - Production on Sat Aug 17 07:53:02 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA parfile=impdp_schema.par
Processing object type TABLE_EXPORT/TABLE/TABLE
     Completed 1 TABLE objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DINH"."EMPS"                               16.80 KB     107 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 2 OBJECT_GRANT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/COMMENT
     Completed 12 COMMENT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
     Completed 6 INDEX objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 4 CONSTRAINT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 6 INDEX_STATISTICS objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "DINH"."EMPS" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "DINH"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "DINH"."EMPS" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "DINH"."JOBS" ("JOB_ID") ENABLE
     Completed 3 REF_CONSTRAINT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/TRIGGER
ORA-39083: Object type TRIGGER failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE TRIGGER update_job_history
  AFTER UPDATE OF job_id, department_id ON employees
  FOR EACH ROW
BEGIN
  add_job_history(:old.employee_id, :old.hire_date, sysdate,
                  :old.job_id, :old.department_id);
END;
ORA-39083: Object type TRIGGER failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE TRIGGER secure_employees
  BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
  secure_dml;
END secure_employees;
     Completed 2 TRIGGER objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39083: Object type TABLE_STATISTICS failed to create with error:
ORA-20000: TABLE "DINH"."EMPLOYEES" does not exist or insufficient privileges
Failing sql is:
DECLARE   c varchar2(60);   nv varchar2(1);   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';   s varchar2(60) := 'DINH';   t varchar2(60) := 'EMPLOYEES';   p varchar2(1);   sp varchar2(1);   stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,cl1) VALUES (:1,6,:2,:3
     Completed 1 TABLE_STATISTICS objects in 0 seconds
Job "SYS"."SYS_IMPORT_FULL_01" completed with 5 error(s) at 22:53:04

See the underlined error above? Looks like impdp is trying to import statistics to the original table.

Create table EMPLOYEES

SAN:(DINH@pdb01):PRIMARY> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMPS                           TABLE

SAN:(DINH@pdb01):PRIMARY> select count(*) from emps;

  COUNT(*)
----------
       107

SAN:(DINH@pdb01):PRIMARY> create table employees as select * from emps where 1=0;

Table created.

SAN:(DINH@pdb01):PRIMARY> exit

impdp parfile=impdp_schema.par

Import: Release 11.2.0.3.0 - Production on Sat Aug 17 07:54:05 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA parfile=impdp_schema.par
Processing object type TABLE_EXPORT/TABLE/TABLE
     Completed 1 TABLE objects in 1 seconds
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DINH"."EMPS"                               16.80 KB     107 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 2 OBJECT_GRANT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/COMMENT
     Completed 12 COMMENT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
     Completed 6 INDEX objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 4 CONSTRAINT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 6 INDEX_STATISTICS objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "DINH"."EMPS" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "DINH"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "DINH"."EMPS" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "DINH"."JOBS" ("JOB_ID") ENABLE
     Completed 3 REF_CONSTRAINT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"DINH"."UPDATE_JOB_HISTORY" created with compilation warnings
ORA-39082: Object type TRIGGER:"DINH"."UPDATE_JOB_HISTORY" created with compilation warnings
ORA-39082: Object type TRIGGER:"DINH"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"DINH"."SECURE_EMPLOYEES" created with compilation warnings
     Completed 2 TRIGGER objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     Completed 1 TABLE_STATISTICS objects in 0 seconds
Job "SYS"."SYS_IMPORT_FULL_01" completed with 6 error(s) at 22:54:06

Statistics imported and where did it go?

SAN:(DINH@pdb01):PRIMARY> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMPLOYEES                      TABLE
EMPS                           TABLE

SAN:(DINH@pdb01):PRIMARY> select count(*) from emps;

  COUNT(*)
----------
       107

SAN:(DINH@pdb01):PRIMARY> select count(*) from employees;

  COUNT(*)
----------
         0

SAN:(DINH@pdb01):PRIMARY> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
EMPS
EMPLOYEES                             107

SAN:(DINH@pdb01):PRIMARY>
About these ads

2 Comments »

  1. Hello, How I Fix this Object type REF_CONSTRAINT failed to create with error:???

    Comment by Carlos — October 21, 2013 @ 9:11 pm | Reply

  2. This is a great tip particularly to those new to
    the blogosphere. Short but very accurate information… Many thanks for sharing this one.
    A must read article!

    Comment by post st lucie pest control — March 10, 2014 @ 11:01 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 294 other followers

%d bloggers like this: