Re: Q: emulating SQLPLUS COPY

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
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.
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp