| how to resolve the error? [message #188310] |
Thu, 17 August 2006 21:11  |
Tom_webex
Messages: 13
Registered: July 2006
Location: China
|
Junior Member |
|
|
I use the sys user create the following script to search the user login&logout:
create table audit_log(
session_id number,
login_on_time date,
login_off_time date,
user_in_db varchar2(64),
machine varchar2(64),
ip_address varchar2(128),
run_program varchar2(64)
);
/
create trigger login_info after logon on database
Begin
insert into audit_log(
session_id,
login_on_time,
login_off_time,
user_in_db,
machine,
ip_address,
run_program)
select AUDSID,
sysdate,
null,
ltrim(rtrim(sys.login_user)),
machine,
ltrim(rtrim(SYS_CONTEXT('USERENV','IP_ADDRESS'))),
program
from v$session
where AUDSID=USERENV('SESSIONID');
END;
/
create trigger logout_info before logoff on database
Begin
update audit_log set login_off_time=sysdate where session_id=USERENV('SESSIONID');
exception when others then
null;
END;
/
SQL> desc v$session
Name Null? Type
----------------------------------------- -------- -------------
SADDR RAW(4)
SID NUMBER
SERIAL# NUMBER
AUDSID NUMBER
PADDR RAW(4)
USER# NUMBER
USERNAME VARCHAR2(30)
COMMAND NUMBER
OWNERID NUMBER
TADDR VARCHAR2(
LOCKWAIT VARCHAR2(
STATUS VARCHAR2(
SERVER VARCHAR2(9)
SCHEMA# NUMBER
SCHEMANAME VARCHAR2(30)
OSUSER VARCHAR2(30)
PROCESS VARCHAR2(9)
MACHINE VARCHAR2(64)
TERMINAL VARCHAR2(16)
PROGRAM VARCHAR2(64)
TYPE VARCHAR2(10)
SQL_ADDRESS RAW(4)
SQL_HASH_VALUE NUMBER
PREV_SQL_ADDR RAW(4)
PREV_HASH_VALUE NUMBER
MODULE VARCHAR2(48)
MODULE_HASH NUMBER
ACTION VARCHAR2(32)
ACTION_HASH NUMBER
CLIENT_INFO VARCHAR2(64)
FIXED_TABLE_SEQUENCE NUMBER
ROW_WAIT_OBJ# NUMBER
ROW_WAIT_FILE# NUMBER
ROW_WAIT_BLOCK# NUMBER
ROW_WAIT_ROW# NUMBER
LOGON_TIME DATE
LAST_CALL_ET NUMBER
PDML_ENABLED VARCHAR2(3)
FAILOVER_TYPE VARCHAR2(13)
FAILOVER_METHOD VARCHAR2(10)
FAILED_OVER VARCHAR2(3)
RESOURCE_CONSUMER_GROUP VARCHAR2(32)
PDML_STATUS VARCHAR2(
PDDL_STATUS VARCHAR2(
PQ_STATUS VARCHAR2(
SQL>
I have check the field in table audit_log and view v$session,it is ok. But I still get the error message from the trace file:
*** SESSION ID:(430.12902) 2006-08-18 08:27:47.823
Skipped error 604 during the execution of SYS.LOGIN_INFO
*** 2006-08-18 08:27:47.839
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06512: at line 2
Who can help me?Thanks very much!
|
|
|
|
| Re: how to resolve the error? [message #188421 is a reply to message #188310] |
Fri, 18 August 2006 08:36  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
select AUDSID,
sysdate,
null,
ltrim(rtrim(sys.login_user)),
machine,
ltrim(rtrim(SYS_CONTEXT('USERENV','IP_ADDRESS'))),
program
from v$session
where AUDSID=USERENV('SESSIONID');
Show complete cut & paste input of above & the results via SQL*Plus
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
