Prev: Oracle 10g or 11g certification?
Next: oracle RowType issue
From: Andrew on 20 Jan 2010 06:35
How do people copy tables from one database to another? The docs say
that imp/exp are deprecated. I tried the copy command in sqlplus but
it destroys the indexes and default attributes! Arrgghh.

I have a feeling sql loader can be used to do the import but how do I
do the export first? Obviously any export will have to produce a file
that is suitable for use to sql loader.

Excuse my ignorance: I am more used to sybase where you can use bcp to
bcp-out followed by bcp-in.

Regards,

Andrew Marlow
From: gazzag on 20 Jan 2010 07:41
On 20 Jan, 11:35, Andrew <marlow.and...(a)googlemail.com> wrote:
> How do people copy tables from one database to another? The docs say
> that imp/exp are deprecated. I tried the copy command in sqlplus but
> it destroys the indexes and default attributes! Arrgghh.
>
> I have a feeling sql loader can be used to do the import but how do I
> do the export first? Obviously any export will have to produce a file
> that is suitable for use to sql loader.
>
> Excuse my ignorance: I am more used to sybase where you can use bcp to
> bcp-out followed by bcp-in.
>
> Regards,
>
> Andrew Marlow

What version of Oracle? A couple of suggestions:

1. As you say, imp and exp are deprecated but have been superceded by
DataPump:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/part_dp.htm#i436481

2. Create a database link and CREATE TABLE AS SELECT or INSERT
INTO ...over the link.

Some more ideas here:
http://groups.google.co.uk/group/comp.databases.oracle.server/browse_thread/thread/25d55ebf0e706312/f7aea9b2c161a1b8?hl=en#f7aea9b2c161a1b8

HTH

-g
From: Mark D Powell on 20 Jan 2010 09:13
On Jan 20, 7:41 am, gazzag <gar...(a)jamms.org> wrote:
> On 20 Jan, 11:35, Andrew <marlow.and...(a)googlemail.com> wrote:
>
> > How do people copy tables from one database to another? The docs say
> > that imp/exp are deprecated. I tried the copy command in sqlplus but
> > it destroys the indexes and default attributes! Arrgghh.
>
> > I have a feeling sql loader can be used to do the import but how do I
> > do the export first? Obviously any export will have to produce a file
> > that is suitable for use to sql loader.
>
> > Excuse my ignorance: I am more used to sybase where you can use bcp to
> > bcp-out followed by bcp-in.
>
> > Regards,
>
> > Andrew Marlow
>
> What version of Oracle?  A couple of suggestions:
>
> 1.  As you say, imp and exp are deprecated but have been superceded by
> DataPump:
>
> http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/part_d...
>
> 2.  Create a database link and CREATE TABLE AS SELECT or INSERT
> INTO ...over the link.
>
> Some more ideas here:http://groups.google.co.uk/group/comp.databases.oracle.server/browse_...
>
> HTH
>
> -g

"deprecated" [sic] is not the same thing as obsoleted. Both exp and
imp still exist on 10g and 11g. If you are migrating the data fom 9i
to 10g you would likely stil use these utilities, namely the 9i exp
followed by the 10g imp.

The closest tool in Oracle to BCP is sqlldr which is used to load
delimited or fixed position data into existing Oracle tables. To
extract data in delimited format you could just spool the output of a
query via SQLPlus. Just set the pagesize to zero, turn off feedback,
and trim the trailing white space via set pagesize 0, set feedback
off, set trimspool on, etc.... See the SQLPlus manual on the set
command options.

HTH -- Mark D Powell --

 | 
Pages: 1
Prev: Oracle 10g or 11g certification?
Next: oracle RowType issue