Tag: ASH

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,
                     sql_opcode
       from dba_hist_active_sess_history;

SQL_OPNAME                SQL_OPCODE
------------------------- -----------------
ALTER TABLESPACE          40
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
                          0
UPDATE                    6

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