Database usage question

[teser]

Is there some sort of trigger or something that could be written to tell me how many users are using the database each day?

 

[kindus]

You may create logon trigger on the database and populate the username and timestamp in a table. Distict count of the users for a day should give you the users logged in a day as a same user may logged in several times in a day

 

[JHoarty]

I believe this will only work in Oracle 8i. The logon event is not a triggerable event prior to that release. Other triggerable events are STARTUP, SHUTDOWN, CREATE, ALTER, DROP, SERVERERROR, AND LOGOFF. :

CREATE TABLE logon_log (
db_name VARCHAR2(30),
event_time DATE,
by_user VARCHAR2(30)
);

CREATE OR REPLACE TRIGGER log_logons
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO logon_log
(db_name,
event_time,
by_user)
VALUES (sys.database_name,
sysdate,
sys.login_user);
COMMIT;
END;
/
John Hoarty
jhoarty@quickestore.com

http://www.quickestore.com

 

[kindus]

If your database is not oracle8i, then you may set the audit trail on and set auditing for all operation. Then the you can generate the database usage fairly well.

Note : Auditing may be expensive in terms of performance and space. And you need to monitor audit trail and archived it/remove from time to time.