Archive

Archive for the ‘impdp’ Category

Restart import Job

September 1, 2011 Leave a comment

1. Attach the import job

[oracle@vmrac1 oracle]$ impdp system/oracle attach=SYS_IMPORT_SCHEMA_05

Import: Release 11.1.0.7.0 – 64bit Production on Thursday, 01 September, 2011 15:55:16

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Release 11.1.0.7.0 – 64bit Production
With the Real Application Clusters option

Job: SYS_IMPORT_SCHEMA_05
Owner: SYSTEM
Operation: IMPORT
Creator Privs: TRUE
GUID: ABCB8691568FDB9EE040030A5ADF367F
Start Time: Thursday, 01 September, 2011 15:55:27
Mode: SCHEMA
Instance: vstbdev1
Max Parallelism: 1
EXPORT Job Parameters:
CLIENT_COMMAND        system/******** DIRECTORY=expdp_dir DUMPFILE=POC_data.DMP FULL=y LOGFILE=expfull.log version=11.1.0
IMPORT Job Parameters:
Parameter Name      Parameter Value:
CLIENT_COMMAND        system/******** parfile=imp.par
TABLE_EXISTS_ACTION   REPLACE
State: IDLING           <– The job is idle       
Bytes Processed: 1,765,419,680
Percent Done: 99
Current Parallelism: 1
Job Error Count: 0
Dump File: /bkup/oracle/POC_DATA.DMP

Worker 1 Status:
Process Name: DW01
State: UNDEFINED
Object Schema: NE
Object Name: R424_SDE_ROWID_UK
Object Type: DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Completed Objects: 1,224
Worker Parallelism: 1

2. Restart the job
Import> start_job

Import> status

Job: SYS_IMPORT_SCHEMA_05
Operation: IMPORT
Mode: SCHEMA
State: EXECUTING    <– The job is running     
Bytes Processed: 1,765,419,680
Percent Done: 99
Current Parallelism: 1
Job Error Count: 0
Dump File: /bkup/oracle/POC_DATA.DMP

Worker 1 Status:
Process Name: DW01
State: EXECUTING

Import>

Advertisements
Categories: impdp

ORA-39083: Object type INDEX_STATISTICS failed to create with error:

August 17, 2011 Leave a comment

I import a schema by impdp. There are errors returned as following.

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 IND_NAME VARCHAR2(60);   IND_OWNER VARCHAR2(60);   colname DBMS_METADATA.T_VAR_COLL; BEGIN  DELETE FROM “SYS”.”IMPDP_STATS”;   colname(1) := ‘PLAYER_ID’;  DBMS_METADATA.GET_STAT_INDNAME(‘SPORT’, ‘GEN_PLAYER’, colname, 1, ind_owner, ind_name);   INSERT INTO “SYS”.”IMPDP_STATS” (type, version, flags, c1, c2, c3, c5,
n1, n2, n3, n4, n5, n6, n7, n8, n9,
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
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [TABLE_STATISTICS]
ORA-06502: PL/SQL: numeric or value error
LPX-00225: end-element tag “HIST_GRAM_LIST_ITEM” does not match start-element tag “EPVALUE”
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPW$WORKER”, line 7839

Then I import the schema again with “EXCLUDE=STATISTICS”, it works successfully.  After that I gather the schema statistics by command below.

SQL> exec dbms_stats.gather_schema_stats(‘sport’);

Categories: impdp, ORA-XXX