1 2 3 4 Previous Next 45 Replies Latest reply on Mar 14, 2005 8:14 AM by 439881 Go to original post
      • 15. Re: How to find what SQL statements are executed by a database user ?
        439881
        You may be right but I have still some errors in the trace file :

        myfile.trc
        Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
        With the Partitioning, OLAP and Oracle Data Mining options
        JServer Release 9.2.0.4.0 - Production
        ORACLE_HOME = /oracle/9i
        System name:     AIX
        Node name:     leo
        Release:     2
        Version:     5
        Machine:     005481AC4C00
        Instance name: LEOBASE_F
        Redo thread mounted by this instance: 1
        Oracle process number: 8
        Unix process pid: 893012, image: oracle@leo (TNS V1-V3)

        *** SESSION ID:(7.10114) 2005-03-10 16:53:23.276
        Skipped error 604 during the execution of SYSDBA.TRIGGER_WEBDFE
        *** 2005-03-10 16:53:23.292
        ksedmp: internal or fatal error
        ORA-00604: wyst±pił bł±d na poziomie 1 rekurencyjnego SQL
        ORA-01031: niewystarczaj±ce uprawnienia
        ORA-06512: przy linia 3

        (errors descriptions are in polish :p)
        • 16. Re: How to find what SQL statements are executed by a database user ?
          Nicolas Gasparotto
          Arthur,

          Perhaps this package is invalid.
          Have you seen the status into dba_objects table ?
          If it's the case you can to recompile SYS invalid object :
          To recompile SYS invalid objects, run (with INTERNAL or SYS) $ORACLE_HOME/rdbms/admin/utlrp.sql

          Oracle highly recommends running this script towards the end of any database migration/upgrade/downgrade.

          You can read metalink notes (225942.1 - Using DBMS_UTILITY.COMPILE_SCHEMA For SYS Objects Fails With ORA-20001 ORA-06512, and 272322.1 - Difference between UTLRP.SQL - UTLIRP.SQL - UTLIP.SQL?) mentionned, utlrp.sql recompiles invalid PLSQL modules (procedures, functions, packages, triggers, types, views, libraries).

          Nicolas.
          • 17. Re: How to find what SQL statements are executed by a database user ?
            439881
            I'm not a DBA so that's looks strange for me :)
            • 18. Re: How to find what SQL statements are executed by a database user ?
              439881
              for information,

              its hard to use the command :
              DBMS_SYSTEM.SET_SQL_TRACE_IN_SESION

              because my application (which connects to the database using user WEBDFE) disconnect/reconnect each time a statement is executed so SERIAL# and PID are changing...

              Cordially,
              Arthur M.
              • 19. Re: How to find what SQL statements are executed by a database user ?
                Nicolas Gasparotto
                Ok, it's already well to have found a workaround.
                Good luck,

                Nicolas.
                • 20. Re: How to find what SQL statements are executed by a database user ?
                  376714
                  Hi again,

                  Did you check the privileges granted to the user?
                  Oracle error 1031 is "insufficient privileges". That means (certainly) that the user have no right to ALTER SESSION.

                  Please check using DBA_SYS_PRIVS that the user actually has the "ALTER SESSION" privilege.

                  SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='<USERNAME_HERE>';
                  -> Should list ALTER SESSION there or
                  -> list CONNECT there.

                  If other roles are granted to the user, please check each role to see if the user inherits the right to alter his session.

                  Regards,

                  Yoann.
                  • 21. Re: How to find what SQL statements are executed by a database user ?
                    376714
                    Geez, sorry.
                    CONNECT role can't be listed in DBA_SYS_PRIVS. TO know if the user has got CONNECT, use the same query, but querying the object DBA_ROLE_PRIVS as the data source.

                    Yoann.
                    • 22. Re: How to find what SQL statements are executed by a database user ?
                      439881
                      I see that the status of trigger object is VALID
                      in dba_objects table...
                      • 23. Re: How to find what SQL statements are executed by a database user ?
                        376714
                        Arthur M,

                        The Trigger structure can be valid, that don't mean the user has the right to execute "ALTER SESSION".

                        You can also check that via:

                        SQL> CONNECT user/pass@service
                        SQL> ALTER SESSION SET SQL_TRACE=TRUE;

                        If you get a "Insufficient privileges" error, then just
                        SQL> GRANT ALTER SESSION TO user;

                        Regards,

                        Yoann.
                        • 24. Re: How to find what SQL statements are executed by a database user ?
                          439881
                          the user has the right to execute this because when I connect manually with toad and using WEBDFE user its work with any problem.

                          In this case, I havea good trace file but not with using the trigger solution.

                          You can see :

                          /leo/formation/dbdc/oracle/udump/leobase_f_ora_712916.trc
                          Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
                          With the Partitioning, OLAP and Oracle Data Mining options
                          JServer Release 9.2.0.4.0 - Production
                          ORACLE_HOME = /oracle/9i
                          System name: AIX
                          Node name: leo
                          Release: 2
                          Version: 5
                          Machine: 005481AC4C00
                          Instance name: LEOBASE_F
                          Redo thread mounted by this instance: 1
                          Oracle process number: 8
                          Unix process pid: 712916, image: oracle@leo (TNS V1-V3)

                          *** 2005-03-11 09:40:29.256
                          *** SESSION ID:(8.9377) 2005-03-11 09:40:29.237
                          APPNAME mod='TOAD 8.0.0.47' mh=363997730 act='' ah=4029777240
                          =====================
                          PARSING IN CURSOR #3 len=35 dep=0 uid=26 oct=42 lid=26 tim=1084502372320467 hv=4050693888 ad='2be26c58'
                          alter session set sql_trace = true
                          END OF STMT
                          EXEC #3:c=0,e=145,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1084502372301827
                          *** 2005-03-11 09:43:25.160
                          =====================
                          PARSE ERROR #2:len=38 dep=0 uid=26 oct=42 lid=26 tim=1084502544101637 err=911
                          alter session set sql_trace = false;
                          =====================
                          PARSING IN CURSOR #3 len=36 dep=0 uid=26 oct=42 lid=26 tim=1084502544114117 hv=2919611770 ad='2be27468'
                          alter session set sql_trace = false
                          END OF STMT
                          PARSE #3:c=0,e=343,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1084502544114113
                          EXEC #3:c=0,e=114,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1084502544114314
                          • 25. Re: How to find what SQL statements are executed by a database user ?
                            439881
                            For information,

                            user WEBDFE has the same privilege than sysdba. it was make specially for this case and will be destroy as soon as i get a good trace file :)
                            • 26. Re: How to find what SQL statements are executed by a database user ?
                              376714
                              Can't see what is wrong.

                              I just tested it on a database, and I successfully get a session trace file:

                              Dump file ----*
                              Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
                              With the Partitioning, OLAP and Oracle Data Mining options
                              JServer Release 9.2.0.4.0 - Production
                              ORACLE_HOME = ----*
                              System name: HP-UX
                              Node name: ----*
                              Release: B.11.11
                              Version: U
                              Machine: 9000/800
                              Instance name: ----*
                              Redo thread mounted by this instance: 1
                              Oracle process number: 13
                              Unix process pid: 8384, image: oracle@*----* (TNS V1-V3)

                              *** SESSION ID:(10.11) 2005-03-11 10:44:28.600
                              APPNAME mod='sqlplus@*----* (TNS V1-V3)' mh=0 act='' ah=0
                              =====================
                              PARSING IN CURSOR #3 len=34 dep=2 uid=0 oct=42 lid=0 tim=5070423408164 hv=1068005395 ad='4821f630'
                              alter session set sql_trace = true
                              END OF STMT
                              EXEC #3:c=0,e=78,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=5070423390251
                              =====================
                              PARSING IN CURSOR #1 len=100 dep=1 uid=0 oct=47 lid=0 tim=5070423408465 hv=1642286371 ad='48222738'
                              begin
                              if user = 'TOAD' THEN
                              execute immediate 'alter session set sql_trace = true';
                              end if;
                              end;
                              END OF STMT



                              I just have a TOAD user so I made a little test with it. Works fine.

                              There's a mess somwhere I just seen not to be able to see.

                              • 27. Re: How to find what SQL statements are executed by a database user ?
                                439881
                                yes manually using toad there is no problem, but when the trigger is trying to activate the trace it doesnt work well..... grrr :)
                                • 28. Re: How to find what SQL statements are executed by a database user ?
                                  376714
                                  In the sample I included, i did not use the TOAD GUI. I juste logged in to the database using sqlplus, account TOAD. the trigger set the trace on :)

                                  • 29. Re: How to find what SQL statements are executed by a database user ?
                                    439881
                                    Are you sure because you executed the command 'alter session set sql_trace = true' ?!