1 2 3 Previous Next 31 Replies Latest reply on Apr 2, 2014 6:59 AM by Atif Iqbal Go to original post
      • 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
                      Atif Iqbal

                      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
                        Atif Iqbal

                        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
                            Atif Iqbal

                            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
                                Atif Iqbal

                                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
                                    Atif Iqbal

                                    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