1 2 Previous Next 15 Replies Latest reply: Jun 7, 2012 7:30 PM by Dean Gagne-Oracle RSS

    expdp/impdp

    user3714906
      Hi,

      I have a situation where I have to change INITRANS of the tables, so I need to perform below steps:

      1.Export the contents of the table
      2. Recreate the table and associated index(s) with the correct INITRANS settings
      3. Re-import the table data

      for export If I use:

      expdp system/manager directory=export dumpfile=PAY_RUN_RESULTS.dmp logfile=PAY_RUN_RESULTS.log content=data_only

      do I need to specify any other option with expdp and what about the indexes and views associated with it?

      Thanks and regards
        • 1. Re: expdp/impdp
          viconstg
          Why not to use
          alter table <tablename> initrans <integer>
          ?

          If the previous is not acceptable, then you must specify the TABLES option to run expdp in any case (or SCHEMAS, or so).
          • 2. Re: expdp/impdp
            Rasheed_
            if using datapump while import specify table_exists_action=append ,then .export u can content=all also .

            but why dont you use method specified by
            viconstg in prev post
            • 3. Re: expdp/impdp
              839439
              Hi
              what is oracle version ??
              os ??

              can u paste the scenario, where u are getting this issue i.e, ora error ?


              --neeraj                                                                                                                                                                                                                                                                   
              • 4. Re: expdp/impdp
                anand prakash - oracle
                Hi,
                1.Export the contents of the table
                2. Recreate the table and associated index(s) with the correct INITRANS settings
                3. Re-import the table data

                for export If I use:

                expdp system/manager directory=export dumpfile=PAY_RUN_RESULTS.dmp logfile=PAY_RUN_RESULTS.log content=data_only

                do I need to specify any other option with expdp and what about the indexes and views associated with it?

                >


                You can use alter table command to change the initrans but that would effect only to data blocks which will be subsequently allocated. So, in case you want to change it for all the blocks, this would be correct. You need to specify "tables" parameter :) I think you just missed it here while posting. Rest all looks ok.While import indexes will ba taken care of. You can run utlrp after the import completes or check for invalid objects pre and post the activity and compile them.

                Anand
                • 5. Re: expdp/impdp
                  user3714906
                  Thanks all for the reply,

                  Actually I am advised by Oracle to change the INITRANS settings using Doc: 226987.1.

                  My Oracle Version is 10.2.0.4

                  Since I will be doing this activity on Production, I want that I should not miss anything which can later land me in trouble like missing any index,views etc as we will have small downtime

                  And also please let me know if I take hr schema export backup using >> expdp hr/hr directory=export dumpfile=hr.dmp logfile=hr.log schemas=hr

                  can I simply use impdp hr/hr directory=export dumpfile=hr.dmp logfile=imp.log ?

                  Because on test instance I am getting some errors like object already exists and "Schema APPS is needed to import this object, but is unaccessible"

                  Please suggest
                  • 6. Re: expdp/impdp
                    viconstg
                    user3714906 wrote:
                    Thanks all for the reply,

                    And also please let me know if I take hr schema export backup using >> expdp hr/hr directory=export dumpfile=hr.dmp logfile=hr.log schemas=hr

                    can I simply use impdp hr/hr directory=export dumpfile=hr.dmp logfile=imp.log ?

                    Because on test instance I am getting some errors like object already exists and "Schema APPS is needed to import this object, but is unaccessible"
                    Yes, you can import using this statement. The errors you've received is explainable:
                    - object already exists -- because schema hr exists in the database. You can use TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE} option -- http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_import.htm#sthref364
                    - Schema APPS is needed -- some imported objects references this scmema, so you should export this schema too if you want to get consistent data

                    Edited by: viconstg on 30.05.2012 16:31

                    P.S. And please don't forget to reward people if their answers helped you
                    • 7. Re: expdp/impdp
                      anand prakash - oracle
                      Hi,

                      >
                      And also please let me know if I take hr schema export backup using >> expdp hr/hr directory=export dumpfile=hr.dmp logfile=hr.log schemas=hr

                      can I simply use impdp hr/hr directory=export dumpfile=hr.dmp logfile=imp.log ?

                      Because on test instance I am getting some errors like object already exists and "Schema APPS is needed to import this object, but is unaccessible">

                      What is your plan of action? Are you taking the export of the schema, take the ddl of tables, dropping the tables, recreate tables with new initrans values and then importing the dump?
                      • 8. Re: expdp/impdp
                        user3714906
                        my POA

                        Take the export backup of hr tables (10 tables)

                        expdp hr/hr directory=export dumpfile=table1.dmp logfile=table1.log tables=HR.table1

                        Get the script of this table1

                        Drop the table and recreate it with desired INITRANS parameter value

                        Then import the data back in the table1

                        impdp he/hr directory=export dumpfile=table1.dmp logfile=table1_imp.log

                        I am following these steps for all 10 tables

                        I have confusion whehter I will use apps user or system or he user for this import and export, cos I am getting these errors like insufficient privilige

                        Thanks
                        • 9. Re: expdp/impdp
                          viconstg
                          If you will re-create the table manually with modified INITRANS, you need to use TABLE_EXISTS_ACTION=APPEND during import, or (even better) CONTENT=DATA_ONLY
                          • 10. Re: expdp/impdp
                            user3714906
                            While importing I am getting this error


                            Starting "HR"."SYS_IMPORT_FULL_01": hr/******** directory=expbkp dumpfile=PAY_RUN_RESULTS.dmp logfile=PAY_RUN_RESULTS_imp.log
                            Processing object type TABLE_EXPORT/TABLE/TABLE
                            Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
                            . . imported "HR"."PAY_RUN_RESULTS" 164.7 MB 4502805 rows
                            Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
                            Processing object type TABLE_EXPORT/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
                            ORA-39083: Object type OBJECT_GRANT failed to create with error:
                            ORA-31625: Schema APPS is needed to import this object, but is unaccessible
                            ORA-01031: insufficient privileges
                            Failing sql is:
                            GRANT SELECT ON "HR"."PAY_RUN_RESULTS" TO "HR_REPORTING_USER"

                            Which user should I use to take export and then import ?
                            • 11. Re: expdp/impdp
                              viconstg
                              user3714906 wrote:
                              Which user should I use to take export and then import ?
                              You should import APPS schema, or you can import CONTENT=DATA_ONLY, to avoid importing grans, etc.
                              • 12. Re: expdp/impdp
                                anand prakash - oracle
                                Take the export backup of hr tables (10 tables)
                                expdp hr/hr directory=export dumpfile=table1.dmp logfile=table1.log tables=HR.table1

                                Get the script of this table1

                                Drop the table and recreate it with desired INITRANS parameter value

                                Then import the data back in the table1

                                impdp he/hr directory=export dumpfile=table1.dmp logfile=table1_imp.log

                                I am following these steps for all 10 tables

                                I have confusion whehter I will use apps user or system or he user for this import and export, cos I am getting these errors like insufficient privilige>

                                Depending on the downtime, you can create 1 dumpfile consisting of all the 10 tables (you can use the parameter "parfile"), take the ddl of all indexes on these tables using dbms_metadata package, drop the table, recreate with desired INITRANS, import using table_exists_action=append. Once import is completed, create the indexes and then recompile invalid objects. I would say do the export/import with system user.


                                Anand
                                • 13. Re: expdp/impdp
                                  Srini Chavali-Oracle
                                  This is an EBS database - exporting/importing individual tables/schemas is not supported. Pl open an SR with Support and ask them for the exact detailed steps on how to achieve this.

                                  Oracle 11i & R12 Human Resources (HRMS) & Benefits (BEN) Tuning & System Health Checks [ID 226987.1]     

                                  HTH
                                  Srini
                                  • 14. Re: expdp/impdp
                                    user3714906
                                    Thanks alot,

                                    you guys are of great help
                                    1 2 Previous Next