f



Copy data from one database instance to another on the same server

I have some data in a couple of temporary tables in a schema in the live 
database. I want to copy the data to the same schema on a test database that 
is on the same server.

What is the best method of doing this? Can I use SQL Developer/PL/SQL, or 
must I use an external tool?

Thanks

CJM 


0
cjmnews04 (80)
11/2/2006 11:16:14 AM
comp.databases.oracle.server 22978 articles. 1 followers. Post Follow

8 Replies
1051 Views

Similar Articles

[PageSpeed] 46

"CJM" <cjmnews04@REMOVEMEyahoo.co.uk> wrote in message 
news:4qu2buFoeuvbU1@individual.net...
>I have some data in a couple of temporary tables in a schema in the live 
>database. I want to copy the data to the same schema on a test database 
>that is on the same server.
>
> What is the best method of doing this? Can I use SQL Developer/PL/SQL, or 
> must I use an external tool?
>
> Thanks
>
> CJM
>

I've tried using the Import From Database feature in OEM, but when I enter 
the Host Credentials I get the following error:


0
cjmnews04 (80)
11/2/2006 11:49:36 AM
"CJM" <cjmnews04@REMOVEMEyahoo.co.uk> wrote in message 
news:4qu2buFoeuvbU1@individual.net...
>I have some data in a couple of temporary tables in a schema in the live 
>database. I want to copy the data to the same schema on a test database 
>that is on the same server.
>
> What is the best method of doing this? Can I use SQL Developer/PL/SQL, or 
> must I use an external tool?
>
> Thanks
>
> CJM
>

I've tried using the Import From Database feature in OEM, but when I enter
the Host Credentials I get the following error:

RemoteOperationException: ERROR: Wrong password for user

I've looked around for a solution to this but all the examples found point 
towards a permissions issue on Windows. unfortunately our server runs Linux! 
Anybody experienced this problem on a Linux box?

Any alternative methods of copying the data?

Thanks

Chris 


0
cjmnews04 (80)
11/2/2006 11:53:26 AM
CJM wrote:
> I have some data in a couple of temporary tables in a schema in the live
> database. I want to copy the data to the same schema on a test database that
> is on the same server.
>
> What is the best method of doing this? Can I use SQL Developer/PL/SQL, or
> must I use an external tool?
>
> Thanks
>
> CJM

You Oracle utilities Export and Import.   To export from Live, from the
command-line on the server:

exp system/<system_password>@<live_db> file=<filename>
log=<log_filename> owner=<schema_name> [
tables=<comma_separated_list_of_table_names> ]

To import into Test:

imp system/<system_password>@<test_db> file=<filename>
log=<log_filename>  fromuser=<schema_name> touser=<schema_name>

HTH

-g

0
gareth2106 (865)
11/2/2006 12:07:47 PM
Depending on the size of the tables, you could also create a DB link in
your Dev database pointing to the schema in the production database,
and then ...
small tables > create table abc as select * from abc@myprod;    or
large tables + 10g > datapump with db link

just 2 more ideals

0
dbaguy_ott (71)
11/2/2006 4:27:30 PM
Good point about the temporary tables. To make things easy, make sure
your environment has this set
export ORACLE_HOME=<path to oracle home>
export PATH=$ORACLE_HOME/bin;./$PATH
export ORACLE_SID=<Sid of DB you are exporting>

Also, if you cannot do it, ask you nearest DBA : )

Frank van Bortel wrote:
> CJM schreef:
> > I have some data in a couple of temporary tables in a schema in the live
> > database. I want to copy the data to the same schema on a test database that
> > is on the same server.
> >
> > What is the best method of doing this? Can I use SQL Developer/PL/SQL, or
> > must I use an external tool?
> >
> > Thanks
> >
> > CJM
> >
> >
>
> If they are really temporary tables, the methods
> described won't work. You can:
> - use an insert into remote table from temporary
>   table during the session
> - use an insert into local table from temporary
>   table, and use one of the methods described
>   by others.
>
> The fact you use temporary tables and want to
> copy them, makes me fear your mistake oracle for
> an other product, and you are not using temporary
> tables at all.
> --
> Regards,
> Frank van Bortel
> 
> Top-posting is one way to shut me up...

0
mfullerton (23)
11/3/2006 1:04:44 AM
CJM schreef:
> I have some data in a couple of temporary tables in a schema in the live 
> database. I want to copy the data to the same schema on a test database that 
> is on the same server.
> 
> What is the best method of doing this? Can I use SQL Developer/PL/SQL, or 
> must I use an external tool?
> 
> Thanks
> 
> CJM 
> 
> 

If they are really temporary tables, the methods
described won't work. You can:
- use an insert into remote table from temporary
  table during the session
- use an insert into local table from temporary
  table, and use one of the methods described
  by others.

The fact you use temporary tables and want to
copy them, makes me fear your mistake oracle for
an other product, and you are not using temporary
tables at all.
-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
0
11/3/2006 8:11:08 AM
"Frank van Bortel" <frank.van.bortel@gmail.com> wrote in message 
news:eidoeq$t45$1@news4.zwoll1.ov.home.nl...
>
> If they are really temporary tables, the methods
> described won't work. You can:
> - use an insert into remote table from temporary
>  table during the session
> - use an insert into local table from temporary
>  table, and use one of the methods described
>  by others.
>
> The fact you use temporary tables and want to
> copy them, makes me fear your mistake oracle for
> an other product, and you are not using temporary
> tables at all.
> -- 

Sorry... I meant temporary as in 
tables-that-I-have-created-for-a-temporary-purpose, not Temporary Tables!

I wondered whether it might be misconstrued and should have used another 
phrase. 


0
cjmnews04 (80)
11/3/2006 10:31:48 AM
I looked at using EXP/IMP but the environment wasn't set up correctly 
(ORACLE_HOME not set etc) so while my colleagues was looking into that I 
looked at using SQL*Plus. Initially, I was going to use the DB Link/'Create 
Table As...' method, but then I came across this method:

copy from uid/pwd@DB to uid/pwd@DB2 replace TABLENAME using Select * from 
TABLENAME;

....and it worked a treat.

Thanks to all those who helped.

Chris 


0
cjmnews04 (80)
11/3/2006 10:37:15 AM
Reply: