Re: insert and commit 1000 records at a time

From: MaryAnn Atkinson <maryann_30_at_yahoo.com>
Date: Fri, 17 Oct 2003 10:43:29 -0800
Message-ID: <F001.005D37FE.20031017104329@fatcity.com>

I still dont get it...
I dont know what I have done to have me confused more than I first asked the question...

• Jared.Still_at_radisys.com wrote:
  > My bad. The SQL is not quite right: 'append' is a hint:
  > alter table resource nologging;
  

dont know what nologging does.

> insert /*+ append */ into resource
> select * from rqmt;

me no understand... me no see 1000 anywhere...

> Read up on direct load insert in the concepts manual,
> along with nologging.

And one more thing... If I asked the question, thats just it, I asked a question. If anyone knows the answer, please offer it here, but dont tell me to go read it up in the national enquirer or I-dont-know-where-you-mean...

Folks, please, if we have something to offer, lets go ahead, if not, just bypass that email and read another one...

thanks,
maa

>
> Bypass the redo and undo - no need for commits.
>
> Just back it up when finished.
>
> Jared
>
>
>
>
>
>
>
> MaryAnn Atkinson <maryann_30_at_YAHOO.COM>
> Sent by: ml-errors_at_fatcity.com
> 10/16/2003 01:49 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
> Subject: Re: insert and commit 1000 records at a time
>
>
>
> --- Jared.Still_at_radisys.com wrote:
> > That will work, slowly.
> > You might like to try something like this
> > insert into resource
> > nologging
> > select * from rqmt
> > append;
>
>
> How's that commiting every 1000 records?
>
> > Read up on the 'append' and 'nologging' first.
>
> ???
>
>
>
>
>
> >
> >
> >
> >
> >
> >
> >
> > Maryann Atkinson <maryann_30_at_yahoo.com>
> > Sent by: ml-errors_at_fatcity.com
> > 10/16/2003 08:54 AM
> > Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com>
> > cc:
> > Subject: insert and commit 1000 records at a time
> >
> >
> > I have 2 tables, Rqmt and Resource, same structure.
> >
> > I need to take all almost-one-million records from Rqmt and
> > insert them to Resource. So far this worked ok:
> >
> > DECLARE
> > RowCount NUMBER := 0;
> >
> > BEGIN
> > SELECT Count(*)
> > INTO RowCount
> > FROM RQMT;
> >
> > IF RowCount > 0 THEN
> >
> > INSERT INTO RESOURCE
> > SELECT Resource_Id, Classification
> > FROM RQMT;
> >
> > RowCount := SQL%RowCount;
> >
> > DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
> > Rows
> > transitioned.');
> > COMMIT;
> > ELSE
> > DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
> > transitioned.');
> > END IF;
> >
> > EXCEPTION
> > WHEN OTHERS THEN
> > Raise;
> > END;
> > /
> >
> >
> > But now I need to commit every 1000 records. Any suggestions as to
> > what would be the best way? I dont think ROWNUM would help here,
> > because it would pick the same 1000 records every time, causing
> > primary key violation...
> >
> >
> > thx
> > maa
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Maryann Atkinson
> > INET: maryann_30_at_yahoo.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting
> services
> >
> ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like
> subscribing).
> >
> Please see the official ORACLE-L FAQ: http://www.orafaq.net

> Author: MaryAnn Atkinson
> INET: maryann_30_at_yahoo.com

---

Do you Yahoo!?
The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: MaryAnn Atkinson
  INET: maryann_30_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Oct 17 2003 - 13:43:29 CDT