Re: Cloning a table

If you use the SQL*plus built-in "copy" command, it can be configured to do commits after a certain number of records.

-Peter

Silver wrote:
>
> This is possibly a very basic question, but I am not able to figure it
> out. Imagine a large table TAB1(col1 number, col2 varchar2(50)) with
> few million rows. Compare the following two methods.
>
> 1) create table TAB1_CLONE (
> col1,
> col2,
> constraint PK_TAB1_CLONE primary key(col1)
> )
> as
> select col1, col2 from tab1;
>
> 2) create table TAB1_CLONE
> col1 number,
> col2 varchar2(50),
> constraint PK_TAB1_CLONE primary key(col1)
> );
>
> insert into TAB1_CLONE (col1, col2) select col1, col2 from TAB1;
> commit;
>
> Now my understanding is that if TAB1 is very large, using method 2
> might give some sort of "rollback segment too small" or "transaction
> log full" kind of error, since we commit only after all rows have been
> inserted. On the other hand, method 1 does not seem to have any such
> errors, and seems faster. How does this work ?
>
> Thanks.
>
> - Silver
Received on Thu Nov 29 2001 - 06:46:17 CST