undo tablespace usage [message #332838] |
Wed, 09 July 2008 23:02 |
caprikar
Messages: 226 Registered: March 2007
|
Senior Member |
|
|
Hi,
We are running a huge delete followed by update in our database and want to monitor UNDO tablespace usage. Can anyone tell me the script i can use to monitor this?
|
|
|
|
|
|
|
|
|
|
|
|
Re: undo tablespace usage [message #332929 is a reply to message #332914] |
Thu, 10 July 2008 02:31 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
balaji1482 wrote on Thu, 10 July 2008 08:45 | Please ignore the above view.
use this dba_free_space to find out the free space available in tablespace.
select tablespace_name, round(sum(bytes)/(1024*1024)) from dba_free_space where tablespace_name='undo_tablespace';
i assume this query will give you the free space of tablespace.
|
Remarks 1, 2, 3 and 4 are still valid.
In addition, dba_free_space is meaningless for an undo tablespace.
Regards
Michel
[Updated on: Thu, 10 July 2008 02:39] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: undo tablespace usage [message #335173 is a reply to message #332838] |
Mon, 21 July 2008 05:46 |
varu123
Messages: 754 Registered: October 2007
|
Senior Member |
|
|
So why are you generalizing it?
For querying how much free space an undo tablespace has,we query dba_free_space.
For queying how much space is in use ,query v$undostat.
>>dba_free_space is meaningless for an undo tablespace.
sounds like it is completely meaningless in any case for an undo tablespace.
|
|
|
|
|
|
|
Re: undo tablespace usage [message #335642 is a reply to message #332929] |
Wed, 23 July 2008 01:55 |
Serg.K.
Messages: 7 Registered: March 2008
|
Junior Member |
|
|
Michel Cadot wrote on Thu, 10 July 2008 02:31 | balaji1482 wrote on Thu, 10 July 2008 08:45 | Please ignore the above view.
use this dba_free_space to find out the free space available in tablespace.
select tablespace_name, round(sum(bytes)/(1024*1024)) from dba_free_space where tablespace_name='undo_tablespace';
i assume this query will give you the free space of tablespace.
|
Remarks 1, 2, 3 and 4 are still valid.
In addition, dba_free_space is meaningless for an undo tablespace.
Regards
Michel
|
Hello Michel.
dba_free_space is not meaningless for an undo tablespace.
SQL> select sum(bytes/1024/1024) from dba_undo_extents;
SUM(BYTES/1024/1024)
--------------------
354.25
SQL> select sum(bytes/1024/1024) from dba_free_space where tablespace_name='UNDO';
SUM(BYTES/1024/1024)
--------------------
145.6875
SQL> select sum(bytes/1024/1024), autoextensible from dba_data_files where tablespace_name='UNDO' group by autoextensible;
SUM(BYTES/1024/1024) AUT
-------------------- ---
500 NO
SQL> select 145.6875 + 354.25 from dual;
145.6875+354.25
---------------
499.9375
|
|
|
|
Re: undo tablespace usage [message #335660 is a reply to message #332838] |
Wed, 23 July 2008 03:36 |
Serg.K.
Messages: 7 Registered: March 2008
|
Junior Member |
|
|
For me, "not free space" in the undo tablespace is the space used by
1. uncommited transactions
2. commited, but unexpired transactions (If retention guarantee is enabled)
3. commited, bun unexpired transactions (undo_retention)
|
|
|
Re: undo tablespace usage [message #335667 is a reply to message #335660] |
Wed, 23 July 2008 04:06 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
So expired extents are free for you and dba_free_space does not show this, this is why I said "dba_free_space" is meaningless for undo tablespace.
Regards
Michel
|
|
|