[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.
--很明顯這些不全！查詢哪些表或者檢視獲得比較全面資訊呢？自己做了一些嘗試：
--很明顯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,很奇怪的表示式。
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('InstancGV$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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- V$sql_text v$sqlarea v$sql 的區別SQL
- V$SQL、V$SQLSTATS、V$SQLAREASQL
- v$sql和v$sqlarea的區別SQL
- v$sql v$sqlarea v$sql_shared_cursor及遊標SQL
- sql執行計劃_v$sqlarea_v$sql_v$sql_shared_cursorSQL
- v$sqlarea,v$sql,v$sqltext的區別和聯絡SQL
- Oracle9i中v$sql、v$sqlarea、v$sqltext、v$sql_plan的聯絡與區別OracleSQL
- v$sql,v$sqlarea,v$sqltext區別SQL
- v$sqlarea,v$sql,v$sqltext三個檢視的區別SQL
- v$sqlarea,v$sql,v$sqltext的區別和聯絡(zt)SQL
- V$SQL 和V$SQLAREA區別SQL
- cursor_sharing與v$sqlarea_v$sql_v$sql_shared_cursorSQL
- v$sql v$sqlarea v$sql_shared_cursor及父遊標，子游標SQL
- [ZT]v$sqlarea,v$sql,v$sqltext這三個檢視提供的sql語句有什麼區別SQL
- v$sql檢視和v$sqlarea檢視的構建SQL
- 檢視 v$sql,v$sqlarea,$sqltext,v$sqltext_with_newlines 的差異SQL
- V$SQL_BIND_CAPTURESQLAPT
- V$SQL_BIND_DATASQL
- (轉):學習Oracle動態效能表-(6)-V$SQL,V$SQL_PLANOracleSQL
- 顯示v$sqltext中完整的sql資訊SQL
- v$session之小測試（二）_與v$sqlSessionSQL
- Whats the difference between the v$sql* viewsSQLView
- v$sql.command_type and v$session.commandSQLSession
- 查詢等待事件(wait event)相關的SQL - v$session_wait, v$rowcache,v$sqltext事件AISQLSession
- 11g新動態效能檢視V$SQL_MONITOR,V$SQL_PLAN_MONITORSQL
- v$SORT_USAGE.SQL_ID 不是會話當前的執行的SQL IDSQL會話
- 使用V$SQL_PLAN檢視SQL
- V$SQLAREA 檢視TOP_SQLSQL
- 11g v$sql 新增列SQL
- v$sql_plan 檢視解析SQL
- 授權某使用者，檢視動態效能檢視的許可權（如v$latch,v$lock,v$sqlarea,v$sql,v$sysstat）SQL
- SQL效能的度量 - 透過v$sql_plan查詢執行計劃SQL
- v$sql_shared_cursor中的BIND_MISMATCHSQL
- v$sqlarea_parent cursor_v$sql_child cursor關係SQL
- SQL調優公式T＝S/V (zt)SQL公式
- oracle之 v$sql_monitor 監視正在執行的SQL語句的統計資訊OracleSQL
- SQL語法提示工具SQL Prompt 釋出v10.6SQL
- V$SQL_SHARED_CURSOR檢視硬解析的原因SQL