| converting permanent tablespace to temporary tablespace [message #336435] |
Sat, 26 July 2008 14:12  |
IT Guru
Messages: 59
Registered: January 2007
|
Member |
|
|
Some Sr. dba said converting permanent tablespace to temporary & use as default tablespace.
It gives improves performance over temporary tablespace .
How one can convert this & what are advantages of this ?
I create normal OMF Permanent tablespace test.
Create tablespace test ;
Convert same to temporary with following command.
Alter tablespace test offline temporary ;
Alter tablespace test offline temporary ;
But still its show as a permanent tablespace in dba_tablespaecs
Select tablespace_name , contents from dba_tablespaces where tablespace_name = ‘TEST’ ;
Cant even make it to default temporary tablespace,
Alter database default temporary tablespace temp ;
Gives error
alter database default temporary tablespace temp
*
ERROR at line 1:
ORA-12902: default temporary tablespace must be SYSTEM or of TEMPORARY type
|
|
|
|
|
|
|
|
| Re: converting permanent tablespace to temporary tablespace [message #336438 is a reply to message #336436] |
Sat, 26 July 2008 14:45  |
IT Guru
Messages: 59
Registered: January 2007
|
Member |
|
|
correction : ask to use as default temporary tablespace.
+ then what is effect of following command on normal permanent table space.( create tablespace test)
Alter tablespace test offline temporary
| Michel Cadot wrote on Sat, 26 July 2008 14:33 | 1/ Use a REAL temporary tablespace as user temporary tablespace using "create temporary tablespace"
2/ A temporary tablespace can't be used as default tablespace.
Regards
Michel
|
[Updated on: Sat, 26 July 2008 14:45] Report message to a moderator |
|
|
|
| Re: converting permanent tablespace to temporary tablespace [message #336439 is a reply to message #336438] |
Sat, 26 July 2008 14:49 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Nothing to do with temporary tablespace:
| from doc | OFFLINE TEMPORARY
If you specify TEMPORARY, then Oracle Database performs a checkpoint for all online datafiles in the tablespace but does not ensure that all files can be written. Files that are offline when you issue this statement may require media recovery before you bring the tablespace back online.
|
Regards
Michel
[Updated on: Sat, 26 July 2008 14:51] Report message to a moderator |
|
|
|
| Re: converting permanent tablespace to temporary tablespace [message #336440 is a reply to message #336438] |
Sat, 26 July 2008 14:50 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
| IT Guru wrote on Sat, 26 July 2008 12:45 | correction : ask to use as default temporary tablespace.
+ then what is effect of following command on normal permanent table space.( create tablespace test)
Alter tablespace test offline temporary
| Michel Cadot wrote on Sat, 26 July 2008 14:33 | 1/ Use a REAL temporary tablespace as user temporary tablespace using "create temporary tablespace"
2/ A temporary tablespace can't be used as default tablespace.
Regards
Michel
|
|
Are you unwilling or incapable to RTFM?
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_3002.htm#sthref5456
OFFLINE TEMPORARY
If you specify TEMPORARY, then Oracle Database performs a checkpoint for all online datafiles in the tablespace but does not ensure that all files can be written. Files that are offline when you issue this statement may require media recovery before you bring the tablespace back online.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
