Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question.

Asked: October 29, 2001 - 10:20 pm UTC

Last updated: August 30, 2022 - 1:29 am UTC

Version: 7,8x

Viewed 100K+ times! This question is

You Asked

Hi Tom,

I'm impressed by the quality of the answers you provide in this site. It is really a boon to us Oracle users...

I wanted your help in the following :

I would like to track the Logon & logout timings of each user on to a flat file (.Txt). How do I do that ?
Since I'm using Forms, Kindly let me know what would be the best possible way I could achieve this.

* Next, to ask u a little different question, can I prevent the unauthorised distribution of my application that I develop in Forms ? The client may always create a copy of my .FMX files and can re-sell my software. I do not want that to happen. Kindy help.



and Tom said...

You would enable auditing in the database and audit connects. This will record the login/logout activity into the audit trail. The audit trail is either a database table OR the system event log (OS specific as to where that is). If you are using the database table, creating a flat file is trivial (simple query and spool from sqlplus).

There is no realistic way to prevent your customers from giving your software to others.


  (112 ratings)

Is this answer out of date? If it is, please let us know via a Comment


Trigger auditing

Andre Whittick Nasser, October 30, 2001 - 8:50 am UTC

As an alternative to auditing logons/logoffs, you might as well create LOGON and LOGOFF triggers that populate a regular history table. I myself prefer it. It's far simpler:

create table log_trail (name varchar2(30), time date, action varchar2(10));

create or replace trigger tr_logon
after logon on database
insert into log_trail values (user, sysdate, 'LOGON');
end tr_logon;

create or replace trigger tr_logoff
before logoff on database
insert into log_trail values (user, sysdate, 'LOGOFF');
end tr_logon;


- Beware of logon triggers. If they are not working, you may not logon to Oracle.

- You must have the CREATE (ANY) TRIGGER and ADMINISTER DATABASE TRIGGER privileges to implement DATABASE triggers.

Tom Kyte
October 30, 2001 - 10:04 am UTC

How is that far easier then a single command "audit connect"

I hate writing code when a simple command would do the trick. Use whats builtin to the system, don't reinvent the wheel. Thats my mantra...

Distribution of application

George Spears, October 30, 2001 - 10:10 am UTC

Just a quick comment. As Tom said, while you cannot prevent the application from being copied, you can make it very difficult to 'get working'. As an example, your application could check the SID of the database, and only work against a certain database. Other things to check could include: The IP range of the client machines, the domain of the database, something pulled out of the client registry, etc...

How to handle siuation when one user have multiple session

Ivan Korac, October 30, 2001 - 10:30 am UTC

The problem is handling of application-users wich could be connected from diferent places and through multiple sessions. It is possible for example with:
AFTER LOGON ON application.schema
machinename VARCHAR2(64);
osuserid VARCHAR2(30);
session_id_part1 NUMBER;
session_id_part2 NUMBER;
SELECT osuser, machine, sid, serial#
FROM v$session WHERE audsid = userenv( 'sessionid' );
OPEN c1;
FETCH c1 INTO osuserid, machinename, session_id_part1, session_id_part2;
INSERT INTO LOGON_table(osuser, machine, logon_time, session_id_1, session_id_2)
VALUES ( osuserid, machinename, sysdate,session_id_part1, session_id_part2);
- similar is needed for log off, and now You have all elements for corect counting of spent time of any user

Tom Kyte
October 30, 2001 - 12:49 pm UTC

I fail to see how this collects any additional information beyond what is already collected by audit connect?? See the comment a couple down from here where I dump dba_audit_trail. Its all there already.

Trigger Auditing

Lester Burnham, October 30, 2001 - 10:31 am UTC

Using a database trigger just adds two more objects (the trigger and the table) into the database. Also, it won't work for the user's 7.x databases. It won't track unsuccessful logon attempts (which may or may not be important). Andre rightly points out that the trigger may be able to prevent logons if it is not working...

Yse Usful

Mohammed Al-moayed, October 30, 2001 - 12:18 pm UTC

Yes, Tom You are right audit connect is the easiest way,
this will help me in the My new security Application but
could you please how to find audit_trail table I tried but I didn't find it ,or you mean we have to create it .

For the trigger above I think he created it by hime self it not oracel system table .

Tom Kyte
October 30, 2001 - 12:48 pm UTC

The main audit trail table is DBA_AUDIT_TRAIL (see below for an example).

</code> <code>
for all of the details.


Andre Whittick Nasser, October 30, 2001 - 12:35 pm UTC

First, I have worked a lot with Oracle auditing in very controlled environments, and managing audit trails is not definately something you would like to do on a sunday morning.

If you are auditing myriads of events, they get interleaved in the views and it takes little time before you burn out brains out, believe me.

Auditing is an art. Carefully defining its scope may be one of the most difficult things to do against the database.

Rule of thumb: Keep the audit trail as simple as possible. Focus on the events your organization procedures have defined as relevant.

If you think LOGON/LOGOFF event are part of your organization's auditing scope, then go ahead...

Creating these triggers and the table adds too little complexity to your environment, and you can customize the code your own way.

Yes, indeed, you cannot audit successful/not successful events, but you have to consider that you can also improve your code in other directions by programming additional features, like the other example above with multiple sessions.

As to the mantra, sorry but, despite being an Oracle afficionado, there are a lot of features that are terribly implemented in the DBMS and you cannot just think they are always better than some eventual code tailored to your needs.

There is a lot of extremely unfriendly stuff in Oracle, and I like to arrange my own environment.

Tom Kyte
October 30, 2001 - 12:46 pm UTC

we are talking about auditing logons and logouts.

You are free to write as much code as you like (you've posted it in the past). I, I like to use the built in feature.

Look at the question, "how to audit logins, logouts". No art, no magic, no muss, no fuss.

I too have some experience using Oracle stuff. I know what its about, believe me.

Rule of thumb: keep everything as simple as possible. Here the correct, proper and accurate answer in my opinion is "audit connect".

The other example -- that one makes no sense to me (as every bit and piece of data they collect -- Oracle ALREADY collects!!!)

Lets see, they get:

SELECT osuser, machine, sid, serial#
FROM v$session WHERE audsid = userenv( 'sessionid' );

(never mind the trigger should be ONE line long with an "insert into select from" for a moment). Well, when I audit connects I get:

tkyte@TKYTE816> @printtbl8 'select * from dba_audit_trail where rownum = 1'
OS_USERNAME : Thomas?Kyte


TIMESTAMP : 08-may-2001 19:19:29
ACTION : 102
LOGOFF_TIME : 08-may-2001 19:19:30
COMMENT_TEXT : Authenticated by: DATABASE


As to the mantra -- we'll just have to agree to totally, 100% disagree here. Not about how features are implemented but about how to properly implemented the solution to the question posed here.

The easy way: audit connect.
The hard way: write code.
The end result from both: the same.
Arranging your own environment is fine, great (if you have my book -- you can see I do the same, look at the section on UTL_SMTP, UTL_HTTP, UTL_TCP for exmaple). When it is needed, I'm all for it. I just happen to very much disagree with your analysis of auditing in this case.

Versions of database specified

Lester Burnham, October 30, 2001 - 1:15 pm UTC

> The end result from both: the same.

In 8i and up - but doesn't the original poster indicate that they have version *7* and version 8x? (Maybe the version 8x means 8i maybe it means 8.0.5 - doesn't say.) Seems to me that the question could use a bit more detail. For example, maybe they just want to track logon/logoff times of their Forms application only and not logon/logoff for all users of the database in general. In any case, the trigger option won't work (unless this feature snuck back into 7.x and 8.0.x) for all of the poster's databases, so the only solution that meets the needs of the original poster is to use database auditing as Tom is suggesting. I know I am being picky, but...

auditing using audit connect

shamim ahmad, October 31, 2001 - 12:21 am UTC

I tried both options
audit connect is working fine for auditing logon and logoff of users. but how to know through which application user logged in at the same time.
There is one problem using logon and logoff triggers when a user have multiple instances, the session id that is captured during logon trigger is diferent from the session id that i am catching during logoff trigger, can u tell me why it is so.

Tom Kyte
October 31, 2001 - 7:40 am UTC

You cannot know what application users are connecting from. The information is v$session is suspect at best (just copy sqlplus.exe to blah.exe and see what happens -- you cannot rely on that information in any way, shape or form.

In any case, I do not see the same behavior you do with a trigger like this:

create table audit_trail
select sysdate x$timestamp, rpad('*',20,'*') x$action, v.*
from v$session v
where 1=0

create or replace trigger logon_trigger
after logon on database
insert into audit_trail
select sysdate, 'LOGON', v.*
from v$session v
where sid = ( select sid from v$mystat where rownum = 1 );

create or replace trigger logoff_trigger
before logoff on database
insert into audit_trail
select sysdate, 'LOGOFF', v.*
from v$session v
where sid = ( select sid from v$mystat where rownum = 1 );

I get the same sid/serial# regardless. The audsid (sessionid) is consistent as well.

Do not miss flexibility

Ivan Korac, October 31, 2001 - 4:44 am UTC

I am not against your solution of course, but I would like to highlight power hidden in logon/logoff trigger solution and additional flexibility.
Our usage include loading of some additional fields (from user interface) in logon_table for e.g. country code and language code which brings very powerfully setup of cubs needed for user reports.

Tom Kyte
October 31, 2001 - 7:53 am UTC

How could your user interface supply a logon trigger with additional information -- its a chicken and egg problem.

I do not and have not suggested that logon/logoff triggers aren't flexible, aren't useful. I have and will continue to suggest that if you want to capture the logon and logout timings of each user -- the answer is "audit". The answer is not "write code".

If you have definitive requirements above and beyond that -- a logon trigger may become something to consider. Given this question was "how to get logon/logout in version 7,8x", the answer remains the same -- audit.

How can I get audit data after enable the audit connect ?

A Reader, October 22, 2002 - 2:31 pm UTC

Hi Tom,
I tried a "after logon/before logoff" trigger method. It works fine when every user logoff normally. However, when a user logoffs abnormally, eg., logoffing by clicking the 'X' on SQLPLUSW interface, the logoff information, say, the time (date) will not be recorded by the trigger. When the user use "EXIT", all work as expected. So I tried the "audit connect" method.
When I was trying to use the "audit connect" method to audit user logon and logoff so that I know how a user had logoned to the database. After reading the document on this topic, I still have problem in getting the audit results. I guess I am missing somthing. I did the following:
1. Logon to the database as sys and issue command: audit connect;
2. connect to the database as any users;
3. select from dba_audit_trail (as sys user or any dba_user);

No rows returned. I even run the CATAUDIT.SQL. What else do I need to do to use the "audit connect" correctly? Can you please help me with the steps I need to go through to audit the connection (logon/logoff)?

Thank you in advance.

Tom Kyte
October 22, 2002 - 3:47 pm UTC


in the init.ora perhaps?

Ignore the above question.

A Reader, October 22, 2002 - 3:28 pm UTC

Please ignore the above question (How can I get audit data after enable the audit connect ?). Metalink has the detail steps (Note:99786.1).
Thank you again.

audit connect & snapshots

Will, December 16, 2002 - 11:07 am UTC

I am finding that when a remote database connects to my database (via dblink) to refresh (complete) a snapshot, the audit trail shows LOGOFF BY CLEANUP for these sessions. Both DB's are 8.0.5.

Is it normal for these sessions to not explicitly disconnect?

Does it make any difference?

Thanks in advance.

Tom Kyte
December 16, 2002 - 12:39 pm UTC

I do not see any problem reports filed against this, so I doubt that is is "a problem". Normal - no, I don't think so. When the job closes the database link, it should close it with a normal logoff.

As it isn't causing any issues right now tho... I would ignore it.

login failures

shihantha, April 10, 2003 - 7:37 am UTC

on the subject of auditing, I would like to know if there is a way to trap login failures. i.e have an audit of failed attempts to log on to the database (invalid username or password)

Tom Kyte
April 10, 2003 - 8:17 am UTC

audit connect whenever NOT successful;


shihantha, April 11, 2003 - 1:07 am UTC

thanks a lot. just what I needed.

I would also like to know if there is any way of setting up an environment where logon failures are recorded automatically... instead of having to run the 'audit connect whenever not successful' statement each time the database is restarted



shihantha, April 11, 2003 - 1:59 am UTC

please ignore the earlier question.

it remains persistent untill we explicitly say NOAUDIT

sorry... must be a slow day for me.. hee hee...


Steven Cooper, April 17, 2003 - 9:26 am UTC


I was under the impression that you can't commit in triggers?

Enter password: ********
system@Dev9i> create table user_logon
2 (
3 user_name varchar2(30),
4 last_logon date,
5 constraint user_logon_pk primary key (user_name)
6 )
7 organization index
8 /

Table created.

system@Dev9i> create or replace trigger logon_trigger
2 after logon on database
3 begin
4 insert into user_logon values ('test',sysdate);
5 commit;
6 end;
7 /

Trigger created.

Then tried to logon :

ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at line 3


Tom Kyte
April 17, 2003 - 11:03 am UTC

you can if you add pragma autonomous_transaction (but beware, if you have 815 and commit in an on logon trigger with an atrans -- you'll be calling support right afterwards -- its OK in all current releases)


Steven Cooper, April 17, 2003 - 11:23 am UTC


Thanks, that works.


Some question about Auditing Connect

Riaz Shahid, May 03, 2003 - 8:52 am UTC

Hello Tom !

I have requirement to have the log of logons & logouts if:

(1) The time is >= 9:00 PM
(2) The day is Sunday.

Should i do it using auditing connect for that (if yes then how ) or just use the logon and logoff triggers ? In my opinion, triggers will be better.

Note that procedures at my remote client side are like :

select 1 into a from dual@DBLink_to_my_database;
if a=1 then
insert into mytab@DBLink_to_my_database
select ....
from LocalTable
where ,.....

update mytab@DBLink_to_my_database
set ....

etc etc.

My understanding is that the statement:

select 1 into a from dual@DBLink_to_my_database

will be considered as two transactions (i.e; two records in my audit table): one for logon and one for logout. And for each statement using insert/select/update, there will be two rows in audit table. Am i true ?

Plz Advise

Riaz Shahid

How to format error stack

Igor, May 15, 2003 - 7:30 am UTC


I have following trigger:

create or replace trigger sa2_logon_trigger
after logon on database --when (user='SA2')
iworkgroup varchar2( 150 );
if (user = 'SA2' ) then
select work_group
into iworkgroup
from table_employee
where objid =
( select objid from table_user where upper( login_name ) = user );
exception when no_data_found then null;

if ( iworkgroup = 'Concert' ) then
raise_application_error ( -20001, 'Login denied.' );
end if;
end if;

When specific user connects with sqlplus, gets:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Login denied.
ORA-06512: at line 17

It's nothing wrong with that but unfortunately Clarify client shows just first line and it doesn't tell too much about real error:
"ORA-00604: error occurred at recursive SQL level 1"

I have no idea how to show just specific error message?


Tom Kyte
May 15, 2003 - 9:40 am UTC

that would be an issue to take up with "clarify". we cannot make them show the entire error message if they don't want to.

the error is the error, you cannot change it.

How to map the pair of logon and logoff?

Minnie, May 28, 2003 - 2:47 pm UTC

Hi tom,
in your solution,

create table audit_trail
select sysdate x$timestamp, rpad('*',20,'*') x$action, v.*
from v$session v
where 1=0
create or replace trigger logon_trigger
after logon on database
insert into audit_trail
select sysdate, 'LOGON', v.*
from v$session v
where sid = ( select sid from v$mystat where rownum = 1 );
create or replace trigger logoff_trigger
before logoff on database
insert into audit_trail
select sysdate, 'LOGOFF', v.*
from v$session v
where sid = ( select sid from v$mystat where rownum = 1 );
quote end.

one can find the pair of logon and logoff by using sid and serial#. But it's not as easy as a select statement to review the information.
First of all, I want to claim that I'd like to using user-defined-trigger instead of audit connect because I need to combine restrict logon time frame and restrict users.
I'm thinking the following solution.
create table audit_trail
select sysdate x$timestamp, rpad('*',20,'*') x$action, v.*, sysdate x$logoff_time
from v$session v
where 1=0
create or replace trigger logon_trigger
after logon on database
insert into audit_trail
select sysdate, 'LOGON', v.*, null
from v$session v
where sid = ( select sid from v$mystat where rownum = 1 );
create or replace trigger logoff_trigger
before logoff on database
update audit_trail set x$logoff_time=sysdate
where sid = ( select sid from v$mystat where rownum=1 );
the audit_trail table looks like pretty similiar with sys.aud$ now.
but logoff trigger will update all the rows having the same sid. How to solve it?

Thank you very much,

Tom Kyte
May 28, 2003 - 7:47 pm UTC

why not....

audit connects

have a trigger to refuse connects except when you want them.

tastes great, less filling....

TOM very write

Sikandar Hayat, May 28, 2003 - 11:28 pm UTC

I am 100% agreed with Mr. TOM that we should use the functions if we have with the DB. For example if we have count(*) function then why we should code for it. We can utilize that time to some other activities. I just like to code if I don't find any solution from the DB which is very rare and happens when I don't have enough knowledge about that functionality.

For example earlier i was not using analytical functions but now I came to know from the discussion of Mr. TOM and his book that these are very useful and really these are very useful.

Now if someone is using 7,8 then to get extra features they should migrate to the new one. If someone is still using 7 then I don't think this is the fault of Oracle Co.

"Very Right" in the above thread

Sikandar Hayat, May 28, 2003 - 11:32 pm UTC

For Mr. Sikander Hayat

Riaz Shahid, May 29, 2003 - 4:42 am UTC

Dear Sikandar !


I wanna know from where u found the book of Tom ? Since its not available in pakistan (acccording to my knowledge)


Ya not available in PK

Sikandar Hayat, May 29, 2003 - 12:21 pm UTC

You are right that the book is not available in Pakistan. I had checked from Islamabad, Lahore and Karachi. Then I purchased it from India which is Indian reprint but of good quality.

Preventing multiple connections?

A reader, October 06, 2003 - 4:11 pm UTC

Being able to use the audit trail to keep track of user connections is wonderful. But what about when we're using a J2EE/Web Cache installation of iAS? The application server can create multiple connections for the user. Is there a way of setting the audit trail to keep only one entry, and not twelve?

Tom Kyte
October 06, 2003 - 6:06 pm UTC

nope, you have 12 connections to audit as far as the database is concerned. it has no way to distinguish between them.

Re: Preventing multiple connections?

A reader, October 06, 2003 - 7:00 pm UTC

In that case then, perhaps a trigger would be the better alternative? True, it asks us to code something which the database already can do, but it would also allow us to do only one insert, instead of twelve - say, if there is already a connection log within the hour. It would prevent the table from getting too big?

Tom Kyte
October 06, 2003 - 8:38 pm UTC

tables cannot be too big

database where born to insert into (and join)

it is what they do best.

why have auditing if you just say "you know, if you are already logged on -- thats good enough -- i don't need to know you also logged in from terminal "badguy" as well"


Sudhir, October 06, 2003 - 10:38 pm UTC

logoff trigger is only way to get v$pq_tqstat for the session which did pq work thru an application, correct? unless you are running your parallel query in sqlplus and have access after main job. Please clarify.


Tom Kyte
October 07, 2003 - 7:53 am UTC

why would sqlplus be any different then any other client?

Can the "after logon on schema" trigger be used to track schema logins?

ht, October 09, 2003 - 7:47 pm UTC

I would like to track when a user connects to a schema-based application. Since my db has multiple schemas, how would I use the "alter logon on schema" trigger to capture that userA connected to appA, appB, and appc?

It seems that the "after logon on schema" trigger is meant to track schema owners. How would I track non-schema owners that have been granted private synonyms to access the app?


Tom Kyte
October 09, 2003 - 8:02 pm UTC

don't know what you mean exactly by a "schema based application"??

If you want this logon trigger to fire for all users, it would be an after logon on database -- not schema -- trigger.

Jamil, October 10, 2003 - 7:32 am UTC

In Pakistan you can get this book from vengaurd Karachi.

How would an "after logon" trigger be used to track app logons?

ht, October 10, 2003 - 11:11 am UTC

If I have 10 schemas in 1 instance and want to track when a non-schema owner user logons to the schema, can that be done with an after logon schema trigger?

Tom Kyte
October 10, 2003 - 11:18 am UTC

it doesn't work like that.

I don't know what you mean by "when a non-schema owner user logons to the schema". that is a contradictory statement.

they either logged into the schema using that schemas credentials....
or they did not....

maybe if you give us a concrete example of what you mean we can sort it out

Will have to create triggers when procs are executed.

ht, October 10, 2003 - 11:50 am UTC

Thanks for the clarification. In going through the doc, it seems that I need to modify my stored procs to track an application's usage instead of implementing triggers.

It seems that triggers cannot be used to track the stored procs a user has executed in an app. Instead, I'll modify the stored proc to write the user's name and sysdate to a table.

I had thought that "after logon on schema" triggers provided a way to track when a user accessed a certain schema.

Tom Kyte
October 10, 2003 - 12:29 pm UTC

auditing will track the procedures (PACKAGES please use PACKAGES instead of procedures) a user executed as well -- very very easy to turn on. no code.

Can I easily implement packages using PSP

ht, October 10, 2003 - 12:38 pm UTC

Thanks, I'll try packages but is there a way to keep my PSP env (loadpsp ...) if I use packages. In other words, would I still be able to load my psps if they're wrapped by packages?

Tom Kyte
October 10, 2003 - 12:54 pm UTC

psp's are ok as procedures since they are somewhat independent of eachother (a psp never calls another psp really)

1 insert calls 3 psps

ht, October 10, 2003 - 1:21 pm UTC

Thanks again for the info. Would it be better to use packages if I call 4 psps to insert an entity? For example, the first psp displays a form that accepts the data to be entered and calls a second psp that displays session info in a header. The third psp parses the array entered and calls the fourth psp that does the actual insert.

If I should be using packages, is there a way to implement without abandoning the "loadpsp ..." method I've been using. It's nice to see the html and pl/sql commands in 1 readable file. If the benefits outweigh this development method, I will gladly move to packages.

Your advice is appreciated.


Tom Kyte
October 10, 2003 - 4:09 pm UTC

they do not really call eachother -- the mod_plsql module calls them one after the other -- they are not dependent on eachother at all in the database.

How to turn on auditing for a schema's objects?

ht, October 10, 2003 - 10:45 pm UTC

I took your advice and enabled auditing. 2 questions:

1. Is there a way to turn on auditing for a specific schema's objects? I'm getting way too much info by tracking "audit execute procedure by access" and would like to only see when my schema is accessed.
2. How would one prevent the %AUD% tables from fragmenting my system tablespace? Can I redefine the tablespace for the aud$ table to a non-system tablespace or is there another recommended method to handle this issue?


Tom Kyte
October 11, 2003 - 10:19 am UTC

1) you can enable auditing on specific schema objects. Have you browsed the audit command syntax fully in the sql reference and read the relevant chapter in the admin guide?

2) you can contact support for guidance in that area.

Schema auditing not possible.

ht, October 11, 2003 - 7:22 pm UTC

I did search your site, Metalink, and the doc and posed the question because it didn't seem that one can turn on auditing for a schema without explicitly auditing each object.
I will contact support about figuring out a way to get the *aud* tables off of the system tbsp.

Tom Kyte
October 11, 2003 - 7:48 pm UTC

yup, that is what:

you can enable auditing on specific schema objects

meant -- you can audit at the object level.

How to track logon & logout

Jamil, October 12, 2003 - 3:14 am UTC

Hi Tom
When I try to access this topic
I am getting this error and the topic is unreadable  and it give this error message <code>
would you please help me how to get this topic display Properly

Best regards
Jamil Shaibani

Tom Kyte
October 12, 2003 - 9:07 am UTC

Multiple Logon Triggers

A reader, December 10, 2003 - 5:33 pm UTC

Hi Tom,

If there are multiple logon triggers in the database, how do Oracle decide the execution order?

For example, one logon trigger sets sql_trace to false, and another one sets it to true. What will be the result?


Tom Kyte
December 10, 2003 - 5:55 pm UTC

triggers fire in a decidedly undecided fashion

the behavior is documented as "undefined order of firing" for all triggers of the same type.

so, if you need an order, better create a SINGLE trigger.

Not sure I like Audit Connect

eric givler, December 10, 2003 - 6:30 pm UTC

We tried using Audit Connect and OEM's NUMEROUS logons to the database essentially filled up the sys.aud$ table and rendered an inability to connect to the instance. Of course this took a few weeks, but there must not have been any oem job that was setup to detect this situation. At that point, we changed the datafiles, and turned it off in favor of what we really wanted to do which was just know the last time a user logged into the system.

That and we were too lazy to figure out what should have been done with it. (oops)

Tom Kyte
December 10, 2003 - 6:44 pm UTC

that is sort of like saying

"not sure i like insert, it gives the ability for our users to fill up tables"

isn't it?

record all activites done by DBA role

Junior, February 06, 2004 - 9:30 am UTC


"audit connect" only able to get log on/log off time. However, my requriement is " record all the activities done by DBA role, and ignore any other users." e.g. If users in DBA role (inclued sys, and system), when they update/delete/modify table or tablespace .... whatever they did to the database must be record in details. Can Oracle support that? Thank you for your answer.

Tom Kyte
February 07, 2004 - 12:36 pm UTC

yup, in 9i we audit sys and sysdba (not before).

You would use the normal audit command (which has lots of documented variations) to audit these things

AND make sure to use the OS audit trail (for hopefully "obvious" reasons....) not the DB audit trail :)

logon and logoff numbers confuse me

jianhui, February 23, 2004 - 4:24 pm UTC

I turn the AUDIT SESSION on and here is what i get after running a few days over the weekend (i updated the actual account names for my privacy). Acct5 is the application owner account and used by the middle tier. 
1)Could you explain what LOGOFF, LOGOFF BY CLEAN UP are?
2)Why is LOGON much smaller than LOGOFF? Isnt LOGOFF supposed be smaller than LOGON, otherwise where does LOGOFF come from if there is no corresponding LOGON?
3)It seems Oracle 9i document isnt clear about this, i checked REFERENCE and DBA Guide, no detail information was found with regard to ACTION_NAME. If you have a link for the apporiate document, could you please post here.

SQL> select ACTION_NAME, count(*) from dba_audit_session
  2  where username='ACCT05'
  3  group by action_name;

ACTION_NAME                   COUNT(*)
--------------------------- ----------
LOGOFF                          878651
LOGOFF BY CLEANUP                  474
LOGON                               13 

Tom Kyte
February 23, 2004 - 5:16 pm UTC

the logoff by cleanup indicates there was an improper exit from the database.
Same behavior exhibited with a ROLLBACK WORK RELEASE, or with the complete
ommission of a COMMIT or ROLLBACK upon "exit".

Now, for the logon/logoff -- can you tell me more about your middle tier. does it use a connection pool, if so -- whose, and do you notice that there are entries in v$process that have no match in v$session (indicating the middle tier is connecting ONCE and keeping the circut open -- but auth/deauth over and over for each request)

about middle tier auth/deauth

Jianhui, February 24, 2004 - 12:01 pm UTC

Quote "(indicating the middle tier
is connecting ONCE and keeping the circut open -- but auth/deauth over and over
for each request) "

It's hard to understand. Doesn't auth or deauth close the session? If the session is closed, how come there is a v$process entry(dedicated server)? If there is not SERVER process, how to keep the circuit open?

Thanks Tom!

Tom Kyte
February 24, 2004 - 12:58 pm UTC

don't confuse a connection (physical circut) with a session.

it is a many to many relationship!

A session can use many processes (shared server)
A single process can have many (0, 1, or more) sessions using it.

a true "logon" is a two step

a) connect (physical circut)
b) create session (can be done over and over with the same connection, either one after the other or many at a time)

even more confused

jianhui, February 24, 2004 - 6:33 pm UTC

A session can use many processes (shared server)
A single process can have many (0, 1, or more) sessions using it.

I can understand one server process can serve more than one session in shared server mode. How can a session use many server processes in this mode?

My case is dedicated server mode, and my understanding is session vs. server process is 1 to 1 relationship. (here the session is the middle tier to the database , not the session of the end user to the middle tier).

That answered half of my question

Jian, February 25, 2004 - 10:00 am UTC

My first half question was how a session used more than one process?

Tom Kyte
February 25, 2004 - 11:03 am UTC

shared server. you answered it already.

each call to the server can and will use a different process -- hence a single session can and will use many processes.

I see what you mean

A reader, February 25, 2004 - 2:18 pm UTC

I was wondering how came a session could use more than one server process at the SAME time.

Tom Kyte
February 25, 2004 - 2:50 pm UTC

parallel query :) conceptually as well. although technically the pq slaves are sessions unto themselves.

auditing unknown user

vinodh, March 17, 2004 - 4:18 am UTC

Hi Tom,
i need to identify the unknown user logon attempts my date.

i actually enabled audit session by user1,user2 ..... for all unknown user .

i was not sure i need to use audit session or audit connect.

kindly could you clarify what's different between these two and how to track the user after enabling the audit.


Tom Kyte
March 17, 2004 - 8:05 am UTC

both will collect the information you want in the same fashion.

dba_audit_trail is how you "track".

audit connect

A reader, March 18, 2004 - 9:54 am UTC

I have a mult-tier applications. The users suppose to login through application, not by others such as sqlplus.
Can the audit trail (audit connect) show who used sqlplus to login the databases?
Thank you so much, you are great.


Tom Kyte
March 18, 2004 - 10:03 am UTC

well, why not just make it so the only things that can net into the database are the middle tier machines?

but short of that, you would capture the 'program' in v$session in a logon trigger -- but you would have to just be aware that i can fake the program simply by copying sqlplus to some other application name easily.

A reader, March 18, 2004 - 10:18 am UTC

but short of that, you would capture the 'program' in v$session in a logon
trigger -- but you would have to just be aware that i can fake the program
simply by copying sqlplus to some other application name easily.
We have been inforcing the application login.
But we want to audit if there were any other ways intruders' logining.

"you would capture the 'program' in v$session in a logon
Do you mean the functionality built with audit trail? Or I have to write it? I want to take advantage of exist of it.

Tom Kyte
March 18, 2004 - 10:26 am UTC

login trigger on the database -- a DIY solution.

what's "a DIY solution"

A reader, March 18, 2004 - 10:34 am UTC

Tom Kyte
March 18, 2004 - 10:34 am UTC


Tracking create session by access for unknow user

Vinodh, March 22, 2004 - 1:42 am UTC

Hi Tom,
i would like to track the user turnon create session.
iam trying to audit create session by access.

1)how to turn this for specific users.
2)if they succesfully logged in then need to track there info to disable the user.

Thanks in advance

Tom Kyte
March 22, 2004 - 6:59 am UTC

have you read the chapter on auditing in the admin guide? it has lots of examples and shows how to turn on/off for specific users.

not sure what point #2 means.

auditing create session

Vinodh, March 23, 2004 - 2:07 am UTC

Hi tom,
I will make clear, I read the chapter in admin guide.

i need to audit the create session.

audit create session by access;

but i need to do that for specific users like, tom,tomkyte,kyte.

how to do that.

Kindly could you explain with example.

Thanks in Advance

Tom Kyte
March 23, 2004 - 7:14 am UTC

</code> <code>

(i asked because I knew the chapter on auditing has an example right in it....)

Got it

Vinodh, March 23, 2004 - 4:54 am UTC


Thanks i got it.

Audited successfully.

audit created session by user1 by access;


Still searching for what i needed.

Vinodh, March 24, 2004 - 7:03 am UTC

Hi tom,
i have enabled audit create session by unknownuser1,2 by access;

now i need to track who are the unknown user logged in.

how to do that.

Kindly could you pls give it with example tom


Tom Kyte
March 24, 2004 - 9:06 am UTC

ok, can you tell me what an "unknown user" is exactly????

audited info

Vinodh, March 24, 2004 - 7:07 am UTC

Hi Tom,
iam trying to get logged on unknown user by

select * from dba_audit_session
where action_name = 'LOGON';

is that correct way, if not not kindly correct me.


Logon unknown user

Vinodh, March 28, 2004 - 9:56 pm UTC

Hi Tom ,
Yes unknown user means , currently we are creating user with there emp id. in oldder day we did with out that some are using some or not . some user are out from company ex. emp. user apart from ex. emp and current users are all unknown user. we would like to track who is using the unknown ids.

Hope it was clear now.


Tom Kyte
March 29, 2004 - 7:14 am UTC

clear as "mud"

"unknown ids"? seems they are "known".

how could the database know which are "unknown"

ok let me explain

vinodh, March 29, 2004 - 8:05 am UTC

Hi Tom,

Ok as you said think that there are 1000 users currently.

500 users active user with emp id attached with there handle.
300 users already left the organisation.

remaining 200 users are unknown users. In that 200 handle may be the old handle of active users and some may be old handle of ex-emp.

i would like to audit for those 200 user.
1)when they logon.
2)How many times they are logging on.

Thats it.
"Clear like pure water now"

Tom Kyte
March 29, 2004 - 10:37 am UTC

nope, not getting it (is anyone else? am i missing something obvious?)

RE: Is anyone else?

Mark A. Williams, March 29, 2004 - 10:51 am UTC

Not sure I am following, but here is a stab at it...

Is this close?

1000 users in the user population

500 active users have an id that is known
300 users with a known id leave the organization

You know that the user ids for those 300 individuals are "inactive" or can be accounted for (excuse the dangling preposition).

You are not sure about the remaining 200 user ids. It could be that "User 101" currently has a user id that previously belonged to "User 201" before "User 201" left the organization? Are user ids being recycled? You want to audit for logon of a set of user ids?

- Mark

Tom Kyte
March 29, 2004 - 11:42 am UTC

if that is it -- that is simply "audit username" 200 times for the ones in question.

RE: Is anyone else?

Kalita, March 29, 2004 - 11:17 am UTC

Not sure if I got it right but anyway..
There are 1000 users. The known users are
500 active users which were created using the emp-ids
300 users that left the company ( created using emp_id)

Out of the remaining 200 users, it could be
users created without using emp_id but those guys are still working for the company
users created without using emp_id but those guys have left the company.

So, I think he wants to find out those people still using their old ids or ids that are still being used even after those employees have left the company.

Sounds like all users which are not linked to emp_ids to me.

Is that any good? :-)


Tom Kyte
March 29, 2004 - 11:50 am UTC

if so, they need to generate a list of users they want to audit and just issue "audit" on that user.

how to audit failed logon/off ONLY?

jian, April 02, 2004 - 1:08 pm UTC

1) Is it possible to audit failed logon/off ONLY? I mean that I dont want to have successful logon/off data in the audit trail.

2) If 1) is not possible, what are the options to clean the successful logon/off data in the audit trail since it grows too fast. Now, I'm using scheduled job to delete them, i.e.

SQL> select what from user_jobs;

-- This job is used to clean AUDIT TRAIL
 -- remove sussessful actions like LOGOFF, LOGFF BY CLEAN UP
 delete aud$ where returncode=0 AND action# IN (101,102);

do you have any better options with better performance? I was thinking about TRUNCATE but since the system logon/off is pretty active, it's not practical to lock the table for TRUNCATE.


Tom Kyte
April 02, 2004 - 1:55 pm UTC

ops$tkyte@ORA9IR2> select count(*) from system.aud$;
ops$tkyte@ORA9IR2> audit connect whenever not successful;
Audit succeeded.
ops$tkyte@ORA9IR2> connect /
ops$tkyte@ORA9IR2> select count(*) from system.aud$;
ops$tkyte@ORA9IR2> connect foo/bar
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
ops$tkyte@ORA9IR2> connect bar/foo
ORA-01017: invalid username/password; logon denied
ops$tkyte@ORA9IR2> connect /
ops$tkyte@ORA9IR2> select count(*) from system.aud$;


A reader, April 02, 2004 - 2:24 pm UTC

audit session
audit connect ?

what is the difference ? which is better ?

i see only audit session in documented in the admin guide. i can see a subset of the audit trail in dba_audit_session.

Tom Kyte
April 02, 2004 - 3:21 pm UTC

they are basically "the same"

A reader, April 05, 2004 - 10:58 am UTC

I'm using "audit session".

How to purge audit records older than, say, 3 months ?


Tom Kyte
April 05, 2004 - 12:00 pm UTC

you'd have to delete from aud$, please contact support for any caveats regarding that.

A reader, April 12, 2004 - 9:28 am UTC

Please take a look:

  1  select ACTION_NAME, username, count(*) from dba_audit_session
  2  where username like 'DB_SRV%'
  3* group by action_name, username
SQL> /

ACTION_NAME                 USERNAME                         COUNT(*)
--------------------------- ------------------------------ ----------
LOGON                       DB_SRV_SAS                              2
LOGON                       DB_SRV_CONF                             6
LOGOFF                      DB_SRV_SAS                          38791
LOGOFF                      DB_SRV_CONF                         96120
LOGOFF BY CLEANUP           DB_SRV_SAS                           2405
LOGOFF BY CLEANUP           DB_SRV_CONF                         43499

6 rows selected.

This is an "AUDIT SESSION" running for 1 week. The applications above are purely client/server in perl using oci, no middle tier. I'm using dedicated servers only. 

1) Why so few logons/logoffs ?

2) The number of LOGOFF BY CLEANUP seems high to me. What can be causing it ? It means the processes is terminated abnormally ( like kill -9 ) ?

3) I have a rollback percentage close to 50 % in statspack. Maybe there is some relation ? 

Tom Kyte
April 12, 2004 - 10:43 am UTC

1) there are 40,000 plus for db_srv_sas and almost 140,000 for db_srv_conf.  why so few?  don't understand how that is "few".

You do understand that each logon is turned into a logoff right.  consider:

  1  select ACTION_NAME, username, count(*) from dba_audit_session
  2* group by action_name, username
ops$tkyte@ORA9IR2> save a rep
Wrote file a.sql
ops$tkyte@ORA9IR2> @a
ACTION_NAME                 USERNAME                         COUNT(*)
--------------------------- ------------------------------ ----------
LOGON                       OPS$TKYTE                               1
ops$tkyte@ORA9IR2> connect /
ops$tkyte@ORA9IR2> @a
ACTION_NAME                 USERNAME                         COUNT(*)
--------------------------- ------------------------------ ----------
LOGON                       OPS$TKYTE                               1
LOGOFF                      OPS$TKYTE                               1
ops$tkyte@ORA9IR2> connect /
ops$tkyte@ORA9IR2> @a
ACTION_NAME                 USERNAME                         COUNT(*)
--------------------------- ------------------------------ ----------
LOGON                       OPS$TKYTE                               1
LOGOFF                      OPS$TKYTE                               2
ops$tkyte@ORA9IR2> connect /
ops$tkyte@ORA9IR2> @a
ACTION_NAME                 USERNAME                         COUNT(*)
--------------------------- ------------------------------ ----------
LOGON                       OPS$TKYTE                               1
LOGOFF                      OPS$TKYTE                               3

see how logoff goes up, but logon does not (logon is showing how many are logged on right now)

2) anything that does not call OCISessionEnd would cause this.  could be an issue with your perl driver, or a crashed program.  For example, I crashed sqlplus using ctl-\ and logged back in:

ops$tkyte@ORA9IR2> /home/tkyte/bin/plus: line 6: 13609 Quit                    sqlplus /
[tkyte@tkyte-pc tkyte]$ sqlplus /
SQL*Plus: Release - Production on Mon Apr 12 10:41:24 2004
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release - Production
ops$tkyte@ORA9IR2> @a
ACTION_NAME                 USERNAME                         COUNT(*)
--------------------------- ------------------------------ ----------
LOGON                       OPS$TKYTE                               1
LOGOFF                      OPS$TKYTE                               3
LOGOFF BY CLEANUP           OPS$TKYTE                               1

3) I don't think so....  but if you do transactions that modify data -- you are doing tons of work only to undo tons of work (not very 'good') 

Auditing sessions

Gill Gilmore, April 13, 2004 - 12:47 pm UTC

Congratulations on this awsome site ! ;)

Tom, I have "taken over" a database environment as a DBA and one of the problems we are facing is devising a way to track sessions as follows:

Issue #1: We have myriad clients all over the company connecting to my database. We have to make an inventory of all the scripts, machines, users, etc. coming in. We have turned on auditing (audit connect) and we are looking at dba_audit_trail and dba_audit_session. However, we are missing the program name and other info in the fashion it appears in v$session (like "sqlplus.exe"). It would be great !

Q1) How could we get the info that is in v$session and is not in the audit trail ? My hunch would be... logon triggers :)) ? Right ? I would end up creating a "custom" audit trail, and I HATE re-inventing the wheel ! :)

Just a comment: in time we plan on instrumenting all of these apps with dbms_client_info.

Issue #2: Once in possesion of all the allowed app names, we would like to create a table like:

Client hostname Username Os_username Program name
=============== ======== =========== ============

With 2 purposes:
1) Only registered users/apps could logon
2) Maintaing an on-line inventory of the client locations

We would need the program name above to authenticate the incoming connection.

Q2) We would have to do this with logon triggers too to validade it against the table, right ?

Q3) What do you think of this "authentication table" solution ?

Issue #3: This seems to be the worst part. Our manager wants us to enforce password complexity and expiring, which I too think is a necessary policy. However, all client apps have their passwords hard-coded in configuration files and the like. I don't like to have harcoded passwords around the company.

Q4) Do you know of any method to "hide" them ? Would you give me any ideas or point me to any docs ?

Q5) Besides, how could I deal with the fact of having to change those passwords in so many places ?

Thank you very much for your advice, Tom !

How can we know about the Forms 6i connection in Database

MEHMOOD MALIK, April 14, 2004 - 6:11 am UTC

Dear Tom:
You are doing a great job. I always learn many things when i come to your site. My question is:

How can I get the information from my Database, that some user is using Forms 6i or Forms 9i? Previously V$SESSION was usefull but currently it doesnt show in the column PROGRAM in Oracle 9i Database.

Tom Kyte
April 14, 2004 - 8:54 am UTC

clients are just clients to us -- forms is not any different that sqlplus, toad, whatever. they are just programs.

the forms client would have to use dbms_application_info to set fields in v$session for you to inspect. This is a good coding standard for ALL shops -- to put lots of calls to dbms_application_info in their code to set the client_info, action, and module columns of v$session.

A reader, April 14, 2004 - 9:46 am UTC

I'm using 9.2. How do I get the program name (and possibly other columns) in v$session from the audit trail ? I'm using "audit session". If not possible, any workaround ?

Tom Kyte
April 14, 2004 - 10:52 am UTC

you'd have to custom audit using a logon/logoff trigger.

V$table information

Goh Seong Hin, June 07, 2004 - 3:25 am UTC

Dear Tom,

I would like to get some of the information from v$session and store it in my Historical table. What is the best way to get the info without have a great performance impact on my database? I have thaught of writing database trigger (on insert) to get the info but doesn't know what is the performance impact like. Based on your exeperience, what would be the best way to retrieve the data from dict tables. Thanks.


Do you sell Effective Oracle by Design book in Malaysia? I can only get Expert one-on-one Oracle.

Tom Kyte
June 07, 2004 - 8:26 am UTC

what information do you need from v$session -- is it information that is static for the life of a session or ever changing information? which columns.

I do not control or participate in the distribution of the books at all - that is up to the publisher. You would normally take the ISBN to any reputable book store and they would be able to order it for you if it is available in your country.

Thanks Tom

Goh Seong Hin, June 15, 2004 - 4:04 am UTC

Dear Tom,

sql address in v$session. I am trying to create a audit trail to keep track all the SQL that were performed by the session. Some suggestion from metalink on Doc ID: 72460.1 which recommend to turn on the oracle audit trail and write a trigger to get the sql address info when there is a update. I feel that this method might not suitable for OLTP environment as insert a lots IO. I wonder if you have any better suggestion on this matter. Thanks.


Tom Kyte
June 15, 2004 - 3:28 pm UTC

dbms_fga to capture SELECTS
triggers to capture DML (ora_sql_txt function in a trigger gives you this)

Thanks Tom

Goh Seong Hin, June 16, 2004 - 3:19 am UTC

Dear Tom,

Thanks for the info. However, ora_sql_txt and dmms_fga feature is only available in Oracle 9i++. Thanks.


Tom Kyte
June 16, 2004 - 12:39 pm UTC

that'll be the only efficient way to accomplish your goal.

you will not be able to capture all selects, you will be hard pressed to create the triggers to capture the right sql.

On Logon triggers and standby database

Alain, September 10, 2004 - 6:18 am UTC


Logon triggers with an insert statement can be very dangerous if you have a physical standby database that you need to open read only. It opens fine, but you can't login ;-).

Tom Kyte
September 10, 2004 - 9:19 am UTC

a DBA should be able to login though.....

Behaviour of Logon/Logoff triggers in connection pooling Environment

Tony, October 27, 2004 - 12:42 am UTC

How will Logon/Logoff triggers behave in connection pooling environment, where pre-defined number of connections are already established with the database? Will it fire for each login and logout?

Tom Kyte
October 27, 2004 - 7:21 am UTC

there is only one login and one logout -- yes, it'll fire on each one, but it WON'T fire on each grab of a connection from the pool

Please help

Sanjay Jha, November 04, 2004 - 5:07 pm UTC

We have a database running on with a profile assigned to all the application users. We also have audit on for the logon and logoff.The requirement is to capture the "evidence" that a user's particular session was indeed terminated when it exceeded the idle time.. Please help.

Tom Kyte
November 05, 2004 - 3:48 pm UTC

other than polling v$session from time to time to look for sniped sessions, or reading the help desk printouts on the number of calls you get about "hey, my session was killed, whats up with that" -- I don't know of a way to "give evidence"

Guess you could setup a "beacon" program that connects, sits idle and then tries to do something -- if it succeeds -- it would be evidence it isn't working.

Can we relocate sys.aud$ table on a different tablespace?

Sanjay Jha, November 09, 2004 - 12:47 pm UTC

Thanks for the reply. Yes, it makes sense to run a dummy process and illustrate the sniped session, because of the profile.

Tom, I had another question. Can we move SYS.AUD$ to a different tablespace other than SYSTEM ? What will be the impact? Oracle version is

Tom Kyte
November 09, 2004 - 1:26 pm UTC

contact support for that last question there.

Audit Connect and connection failure

Mohini, December 09, 2004 - 11:04 am UTC

OS: HP-UX 11i
We had an issue couple of days ago..where we saw ORA-7445 errors in alert log. We opened a tar for it and came to a find out that somebody is trying (over and over!!) to connect to our database with a 7.3 client. They have finally stopped after trying a dozen times!!!.
I thought "audit connect" might help in this situation to tell
me where the connection is coming from (in the future)..

Question: Will audit connect still work considering this connection won't just fail but also is creating core dumps.


Tom Kyte
December 09, 2004 - 3:16 pm UTC

it would depend on what level the 7445 was happening - if it happened before or after the actual "authenticate me" call.

How to execute the os cmd ping using java.lang.Runtime.exec

Azharudeen, January 12, 2005 - 5:43 am UTC

I dont know how to execute it will please forward the desire program it will be very useful for me in my project

Tom Kyte
January 12, 2005 - 9:28 am UTC

$ man ping

how do i get macine name when thhere is only one schema and user is connecting FROM 5 terminals

ghazanfar, January 28, 2005 - 1:16 am UTC

is it possible to get macine name when there is only one schema and user is connecting from 5 different terminals
i have a problem to track update and deletes on schema objects but problem is of single user schema

Tom Kyte
January 28, 2005 - 7:20 am UTC

SQL> audit ....;

the terminal is captured via audit.  look at dba_audit_trail, it is right there.



A reader, March 02, 2005 - 6:44 pm UTC

Thanks again!

How to track logons and log-offs seperately

Sanjaya Balasuriya, July 28, 2005 - 7:46 am UTC

Hi Tom,
"You do understand that each logon is turned into a logoff"
If the same entry of logon is turned into a logoff, how can we trace logons and logoffs separately ?

Is there any other way to track logon and logoff times ?


Tom Kyte
July 28, 2005 - 9:19 am UTC

look at the full record.

when you have a logon event, it looks like this:

OS_USERNAME                   : "tkyte"
USERNAME                      : "OPS$TKYTE"
USERHOST                      : ""
TERMINAL                      : ""
TIMESTAMP                     : "28-jul-2005 09:10:33"
ACTION_NAME                   : "LOGON"
LOGOFF_TIME                   : ""
LOGOFF_LREAD                  : ""
LOGOFF_PREAD                  : ""
LOGOFF_LWRITE                 : ""
LOGOFF_DLOCK                  : ""
SESSIONID                     : "5942"
RETURNCODE                    : "0"
CLIENT_ID                     : ""
SESSION_CPU                   : ""

when that logs off -- it updates it to this:

OS_USERNAME                   : "tkyte"
USERNAME                      : "OPS$TKYTE"
USERHOST                      : ""
TERMINAL                      : ""
TIMESTAMP                     : "28-jul-2005 09:10:33"
ACTION_NAME                   : "LOGOFF"
LOGOFF_TIME                   : "28-jul-2005 09:12:10"
LOGOFF_LREAD                  : "2738"
LOGOFF_PREAD                  : "278"
LOGOFF_LWRITE                 : "18"
LOGOFF_DLOCK                  : "0"
SESSIONID                     : "5942"
RETURNCODE                    : "0"
CLIENT_ID                     : ""
SESSION_CPU                   : "26"

You have the logon logoff times tracked for you already. 


Sanjaya Balasuriya, August 08, 2005 - 7:12 am UTC

Hi Tom,

How to interpret the values of the column "OBJ_PRIVILEGE" ?


Meaning of the values in OBJ_PRIVILEGE

Sanjaya Balasuriya, August 09, 2005 - 9:45 pm UTC

Hi Tom,
In the document you pointed, it says;
"OBJ_PRIVILEGE VARCHAR2(16) Object privileges granted or revoked by a GRANT or REVOKE statement"

But what are meaning of the values in that column ? How can we say what are privileges granted or revoked by looking at the value of this column ?
For eg. here are values in my dba_audit_trail.OBJ_PRIVILEGE;


Thanks in advance.

Tom Kyte
August 10, 2005 - 10:07 am UTC

Ahh, now I understand, they map to the same thing as ses_actions

one for each action type in the order ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, and EXECUTE. Positions 14, 15, and 16 are reserved for future use.

ops$tkyte@ORA10GR1> audit grant on t by access;

Audit succeeded.

ops$tkyte@ORA10GR1> grant alter on t to scott;

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;


ops$tkyte@ORA10GR1> grant delete on t to scott;

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;


ops$tkyte@ORA10GR1> grant index on t to scott;

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;


ops$tkyte@ORA10GR1> grant insert on t to scott;

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;


ops$tkyte@ORA10GR1> grant lock on t to scott;
grant lock on t to scott
ERROR at line 1:
ORA-00990: missing or invalid privilege

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;


ops$tkyte@ORA10GR1> grant select on t to scott;

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;


ops$tkyte@ORA10GR1> grant update on t to scott;

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;


6 rows selected.

ops$tkyte@ORA10GR1> grant references on t to scott;

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;


7 rows selected.

Thanks sir...

Sanjaya Balasuriya, August 10, 2005 - 9:55 pm UTC

This is the information I was looking for. I searched through the documentations for more than 2 hours to get this information without any luck.

sal, September 21, 2005 - 10:32 am UTC

Hi Tom,

This site really a boon to us Oracle users...

through OEM can we see changed passwords
ie new password as well as old password

A reader, October 31, 2005 - 5:39 pm UTC

desc logonaudittable
Name Null? Type
----------------------------------------- -------- --------------------------

Event can be 'LOGON' or 'LOGOFF'

If I use the following SQL for getting the logon and logoff details

select a.sid,a.serial#,a.timestamp LOGON_TIME,b.timestamp LOGOFF_TIME,trunc(a.timestamp) LOGON_DATE,trunc(b.timestamp) LOGOFF_DATE,
(b.timestamp-a.timestamp)*24*60*60 "Total_Duration(in seconds)", a.username,a.osuserid,a.machinename,a.program
from logonaudittable a, logonaudittable b where a.SID=b.sid and a.SERIAL#=b.SERIAL# and
a.EVENT='LOGON' and b.event='LOGOFF'

Some of the records are showing more than 1 year since I guess sid,serial# are getting repeated. How to get around this problem ?


Tom Kyte
November 01, 2005 - 5:50 am UTC

You'd need to add a "sanity check" to the where clause

and (b.timestamp-a.timestamp) <= some_threshold_of_reasonable_time

Or, use analytics with lag instead of a join.

select *
from (
select ....,
lag(columns you want) over (partition by sid, serial#, order by timestamp)
other lags.....
from your_table
where event in ( 'LOGON', 'LOGOFF' )
where event = 'LOGON';

A reader, October 31, 2005 - 5:51 pm UTC

select a.sid,a.serial#,a.timestamp LOGON_TIME,b.timestamp LOGOFF_TIME,trunc(a.timestamp) LOGON_DATE,trunc(b.timestamp) LOGOFF_DATE,
(b.timestamp-a.timestamp)*24*60*60 "Total_Duration(in seconds)", a.username,a.osuserid,a.machinename,a.program
from logonaudittable a, logonaudittable b where a.SID=b.sid and a.SERIAL#=b.SERIAL# and
a.EVENT='LOGON' and b.event='LOGOFF' and a.username=b.username and a.osuserid=b.osuserid

Even the above SQL gives a total duration of more than 1 year for some users

Concurrent Users

A reader, December 01, 2005 - 5:08 am UTC

Hi Tom
I am little bit confuse on no of concurrent users
Can you tell me how can we found out the no of concurrent users at a given point of time from the DB


Tom Kyte
December 01, 2005 - 12:38 pm UTC

select * from v$license

select * from v$session where status = 'ACTIVE';

Concurrent Users

Praful, December 02, 2005 - 5:26 am UTC

Hi Tom,

The V$license shows the following output


Does this mean that 3275 are the concurrent users ??

Also the v$session shows the active sessions as 165
Does this mean that there are 165 concurrent users??

Tom Kyte
December 02, 2005 - 10:58 am UTC

3,275 sessions are currently established, of which 165 where actually doing something.

Depends on how you define "concurrent". You have 165 ACTIVE sessions, you have 3,275 sessions.

Concurrent Users

Praful, December 02, 2005 - 11:50 am UTC

Hi Tom

thanx a lot
The info you provide is helpful to me

Thanx a lot once again

Last User Login

V, December 20, 2005 - 1:55 pm UTC

Is there anyway without using audit to tell the last time a user logged in?

Tom Kyte
December 20, 2005 - 2:11 pm UTC

only if they are still logged in, unless you capture it somewhere - we don't.

adding an index on sys.aud$

A reader, February 15, 2006 - 5:02 pm UTC

Is adding an index sys.aud$ in general an safe thing to do, if performance has been tested beforehand?

Tom Kyte
February 15, 2006 - 9:53 pm UTC

contact support.

Determine from which program the session logon

Sean, March 21, 2006 - 5:27 pm UTC

We saw hundreds of logon and logoff in the short period (within a minute) by looking at dba_audit_session. We would like to know from which program these sessions logon. V$session has the program column to provide such information. Is there similar information in the audit dictionary?

Thanks so much for help.


Tom Kyte
March 22, 2006 - 3:25 pm UTC

I do not know of it being captured, we'd have to use a logon trigger to capture more information.


Mark, May 04, 2006 - 9:08 am UTC

Hi Tom,

Is it normal for inacitve sessions to hang around for hours on end and not be reused in Oracle?

We have between 300-550 concurrent DEDICATED Server sessions in our application at any one time. Sometimes we will see sessions shoot up in numbers, yet there will be dozens and dozens of INACTIVE sessions that seem to never be reused.

I get this information from

08:59:39 HT4:PHYDB001082758:08:27 - LIVE>
08:59:58 HT4:PHYDB001082758:08:27 - LIVE> SELECT sid, serial#
08:59:59 2 ,TO_CHAR(logon_time, 'YYYY-MON-DD HH24:MI:SS') logon_time
08:59:59 3 ,REPLACE(
08:59:59 4 TO_CHAR(FLOOR(last_call_et / 3600), '00') || ':' ||
08:59:59 5 TO_CHAR(FLOOR(MOD(last_call_et, 3600) / 60), '00') ||
08:59:59 6 ':' ||
08:59:59 7 TO_CHAR(MOD(MOD(last_call_et, 3600), 60), '00'), ' ', ''
08:59:59 8 ) last_call_et
08:59:59 9 ,status
08:59:59 10 FROM v$session s
08:59:59 11 ORDER BY logon_time ASC
08:59:59 12 /

---------- ---------- -------------------- ----------- --------
1 1 2006-MAY-04 00:11:16 08:47:38 ACTIVE
2 1 2006-MAY-04 00:11:16 08:47:38 ACTIVE
3 1 2006-MAY-04 00:11:16 08:47:38 ACTIVE
4 1 2006-MAY-04 00:11:17 08:47:38 ACTIVE
5 1 2006-MAY-04 00:11:17 08:47:38 ACTIVE
6 1 2006-MAY-04 00:11:17 08:47:38 ACTIVE
7 1 2006-MAY-04 00:11:17 08:47:38 ACTIVE
11 1 2006-MAY-04 00:11:19 08:47:38 ACTIVE
12 15 2006-MAY-04 00:11:57 08:46:59 INACTIVE
40 1812 2006-MAY-04 03:39:12 05:19:41 INACTIVE
27 636 2006-MAY-04 03:39:19 05:19:35 INACTIVE
23 3968 2006-MAY-04 03:39:26 05:19:27 INACTIVE
43 1267 2006-MAY-04 03:39:26 05:19:27 INACTIVE
17 2890 2006-MAY-04 04:40:19 04:18:38 INACTIVE
15 2563 2006-MAY-04 04:55:21 04:03:35 INACTIVE
41 1982 2006-MAY-04 04:56:56 04:02:00 INACTIVE
29 4837 2006-MAY-04 05:52:09 03:06:47 INACTIVE
26 4319 2006-MAY-04 05:55:29 03:03:27 INACTIVE
21 11171 2006-MAY-04 06:15:26 02:43:33 INACTIVE
55 331 2006-MAY-04 06:19:11 02:39:44 INACTIVE
73 62 2006-MAY-04 06:25:39 02:33:23 INACTIVE
81 214 2006-MAY-04 06:28:53 02:30:02 INACTIVE
70 371 2006-MAY-04 06:33:45 02:25:08 INACTIVE
22 3133 2006-MAY-04 06:35:22 02:23:32 INACTIVE
45 2802 2006-MAY-04 06:38:19 02:20:32 INACTIVE
50 2019 2006-MAY-04 06:41:16 02:17:38 INACTIVE
57 1255 2006-MAY-04 06:41:16 02:17:38 INACTIVE
93 947 2006-MAY-04 06:43:33 02:15:28 INACTIVE
34 3590 2006-MAY-04 06:43:43 02:15:13 INACTIVE
91 570 2006-MAY-04 06:44:25 02:14:37 INACTIVE
59 1512 2006-MAY-04 06:49:26 02:09:27 INACTIVE
39 6805 2006-MAY-04 06:54:42 02:04:13 INACTIVE
69 1377 2006-MAY-04 06:54:49 02:04:05 INACTIVE
99 1148 2006-MAY-04 06:56:05 02:02:50 INACTIVE
20 5824 2006-MAY-04 06:56:42 02:02:12 INACTIVE
85 1289 2006-MAY-04 06:56:42 02:02:12 INACTIVE
61 2254 2006-MAY-04 07:00:12 01:58:42 INACTIVE
49 2835 2006-MAY-04 07:00:39 01:58:17 INACTIVE
88 1186 2006-MAY-04 07:01:58 01:56:55 INACTIVE
46 3568 2006-MAY-04 07:04:59 01:53:57 INACTIVE
120 402 2006-MAY-04 07:05:39 01:53:16 INACTIVE
130 451 2006-MAY-04 07:06:13 01:52:42 INACTIVE
117 586 2006-MAY-04 07:09:11 01:49:50 INACTIVE
67 1978 2006-MAY-04 07:14:50 01:44:03 INACTIVE
136 296 2006-MAY-04 07:16:00 01:42:59 INACTIVE
32 10429 2006-MAY-04 07:18:26 01:40:23 INACTIVE
33 4829 2006-MAY-04 07:20:04 01:38:53 INACTIVE
36 3828 2006-MAY-04 07:20:27 01:38:29 INACTIVE
113 1352 2006-MAY-04 07:20:46 01:38:12 INACTIVE
122 744 2006-MAY-04 07:24:47 01:34:07 INACTIVE
147 752 2006-MAY-04 07:25:15 01:33:38 INACTIVE
56 4354 2006-MAY-04 07:25:25 01:33:31 INACTIVE
112 1965 2006-MAY-04 07:25:49 01:30:09 INACTIVE
126 1723 2006-MAY-04 07:28:29 01:30:27 INACTIVE
95 1776 2006-MAY-04 07:29:32 01:29:22 INACTIVE
76 2408 2006-MAY-04 07:29:36 01:29:21 INACTIVE
159 429 2006-MAY-04 07:30:02 01:28:52 INACTIVE
44 4410 2006-MAY-04 07:30:25 01:28:30 INACTIVE
72 5165 2006-MAY-04 07:30:34 01:28:19 INACTIVE
148 1273 2006-MAY-04 07:30:48 01:28:06 INACTIVE
127 1465 2006-MAY-04 07:36:08 01:22:45 INACTIVE
96 1817 2006-MAY-04 07:37:11 01:21:43 INACTIVE
172 78 2006-MAY-04 07:37:53 01:21:00 INACTIVE
105 2690 2006-MAY-04 07:39:12 01:19:42 INACTIVE
79 3217 2006-MAY-04 07:40:26 01:18:27 INACTIVE
128 3418 2006-MAY-04 07:40:32 01:18:23 INACTIVE
42 6290 2006-MAY-04 07:49:38 01:08:01 INACTIVE
139 4097 2006-MAY-04 07:51:09 01:07:44 INACTIVE
104 2845 2006-MAY-04 07:52:37 01:05:54 INACTIVE
37 9683 2006-MAY-04 07:52:39 01:06:16 INACTIVE
173 3307 2006-MAY-04 07:54:13 01:04:41 INACTIVE
119 3021 2006-MAY-04 07:54:33 00:00:00 INACTIVE
197 1084 2006-MAY-04 07:54:37 01:04:19 INACTIVE
141 3360 2006-MAY-04 07:57:38 01:01:21 INACTIVE
163 4462 2006-MAY-04 07:58:39 01:00:14 INACTIVE
218 171 2006-MAY-04 08:01:00 00:58:01 INACTIVE
180 2812 2006-MAY-04 08:01:19 00:57:47 INACTIVE
230 204 2006-MAY-04 08:10:30 00:48:23 INACTIVE
220 228 2006-MAY-04 08:11:57 00:46:56 INACTIVE
254 775 2006-MAY-04 08:12:04 00:46:52 INACTIVE
264 327 2006-MAY-04 08:12:20 00:46:36 INACTIVE
266 609 2006-MAY-04 08:12:26 00:46:27 INACTIVE
129 2798 2006-MAY-04 08:13:34 00:45:19 INACTIVE
124 4355 2006-MAY-04 08:15:56 00:42:56 INACTIVE
110 6545 2006-MAY-04 08:16:55 00:41:58 INACTIVE
280 2469 2006-MAY-04 08:17:36 00:41:19 INACTIVE
133 4974 2006-MAY-04 08:19:25 00:39:29 INACTIVE
169 1902 2006-MAY-04 08:20:34 00:38:22 INACTIVE
152 7278 2006-MAY-04 08:22:24 00:36:31 INACTIVE
258 878 2006-MAY-04 08:22:54 00:36:01 INACTIVE
190 6849 2006-MAY-04 08:27:57 00:00:00 ACTIVE
201 5046 2006-MAY-04 08:29:12 00:29:42 INACTIVE
214 5215 2006-MAY-04 08:29:49 00:29:03 INACTIVE
265 3399 2006-MAY-04 08:30:56 00:19:44 INACTIVE
132 5940 2006-MAY-04 08:30:58 00:19:41 INACTIVE
284 1881 2006-MAY-04 08:32:02 00:26:52 INACTIVE
38 9481 2006-MAY-04 08:32:27 00:26:22 INACTIVE
200 1827 2006-MAY-04 08:34:21 00:24:32 INACTIVE
144 6879 2006-MAY-04 08:36:07 00:22:46 INACTIVE
184 7137 2006-MAY-04 08:36:15 00:19:43 INACTIVE
174 3437 2006-MAY-04 08:36:17 00:19:41 INACTIVE
263 1844 2006-MAY-04 08:36:36 00:21:49 INACTIVE
167 4813 2006-MAY-04 08:41:15 00:17:39 INACTIVE
315 747 2006-MAY-04 08:42:35 00:16:17 INACTIVE
30 10298 2006-MAY-04 08:42:38 00:16:14 INACTIVE
318 256 2006-MAY-04 08:43:54 00:00:04 INACTIVE
115 10689 2006-MAY-04 08:44:43 00:14:10 INACTIVE
248 2418 2006-MAY-04 08:46:42 00:04:32 INACTIVE
83 9023 2006-MAY-04 08:47:14 00:04:39 INACTIVE
176 6612 2006-MAY-04 08:47:47 00:01:06 INACTIVE
231 2950 2006-MAY-04 08:47:47 00:01:08 INACTIVE
322 1641 2006-MAY-04 08:48:32 00:00:30 INACTIVE
188 3844 2006-MAY-04 08:48:43 00:00:11 INACTIVE
255 5114 2006-MAY-04 08:49:09 00:00:02 INACTIVE
262 5444 2006-MAY-04 08:49:09 00:00:00 ACTIVE
199 4621 2006-MAY-04 08:49:13 00:00:02 INACTIVE
217 3765 2006-MAY-04 08:49:13 00:00:15 INACTIVE
58 14404 2006-MAY-04 08:49:25 00:09:28 INACTIVE
288 6403 2006-MAY-04 08:49:36 00:04:07 INACTIVE
157 4275 2006-MAY-04 08:49:40 00:00:17 INACTIVE
82 11687 2006-MAY-04 08:49:41 00:00:17 INACTIVE
267 3677 2006-MAY-04 08:49:49 00:09:04 INACTIVE
287 1171 2006-MAY-04 08:49:55 00:00:37 INACTIVE
100 11594 2006-MAY-04 08:49:58 00:04:32 INACTIVE
308 2507 2006-MAY-04 08:49:58 00:00:04 INACTIVE
13 17802 2006-MAY-04 08:50:00 00:00:04 INACTIVE
151 6614 2006-MAY-04 08:50:01 00:00:17 INACTIVE
161 5644 2006-MAY-04 08:50:07 00:00:02 INACTIVE
213 6159 2006-MAY-04 08:50:09 00:04:33 INACTIVE
229 5805 2006-MAY-04 08:50:10 00:00:06 INACTIVE
261 7169 2006-MAY-04 08:50:11 00:00:03 INACTIVE
166 14714 2006-MAY-04 08:50:14 00:00:06 INACTIVE
272 2450 2006-MAY-04 08:50:19 00:00:00 INACTIVE
103 6561 2006-MAY-04 08:50:21 00:00:02 INACTIVE
118 10514 2006-MAY-04 08:50:21 00:00:00 INACTIVE
187 5279 2006-MAY-04 08:50:24 00:00:47 INACTIVE
319 422 2006-MAY-04 08:50:25 00:00:00 INACTIVE
244 4278 2006-MAY-04 08:50:26 00:00:00 INACTIVE
75 10822 2006-MAY-04 08:50:43 00:00:02 INACTIVE
219 11324 2006-MAY-04 08:50:43 00:00:37 INACTIVE
87 10479 2006-MAY-04 08:50:43 00:00:23 INACTIVE
270 5024 2006-MAY-04 08:51:11 00:07:44 INACTIVE
235 6340 2006-MAY-04 08:51:21 00:00:17 INACTIVE
238 6151 2006-MAY-04 08:51:21 00:00:00 INACTIVE
246 4019 2006-MAY-04 08:51:23 00:00:02 INACTIVE
154 7956 2006-MAY-04 08:51:36 00:07:16 INACTIVE
16 15864 2006-MAY-04 08:51:40 00:00:02 INACTIVE
135 10261 2006-MAY-04 08:51:40 00:00:11 INACTIVE
302 3167 2006-MAY-04 08:51:45 00:00:00 INACTIVE
277 2372 2006-MAY-04 08:51:56 00:00:23 INACTIVE
19 16522 2006-MAY-04 08:52:10 00:00:12 INACTIVE
89 7578 2006-MAY-04 08:52:20 00:00:02 INACTIVE
198 7770 2006-MAY-04 08:52:21 00:00:14 INACTIVE
301 1450 2006-MAY-04 08:52:21 00:00:04 INACTIVE
149 6670 2006-MAY-04 08:52:26 00:00:33 INACTIVE
279 3389 2006-MAY-04 08:52:26 00:00:02 INACTIVE
241 7266 2006-MAY-04 08:52:45 00:00:02 INACTIVE
192 3567 2006-MAY-04 08:52:46 00:00:02 INACTIVE
195 6841 2006-MAY-04 08:52:46 00:04:10 INACTIVE
208 4883 2006-MAY-04 08:52:46 00:04:10 INACTIVE
245 4073 2006-MAY-04 08:52:56 00:00:37 INACTIVE
54 7733 2006-MAY-04 08:53:07 00:04:36 INACTIVE
221 6433 2006-MAY-04 08:53:07 00:00:02 INACTIVE
131 11404 2006-MAY-04 08:53:15 00:00:00 INACTIVE
178 10000 2006-MAY-04 08:53:15 00:00:23 INACTIVE
243 1531 2006-MAY-04 08:53:15 00:00:23 INACTIVE
271 5303 2006-MAY-04 08:53:15 00:00:02 INACTIVE
323 1216 2006-MAY-04 08:53:15 00:04:32 INACTIVE
317 2361 2006-MAY-04 08:53:15 00:00:00 ACTIVE
269 6955 2006-MAY-04 08:53:15 00:00:14 INACTIVE
146 9329 2006-MAY-04 08:53:15 00:00:02 INACTIVE
71 7035 2006-MAY-04 08:53:16 00:04:32 INACTIVE
165 12860 2006-MAY-04 08:53:16 00:00:23 INACTIVE
171 5970 2006-MAY-04 08:53:16 00:04:16 INACTIVE
321 1105 2006-MAY-04 08:53:16 00:04:45 INACTIVE
297 2398 2006-MAY-04 08:53:16 00:00:33 INACTIVE
9 20762 2006-MAY-04 08:53:17 00:05:36 INACTIVE
162 9663 2006-MAY-04 08:53:17 00:00:03 INACTIVE
293 4569 2006-MAY-04 08:53:17 00:00:23 INACTIVE
175 8959 2006-MAY-04 08:53:17 00:00:00 INACTIVE
274 2590 2006-MAY-04 08:53:20 00:00:17 INACTIVE
111 3649 2006-MAY-04 08:53:21 00:00:06 INACTIVE
222 4347 2006-MAY-04 08:53:21 00:00:06 INACTIVE
305 504 2006-MAY-04 08:53:27 00:00:02 INACTIVE
332 592 2006-MAY-04 08:53:34 00:02:49 INACTIVE
108 7957 2006-MAY-04 08:53:39 00:00:41 INACTIVE
140 7999 2006-MAY-04 08:53:47 00:00:00 ACTIVE
234 6873 2006-MAY-04 08:54:00 00:00:02 INACTIVE
325 1183 2006-MAY-04 08:54:05 00:00:11 INACTIVE
77 13656 2006-MAY-04 08:54:10 00:00:23 INACTIVE
298 3726 2006-MAY-04 08:54:10 00:00:17 INACTIVE
196 5261 2006-MAY-04 08:54:11 00:00:23 INACTIVE
256 3121 2006-MAY-04 08:54:11 00:00:23 INACTIVE
186 7785 2006-MAY-04 08:54:14 00:00:00 INACTIVE
237 4703 2006-MAY-04 08:54:14 00:00:03 INACTIVE
304 1241 2006-MAY-04 08:54:14 00:00:00 INACTIVE
278 10098 2006-MAY-04 08:54:15 00:00:15 INACTIVE
28 15585 2006-MAY-04 08:54:17 00:00:02 INACTIVE
337 133 2006-MAY-04 08:54:20 00:00:07 INACTIVE
346 12 2006-MAY-04 08:54:21 00:00:11 INACTIVE
345 105 2006-MAY-04 08:54:22 00:00:17 INACTIVE
78 11289 2006-MAY-04 08:54:31 00:00:02 INACTIVE
342 530 2006-MAY-04 08:54:31 00:00:00 INACTIVE
143 5485 2006-MAY-04 08:54:31 00:00:19 INACTIVE
328 1100 2006-MAY-04 08:54:33 00:00:54 INACTIVE
247 6273 2006-MAY-04 08:54:35 00:00:04 INACTIVE
353 390 2006-MAY-04 08:54:49 00:00:59 INACTIVE
68 11606 2006-MAY-04 08:54:51 00:00:00 INACTIVE
207 7843 2006-MAY-04 08:55:11 00:00:19 INACTIVE
299 1010 2006-MAY-04 08:55:11 00:00:04 INACTIVE
360 4 2006-MAY-04 08:55:11 00:00:04 INACTIVE
355 8 2006-MAY-04 08:55:11 00:00:02 INACTIVE
309 3054 2006-MAY-04 08:55:11 00:00:04 INACTIVE
286 4275 2006-MAY-04 08:55:11 00:00:04 INACTIVE
377 802 2006-MAY-04 08:55:19 00:00:37 INACTIVE
378 502 2006-MAY-04 08:55:19 00:00:02 INACTIVE
379 755 2006-MAY-04 08:55:21 00:00:30 INACTIVE
102 6061 2006-MAY-04 08:55:29 00:00:02 INACTIVE
98 9499 2006-MAY-04 08:55:35 00:00:06 INACTIVE
10 16893 2006-MAY-04 08:55:42 00:00:00 ACTIVE
311 2843 2006-MAY-04 08:55:43 00:00:02 INACTIVE
344 1793 2006-MAY-04 08:55:45 00:00:00 INACTIVE
63 13249 2006-MAY-04 08:55:46 00:00:02 INACTIVE
121 7048 2006-MAY-04 08:55:46 00:00:02 INACTIVE
338 465 2006-MAY-04 08:55:47 00:00:00 INACTIVE
382 1352 2006-MAY-04 08:55:47 00:00:12 INACTIVE
183 7033 2006-MAY-04 08:55:54 00:00:15 INACTIVE
273 2593 2006-MAY-04 08:56:00 00:00:00 INACTIVE
296 1959 2006-MAY-04 08:56:00 00:00:02 INACTIVE
86 9980 2006-MAY-04 08:56:12 00:00:11 INACTIVE
362 259 2006-MAY-04 08:56:18 00:00:15 INACTIVE
357 310 2006-MAY-04 08:56:19 00:00:02 INACTIVE
285 6405 2006-MAY-04 08:56:20 00:00:30 INACTIVE
370 545 2006-MAY-04 08:56:30 00:00:17 INACTIVE
84 7991 2006-MAY-04 08:56:34 00:00:03 INACTIVE
335 343 2006-MAY-04 08:56:35 00:00:00 INACTIVE
276 3455 2006-MAY-04 08:56:38 00:00:37 INACTIVE
65 9210 2006-MAY-04 08:56:39 00:00:02 INACTIVE
356 611 2006-MAY-04 08:56:44 00:00:11 INACTIVE
106 7595 2006-MAY-04 08:56:50 00:00:17 INACTIVE
257 2790 2006-MAY-04 08:56:55 00:00:02 INACTIVE
142 6496 2006-MAY-04 08:56:56 00:00:04 INACTIVE
236 4147 2006-MAY-04 08:56:59 00:00:23 INACTIVE
373 464 2006-MAY-04 08:56:59 00:00:37 INACTIVE
206 4226 2006-MAY-04 08:57:00 00:00:54 INACTIVE
334 845 2006-MAY-04 08:57:04 00:01:50 INACTIVE
354 704 2006-MAY-04 08:57:06 00:00:02 INACTIVE
101 6725 2006-MAY-04 08:57:10 00:00:00 INACTIVE
8 16176 2006-MAY-04 08:57:15 00:00:04 INACTIVE
31 18342 2006-MAY-04 08:57:16 00:00:05 INACTIVE
177 9192 2006-MAY-04 08:57:18 00:00:00 INACTIVE
283 2935 2006-MAY-04 08:57:19 00:00:04 INACTIVE
204 4528 2006-MAY-04 08:57:22 00:00:04 INACTIVE
239 5954 2006-MAY-04 08:57:23 00:00:00 INACTIVE
359 416 2006-MAY-04 08:57:23 00:00:30 INACTIVE
66 13784 2006-MAY-04 08:57:25 00:00:37 INACTIVE
292 3206 2006-MAY-04 08:57:25 00:00:02 INACTIVE
25 18143 2006-MAY-04 08:57:26 00:00:19 INACTIVE
35 19443 2006-MAY-04 08:57:26 00:00:00 INACTIVE
160 4648 2006-MAY-04 08:57:26 00:01:26 INACTIVE
275 6607 2006-MAY-04 08:57:27 00:00:03 INACTIVE
249 3861 2006-MAY-04 08:57:30 00:00:02 INACTIVE
369 386 2006-MAY-04 08:57:32 00:00:15 INACTIVE
260 5671 2006-MAY-04 08:57:33 00:01:04 INACTIVE
224 6798 2006-MAY-04 08:57:37 00:00:17 INACTIVE
313 1508 2006-MAY-04 08:57:38 00:00:00 INACTIVE
251 706 2006-MAY-04 08:57:39 00:01:10 INACTIVE
371 748 2006-MAY-04 08:57:39 00:01:10 INACTIVE
52 13672 2006-MAY-04 08:57:40 00:01:09 INACTIVE
107 9710 2006-MAY-04 08:57:46 00:01:08 INACTIVE
158 8741 2006-MAY-04 08:57:48 00:00:02 INACTIVE
62 12389 2006-MAY-04 08:57:51 00:00:02 INACTIVE
155 7601 2006-MAY-04 08:57:51 00:01:04 INACTIVE
375 1071 2006-MAY-04 08:57:51 00:00:00 INACTIVE
300 3622 2006-MAY-04 08:57:52 00:00:02 INACTIVE
331 481 2006-MAY-04 08:57:54 00:00:06 INACTIVE
181 8039 2006-MAY-04 08:57:55 00:00:59 INACTIVE
189 5988 2006-MAY-04 08:58:01 00:00:00 INACTIVE
343 1992 2006-MAY-04 08:58:06 00:00:49 INACTIVE
339 975 2006-MAY-04 08:58:09 00:00:02 INACTIVE
145 5969 2006-MAY-04 08:58:18 00:00:37 INACTIVE
367 40 2006-MAY-04 08:58:18 00:00:02 INACTIVE
225 2169 2006-MAY-04 08:58:19 00:00:02 INACTIVE
333 1345 2006-MAY-04 08:58:19 00:00:05 INACTIVE
242 2696 2006-MAY-04 08:58:19 00:00:34 INACTIVE
253 7188 2006-MAY-04 08:58:19 00:00:11 INACTIVE
205 4896 2006-MAY-04 08:58:20 00:00:06 INACTIVE
138 12153 2006-MAY-04 08:58:22 00:00:06 INACTIVE
168 9324 2006-MAY-04 08:58:24 00:00:30 INACTIVE
326 86 2006-MAY-04 08:58:24 00:00:17 INACTIVE
212 2846 2006-MAY-04 08:58:25 00:00:15 INACTIVE
215 2027 2006-MAY-04 08:58:25 00:00:02 INACTIVE
341 284 2006-MAY-04 08:58:32 00:00:14 INACTIVE
348 585 2006-MAY-04 08:58:32 00:00:15 INACTIVE
281 6066 2006-MAY-04 08:58:33 00:00:11 INACTIVE
368 1134 2006-MAY-04 08:58:33 00:00:02 INACTIVE
361 1157 2006-MAY-04 08:58:33 00:00:19 INACTIVE
291 4363 2006-MAY-04 08:58:33 00:00:02 INACTIVE
18 16867 2006-MAY-04 08:58:34 00:00:15 INACTIVE
185 8366 2006-MAY-04 08:58:34 00:00:00 INACTIVE
53 9194 2006-MAY-04 08:58:35 00:00:19 INACTIVE
64 11061 2006-MAY-04 08:58:35 00:00:19 INACTIVE
223 5569 2006-MAY-04 08:58:35 00:00:15 INACTIVE
109 11947 2006-MAY-04 08:58:36 00:00:17 INACTIVE
226 5329 2006-MAY-04 08:58:36 00:00:17 INACTIVE
51 12519 2006-MAY-04 08:58:38 00:00:15 INACTIVE
191 11473 2006-MAY-04 08:58:38 00:00:00 INACTIVE
307 4156 2006-MAY-04 08:58:38 00:00:00 INACTIVE
60 8278 2006-MAY-04 08:58:47 00:00:06 INACTIVE
92 10795 2006-MAY-04 08:58:47 00:00:02 INACTIVE
294 1909 2006-MAY-04 08:58:47 00:00:02 INACTIVE
114 7253 2006-MAY-04 08:58:48 00:00:05 ACTIVE
193 4873 2006-MAY-04 08:58:48 00:00:06 ACTIVE
156 8571 2006-MAY-04 08:58:49 00:00:03 INACTIVE
182 6836 2006-MAY-04 08:58:49 00:00:00 INACTIVE
259 3775 2006-MAY-04 08:58:49 00:00:00 INACTIVE
327 1963 2006-MAY-04 08:58:49 00:00:04 INACTIVE
289 1549 2006-MAY-04 08:58:49 00:00:02 INACTIVE
194 5692 2006-MAY-04 08:58:49 00:00:04 INACTIVE
24 12298 2006-MAY-04 08:58:50 00:00:02 INACTIVE
374 897 2006-MAY-04 08:58:50 00:00:02 INACTIVE
372 552 2006-MAY-04 08:58:50 00:00:02 INACTIVE
363 1262 2006-MAY-04 08:58:50 00:00:00 INACTIVE
329 1760 2006-MAY-04 08:58:50 00:00:02 INACTIVE
320 4395 2006-MAY-04 08:58:50 00:00:03 INACTIVE
314 5760 2006-MAY-04 08:58:50 00:00:03 INACTIVE
268 5698 2006-MAY-04 08:58:50 00:00:02 INACTIVE
233 6124 2006-MAY-04 08:58:50 00:00:02 INACTIVE
232 5103 2006-MAY-04 08:58:50 00:00:02 INACTIVE
211 7256 2006-MAY-04 08:58:50 00:00:03 INACTIVE
209 5461 2006-MAY-04 08:58:50 00:00:03 INACTIVE
179 9533 2006-MAY-04 08:58:50 00:00:02 INACTIVE
137 10420 2006-MAY-04 08:58:50 00:00:02 INACTIVE
74 10624 2006-MAY-04 08:58:51 00:00:02 INACTIVE
164 10844 2006-MAY-04 08:58:51 00:00:00 INACTIVE
90 10368 2006-MAY-04 08:58:51 00:00:02 INACTIVE
228 10641 2006-MAY-04 08:58:51 00:00:02 INACTIVE
364 68 2006-MAY-04 08:58:51 00:00:02 ACTIVE
349 93 2006-MAY-04 08:58:51 00:00:02 INACTIVE
330 326 2006-MAY-04 08:58:51 00:00:00 INACTIVE
310 3480 2006-MAY-04 08:58:51 00:00:02 INACTIVE
282 7214 2006-MAY-04 08:58:51 00:00:02 INACTIVE
216 5381 2006-MAY-04 08:58:51 00:00:00 INACTIVE
210 8362 2006-MAY-04 08:58:51 00:00:02 INACTIVE
202 7021 2006-MAY-04 08:58:51 00:00:02 INACTIVE
47 15624 2006-MAY-04 08:58:52 00:00:00 INACTIVE
94 11325 2006-MAY-04 08:58:52 00:00:02 INACTIVE
340 528 2006-MAY-04 08:58:52 00:00:02 ACTIVE
150 7500 2006-MAY-04 08:58:52 00:00:00 INACTIVE
153 10776 2006-MAY-04 08:58:53 00:00:00 INACTIVE
240 3856 2006-MAY-04 08:58:53 00:00:00 INACTIVE
350 2037 2006-MAY-04 08:58:53 00:00:00 INACTIVE
312 3916 2006-MAY-04 08:58:53 00:00:02 INACTIVE

353 rows selected.

Elapsed: 00:00:01.06
09:00:01 HT4:PHYDB001082758:08:27 - LIVE> SELECT TO_CHAR(SYSDATE, 'YYYY-MON-DD HH24:MI:SS') AS vsysdate
09:00:01 2 FROM dual
09:00:01 3 /

2006-MAY-04 08:58:57

As you can see, I ran this at 8:58:57, and a bunch of new sessions were created just beforer that, but I have a bunch of INACTIVE sessions sitting there not being reused. SID's 40, 27, 43, and 23 have been inactive for over 5 hours.

Most of the inactive sessions have this SQL for their last sql call (by hash):

FROM dual
FROM sys.user$ u1, sys.sysauth$ sa
WHERE u1.user# = sa.privilege#
AND sa.grantee# = UID
AND = UPPER(:b1))

which occurs in a LOGON trigger to set the environment based on a given ROLE to that USER (namely, cursor_sharing and current_schema are set).

It's a web application, Oracle Standard.

Should I worry about this?


Tom Kyte
May 04, 2006 - 4:39 pm UTC

inactive sessions are simply sessions your clients have created and are not doing anything with.

these inactive sessions will stay there as long as the client that created them lets them be there.

we would not get rid of the them (you can use a profile to have them killed - but they will just move from inactive to killed - they will still be there until the client does something about them)

You have to look at your middle tier to see why it is holding so many open sessions - nothing to do with the database here - all about the client

How to find active users

Ram, May 31, 2006 - 2:37 am UTC

Hi Tom,
We have 100 plus database users currently in our system. But all are not active,I want to get a list of users who are not connecting last 3 months. Please give me the solution if it is possible.
Another one When I create new user I want to set some PASSWORD validations of my own other than Oracle default checks. I don’t want to write any procedure I just want to set it as in the profile, please give us the solution with example


Tom Kyte
May 31, 2006 - 9:56 am UTC

Enable auditing - audit connect.

3 months from now, you'll know.

Have you checked out the ADMIN GUIDE? It has a pretty clear example. You'll have to sort of write a procedure however if you "want to set some password validations of your own" - how else do you think you would incorporate logic like that???

standby database archive log not updating

ramesh, June 21, 2006 - 12:45 am UTC

Hi tom
i am working as oracle dba and i have aproble can u pls help me.
i have two database one is "prod" and second one is "stby" standby database in same machine. i did all primary work both primary and standby database , when give alter system archive log current or commit statment in primary database which created one archive log , which has automaticaly transfer to standby database . but my problem is that archive log is not update standby database ,moreover when i see the standby database which is their
pls telme how can i slove it
moreover even if i did "recover managed standby database timeout 60" is not updating which shows old one. ie current archive log is not updating.

Ramesh dba

Tom Kyte
June 21, 2006 - 4:35 pm UTC

"U" isn't here... sorry, don't know where "U" lives or what sites they frequent....

I would say "you did not configured standby correctly"

Please utilize support for installation/configuration.. Insufficient data here and I'd rather not have you post everything they would need (which would be the step by steps you did to create the standby in the first place)

Answering unrelated questions (you're too nice)

Kate Johnson, July 18, 2006 - 5:37 pm UTC

There are evil doers out there wasting my time (and others) posting responses to questions not intended for the thread.
Example: Thread titled "How to track logon & logout" was created, then during the course of the thread's history, someone asked you about archive logs on a standby database. I'm sure you have heard this complaint before, what can you do, right? (except not answer the question or tell them it is not relevant).

I on the other hand, have a followup question regarding the original thread intent and sadly cannot ask it.

Logon/logoff impact on system scalability

Vlado, September 14, 2006 - 9:55 am UTC

Using statspack we have been tracking the number of logons in our system (logons cumulative).
We average approx. 4 logons per second, which looks bad but I'm having trouble to quantify exactly how bad it is.
Is there any statistic available in Oracle (or Solaris) that could indicate how big of an impact it has on CPU or IO or memory utilization?

Tom Kyte
September 14, 2006 - 10:40 am UTC

it depends. It depends on what happens on your system as part of the logon.

It depends on dedicated versus shared server.
It on presence or lack of logon trigger
It depends on auditing
It depends on whether you are using the same account or different ones all of the time.

The only way I know to quantify things like that is to set up a test and measure

Tracking password changes

Mayse, January 18, 2008 - 3:07 pm UTC

Hi Tom,

Is it possible to track changes to the passwords Oracle 9i and later. I don't really care to know the passwords themselves (hashed or otherwise), but the fact the user changed the password and when. So the username or id, the date and time the password was changed and if possible the old and the new hashed/unhashed passwords.


Did I miss it?

Ma$e, January 23, 2008 - 3:07 pm UTC

Hi Tom,

I reviewed the link but I don't seem to find the views or tables where the information I am after is mentioned.


Tom Kyte
January 23, 2008 - 8:24 pm UTC

You implement a policy that says "you must redo your password every so often, you must not reuse your password for N times"

and then you just know it was done, it could not be otherwise.

or, you can implement a password complexity routine and log whatever you want, where ever you want.

auditing logons as sysdba

Rosalyn, September 05, 2008 - 4:39 pm UTC

Regarding securing the database

let me explain the environment

Oracle version EE
OS Solaris 5.10

We have 4 DBA in the OS dba group.
to login to the database in the server we doas follows
$>su - oracle
and then execute the sqlplus

This is because we do not have access to the sqlplus in our OS account.

Then we log in as sysdba connect as "/ as sysdba" or <oracle_username> as sysdba.
Now because we are authenticated we are able to log in.

Now if I look at the .aud files that is generated in the operating system

I can see the dbuser is "/" and client user is "oracle"

Now if I log in as myself (<oracledba_username> as sysdba) the .aud files have the same infomation
that is
dbuser is "/"
client user is "oracle"

I have locked SYS and SYSTEM account as you suggested.

There is no way I can figure out which dba from the 4 of us logged in.

How can this be fixed. How an I figure out which client user or which db_user logged in as sysdba

Can I do the following without breaching security

1)Each of the user from the dba group should be able to execute the sqlplus from their own OS user account.
if this is the right route to go. What is the best way to do this without making a hole in the security.
Is runnng the the right thing to do.
or is there another way to do this.
Fromwhat I read is not right thing to do because all users will get access to sqlplus.


2)What other options do I have to be able to get the correct OS user information in the .aud file in the OS

I did read Arup Nandas Project lockdown ( )
in which he suggested to change permissions on the oracle executable.
and always user listener to connect to the database.
I am not sure if I can do this at this point of time.

3)I would like to lock down the ORACLE OS user but then the dbas from the os dba group should be able to
execute the sqlplus from their own account. What is the safe way to do this.

I donot want to do an user identified externally yet .
we want the user to login as themself as sysdba with a password.

Please letme know if I have missed doing anything in auditing or setting up oracle.

Thanks for all you wonderful explanations.

Tom Kyte
September 05, 2008 - 5:20 pm UTC

$>su - oracle
and then execute the sqlplus

This is because we do not have access to the sqlplus in our OS account.

that is really bad - you cannot secure that, you need to stop that - they do not need and should not have access to the oracle OS account like that.

if you log into the OS as yourself, you would see yourself in the aud file:
Wed May 21 15:01:28 2008


can you "prove" otherwise - eg: issue "date", issue "id", issue "sqlplus / as sysdba" - exit, goto audit area and ls -ltr | tail, cat the file that matches the date.

A reader, September 05, 2008 - 7:35 pm UTC


A reader, September 05, 2008 - 7:35 pm UTC

last user logon

Sergio, December 02, 2008 - 1:57 pm UTC

If I only need known the last time a user logged in, what is best:

1 - Enable auditing and write some code that each some time select the max logon date from each user from audit trail and update this in some last_logon_table, then purge the audit trail.


2 - Put a logon trigger that update the last logon time in the last_logon_table.

In two case if there are some "problem" the user cant login, and in the two case I need to write some code.

Thank you very much for your excelent help for all of us.
Tom Kyte
December 09, 2008 - 9:08 am UTC

well, both cases require 'code'.

This is sort of a six one way, half a dozen the other. Either approach 'works'. Either approach has the "if there is some problem" problem (say the tablespace with the audit trail fills up in case 1..)

I might be tempted to use a very simple logon trigger

  merge into my_audit_trail
  using (select ........ 
             <from v$ tables, sys_context values from dual, whatever>)
  on (join condition)
  when matched then update
  when not matched then insert

last user logon

Sergio Soria, December 09, 2008 - 10:15 am UTC

Hi Tom,
thanks for you responce.

This logon trigger will be very used, and can produce some locks and problems to users.

What do you think about

merge into my_audit_trail
using (select ........
<from v$ tables, sys_context values from dual, whatever>)
on (join condition)
when matched then update
when not matched then insert
, ......);

Thanks again.
Tom Kyte
December 09, 2008 - 2:18 pm UTC

why would it cause a locking issue???

what problems do you envision?

please - no hypothetical "it MIGHT ..." - give us a for example of what evil you think this might do???

Last user logon

Sergio Soria, December 10, 2008 - 10:01 am UTC

The locking would be caused by a hang session of new user making the insert in my_audit_trail, or other not new makind the update.
I don't see any particular problem, I think about if table my_audit_trail lock then nobody can logon into the database, and I ask you if this hyphotetical situation can occurs. For your responce this is not likely.

Another question about this, the "last logon time" will be included in some version of Oracle database ? like a field in the dba_users or in some other way.
Tom Kyte
December 10, 2008 - 10:08 am UTC

the insert would happen and commit - it would not hang.

please explain how you think it would hang - once the session logs in - trigger fires, insert happens and commits (umm, sort of like the insert into sys.aud$ would.... apples - apples - both approaches insert+commit)

as far as we are concerned the last logon time is already available - via audit connect

Last user logon

Sergio Soria, December 30, 2008 - 6:44 am UTC

Hi Tom

I known that "the insert would happen and commit - it would not hang." but if hang nobody can logon to database.

I don't have no idea about how can happend, was the doubt and wanted to know your opinion.

Tom Kyte
January 05, 2009 - 9:09 am UTC

I am not at all sure what you are asking here.

Last user logon

Sergio Soria, January 05, 2009 - 1:38 pm UTC

Nothing, thanks for all.

A mysterious action "DROP TABLE" undetected

Lin Xian Han, August 24, 2011 - 11:19 pm UTC


Need your help as Oracle Detective.
After impdp action do, in range of 0 - 48 hours, drop table action happened.
When checking :
there is no DROP TABLE action. I don't know how could it be.

Very need your advice solving this mysterious event;

Tom Kyte
August 30, 2011 - 4:02 pm UTC

you don't say that you actually audited DROPs

you don't say if you are auditing SYSDBA

you do know many of your DBAs can probably modify the audit trail too - if you want to catch them - you'll need OS auditing, definitely for SYSDBA

columns timestamp and logoff_time since 11g

DUR, December 07, 2011 - 5:20 am UTC

Hi Tom,

Thanks for what you're doing here on forum - it helps me a lot.

I noticed that since version 11g there is no connect time presented in dba_audit... views after a session ends. I mean the (logoff_time - timestamp) = 0.

How can I see the connect time of a particular disconnected session ?

OK - sorry I can see separate records with logon action

DUR, December 09, 2011 - 5:54 am UTC

OK - sorry, I can see separate records with logon action...

create session or connect

A reader, January 13, 2012 - 6:49 am UTC


is there a difference, audit create session Or audit connect?
Tom Kyte
January 17, 2012 - 9:25 am UTC

they are synonymous.

In fact, you can "audit create session" and then "noaudit connect" to turn it off and vice versa.

create session or connect

A reader, February 28, 2012 - 1:49 am UTC


We want to NOT audit a specific user, is it possibly to do this;

audit create session by access;
noaudit create session by proxy_conn_user;

Or do you have to explicitly issue an AUDIT command for everyone except proxy_conn_user?

Tom Kyte
February 28, 2012 - 7:28 am UTC

the noaudit by user would only take away an audit by a user.

You would have to audit those you wish to audit.



Use the NOAUDIT statement to stop auditing operations previously enabled by the AUDIT statement.

The NOAUDIT statement must have the same syntax as the previous AUDIT statement. Further, it reverses the effects only of that particular statement. For example, suppose one AUDIT statement A enables auditing for a specific user. A second statement B enables auditing for all users. A NOAUDIT statement C to disable auditing for all users reverses statement B. However, statement C leaves statement A in effect and continues to audit the user that statement A specified.


Hassan Reda, February 28, 2012 - 2:51 am UTC

Is there are a way to populate v$session client_info column to AUD$ ?


A reader, February 28, 2012 - 10:17 am UTC


You said
<<You cannot know what application users are connecting from. The information is v$session is
suspect at best (just copy sqlplus.exe to blah.exe and see what happens -- you cannot rely on that
information in any way, shape or form.>>

OK, that is true but the session row has other client information

OSUSER VARCHAR2(30) Operating system client user name
PROCESS VARCHAR2(12) Operating system client process ID
MACHINE VARCHAR2(64) Operating system machine name
TERMINAL VARCHAR2(30) Operating system terminal name
PROGRAM VARCHAR2(48) Operating system program name

Is this reliable info or it can be changed by user too?

Of course, i am talking about client/server environemnt. for web oracle will capture the web server info.
Tom Kyte
February 28, 2012 - 10:46 am UTC

OSUSER - depends, how much control does the end user have other their machine. What OS's are they using.

process id is sort of meaningless for you.

terminal likely not useful either.

we already talked about program.

create session or connect

A reader, March 01, 2012 - 2:05 am UTC

the noaudit by user would only take away an audit by a user.

You would have to audit those you wish to audit.

Thanks Tom - for us, that means issuing an audit command 1000's of times as we have 1000's of users. Was hoping there was a command we can execute a few times for the few exceptions we have.

Login for secondary database

Sumanth Yampati, August 26, 2022 - 10:38 am UTC

Hi Tom,

There is a requirement where we have to track the logins of reporting users(Users who connects only to Secondary db i.e READ_ONLY). We had tried collecting data from v$active_session_history, but data in that table is not stable and may disappear within few hours. Can you please provide a stable solution for this?

Connor McDonald
August 30, 2022 - 1:29 am UTC

The standard audit facilties work (eg AUDIT SESSION) but since the database is readonly, the audit_trail parameter is ignored and writes to the OS. But this give the audit of logins as you want