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

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?