Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Saturday, May 25, 2013

SQL_OPCODE values from an 11gR2 Database

Print Friendly and PDF
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.

No comments: