DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: ORA-06512: at "SYS.STANDARD"

Hybrid View

  1. #1
    Join Date
    Nov 2002
    Location
    Dublin, Ireland
    Posts
    133

    ORA-06512: at "SYS.STANDARD"

    Hi DBAs,

    I am on Oracle8i Release 8.1.7.4.0.
    Currently I am having below problem on dev box.

    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

    This is the first time I hit this problem - how do I do to solve this issue

    Thanks in advance.

    Regards,
    Giani

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Hmmm. Odds are your data dictionary is messed up.
    Jeff Hunter

  3. #3
    Join Date
    Nov 2002
    Location
    Dublin, Ireland
    Posts
    133
    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,
    Giani

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by Giani
    Is there anyway I can write this trigger properly ?
    You mean "Is there anyway I can write this trigger properly, so it does not look like spagheti code"?

    Sure, something like:
    Code:
    Create or Replace trigger
    restrict_ip
    after logon on database
    declare
      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')
        IN (c_exclude1,c_exclude2,c_exclude3 )
      then
        raise_application_error(-20001,'connection refused');
      end if;
    end;
    It performs exactly the same thing your trigger does, but doesn't it look much more compact?

    Now I don't thing the triger is what is causing your troubles, the trigger is just the manifestation of the real problem you have in your database. Because I'm almost certain the real reason for your troubles lies in the table SYS.DUAL!

    What does this query return:
    SELECT COUNT(*) FROM dual;

    I bet it returns 2 or more. Someone must have inserted you aditional rows in your table DUAL. Delete all rows but one from there and you'll be fine.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Nov 2002
    Location
    Dublin, Ireland
    Posts
    133
    Thanks a lot jmodic!
    You are absolutely right, my problem was on dual table.

    Regards,
    Giani

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width