OraFAQ Forum: Server Administration » Default Undo vs Default Temp tablesapces

Home » RDBMS Server » Server Administration » Default Undo vs Default Temp tablesapces (windows 2012, oracle 12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Default Undo vs Default Temp tablesapces [message #636610]

Wed, 29 April 2015 02:03

muktha_22
Messages: 527
Registered: December 2009

Senior Member

HI All,

Having a small doubt,

We do setup the default temp tablespace as:

Code: [Select all] [Show/ hide]

alter database default temporary tablespace temp;

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

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: Default Undo vs Default Temp tablesapces [message #636612 is a reply to message #636610]

Wed, 29 April 2015 02:11

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Can you execute those commands in mount mode? I can't:

Code: [Select all] [Show/ hide]

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>

Report message to a moderator

Re: Default Undo vs Default Temp tablesapces [message #636613 is a reply to message #636612]

Wed, 29 April 2015 02:25

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

Report message to a moderator

Re: Default Undo vs Default Temp tablesapces [message #636614 is a reply to message #636613]

Wed, 29 April 2015 02:53

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Default Undo vs Default Temp tablesapces [message #636623 is a reply to message #636613]

Wed, 29 April 2015 06:38

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
============================================================================

Report message to a moderator