Can somebody point to official or unofficial documentation on how to
dump and read info of a rollback segment block? (based on info in
v$transaction)
I have some transactions that stay open for too long (usually
transactions last <1 second, it's a web app). They have TX
locks/transaction slots open. I have a suspicion it's due to sql over
the link.
Any other help would be appreciated as well.

Thanks.
........
We use Oracle 8.1.7.4 on Solaris 2.7 boxes
remove NSPAM to email

On Tue, 01 Jul 2003 17:57:07 GMT, andreyNSPAM@bookexchange.net
(NetComrade) wrote:

>Can somebody point to official or unofficial documentation on how to
>dump and read info of a rollback segment block? (based on info in
>v$transaction)
>I have some transactions that stay open for too long (usually
>transactions last <1 second, it's a web app). They have TX
>locks/transaction slots open. I have a suspicion it's due to sql over
>the link.
>Any other help would be appreciated as well.
>
>Thanks.
>.......
>We use Oracle 8.1.7.4 on Solaris 2.7 boxes
>remove NSPAM to email


If you suspect it is sql, you can easily identify the sql by linking
the addr column of v$transaction back to the taddr column of
v$session. The sql_address and the sql_hash_value of v$session point
to the offending statement.
I have to look up the info to dump blocks, but I'm not sure it is
really useful. The lock info is maintained in data blocks.


Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

What if there were other sql statements executed after the 'offending'
statement?

Anyway, unfortunately sql_address and hash_value show zeros, i've
already tried that, but thanks.

The only lock for the session is a TX lock, and nothing else. the id's
for TX locks containt rollback info, if I recall correctly, which is
already given in v$transaction table.

If there is any other way in identifying the TX lock source I could
use, it would be greatly appreciated.

Thanks,



On Tue, 01 Jul 2003 22:46:34 +0200, Sybrand Bakker
<gooiditweg@sybrandb.demon.nl> wrote:


>If you suspect it is sql, you can easily identify the sql by linking
>the addr column of v$transaction back to the taddr column of
>v$session. The sql_address and the sql_hash_value of v$session point
>to the offending statement.
>I have to look up the info to dump blocks, but I'm not sure it is
>really useful. The lock info is maintained in data blocks.

........
We use Oracle 8.1.7.4 on Solaris 2.7 boxes
remove NSPAM to email

Hi

Norman posted a link to the Oracle faq in 2002 as follows http://www.jlc
omp.demon.co.uk/faq/blockdump.html that shows how to dump a single block
or multiple blocks.

I also knew of an undocumented way to do it:

obtain a DBA (data block address) - you can use file number and block
number and dbms_utility for example:

exec dbms_output.put_line(dbms_utility.make_data_block_ address(1,7));

and then feed the number returned to

alter session set events 'immediate trace name blockdump level {address
from above}';

I am not sure that this will work on later versions of Oracle but you
can try it. It should create a trace in user_dump_dest. Remember that
block dumps are not too readable and considered internal information by
Oracle for use by support.

You could also use LogMiner (can be used on redo logs but much better on
archive logs) and dump out the transaction that way. See Toms book page
1097 for a section detailing how to use the packages. It is quite easy
really, create a dictionary, find the log you need and load it and start
log miner. Then you can query the contents from v$logmnr_contents.

You could also look at raw redo. There is a good paper at http://www.ora
faq.com/papers/redolog.pdf showing how to do this.

hth

kind regards

Pete

--
Pete Finnigan
emailete@petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

Pete Finnigan <pete@petefinnigan.com> wrote in message news:<iZfQlYBgirA$EwxS@peterfinnigan.demon.co.uk>. ..
>
> alter session set events 'immediate trace name blockdump level {address
> from above}';
>
> I am not sure that this will work on later versions of Oracle but you
> can try it. It should create a trace in user_dump_dest. Remember that

That's not available after Oracle 7. Beginning with Oracle 8, you have
to alter system to dump a block.

Yong Huang

andreyNSPAM@bookexchange.net (NetComrade) wrote in message news:<3f01ca60.2257970810@nyc.news.speakeasy.net>. ..
> Can somebody point to official or unofficial documentation on how to
> dump and read info of a rollback segment block? (based on info in
> v$transaction)
> I have some transactions that stay open for too long (usually
> transactions last <1 second, it's a web app). They have TX
> locks/transaction slots open. I have a suspicion it's due to sql over
> the link.
> Any other help would be appreciated as well.

I find Note:1039126.6 from metalink pretty helpful.

jg
--
@home.com is bogus. Remember pencil drops?
http://www.signonsandiego.com/news/c...rwarnings.html

Thanks Yong, I wasn't certain it was oracle 7 only.

cheers

Pete

In article <b3cb12d6.0307021315.5faedf6@posting.google.com> , Yong Huang
<yong321@yahoo.com> writes
>Pete Finnigan <pete@petefinnigan.com> wrote in message news:<iZfQlYBgirA$EwxS@pe
>terfinnigan.demon.co.uk>...
>>
>> alter session set events 'immediate trace name blockdump level {address
>> from above}';
>>
>> I am not sure that this will work on later versions of Oracle but you
>> can try it. It should create a trace in user_dump_dest. Remember that
>
>That's not available after Oracle 7. Beginning with Oracle 8, you have
>to alter system to dump a block.
>
>Yong Huang

--
Pete Finnigan
emailete@petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

On Tue, 01 Jul 2003 21:21:21 GMT, andreyNSPAM@bookexchange.net
(NetComrade) wrote:

>What if there were other sql statements executed after the 'offending'
>statement?
>
>Anyway, unfortunately sql_address and hash_value show zeros, i've
>already tried that, but thanks.
>
>The only lock for the session is a TX lock, and nothing else. the id's
>for TX locks containt rollback info, if I recall correctly, which is
>already given in v$transaction table.
>
>If there is any other way in identifying the TX lock source I could
>use, it would be greatly appreciated.
>
>Thanks,

There are issues involving distributed SELECT's causing TX locks on
rollback. Do you use these? Then maybe the following link can put you
on track:

http://www.jlcomp.demon.co.uk/faq/dblink_commit.html

HTH, Jaap.