OraFAQ Forum: Server Administration » undo tablespace usage

Home » RDBMS Server » Server Administration » undo tablespace usage

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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?

Report message to a moderator

Re: undo tablespace usage [message #332840 is a reply to message #332838]

Wed, 09 July 2008 23:08

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>

Report message to a moderator

Re: undo tablespace usage [message #332847 is a reply to message #332838]

Wed, 09 July 2008 23:28

Michel Cadot
Messages: 68672
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

v$transaction gives you the number of undo blocks used by each transaction.

Regards
Michel

Report message to a moderator

Re: undo tablespace usage [message #332848 is a reply to message #332840]

Wed, 09 July 2008 23:29

caprikar
Messages: 226
Registered: March 2007

Senior Member

But how do i know how much free undo space is available?

Report message to a moderator

Re: undo tablespace usage [message #332849 is a reply to message #332848]

Wed, 09 July 2008 23:31

Michel Cadot
Messages: 68672
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

By querying the last but one view pointed by Ana.

Regards
Michel

Report message to a moderator

Re: undo tablespace usage [message #332860 is a reply to message #332849]

Wed, 09 July 2008 23:44

caprikar
Messages: 226
Registered: March 2007

Senior Member

Is the Bytes in DBA_UNDO_EXTENTS is free bytes available?

Report message to a moderator

Re: undo tablespace usage [message #332863 is a reply to message #332860]

Wed, 09 July 2008 23:53

Michel Cadot
Messages: 68672
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What does the documentation say?

Regards
Michel

Report message to a moderator

Re: undo tablespace usage [message #332877 is a reply to message #332848]

Thu, 10 July 2008 00:32

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.

Report message to a moderator

Re: undo tablespace usage [message #332891 is a reply to message #332877]

Thu, 10 July 2008 01:00

Michel Cadot
Messages: 68672
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

balaji1482 wrote on Thu, 10 July 2008 07:32

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

Report message to a moderator

Re: undo tablespace usage [message #332914 is a reply to message #332838]

Thu, 10 July 2008 01:45

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.

Report message to a moderator

Re: undo tablespace usage [message #332929 is a reply to message #332914]

Thu, 10 July 2008 02:31

Michel Cadot
Messages: 68672
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

balaji1482 wrote on Thu, 10 July 2008 08:45

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

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
/

Report message to a moderator

Re: undo tablespace usage [message #333000 is a reply to message #332983]

Thu, 10 July 2008 04:52

Michel Cadot
Messages: 68672
Registered: March 2007
Location: Nanterre, France, http://...

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

Report message to a moderator

Re: undo tablespace usage [message #335060 is a reply to message #333000]

Sun, 20 July 2008 10:14

balaji1482
Messages: 312
Registered: July 2007
Location: INDIA

Senior Member

why undo tablespace should not named as UNDOTBS1? any specific reason??

Report message to a moderator

Re: undo tablespace usage [message #335061 is a reply to message #335060]

Sun, 20 July 2008 10:21

Michel Cadot
Messages: 68672
Registered: March 2007
Location: Nanterre, France, http://...

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

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?

Report message to a moderator

Re: undo tablespace usage [message #335159 is a reply to message #335148]

Mon, 21 July 2008 04:53

Michel Cadot
Messages: 68672
Registered: March 2007
Location: Nanterre, France, http://...

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

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.

Report message to a moderator

Re: undo tablespace usage [message #335178 is a reply to message #335173]

Mon, 21 July 2008 05:58

Michel Cadot
Messages: 68672
Registered: March 2007
Location: Nanterre, France, http://...

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

Report message to a moderator

Re: undo tablespace usage [message #335192 is a reply to message #332838]

Mon, 21 July 2008 06:56

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.

Report message to a moderator

Re: undo tablespace usage [message #335197 is a reply to message #335192]

Mon, 21 July 2008 07:08

Michel Cadot
Messages: 68672
Registered: March 2007
Location: Nanterre, France, http://...

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

Report message to a moderator

Re: undo tablespace usage [message #335200 is a reply to message #332838]

Mon, 21 July 2008 07:25

varu123
Messages: 754
Registered: October 2007

Senior Member

Ok then leave it.

Report message to a moderator

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

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

Report message to a moderator

Re: undo tablespace usage [message #335648 is a reply to message #335642]

Wed, 23 July 2008 02:27

Michel Cadot
Messages: 68672
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Sergey,
For you, what does "free space" mean? Or "not free space"?

Regards
Michel

Report message to a moderator

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)

Report message to a moderator

Re: undo tablespace usage [message #335667 is a reply to message #335660]

Wed, 23 July 2008 04:06

Michel Cadot
Messages: 68672
Registered: March 2007
Location: Nanterre, France, http://...

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

Report message to a moderator

Previous Topic:	10g ORA-00201 - Controlfile version
Next Topic:	ORA-00600: internal error code, arguments: [17059]

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Aug 07 04:06:53 CDT 2024