1 2 Previous Next 22 Replies Latest reply: Apr 17, 2013 10:43 AM by Richard Harrison . RSS

    expdp(datapump) or exp(orignal export).

    947771
      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.
          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
            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.
              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
                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.
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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 .
                                    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