Re: Recover Space-- Fun

Hi,
If you ar running Oracle 7.2 or 7.3 you might try this.

On each table in your system do: ALTER TABLE <table_name> FREE UNUSED; (Make a SQL*Plus script doing this)
Then you could query the dba_free_space and dba_data_files views and subtract free space from the total space in each datafile. This will give you the new filesize.

If you are running 7.3 you could to then could do alter tablespace <tablespacename> coalesce; (If not on 7.3 just continue to the next step)

On each file in your database you then do: ALTER DATABASE DATAFILE '<file_name>' RESIZE newsize;

This will shrink your database to a minium size. You might want to add som extra MB in each tablespace so tables beeing used could grow.

This is a fast way to shrink a database to a minimum without reloading or analyzing table usage.

It may not be the final solution but it might free some diskspace and buy you i some time.

Rgds
Steinar

Brian Spears (brian_spears_at_dsi.bc.ca) wrote:
: HI,
 

: This is the cenario,
:
: We are disk space starved and desperately need space and so on..
:
: Last DBA created two tablespaces that have lots of objects in
: them but most are not active now.
 

: I took the tablespaces offline to see if they are still needed
: and they are :-)
 

: One tablespace has 10,000 tables in it only a small portion
: are live. The tablespace sizes are 1 and 1.5 gigs and this
: space would do til the bugdet blessings poured out on us-- I think.
 

: I would like to drop the tablespace and the non-valid objects
: and store any used ones in a new smaller datafile.
 

: -------------
 

: QUESTIONS:
 
: RISK
: If I make a mistake and drop valid tables or indexes-- hundreds
: of users and lots of do-do if you know what I mean.
:
 

: 1.) How to find the valid tables?
: ---audit the Tablespace and tables??? Hope you have an ALL
: somewhere but any suggestion on a good audit plan would be
: greatly appreciated. This will not get all of them as some tables are
: static parameter tables. So I will use the tedious method of
: peek and poke on suspected tables.
 

: 2) How to move the active object from one datafile to a new or
: another existing one.
:
: 3) Is the only way to shrink a datafile is to export full and
: recreate the entire database? If so what parts of the data
: base need to be created for the export to fill in the details?
:
: 4) Any other ways to get this space back?
 

: Brian Spears
: Paranoya & Pain Pleasantly Polished
Received on Thu Mar 06 1997 - 00:00:00 CST