Oracle PL/SQL Tutorial/System Tables Data Dictionary/v sql
<source lang="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></source>