10 Replies Latest reply: Oct 12, 2012 12:33 PM by orafad RSS

    How To Import and Export data in Oracle 10.2 XE

    750454
      Hi,
      we want to export schemas from one DB instance and import it to other instance. Please let us know how can this be achieved in 10.2.0.1 version of DB.


      Regards,
      Swati
        • 1. Re: How To Import and Export data in Oracle 10.2 XE
          orafad
          Use expdp / impdp or exp / imp tools included and documented.

          http://docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/impexp.htm
          $ expdp help=y
          • 2. Re: How To Import and Export data in Oracle 10.2 XE
            750454
            Thanks,for your support.

            But there is a problem i am working in a windows xp 2002 and while in sql command writing expdp or impdp it shows command not found.
            it is some prerequisite require to run these command if so,than please let me know.


            Regards
            Swati
            • 3. Re: How To Import and Export data in Oracle 10.2 XE
              Paul M.
              while in sql command writing expdp or impdp it shows command not found.
              expdp and impdp are not sql commands, you have to execute them at OS prompt.
              • 4. Re: How To Import and Export data in Oracle 10.2 XE
                750454
                Hi

                I am getting below error while exporting the data:

                Export: Release 10.2.0.1.0 - Production on Tuesday, 09 October, 2012 16:53:13

                Copyright (c) 2003, 2005, Oracle. All rights reserved.

                Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Productio
                n
                Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** DIRECTORY=expo
                rt DUMPFILE=swatioer.dmp TRANSPORT_TABLESPACES=OER_DATA TRANSPORT_FULL_CHECK=y L
                OGFILE=oer.log
                ORA-39123: Data Pump transportable tablespace job aborted
                ORA-29335: tablespace 'OER_DATA' is not read only

                Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 16:53:20

                Can someone please help me out with the same?

                Thanks and Regards
                Swati
                • 5. Re: How To Import and Export data in Oracle 10.2 XE
                  Paul M.
                  ORA-29335: tablespace 'OER_DATA' is not read only
                  See if Problem Exporting 'USER' Tablespace Metadata may help you.
                  • 6. Re: How To Import and Export data in Oracle 10.2 XE
                    orafad
                    Try using a different login than SYSTEM.

                    You could create a new user for exporting and grant exp_full_database role to the user (or to an existing user).
                    • 7. Re: How To Import and Export data in Oracle 10.2 XE
                      750454
                      Hi,

                      firstly i would say that the link of discussion which you have reffered i don't think that this is for 10g xpress edition.kindly confirm this,and still i tried the solution given in that link it is giving the below error.even tried from different user.


                      Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** DIRECTORY=expo
                      rt DUMPFILE=swatioer.dmp TRANSPORT_TABLESPACES=OER_DATA TRANSPORT_FULL_CHECK=y L
                      OGFILE=oer.log
                      ORA-39123: Data Pump transportable tablespace job aborted
                      ORA-29335: tablespace 'OER_DATA' is not read only

                      Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 09:55:2


                      And after that i tried by folowing below steps:-

                      1>ALTER TABLESPACE OER_DATA READ only;
                      2>EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'OER_DATA,OER_INDEX', incl_constraints => TRUE);
                      3>And then export command it shows below error:-



                      Copyright (c) 2003, 2005, Oracle. All rights reserved.

                      Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Productio
                      n
                      Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** DIRECTORY=expo
                      rt DUMPFILE=swatioer.dmp TRANSPORT_TABLESPACES=OER_DATA,OER_INDEX TRANSPORT_FULL
                      _CHECK=y LOGFILE=swatioer.log
                      ORA-39123: Data Pump transportable tablespace job aborted
                      ORA-00439: feature not enabled: Export transportable tablespaces

                      Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 12:30:5
                      4

                      As this Export transportable tablespaces is False to export it require to be true.
                      so,kindly let me know that export import is possible in this xpress edition or not if yes than "Export transportable tablespaces" it should be true.
                      how can i set this to true.

                      Regards
                      swati
                      • 8. Re: How To Import and Export data in Oracle 10.2 XE
                        orafad
                        >
                        Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
                        ORA-00439: feature not enabled: Export transportable tablespaces
                        After fixing the first issue, a bigger one appeared.

                        Per XE Licensing information, mentioned feature is not available:

                        http://docs.oracle.com/cd/B25329_01/doc/license.102/b25456/toc.htm#BABDFDAI
                        (same applies to 11.2 XE).


                        Try a different mode of export. What are you trying to accomplish, what's the purpose behind exporting?
                        • 9. Re: How To Import and Export data in Oracle 10.2 XE
                          750454
                          Hi,

                          Actually there are certain properties which are store in our database,we want to take backup of those properties from one database environment and want to restore on the another one.

                          Thanks and Regards,
                          Swati
                          • 10. Re: How To Import and Export data in Oracle 10.2 XE
                            orafad
                            >
                            Actually there are certain properties which are store in our database,
                            What does properties refer to here?
                            we want to take backup of those properties from one database environment and want to restore on the another one.
                            Have you tried export in schema mode? (with expdp, see schemas=... parameter)