This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

SQL_OPCODE values from an 11gR2 Database

If you have a need to find the actual numeric operation code for different SQL operations, then this is possible if you can access the ASH table as highlighted below.

The snapshot was taken from an 11gR2 database:

SQL> select distinct sql_opname,
       from dba_hist_active_sess_history;

SQL_OPNAME                SQL_OPCODE
------------------------- -----------------
DELETE                    7
ALTER DATABASE            35
ANALYZE TABLE             62
ALTER TABLE               15
INSERT                    2
UPSERT                    189
ALTER SUMMARY             172
CREATE TABLE              1
SELECT                    3
LOCK TABLE                26
CREATE INDEX              9
PL/SQL EXECUTE            47
TRUNCATE TABLE            85
CALL METHOD               170
UPDATE                    6

I guess the newer the “feature” the higher the number, since Oracle couldn’t just go and change the number between releases.

Add Your Comment

* Indicates Required Field

Your email address will not be published.