Oracle PL/SQL/System Tables Views/V sql

Display the text of a cached query in the shared pool, then the execution plan.

    
SQL>
SQL> SELECT sql_text
  2  FROM v$sql
  3  WHERE sql_text LIKE "%&1%"
  4    AND sql_text NOT LIKE "%v$sql%"
  5    and rownum < 20
  6  /
Enter value for 1:
old   3: WHERE sql_text LIKE "%&1%"
new   3: WHERE sql_text LIKE "%%"
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select id, name, block_size, advice_status,                  size_for_estimate, size_factor, buffers_for_estimate,        estd_physical_read_factor, estd_physical_reads,
   estd_physical_read_time,                                     estd_pct_of_db_time_for_reads,                       estd_cluster_reads,
      estd_cluster_read_time                                       from   gv$db_cache_advice where inst_id = userenv("instance")
delete from user_history$ where user# = :1
DECLARE  STATUS VARCHAR2(20);  DSTART DATE;  DSTOP DATE;  PVALUE NUMBER;  PNAME VARCHAR2(30); BEGIN  PNAME := "mbrc";  DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pva
lue, stattab => " mystats", statid => "DAY", statown => "sqle");  DBMS_OUTPUT.PUT_LINE("average multiblock readcount: "||pvalue); END;
SELECT NULL FROM DR$INDEX WHERE IDX_STATUS = :B2 AND IDX_OWNER# = -USERENV("SESSIONID") AND IDX_NAME = :B1
update sys.job$ set this_date=:1 where job=:2
update sys.job$ set this_date=:1 where job=:2
select ts# from ts$ where name=:1
SELECT  topology   FROM  SDO_TOPO_METADATA_TABLE a, TABLE(a.Topo_Geometry_Layers) b   WHERE b.owner = "sqle"  AND b.table_name = "ORD"
SELECT  DBMS_METADATA.GET_DDL("TABLE", "emp", "STUDENT1") FROM DUAL
delete from idl_sb4$ where obj#=:1 and part=:2 and version<>:3
update histgrm$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4
delete from  viewtrcol$ where obj#=:1
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
begin "SYS"."DBMS_REPCAT_UTL"."DROP_USER_REPSCHEMA"(:myuser); end;
begin :1 := ctxsys.drvxmd.NextIndexObject(:2,:3,:4);end;
begin :1 := ctxsys.drvxmd.NextIndexObject(:2,:3,:4);end;
DELETE FROM SYS.AUX_STATS$ WHERE SNAME = "SYSSTATS_INFO"
SELECT IXO_CLA_ID, IXO_OBJ_ID, IXO_ACNT FROM DR$INDEX_OBJECT WHERE IXO_IDX_ID = :B1 ORDER BY IXO_CLA_ID
delete from defrole$ where user#=:1 and role#=:2 and not exists (select null from sysauth$ where grantee#=1 and privilege#=:2)
begin "SYS"."DBMS_REGISTRY_SYS"."DROP_USER"(:myuser); end;
19 rows selected.
SQL> col id format 99
SQL> col operation format a20
SQL> col options format a20
SQL> col object_name format a30
SQL> col cost format 9999
SQL>
SQL> SELECT id, operation, options, object_name, cost
  2  FROM v$sql_plan VP, v$sql VS
  3  WHERE VP.address = VS.address
  4    AND VP.hash_value = VS.hash_value
  5    AND sql_text LIKE "%&1%"
  6    AND sql_text NOT LIKE "%v$sql%"
  7    and rownum < 20
  8  ORDER BY id
  9  /
Enter value for 1:
old   5:   AND sql_text LIKE "%&1%"
new   5:   AND sql_text LIKE "%%"
 ID OPERATION            OPTIONS              OBJECT_NAME                     COST
--- -------------------- -------------------- ------------------------------ -----
  0 DELETE STATEMENT                                                             2
  0 SELECT STATEMENT                                                             3
  0 DELETE STATEMENT                                                             1
  1 DELETE                                    FGACOL$
  1 DELETE                                    OBJAUTH$
  1 TABLE ACCESS         BY INDEX ROWID       OBJAUTH$                           3
  2 INDEX                RANGE SCAN           I_FGACOL
  2 INDEX                RANGE SCAN           I_OBJAUTH1                         2
  2 INDEX                RANGE SCAN           I_OBJAUTH1                         2
  3 NESTED LOOPS                                                                53
  6 TABLE ACCESS         BY INDEX ROWID       USER$                              1
  8 TABLE ACCESS         BY INDEX ROWID       OBJ$                               3
  9 INDEX                RANGE SCAN           I_OBJ2                             2
 10 TABLE ACCESS         BY INDEX ROWID       NTAB$                             12
 11 INDEX                RANGE SCAN           I_NTAB3
 12 TABLE ACCESS         CLUSTER              COL$                               1
 13 NESTED LOOPS                                                                18
 ID OPERATION            OPTIONS              OBJECT_NAME                     COST
--- -------------------- -------------------- ------------------------------ -----
 14 INDEX                RANGE SCAN           I_OBJAUTH1                         2
 15 FIXED TABLE          FULL                 X$KZSRO                           16
19 rows selected.
SQL>

Query v$sql for sql text

  
SQL>
SQL> alter session set cursor_sharing=force;
Session altered.
SQL> select substr( username, 1, 1 )
  2    from all_users au2
  3   where rownum = 1;
SUBSTR(USERNAME,1,1)
------------------------------
S
1 row selected.
SQL>
SQL> select sql_text from v$sql
  2     where sql_text like "select /* TAG */ %";
no rows selected
SQL>
SQL>