1 2 3 Previous Next 31 Replies Latest reply: Apr 2, 2014 1:59 AM by 846411 RSS

    no rows selected

    941949

      dear all,

                 i need your valuable help,i connected to database through toad as "SCOTT " schema

      and on toad i executed the query "select * from emp".

                             I  logged in as  "/ as sysdba" on sqlplus and i ran the query shown below.

      sql>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 a.program='TOAD';

       

      no rows selected

       

      My question is why the above query result is not showing query executed on toad that is

      "select * from emp". your help appreciated.

       

      thanks and regards .

        • 1. Re: no rows selected
          Partha Sarathy S

          Try this.

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

                where a.prev_sql_id=b.sql_id

                and a.username='SCOTT'

                and UPPER(a.program)='TOAD';

          • 2. Re: no rows selected
            Hemant K Chitale

            I generally see program to be 'toad.exe'  (not 'TOAD') in v$session.  HAvge you queried V$SESSION to identiy the toad sessions properly ?

             

            Also, why do you insist on using prev_sql_id ?

             

            Hemant K Chitale


            • 3. Re: no rows selected
              Fran

              duplicated thread:

              not working on toad

              • 4. Re: no rows selected
                941949

                Hello,

                        Thanks for replying,i tried whatever u said i.e i changed toad to toad.exe and i used join a.sql_id=b.sql_id but no change in the result,kindly help.

                your help appreciated.

                thanks & regards.

                • 5. Re: no rows selected
                  846411

                  Are you logged in to Toad with Scott user as SYSDBA ?

                   

                  Tables v$session and v$sqltext are in SYS Schema.


                  When you login as SYSDBA your schema is SYS. but if you login as normal scott user then you are logged in as scott schema.

                  You check check this by using below command.


                  #: sqlplus scott

                  sql > show user

                  SCOTT


                  #: sqlplus scott as sysdba

                  sql > show user

                  SYS


                  Either you need to grant scott SELECT_CATALOG_ROLE or you will have to connect to SCott as SYSDBA from toad.



                  • 6. Re: no rows selected
                    Hemant K Chitale

                    First,  query V$session standalone to see if you can identify the toad connections.

                     

                    Then see if you can selectively query V$sqlarea for some of the SQL_ID values from V$session.

                     

                    Build your script incrementally.

                     

                    Hemant K Chitale

                     


                    • 7. Re: no rows selected
                      941949

                      hi atif,

                               thanks for replying me,i logged into the toad as scott (normal user not as sysdba) and i logged into the sqlplus as "/ as sysdba".i m running v$session and v$sqltext on sqlplus as user "/ as sysdba".can you please help me in solving my issue?

                      • 8. Re: no rows selected
                        sagargole

                        Are you working on Real Application Clusters. If yes then you can you try gv$ instead of v$ in your query. For more info on gv$ views checkout the following link.

                         

                        About Dynamic Performance Views

                        • 9. Re: no rows selected
                          941949

                          hi, i m  not working on RAC.

                          • 10. Re: no rows selected
                            846411

                            Please try below query and let me know.

                            select * from v$session a,v$sqltext b

                                  where a.prev_sql_id=b.sql_id

                                  and a.username='SCOTT'

                                  and a.program='toad.exe';

                            • 11. Re: no rows selected
                              941949

                              i tried what u said to me,but same result no rwos selected.

                              • 12. Re: no rows selected
                                onkar.nath

                                the program name appears to be Toad.exe and TOAD. Anyways, run below command:

                                 

                                select program,username from v$session where username='SCOTT';

                                 

                                This will give you all the programs being executed by scott and then you can find the exact program name and replace that in your query.

                                 

                                Though, not a very sophisticated approach but it works at least.

                                 

                                Onkar

                                • 13. Re: no rows selected
                                  941949

                                  thanks onkar that is Toad.exe but the problem is that  when i try to find the the query query executing by the schema scott on toad.

                                  on toad:

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

                                  scott running the query "select * from emp" on toad.

                                                

                                  on sqlplus:

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

                                  sql>/ as sysdba

                                  sql>

                                  select * from v$session a,v$sqltext b

                                        where a.prev_sql_id=b.sql_id

                                        and a.username='SCOTT'

                                        and 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

                                  • 14. Re: no rows selected
                                    846411

                                    It depends on Versions to version as shown below.

                                     

                                    toad.exe           Version ::  9.1

                                    Toad.exe          Version :: 11.6

                                     

                                    Best Option is run below query and see what is visible in your terminal. and add the same condition in program.

                                     

                                    select * from v$session a,v$sqltext b

                                          where a.prev_sql_id=b.sql_id

                                          and a.username='SCOTT'

                                     

                                    Regards,

                                    Atif Iqbal

                                    1 2 3 Previous Next