Copy entire Schema from one database to another

[Jamie22]

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

[rajavu]

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.

[rajavu]

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

Stanford EXP-IMP


Orafaq EXP-IMP

[Jamie22]

hello, thanks for your reply.

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

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

[simply_dba]

This will come if the version of exp utility is higher than that of the database.

[rajavu]

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.

[debasisdas]

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

[Jamie22]

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

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

create directory data_pump_dir as 'e:\oracle';

later when i tried to use expdp

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

[tyro]

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):

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

expdp scott/tiger schemas=SCOTT directory=SCOTT_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

Hope this helps

[rajavu]

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.