14 Replies Latest reply on Jul 8, 2011 10:28 AM by 874225

    ORA-39083 - During Impdp

    Dba_SureshS
      Hi all,

      My db is in 10.1.0.5

      I took export of an table in my prod db and tried to import the table in preprod db.

      But It threw the following error.

      ORA-39083: Object type OBJECT_GRANT failed to create with error:
      ORA-01917: user or role 'LFOPRODSELECT' does not exist
      Failing sql is:
      GRANT SELECT ON "LFO_UAT"."QUESTION" TO "LFOPRODSELECT"

      Because of this error few of the constraints are not getting created.

      Please help....

      Thanks in advance
        • 1. Re: ORA-39083 - During Impdp
          OrionNet
          Hello,

          How did you export your table, I mean with what options? Also make sure role exists in preprod db
          Try this,
          exp username/password tables=(mytables, moretables) file=mytable.dmp lpg=mytable.log  grants=Y
          
          imp username/password tables=(mytables, moretables) file=mytable.dmp log=myimport.log grants=Y
          Regards
          • 2. Re: ORA-39083 - During Impdp
            Dba_SureshS
            Hi,

            Thanks for the input.

            Option which is used

            expdp username/password directory=name dumpfile=name logfile=name tables=name


            This is how i usually export and import tables, but all of sudden this error exists.


            I tried exp username/password tables=(mytables, moretables) file=mytable.dmp log=mytable.log grants=Y
            But it threw few errors EXP-00091: Exporting questionable statistics.
            EXP-00091: Exporting questionable statistics.
            EXP-00091: Exporting questionable statistics.


            Do we have any options in expdp???

            Thanks.
            • 3. Re: ORA-39083 - During Impdp
              OrionNet
              Hello,

              With conventional export you can use this;
              exp username/password tables=(mytables, moretables) file=mytable.dmp log=mytable.log grants=Y statistics=NONE
              With datapump you can use this,make sure user have create any directory privs
              SQL> create or replace directory EXPORT_DIR as 'your path to dir';
              
              C:>expdp username/password directory=TEST_DIR tables=my_objects dumpfile=te
              
              Export: Release 10.2.0.1.0 - Production on Thursday, 25 December, 2008 21:40:13
              
              Copyright (c) 2003, 2005, Oracle.  All rights reserved.
              
              Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
              With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
              Starting "KLONDIKE"."SYS_EXPORT_TABLE_01":  username/******** directory=TEST_DIR tables=my_objects dumpfile=test.dmp logf
              Estimate in progress using BLOCKS method...
              Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
              Total estimation using BLOCKS method: 88 MB
              Processing object type TABLE_EXPORT/TABLE/TABLE
              Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
              Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
              Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
              Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
              . . exported "USERNAME"."MY_OBJECTS"                     70.84 MB  758088 rows
              Master table "USERNAME"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
              ******************************************************************************
              Dump file set for KLONDIKE.SYS_EXPORT_TABLE_01 is:
                E:\U01\ORADATA\DSS\LOG\TEST.DMP
              Job "USERNAME"."SYS_EXPORT_TABLE_01" successfully completed at 21:41:34
              Regards
              • 4. Re: ORA-39083 - During Impdp
                Dba_SureshS
                Hi,

                Thanks for the information.

                I usually take export through expdp option only and directory is already available in my db. The method which you said is the way i usually take backup.

                Through that method only i got the error

                Thanks & Regards.
                • 5. Re: ORA-39083 - During Impdp
                  OrionNet
                  Hello,

                  Exactly what error are you getting using expdp/impdp?

                  Regards
                  • 6. Re: ORA-39083 - During Impdp
                    Dba_SureshS
                    Hi,

                    Thanks for the replies.

                    ORA-39083: Object type OBJECT_GRANT failed to create with error:
                    ORA-01917: user or role 'LFOPRODSELECT' does not exist
                    Failing sql is:
                    GRANT SELECT ON "LFO_UAT"."QUESTION" TO "LFOPRODSELECT"


                    Thanks & Regards.
                    • 7. Re: ORA-39083 - During Impdp
                      OrionNet
                      Hello,

                      GRANT SELECT ON "LFO_UAT"."QUESTION" TO "LFOPRODSELECT"

                      Make sure role or users exists in your target db "LFOPRODSELECT".
                      Create role LFOPRODSELECT;
                      • 8. Re: ORA-39083 - During Impdp
                        Dba_SureshS
                        Hi,

                        The owner of the table QUESTION is LFO_UAT. Then do i need to grant select on the table QUESTION to the user LFO_UAT.


                        Thanks.
                        • 9. Re: ORA-39083 - During Impdp
                          OrionNet
                          Hello,

                          If owner of the table is LFO_UAT,user owns it meaning LFO_UAT can do any DML/DDL operations on the table. So no need to give any grants to the user itself. Hope this helps you.

                          Regards
                          • 10. Re: ORA-39083 - During Impdp
                            Dba_SureshS
                            Hi,

                            Still the same error. Beause of the error i was not able to create reference key for that table.

                            Later, manually i gave grant select on table_name to user(LFO_UAT)

                            Now i tried to create reference key it got created. Dont know what went wrong.


                            Thanks & Regards.
                            • 11. Re: ORA-39083 - During Impdp
                              OrionNet
                              So is it working for you now or still getting errors while importing. If you can post exact export/import commands that will make it easy to help you?
                              • 12. Re: ORA-39083 - During Impdp
                                Dba_SureshS
                                Hi ,


                                expdp username/password directory=name dumpfile=name.dmp logfile=name.log tables=lfo_prod.questions


                                impdp username/password directory=name dumpfile=name.dmp logfile=name.log remap_schema=lfo_prod:lfo_uat remap_tablespace=datatbsprod:lfo_data table_exists_action=replace


                                These are exact commands i used. Still my Import end up with same error.



                                Thanks & Regards.
                                • 13. Re: ORA-39083 - During Impdp
                                  OrionNet
                                  Hello,

                                  Here is a test case using expdp/impdp option you posted here. So my recommendation create a role without any grants in UAT schema before importing your table, hope this helps
                                  create role LFOPRODSELECT;
                                  EXPORT*
                                  1. created a table in "prd" schema: my_objects
                                  2. bitmap index on my_objects.owner (just for test purpose)
                                  3. A role "myrole" with grant select on my_objects
                                  grant select on prd.my_objects to myrole;
                                  C:\expdp prd/prd directory=TEST_DIR dumpfile=myobjects.dmp logfile=myobjects.log tables=prd.my_objects
                                  
                                  Export: Release 10.2.0.1.0 - Production on Friday, 26 December, 2008 22:19:10
                                  Copyright (c) 2003, 2005, Oracle. All rights reserved.
                                  Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
                                  With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
                                  Starting "PRD"."SYS_EXPORT_TABLE_01": prd/******** directory=TEST_DIR dumpfile=myobjects.dmp logfile=myobjects.log tables=prd.my_objects
                                  Estimate in progress using BLOCKS method...
                                  Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
                                  Total estimation using BLOCKS method: 184 MB
                                  Processing object type TABLE_EXPORT/TABLE/TABLE
                                  Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
                                  Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
                                  Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
                                  Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
                                  . . exported "PRD"."MY_OBJECTS" 141.6 MB 1516176 rows
                                  Master table "PRD"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
                                  ******************************************************************************
                                  Dump file set for KLONDIKE.SYS_EXPORT_TABLE_01 is:
                                  E:\U01\ORADATA\DSS\LOG\MYOBJECTS.DMP
                                  Job "KLONDIKE"."SYS_EXPORT_TABLE_01" successfully completed at 22:19:34
                                  Import test case 1: Without ROLE to recreate error at import_
                                  C:>impdp uat/uat directory=TEST_DIR dumpfile=myobjects.dmp remap_schema=prd:uat remap_tablespace=orion_data_ts:ama_data logfile=myimport.log table_exists_action=replace
                                  Import: Release 10.2.0.1.0 - Production on Friday, 26 December, 2008 23:09:30
                                  Copyright (c) 2003, 2005, Oracle. All rights reserved.
                                  Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
                                  With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
                                  Master table "UAT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
                                  Starting "UAT"."SYS_IMPORT_FULL_01": uat/******** directory=TEST_DIR dumpfile=myobjects.dmp remap_schema=prd:uat remap_tablespace=orion_data_ts:ama_data logfile=myimport.log table_exists_action=replace
                                  Processing object type TABLE_EXPORT/TABLE/TABLE
                                  Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
                                  . . imported "UAT"."MY_OBJECTS" 141.6 MB 1516176 rows
                                  Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
                                  ORA-39083: Object type OBJECT_GRANT failed to create with error:
                                  ORA-01917: user or role 'MYROLE' does not exist
                                  Failing sql is:
                                  GRANT SELECT ON "UAT"."MY_OBJECTS" TO "MYROLE"
                                  
                                  Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
                                  Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
                                  Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
                                  Job "UAT"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 23:10:16
                                  Test Case 2: With role_
                                  SQL> create role myrole;
                                  
                                  C:\>impdp uat/uat directory=TEST_DIR dumpfile=myobjects.dmp remap_schema=klondike:testme remap_tablespace=orion_data_ts:ama_data logfile=myimport.log table_exists_action=replace
                                  Import: Release 10.2.0.1.0 - Production on Friday, 26 December, 2008 23:12:55
                                  Copyright (c) 2003, 2005, Oracle. All rights reserved.
                                  Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
                                  With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
                                  Master table "UAT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
                                  Starting "UAT"."SYS_IMPORT_FULL_01": uat/******** directory=TEST_DIR dumpfile=myobjects.dmp remap_schema=prd:uat remap_tablespace=orion_data_ts:ama_data logfile=myimport.log table_exists_action=replace
                                  Processing object type TABLE_EXPORT/TABLE/TABLE
                                  Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
                                  . . imported "UAT"."MY_OBJECTS" 141.6 MB 1516176 rows
                                  Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
                                  Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
                                  Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
                                  Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
                                  Job "UAT"."SYS_IMPORT_FULL_01" successfully completed at 23:13:43
                                  Regards

                                  Edited by: OrionNet on Dec 26, 2008 11:32 PM

                                  Edited by: OrionNet on Dec 26, 2008 11:33 PM

                                  Edited by: OrionNet on Dec 26, 2008 11:33 PM
                                  1 person found this helpful
                                  • 14. Re: ORA-39083 - During Impdp
                                    874225
                                    Hi all,

                                    My db is 10.2.0.4

                                    I took export data in linux oracle prod db and i'm trying to import on widows 10.2.0.1
                                    but it through's the following error's


                                    ORA-39083: object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
                                    ORA-00910: specified lengh too lang for its datatype
                                    failing sql is:
                                    ORA-01919
                                    ORA-39112
                                    ORA-39082



                                    plz help me any one


                                    thanks

                                    regards
                                    vishnu

                                    Edited by: 871222 on Jul 8, 2011 3:23 AM