Jul,01
TIP
: How to avoid using large Rollback segment by using copy command utility ?
By using copy command you can reduce
the burden on a rollback segment by
dividing the entire inserting data
into pieces or batches which commit individually and thus avoid of using large
rollback segment.
Copy command is used to copy
data from one table to another with in
the same database or from one database to another across network.
usage: COPY FROM <db> TO <db>
<opt> <table> { (<cols>) } USING <sel>
<db> : database string,
e.g., scott/tiger@d:chicago-mktg
<opt> : ONE of the
keywords: APPEND, CREATE, INSERT or REPLACE
<table>: name of the destination table
<cols> : a comma-separated list of destination column aliases
<sel> : any valid SQL
SELECT statement
Append : - Create table in the destination if it does not exist and ignore
if it exist and insert rows
Create :- Create table in the destination and insert rows
Insert :- insert the rows only . Error occurs if the table does not
exist.
Replace :- Drop the existing destination table and creates new table with
data.
Set arraysize and set copycommit
controls the transactions commit batch mechanism.
set arraysize 1000 determine the 1000 records of the total records will be retrieved in each batch.
set copycommit 1 indicates one batch should be commited at a time.
So if we are inserting 100,000
records then data will commit after
inserting 1000 records every time.
If you have a small rollback segment
, set arraysize to less number of records and copycommit to 1.
ora816 SamSQL :> copy from scott/tiger@Source
-
to –
scott/tiger@destination
create bonusnew(ename) -
using -
select ename from bonus;
Here bonus is the table in the source
location , bonusnew will be the name of the
table created in the destination.
End of Tip
|
Welcome
to send comments or feedback at Wadhwa_s@Hotmail.com |
|
|
Copyright © Oracle Techniques All Rights Reserved
|