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!
This days I faced some ORA-600 as per below in a database:
ORA-00600: internal error code, arguments: , , , , , , , , , , 
Checking for error in Database I find it was related to a pretty complex UPDATE, as per:
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.
Ok Matheus, but what can I do?
Let’s to the obvious and generally applied methods:
As a second part of last week’s post, here is a way to accomplish that without using audit. Using a simple logon trigger! 🙂
Take a look:
First step: Create a table to store the data.
CREATE TABLE SYS.TRG_LOGON (SCHEMA VARCHAR2(50), SERVER VARCHAR2(200), FAILTIME DATE, ERROR VARCHAR2(200));
Have you ever killed a session in database and the session doesn’t disappear? Or yet, you killed the SPID of the session in OS and the session still running in database, but now you cannot see the SPID?
Very weird, but it happens. First of all, let us point some interesting items:
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:
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…
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], , , , , , , , , , , 
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