Recreating temp tablespace

[beechill4]

Hi,

metalink has advised to recreate a temp tablespace to get rid of some i/o errors.

what are the implications of doing this?

 

[SantaMufasa]

No particular implications if you recreate TEMP while no one is using the database. I recommend doing this during scheduled maintenance, after you do the following:

shutdown immediate
<do a full, cold backup>
startup restrict <-- keeps others out while doing maintenance
<do your TEMP ts drop and recreate>
alter database disable restricted session; <-- reopens to others

To avoid having to reassign users to the new temp tablespace, be sure to use the same ts name for the temp tablespace as the first temp tablespace.

Let us know how things go.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[beechill4]

dave,

took a slightly different approach:

1. created a new temp tablespace
2. changed this to be the default temp tablsepace
3. dropped the original tablespace

 

[SantaMufasa]

Sure...that's fine. The only downside is that you must reassign users' temp tablespace to the new name:

ALTER USER <username> TEMPORARY TABLESPACE <new temp tablespace name>;

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]