Oracle Database 9i: Where is my SQL_ID? Which SQL is in lock?

Struggling with that, right?
As you know, in Oracle Database 9i the view V$SESSION doesn’t have SQL_ID column…
So how to map SQLs in my database? And, for example, how to get the SQLs causing a lock?

In the end of the day, the SQL_ID is only a representation of the hash_value of an SQL. You can even make the translation from SQL_ID to Hash Value as you can check on this post by Tanel Poder.

Ok, but I have to map which sql is causing the lock in my 9i database, how can I do that?

Here it goes:

If session status is ACTIVE:

SELECT s1.sql_text from v$sqlarea s1,v$session s2 where s2.SID=&sid and s2.SQL_ADDRESS = s1.ADDRESS

If session status is INACTIVE:

SELECT s1.sql_text from v$sqlarea s1,v$session s2 where s2.SID=&sid and s2.prev_sql_addr = s1.ADDRESS

You’re welcome! 😉

See you next week!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s