RE: How to identify the SQL being rolled back?

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Fri, 26 Aug 2005 13:12:17 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C45023614D1@NT15.oneneck.corp>

Another great idea from Raj - thanks! v$open_cursor shows me the history of statements that were executed prior to the rollback:

SQL> select hash_value, sql_text from v$open_cursor where sid = 307;

HASH_VALUE SQL_TEXT
---------- ------------------------------------------------------------

2702978414 DELETE IBS_SPEC_ASSIGNMENTS     WHERE SPEC_ID = :b1
2628285900 DELETE FROM IBS_SPEC     WHERE spec_id = :b1
1235661278 DELETE FROM SOBJ_AUDIT_ACTIONS           WHERE spec_id = :b1
1226881397 ROLLBACK
 131654839 DELETE FROM IBS_SPEC_STRUCTURE     WHERE parent_spec_id = :b
2450592051 DELETE FROM IBS_SPEC_VALUE     WHERE spec_id = :b1

347018313 BEGIN PKG_IBS_ITEM_SPEC.SP_DELETE_SPEC (:1,:2,:3,:4,:5); END 2274563541 BEGIN PKG_CLEANUP.SP_DELETE_UNLINKED_REF_OBJECTS (:1,:2,:3,: 1399146842 DELETE IBS_SPEC_ASSIGNMENTS WHERE ASS_SPEC_ID = :b1 3476119084 SELECT COUNT(*) FROM WF_EVENT WHERE bo_id = :b1 OR next_b 3692263296 DELETE FROM SMART_SECTION_ROWS WHERE spec_id = :b1 3164970367 select distinct object_id from object_formula_queue 1053795750 COMMIT
646950321 DELETE FROM IBS_SPEC_VALUE_NOTES WHERE spec_id = :b1 2870329576 SELECT COUNT(*) FROM USER_CREDENTIALS WHERE user_id = :b1 784582693 delete OBJECT_SEARCH_PARSE_QUEUE where OBJECT_ID=:1

 557834814 INSERT INTO OBJECT_SEARCH_PARSE_QUEUE (object_id)        VAL
1855317302 DELETE FROM SMART_SECTION_ROOT_OBJS     WHERE spec_id = :b1

18 rows selected.

-----Original Message-----

From: rjamya [mailto:rjamya_at_gmail.com]
Sent: Friday, August 26, 2005 12:44 PM
To: Allen, Brandon
Subject: Re: How to identify the SQL being rolled back?

can you check v$open_cursor for that sid and see sqls for the hash values you get? I am hoping that the cursor isn't closed yet.

honetly I am shooting in the dark.
Raj

On 8/26/05, Allen, Brandon <Brandon.Allen_at_oneneck.com> wrote: Excellent idea, unfortunately, v$session is showing the same values for the prev* columns as for the current address and hash_value columns - which just point to the "ROLLBACK" sql_text.

Thanks anyway!

-----Original Message-----

From: rjamya [mailto:rjamya_at_gmail.com]
Sent: Friday, August 26, 2005 12:32 PM
To: Allen, Brandon
Subject: Re: How to identify the SQL being rolled back?

how about v$session.prev_sql_addr and prev_hash_value for that session... can you use those to look into v$sql? does it give you anything useful?

Raj

On 8/26/05, Allen, Brandon <Brandon.Allen_at_oneneck.com> wrote: Thanks Jared - nice script. Unfortunately it doesn't tell me what I want to know - it still shows only the "ROLLBACK" from v$sql, but I'm trying to find *what* is being rolled back - either the original SQL statements, or at least the objects that are being rolled back. Any more ideas?

SQL> @showtrans

                                       Recur               Used         Used
O/S      Oracle           R-S    Space sive  No             Rbs          RBS        Logical       Physical
User     Userid       SID Name   Trans Trans Undo          Blks         Recs        IO Blks        IO Blks

-------- ---------- ----- ------ ----- ----- ----- ------------ ------------ -------------- --------------
Current
Statement

SYSTEM   TRIRIGA      307 _SYSSM NO    NO    NO         138,823    7,804,812    104,698,963      2,298,711
                          U7$

ROLLBACK
-----Original Message-----

From: Jared Still [mailto:jkstill_at_gmail.com] Sent: Friday, August 26, 2005 12:13 PM

You might try the script below.

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--

http://www.freelists.org/webpage/oracle-l

select standard_disclaimer from company_requirements where category = 'MANDATORY';

select standard_disclaimer from company_requirements where category = 'MANDATORY';
--

http://www.freelists.org/webpage/oracle-l Received on Fri Aug 26 2005 - 15:12:49 CDT