Re: changing temporary tablespace for all schemas

Hello Cristian,

it depends. If your temp ts was created by "create temporary tablespace bigtemp" (only possible in 8.1.x, recommended for temporary ts) you cannot offline it (you would receive ORA-03217).

Before you can delete the datafiles you *must* drop the bigtemp tablespace. In Unix you can check by the fuser command if the datafile is still in use. You will discover that only the drop tablespace is really relieving the ts.

Martin

Cristian Veronesi wrote:
>
> Hello.
> All my schemas use an over-dimensioned temporary tablespace called
> BIGTEMP and I have to free some space on the HD, so I wish to make them
> to use a smaller temporary tablespace called SMALLTEMP instead.
> Please let me know if these steps are correct (Oracle 7.3):
>
> 1. for each schema, issue the command: ALTER USER ... TEMPORARY
> TABLESPACE SMALLTEMP ;
> 2. ALTER TABLESPACE BIGTEMP OFFLINE ;
> 3. delete BIGTEMP datafile (gulp!)
>
> TIA, Cris
>
> --
> Cristian Veronesi ><((((º> http://www.crpa.it
>
> "Ogni limite ha una pazienza" (A. De Curtis)
Received on Fri Mar 23 2001 - 15:45:23 CST