This discussion is archived
1 2 Previous Next 15 Replies Latest reply: May 26, 2013 4:49 AM by EdStevens RSS

continuously run a query in sql prompt

user-1221 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    you can do:

    1) AUDIT users
    or
    2) Trigger "after logon on database"
  • 2. Re: continuously run a query in sql prompt
    sybrand_b Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points