database fragmentation

[lgaorcl]

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!

[marist89]

Why do you think it's fragmented? If you export/import once a month, I doubt it is fragmented.

[lgaorcl]

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!

[zainalj]

i think that is the bad database administration way.

[lgaorcl]

Jeff:

What do you think now?

Anybody have more concrete, constructive opinions? I appreciate it!

Thanks!

[marist89]

Originally posted by lgaorcl
The delete won't release the space.

So? The space will be reused next time you import.

[lgaorcl]

But why do we get ora-01652 so often?

[Orca777]

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

[marist89]

Originally posted by lgaorcl
But why do we get ora-01652 so often?

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.

[ocpwannabe]

you should look into lmt