I got the question to create a script that get fired every hour to select the users that are using the application so that they can get an overview on how many users use the application and see if it is increasing or decreasing by a new way of working that they have intorduced. DB is Oracle 10g platform is linux.
I can select users from v$session but I think a trgger has to be created to fire the select query every hour and put the result in a table. It is difficult to store all names per hour in a table but a count can be placed in a simple table.
I need advice on how to deal with this issue the best way. Must it be an external script that is schedlued by crontab or a trigger in the db itself? and some suggestion to setup the script and or trigger.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
If you create a logon and logoff trigger and an audit table to capture when users login and out of the database you can then write sql to analyse this information in any way you want.
See the following for more information:
http://www.dba-oracle.com/
A simple example:
1. create a table AUDIT_TABLE having 2 columns WHEN (type DATE) and USERS (type INTEGER).
2. You create a procedure COUNT_USERS executing the following statement: INSERT INTO AUDIT_TABLE SELECT SYSDATE, COUNT(*) FROM V$SESSION WHERE PROGRAM = '<your_program>'
3. Create a job executing each hour you procedure:
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
(
job => X,
what => 'myjob',
next_date => SYSDATE,
interval => SYSDATE+1/24,
no_parse => FALSE
);
JobNumber := to_char(X);
END;
/
Br,
Pablo
The only problem with using a job to run, say, every hour is that you are possibly missing users that login and logout during that hour, so your analysis will not be complete.
Using triggers to record EVERY login and logout will provide you with the most complete audit data, which you can then analyse any way you like i.e. identify all users logged in at 10:00am each day or anyone who logged in during the day. What is the average connection time for a user etc.
You may also need to consider clearing down the audit table from time to time, but this depends on how many users you have, what their activity is and how long you want to keep the data for.
Hi,
whenever we use any procedure/function call from DBMS_JOB package, we need to have a commit at the end to be on the safer side. Most of the procedures like broken(..),what(..), etc need a commit so that other sessions can get that info. because they will not do a internal commit after the call is over.
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
(
job => X,
what => 'myjob',
next_date => SYSDATE,
interval => SYSDATE+1/24,
no_parse => FALSE
);
JobNumber := to_char(X);
Commit; --> is required here.
END;
/
Business Accounts
Answer for Membership
by: HenkaPosted on 2009-10-21 at 01:02:28ID: 25621553
I would create a job and this job would run every hour.