This content has been marked as final.
Show 3 replies
-
1. Re: getting the undo tablespace use by session - level
rp0428 Oct 9, 2012 2:29 AM (in response to redologger)Please rephrase your question. It is not clear what you are asking.1 person found this helpful
If you are trying to calc the number of undo BYTES used then, yes, you need to multiply 'used_ublk' by the block size.
You are using 16384 but the parameter list is showing 8192.
Your thread title says you want the data by session so you would need to sum the blocks or bytes and group by sid and serial# to get the total for each session. -
2. Re: getting the undo tablespace use by session - level
redologger Oct 9, 2012 3:19 AM (in response to rp0428)rp0428 wrote:
thanks for the confirmation regarding using the block size.
Please rephrase your question. It is not clear what you are asking.
If you are trying to calc the number of undo BYTES used then, yes, you need to multiply 'used_ublk' by the block size.
You are using 16384 but the parameter list is showing 8192.
Your thread title says you want the data by session so you would need to sum the blocks or bytes and group by sid and serial# to get the total for each session.
Not too sure what u are saying on the last part
" Your thread title says you want the data by session so you would need to sum the blocks or bytes and group by sid and serial# to get the total for each session."
i was trying to get the undo tablespace used by a session / SQL consuming the allocated undo tablespace space in situation where it is not committing at the appropriate time and bad coding. Just testing it out.
so my logic is base on the USED_UBLK column of the v$transaction, we can get how much undo space it is consuming from not committing. -
3. Re: getting the undo tablespace use by session - level
Hemant K Chitale Oct 9, 2012 3:28 AM (in response to redologger)The Block Size of the UNDO Tablespace would be the default block size which is 8192 as indicated by the database instance parameter.1 person found this helpful
Hemant K Chitale