We have a database on VAX/VMS which is for exp/imp once a month. Now it's very fragmented. How many ways do I have to defragment the database? The version of oracle is 7.1.6.
Thanks!
Printable View
We have a database on VAX/VMS which is for exp/imp once a month. Now it's very fragmented. How many ways do I have to defragment the database? The version of oracle is 7.1.6.
Thanks!
Why do you think it's fragmented? If you export/import once a month, I doubt it is fragmented.
When we import data now, we ofter get Ora-01652. And I checked the free space for those tablespaces, there are plenty of free space there. Some of the up to 3 to 4 gig. Another reason I thought it's fragmented it because everytime after import data, we delete the data and wait until next import. The delete won't release the space.
Thanks for the response. Tell me more what you think!
i think that is the bad database administration way.
Jeff:
What do you think now?
Anybody have more concrete, constructive opinions? I appreciate it!
Thanks!
So? The space will be reused next time you import.Quote:
Originally posted by lgaorcl
The delete won't release the space.
But why do we get ora-01652 so often?
Hi,
* First export the whole user-schema with COMPRESS=Y
* Then build an INDEXFILE with the imp-command
* This file is a DDL-FILE you can see now the initital/next extent of the tables.
* Then try to group the tables size in SMALL MEDIUM LARGE XLARGE
* For every sizecategory you specify a storage
* You could build for each size-group an own Tablespace.
* Edit the INDEXFILE and adopt the NEXT and INITIAL ( should be the same ) and the TABLESPACE-CLAUSE and PCTINCREASE should always be 0, depending on the size-category
* DROP all tables
* CREATE them thru the INDEXFILE
* import your objects with IGNORE=Y
So there should be new Tablespaces which all have Objects with same storage depending on size-category.
A Reorganization should for the existings object never more be neccessary, even with Oracle 7
Orca
Because you don't have enough contiguous space. This could be due to your INITIAL or NEXT values being too large. If you're terribly concerned about this error and you don't want to add the needed space, you should drop the tables before you import, coalesce the free space, and import.Quote:
Originally posted by lgaorcl
But why do we get ora-01652 so often?
you should look into lmt
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.
Then you are left with no choice than rebuilding your tablespace with new storage parameters to avoid this error.
Should I just alter tablespace to change the default parameters, Or should I drop the tablespace and recreate it?
Is there any difference between these two?
Thanks!
Altering the parameter when the tablespace is full doesn't help. You have to alter it when you drop few tables (remembered you saying you drop tables monthly) and you have some space over there...
There is difference. If you alter parameter that parameter take effect for the extents that will grown in future(I mean for the tables you will be loading). If you drop and recreate the tablespace, those parameters valid for all the tables you will be loading into tablespace.
Perhaps, you could recreate your tablespaces before you do the import. You might even do something crazy like upgrade to a 32 bit database like 8i. Isn't Oracle 7.1.x a 16 bit database?
The tablespace was empty before the new import. So I change the default parameter for the tablespace to initial 5120k, next 5120k and pct_increaase 0%. But today we did the import again, and the we got the same error message. It did not work!
Can anybody tell me why?
Thanks!
Can anybody tell me why it did not work?
Did you confirm that the storage parameters have been changed from 16384 to 5120K ? Check out your DBA_TABLESPACES view for reflected changes. By any chance are you taking exports of the tables with compress=y ? If so cahnge it to compress=n.
Yes, I did comfirm the change! The compress can only be no in this version!
These three parameters control extent sizes in the tablespace. You should not hit any fragmentation issues after setting this parameters. What error you got this time ?Quote:
Originally posted by lgaorcl
the default parameter for the tablespace to initial 5120k, next 5120k and pct_increaase 0%.
The fool-proof method would be to pre-create your tables with the correct INITIAL, NEXT, and PCTINCREASE values and import with ignore=y.
Jeff,
Let me understand this, after changing the parameters that control extent sizes in tablespaces there may be an error if the tablespace runs outta space, there shouldn't be any error having free space in the tablespace right ? It seems tablespace is running outta space.
I don't know how things were before 7.3. All my experiences are limited to only V7.3 thru 9i :)
The error message is exactly the same:
ORA-01652: unable to extend temp segment by 794170 (I am still wondering: is this number block size or bytes?) in tablespace xyz.
Since the storage parameters are in the export file they could:Quote:
Originally posted by sreddy
Jeff,
Let me understand this, after changing the parameters that control extent sizes in tablespaces there may be an error if the tablespace runs outta space, there shouldn't be any error having free space in the tablespace right ? It seems tablespace is running outta space.
I don't know how things were before 7.3. All my experiences are limited to only V7.3 thru 9i :)
1. change the TS parameters
2. export the tables which carry the old storage parameters
3. drop objects
4. imp objects with old storage parameters regardless of hwo the TS was setup.
This scenario would yield the same results as before.
The fact that the temporary segment is trying to grab over 775K where they specified the NEXT extent as 16K tells me that the new parameters are not being used.
By pre-creating the table to hold the data you can control the size of your extents BEFORE you start loading data.
Why it would make a difference if you create the table first with those parameters?
Thanks!
The export file "remembers" the previous storage parameters. If you export, drop, import, the tables will have the EXACT same storage definition as before.