This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jun 7, 2012 12:30 PM by Dean Gagne RSS

expdp/impdp

user3714906 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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
    Rash_ Explorer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks alot,

    you guys are of great help
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points