RE: A question on table export

  • From: "Elliott, Patrick" <patrick.elliott@xxxxxxxxxxxxx>
  • To: "Elliott, Patrick" <patrick.elliott@xxxxxxxxxxxxx>, "anuragdba@xxxxxxxxx" <anuragdba@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 1 Jul 2008 09:40:05 -0500

I haven't worked with bulk inserts much, so anyone who has please feel free to 
respond with an improved solution using bulk inserts.


From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Elliott, Patrick
Sent: Tuesday, July 01, 2008 9:28 AM
To: anuragdba@xxxxxxxxx; ORACLE-L
Subject: RE: A question on table export

The only reason that I can think of to import into the same schema is to maybe 
prevent the rollback segment from filling up undo or you are getting snapshot 
too old.  If you are having trouble with too many rows on the insert, then you 
just need to code the insert in pl/sql with intermediate commits.

    SELECT *
      FROM source_table;
  row_cnt NUMBER := 0;
  FOR r1 IN c1 LOOP
    INSERT INTO destination_table
    VALUES (r1.col1, r1.col2, ... r1.coln);
    row_cnt := row_cnt +1;
    IF row_cnt > 1000 THEN
      row_cnt := 0;
    END IF;

Just change the 1000 to a higher or lower number depending on your 
requirements.  Change source_table and destination_table to the real table 
names and col1, col2, .. to the real column names.


From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Anurag Verma
Sent: Monday, June 30, 2008 6:10 PM
Subject: A question on table export

I have an Oracle 9i ( database.

One question on table export.

Can we export a table and import into another table (which has the same 

Both the tables are in the same schema.



Anurag Verma,
Database Administrator
ERCOT(Electric Reliability Council of Texas),
Texas 76574

[CONFIDENTIALITY AND PRIVACY NOTICE] Information transmitted by this email is 
proprietary to Medtronic and is intended for use only by the individual or 
entity to which it is addressed, and may contain information that is private, 
privileged, confidential or exempt from disclosure under applicable law. If you 
are not the intended recipient or it appears that this mail has been forwarded 
to you without proper authority, you are notified that any use or dissemination 
of this information in any manner is strictly prohibited. In such cases, please 
delete this mail from your records. To view this notice in other languages you 
can either select the following link or manually copy and paste the link into 
the address bar of a web browser:

Other related posts: