Hi all, I want to write a sql proc 1

[shantanu125]

Hi all,
I want to write a sql procedure that will create a log file which stores login sessions of users.I am using Oracle8i.
The application is in Power Builder (Client-server).This is necessary for tracking how many users generally connect at a time. I am unable to find a solution...
Can anyone help!!!!!!!!!!!!!!
Shantanu

 

[jee]

Instead of doing something in PB, why don't you attack it from the Database side. For instance, you could enable Oracle Auditing and specify that logins are to be logged. If Oracle Auditing doesn't do what you want, you could write a trigger that executes whenever any user connects to the database and log various information about that session.

For more information about Oracle Auditing, see the Oracle 8i Administrator's Guide, chapter 12. For information about using Triggers to audit logins, see the Oracle 8i Application Developer's Guide. If you don't have access to these documents (they are in the Oracle install CD set), you can view them online at the Oracle Technology Network website - you have to register, but access is free. The 8i documentation URL is:

http://otn.oracle.com/docs/products/oracle8i/content.html

 

[shantanu125]

Thanx a lot jee for ur help...
it has given me a direction to proceed...
Thanks again
shantanu

 

[shantanu125]

Hi all,

my problem is nearly solved.. what i did is as follows:
1)I created the audit trail views by connecting as sys and running the script cataudit.sql
2)Then I granted audit permission by giving command:
SQL>audit session by session whenever successful.
This command enabled auditing for sessions only.
3)I write a query to display the required information by selecting columns from view dba_audit_session(created by cataudit.sql).

Everything is fine and it is displaying information of login sessions. But the only thing that still is bugging me is that it is displaying two records for every login session.One for the login window(we have a login window in our application where username and password is verified) and other for connecting to database.I request jee and others to help me in this regard.

Shantanu

 

[carp]

We have a procedure on the database that goes out hourly and (among other things) counts how many sessions are active (SELECT COUNT(*) FROM v$session). This allows us to create usage profiles without incurring the overhead of auditing.

 

[shantanu125]

Hi Carp,

I have to maintain the log-file of sessions not only the current session but earlier sessions also....
I think it is not possible without auditing....
Your suggestion is alright but it can't display OS-username
and maintain history....
shantanu