0

Please find the below query:

select count(*) from v$session;

This has to be run on the database every 5 minutes. What is the right way to achieve this?

The query has to be run every 5 minutes on the same sql*plus screen. Instead of pressing "/" every minute, I wanted to understand if there is a way.

Database/Server details:

SQL> select * from v$version;
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production

SQL> !uname -a
HP-UX ap12121 B.11.11 U 9000/800 ap12121 unlimited-user license
0

2 Answers 2

1

I don't know of a way to do what you're asking from within sqlplus, but you can combine a shell script with the "watch" command to achieve something close to what you're looking for:

Create a simple shell script (sessions.sh) to call sqlplus and run the query:

#!/bin/sh
sqlplus username/password << EOF
select count(*) from v$session;
EOF

Then use the watch command (or HP-UX equivalent, assuming there is one) to execute the script:

watch -n 300 sessions.sh

Or use cron to schedule the script to run every 5 minutes and direct the output to a log file:

crontab:
0/5 * * * * sessions.sh >> sessions.log

then tail the log file:

tail -f sessions.log
0
2

How about using a PL/Sql block:

DECLARE
    v_sessions NUMBER;
BEGIN
    WHILE true LOOP
        SELECT COUNT (*) INTO v_sessions FROM v$session;
        DBMS_OUTPUT.PUT_LINE ('Sessions: ' || v_sessions);
        DBMS_SESSION.SLEEP (300); -- 5 minutes
    END LOOP;
END;

If it really is 8i, use DBMS_LOCK.SLEEP (300) instead.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.