moving tmp tablespace

[maryam421]

Hi,
I am using oracle 10g on solaris unix. I am planning to move the temp table space datafile to a different location. What is the best way to do it. The database is online and user are working on it.
I read few articles and I understand what it says like create the new temp tablespace drop the old table space and remane the new tablespace. But since the user are working they will get errors.
Is there any other way I can move the data file without effecting the users?
Thanks

 

[SantaMufasa]

Maryam,

I believe that your best solution for relocating the TEMP tablespace without interfering with users' on-going work is the following:

1) Create a new TEMP2 tablespace in the target new location.

2) For each user in the database issue this command:

ALTER USER <username> temporary tablespace TEMP2;

3) Since activity against a TEMP tablespace is transient and temporary, you can, after a period of time (10-20 minutes) be assured that the old TEMP tablespace no longer has any activity against it. At that point, you can issue a command to drop the original TEMP tablespace. (The actual command to drop the tablespace may differ slightly depending upon the type of TEMP tablespace you are trying to drop.)

How does this strategy sound to you?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[maryam421]

Mufasa,
Startegy is good but there are many users to this application. I should say in 100. this is oracle app and in production.
So not sure how long it will take for no activity in tmp table space. Worth giving a shot.
Any more ideas!!
Thanks

 

[SantaMufasa]

Sure...1) Shut down the database, 2) Startup RESTRICT, 3) Drop temp tablespace, 4) Re-create temp tablespace, 5) ALTER SYSTEM DISABLE RESTRICTED SESSION. But this method makes your application inaccessible during the few minutes that the database is restricted.

The first method allows you to keep the application up and running. With the first method, once you re-assign the TEMPORARY TABLESPACE for all users, they will no longer initiate any activity in the old TEMP tablespace. I cannot imagine that residual activity in the old TEMP tablespace will remain for more than just a few minutes. I don't know why you are worried about waiting for a few minutes...after all, the current temp tablespace has been out there for days/weeks/months/years, right? What's the rush? <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]