Re: A script to reorganize a tablespace !!!

noel_at_omega.univ-lille1.fr (Yves Noel) writes:
>- taking the tablespace offline :
> - check that the database is open
> - connect internal
> - verify that there are no active rollback segments in the tablespace
> - alter tablespace xxx offline
>- export tables of the tablespace

Oops! You can not do this, because once the tablespace is OFFLINE, it can not be accessed by Oracle for exportation.

>- delete tables of the tablespace
>- import
>- taking the tablespace online.

I'm not sure that there is a script to do this, as exporting each table in a tablespace is not something directly supported... unless you are lucky enough that a (small) set of users have tables only in the tablespace in question.

One perhaps easier solution is to do a full database export to disk (compression is optional but easy), remake the system tablespace by doing an Oracle re-install, and then importing the entire export from disk. I have done this enough times that I am confident that it works and I don't take "safety" backups of the physical data files.

>Thanks in advance and excuse my poor English !
>--
>------------------------------------------------------------------------------
> _/_/_/_/ _/_/_/_/ _/_/_/_/ _/_/_/_/ I use
> _/_/ _/_/ _/_/ _/_/ ORACLE v6.0.36.5.2
> _/_/ _/_/ _/_/ _/_/ on
>_/_/_/_/ . _/_/_/_/ . _/_/ . _/_/_/_/ . DEC RISC ULTRIX v4.3
>------------------------------------------------------------------------------
>Yves NOEL - Database Administrator Mail : Yves.Noel_at_univ-lille1.fr
>C.I.T.I. (batiment M4)
>Universite des Sciences & Technologies de Lille Phone : (33) 20.43.42.70
>59655 Villeneuve d'Ascq Cedex - FRANCE Fax : (33) 20.43.66.25
>------------------------------------------------------------------------------

-- 
Andrew Finkenstadt  |  Systems Analyst, Homes & Land Publishing Corporation
+1 904-575-0189     |  GEnie Sysop             ,,,
andy_at_genie.geis.com |                         (. .)     Peek-a-boo!
andy_at_homes.com      +----------------------o00-(_)-00o---------------------