Trouver la liste des commandes DML et DDL gérées par Oracle : vue V$SQLCOMMAND - Find the list of DML and DDL commands
Introduction
Le dictionnaire de données d'Oracle renferme des milliers de vues; il est bien sur impossible de toutes les explorer mais certaines, au détour d'un livre ou d'un forum, m'ont bien plu. Par exemple V$SQLCOMMAND dont la définition est "displays the mapping between SQL opcodes and names" : en clair, c'est la liste de toutes les commandes Oracle qu'on peut exécuter :-)
Vous les connaissez déjà toutes? Vous êtes sur? Y compris UPSERT? Y compris UNDROP OBJECT?
Points d'attention
NA.
Base de tests
N'importe quelle base Oracle; tests sur une 12.1.
Exemples
============================================================================================
La liste des 175 commandes disponibles
============================================================================================
La vue V$SQLCOMMAND contient 175 commandes, cependant certaines n'y sont pas, comme le MERGE; est-ce que ce MERGE est juste une coquille et Oracle le traduit en INSERT, UPDATE ou DELETE selon les besoins?
Tri par COMMAND_TYPE
En classant par COMMAND_TYPE, on voit presque l'historique des commandes apparues avec les versions d'Oracle. CREATE TABLE est la base, elle a l'id 1, les commandes sur les pluggables database sont vers la fin... J'en ai même découvert que je ne connaissais absolument pas, comme l'opération "DROP MATERIALIZED ZONEMAP".
SQL> select COMMAND_TYPE, COMMAND_NAME from v$sqlcommand order by 1;
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 SEGMENT
37 ALTER ROLLBACK SEGMENT
38 DROP ROLLBACK SEGMENT
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
222 CREATE SCHEMA SYNONYM
224 DROP SCHEMA SYNONYM
225 ALTER DATABASE LINK
226 CREATE PLUGGABLE DATABASE
227 ALTER PLUGGABLE DATABASE
228 DROP PLUGGABLE DATABASE
229 CREATE AUDIT POLICY
230 ALTER AUDIT POLICY
231 DROP AUDIT POLICY
238 ADMINISTER KEY MANAGEMENT
239 CREATE MATERIALIZED ZONEMAP
240 ALTER MATERIALIZED ZONEMAP
241 DROP MATERIALIZED ZONEMAP
175 lignes selectionnees.
Tri par Nom
Si on classe par nom, on voit tous les ALTER, CREATE, DROP et d'autres commandes plus originales comme "ADMINISTER KEY MANAGEMENT",
"CALL METHOD", "DISASSOCIATE STATISTICS", les commandes PURGE etc etc.
SQL> select COMMAND_TYPE, COMMAND_NAME from v$sqlcommand order by 2;
COMMAND_TYPE COMMAND_NAME
------------ ----------------------------------------------------------------
238 ADMINISTER KEY MANAGEMENT
217 ALTER ASSEMBLY
230 ALTER AUDIT POLICY
5 ALTER CLUSTER
35 ALTER DATABASE
225 ALTER DATABASE LINK
175 ALTER DIMENSION
193 ALTER DISK GROUP
213 ALTER EDITION
219 ALTER FLASHBACK ARCHIVE
92 ALTER FUNCTION
11 ALTER INDEX
166 ALTER INDEXTYPE
161 ALTER JAVA
196 ALTER LIBRARY
75 ALTER MATERIALIZED VIEW
72 ALTER MATERIALIZED VIEW LOG
240 ALTER MATERIALIZED ZONEMAP
183 ALTER OPERATOR
179 ALTER OUTLINE
95 ALTER PACKAGE
98 ALTER PACKAGE BODY
227 ALTER PLUGGABLE DATABASE
25 ALTER PROCEDURE
67 ALTER PROFILE
70 ALTER RESOURCE COST
210 ALTER REWRITE EQUIVALENCE
79 ALTER ROLE
37 ALTER ROLLBACK SEGMENT
14 ALTER SEQUENCE
42 ALTER SESSION
172 ALTER SUMMARY
192 ALTER SYNONYM
49 ALTER SYSTEM
15 ALTER TABLE
40 ALTER TABLESPACE
58 ALTER TRACING
60 ALTER TRIGGER
80 ALTER TYPE
82 ALTER TYPE BODY
43 ALTER USER
88 ALTER VIEW
64 ANALYZE CLUSTER
63 ANALYZE INDEX
62 ANALYZE TABLE
168 ASSOCIATE STATISTICS
30 AUDIT OBJECT
170 CALL METHOD
190 CHANGE PASSWORD
29 COMMENT
44 COMMIT
216 CREATE ASSEMBLY
229 CREATE AUDIT POLICY
87 CREATE BITMAPFILE
4 CREATE CLUSTER
177 CREATE CONTEXT
57 CREATE CONTROL FILE
34 CREATE DATABASE
32 CREATE DATABASE LINK
174 CREATE DIMENSION
157 CREATE DIRECTORY
194 CREATE DISK GROUP
212 CREATE EDITION
218 CREATE FLASHBACK ARCHIVE
91 CREATE FUNCTION
9 CREATE INDEX
164 CREATE INDEXTYPE
160 CREATE JAVA
159 CREATE LIBRARY
74 CREATE MATERIALIZED VIEW
71 CREATE MATERIALIZED VIEW LOG
239 CREATE MATERIALIZED ZONEMAP
163 CREATE OPERATOR
180 CREATE OUTLINE
94 CREATE PACKAGE
97 CREATE PACKAGE BODY
188 CREATE PFILE
226 CREATE PLUGGABLE DATABASE
24 CREATE PROCEDURE
65 CREATE PROFILE
206 CREATE RESTORE POINT
52 CREATE ROLE
36 CREATE ROLLBACK SEGMENT
56 CREATE SCHEMA
222 CREATE SCHEMA SYNONYM
13 CREATE SEQUENCE
187 CREATE SPFILE
171 CREATE SUMMARY
19 CREATE SYNONYM
1 CREATE TABLE
39 CREATE TABLESPACE
59 CREATE TRIGGER
77 CREATE TYPE
81 CREATE TYPE BODY
51 CREATE USER
21 CREATE VIEW
209 DECLARE REWRITE EQUIVALENCE
7 DELETE
169 DISASSOCIATE STATISTICS
184 Do not use 184
185 Do not use 185
186 Do not use 186
215 DROP ASSEMBLY
231 DROP AUDIT POLICY
89 DROP BITMAPFILE
8 DROP CLUSTER
178 DROP CONTEXT
203 DROP DATABASE
33 DROP DATABASE LINK
176 DROP DIMENSION
158 DROP DIRECTORY
195 DROP DISK GROUP
214 DROP EDITION
220 DROP FLASHBACK ARCHIVE
93 DROP FUNCTION
10 DROP INDEX
165 DROP INDEXTYPE
162 DROP JAVA
84 DROP LIBRARY
76 DROP MATERIALIZED VIEW
73 DROP MATERIALIZED VIEW LOG
241 DROP MATERIALIZED ZONEMAP
167 DROP OPERATOR
181 DROP OUTLINE
96 DROP PACKAGE
99 DROP PACKAGE BODY
228 DROP PLUGGABLE DATABASE
68 DROP PROCEDURE
66 DROP PROFILE
207 DROP RESTORE POINT
211 DROP REWRITE EQUIVALENCE
54 DROP ROLE
38 DROP ROLLBACK SEGMENT
224 DROP SCHEMA SYNONYM
16 DROP SEQUENCE
173 DROP SUMMARY
20 DROP SYNONYM
12 DROP TABLE
41 DROP TABLESPACE
61 DROP TRIGGER
78 DROP TYPE
83 DROP TYPE BODY
53 DROP USER
22 DROP VIEW
50 EXPLAIN
204 FLASHBACK DATABASE
205 FLASHBACK TABLE
17 GRANT OBJECT
2 INSERT
26 LOCK TABLE
31 NOAUDIT OBJECT
27 NO-OP
47 PL/SQL EXECUTE
198 PURGE DBA RECYCLEBIN
201 PURGE INDEX
200 PURGE TABLE
199 PURGE TABLESPACE
197 PURGE USER RECYCLEBIN
28 RENAME
18 REVOKE OBJECT
45 ROLLBACK
46 SAVEPOINT
3 SELECT
90 SET CONSTRAINTS
55 SET ROLE
48 SET TRANSACTION
86 TRUNCATE CLUSTER
85 TRUNCATE TABLE
202 UNDROP OBJECT
6 UPDATE
182 UPDATE INDEXES
191 UPDATE JOIN INDEX
189 UPSERT
23 VALIDATE INDEX
0
175 lignes selectionnees.
Cette table semblait quelconque de prime abord mais elle liste beaucoup de commandes qui m'étaient totalement inconnues. Je ne sais même pas si sur le site docs.oracle.com il y a une page identique au contenu de cette vue.
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)