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?

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.
      

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.