| Moving TEMP tablespace [message #59234] |
Wed, 05 November 2003 13:09  |
Mark K
Messages: 18
Registered: October 2003
|
Junior Member |
|
|
How do I move a temporary tablespace?
I thought I could use
ALTER TABLESPACE... RENAME DATAFILE... TO...
but when I look for the existing datafile info in DBA_DATA_FILES, the TEMP tablespace does not show at all.
I'm missing something. Can someone clarify?
Thanks.
-- Mark K.
|
|
|
|
| Re: Moving TEMP tablespace [message #59235 is a reply to message #59234] |
Wed, 05 November 2003 13:11  |
Mark K
Messages: 18
Registered: October 2003
|
Junior Member |
|
|
ANSWERED MY OWN QUESTION! (I think -- please validate my thinking)
I have to look at a DIFFERENT view for TEMP: DBA_TEMP_FILES instead of DBA_DATA_FILES.
I think that's all.
Thanks for your time.
-- Mark K.
|
|
|
|
| STILL A PROBLEM [message #59237 is a reply to message #59234] |
Wed, 05 November 2003 13:50 |
Mark K
Messages: 18
Registered: October 2003
|
Junior Member |
|
|
Well, I found the file, fine, but I cannot take a TEMPORARY tablespace OFFLINE... in order to RENAME DATAFILE... TO.
And if I shutdown the instance, move the file, what happens when the instance comes back up?
I guess forget all these sub-questions; my question remains: How does one move a TEMPORARY tablespace?
Thanks.
-- Mark K.
|
|
|
|
| Re: STILL A PROBLEM [message #59238 is a reply to message #59237] |
Wed, 05 November 2003 14:07 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Take the tempfile offline (alter database tempfile 'name' offline;),
move/copy the file at the OS level,
rename the tempfile(alter database rename 'old file' to 'new file') ,
bring it online ( alter database tempfile 'name' online;)
|
|
|
|
| OFFLINE doesn't work for TEMP [message #59239 is a reply to message #59238] |
Wed, 05 November 2003 14:50 |
Exadata
Messages: 2
Registered: October 2003
|
Junior Member |
|
|
Hi,
Thanks for the reply.
That's the problem I'm facing -- I'm moving all my other tablespaces in the manner you describe, but when I got to TEMP, the alter command give error that option not valid for temporary tablespaces.
I'm thinking maybe I have to simply create a new TEMP01 tablespace, alter database to make it default TEMP, drop old TEMP.
Thoughts?
-- Mark K.
|
|
|
|
| Re: OFFLINE doesn't work for TEMP [message #59240 is a reply to message #59239] |
Wed, 05 November 2003 15:03 |
ctg
Messages: 146
Registered: July 2002
|
Senior Member |
|
|
|
that is it. you cant take the temp tablespace offline because everyone is allowed to sort and needs a place to sort. so make a new one it the location you wanted to relocate the old one. and then drop the old one.
|
|
|
|
| Re: OFFLINE doesn't work for TEMP [message #59241 is a reply to message #59239] |
Wed, 05 November 2003 19:10 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
|
I must be thinking something else.. Right, you cannot rename tempfiles of a Locally MAnaged temporary tablespace in that fashion,although you can take the tempfile offline. As you mentioned, you can create a new one and drop the old one easily.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
