Re: copying tables from one database to another
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 20 Jan 2010 06:13:48 -0800 (PST)
Message-ID: <9fe72484-265b-4969-8971-a900e86b23d6_at_r5g2000yqb.googlegroups.com>
On Jan 20, 7:41 am, gazzag <gar..._at_jamms.org> wrote:
> On 20 Jan, 11:35, Andrew <marlow.and..._at_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
Date: Wed, 20 Jan 2010 06:13:48 -0800 (PST)
Message-ID: <9fe72484-265b-4969-8971-a900e86b23d6_at_r5g2000yqb.googlegroups.com>
On Jan 20, 7:41 am, gazzag <gar..._at_jamms.org> wrote:
> On 20 Jan, 11:35, Andrew <marlow.and..._at_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 -- Received on Wed Jan 20 2010 - 08:13:48 CST