Home » RDBMS Server » Server Administration » undo tablespace usage
undo tablespace usage [message #332838] Wed, 09 July 2008 23:02 Go to next message
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 #332840 is a reply to message #332838] Wed, 09 July 2008 23:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
here is a free clue
21:07:10 SQL> select view_name from dba_views where view_name like '%UNDO%';

VIEW_NAME
------------------------------
V_$UNDOSTAT
GV_$UNDOSTAT
DBA_UNDO_EXTENTS
DBA_HIST_UNDOSTAT

21:07:38 SQL> 

Re: undo tablespace usage [message #332847 is a reply to message #332838] Wed, 09 July 2008 23:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
v$transaction gives you the number of undo blocks used by each transaction.

Regards
Michel
Re: undo tablespace usage [message #332848 is a reply to message #332840] Wed, 09 July 2008 23:29 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
But how do i know how much free undo space is available?
Re: undo tablespace usage [message #332849 is a reply to message #332848] Wed, 09 July 2008 23:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
By querying the last but one view pointed by Ana.

Regards
Michel
Re: undo tablespace usage [message #332860 is a reply to message #332849] Wed, 09 July 2008 23:44 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
Is the Bytes in DBA_UNDO_EXTENTS is free bytes available?
Re: undo tablespace usage [message #332863 is a reply to message #332860] Wed, 09 July 2008 23:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What does the documentation say?

Regards
Michel
Re: undo tablespace usage [message #332877 is a reply to message #332848] Thu, 10 July 2008 00:32 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

use this view dba_tablespace to find out the free space available in undo tablespace.

select tablespace_name,round(sum(bytes)/(1024*1024) from dba_tablespace where tablespace_name like 'undo%' group by tablespace_name.
Re: undo tablespace usage [message #332891 is a reply to message #332877] Thu, 10 July 2008 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
balaji1482 wrote on Thu, 10 July 2008 07:32
use this view dba_tablespace to find out the free space available in undo tablespace.

select tablespace_name,round(sum(bytes)/(1024*1024) from dba_tablespace where tablespace_name like 'undo%' group by tablespace_name.

1/ Format your query (use code tags)
2/ Format your query (split line, indent)
3/ Test your query, post at least something syntaxically correct
4/ Are you sure it is mandatory that undo tablespace name starts with "undo" in lower case?
5/ Are you sure dba_tablespace exists?
6/ How dba_tablespaceS can give free space?
7/ Why sum and group by? Isn't there one line per tablespace?

Glad to see you want to help but put in it a little effort and concentration.

Regards
Michel

Re: undo tablespace usage [message #332914 is a reply to message #332838] Thu, 10 July 2008 01:45 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

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.
Re: undo tablespace usage [message #332929 is a reply to message #332914] Thu, 10 July 2008 02:31 Go to previous messageGo to next message
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 #332983 is a reply to message #332929] Thu, 10 July 2008 04:26 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

I am sure the below query will give you the free space of undo tablespace.


select tablespace_name,sum(bytes/1024/1024) "SIZE MB" from dba_free_space where
tablespace_name='UNDOTBS1' group by tablespace_name
/
Re: undo tablespace usage [message #333000 is a reply to message #332983] Thu, 10 July 2008 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Remarks 1, 2 and additional remark in my previous post are still valid and I add that an undo tablespace may be not be named UNDOTBS1 (quite like remark 4 before).

Regards
Michel
Re: undo tablespace usage [message #335060 is a reply to message #333000] Sun, 20 July 2008 10:14 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

why undo tablespace should not named as UNDOTBS1? any specific reason??
Re: undo tablespace usage [message #335061 is a reply to message #335060] Sun, 20 July 2008 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You have a flaw in your logic.
The question is "why undo tablespace should named as UNDOTBS1?"

Regards
Michel

[Updated on: Sun, 20 July 2008 10:22]

Report message to a moderator

Re: undo tablespace usage [message #335148 is a reply to message #332838] Mon, 21 July 2008 03:59 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
>>dba_free_space is meaningless for an undo tablespace.

Can you elaborate more Plz?

On what regards is it meaningless?

Re: undo tablespace usage [message #335159 is a reply to message #335148] Mon, 21 July 2008 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It is meaningless because undo extents are not managed based on free space but on usage.

Have a look at v$undostat view definition.

Regards
Michel
Re: undo tablespace usage [message #335173 is a reply to message #332838] Mon, 21 July 2008 05:46 Go to previous messageGo to next message
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 #335178 is a reply to message #335173] Mon, 21 July 2008 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I don't understand your sentence "sounds like it is completely meaningless in any case for an undo tablespace."
What "it" is referencing?

Regards
Michel
Re: undo tablespace usage [message #335192 is a reply to message #332838] Mon, 21 July 2008 06:56 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
It is referencing to the statement you made
Quote:
dba_free_space is meaningless for an undo tablespace.
Re: undo tablespace usage [message #335197 is a reply to message #335192] Mon, 21 July 2008 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So what is meaningless in your post my sentence or dba_free_space or what?
I really don't understand your post and the relation between the 2 sentences :
"For querying how much free space an undo tablespace has,we query dba_free_space."
"sounds like it is completely meaningless in any case for an undo tablespace."

Regards
Michel
Re: undo tablespace usage [message #335200 is a reply to message #332838] Mon, 21 July 2008 07:25 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Ok then leave it.
Re: undo tablespace usage [message #335642 is a reply to message #332929] Wed, 23 July 2008 01:55 Go to previous messageGo to next message
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 #335648 is a reply to message #335642] Wed, 23 July 2008 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Sergey,
For you, what does "free space" mean? Or "not free space"?

Regards
Michel
Re: undo tablespace usage [message #335660 is a reply to message #332838] Wed, 23 July 2008 03:36 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: 10g ORA-00201 - Controlfile version
Next Topic: ORA-00600: internal error code, arguments: [17059]
Goto Forum:
  


Current Time: Fri Nov 29 19:36:23 CST 2024