Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

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

Answered by: Tom Kyte - Last updated: February 28, 2012 - 10:46 am UTC

Category: Database - 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.

Thanks.

Viji

and we 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.

and you rated our response

  (111 ratings)

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

Reviews

Trigger auditing

October 30, 2001 - 8:50 am UTC

Reviewer: Andre Whittick Nasser from Rio de Janeiro, Brazil

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
begin
insert into log_trail values (user, sysdate, 'LOGON');
commit;
end tr_logon;

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

Notes:

- 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

Followup  

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

October 30, 2001 - 10:10 am UTC

Reviewer: George Spears from Dayton, OH

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

October 30, 2001 - 10:30 am UTC

Reviewer: Ivan Korac from Ulm, Germany

The problem is handling of application-users wich could be connected from diferent places and through multiple sessions. It is possible for example with:
-- Trigger LOGONAUDITING
CREATE OR REPLACE TRIGGER logonauditing
AFTER LOGON ON application.schema
DECLARE
machinename VARCHAR2(64);
osuserid VARCHAR2(30);
session_id_part1 NUMBER;
session_id_part2 NUMBER;
CURSOR c1 IS
SELECT osuser, machine, sid, serial#
FROM v$session WHERE audsid = userenv( 'sessionid' );
BEGIN
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);
CLOSE c1;
COMMIT;
END;
/
- similar is needed for log off, and now You have all elements for corect counting of spent time of any user

Tom Kyte

Followup  

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

October 30, 2001 - 10:31 am UTC

Reviewer: Lester Burnham from Orlando, FL USA

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

October 30, 2001 - 12:18 pm UTC

Reviewer: Mohammed Al-moayed from Yemen

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

Followup  

October 30, 2001 - 12:48 pm UTC

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

See
</code> http://docs.oracle.com/docs/cd/A81042_01/DOC/server.816/a76956/audit.htm#1108 <code>
for all of the details.

Well...

October 30, 2001 - 12:35 pm UTC

Reviewer: Andre Whittick Nasser from Rio de Janeiro, Brazil

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

Followup  

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

USERNAME : TKYTE
USERHOST :
TERMINAL : TKYTE-DELL

TIMESTAMP : 08-may-2001 19:19:29
OWNER :
OBJ_NAME :
ACTION : 102
ACTION_NAME : LOGOFF BY CLEANUP
NEW_OWNER :
NEW_NAME :
OBJ_PRIVILEGE :
SYS_PRIVILEGE :
ADMIN_OPTION :
GRANTEE :
AUDIT_OPTION :
SES_ACTIONS :
LOGOFF_TIME : 08-may-2001 19:19:30
LOGOFF_LREAD : 79
LOGOFF_PREAD : 1
LOGOFF_LWRITE : 11
LOGOFF_DLOCK : 0
COMMENT_TEXT : Authenticated by: DATABASE
SESSIONID : 8233

ENTRYID : 1
STATEMENTID : 1
RETURNCODE : 0
PRIV_USED : CREATE SESSION
-----------------


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

October 30, 2001 - 1:15 pm UTC

Reviewer: Lester Burnham from Orlando, FL USA

> 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

October 31, 2001 - 12:21 am UTC

Reviewer: shamim ahmad from New delhi, India

tom
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

Followup  

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
as
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
begin
insert into audit_trail
select sysdate, 'LOGON', v.*
from v$session v
where sid = ( select sid from v$mystat where rownum = 1 );
commit;
end;
/

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


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


Do not miss flexibility

October 31, 2001 - 4:44 am UTC

Reviewer: Ivan Korac from Ulm, Germany

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

Followup  

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 ?

October 22, 2002 - 2:31 pm UTC

Reviewer: A Reader from USA

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

Followup  

October 22, 2002 - 3:47 pm UTC

audit_trail=true;

in the init.ora perhaps?

Ignore the above question.

October 22, 2002 - 3:28 pm UTC

Reviewer: A Reader from USA

Tom,
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

December 16, 2002 - 11:07 am UTC

Reviewer: Will from Austin

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

Followup  

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

April 10, 2003 - 7:37 am UTC

Reviewer: shihantha from sri lanka

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

Followup  

April 10, 2003 - 8:17 am UTC

audit connect whenever NOT successful;



persistence

April 11, 2003 - 1:07 am UTC

Reviewer: shihantha from sri lanka

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

thanks

oops

April 11, 2003 - 1:59 am UTC

Reviewer: shihantha from sri lanka

please ignore the earlier question.

it remains persistent untill we explicitly say NOAUDIT

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

Commit

April 17, 2003 - 9:26 am UTC

Reviewer: Steven Cooper from England

Tom,

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

e.g.
system@Dev9i> CONNECT SYS AS SYSDBA
Enter password: ********
Connected.
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>
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 :

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


Steven

Tom Kyte

Followup  

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)

re:Commit

April 17, 2003 - 11:23 am UTC

Reviewer: Steven Cooper from England

Tom,

Thanks, that works.

Steven

Some question about Auditing Connect

May 03, 2003 - 8:52 am UTC

Reviewer: Riaz Shahid from PRAL, Islamabad, Pakistan

Hello Tom !

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

(1) The time is >= 9:00 PM
OR
(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

May 15, 2003 - 7:30 am UTC

Reviewer: Igor from France

Hi

I have following trigger:

create or replace trigger sa2_logon_trigger
after logon on database --when (user='SA2')
declare
iworkgroup varchar2( 150 );
begin
if (user = 'SA2' ) then
sa.set_context_sa2;
else
begin
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;
end;

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

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

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?

Igor


Tom Kyte

Followup  

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?

May 28, 2003 - 2:47 pm UTC

Reviewer: Minnie from USA

Hi tom,
in your solution,

quote:
create table audit_trail
as
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
begin
insert into audit_trail
select sysdate, 'LOGON', v.*
from v$session v
where sid = ( select sid from v$mystat where rownum = 1 );
commit;
end;
/
create or replace trigger logoff_trigger
before logoff on database
begin
insert into audit_trail
select sysdate, 'LOGOFF', v.*
from v$session v
where sid = ( select sid from v$mystat where rownum = 1 );
commit;
end;
/
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
as
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
begin
insert into audit_trail
select sysdate, 'LOGON', v.*, null
from v$session v
where sid = ( select sid from v$mystat where rownum = 1 );
commit;
end;
/
create or replace trigger logoff_trigger
before logoff on database
begin
update audit_trail set x$logoff_time=sysdate
where sid = ( select sid from v$mystat where rownum=1 );
commit;
end;
/
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

Followup  

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

May 28, 2003 - 11:28 pm UTC

Reviewer: Sikandar Hayat from Pindi Bhattian - Pakistan

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

May 28, 2003 - 11:32 pm UTC

Reviewer: Sikandar Hayat from Pindi Bhattian - Pakistan


For Mr. Sikander Hayat

May 29, 2003 - 4:42 am UTC

Reviewer: Riaz Shahid from PRAL, Islamabad, Pakistan

Dear Sikandar !

AOA

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

Regards

Ya not available in PK

May 29, 2003 - 12:21 pm UTC

Reviewer: Sikandar Hayat from Pindi Bhattian - Pakistan

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.

sikandar_h@hotmail.com

Preventing multiple connections?

October 06, 2003 - 4:11 pm UTC

Reviewer: A reader

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

Followup  

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?

October 06, 2003 - 7:00 pm UTC

Reviewer: A reader

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

Followup  

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"



clarification

October 06, 2003 - 10:38 pm UTC

Reviewer: Sudhir from Lexington, KY USA

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.

Thanks

Tom Kyte

Followup  

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?

October 09, 2003 - 7:47 pm UTC

Reviewer: ht from California

Tom,
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?

Thanks,
ht


Tom Kyte

Followup  

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.

October 10, 2003 - 7:32 am UTC

Reviewer: Jamil

In Pakistan you can get this book from vengaurd Karachi.



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

October 10, 2003 - 11:11 am UTC

Reviewer: ht from California

Tom,
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?
Thanks,
ht

Tom Kyte

Followup  

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.

October 10, 2003 - 11:50 am UTC

Reviewer: ht from California

Tom,
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.
Thanks,
ht

Tom Kyte

Followup  

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

October 10, 2003 - 12:38 pm UTC

Reviewer: ht from California

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?
Thanks,
ht

Tom Kyte

Followup  

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

October 10, 2003 - 1:21 pm UTC

Reviewer: ht from California

Tom,
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.

ht

Tom Kyte

Followup  

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?

October 10, 2003 - 10:45 pm UTC

Reviewer: ht from California

Tom,
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?

Thanks,
ht

Tom Kyte

Followup  

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.

October 11, 2003 - 7:22 pm UTC

Reviewer: ht from California

Tom,
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.
ht

Tom Kyte

Followup  

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

October 12, 2003 - 3:14 am UTC

Reviewer: Jamil from Saudi

Hi Tom
When I try to access this topic
</code> http://download-east.oracle.com/docs/cd/A81042_01/DOC/server.816/a76956/audit.htm#1108
I am getting this error and the topic is unreadable  and it give this error message
http://otn.oracle.com/errors/404.html <code>
would you please help me how to get this topic display Properly

Best regards
Jamil Shaibani


Tom Kyte

Followup  

October 12, 2003 - 9:07 am UTC

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/audit.htm#1108 <code>

they moved the docs on otn :(

Multiple Logon Triggers

December 10, 2003 - 5:33 pm UTC

Reviewer: A reader

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?

Thanks.

Tom Kyte

Followup  

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

December 10, 2003 - 6:30 pm UTC

Reviewer: eric givler from hbg, pa

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

Followup  

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

February 06, 2004 - 9:30 am UTC

Reviewer: Junior from VA, USA

Tom:

"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

Followup  

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

February 23, 2004 - 4:24 pm UTC

Reviewer: jianhui from DC

Tom,
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

Followup  

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

February 24, 2004 - 12:01 pm UTC

Reviewer: Jianhui from DC

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

Followup  

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

February 24, 2004 - 6:33 pm UTC

Reviewer: jianhui from DC

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


Tom,
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).


Tom Kyte

Followup  

February 25, 2004 - 7:49 am UTC

read


</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5671284058977 <code>



That answered half of my question

February 25, 2004 - 10:00 am UTC

Reviewer: Jian from DC

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

Tom Kyte

Followup  

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

February 25, 2004 - 2:18 pm UTC

Reviewer: A reader

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

Tom Kyte

Followup  

February 25, 2004 - 2:50 pm UTC

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



auditing unknown user

March 17, 2004 - 4:18 am UTC

Reviewer: vinodh from India,Chennai

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.

Thanks,

Tom Kyte

Followup  

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

March 18, 2004 - 9:54 am UTC

Reviewer: A reader

Tom,
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.

Mike

Tom Kyte

Followup  

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.

March 18, 2004 - 10:18 am UTC

Reviewer: A reader

"
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
trigger"
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

Followup  

March 18, 2004 - 10:26 am UTC

login trigger on the database -- a DIY solution.

what's "a DIY solution"

March 18, 2004 - 10:34 am UTC

Reviewer: A reader


Tom Kyte

Followup  

March 18, 2004 - 10:34 am UTC

Do
It
Yourself

Tracking create session by access for unknow user

March 22, 2004 - 1:42 am UTC

Reviewer: Vinodh from India,Chennai

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

Followup  

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

March 23, 2004 - 2:07 am UTC

Reviewer: Vinodh from India, Chennai

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

Followup  

March 23, 2004 - 7:14 am UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96521/audit.htm#9992 <code>

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

Got it

March 23, 2004 - 4:54 am UTC

Reviewer: Vinodh from India, Chennai

Tom,

Thanks i got it.

Audited successfully.

audit created session by user1 by access;

Thanks,



Still searching for what i needed.

March 24, 2004 - 7:03 am UTC

Reviewer: Vinodh from India, Chennai

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

Thanks,



Tom Kyte

Followup  

March 24, 2004 - 9:06 am UTC

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

audited info

March 24, 2004 - 7:07 am UTC

Reviewer: Vinodh from India,Chennai

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.

Thanks,



Logon unknown user

March 28, 2004 - 9:56 pm UTC

Reviewer: Vinodh from India, Chennai

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.

thanks,


Tom Kyte

Followup  

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

March 29, 2004 - 8:05 am UTC

Reviewer: vinodh from India, Chennai

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

Followup  

March 29, 2004 - 10:37 am UTC

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




RE: Is anyone else?

March 29, 2004 - 10:51 am UTC

Reviewer: Mark A. Williams from Indianapolis, IN USA

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

Followup  

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?

March 29, 2004 - 11:17 am UTC

Reviewer: Kalita

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
or
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? :-)

Cheers

Tom Kyte

Followup  

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?

April 02, 2004 - 1:08 pm UTC

Reviewer: jian from DC

Tom,
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;

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

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.

Thanks! 

Tom Kyte

Followup  

April 02, 2004 - 1:55 pm UTC

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

Audit

April 02, 2004 - 2:24 pm UTC

Reviewer: A reader

audit session
or
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

Followup  

April 02, 2004 - 3:21 pm UTC

they are basically "the same"

April 05, 2004 - 10:58 am UTC

Reviewer: A reader

I'm using "audit session".

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

Thanks

Tom Kyte

Followup  

April 05, 2004 - 12:00 pm UTC

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

April 12, 2004 - 9:28 am UTC

Reviewer: A reader

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

Followup  

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 /
Connected.
ops$tkyte@ORA9IR2> @a
 
ACTION_NAME                 USERNAME                         COUNT(*)
--------------------------- ------------------------------ ----------
LOGON                       OPS$TKYTE                               1
LOGOFF                      OPS$TKYTE                               1
 
ops$tkyte@ORA9IR2> connect /
Connected.
ops$tkyte@ORA9IR2> @a
 
ACTION_NAME                 USERNAME                         COUNT(*)
--------------------------- ------------------------------ ----------
LOGON                       OPS$TKYTE                               1
LOGOFF                      OPS$TKYTE                               2
 
ops$tkyte@ORA9IR2> connect /
Connected.
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 9.2.0.4.0 - Production on Mon Apr 12 10:41:24 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - 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

April 13, 2004 - 12:47 pm UTC

Reviewer: Gill Gilmore from Auckland, NZ

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

April 14, 2004 - 6:11 am UTC

Reviewer: MEHMOOD MALIK from Karachi, Pakistan

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

Followup  

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.

April 14, 2004 - 9:46 am UTC

Reviewer: A reader

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

Followup  

April 14, 2004 - 10:52 am UTC

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



V$table information

June 07, 2004 - 3:25 am UTC

Reviewer: Goh Seong Hin from Malaysia

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.

Rgds
SHGoh

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

Tom Kyte

Followup  

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

June 15, 2004 - 4:04 am UTC

Reviewer: Goh Seong Hin from Malaysia

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.

Rgds
SHGOh



Tom Kyte

Followup  

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

June 16, 2004 - 3:19 am UTC

Reviewer: Goh Seong Hin from Malaysia

Dear Tom,

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

Rgds
SHGoh

Tom Kyte

Followup  

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

September 10, 2004 - 6:18 am UTC

Reviewer: Alain from France

Hello,

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

Followup  

September 10, 2004 - 9:19 am UTC

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

Behaviour of Logon/Logoff triggers in connection pooling Environment

October 27, 2004 - 12:42 am UTC

Reviewer: Tony from Chennai, INDIA

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

Followup  

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

November 04, 2004 - 5:07 pm UTC

Reviewer: Sanjay Jha from Toronto

We have a database running on 8.1.7.4 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

Followup  

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?

November 09, 2004 - 12:47 pm UTC

Reviewer: Sanjay Jha from Toronto

Tom,
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 8.1.7.4.

Tom Kyte

Followup  

November 09, 2004 - 1:26 pm UTC

contact support for that last question there.

Audit Connect and connection failure

December 09, 2004 - 11:04 am UTC

Reviewer: Mohini from Germany

Database: 9.2.0.5
OS: HP-UX 11i
Tom,
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.

Thanks.

Tom Kyte

Followup  

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

January 12, 2005 - 5:43 am UTC

Reviewer: Azharudeen from INDIA

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

Followup  

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

January 28, 2005 - 1:16 am UTC

Reviewer: ghazanfar

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

Followup  

January 28, 2005 - 7:20 am UTC

SQL> audit ....;

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

 

LOGOFF TRIGGER

March 02, 2005 - 6:44 pm UTC

Reviewer: A reader

Thanks again!

How to track logons and log-offs seperately

July 28, 2005 - 7:46 am UTC

Reviewer: Sanjaya Balasuriya from Colombo, Sri Lanka

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 ?

Thanks

Tom Kyte

Followup  

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. 

OBJ_PRIVILEGE

August 08, 2005 - 7:12 am UTC

Reviewer: Sanjaya Balasuriya from Colombo, Sri Lanka

Hi Tom,

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

Thanks.

Tom Kyte

Followup  

August 08, 2005 - 8:29 am UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96536/ch2205.htm#1307791 <code>

Meaning of the values in OBJ_PRIVILEGE

August 09, 2005 - 9:45 pm UTC

Reviewer: Sanjaya Balasuriya from Colombo, Sri Lanka

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;

---------N------
------G---------
----------G-----
G--G-GG--GGG----


Thanks in advance.

Tom Kyte

Followup  

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;

OBJ_PRIVILEGE
----------------
Y---------------

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

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;

OBJ_PRIVILEGE
----------------
Y---------------
---Y------------

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

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;

OBJ_PRIVILEGE
----------------
-----Y----------
Y---------------
---Y------------

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

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;

OBJ_PRIVILEGE
----------------
-----Y----------
------Y---------
Y---------------
---Y------------

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;

OBJ_PRIVILEGE
----------------
-----Y----------
------Y---------
Y---------------
---Y------------

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

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;

OBJ_PRIVILEGE
----------------
-----Y----------
------Y---------
---------Y------
Y---------------
---Y------------

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

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;

OBJ_PRIVILEGE
----------------
-----Y----------
------Y---------
---------Y------
----------Y-----
Y---------------
---Y------------

6 rows selected.

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

Grant succeeded.

ops$tkyte@ORA10GR1> select obj_privilege from dba_audit_trail;

OBJ_PRIVILEGE
----------------
-----Y----------
------Y---------
---------Y------
----------Y-----
-----------Y----
Y---------------
---Y------------

7 rows selected.
 

Thanks sir...

August 10, 2005 - 9:55 pm UTC

Reviewer: Sanjaya Balasuriya from Colombo, Sri Lanka

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.


September 21, 2005 - 10:32 am UTC

Reviewer: sal from chennai

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


October 31, 2005 - 5:39 pm UTC

Reviewer: A reader

desc logonaudittable
Name Null? Type
----------------------------------------- -------- --------------------------
EVENT VARCHAR2(10)
SID NUMBER
SERIAL# NUMBER
TIMESTAMP DATE
USERNAME VARCHAR2(30)
OSUSERID VARCHAR2(30)
MACHINENAME VARCHAR2(64)
PROGRAM VARCHAR2(80)

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 ?

thanks

Tom Kyte

Followup  

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)
last_val_of_column,
other lags.....
from your_table
where event in ( 'LOGON', 'LOGOFF' )
)
where event = 'LOGON';



October 31, 2005 - 5:51 pm UTC

Reviewer: A reader

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

December 01, 2005 - 5:08 am UTC

Reviewer: A reader

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

rgds
Praful

Tom Kyte

Followup  

December 01, 2005 - 12:38 pm UTC

select * from v$license

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

Concurrent Users

December 02, 2005 - 5:26 am UTC

Reviewer: Praful

Hi Tom,

The V$license shows the following output

SESSIONS_MAX 0
SESSIONS_WARNING 0
SESSIONS_CURRENT 3275
SESSIONS_HIGHWATER 3444
USERS_MAX 0

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

Followup  

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

December 02, 2005 - 11:50 am UTC

Reviewer: Praful


Hi Tom

thanx a lot
The info you provide is helpful to me

Thanx a lot once again


Last User Login

December 20, 2005 - 1:55 pm UTC

Reviewer: V from NY

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

Tom Kyte

Followup  

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$

February 15, 2006 - 5:02 pm UTC

Reviewer: A reader

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

Tom Kyte

Followup  

February 15, 2006 - 9:53 pm UTC

contact support.

Determine from which program the session logon

March 21, 2006 - 5:27 pm UTC

Reviewer: Sean from NJ, USA

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.

Sean


Tom Kyte

Followup  

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.

INACTIVE SESSION BEHAVIOR

May 04, 2006 - 9:08 am UTC

Reviewer: Mark from Boston, MA

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 /

SID SERIAL# LOGON_TIME LAST_CALL_E STATUS
---------- ---------- -------------------- ----------- --------
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 /

VSYSDATE
--------------------
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):

SELECT COUNT(*)
FROM dual
WHERE EXISTS(SELECT NULL
FROM sys.user$ u1, sys.sysauth$ sa
WHERE u1.user# = sa.privilege#
AND sa.grantee# = UID
AND u1.name = 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 8.1.7.4 Standard.

Should I worry about this?

Regards,
Mark


Tom Kyte

Followup  

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

May 31, 2006 - 2:37 am UTC

Reviewer: Ram from INDIA

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


Thanks
Ram

Tom Kyte

Followup  

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

June 21, 2006 - 12:45 am UTC

Reviewer: ramesh from india

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

Followup  

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)

July 18, 2006 - 5:37 pm UTC

Reviewer: Kate Johnson from Phoenix, AZ USA

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

September 14, 2006 - 9:55 am UTC

Reviewer: Vlado from Knoxville, TN

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

Followup  

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

January 18, 2008 - 3:07 pm UTC

Reviewer: Mayse from US

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.

Thanks
Ma$e

Did I miss it?

January 23, 2008 - 3:07 pm UTC

Reviewer: Ma$e

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.

Thanks

Tom Kyte

Followup  

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

September 05, 2008 - 4:39 pm UTC

Reviewer: Rosalyn from TX, USA

Regarding securing the database

let me explain the environment

Oracle version 10.2.0.3 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.
and
O7_DICTIONARY_ACCESSIBILITY=FALSE



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 changePerm.sh the right thing to do.
or is there another way to do this.
Fromwhat I read changePerm.sh is not right thing to do because all users will get access to sqlplus.

or

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 ( http://www.oracle.com/technology/pub/articles/project_lockdown/index.html )
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

Followup  

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
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: tkyte
CLIENT TERMINAL: pts/3
STATUS: 0

......


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.

September 05, 2008 - 7:35 pm UTC

Reviewer: A reader


audits

September 05, 2008 - 7:35 pm UTC

Reviewer: A reader


last user logon

December 02, 2008 - 1:57 pm UTC

Reviewer: Sergio from Buenos Aires, Argentina

Tom,
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.

or

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

Followup  

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

begin
  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
end;



last user logon

December 09, 2008 - 10:15 am UTC

Reviewer: Sergio Soria from Buenos Aires, Argentina

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

begin
DBMS_JOB.SUBMIT(..... ,
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
, ......);
end;

Thanks again.
Tom Kyte

Followup  

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

December 10, 2008 - 10:01 am UTC

Reviewer: Sergio Soria from Buenos Aires, Argentina

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

Followup  

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

December 30, 2008 - 6:44 am UTC

Reviewer: Sergio Soria from Buenos Aires, Argentina

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.

Thanks
Tom Kyte

Followup  

January 05, 2009 - 9:09 am UTC

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

Last user logon

January 05, 2009 - 1:38 pm UTC

Reviewer: Sergio Soria from Buenos Aires, Argentina

Nothing, thanks for all.

A mysterious action "DROP TABLE" undetected

August 24, 2011 - 11:19 pm UTC

Reviewer: Lin Xian Han from Indonesia

Sir,

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

Very need your advice solving this mysterious event;

Thx.
Tom Kyte

Followup  

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

December 07, 2011 - 5:20 am UTC

Reviewer: DUR from PL

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

December 09, 2011 - 5:54 am UTC

Reviewer: DUR from PL

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

create session or connect

January 13, 2012 - 6:49 am UTC

Reviewer: A reader

tom,

is there a difference, audit create session Or audit connect?
Tom Kyte

Followup  

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

February 28, 2012 - 1:49 am UTC

Reviewer: A reader

Tom,

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

Followup  

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.

...
<quote>
NOAUDIT

Purpose

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.
</quote>

AUD$

February 28, 2012 - 2:51 am UTC

Reviewer: Hassan Reda from Egypt

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

session

February 28, 2012 - 10:17 am UTC

Reviewer: A reader

Tom:

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

http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2088.htm

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

Followup  

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

March 01, 2012 - 2:05 am UTC

Reviewer: A reader

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

You would have to audit those you wish to audit.
</QUOTE>

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.