1 2 Previous Next 15 Replies Latest reply: May 26, 2013 6:49 AM by EdStevens RSS

    continuously run a query in sql prompt

    user-1221
      Hi,

      We want to run a query which shows current logged-in sessions, the query should run continuously from the sql prompt.

      select inst_id NODE, count(*) Current_sessions from GV$SESSION where username IN ('XxX','XXX') group by inst_id order by 1;

      Can it be implemented with some sort of stored procedure which runs in for loop with some time interval?

      Any help would be highly appreciated.

      Thanks in advance!
        • 1. Re: continuously run a query in sql prompt
          Fran
          you can do:

          1) AUDIT users
          or
          2) Trigger "after logon on database"
          • 2. Re: continuously run a query in sql prompt
            sybrand_b
            This has 'VERY BAD IDEA' written all over it.
            Such functionality is already present in AWR and/or statspack, and your little script will use a lot of extra resources, as you will communicate over the interconnect all the time.

            ----------------
            Sybrand Bakker
            Senior Oracle DBA
            • 3. Re: continuously run a query in sql prompt
              EdStevens
              user-1221 wrote:
              Hi,

              We want to run a query which shows current logged-in sessions, the query should run continuously from the sql prompt.

              select inst_id NODE, count(*) Current_sessions from GV$SESSION where username IN ('XxX','XXX') group by inst_id order by 1;

              Can it be implemented with some sort of stored procedure which runs in for loop with some time interval?

              Any help would be highly appreciated.

              Thanks in advance!
              Ask the wrong question, and get bad advice.

              Instead of asking how to implement an ill-conceived solution to some un-stated problem, describe the problem you expect this "solution" to solve. Then you will get much better advice.
              • 4. Re: continuously run a query in sql prompt
                sb92075
                user-1221 wrote:
                Hi,

                We want to run a query which shows current logged-in sessions, the query should run continuously from the sql prompt.

                select inst_id NODE, count(*) Current_sessions from GV$SESSION where username IN ('XxX','XXX') group by inst_id order by 1;

                Can it be implemented with some sort of stored procedure which runs in for loop with some time interval?
                Realize & understand that PL/SQL only ever returns actual results upon COMPLETION!
                so nobody will see anything while your silly procedure is going LOOP by LOOP!
                • 5. Re: continuously run a query in sql prompt
                  user-1221
                  The problem is that my manager want to display total sessions on a monitor, and Iam looking for an efficient way to do this.
                  • 6. Re: continuously run a query in sql prompt
                    sb92075
                    user-1221 wrote:
                    The problem is that my manager want to display total sessions on a monitor, and Iam looking for an efficient way to do this.
                    Create a "magic" icon on your Manager's PC such that it issues single SELECT & displays the results when the PHB clicks the icon.
                    • 7. Re: continuously run a query in sql prompt
                      EdStevens
                      user-1221 wrote:
                      The problem is that my manager want to display total sessions on a monitor, and Iam looking for an efficient way to do this.
                      Sounds like he's got too much time on his hands. Have you asked him the same question we asked you: What problem does this solve and perhaps there is a better -- more efficient -- way to solve it.

                      Beyond that, Quest Spotlight provides a nice, real-time display of all sorts of things going on with the database. The problem with that is that the PHB will get "excited" about anything that isn't green, regardless of the reason.
                      • 8. Re: continuously run a query in sql prompt
                        Billy~Verreynne
                        user-1221 wrote:
                        The problem is that my manager want to display total sessions on a monitor, and Iam looking for an efficient way to do this.
                        Ours is not to reason why when it comes to the PHB...

                        The watch command (Linux/Unix) is useful. Create a shell script that runs SQL*Plus and does the select and display of the current status. Then run that shell script via watch.

                        E.g. run the shell script every 10 seconds
                        watch -n10 /home/phb/bin/total-oracle-sessions.sh
                        Watch uses a curses display - so it does a nice console interface (displaying date and time and script output), with proper full console screen refreshes, etc. Using the -d switch even has watch highlighting the differences between the output of the last script execution and the output of the current execution - e.g. such as highlighting metrics as they are turning over.
                        • 9. Re: continuously run a query in sql prompt
                          John Stegeman
                          May I suggest that your SQL*Plus script look something like this:
                          select 'All is well in the database, except there is something that keeps running a silly SQL script every 10 seconds' from dual;
                          :)
                          • 10. Re: continuously run a query in sql prompt
                            Billy~Verreynne
                            Funny.. :D

                            I often use watch myself. I have my own home rolled bash script/CLI sysdba interface that I use for, well most everything the last 10 years, when it comes to monitoring, checking or whatever dba action. And as the output can be grep'ed, awk'ed, sed'ed, and what not, it fits my CLI world just fine. Allowing me to for example monitor long running processes, PQ processing, and so on, customised as needed, using the watch command.
                            • 11. Re: continuously run a query in sql prompt
                              user-1221
                              I wonder if you do the same with your PHB :)

                              Anyways I created a small batch file and scheduled it with Windows Task Scheduler, hope this will satisfy him.

                              thanks all for replying.
                              • 12. Re: continuously run a query in sql prompt
                                EdStevens
                                user-1221 wrote:
                                I wonder if you do the same with your PHB :)

                                Anyways I created a small batch file and scheduled it with Windows Task Scheduler, hope this will satisfy him.

                                thanks all for replying.
                                But doesn't that make him have to go look for the results? And doesn't that miss the "requirement" for "continuous"?

                                Maybe something like this will satisfy the e-Director of Information and Office Technology
                                --- cmd file
                                :query
                                sqlplus scott/tiger@orcl @run_query
                                goto query
                                
                                
                                --- sqlfile:  run_query.sql
                                set echo off 
                                select inst_id NODE, 
                                       count(*) Current_sessions 
                                from GV$SESSION 
                                where username IN ('XxX','XXX') 
                                group by inst_id 
                                order by 1; 
                                select to_char(sysdate,'dd-Mon-yyyy hh24:mi:ss') CurrentTime from dual;
                                prompt Are we there yet?
                                exit
                                Edited by: EdStevens on May 25, 2013 8:30 AM

                                Edited by: EdStevens on May 25, 2013 8:32 AM

                                Edited by: EdStevens on May 25, 2013 1:35 PM
                                • 13. Re: continuously run a query in sql prompt
                                  user-1221
                                  Thanks Ed.
                                  It was simple doesn't need to be scheduled just keep some timeout value and keep it running.
                                  Great !
                                  • 14. Re: continuously run a query in sql prompt
                                    1011035
                                    Dear Boss,
                                    try this

                                    Create the two shell scripts and call the sql file first script for it exeute and display the result for every 13 sec ...


                                    shells scripts 1
                                    __________________
                                    while true
                                    do
                                    {
                                    echo Display refreshed at `date`
                                    cat /tmp/m1.ses
                                    echo Press \^c to terminate the monitor...!
                                    sleep 13
                                    /home/oracle/scripts/run.sh
                                    clear
                                    }
                                    done


                                    shell scripts
                                    __________________________________
                                    ORACLE_SID=premia
                                    HOME=/home/oracle
                                    LD_LIBRARY_PATH=/oracle10g/dbhome/lib
                                    ORACLE_HOME=/oracle10g/dbhome
                                    export ORACLE_SID ORACLE_HOME
                                    PATH=/usr/sbin:/usr/bin:/usr/ccs/bin:/usr/local/bin:$ORACLE_HOME/bin
                                    export PATH
                                    set `date`
                                    FN=monitor_$2$3
                                    sqlplus -s <<abc
                                    isys/dbadmin
                                    --set head off
                                    --set feed off
                                    set pagesize 200
                                    set linesize 150
                                    spool /tmp/m1.ses
                                    start /home/oracle/scripts/m.sql(put the sql query for whatever you want )
                                    spool off
                                    exit;
                                    abc
                                    cat /tmp/m1.ses >> /tmp/$FN

                                    Thanks
                                    SS.vijaibabu
                                    1 2 Previous Next