Home » RDBMS Server » Server Administration » converting permanent tablespace to temporary tablespace
converting permanent tablespace to temporary tablespace [message #336435] Sat, 26 July 2008 14:12 Go to next message
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 #336436 is a reply to message #336435] Sat, 26 July 2008 14:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
Re: converting permanent tablespace to temporary tablespace [message #336437 is a reply to message #336435] Sat, 26 July 2008 14:41 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>Some Sr. dba said converting permanent tablespace to temporary & use as default tablespace.
>It gives improves performance over temporary tablespace .
Sr. DBA is wrong in this situation.
Ask Sr. DBA to provide reproducable benchmark test to prove his claim.

Re: converting permanent tablespace to temporary tablespace [message #336438 is a reply to message #336436] Sat, 26 July 2008 14:45 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
BlackSwan
Messages: 25039
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.


Previous Topic: does log switch take place at 1/3 full redo-log buffer ?
Next Topic: Downgrade Database 10g
Goto Forum:
  


Current Time: Tue Dec 06 06:33:58 CST 2016

Total time taken to generate the page: 0.06604 seconds