| Switching UNDO tablespace [message #245217] |
Fri, 15 June 2007 08:54  |
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 #245244 is a reply to message #245217] |
Fri, 15 June 2007 11:24  |
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 |
 |
Michel Cadot
Messages: 68686
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
