Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to make a quick copy ?

Re: How to make a quick copy ?

From: joel garry <joel-garry_at_home.com>
Date: 28 Feb 2007 16:57:53 -0800
Message-ID: <1172710673.751471.203830@m58g2000cwm.googlegroups.com>


On Feb 27, 10:40 am, "dean" <deanbrow..._at_yahoo.com> wrote:
> On Feb 27, 12:27 pm, Jerome Vitalis
>
>
>
>
>
> <vitalismanN0S..._at_gmail.com.invalid> wrote:
> > Peter Keckeis wrote:
> > > Hi all,
>
> > > what do you suggest to copy about 2000000 rows from table_a to table_b,
> > > where both have the same column definitions?
>
> > > Oracle 10g
>
> > > Is this a possible solution?
> > > SQL> CREATE TABLE table_b AS (SELECT * FROM table_a);
>
> > > or how can i, after exporting table_a with the EXP utility,
> > > import the data into the new table_b?
>
> > > or do you have a better way?
>
> > You can use CTAS in NOLOGGING mode to speed things up. Be sure to read
> > about all the implications of this clause before using it
>
> truncate table A;
>
> insert /* +append*/ into A select * from B;
>
> Would that not work?

Well, if you are truncating, that implies you are doing it more than once. If you are doing it more than once, they might not have the same columns any more... and then there are backup issues.

CTAS and append work just fine as long as you know the implications (as Jerome noted). Generally, specifying all columns explicitly is considered the better practice, but really it depends on the requirements - is it something to be done just a few times or regularly, will it be controlled by just one person, what are uptime and recovery requirements, how fast is the hardware, are there time window issues, why do they have the same columns, what else is the db being used for, are the storage parameters appropriate and so on.

I find "quick copy" and "millions of records" often are exclusive.

jg

--
@home.com is bogus.
http://www.infiltration.org/abandoned.html
Received on Wed Feb 28 2007 - 18:57:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US