-
I have a objectname table objx size 72.66 Mbs with initial of
1,048,576 with NUMBER OF EXTENTS 263 and MAX EXTENTS
of 505. size 76 megs.
since number of extents is near max extents I am looking for guidance in what to do to make sure that I dont run out of extents.
would i wanna increase max extents or export then coalesce then import with new initial etc.?
"High Salaries = Happiness = Project Success."
-
Originally posted by jlakhani
since number of extents is near max extents I am looking for guidance in what to do to make sure that I dont run out of extents.
would i wanna increase max extents or export then coalesce then import with new initial etc.?
Depends on your version of Oracle. If you are on 8.1.x, you can either set the maxextents to unlimited (ALTER TABLE xyz STORAGE (maxextents UNLIMITED);) or you could "move" the table with larger storage paramters (ALTER TABLE xyz MOVE TABLESPACE same_tbs STORAGE (INITIAL 8M next 8M MAXEXTENTS UNLIMITED);)
Jeff Hunter
-
QUESTION:I have a objectname table objx size 72.66 Mbs with initial of 1,048,576 with NUMBER OF EXTENTS 263 and MAX EXTENTS of 505. size 76 megs.
What I would like to do is decrease the number of extents. I believe If I exp the table compress extents then resize the initial and next then I imp back then my extents should be fine.
what are the procedures and what do i need to keep in mind?
"High Salaries = Happiness = Project Success."
-
1. export the table (default will be compress=y)
2. drop the table
3. precreate the table with (say) initial=table_size. Check for this space size in the ts, since there will be ts frag. upon dropping the table. If necessary, increase ts size to accomodate initial extent size.
4. import table with ignore=y
5. create indexes associated with the table.
-
Halo
I have over 500 Megs left on the tablespace and since the current initial is 1,048,576 with NUMBER OF EXTENTS 263
what how big should i make the initial>?
"High Salaries = Happiness = Project Success."
-
You can size the initial to be the size of the table itself, or initial=next=(say) half the table size. Another thing to keep in mind is the rate of table growth and setting next and pctinc. accordingly.
-
With the information you provided I would say your tablespace is fragmented.
Thats depends upon the max chunk size you have on the tablespace. If you do have max free chunk of 25/50 meg you can say your inital extent size equal to 25/50 M.
Whatever you do, you run outta space if it looks for space requirement in future unless you extend your tablespace size.
Reddy,Sam
-
Sam you mean Max extents by max chunks?
Max extents is 505
"High Salaries = Happiness = Project Success."
-
Max chunk meaning the largest free extent size in the ts. If this is relatively small, you might want to:
1. increase size of ts to have a larger extent OR
2. set inital=largest free extent size, next=next largest extent size.
-
I meant Max free space chunk you have on your tablespace, not max extents.
If and only if you have that much freespace chunk(you are trying to allocate for your initial extent) your allocation will be succesfull otherwise it can't allocate and you get an error.
Reddy,Sam
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
|