I recall looking at this briefly. Since you did not post a test case, the amount of investigative effort must have been more than I had time for, so it fell through the cracks. Anyway, my guess would be that any bug here is on the database side.
A quick Google on "Oracle merge statement type 189" yielded some blog references saying that type 189 is a merge statement and that such statements do not show up in SYS.AUDIT_ACTIONS. For example...
Not sure which database versions may be involved, or if any bugs where logged against the Oracle RDBMS.
SQL Developer Team
Hello Gary Grahem.
Thank you for the response.
Tools -> Monitor sessions runs the following SQL query:
with vs as (select rownum rnum,
select vs.sid ,serial# serial, vs.sql_id,
case when vs.status = 'ACTIVE'
else null end "Seconds in Wait",
97,'CRE PKG BODY',
98,'ALT PKG BODY',
99,'DRP PKG BODY',
vs.osuser "OS User",
where vs.USERNAME is not null
and nvl(vs.osuser,'x') <> 'SYSTEM'
and vs.type <> 'BACKGROUND'
order by 1
You can get this text by pushing "SQL" button above table from result of Tools -> Monitor sessions.
I guess it was developed by SQL Dev team.
And as you can see - there is no decode branch for value "189".
Test case - run any MERGE statement that works about minute, then open Tools -> Monitor sessions, find your session and look into "Command" column.
We're licensed through the database, so if you can log bugs on the database, you can log them on SQL Developer.
Also, you can copy that report down to a user defined report and add your own decode item for MERGE - instructions on how to do that here.
Test case - run any MERGE statement that works about minute
Keep in mind that the person trying to respond, especially a developer, may not have such a case readily available. That decreases the likelihood of receiving a response. Using the SQL button and examining the decode suffices in this case. Thanks.
Best to log a bug through My Oracle Support, as Jeff says. While adding a 189,'MERGE' line to the decode seems easiest, it would be nice to get a general fix where the matching SQL command is retrieved for any command number. The MERGE command came out with Oracle 9i, but in taking a look through some SYS views that map SQL command (name,number) pairs (in Oracle 11.2), I do not see any that lists all commands.
The MERGE command came out with Oracle 9i, but in taking a look through some SYS views that map SQL command (name,number) pairs (in Oracle 11.2), I do not see any that lists all commands.
If you just want to see a list of them look at the 'catmacd.sql' script for data vault - there are over 250 entries there
--INSERT INTO dvsys.code_t$(id#, value, description, language) VALUES(188,'CREATE PFILE','','us') ;
--INSERT INTO dvsys.code_t$(id#, value, description, language) VALUES(189,'MERGE','','us') ;
In terms of a list you could use in sql developer you will likely need to get the appropriate source from the DB team.
Thanks for this tip. I suppose the fact these entries are only commented out rather than removed entirely provides some measure of comfort. A view containing these mappings or, better yet, a look-up API, would be welcome. We will have to wait and see, based on a bug getting logged -- one clue might be whether/how AWR fixed this.