In 7.x? I don't think so.Quote:
Originally posted by ocpwannabe
you should look into lmt
Printable View
In 7.x? I don't think so.Quote:
Originally posted by ocpwannabe
you should look into lmt
Hi, Jeff:
It's the time of the month again for us to do import into the above mentioned database. I was busy with something else, so the problem is still there.
I am asking Jeff about this is because last time in your reply you mentioned initial or next extent too large could cause this ora-01652 error. Why is that? Could you explain a little bit? The initial size for this tablespace is 1048576 and next is 16384, the pct_increase is 50%. What would be the appropriate size for them? The full message is: ORA-01652: unable to extend temp segment by 794170 in tablespace XYZ.
Thank you!
Lisa
$oerr ora 1652Quote:
Originally posted by lgaorcl
The full message is: ORA-01652: unable to extend temp segment by 794170 in tablespace XYZ.
01652, 00000, "unable to extend temp segment by %s in tablespace %s"
// *Cause: Failed to allocate an extent for temp segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.
Your TEMP tablespace doesn't have a chunk of 794170 bytes space to extend. You have to make sure initial_extent size and next_extent are of same size and pct_increase 0 to avoid fragmentation.
http://www.oreilly.com/catalog/oress...ter/defrag.pdf
Though, you asked jeff I thought I would be able to help you with this error. See the doc for insight info on fragmentation.
Thanks for the reply! I have some questions here:
1. Is 794170 bytes or blocks?
2. Do I have to make initial, next the same size? If I do, how much they should be?
3. How about I don't have free space to add? Can changing initial, next, and pct_increase parameter solve the problem?
Thanks again for the help!
1. Is 794170 bytes or blocks?Quote:
Originally posted by lgaorcl
I have some questions here:
**Bytes not blocks
2. Do I have to make initial, next the same size? If I do, how much they should be?
**Use any of 64K/128K/256K/1024K. Read the doc I have given to pick right size for extent size. Really doesn't matter with the size. Make sure its multiple of 64K/128K...
3. How about I don't have free space to add? Can changing initial, next, and pct_increase parameter solve the problem?
**I can't test it, as I don't have V7 database. For TEMP tablespace , you should be able to alter initial,next and pct_increase parameters.
Your segment is extending 16384*1.5 the first time. The next time, your segment is extending 16384*1.5*1.5, the next 16384*1.5*1.5*1.5. Eventually, your segment size gets so big that it can not find a free contiguous extent. My suggestion would be to decrease your pctincrease to 0 so you can control the size of your next extent.
16K times 1.5 raised to 9.57 is when you run out of room - you're extending at least 8 times with that 50% increase.
Is it possible to just TRUNCATE the tables and then load your data. Wouldn't this solve the problem.
MH
Nothing needs to be done its just TEMP tablespace. It couldn't extend when it needs more space... thats it. By altering PCT_INCREASE OR initial_extent,next_extent and pct_increase she should be able to get rid of this error.
Thanks for the reply! I just wanted to clear a few things.
It's not a temp tablespace. It's a data tablespace.
We actually drop the tables, not deleting the tables.
The database version is 7.1.3, so there is no way to coalesce the tablespace.
There are plenty of free space in the tablespace, just not contiguous.