This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Apr 17, 2013 8:43 AM by Richard Harrison . RSS

expdp(datapump) or exp(orignal export).

947771 Newbie
Currently Being Moderated
Hi

i do not have xmltype in my tables or views but in stored proc i do have for processing xml.
so should i use expdp(datapump) or exp(orignal export).

yours sincerely

Edited by: 944768 on Apr 1, 2013 4:53 AM
  • 1. Re: expdp(datapump) or exp(orignal export).
    Paul M. Oracle ACE
    Currently Being Moderated
    should i use expdp(datapump) or exp(orignal export).
    See Choosing the Right Export/Import Utility
  • 2. Re: expdp(datapump) or exp(orignal export).
    947771 Newbie
    Currently Being Moderated
    i have read that part , but this point is not written explicitly.


    so pls tel me.

    yours sincerely
  • 3. Re: expdp(datapump) or exp(orignal export).
    Paul M. Oracle ACE
    Currently Being Moderated
    Is this for backup purposes ? If so, then you shouldn't use either of them, you should use RMAN.

    That said, unless you need particular options, use exp.
  • 4. Re: expdp(datapump) or exp(orignal export).
    947771 Newbie
    Currently Being Moderated
    i want to backup only a schema not the whole database?

    can it be done using rman.



    yours sincerely

    Edited by: 944768 on Apr 2, 2013 4:54 AM
  • 5. Re: expdp(datapump) or exp(orignal export).
    Paul M. Oracle ACE
    Currently Being Moderated
    i want to backup only a schema not the whole database?

    can it be done using rman.
    No.
  • 6. Re: expdp(datapump) or exp(orignal export).
    947771 Newbie
    Currently Being Moderated
    i have one more problem
    the topic
    "Exporting and Importing Data with the Export and Import Utilities" was given at

    10-16 Oracle Database Express Edition 2 Day DBA

    and then a reference was written

    See Oracle Database Utilities for further information about the Export and Import
    utilities and for examples of how to use them.

    i went to oracle database utilities and found

    "Original Export" at Original Export 21-1

    1)i wanted to know that i have found the right utility which was refrenced in 2 day dba or it is different , if it is different then pls show link?

    2) in "Orignal Export" it is written "The only supported use of original Export in Oracle
    Database 11g is backward migration of XMLType data to Oracle Database 10g release 2 (10.2) or earlier" .

    then how one can use "Orignal Export" in exporting a schema having xmltype col or tables?
    and because of this only i got doubt that i have found correct utility or not.

    yours sincerely
  • 7. Re: expdp(datapump) or exp(orignal export).
    clcarter Expert
    Currently Being Moderated
    i have found the right utility
    Yes indeed you have the right ones, as there are only one each (one exp, one imp) utility program per database software installation.
    how one can use "Orignal Export" in exporting a schema having xmltype col or tables
    Good question, read the additional notes on exp/imp, quote:
    Original Export is desupported for general use as of Oracle Database 11g.
    The old exp/imp utilities are older than the xml data types. If those types are not used in the schemas then exp/imp should work. But there is no support for exporting/importing unless using the newer datapump programs.

    Datapump can muti thread and generally performs better, although XE can do any multi threaded operation. But overall datapump is a better utility, just a bit different with a few more options and parameters.

    And since this forum is the only official support channel for XE we're it.
  • 8. Re: expdp(datapump) or exp(orignal export).
    947771 Newbie
    Currently Being Moderated
    page no: "10-8 Oracle Database Express Edition 2 Day DBA"

    says Export and Import utilities supports XMLtype.

    1)so can i use original export given in database utilities doc for xmltype cols or xmltype tables ? ( i am using oracle 11g express edition r2)

    2) one more question if some body is connected to a schema from entry form but no body is using it , in this case should one export the schema or first he should restart the

    database to cut the connections then he should make export of it.

    yours sincerely

    Edited by: 944768 on Apr 3, 2013 12:14 AM

    Edited by: 944768 on Apr 3, 2013 12:16 AM

    Edited by: 944768 on Apr 3, 2013 12:16 AM

    Edited by: 944768 on Apr 3, 2013 1:03 AM

    Edited by: 944768 on Apr 3, 2013 4:16 AM

    Edited by: 944768 on Apr 3, 2013 5:54 AM

    Edited by: 944768 on Apr 3, 2013 5:58 AM
  • 9. Re: expdp(datapump) or exp(orignal export).
    clcarter Expert
    Currently Being Moderated
    xml ... ?
    That one I have no idea. Try it and see. Test and verity your result. Know what to expect, and don't be surprised if you ... find something you were not expecting.
    connected to a schema
    One does not connect to a schema, connections are opened to a database instance. A schema is a collection of database objects.
    entry form but no body is using it ...
    That is an entirely different subject, try a google with the search terms rdbms acid test.

    In oracle, the only session (connection) that can "see" updates is the updating session. No other session may sees those changes until the transaction is committed to the database. Until the commit, all other connections see the "before" value.

    Queries that begin before other transactions commit see the before values, oracle keeps the old value in undo. So an export will "see" the values committed as of the time the export begins.
  • 10. Re: expdp(datapump) or exp(orignal export).
    947771 Newbie
    Currently Being Moderated
    thanku

    is the export import given at "Export and Import utilities supports XMLtype" at "page no: "10-8 Oracle Database Express Edition 2 Day DBA""

    and orignal export given at database utilities document is same because in database utility doc , its written that it is for migration to 10g

    where as in Oracle Database Express Edition 2 Day DBA its written that it used for xmltype data.

    yours sincerely

    Edited by: 944768 on Apr 3, 2013 11:08 PM

    Edited by: 944768 on Apr 3, 2013 11:09 PM

    Edited by: 944768 on Apr 6, 2013 1:37 AM
  • 11. Re: expdp(datapump) or exp(orignal export).
    947771 Newbie
    Currently Being Moderated
    i have one more doubt ,

    1)can i use any DIRECTORY name and any path of windows server 2003 in following command?

    CREATE OR REPLACE DIRECTORY dmpdir AS ’c:\oraclexe\app\tmp’;


    2)can i use any name in DUMPFILE like hr01Apr2013.dmp in following command.

    expdp SYSTEM/password SCHEMAS=hr DIRECTORY=dmpdir DUMPFILE=schema.dmp
    LOGFILE=expschema.log

    yours sincerely
  • 12. Re: expdp(datapump) or exp(orignal export).
    atom6008 Newbie
    Currently Being Moderated
    Hi 944768,

    Yes, you can use any directory or dump file name. There is no restriction on the name.

    And for the path you will be mentioning while creating the directory object, the user should have full privilege to write into that path.

    Regards,
    ATOM
  • 13. Re: expdp(datapump) or exp(orignal export).
    947771 Newbie
    Currently Being Moderated
    i have one more problem

    SQL>@.[%NLS_LANG%].

    SP2-0310: unable to open file "[AMERICAN_AMERICA.WE8MSWIN1252]"


    and database said select * from NLS_DATABASE_PARAMETERS;

    NLS_LANGUAGE AMERICAN
    NLS_TERRITORY AMERICA
    NLS_CURRENCY $
    NLS_ISO_CURRENCY AMERICA
    NLS_NUMERIC_CHARACTERS .,
    NLS_CHARACTERSET AL32UTF8
    NLS_CALENDAR GREGORIAN
    NLS_DATE_FORMAT DD-MON-RR
    NLS_DATE_LANGUAGE AMERICAN
    NLS_SORT BINARY
    NLS_TIME_FORMAT HH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY $
    NLS_COMP BINARY
    NLS_LENGTH_SEMANTICS BYTE
    NLS_NCHAR_CONV_EXCP FALSE
    NLS_NCHAR_CHARACTERSET AL16UTF16
    NLS_RDBMS_VERSION 11.2.0.2.0


    both nls_lang look diffrent can it create problem in my empdp impdp ?

    i am using

    Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
    PL/SQL Release 11.2.0.2.0 - Production
    "CORE     11.2.0.2.0     Production"
    TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
    NLSRTL Version 11.2.0.2.0 - Production
  • 14. Re: expdp(datapump) or exp(orignal export).
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    NLS_LANG should be set the same at the operating system level and database level when doing impdp/expdp do avoid any translation of characters during the export/import process.

    If you are just using normal 'english' characters then it probably makes no difference.

    In your case for instance if you have a western european ö character this will likely get converted when you do the export and may end up being 'corrupted' in the file when you load it into another database it may just appear as upside down question marks.

    Regards,
    Harry
1 2 Previous Next

Legend

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