Oracle import 心得

imp error _其中某些index無法成功import


. . importing table    "WP_SUB_XXX"    7609037 rows imported

IMP-00017: following statement failed with ORACLE error 1658:

 "CREATE INDEX "WP_SUB_PICT_PIC_IDX" ON "WP_SUB_PAID_INFO_CODE_TBL" ("PAID_IN"

 "FO_CODE" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 346161152 NE"

 "XT 131072 MINEXTENTS 1 MAXEXTENTS 8192 PCTINCREASE 0 FREELISTS 1 FREELIST G"

 "ROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "INDX" LOGGING"

IMP-00003: ORACLE error 1658 encountered

ORA-01658: unable to create INITIAL extent for segment in tablespace INDX

IMP-00017: following statement failed with ORACLE error 20000:

 "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"WP_SUB_PICT_PIC_IDX"',NULL,NULL,NU"

 "LL,5915993.14285714,37008,78,474,2,724299,2,0); END;"

IMP-00003: ORACLE error 20000 encountered

''ORA-20000: Unable to set values for index WP_SUB_PICT_PIC_IDX: does not exist or insufficient privileges''

ORA-06512: at "SYS.DBMS_STATS", line 3718

ORA-06512: at "SYS.DBMS_STATS", line 3823

ORA-06512: at line 1




-
1.要確定target 資料庫裡面 user 是否有create index 的權限  (grant resource to bvuser;)

2.或是User 在tablespace 裡面有無quota?  (grant unlimited tablespace to user; )

3.或有可能遇到tablespace datafile大小達到上限
ex:
8k block size , 單一 datafile size : 32GB

4.當初建index 的時候是用特定USER連到資料庫建的，所以匯入時，DDL 中不會顯示出index owner，而造成misleading....

例如create index INDEX_NAME on... 要改成 create index USER_NAME.INDEX_NAME...