1 2 Previous Next 22 Replies Latest reply on Jul 9, 2012 5:57 AM by 914302

    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 Previous Next