12c New Feature: DBMS_UTILITY.EXPAND_SQL_TEXT


hund

This new feature allows to transform an SQL statement that contains views. The result is a SQL statement in which the views are dissolved and integrated into the SQL.This support for the DBA to find application logic errors or performance problems.

Here is a example where a query from your application and Views are used. In my case i used a sample SQL to query the Oracle session and the Process ID.

select p.spid,s.sid,s.serial#,s.username,s.status
 from v$process p,v$session s
 where s.paddr = p.addr and s.status = 'ACTIVE' and s.username like '%SYS%';

Now let us transform the Query to see which tables are used. Here is a small example code.

declare 
isql CLOB := 'select p.spid,s.sid,s.serial#,s.username,s.status from v$process p,v$session s where s.paddr = p.addr and s.status = ''ACTIVE'' and s.username like ''%SYS%'''; 
osql CLOB; 
begin 
DBMS_UTILITY.EXPAND_SQL_TEXT ( isql,osql);
dbms_output.put_line(osql); 
end;

What we now get here as return value is the complete SQL Statement.

SELECT "A2"."SPID" "SPID","A1"."SID" "SID","A1"."SERIAL#" "SERIAL#","A1"."USERNAME" "USERNAME","A1"."STATUS" "STATUS" 
  FROM  (SELECT "A3"."ADDR" "ADDR","A3"."SPID" "SPID","A3"."USERNAME" "USERNAME","A3"."SERIAL#" "SERIAL#" 
  FROM  (SELECT "A8"."INST_ID" "INST_ID","A8"."ADDR" "ADDR","A8"."KSUPRPID" "SPID","A8"."KSUPRUNM" "USERNAME","A8"."KSUPRSER" "SERIAL#" FROM SYS."X$KSUPR" "A8" 
 WHERE  BITAND("A8"."KSSPAFLG",1)<>0) "A3" WHERE "A3"."INST_ID"=USERENV('INSTANCE')) "A2", 
 (SELECT "A4"."SID" "SID","A4"."SERIAL#" "SERIAL#","A4"."PADDR" "PADDR","A4"."USERNAME" "USERNAME","A4"."STATUS" "STATUS" 
  FROM  (SELECT "A7"."INST_ID" "INST_ID","A7"."INDX" "SID","A7"."KSUSESER" "SERIAL#","A7"."KSUSEPRO" "PADDR","A7"."KSUUDLNA" "USERNAME",
  DECODE(BITAND("A7"."KSUSEIDL",11),1,'ACTIVE',0,DECODE(BITAND("A7"."KSUSEFLG",4096),0,'INACTIVE','CACHED'),2,'SNIPED',3,'SNIPED','KILLED') "STATUS" 
   FROM SYS."X$KSUSE" "A7",SYS."X$KSLED" "A6",SYS."X$KSLWT" "A5" 
  WHERE BITAND("A7"."KSSPAFLG",1)<>0 AND BITAND("A7"."KSUSEFLG",1)<>0 AND "A7"."INDX"="A5"."KSLWTSID" AND "A5"."KSLWTEVT"="A6"."INDX") "A4" 
  WHERE "A4"."INST_ID"=USERENV('INSTANCE')) "A1" WHERE "A1"."PADDR"="A2"."ADDR" AND "A1"."STATUS"='ACTIVE' AND "A1"."USERNAME" LIKE '%SYS%'
Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s