-
Help, Here is the scenario. I am trying to import a schema (for days now) from a database on another machine to a new machine. I have created all the tablespaces, roles, users, etc. Some tables import ok others are getting an error.
Below is the tablespaces from the old database.
TABLESPACE_NAME INITIAL_EX NEXT_EXTEN MIN_EXTENT MAX_EXTENT PCT_INCREA MIN_EXTLEN STATUS CONTENTS LOGGING
-------------------------------------- ---------- ---------- ---------- ---------- ---------- --------- --------- ---------
SYSTEM 12288 12288 1 249 50 0 ONLINE PERMANENT LOGGING
RBS 1048576 1048576 10 249 0 0 ONLINE PERMANENT LOGGING
TEMP 1048576 1048576 1 2147483645 0 0 ONLINE TEMPORARY LOGGING
TOOLS 20480 20480 1 249 50 0 ONLINE PERMANENT LOGGING
USERS 20480 20480 1 249 50 0 ONLINE PERMANENT LOGGING
MY 16384 8192 1 2147483645 0 0 ONLINE PERMANENT LOGGING
SPA_DATA 20480 40960 1 2147483645 0 0 ONLINE PERMANENT LOGGING
BUS_DATA 20480 40960 1 500 0 0 ONLINE PERMANENT LOGGING
MY_INDEX 20480 40960 1 2147483645 0 0 ONLINE PERMANENT LOGGING
9 rows selected.
Tablespaces from the new database.
TABLESPACE_NAME INITIAL_EX NEXT_EXTEN MIN_EXTENT MAX_EXTENT PCT_INCREA MIN_EXTLEN STATUS CONTENTS LOGGING
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- --------- --------- ---------
SYSTEM 12288 12288 1 249 50 0 ONLINE PERMANENT LOGGING
RBS 1048576 1048576 10 249 0 0 ONLINE PERMANENT LOGGING
TEMP 1048576 1048576 1 2147483645 0 0 ONLINE TEMPORARY LOGGING
TOOLS 20480 20480 1 249 50 0 ONLINE PERMANENT LOGGING
USERS 20480 20480 1 249 50 0 ONLINE PERMANENT LOGGING
MY 16384 8192 1 2147483645 0 0 ONLINE PERMANENT LOGGING
MY_INDEX 20480 40960 1 2147483645 0 0 ONLINE PERMANENT LOGGING
BUS_DATA 20480 40960 1 2147483645 0 0 ONLINE PERMANENT LOGGING
SPA_DATA 20480 40960 1 2147483645 0 0 ONLINE PERMANENT LOGGING
9 rows selected.
Error from Import:
ORA-01658: unable to create INITIAL extent for segment in tablespace MY_INDEX
IMP-00017: following statement failed with ORACLE error 1658:
"CREATE INDEX "S30_IX2" ON "S30" ("SP_FID" ) PCTFREE 5 INITRANS 2 MAXTRANS "
"255 STORAGE (INITIAL 409600 NEXT 102400 MINEXTENTS 1 MAXEXTENTS 500 PCTINCR"
"EASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SDE_I"
"NDEX" LOGGING"
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace MY_INDEX
. . importing table "S31" 7855 rows imported
IMP-00017: following statement failed with ORACLE error 1658:
"CREATE UNIQUE INDEX "S31_IX1" ON "S31" ("GX" , "GY" , "EMINX" , "EMINY" , ""
"EMAXX" , "EMAXY" , "SP_FID" ) PCTFREE 5 INITRANS 2 MAXTRANS 255 STORAGE (I"
"NITIAL 1024000 NEXT 102400 MINEXTENTS 1 MAXEXTENTS 500 PCTINCREASE 0 FREELI"
questions:
*** I am assumming that the import is trying to create an initial extent for index s30_IX2 of 409600,
and an initial extent for index s31_IX1 of 1024000. the old database had an initial extent of 20480. How did these extents get larger than 20480?
***same thing with the next extent was to be 40960 but both of these next extents are looking for 102400. How did they grow bigger than the original storage.
***Is this the reason why my import fails?
***What do you sugget I do, increasing the initial and next extent seems very high. Is this ok to do?
Thanks,
Kathy
-
exp - imp eliminate the segment extent, so if you have a segment made on 10 extnets...you'll have e segment made on 1 extent (as sum of old extents). So increase the tablespace size and retry the import (only of the table failed...after droping the new one). i don't sugest you change the initial extent of the index because in creation it will take all the extents necessary for the creation ...(and prob. you'll take error)
-
I guess while taking export you mentioned compress=y so its trying to compress the exisiting data to 1 extent and placing into 1 single extent while importing.
couple of options to deal with.
choose compress=n option for export.
else try first importing with no data rows=n and then data with rows=y
create tablespace with enough space.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|