10 Replies Latest reply on Sep 5, 2012 11:46 PM by Srini Chavali-Oracle

    How to fix this error: IMP-00003: ORACLE error 1435 encountered

    958907
      Hello,

      I am new to oracle database. I am trying import a data dump file *.dmp into my test database. I am using Oracle Database 11g. I have created "datatest" to be the user and I granted all possible rights that was there. :) but still getting this error message.

      Does Oracle has a GUI tool somewhere I can try or how I can fix this issue? PLEASE HELP.

      Below are the actions taken:
      =========================================================================

      C:\app\Administrator\admin\ORCLENT1\dpdump>imp

      Import: Release 11.2.0.1.0 - Production on Wed Aug 29 22:32:39 2012

      Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

      Username: datatest
      Password:

      Connected to: Personal Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      Import data only (yes/no): no > no

      Import file: EXPDAT.DMP > expdat.dmp

      Enter insert buffer size (minimum is 8192) 30720> 8299

      Export file created by EXPORT:V11.02.00 via conventional path

      Warning: the objects were exported by SYSTEM, not by you

      import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
      import server uses AL32UTF8 character set (possible charset conversion)
      List contents of import file only (yes/no): no > no

      Ignore create error due to object existence (yes/no): no > no

      Import grants (yes/no): yes > yes

      Import table data (yes/no): yes > yes

      Import entire export file (yes/no): no > yes

      . importing SYSTEM's objects into DATATEST
      . importing DW_OWNER's objects into DW_OWNER
      IMP-00003: ORACLE error 1435 encountered
      ORA-01435: user does not exist
      Import terminated successfully with warnings.

      =============================================================
        • 1. Re: How to fix this error: IMP-00003: ORACLE error 1435 encountered
          Sunny kichloo
          Import entire export file (yes/no): no > yes
          
          . importing SYSTEM's objects into DATATEST
          . importing DW_OWNER's objects into DW_OWNER
          IMP-00003: ORACLE error 1435 encountered
          ORA-01435: user does not exist
          The user DW_OWNER is not present in your target database that is why you are getting the error.

          Also if you want to import DW_OWNER data into DATATEST user you have to use fromuser and touser clause

          This doc will help

          http://docs.oracle.com/cd/E14072_01/server.112/e10701/original_import.htm#



          In your case it will be

          imp username/password file=expdat.dmp log=imp.log fromuser=DW_OWNER touser=DATATEST
          1 person found this helpful
          • 2. Re: How to fix this error: IMP-00003: ORACLE error 1435 encountered
            Srini Chavali-Oracle
            Pl also pay attention to this warning

            >
            ...
            import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
            import server uses AL32UTF8 character set (possible charset conversion)
            ...
            >

            You need to set NLS_LANG to AL32UTF8 before performing the import. See this MOS Doc

            NLS considerations in Import/Export - Frequently Asked Questions [ID 227332.1]

            HTH
            Srini
            • 3. Re: How to fix this error: IMP-00003: ORACLE error 1435 encountered
              958907
              Thanks for the pointer, I actually double checked on my database by running this line
              select * from NLS_DATABASE_PARAMETERS. It returns with "AL32UTF8"

              Is there anything else I should check? Many thanks.

              Edited by: user1186998 on Sep 4, 2012 2:14 PM
              • 4. Re: How to fix this error: IMP-00003: ORACLE error 1435 encountered
                958907
                Thanks for helping with the script. It is very helpful, especially for newbie like me. :) When I run the script, there seems to be a lot of error during this process. Here is the log. Any suggestions how I can fix this?

                =====================================================

                Connected to: Personal Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
                With the Partitioning, OLAP, Data Mining and Real Application Testing options

                Export file created by EXPORT:V11.02.00 via conventional path

                Warning: the objects were exported by SYSTEM, not by you

                import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
                import server uses AL32UTF8 character set (possible charset conversion)
                . . importing table "MVW_MATCH_STATS" 335481 rows imported
                . . importing table "MVW_PLAYER_AGG" 34937 rows imported
                . . importing table "MVW_PLAYER_TOURNAMENT_AGG" 519174 rows imported
                . . importing table "MVW_TOURNAMENT_AGG" 12785 rows imported
                . . importing table "MVW_UT_AGG1" 8 rows imported
                . . importing table "TMP_CATEGORY_ATP" 29999 rows imported
                . . importing table "TMP_CATEGORY_WTA" 29999 rows imported
                . . importing table "TMP_IMPORT_STATS_PLAYER1" 32578 rows imported
                . . importing table "TMP_MATCH_COURT_TYPE" 335562 rows imported
                . . importing table "TMP_PLAYER_ATP" 47070 rows imported
                . . importing table "TMP_RESULT_COMBINATIONS" 16889 rows imported
                . . importing table "TMP_STADIA" 852 rows imported
                . . importing table "TMP_TOUR_ATP" 16762 rows imported
                . . importing table "TMP_TOUR_STADIA" 5222 rows imported
                . . importing table "TMP_TOUR_WTA" 9000 rows imported
                . . importing table "W_COURT_TYPE_D" 7 rows imported
                . . importing table "W_DATE_D" 22330 rows imported
                . . importing table "W_MATCH_D" 506408 rows imported
                . . importing table "W_MATCH_F" 1012812 rows imported
                . . importing table "W_MINUTE_D" 1440 rows imported
                . . importing table "W_PLAYER_D" 35305 rows imported
                . . importing table "W_PLAYER_D_F" 23538 rows imported
                . . importing table "W_PLAYER_D_M" 45804 rows imported
                . . importing table "W_ROUND_D" 19 rows imported
                . . importing table "W_STADIUM_D" 428 rows imported
                . . importing table "W_TOURNAMENT_D" 12882 rows imported
                . . importing table "W_TOURNAMENT_F" 433111 rows imported
                IMP-00017: following statement failed with ORACLE error 959:
                "CREATE TABLE "W_USAGE_TRACKING" ("USER_NAME" VARCHAR2(128), "REPOSITORY_NAM"
                "E" VARCHAR2(128), "SUBJECT_AREA_NAME" VARCHAR2(128), "NODE_ID" VARCHAR2(15)"
                ", "START_TS" DATE, "START_DT" DATE, "START_HOUR_MIN" CHAR(5), "END_TS" DATE"
                ", "END_DT" DATE, "END_HOUR_MIN" CHAR(5), "QUERY_TEXT" VARCHAR2(1024), "QUER"
                "Y_BLOB" CLOB, "QUERY_KEY" VARCHAR2(128), "SUCCESS_FLG" NUMBER(10, 0), "ROW_"
                "COUNT" NUMBER(10, 0), "TOTAL_TIME_SEC" NUMBER(10, 0), "COMPILE_TIME_SEC" NU"
                "MBER(10, 0), "NUM_DB_QUERY" NUMBER(10, 0), "CUM_DB_TIME_SEC" NUMBER(10, 0),"
                " "CUM_NUM_DB_ROW" NUMBER(10, 0), "CACHE_IND_FLG" CHAR(1) NOT NULL ENABLE, ""
                "QUERY_SRC_CD" VARCHAR2(30), "SAW_SRC_PATH" VARCHAR2(250), "SAW_DASHBOARD" V"
                "ARCHAR2(150), "SAW_DASHBOARD_PG" VARCHAR2(150), "PRESENTATION_NAME" VARCHAR"
                "2(128), "ERROR_TEXT" VARCHAR2(250), "IMPERSONATOR_USER_NAME" VARCHAR2(128),"
                " "NUM_CACHE_INSERTED" NUMBER(10, 0), "NUM_CACHE_HITS" NUMBER(10, 0), "QUERY"
                "_DATE_WID" NUMBER(10, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 ST"
                "ORAGE(INITIAL 3145728 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS"
                " 1 BUFFER_POOL DEFAULT) TABLESPACE "DW_DATA" NOLOGGING NOCOMPRESS LOB ("QUE"
                "RY_BLOB") STORE AS BASICFILE (TABLESPACE "DW_DATA" ENABLE STORAGE IN ROW C"
                "HUNK 8192 RETENTION NOCACHE NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 "
                "MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
                IMP-00003: ORACLE error 959 encountered
                ORA-00959: tablespace 'DW_DATA' does not exist
                . . importing table "W_USER" 8 rows imported
                IMP-00017: following statement failed with ORACLE error 942:
                "CREATE SNAPSHOT "MVW_UT_AGG1" ( "USER_NAME","QUERY_COUNT","ROW_COUNT" ) US"
                "ING ("MVW_UT_AGG1", (9, 'ORCL', 1, 0, 0, "DW_OWNER", "W_USAGE_TRACKING", '2"
                "012-05-31:23:56:48', 8, 74659, '2012-05-31:23:56:48', '', 1, '0280', 281368"
                "84, 0, NULL), 2101313, 10, ('1950-01-01:12:00:00', 25, 0, 0, 28136884, 0, 2"
                "098180, 2, NULL, NULL)) REFRESH COMPLETE AS"
                "SELECT "
                "user_name,"
                "count(*) as query_count,"
                "sum(row_count) as row_count"
                "FROM W_USAGE_TRACKING "
                "group by user_name"
                IMP-00003: ORACLE error 942 encountered
                ORA-00942: table or view does not exist
                IMP-00017: following statement failed with ORACLE error 12003:
                "ALTER SNAPSHOT "MVW_UT_AGG1" COMPILE"
                IMP-00003: ORACLE error 12003 encountered
                ORA-12003: materialized view "YANXIZAOTENNIS"."MVW_UT_AGG1" does not exist
                About to enable constraints...
                Import terminated successfully with warnings.


                ==============================
                • 5. Re: How to fix this error: IMP-00003: ORACLE error 1435 encountered
                  Srini Chavali-Oracle
                  user1186998 wrote:
                  Thanks for the pointer, I actually double checked on my database by running this line
                  select * from NLS_DATABASE_PARAMETERS. It returns with "AL32UTF8"

                  Is there anything else I should check? Many thanks.

                  Edited by: user1186998 on Sep 4, 2012 2:14 PM
                  Yes - NLS_LANG is a client-side setting - you have checked a database setting.

                  Issue this command before invoking imp
                  set NLS_LANG = AMERICAN_AMERICA.AL32UTF8
                  HTH
                  Srini
                  • 6. Re: How to fix this error: IMP-00003: ORACLE error 1435 encountered
                    958907
                    Where do you execute this? I have logged in both as the user and SYS, both of them return the following error:

                    =================================

                    Connected to:
                    Personal Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options

                    SQL> set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
                    SP2-0735: unknown SET option beginning "NLS_LANG=A..."
                    SQL> SET NLS_LANG = AMERICAN_AMERICA.AL32UTF8;
                    SP2-0158: unknown SET option "NLS_LANG"

                    ==================================
                    • 7. Re: How to fix this error: IMP-00003: ORACLE error 1435 encountered
                      Srini Chavali-Oracle
                      user1186998 wrote:
                      Where do you execute this? I have logged in both as the user and SYS, both of them return the following error:

                      =================================

                      Connected to:
                      Personal Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
                      With the Partitioning, OLAP, Data Mining and Real Application Testing options

                      SQL> set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
                      SP2-0735: unknown SET option beginning "NLS_LANG=A..."
                      SQL> SET NLS_LANG = AMERICAN_AMERICA.AL32UTF8;
                      SP2-0158: unknown SET option "NLS_LANG"

                      ==================================
                      NLS_LANG is a Windows environment variable - the set command needs to be executed in a command window before imp is executed.

                      HTH
                      Srini
                      • 8. Re: How to fix this error: IMP-00003: ORACLE error 1435 encountered
                        Srini Chavali-Oracle
                        >
                        ...
                        ORA-00959: tablespace 'DW_DATA' does not exist
                        ...
                        >

                        Pre-create this tablespace and retry the import

                        HTH
                        Srini
                        • 9. Re: How to fix this error: IMP-00003: ORACLE error 1435 encountered
                          958907
                          I have re-created this table space and used SQL Developer to assign unlimited tablespace for DW_DATA, now when I import the data again, I am getting more errors. I have tried to go to back and recreated another user to import, but seems like error still comes up. Here are the steps I did:
                          1. Create a new user
                          2. I ran this command in CMD: set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
                          3. I logged in to database and create data space DW_DATA
                          4. SQL Developer to assign unlimited tablespace for this table DW_DATA
                          5. Import the data dump: Imp tennisdb/xxxxxxxxxxxxxx file=tennisdb.dmp log=imp3.log fromuser=DW_OWNER touser=tennisdb

                          Am I following the right path?

                          Here is a short log of what the error says:

                          ============================

                          Connected to: Personal Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
                          With the Partitioning, OLAP, Data Mining and Real Application Testing options

                          Export file created by EXPORT:V11.02.00 via conventional path

                          Warning: the objects were exported by SYSTEM, not by you

                          import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
                          export client uses WE8MSWIN1252 character set (possible charset conversion)
                          IMP-00015: following statement failed because the object already exists:
                          "CREATE SEQUENCE "W_MATCH_F_IDW_MATCH_F_SEQ" MINVALUE 1 MAXVALUE 99999999999"
                          "9999999999999 INCREMENT BY 1 START WITH 1012821 CACHE 20 NOORDER NOCYCLE"
                          IMP-00015: following statement failed because the object already exists:
                          "CREATE SEQUENCE "W_STADIUM_D_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999"
                          "9999999 INCREMENT BY 1 START WITH 441 CACHE 20 NOORDER NOCYCLE"
                          IMP-00015: following statement failed because the object already exists:
                          "CREATE SEQUENCE "W_TOURNAMENT_F_SEQ" MINVALUE 1 MAXVALUE 999999999999999999"
                          "999999 INCREMENT BY 1 START WITH 433115 CACHE 20 NOORDER NOCYCLE"
                          ============================

                          Edited by: user1186998 on Sep 5, 2012 1:25 PM
                          • 10. Re: How to fix this error: IMP-00003: ORACLE error 1435 encountered
                            Srini Chavali-Oracle
                            >
                            import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
                            export client uses WE8MSWIN1252 character set (possible charset conversion)
                            ...
                            >

                            You are importing a different file than the previous post above - export file for this run was created using NLS_LANG set to WE8MSWIN1252 and the NLS_LANG for the import was set AL32UTF8 - hence the warning. You need to be consistent for the setting for NLS_LANG for both export and import.

                            >
                            ...
                            IMP-00015: following statement failed because the object already exists:
                            "CREATE SEQUENCE "W_MATCH_F_IDW_MATCH_F_SEQ" MINVALUE 1 MAXVALUE 99999999999"
                            "9999999999999 INCREMENT BY 1 START WITH 1012821 CACHE 20 NOORDER NOCYCLE"
                            IMP-00015: following statement failed because the object already exists:
                            "CREATE SEQUENCE "W_STADIUM_D_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999"
                            "9999999 INCREMENT BY 1 START WITH 441 CACHE 20 NOORDER NOCYCLE"
                            IMP-00015: following statement failed because the object already exists:
                            "CREATE SEQUENCE "W_TOURNAMENT_F_SEQ" MINVALUE 1 MAXVALUE 999999999999999999"
                            "999999 INCREMENT BY 1 START WITH 433115 CACHE 20 NOORDER NOCYCLE"
                            ...
                            >

                            These errors are ignorable - they are simply stating that the object you are importing already exists - you can ignore these errors by setting the parameter IGNORE=Y - check the docs for details.

                            http://docs.oracle.com/cd/E11882_01/server.112/e22490/original_import.htm

                            HTH
                            Srini