Oracle PL/SQL/System Tables Views/V sql
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>