ORA-00604: error occurred at recursive SQL level 1

914302
    Dear ALL

    I performed Full Database expdp and impdp from Production to test server its successful but while creating any user or changing password its shows me error below

    SQL> create user bam identified by bam
    2 default tablespace users
    3 temporary tablespace temp
    4 quota unlimited on users;
    create user bam identified by bam
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at line 7


    SQL> alter user system identified by 12345;
    alter user system identified by 12345
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at line 7

    kindly help me to resolve this issue.

    Thanks.
      • 1. Re: ORA-00604: error occurred at recursive SQL level 1
        Herald ten Dam
        Hi,

        is there a logon trigger on this database?
        select trigger_name from dba_triggers where triggering_event like '%LOGON%';
        If so, disable the trigger or modify code so it will work

        Herald ten Dam
        http://htendam.wordpress.com
        • 2. Re: ORA-00604: error occurred at recursive SQL level 1
          914302
          Hi Herald

          SQL> select trigger_name from dba_triggers where triggering_event like '%LOGON%';

          TRIGGER_NAME
          ------------------------------
          LOGON_SUCCESS

          I disabled the trigger

          SQL> select trigger_name,STATUS from dba_triggers where triggering_event like '%LOGON%';

          TRIGGER_NAME STATUS
          ------------------------------ --------
          LOGON_SUCCESS DISABLED

          But the problem still exists..

          SQL> alter user system identified by 12345;
          alter user system identified by system01
          *
          ERROR at line 1:
          ORA-00604: error occurred at recursive SQL level 1
          ORA-06502: PL/SQL: numeric or value error: character string buffer too small
          ORA-06512: at line 7

          Thanks.
          • 3. Re: ORA-00604: error occurred at recursive SQL level 1
            Lukasz Mastalerz
            Hi,

            Do you have any password verify function on your default profile?
            SELECT profile, resource_name, limit 
            FROM   dba_profiles 
            WHERE resource_name = 'PASSWORD_VERIFY_FUNCTION';
            Lukasz
            • 4. Re: ORA-00604: error occurred at recursive SQL level 1
              914302
              Hi Lukasz

              SQL> SELECT profile, resource_name, limit FROM dba_profiles WHERE resource_name = 'PASSWORD_VERIFY_FUNCTION';

              PROFILE RESOURCE_NAME
              ------------------------------ --------------------------------
              LIMIT
              ----------------------------------------
              DEFAULT PASSWORD_VERIFY_FUNCTION
              NULL

              MONITORING_PROFILE PASSWORD_VERIFY_FUNCTION
              DEFAULT

              KICKOUT PASSWORD_VERIFY_FUNCTION
              DEFAULT


              Thanks.
              • 5. Re: ORA-00604: error occurred at recursive SQL level 1
                Lukasz Mastalerz
                Ok, looks like a trigger. Could you check what 'CREATE' triggers do you have?
                SELECT owner, trigger_name, trigger_type, triggering_event, base_object_type 
                FROM    dba_triggers 
                WHERE triggering_event LIKE 'CREATE%';
                Lukasz
                • 6. Re: ORA-00604: error occurred at recursive SQL level 1
                  914302
                  Hi Lukasz

                  sorry for late reply, here is the o/p

                  SQL> SELECT owner, trigger_name, trigger_type, triggering_event, base_object_type FROM dba_triggers WHERE triggering_event LIKE 'CREATE%';

                  OWNER TRIGGER_NAME TRIGGER_TYPE
                  ------------------------------ ------------------------------ ----------------
                  TRIGGERING_EVENT
                  --------------------------------------------------------------------------------
                  BASE_OBJECT_TYPE
                  ----------------
                  WMSYS NO_VM_DDL BEFORE EVENT
                  CREATE OR ALTER OR DROP OR RENAME
                  DATABASE

                  SYS CDC_CREATE_CTABLE_AFTER AFTER EVENT
                  CREATE
                  DATABASE

                  OWNER TRIGGER_NAME TRIGGER_TYPE
                  ------------------------------ ------------------------------ ----------------
                  TRIGGERING_EVENT
                  --------------------------------------------------------------------------------
                  BASE_OBJECT_TYPE
                  ----------------

                  SYS CDC_CREATE_CTABLE_BEFORE BEFORE EVENT
                  CREATE
                  DATABASE

                  EXFSYS EXPFIL_RESTRICT_TYPEEVOLVE BEFORE EVENT
                  CREATE OR ALTER

                  OWNER TRIGGER_NAME TRIGGER_TYPE
                  ------------------------------ ------------------------------ ----------------
                  TRIGGERING_EVENT
                  --------------------------------------------------------------------------------
                  BASE_OBJECT_TYPE
                  ----------------
                  DATABASE

                  MDSYS SDO_ST_SYN_CREATE BEFORE EVENT
                  CREATE
                  DATABASE


                  Thanks.
                  • 7. Re: ORA-00604: error occurred at recursive SQL level 1
                    Solomon Yakobson
                    Issue:
                    column triggering_event format a40
                    select distinct triggering_event,status from dba_triggers;
                    and post results.

                    SY.
                    • 8. Re: ORA-00604: error occurred at recursive SQL level 1
                      914302
                      Hi SY

                      SQL> column triggering_event format a40
                      SQL> select distinct triggering_event,status from dba_triggers;

                      TRIGGERING_EVENT STATUS
                      ---------------------------------------- --------
                      INSERT OR UPDATE OR DELETE ENABLED
                      DROP ENABLED
                      DROP DISABLED
                      CREATE OR ALTER OR DROP OR RENAME DISABLED
                      DDL ENABLED
                      CREATE ENABLED
                      INSERT OR UPDATE ENABLED
                      CREATE DISABLED
                      ALTER OR RENAME DISABLED
                      TRUNCATE DISABLED
                      DROP OR TRUNCATE ENABLED

                      TRIGGERING_EVENT STATUS
                      ---------------------------------------- --------
                      SHUTDOWN ENABLED
                      TRUNCATE ENABLED
                      DELETE ENABLED
                      ALTER DISABLED
                      CREATE OR ALTER DISABLED
                      INSERT ENABLED
                      UPDATE ENABLED
                      INSERT DISABLED
                      UPDATE DISABLED
                      STARTUP ENABLED
                      UPDATE OR DELETE ENABLED

                      TRIGGERING_EVENT STATUS
                      ---------------------------------------- --------
                      RENAME ENABLED
                      ERROR ENABLED

                      24 rows selected.
                      Thanks..
                      • 9. Re: ORA-00604: error occurred at recursive SQL level 1
                        Solomon Yakobson
                        You have some DDL, CREATE & ERROR triggers enabled. Disable them and give it a try.

                        SY.
                        • 10. Re: ORA-00604: error occurred at recursive SQL level 1
                          914302
                          Dear SY

                          As you said to disable trigger on DDL, CREATE & ERROR , I checked in production server its same.

                          I am getting problem in Test Server after making full expdp and impdp.

                          Thanks.
                          • 11. Re: ORA-00604: error occurred at recursive SQL level 1
                            Osama_Mustafa
                            Cause: An arithmetic, numeric, string, conversion, or constraint error
                            occurred.
                            For example, this error occurs if an attempt is made to assign the
                            value NULL to a variable declared NOT NULL, or if an attempt is made
                            to assign an integer larger than 99 to a variable declared NUMBER(2).
                            Action: Change the data, how it is manipulated, or how it is declared so that
                            values do not violate constraints.
                            • 12. Re: ORA-00604: error occurred at recursive SQL level 1
                              914302
                              Dear Osama

                              Can you explain me how to change data, whenever I am taking full import I got this error.

                              Please explain the procedure.

                              Thanks
                              • 13. Re: ORA-00604: error occurred at recursive SQL level 1
                                Osama_Mustafa
                                did you try to use
                                SET SERVEROUT ON SIZE 1000000
                                OR
                                SET SERVEROUT ON SIZE Unlimited .

                                Create user osama identified by Osama ;


                                Post output

                                Osama ...

                                Edited by: Osama-mustafa on Jul 8, 2012 3:20 PM
                                • 14. Re: ORA-00604: error occurred at recursive SQL level 1
                                  Solomon Yakobson
                                  911299 wrote:

                                  As you said to disable trigger on DDL, CREATE & ERROR , I checked in production server its same.

                                  I am getting problem in Test Server after making full expdp and impdp.
                                  So did you disbale DDL, CREATE & ERROR triggers on Test Server? It has to be some DDL trigger:
                                  SQL> create or replace
                                    2    trigger ORA_06502
                                    3      after alter
                                    4      on database
                                    5      declare
                                    6          v_str1 varchar2(1);
                                    7          v_str2 varchar2(3);
                                    8      begin
                                    9          v_str2 := 'ABC';
                                   10          v_str1 := v_str2;
                                   11  end;
                                   12  /
                                  
                                  Trigger created.
                                  
                                  SQL> alter user u1 identified by u1
                                    2  /
                                  alter user u1 identified by u1
                                  *
                                  ERROR at line 1:
                                  ORA-00604: error occurred at recursive SQL level 1
                                  ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                                  ORA-06512: at line 6
                                  Now why it works in Prod but fails in Test. Most likely Test has multi-byte character set and Prod does not. Or, at least Prod & Test have different character sets. Now assume some trigger, e.g. AFTER ALTER declares a variable X VARCHAR2(10 BYTE). If Prod has single byte character set X can hold up to 10 characters. However if Test has multi-byte character set X can't hold 10 characters and therefore attempt to assign 10 character string to X will raise ORA-06502.

                                  SY.
                                  1 2 上一个 下一个