Re: Q: emulating SQLPLUS COPY

Thomas Kyte <tkyte_at_oracle.com> wrote in message news:<a8s8br01aqf_at_drn.newsguy.com>...
> In article <3cb0ee43_at_news.victoria.tc.ca>, yf110_at_vtn1.victoria.tc.ca says...
> >
> >Thomas Kyte (tkyte_at_oracle.com) wrote:
> >: In article <3cae4695_at_news.victoria.tc.ca>, yf110_at_vtn1.victoria.tc.ca says...
> >: >
> >: >Hello...
> >: >
> >: >I can use SQL PLus COPY command to copy a large table (with no constraints
> >: >or indexes) and it takes about 35 minutes.
> >: >
>
> >: hows about:
>
> >: insert /*+ append */ into local_table
> >: select * from remote_table;
> >
> >I've tried that. The two problems with that are either the rollback
> >segments run out of space or the remote system times out because it cannot
> >maintain a consistent view of its copy of the table (which I guess is
> >basically the same problem - lack of space).
> >
>
> two problems with the above statement:
>
> 1) the append hint on the insert will not do undo, it writes above the hwm of
> the table directly. If the table is 'nologging', it won't even generate redo
>

SQL> create table t (f1 varchar2(100)) nologging;

Table created.

SQL> insert /*+ append */ into t select sysdate from dual;

1 row created.

SQL> rollback;

Rollback complete.

SQL> select * from t;

no rows selected

SQL>  Doesn't it mean that undo IS generated ?

> 2) the committing of the sqlplus copy command will NOT lessen the change of the
> ora-1555 on the remote site. The query stays open from START to FINISH. So,
> the sqlplus copy command stands the same exact chance as the "insert select"
> does -- the QUERY never closes hence the read consistency issue is there from
> the get go for BOTH approaches.
>
>
> It is not a "lack of space" that causes 1555's, it is improperly sized rollback
> segments. They need to be permanently larger (they need to be large enough so
> as to NOT wrap during the longest running of any query in your system).
>
> So, insert /*+ append */ will not blow out local rbs AND the query has the same
> exact change of 1555 with "insert select" as it does with sqlplus copy since
> BOTH approaches keep the select statement open for the same amount of time!
>
> >That is why I'm trying COPY - it allows a commit to occur frequently so we
> >don't have that problem. (I'm not entirely clear why committing at this
> >end should solve the "consistent view" problem at the remote end, but it
> >does.)
>
> no, no it doesn't. You got "lucky" is all. You have the same exact requirement
> with copy as with the other approaches as regards the read consistency..
>
> >
> >The problem with COPY is that it replaces the entire table, which means
> >that in the next replication step (which is done by Oracle replication via
> >snapshots), the entire table is copied again.
> >
> >Other tables in the system are copied via a procedure that updates changed
> >rows and inserts new rows. (Deletes are ignored) This way the oracle
> >replication which runs next can automatically minimize the data copied to
> >the next set of hosts.
> >
> >The COPY command is really to provide a base case so that I know what
> >should be possible in terms of time to copy.
> >
> >(And no, there is no possibility of increasing the amount of space.)
> >
> >
> >
> >: never do procedurally what you can simply do in a single statement.
> >
> >
> >
> >
> >: >I want to do the same thing via PL/SQL, but it takes much longer (ten
> >: >hours).
> >: >
> >: >My PL/SQL code has the following very simple outline. Initially the local
> >: >table is empty, and has no constraints or indexes.
> >: >
> >: >
> >: > cursor C is select * from remote_table
> >: >
> >: > one_row local_table%rowtype; -- both tables have same layout
> >: >
> >: > open C
> >: > loop
> >: > fetch C into one_row
> >: > exit when end of data
> >: >
> >: > insert into local_table values
> >: > ( one_row.col1 ,
> >: > one_row.col2 ,
> >: > ... ,
> >: > one_row.col67
> >: > );
> >: >
> >: > end loop
> >: > close C
> >: >
> >
> >I forgot to mention that I am commiting every 100 rows. I've tried a few
> >different numbers for that, every 10 rows, and also every 10,000 rows.
> >That didn't seem to make much difference.
> >
Received on Mon Apr 08 2002 - 14:31:01 CDT