This discussion is archived
10 Replies Latest reply: Oct 12, 2012 10:33 AM by orafad RSS

How To Import and Export data in Oracle 10.2 XE

750454 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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. Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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. Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    >
    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)

Legend

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