1 2 3 Previous Next 31 Replies Latest reply: Apr 2, 2014 1:59 AM by 846411 Go to original post RSS
      • 15. Re: no rows selected
        941949

        hi atif,i tried as you said but same result as  shown above.

        • 16. Re: no rows selected
          Partha Sarathy S

          What is the output of this query?

           

          select A.PROGRAM from v$session a,v$sqltext b

                where a.prev_sql_id=b.sql_id

                and a.username='SCOTT';

          • 17. Re: no rows selected
            941949

            hi,

                the output of the your query is shown below

             

             

             

            SQL> select A.PROGRAM from v$session a,v$sqltext b

              2        where a.prev_sql_id=b.sql_id

              3        and a.username='SCOTT';

             

             

            PROGRAM

            -------------------------------------------------------

            Toad.exe

            Toad.exe

            Toad.exe

            • 18. Re: no rows selected
              Partha Sarathy S

              Then this should give the result what you are looking for.


              select a.username,b.sql_text from v$session a,v$sqltext b
                    where a.prev_sql_id=b.sql_id
                    and a.username='SCOTT'
                 and upper(a.program)='TOAD.EXE';

              • 19. Re: no rows selected
                941949

                the query is not giving the actual query(select * from emp) running by the schema on toad ,the query output is shown below

                 

                 

                 

                SQL> select a.username,b.sql_text from v$session a,v$sqltext b

                  2        where a.prev_sql_id=b.sql_id

                  3        and a.username='SCOTT'

                  4     and upper(a.program)='TOAD.EXE';

                 

                 

                USERNAME

                ------------------------------

                SQL_TEXT

                ----------------------------------------------------------------

                SCOTT

                AND   owner = :  o

                 

                 

                SCOTT

                SYS.ALL_OBJECTS WHERE object_name = :nm AND   object_type = :t

                 

                 

                SCOTT

                Select CREATED, LAST_DDL_TIME, OBJECT_ID, STATUS, TIMESTAMP FROM

                • 20. Re: no rows selected
                  KarK

                  Try the below:

                   

                  select a.username,b.sql_text from v$session a,v$sqltext b

                         where  a.username='SCOTT'

                      and upper(a.program)='TOAD.EXE' and upper(b.sql_text)  like '%SELECT * FROM EMP%';

                  • 21. Re: no rows selected
                    941949

                    hello kark,

                                   the output of your query is swhoing the exact sql query executing by the schema SCOTT on toad and its output  is shown below.

                    USERNAME

                    ------------------------------

                    SQL_TEXT

                    ----------------------------------------------------------------

                    SCOTT

                    nd upper(b.sql_text)  like '%SELECT * FROM EMP%'

                     

                     

                    SCOTT

                    select * from emp

                     

                     

                    But my question is,we dnt know what queries

                    are running by the schema on toad.

                    • 22. Re: no rows selected
                      846411

                      Run Below Query you will get clear Idea of what you are expecting. This should work now as I have tried it just now

                       

                      However you will also see previous sql here and it depends on you which SQL you should be choosing.

                       

                       

                      select a.username,b.sql_text,a.sql_id,a.prev_sql_id,program

                      from v$session a,v$sqltext b

                        where a.sql_id=b.sql_id
                        and a.username='SCOTT'
                        and lower(program) like ('toad.exe');
                      • 23. Re: no rows selected
                        846411

                        Also it will produce output on multiple lines.

                        • 24. Re: no rows selected
                          941949

                          i tried but same result "no rows selected"

                          • 25. Re: no rows selected
                            846411

                            Can you please send the snapshot of what you can see?

                            • 26. Re: no rows selected
                              941949

                              unable to insert snapshot in otn.

                               

                              on toad (schema:scott)

                              ------------------------------

                              select * from salgrade;

                               

                              on sqlplus (schema:/ as sysdba)

                              --------------------------------------------

                               

                              SQL> select a.username,b.sql_text,a.sql_id,a.prev_sql_id,program

                                2  from v$session a,v$sqltext b

                                3     where a.sql_id=b.sql_id

                                4     and a.username='SCOTT'

                                5     and lower(program) like ('toad.exe');

                               

                               

                              no rows selected

                              • 27. Re: no rows selected
                                846411

                                Try below one also. both queries are giving output for me. Not sure what is wrong with your DB.

                                 

                                select a.username,b.sql_text,a.sql_id,a.prev_sql_id,program

                                 

                                from v$session a,v$sqltext b

                                 

                                where a.prev_sql_id=b.sql_id

                                 

                                and a.username='SCOTT'

                                 

                                and lower(program) like ('toad.exe');

                                • 28. Re: no rows selected
                                  941949

                                  i tried your new query also but same problem,can you please send me the sanpshot of your test?

                                  • 29. Re: no rows selected
                                    846411

                                    How come this is not retrieving any data as you have already checked this and you were able to get the data as per your message at