copy2event.txt
10-20-2001 Bikle
-----------------

This file shows syntax and a screen dump related to using the SQL copy
command to copy rows into the EVENT table from the EVENTBIG table.  A
nice feature of the copy command is that we can specify that commits
occur during the running of the command.  These periodic commts
prevent the rollback segments from getting over filled with data which
would then cause the script to fail.

The first step to this effort is to create or verify that we have a
public database link which points to the database which contains both
the EVENT table and the EVENTBIG table.

The easiest way to do this is just assume a link exists and see if it
works in an sql statement:

testbikle01-oracle-% sqg

SQL*Plus: Release 8.1.6.0.0 - Production on Sat Oct 20 22:16:34 2001

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

22:16:34 SQL> select count(*) from event@BIKLEDEV;

  COUNT(*)
----------
         0

22:16:48 SQL> select count(*) from eventbig@BIKLEDEV;

  COUNT(*)
----------
   4961967

22:17:00 SQL> select count(*) from event;

  COUNT(*)
----------
         0

22:17:07 SQL> select count(*) from eventbig;

  COUNT(*)
----------
   4961967

22:17:22 SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
testbikle01-oracle-%

Next, we fashioned a useful copy command:

SET ARRAYSIZE 100 COPYCOMMIT 500

COPY FROM BIKLE/BIKLE@BIKLEDEV INSERT EVENT USING SELECT * FROM EVENTBIG WHERE EVENT_TIME>'01-JUL-01';

A screen dump is displayed below:

testbikle01-oracle-% sqg

SQL*Plus: Release 8.1.6.0.0 - Production on Sat Oct 20 22:23:42 2001

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

22:23:42 SQL> SET ARRAYSIZE 100 COPYCOMMIT 500
22:23:52 SQL> COPY FROM BIKLE/BIKLE@BIKLEDEV INSERT EVENT USING SELECT * FROM EVENTBIG WHERE EVENT_TIME>'01-JUL-01';

Array fetch/bind size is 100. (arraysize is 100)
Will commit after every 500 array binds. (copycommit is 500)
Maximum long size is 80. (long is 80)
   3364 rows selected from BIKLE@BIKLEDEV.
   3364 rows inserted into EVENT.
   3364 rows committed into EVENT at DEFAULT HOST connection.

22:24:36 SQL>
22:24:36 SQL> select count(*) FROM EVENTBIG WHERE EVENT_TIME>'01-JUL-01';

  COUNT(*)
----------
      3364

22:37:11 SQL> select count(*) FROM EVENT WHERE EVENT_TIME>'01-JUL-01';

  COUNT(*)
----------
      3364

22:37:25 SQL> select count(*) FROM EVENT;

  COUNT(*)
----------
      3364

22:37:34 SQL>