Friday, December 9, 2011

ORA-39083: Object type INDEX_STATISTICS failed ,ORA-01403: no data found

Once while importing , the above error occurred . I have  generated the sql file of this import operation. After analsing and googling , i found that this error may occurred  because any one index  is missing, for some reason, that why  the impdp utility fails importing the statistics associated to that particular missing index. In this case, the problem is generated because expdp utility puts the CREATE INDEX statements in wrong order into the dumpfile . Below are the details .

C:\>impdp system/xxxx@orcl  DUMPFILE=SHAIK72_01.dmp LOGFILE=SHAIK72_imp.log remap_schema= SHAIK72:SHAIK72
Import: Release 11.2.0.1.0 - Production on Fri Dec 9 10:31:59 2011
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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** DUMPFILE=SHAIK72_01.dmp LOGFILE=SHAIK72_imp.log remap_schema=
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SHAIK72"."AD_ARCHIVE"                      188.3 MB    1785 rows
. . imported "SHAIK72"."AD_ATTACHMENT"                   81.80 MB     240 rows
. . imported "SHAIK72"."T_REPORTSTATEMENT"               60.03 MB 1012428 rows
. . imported "SHAIK72"."AD_QUERYLOG"                     25.75 MB   98396 rows
. . imported "SHAIK72"."FACT_ACCT"                       25.82 MB  123994 rows
. . imported "SHAIK72"."T_TRIALBALANCE"                  13.90 MB   59977 rows
. . imported "SHAIK72"."AD_WF_ACTIVITY"                  18.46 MB  110882 rows
. . imported "SHAIK72"."AD_WF_EVENTAUDIT"                18.30 MB  110882 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-01403: no data found
ORA-01403: no data found
Failing sql is :
DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN  DELETE FROM "SYS"."IMPDP_STATS";   c(1) :=   DBMS_METADATA.GET_STAT_COLNAME('SHAIK72','C_BPARTNER','NULL ',NULL,0);  DBMS_METADATA.GET_STAT_INDNAME('SHAIK72','C_BPARTNER',c,1,i_o,i_n);   INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
ORA-39082: Object type VIEW:"SHAIK72"."DSI_V_RETAILER_STRUCT" created with compilation warnings
ORA-39082: Object type VIEW:"SHAIK72"."DSI_V_SHIPMENT_DETAILS" created with compilation warnings
ORA-39082: Object type VIEW:"SHAIK72"."M_STORAGE_V" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 6 error(s) at 10:59:21


Here are the workaroud to solve this issue .

1.) Use traditional export/import utility .

2.) First import the dump by excluding the indexes and later import the same dump by including the indexes as below :  
a.)  impdp system/xxxx DUMPFILE=SHAIK72_01.dmp LOGFILE=SHAIK72_imp.log remap_schema= SHAIK72:SHAIK72  exclude=indexes and then

b.)  impdp system/xxxx  DUMPFILE=SHAIK72_01.dmp LOGFILE=SHAIK72_imp1.log remap_schema= SHAIK72:SHAIK72  include=indexes 

3.)  On googling , i found that using parameter  EXCLUDE=STATISTICS may also solve this issue . Check the below link  for this parameter .
http://dbhk.wordpress.com/category/impdp/


Enjoy     :-) 

No comments: