Home » RDBMS Server » Server Administration » Default Undo vs Default Temp tablesapces (windows 2012, oracle 12c)
Default Undo vs Default Temp tablesapces [message #636610] Wed, 29 April 2015 02:03 Go to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
HI All,

Having a small doubt,

We do setup the default temp tablespace as:
alter database default temporary tablespace temp;


But we do that for UNDO tablespace in SYSTEM level as:

ALTER SYSTEM SET UNDO_TABLESPACE <name>;



Both are releasing the space once shutdown the DB.

Then why we required one in MOUNT level (Alter database...)and One in OPEN level (Alter system...)?

Regards
Muktha
Re: Default Undo vs Default Temp tablesapces [message #636612 is a reply to message #636610] Wed, 29 April 2015 02:11 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Can you execute those commands in mount mode? I can't:
orclz> startup mount;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size                  3050600 bytes
Variable Size             373293976 bytes
Database Buffers          406847488 bytes
Redo Buffers                5337088 bytes
Database mounted.
orclz> ALTER SYSTEM SET UNDO_TABLESPACE=undotbs1;
ALTER SYSTEM SET UNDO_TABLESPACE=undotbs1
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only


orclz> alter database default temporary tablespace temp;
alter database default temporary tablespace temp
*
ERROR at line 1:
ORA-01109: database not open


orclz>
Re: Default Undo vs Default Temp tablesapces [message #636613 is a reply to message #636612] Wed, 29 April 2015 02:25 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi John,

Sorry for the post, without testing myself by believing a note in internet.

But could you tell me, why UNDO tablespace to be setup in SYSTEM level not in DB level, as both are purpose of temporary.

Regards
Muktha
Re: Default Undo vs Default Temp tablesapces [message #636614 is a reply to message #636613] Wed, 29 April 2015 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No they are not the same purpose.
undo_tablespace is an instance parameter NOT a database one.
(Default) temporary tablespace is a database property NOT an instance one.

I advise you to read Database Concepts.

Re: Default Undo vs Default Temp tablesapces [message #636623 is a reply to message #636613] Wed, 29 April 2015 06:38 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
muktha_22 wrote on Wed, 29 April 2015 02:25
Hi John,

Sorry for the post, without testing myself by believing a note in internet.




============================================================================
Guy: Where did you hear that?
Girl: On the internet
Guy: And you believed it?
Girl: Sure. They can't put anything on the internet that isn't true
Guy: Where did you hear that?
Girl: On the internet. Oh, here comes my date. I met him on the internet. He's a French Model.
Guy 2 (with sly wink) : Bonjour


http://www.youtube.com/watch?v=bufTna0WArc They can't put anything on the internet that isn't true
============================================================================
Re: Default Undo vs Default Temp tablesapces [message #636625 is a reply to message #636623] Wed, 29 April 2015 07:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Laughing

Re: Default Undo vs Default Temp tablesapces [message #636626 is a reply to message #636625] Wed, 29 April 2015 08:40 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Have you ever used the "French model" line, Michel? Very Happy
Re: Default Undo vs Default Temp tablesapces [message #636636 is a reply to message #636623] Wed, 29 April 2015 09:55 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
LoL!?! That model guy is so cool... Laughing
Re: Default Undo vs Default Temp tablesapces [message #636651 is a reply to message #636626] Wed, 29 April 2015 13:41 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

gazzag wrote on Wed, 29 April 2015 15:40
Have you ever used the "French model" line, Michel? Very Happy


No need, I just have to show my avatar... to make them run away.

Previous Topic: Database up gradation
Next Topic: Has Oracle certified RHEL 7.1 Version to install Oracle 11g R2 ?
Goto Forum:
  


Current Time: Thu Mar 28 15:48:34 CDT 2024