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!

Parameter Workarounds: Changing your Entire Database’s Behavior Because of a Query

This post is just a reflection, basically based on my last post about Adaptive Query Optimization/SQL Plan Directives. Several times we find some situations that can be solved by setting a parameter (likely a “_”) and we just proceed with an “alter system” disregarding about all the impact of this.

This is specially important when talking about “optimizer” parameters even for bugs causing ORA-600’s or performance issues… Changing those parameters in system will affect not only the SQL with the error but all SQLs in database, which may cause a really bad effect in some cases.

bad-idea-sign

Ok Matheus, but what can I do?
Let’s to the obvious and generally applied methods:

Continue reading

Converting Between SQLServer, Oracle, PostgreSQL, MySQL, Sybase and others…

Hi all!
I was asked to make a conversion from T-SQL (MSSQL) Procedure to PL/PGSQL. Regarding how boring is this task, the follow link helped me:
http://www.sqlines.com/online

I highly recommend it. The site has a commercial solution to convert all database, but some code can be converted online for free. 🙂
The conversion not fixed at all, but make a good part of the work… And all help is helpful…

Continue reading

Materialized View with DBLink: ORA-00600: internal error code, arguments: [kkzuasid]

Hello guys!
Not being able to refresh you Materialized View because of this error?

bamdb> exec dbms_mview.refresh('PROD_ORABAM.MVIEW_TEST','C');
BEGIN dbms_mview.refresh('PROD_ORABAM.MVIEW_TEST','C'); END;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkzuasid], [2], [0], [1], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
ORA-06512: at line 1

The bad new is there is no workaround (I usually prefer workaround for this, is quicker and less complicated).
But the good new is there is a patch for this: Patch 17705023 : ORA-600 [KKZUASID] ON MV REFRESH

Continue reading