Re: Compact Oracle Database (Shrink it using SQL Plus)

4 gig - I would drop the database completely - first perform a full export - recreate DB and smaller tablespaces - followed by full import.

If you truncate the tables - there's still a chance some lonely initial extent of segment is residing at the end of a datafile ... also if you truncate the tables and the perform an import with ignore = y - first disable triggers, drop indexes, and disable all constraints ... (but that's just a detail)

You could also use Quest Spacemanager to reorg your database (yeah GUI DBA rules ... not :-) )

"Howard J. Rogers" <howardjr_at_www.com> wrote in message news:3be058f8$0$15809$afc38c87_at_news.optusnet.com.au...
> Do you come from a MS SQL Server background by any chance? Because that
> sort of functionality does not exist in Oracle.
>
> It can be done, but not in SQL Plus. The principle would be: take a full
> database export, truncate all the tables in the database, then use the
> 'alter database datafile 'C:\blah\blah\file.dbf' resize 10m' command to
> manually shrink all your now-empty datafiles. Then you run import with
> ignore=y, and you get your data back. Hopefully, at any rate: if you've
> shrunk the files too small, and there's not enough room to insert all the
> data, you can expect fireworks. However, you could also make all your
data
> files autoextensible for the purposes of import only (alter database
> datafile 'etc etc etc' autoextend on), and then when all the data is back,
> change all the files to being 'autoextensible off' (because autoextension
is
> a poor performance factor).
>
> Deletes, incidentally, *never* free up space within a data file. Drop or
> truncate commands do that -but dropping a table merely frees up space
within
> a file of xxxMb, it doesn't make the datafile itself get any smaller.
>
> --
>
> Oracle Resources : http://www.geocities.com/howardjr2000
> ========================================
>
>
> "Chris" <Chris.Ilmberger_at_dataflux.com> wrote in message
> news:9e0f2b2e.0110310747.596421f6_at_posting.google.com...
> > I have an Oracle Database that has grown over 4 gig. I deleted many
> > tables and want to know how I can shrink the database using SQL Plus.
> > Does anyone know how to do this?
>
>
Received on Wed Oct 31 2001 - 14:51:49 CST