Home » RDBMS Server » Server Administration » Switching UNDO tablespace
Switching UNDO tablespace [message #245217] Fri, 15 June 2007 08:54 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Why my rollback is not working ,after UNDO tablespace switch ?

SQL> select * from emp;

no rows selected

SQL> insert into emp values(101167,'MARK');

1 row created.

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- --------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string TESTUNDO
SQL> alter system set undo_tablespace=PSAPUNDO;

System altered.

SQL> select * from emp;

ID NAME
---------- ------
101167 MARK

SQL> rollback;

Rollback complete.

SQL> select * from emp;

ID NAME
---------- -----
101167 MARK
Re: Switching UNDO tablespace [message #245239 is a reply to message #245217] Fri, 15 June 2007 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ALTER SYSTEM internally commits.
If you want to test that, you have to execute the ALTER SYSTEM in another session.

Regards
Michel

[Updated on: Fri, 15 June 2007 11:14]

Report message to a moderator

Re: Switching UNDO tablespace [message #245244 is a reply to message #245217] Fri, 15 June 2007 11:24 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Thanks Michel,

But oracle documentation says when we switch undo tablespace(UndoTS1 to UNDOTS2),Old transactions using UNDOTS1 will still use the same until it completes.

if we are using ALTER SYSTEM to switch UNDO tablespace,it should commit all active transactions and free up old undo tablespace (UNDOTS1)
Re: Switching UNDO tablespace [message #245247 is a reply to message #245244] Fri, 15 June 2007 11:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ALTER SYSTEM commits the current transaction not all transactions.
All and any DDL commit, this is not an exception and does not have any relation with undo tablespace switching.

When you execute ALTER SYSTEM, you commit and end your current transaction.

Reexecute your example with "alter system" in ANOTHER session and you will see your rollback works.

Regards
Michel

[Updated on: Fri, 15 June 2007 11:31]

Report message to a moderator

Re: Switching UNDO tablespace [message #245249 is a reply to message #245217] Fri, 15 June 2007 11:33 Go to previous message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
ok ,an internal COMMIT only to the session which fire 'ALTER SYSTEM'...

great !Thanks for the explanation.
Previous Topic: Is my understanding regarding transactions correct ?
Next Topic: Util
Goto Forum:
  


Current Time: Mon Dec 05 21:28:20 CST 2016

Total time taken to generate the page: 0.13515 seconds