1. The Forum has been upgraded to a modern and advanced system. Please read the announcement about this update.
  2. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Copy entire Schema from one database to another

Discussion in 'General' started by Jamie22, Nov 19, 2008.

  1. Jamie22

    Jamie22 Active Member

    Messages:
    10
    Likes Received:
    1
    Trophy Points:
    90
    Hi there :)

    I have a requirement that i need to copy all objects from one particular schema to another schema. Both the database are 10g (versions are same). Basically the objective is that we have an application that uses oracle as the database. We have installed the same application on another machine. Now it would be a pain to do all the configuration activities again on the second database.

    can someone please guide us how we can copy all the objects and the data from the first database to the second. the volume of data is also not huge (since it's mostly configuration tables).

    thanks in advance for your time

    Regards,
    Jamie :)
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    The primary question that come in my mind is ,

    Why can't you export the first Schema for tables (configuration schema) and import into the second schema . I hope the second schema is blank .

    If Second schema is not blank, truncate tables , do Table Export of First schema and import into second schema.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
  4. Jamie22

    Jamie22 Active Member

    Messages:
    10
    Likes Received:
    1
    Trophy Points:
    90
    hello, thanks for your reply.

    I am getting this error when I am trying to use the exp utility. Any suggestions?

    Code (Text):
    EXP-00056: ORACLE error 6550 encountered
    ORA-06550: line 1, column 41:
    PLS-00302: component 'SET_NO_OUTLINES' must be declared
    ORA-06550: line 1, column 15:
    PL/SQL: Statement ignored
    EXP-00000: Export terminated unsuccessfully
    thanks for your replies :)
     
  5. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    This will come if the version of exp utility is higher than that of the database.
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It is just a matter of googling with the Error code and description in internet.

    I just googled and got hundreds of result . Some of the pages exactly matching your issue are from

    Here from Orafaq

    Here from Oracle

    export using same or lower version of database.
     
  7. debasisdas

    debasisdas Active Member

    Messages:
    46
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    Bangalore, India
    Have you tried to use EXPDP / IMPDP as you are on 10g.
     
  8. Jamie22

    Jamie22 Active Member

    Messages:
    10
    Likes Received:
    1
    Trophy Points:
    90
    hi debasisdas, thank u for ur reply, first i tried expdp and i got this error

    Code (Text):
    ORA-39002: invalid operation
    ORA-39070: Unable to open the log file.
    ORA-39087: directory name DATA_PUMP_DIR is invalid
    I did some research online and found that i need to deine the data_pump_dir, so i created the directory as
    Code (Text):

    create directory data_pump_dir as 'e:\oracle';
     
    later when i tried to use expdp
    Code (Text):

    E:\>expdp

    Export: Release 10.2.0.1.0 - Production on Saturday, 22 November, 2008 13:03:16

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

    Username: system
    Password:

    Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
    tion
    With the Partitioning, OLAP and Data Mining options
    UDE-00018: Message 18 not found;  product=RDBMS; facility=UDE
     
    now i am getting this error, :( i am sorry i am trying to do this for the first time
     
  9. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    few things here,

    1> you need to run the expdp command from your bin directory,
    2> but before you need to create the directory you will use

    Do this (assuming u r exporting SCOTT):

    Code (Text):

    sqlplus / AS SYSDBA
    GRANT CREATE ANY DIRECTORY TO scott;
    CREATE OR REPLACE DIRECTORY SCOTT_DIR AS 'e:/oracle/SCOTT_DUMP';
    GRANT READ, WRITE ON DIRECTORY SCOTT_DIR TO scott;
     
    then come back to command prompt and cd to your oracle bin directory and do this

    Code (Text):

    expdp scott/tiger schemas=SCOTT directory=SCOTT_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
     
    Hope this helps :)
     
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You can refer these links before you start in full swing ..

    DATAPUMP

    DATAPUMP

    Even Normal Export and import will work in 10g . You can try the export import from the Server itself.

    The most important difference between expdp-impdp and normal exp-imp is that Datapump will work only from the server but normal exp-imp will work even from the client machines.
     
  11. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    And more over ,

    UDE-00018