9 Replies Latest reply on Aug 14, 2014 2:21 AM by Gary Graham-Oracle

    4.0.2.15 bug - "merge" statement type is shown as 189

    Working!

      Hello.

       

      SQL Developer 4.0.2.15 on Windows 7 64-bit

       

      In Tools -> Monitor sessions: SQL statement of type merge as shown as "189" in "command" column.

      I guess it's a bug.

        • 1. Re: 4.0.2.15 bug - "merge" statement type is shown as 189
          Working!

          Do any SQLDev team members go through this forum?

           

          Jeff Smith SQLDev PM Gary Graham Where are you?

          • 2. Re: 4.0.2.15 bug - "merge" statement type is shown as 189
            Gary Graham-Oracle

            Hi Working!,

             

            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...

            http://mwidlake.wordpress.com/2010/01/08/more-on-command_type-values/

            https://mwidlake.wordpress.com/tag/awr/

             

            Not sure which database versions may be involved, or if any bugs where logged against the Oracle RDBMS.

             

            Regards,

            Gary

            SQL Developer Team

            • 3. Re: 4.0.2.15 bug - "merge" statement type is shown as 189
              Jeff Smith Sqldev Pm-Oracle

              Yes, but not for bug reports. This is a community forum for questions and discussions. Bugs should go to My Oracle Support. Unless it's a Beta/Early Adopter..

              • 4. Re: 4.0.2.15 bug - "merge" statement type is shown as 189
                Working!

                Hello Gary Grahem.

                Thank you for the response.

                 

                Tools -> Monitor sessions runs the following SQL query:

                 

                 

                                                                  with vs as (select rownum rnum,

                                                                                              sid,

                                                                                              serial#,

                                                                                              status,

                                                                                              username,

                                                                                              last_call_et,

                                                                                              command,

                                                                                              machine,

                                                                                              osuser,

                                                                                              module,

                                                                                              action,

                                                                                              resource_consumer_group,

                                                                                              client_info,

                                                                                              client_identifier,

                                                                                              type,

                                                                                              terminal,

                                                                                              sql_id

                                                                                         from gv$session)

                                                                             select vs.sid ,serial# serial, vs.sql_id,

                                                                                    vs.username "Username",

                                                                                    case when vs.status = 'ACTIVE'

                                                                                              then last_call_et

                                                                                         else null end "Seconds in Wait",

                                                                                    decode(vs.command, 

                                                                                     0,null,

                                                                                     1,'CRE TAB',

                                                                                     2,'INSERT',

                                                                                     3,'SELECT',

                                                                                     4,'CRE CLUSTER',

                                                                                     5,'ALT CLUSTER',

                                                                                     6,'UPDATE',

                                                                                     7,'DELETE',

                                                                                     8,'DRP CLUSTER',

                                                                                     9,'CRE INDEX',

                                                                                     10,'DROP INDEX',

                                                                                     11,'ALT INDEX',

                                                                                     12,'DROP TABLE',

                                                                                     13,'CRE SEQ',

                                                                                     14,'ALT SEQ',

                                                                                     15,'ALT TABLE',

                                                                                     16,'DROP SEQ',

                                                                                     17,'GRANT',

                                                                                     18,'REVOKE',

                                                                                     19,'CRE SYN',

                                                                                     20,'DROP SYN',

                                                                                     21,'CRE VIEW',

                                                                                     22,'DROP VIEW',

                                                                                     23,'VAL INDEX',

                                                                                     24,'CRE PROC',

                                                                                     25,'ALT PROC',

                                                                                     26,'LOCK TABLE',

                                                                                     28,'RENAME',

                                                                                     29,'COMMENT',

                                                                                     30,'AUDIT',

                                                                                     31,'NOAUDIT',

                                                                                     32,'CRE DBLINK',

                                                                                     33,'DROP DBLINK',

                                                                                     34,'CRE DB',

                                                                                     35,'ALTER DB',

                                                                                     36,'CRE RBS',

                                                                                     37,'ALT RBS',

                                                                                     38,'DROP RBS',

                                                                                     39,'CRE TBLSPC',

                                                                                     40,'ALT TBLSPC',

                                                                                     41,'DROP TBLSPC',

                                                                                     42,'ALT SESSION',

                                                                                     43,'ALT USER',

                                                                                     44,'COMMIT',

                                                                                     45,'ROLLBACK',

                                                                                     46,'SAVEPOINT',

                                                                                     47,'PL/SQL EXEC',

                                                                                     48,'SET XACTN',

                                                                                     49,'SWITCH LOG',

                                                                                     50,'EXPLAIN',

                                                                                     51,'CRE USER',

                                                                                     52,'CRE ROLE',

                                                                                     53,'DROP USER',

                                                                                     54,'DROP ROLE',

                                                                                     55,'SET ROLE',

                                                                                     56,'CRE SCHEMA',

                                                                                     57,'CRE CTLFILE',

                                                                                     58,'ALTER TRACING',

                                                                                     59,'CRE TRIGGER',

                                                                                     60,'ALT TRIGGER',

                                                                                     61,'DRP TRIGGER',

                                                                                     62,'ANALYZE TAB',

                                                                                     63,'ANALYZE IX',

                                                                                     64,'ANALYZE CLUS',

                                                                                     65,'CRE PROFILE',

                                                                                     66,'DRP PROFILE',

                                                                                     67,'ALT PROFILE',

                                                                                     68,'DRP PROC',

                                                                                     69,'DRP PROC',

                                                                                     70,'ALT RESOURCE',

                                                                                     71,'CRE SNPLOG',

                                                                                     72,'ALT SNPLOG',

                                                                                     73,'DROP SNPLOG',

                                                                                     74,'CREATE SNAP',

                                                                                     75,'ALT SNAP',

                                                                                     76,'DROP SNAP',

                                                                                     79,'ALTER ROLE',

                                                                                     79,'ALTER ROLE',

                                                                                     85,'TRUNC TAB',

                                                                                     86,'TRUNC CLUST',

                                                                                     88,'ALT VIEW',

                                                                                     91,'CRE FUNC',

                                                                                     92,'ALT FUNC',

                                                                                     93,'DROP FUNC',

                                                                                     94,'CRE PKG',

                                                                                     95,'ALT PKG',

                                                                                     96,'DROP PKG',

                                                                                     97,'CRE PKG BODY',

                                                                                     98,'ALT PKG BODY',

                                                                                     99,'DRP PKG BODY',

                                                                                     to_char(vs.command)) "Command",

                                                                                    vs.machine "Machine",

                                                                                    vs.osuser "OS User",

                                                                                    lower(vs.status) "Status",

                                                                                    vs.module "Module",

                                                                                    vs.action "Action",

                                                                                    vs.resource_consumer_group,

                                                                                    vs.client_info,

                                                                                    vs.client_identifier

                                                                               from vs

                                                                              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.

                • 5. Re: 4.0.2.15 bug - "merge" statement type is shown as 189
                  Working!

                  Hello Jeff Smith SQLDev PM,

                  thank you for the response.

                   

                  Do you have any instruction on filing SQL Developer bugs in MOS?

                  If I want to file a bug - do I need to buy any license on SQL Developer or anybody could file a bug?

                  • 6. Re: 4.0.2.15 bug - "merge" statement type is shown as 189
                    Jeff Smith Sqldev Pm-Oracle

                    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.

                    • 7. Re: 4.0.2.15 bug - "merge" statement type is shown as 189
                      Gary Graham-Oracle

                      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. 

                      • 8. Re: 4.0.2.15 bug - "merge" statement type is shown as 189
                        rp0428
                        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.

                        • 9. Re: 4.0.2.15 bug - "merge" statement type is shown as 189
                          Gary Graham-Oracle

                          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.