Home » RDBMS Server » Server Administration » how to resolve the error?
how to resolve the error? [message #188310] Thu, 17 August 2006 21:11 Go to next message
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(Cool
LOCKWAIT VARCHAR2(Cool
STATUS VARCHAR2(Cool
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(Cool
PDDL_STATUS VARCHAR2(Cool
PQ_STATUS VARCHAR2(Cool

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 Go to previous message
BlackSwan
Messages: 25033
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
Previous Topic: 64 bit to 32 bit
Next Topic: Arhive log file suddenly increased
Goto Forum:
  


Current Time: Sat Dec 03 07:44:06 CST 2016

Total time taken to generate the page: 0.09575 seconds