關于Import error ora-01658
2024-07-21 02:33:57
供稿:網友
做一個eXP/imp lab:
create a user apple1 default tablespace user1.
create a user apple2 default tablespace user2.
apple1 create a table ABC on user1 tablespace.
我預備把ABC這個table匯出,然后匯入到apple2的schema名為QQQ的table,并且希望這個table要放在apple2的default tablespace (也就是user2)。
1. exp full=y comPRess=n rows =y file=test.dmp
2. imp system/jakfjks file=test.dmp fromuser=apple1 touser=apple2 tables=(ABC)
importing APPLE1's objects into APPLE2
IMP-00017: following statement failed with Oracle error 1658:
"CREATE TABLE "ABC" ("ID" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT"
"RANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE ""
"USER1" LOGGING NOCOMPRESS"
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace USER1
Import terminated sUCcessfully with warnings.
希奇,我明明是要import到user2這個tablespace上,為什么是user1 tablespace出現無法create extent的錯誤訊息?要新增extent應該也是在user2這個tablespace上呀?
這是因為,當你import的時候,他就像在執行 建立table的script,并且帶著這些參數 "TABLESPACE " "USER1" "LOGGING NOCOMPRESS" in the script,也就是說他會把table建立在原來的user1 tablespace但owner會為apple2 。
假如我不希望原來的ABC和新增的QQQ這兩個table放在同一個tablespace上怎么辦呢?
alter user apple2 quota unlimited on user2;
alter user apple2 quota 0 on user1;
So while importing this ABC get imported to user2 tablespace it won't use user1 tablespace~~