Copy entire Schema from one database to another

in ; Hi there I have a requirement that i need to copy all objects from one particular schema to another schema. ...
+ Post Reply + Post New Topic
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11
  1. #1
    Jamie22 is offline Junior Member
    Points: 890, Level: 15
    Join Date
    22 Oct 2008
    Posts
    10
    Points
    890

    Question Copy entire Schema from one database to another

    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. #2
    rajavu's Avatar
    rajavu is offline Forum Guru
    Points: 10,830, Level: 68
    Join Date
    13 Oct 2008
    Location
    @ Bangalore , India
    Posts
    815
    Points
    10,830

    Re: Copy entire Schema from one database to another

    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.

    Raj.

  3. #3
    rajavu's Avatar
    rajavu is offline Forum Guru
    Points: 10,830, Level: 68
    Join Date
    13 Oct 2008
    Location
    @ Bangalore , India
    Posts
    815
    Points
    10,830

    Re: Copy entire Schema from one database to another

    You can get the syntax and method of Export and Import from the below links

    Stanford EXP-IMP


    Orafaq EXP-IMP

    Raj.

  4. #4
    Jamie22 is offline Junior Member
    Points: 890, Level: 15
    Join Date
    22 Oct 2008
    Posts
    10
    Points
    890

    Re: Copy entire Schema from one database to another

    hello, thanks for your reply.

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

    Code :
    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. #5
    simply_dba's Avatar
    simply_dba is offline Forum Advisor
    Points: 1,740, Level: 24
    Join Date
    13 Oct 2008
    Location
    Kolkata, India
    Posts
    95
    Points
    1,740

    Re: Copy entire Schema from one database to another

    This will come if the version of exp utility is higher than that of the database.
    Some of the world's greatest feats were accomplished by people not smart enough to know they were impossible.

  6. #6
    rajavu's Avatar
    rajavu is offline Forum Guru
    Points: 10,830, Level: 68
    Join Date
    13 Oct 2008
    Location
    @ Bangalore , India
    Posts
    815
    Points
    10,830

    Re: Copy entire Schema from one database to another

    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.

    Raj.

  7. #7
    debasisdas's Avatar
    debasisdas is offline Member
    Points: 530, Level: 10
    Join Date
    15 Nov 2008
    Location
    Bangalore, India
    Posts
    46
    Points
    530

    Re: Copy entire Schema from one database to another

    Have you tried to use EXPDP / IMPDP as you are on 10g.
    Share Your Knowledge.

  8. #8
    Jamie22 is offline Junior Member
    Points: 890, Level: 15
    Join Date
    22 Oct 2008
    Posts
    10
    Points
    890

    Unhappy Re: Copy entire Schema from one database to another

    Quote Originally Posted by debasisdas View Post
    Have you tried to use EXPDP / IMPDP as you are on 10g.
    hi debasisdas, thank u for ur reply, first i tried expdp and i got this error

    Code :
    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 :
    create directory data_pump_dir as 'e:\oracle';

    later when i tried to use expdp
    Code :
    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. #9
    tyro's Avatar
    tyro is offline Forum Genius
    Points: 5,680, Level: 48
    Join Date
    20 Aug 2008
    Location
    India
    Posts
    368
    Points
    5,680

    Re: Copy entire Schema from one database to another

    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 :
    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 :
    expdp scott/tiger schemas=SCOTT directory=SCOTT_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

    Hope this helps

  10. #10
    rajavu's Avatar
    rajavu is offline Forum Guru
    Points: 10,830, Level: 68
    Join Date
    13 Oct 2008
    Location
    @ Bangalore , India
    Posts
    815
    Points
    10,830

    Re: Copy entire Schema from one database to another

    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.

    Raj.

Page 1 of 2 1 2 LastLast
Other Solutions
  1. Retrieving APPS Schema from OTHER database instance
    By bhavakrish in forum Other Databases
    Replies: 1
    Last Post: 04-28-2010, 01:56 PM
  2. How to copy the Oracle database structure to new server?
    By momer79 in forum Security, Backup and Recovery
    Replies: 12
    Last Post: 01-23-2010, 12:25 PM
  3. Copy oracle database from one server to other
    By momer79 in forum Security, Backup and Recovery
    Replies: 1
    Last Post: 12-29-2009, 12:34 PM
  4. Not publishing entire site with all pages
    By nv0044984 in forum Oracle Webcenter Suite (formerly Oracle ECM)
    Replies: 0
    Last Post: 09-17-2009, 05:45 AM
  5. Database Schema of UCM Stellent
    By smiley_chappy in forum Oracle Webcenter Suite (formerly Oracle ECM)
    Replies: 6
    Last Post: 05-24-2009, 08:42 AM