Browse oracle-l: This Month's Archive • Main Archive Page • Related posts • Previous by Date • Next by Date

RE: A question on table export

From: "Blanchard William" <William.Blanchard@xxxxxxxxxx>
To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 1 Jul 2008 11:48:35 -0500

Why not just do an insert?

INSERT INTO <table_b> SELECT * FROM <table_a>;

________________________________

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

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

Pat 

________________________________

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.

DECLARE
  CURSOR c1 IS 
    SELECT * 
      FROM source_table;
  row_cnt NUMBER := 0;
BEGIN
  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
      COMMIT;
      row_cnt := 0;
    END IF;
  END LOOP;
  COMMIT;
END;
/

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.

Pat 

________________________________

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

I have an Oracle 9i (9.2.0.7.0) database.

One question on table export.

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

Both the tables are in the same schema.

Thanks, 

-- 

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:
http://emaildisclaimer.medtronic.com

References:

RE: A question on table export
From: Elliott, Patrick

RE: A question on table export
From: Elliott, Patrick

RE: A question on table export

Other related posts: