Re: DK15789: How do a reclaim space after deleting table data in a datafile

From: Michael Draves <michael_draves_at_hotmail.com>
Date: 22 Mar 2003 09:05:35 -0800
Message-ID: <f28184a0.0303220905.49149997@posting.google.com>

If you deallocate all the space in the existing tables you have probably achieved just what you asked for and not what you want. The tablespace is still fragmented which you could see if you have TABLESPACE MANAGER. The tables segments will be shrunk but scattered over the tablespace instead of contiguous. Even if it takes more space for empty tables a better goal might be to have all segment sizes the same so if a table is dropped all the space is reuseable. See the article "How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation" especially to determine the segment size to use:
http://technet.oracle.com/deploy/availability/pdf/defrag.pdf http://www.oreilly.com/catalog/oressentials/chapter/defrag.pdf

Since you appear to have a good export and hopefully a good backup why don't you:

1. Run the import to get an indexfile script.
2. Copy this script to another for creating the tables
3. Leave only the Create table commands with 'REM ' in front of them.
4. Remove all the 'REM ' remarks commenting out these commands.
5. Set all the initial and next values to the same size and

pctincrease to 0
6. Drop all the the tables in the tablespace. 7. Coalesce the tablespaces that contained these tables and the indexes
8. Run the script to create the initial segments of the tables 9. Import the data allowing errors but don't create the indexes 10. Modify the original indexfile script and set all the initial and next values

to the same size and pctincrease to 0

11. Create the indexes using this modified index file script
12. Analyze the schemas
13. Change the default extent segment sizes on the tablespace if

possible

kwall7904_at_hotmail.com (DK15798) wrote in message news:<9acfd9f2.0303201828.14fa88e_at_posting.google.com>...
> I'm sorry if I was confusing, let me clarify. I have three data
> files,
> each one is a gig. Your are correct of course they do not grow or
> shrink
> (autoextend is off).
> My problem is exactly as you describe in your second paragraph.
> I am deleting and truncating the tables, but the tablespace usage is
> not being altered (it is not decreasing as I would hope). I do not
> want the datafile space to continue to be allocated to any particular
> table. I guess my question would then be, how do a remove the extents
> that are committed to my tables? Although, I'm not sure that is the
> correct question either. All I really want to do is have the
> tablespace show how much space is being used by the tables, not how
> much has been allocated for the tables.
> Just to clarify the rest... Yes I meant I dropped the tables,
> not deleted. On the import the options I specified were,
> 1. list just table data <no>
> 2. import grants <yes>
> 3. ignore errors <yes>
> 4. Import table data <yes>
> 5 Import entire database <yes>
>
> And yes I realize "that Oracle 7.3 is now exhibiting in the Museum of
> Ancient Computer History."
>
> I hope this helps you help me.
> Thanks again for your help.
>
Received on Sat Mar 22 2003 - 11:05:35 CST