| Undo Tbs is not clearing/Flushing [message #616607] |
Thu, 19 June 2014 00:27  |
janakors
Messages: 232
Registered: September 2009
|
Senior Member |
|
|
hi,
i am having 11.2.0.2 with 2 x nodes RAC on solaris 10. Undo tablespace2 which is assosiated with 2nd node (not primary node) is not getting cleared or fllushed. undo retention is 900 sec but in past 2 weeks from now i have increase (i had to) by 20gb, so i don't have any idea why it it is not getting cleared and very soon my disk group be out of space. current size of this undo tablespace is 28 gb
kindly guide
Regards
[Updated on: Thu, 19 June 2014 00:34] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Undo Tbs is not clearing/Flushing [message #616658 is a reply to message #616656] |
Thu, 19 June 2014 05:45  |
janakors
Messages: 232
Registered: September 2009
|
Senior Member |
|
|
SQL>select t1.sid,t1.username,t2.xidusn,t2.used_urec,t2.used_ublk from v$session t1,v$transaction t2 where t1.saddr = t2.ses_addr;
SID USERNAME XIDUSN USED_UREC USED_UBLK
------- ----------------------------- ------- ---------- ----------
1351 USER01 20 1 1
SQL>
|
|
|
|
| Re: Undo Tbs is not clearing/Flushing [message #616663 is a reply to message #616658] |
Thu, 19 June 2014 05:55 |
janakors
Messages: 232
Registered: September 2009
|
Senior Member |
|
|
i have also tried following but nothing found
SQL> SELECT COUNT(*)
2 FROM v$transaction t, v$session s, v$mystat m
3 WHERE t.ses_addr = s.saddr
4 AND s.sid = m.sid
5 AND ROWNUM = 1;
COUNT(*)
----------
0
|
|
|
|
| Re: Undo Tbs is not clearing/Flushing [message #616671 is a reply to message #616607] |
Thu, 19 June 2014 07:26 |
John Watson
Messages: 8965
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
janakors wrote on Thu, 19 June 2014 06:27hi,
i am having 11.2.0.2 with 2 x nodes RAC on solaris 10. Undo tablespace2 which is assosiated with 2nd node (not primary node) is not getting cleared or fllushed. undo retention is 900 sec but in past 2 weeks from now i have increase (i had to) by 20gb, so i don't have any idea why it it is not getting cleared and very soon my disk group be out of space. current size of this undo tablespace is 28 gb
kindly guide
Regards
If I understand you correctly, you are increasing the size of the datafile(s) manually. Why? Are you getting any errors? For an undo tablespace to have no free space is normal, and not necessarily any cause for concern.
|
|
|
|
|
|
|
|
| Re: Undo Tbs is not clearing/Flushing [message #616732 is a reply to message #616721] |
Fri, 20 June 2014 02:53 |
John Watson
Messages: 8965
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
janakors wrote on Fri, 20 June 2014 06:21well, you are right but if undo tablespace have no space left, what u think what will happen, it wont allow any DML as it cant undo if necessary .no free extent. please comment
Regards Trust me. I'm a DBA.
|
|
|
|
| Re: Undo Tbs is not clearing/Flushing [message #616733 is a reply to message #616726] |
Fri, 20 June 2014 02:56 |
janakors
Messages: 232
Registered: September 2009
|
Senior Member |
|
|
well after browsing the net i found something here https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:676925300346249247
and it says that if no space left so it will reuse even those extents as well whose retention time is still left.
Now look at following at my DB
SQL> select status, count(1)from dba_undo_extents group by status;
STATUS COUNT(1)
--------- ----------
ACTIVE 1
EXPIRED 914
UNEXPIRED 383
here 383 are expired so why not this space is reclaimed? does because i am not hitting 100% of undo tablespace , if yes so then i dont need
to worry about the space usage of undo tablespace?
please comments
Reagrds
|
|
|
|
| Re: Undo Tbs is not clearing/Flushing [message #616734 is a reply to message #616733] |
Fri, 20 June 2014 03:03 |
janakors
Messages: 232
Registered: September 2009
|
Senior Member |
|
|
Quote:
SQL> select status, count(1)from dba_undo_extents group by status;
STATUS COUNT(1)
--------- ----------
ACTIVE 1
EXPIRED 914
UNEXPIRED 383
What accounts for the contents being so full if so much is not active or expired?
|
|
|
|
|
|
|
|
| Re: Undo Tbs is not clearing/Flushing [message #616737 is a reply to message #616736] |
Fri, 20 June 2014 03:13 |
John Watson
Messages: 8965
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
How far can this analogy be pushed.....
It is more a case of putting the cup in the microwave, and warming up the cold tea to make it drinkable.
Seriously, for the undo tablespace to be full is a natural state. Uncle Oracle will re-use the space within the undo segments as necessary.
|
|
|
|
| Re: Undo Tbs is not clearing/Flushing [message #616739 is a reply to message #616737] |
Fri, 20 June 2014 03:22 |
janakors
Messages: 232
Registered: September 2009
|
Senior Member |
|
|
ok thanks ....what i understand is if undo tablespce hit 100 % and there are a lot of expired extents so they will be re-used and even (if retention is not guaranteed) unexpired extents will be re-used. correct or not, final reply please
thank You very much for your time and everyone else
Regards
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
