Hi Jeff,

Thanks for the input.

Here is what got from trace file when I connected as internal :
-----------------
*** 2003-04-04 16:22:13.951
*** SESSION ID:(20.571) 2003-04-04 16:22:13.948
Error in executing triggers on connect internal
*** 2003-04-04 16:22:13.951
ksedmp: internal or fatal error
-----------------

Thanks to the above alert I was able to findout which SYS trigger was causing problem.
so it was after logon trigger written a dev DBA. He said that it worked before and
users were able to logon...


SQL>sho user
USER is "SYS"
SQL>select 'Create or Replace trigger ',description,trigger_body
2 from user_triggers where trigger_name = 'RESTRICT_IP';

Create or Replace trigger
restrict_ip
after logon on database
declare
c_exclude varchar2(16) := ' ';
c_exclude1 varchar2(16) := '19.40.60.58';
c_exclude2 varchar2(16) := '19.9.100.1';
c_exclude3 varchar2(16) := '19.9.100.2';
begin
if ( sys_context('userenv','ip_address') = c_exclude1 )
then
raise_application_error( -20001, 'connection refused' );
end if;
if ( sys_context('userenv','ip_address') = c_exclude2 )
then
raise_application_error( -20001, 'connection refused' );
end if;
if ( sys_context('userenv','ip_address') = c_exclude3 )
then
raise_application_error( -20001, 'connection refused' );
end if;
end;


1 row selected.

SQL>alter trigger RESTRICT_IP disable ;

Trigger altered.

SQL>connect usert
Enter password:
Connected.

SQL>connect internal
Connected.
SQL>alter trigger RESTRICT_IP enable ;

Trigger altered.

SQL>connect usert
Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.STANDARD", line 609
ORA-06512: at line 7

Is there anyway I can write this trigger properly ?

Regards,