Re: resizing initial extent downwards

Thanks guys for the great response, Jonathan's solution has proved to be our best option.

for the record:-

this was out case:
before delete: initial = 250Mb
after reimport: initial = 250Mb
alter table xxx deallocate unused keep 16k after alter: initial = 1.5Mb

so its upsized to initial to fit the entire table into the initial extent.

From here we will generate a script to run this alter on all tables within a tablespace.

Alter table XXX deallocate unused keep 20M;

Not sure which version of Oracle this first appeared in. 7.3.late I think.

This frees up space below the high water mark - of which there should be lots if you've just re-imported the table.

--
Jonathan Lewis

Steve Salvemini wrote:


> 

> What we'd like to do is delete 90% of a table (where the initial extent

> has been extended to take up the whole table ie. 250Mb), and reclaim

> that space.

> 

> We initially thought that by exporting the table and re-importing it,

> the initial

> extent would be set to the actual size of the table. It doesn't though,

> it keeps it at 250Mb, where the data now only needs 20Mb as an initial

> extent.

> 

> We are doing this same process for some 200+ tables so a generic

> solution would be best.

> 

> Any ideas?

> 

> Thanks

> 

> Steve


-- 
-----------------------------------------------------------

Steven Salvemini
Peoplesoft Technical Specialist, Information Technology Services
ADELAIDE UNIVERSITY SA 5005
AUSTRALIA
Tel: +61 8 8303 6358  Fax: +61 8 8303 4400
Email: steve.salvemini_at_adelaide.edu.au

----------------------------------------------------------- 
This email message is intended only for the addressee(s) 
and contains information which may be confidential and/or
copyright.  If you are not the intended recipient please
do not read, save, forward, disclose, or copy the contents
of this email. If this email has been sent to you in error,
please delete this email and any copies or links to this
email completely and immediately from your system.  No 
representation is made that this email is free of viruses.
Virus scanning is recommended and is the responsibility of 
the recipient.