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: Q: emulating SQLPLUS COPY

Re: Q: emulating SQLPLUS COPY

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 9 Apr 2002 10:19:04 -0700
Message-ID: <a8v7q801tlq@drn.newsguy.com>


In article <9f17469e.0204081131.47680f04_at_posting.google.com>, igoriz_at_cmtk.net says...
>
>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 ?

only enough to undo the data dictionary updates:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects where 1=0;
Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t select * from all_objects;
17183 rows created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction;

 USED_UBLK


         1

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> commit; Commit complete.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t select * from all_objects; 17183 rows created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction;

 USED_UBLK


         9

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>

Undo do "undo" the operation is generated. Undo to "undo" the inserts is not.

That is why:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t select * from all_objects where rownum=1;

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from t; select * from t

              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

won't work for example. You've gotta commit the changes else the read consistency model we use that depends on the undo won't work in that transaction (cause there isn't any undo). Other sessions are not affected because they do not see the newly added data at all.

--
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 
Received on Tue Apr 09 2002 - 12:19:04 CDT

Original text of this message

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