[20120207]v$sql的command_type.txt

今天一位朋友问v$sql中的字段command_type里面的数字表示哪些？我仅仅比较熟悉2个
3=select
47=Pl/sql Execute

其它我就不是很熟悉了。

我查询我的测试机器：
select distinct command_type from v$sql order by 1;

COMMAND_TYPE
------------
           2
           3
           6
           7
          26
          47

6 rows selected.

--很明显这些不全！查询哪些表或者视图获得比较全面信息呢？自己做了一些尝试：

SELECT view_name,substr(view_definition,1,86) FROM gv$fixed_view_definition WHERE LOWER (view_definition) LIKE '%command_type%';

VIEW_NAME                      SUBSTR(VIEW_DEFINITION,1,86)

------------------------------ --------------------------------------------------------------------------------------

V$SQLAREA                      select    SQL_TEXT,           SQL_FULLTEXT,           SQL_ID,           SHARABLE_MEM,

V$SQL                          select  SQL_TEXT , SQL_FULLTEXT , SQL_ID,  SHARABLE_MEM , PERSISTENT_MEM , RUNTIME_MEM

V$SQLAREA_PLAN_HASH            select  SQL_TEXT,          SQL_FULLTEXT,          ADDRESS,          HASH_VALUE,

V$SQLTEXT                      select  ADDRESS, HASH_VALUE, SQL_ID, COMMAND_TYPE , PIECE,  SQL_TEXT from GV$SQLTEXT w

V$SQLTEXT_WITH_NEWLINES        select  ADDRESS, HASH_VALUE, SQL_ID, COMMAND_TYPE, PIECE,          SQL_TEXT  from GV$S

V$SQLCOMMAND                   select COMMAND_TYPE, COMMAND_NAME  from GV$SQLCOMMAND where inst_id = USERENV('Instanc

GV$STREAMS_MESSAGE_TRACKING    SELECT inst_id, tracking_label_knstmt, tag_knstmt, component_name_knstmt,         comp

V$STREAMS_MESSAGE_TRACKING     select TRACKING_LABEL, TAG, COMPONENT_NAME, COMPONENT_TYPE,         ACTION, ACTION_DET

V$SQL_REDIRECTION              select ADDRESS,PARENT_HANDLE,HASH_VALUE,SQL_ID,CHILD_NUMBER, PARSING_USER_ID, PARSING_

9 rows selected.

--很明显V$SQLCOMMAND 满足需要。


COMMAND_TYPE COMMAND_NAME
------------ -------------------------
           0
           1 CREATE TABLE
           2 INSERT
           3 SELECT
           4 CREATE CLUSTER
           5 ALTER CLUSTER
           6 UPDATE
           7 DELETE
           8 DROP CLUSTER
           9 CREATE INDEX
          10 DROP INDEX
          11 ALTER INDEX
          12 DROP TABLE
          13 CREATE SEQUENCE
          14 ALTER SEQUENCE
          15 ALTER TABLE
          16 DROP SEQUENCE
          17 GRANT OBJECT
          18 REVOKE OBJECT
          19 CREATE SYNONYM
          20 DROP SYNONYM
          21 CREATE VIEW
          22 DROP VIEW
          23 VALIDATE INDEX
          24 CREATE PROCEDURE
          25 ALTER PROCEDURE
          26 LOCK TABLE
          27 NO-OP
          28 RENAME
          29 COMMENT
          30 AUDIT OBJECT
          31 NOAUDIT OBJECT
          32 CREATE DATABASE LINK
          33 DROP DATABASE LINK
          34 CREATE DATABASE
          35 ALTER DATABASE
          36 CREATE ROLLBACK SEG
          37 ALTER ROLLBACK SEG
          38 DROP ROLLBACK SEG
          39 CREATE TABLESPACE
          40 ALTER TABLESPACE
          41 DROP TABLESPACE
          42 ALTER SESSION
          43 ALTER USER
          44 COMMIT
          45 ROLLBACK
          46 SAVEPOINT
          47 PL/SQL EXECUTE
          48 SET TRANSACTION
          49 ALTER SYSTEM
          50 EXPLAIN
          51 CREATE USER
          52 CREATE ROLE
          53 DROP USER
          54 DROP ROLE
          55 SET ROLE
          56 CREATE SCHEMA
          57 CREATE CONTROL FILE
          58 ALTER TRACING
          59 CREATE TRIGGER
          60 ALTER TRIGGER
          61 DROP TRIGGER
          62 ANALYZE TABLE
          63 ANALYZE INDEX
          64 ANALYZE CLUSTER
          65 CREATE PROFILE
          66 DROP PROFILE
          67 ALTER PROFILE
          68 DROP PROCEDURE
          70 ALTER RESOURCE COST
          71 CREATE MATERIALIZED VIEW LOG
          72 ALTER MATERIALIZED VIEW LOG
          73 DROP MATERIALIZED VIEW  LOG
          74 CREATE MATERIALIZED VIEW
          75 ALTER MATERIALIZED VIEW
          76 DROP MATERIALIZED VIEW
          77 CREATE TYPE
          78 DROP TYPE
          79 ALTER ROLE
          80 ALTER TYPE
          81 CREATE TYPE BODY
          82 ALTER TYPE BODY
          83 DROP TYPE BODY
          84 DROP LIBRARY
          85 TRUNCATE TABLE
          86 TRUNCATE CLUSTER
          87 CREATE BITMAPFILE
          88 ALTER VIEW
          89 DROP BITMAPFILE
          90 SET CONSTRAINTS
          91 CREATE FUNCTION
          92 ALTER FUNCTION
          93 DROP FUNCTION
          94 CREATE PACKAGE
          95 ALTER PACKAGE
          96 DROP PACKAGE
          97 CREATE PACKAGE BODY
          98 ALTER PACKAGE BODY
          99 DROP PACKAGE BODY
         157 CREATE DIRECTORY
         158 DROP DIRECTORY
         159 CREATE LIBRARY
         160 CREATE JAVA
         161 ALTER JAVA
         162 DROP JAVA
         163 CREATE OPERATOR
         164 CREATE INDEXTYPE
         165 DROP INDEXTYPE
         166 ALTER INDEXTYPE
         167 DROP OPERATOR
         168 ASSOCIATE STATISTICS
         169 DISASSOCIATE STATISTICS
         170 CALL METHOD
         171 CREATE SUMMARY
         172 ALTER SUMMARY
         173 DROP SUMMARY
         174 CREATE DIMENSION
         175 ALTER DIMENSION
         176 DROP DIMENSION
         177 CREATE CONTEXT
         178 DROP CONTEXT
         179 ALTER OUTLINE
         180 CREATE OUTLINE
         181 DROP OUTLINE
         182 UPDATE INDEXES
         183 ALTER OPERATOR
         184 Do not use 184
         185 Do not use 185
         186 Do not use 186
         187 CREATE SPFILE
         188 CREATE PFILE
         189 UPSERT
         190 CHANGE PASSWORD
         191 UPDATE JOIN INDEX
         192 ALTER SYNONYM
         193 ALTER DISK GROUP
         194 CREATE DISK GROUP
         195 DROP DISK GROUP
         196 ALTER LIBRARY
         197 PURGE USER RECYCLEBIN
         198 PURGE DBA RECYCLEBIN
         199 PURGE TABLESPACE
         200 PURGE TABLE
         201 PURGE INDEX
         202 UNDROP OBJECT
         203 DROP DATABASE
         204 FLASHBACK DATABASE
         205 FLASHBACK TABLE
         206 CREATE RESTORE POINT
         207 DROP RESTORE POINT
         209 DECLARE REWRITE EQUIVALENCE
         210 ALTER REWRITE EQUIVALENCE
         211 DROP REWRITE EQUIVALENCE
         212 CREATE EDITION
         213 ALTER EDITION
         214 DROP EDITION
         215 DROP ASSEMBLY
         216 CREATE ASSEMBLY
         217 ALTER ASSEMBLY
         218 CREATE FLASHBACK ARCHIVE
         219 ALTER FLASHBACK ARCHIVE
         220 DROP FLASHBACK ARCHIVE
         225 ALTER DATABASE LINK

163 rows selected.

SELECT view_name,substr(view_definition,1,120)  FROM gv$fixed_view_definition where view_name='GV$SQLCOMMAND';
VIEW_NAME                      SUBSTR(VIEW_DEFINITION,1,120)
------------------------------ --------------------------------------------------------------
GV$SQLCOMMAND                  select inst_id, indx, oct_name  from x$oct where indx=oct_type
1 row selected.

--从另外的方式查询，可以知道基表是x$oct.注意它的where条件indx=oct_type,很奇怪的表达式。

SQL> desc x$oct;

Name      Null?    Type

--------- -------- ------------

ADDR               RAW(8)

INDX               NUMBER

INST_ID            NUMBER

OCT_TYPE           NUMBER

OCT_NAME           VARCHAR2(64)

--原来OCT_TYPE也是里面一个字段。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-715738/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-715738/