Oracle PL/SQL Tutorial/System Tables Data Dictionary/v sql

Материал из SQL эксперт
Перейти к: навигация, поиск

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

   <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>