Brian Spears 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
1) Enable auditing on Selects. If it isn't being queried, do you really
need it?
2) Export the named objects (table=), drop named objects, recreate named
objects in preferred location, Import data (ignore=y).
3) I didn't notice the Oracle7 version number anywhere in your e-mail,
so drop the emptied tablespace/datafile(s), recreate if needed.
4) Migrate to 7.3, where you can resize datafiles.
--
Rich Woods
Technical Field Support Specialist, Oracle Corporation, USA
The above statements and opinions are my own and do not
necessarily represent those of Oracle Corporation.
Received on Wed Mar 05 1997 - 00:00:00 CST
