How to resize objects down to min size

[rtefft]

I have a 500 table database with a total size of 6gb. However, ANALYZE or an export reveals only about 350mb of data in there. This tells me that the table/object sizes are huge compared to what we really need.

Does anyone know a way I can resize the tables down to the minimum needed size? For a size, I was thinking of NUM_ROWS * AVG_ROW_LEN from DBA_SEGMENTS after running the Analyze. The "compress extents" option for Export does not do this; it simply adds up all the extents for an object and creates a single extent the same size.

Resizing the tables by hand will take days/weeks...

Thanks in advance. ____________________________
Rich Tefft
PL/SQL Programmer/Instructor

 

[lfoata]

When you export a database, only the data is exported with the index and constraints definitions. So your database (including tables, indexes, constraints ) is much bigger than 350 Megs. Also export compresses the data.
Export also moves the data to free unused segments or blocks and makes the data contiguous.
You could write a sql or plsql than will use the formula you specified to create the new tables and then copy the old tables into the new tables, but I find it not very usefull.
The way I have done it and seen done it is with export. It is fast, efficient and does the job.
You may also look at resizing the tablespaces (datafiles). Even if you change the tables, if you dont resize the tablespaces, you are not going to free any disk space.

 

[rtefft]

As I said in my post, export does not reduce the total size of an object. Since my post, I exported with COMPRESS=Y and am currently importing, and find that the objects (though defragged) are still much larger than they have to be. They are the same size as before the export, but in a single large extent each instead of the multiple ones it had. I have tables with only 2k of data in them, but which may have an initial extent of 500k, 200K, or even 10M. (they are all in the same tablespace too).

I am running 8i Enterprise Edition 8.1.6.

I've been waiting to reduce my tablespaces... I just have to shrink the tables first. ____________________________
Rich Tefft
PL/SQL Programmer/Instructor

 

[carp]

At this point, since you've compressed extents, I think you might have to export the data, drop the tables, recreate them with the INITIAL size you want (along with indexes, constraints, triggers, etc), and import with IGNORE=Y.
Yep, it's ugly and it might take a while, but this is the only approach I can think of.

(Although....if you know how large you really want the INITIAL extent to be, you COULD edit your export dump file to change the INITIAL settings on the tables, drop the tables, and then import with the editted dump file. This approach DOES have risk associated with it, and Oracle will not support it, so you'd better have a second copy of your dump file so you can import using the unaltered dump file if things go bad. Also, be sure your text editor doesn't inject its own information into the dump file!).

 

[lfoata]

Ok, I am a bit slow but now I undestand that you want to reduce the initial extent of the object to less than what it was created with, and that export does not do.
You will have to do as carp says, unless there is a product I do not know of that does that.

 

[rtefft]

Rebuilding the tables seems to be the only method. Fortunately, I found a "Rebuild Table" utility in TOAD which scripts everything for me. I can only do 1 table at a time, but it takes little/no effort to do each one.

Thanks for the help, sorry I was not very clear. ____________________________
Rich Tefft
PL/SQL Programmer/Instructor