FAQ
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

--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Bradd Piontek at Jun 30, 2008 at 11:58 pm
    No. Why not just insert into the other table?
    On 6/30/08, Anurag Verma wrote:
    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
    --
    Sent from Gmail for mobile | mobile.google.com

    Bradd Piontek
    Twitter: http://www.twitter.com/piontekdd
    Oracle Blog: http://piontekdd.blogspot.com
    Linked In: http://www.linkedin.com/in/piontekdd
    Last.fm: http://www.last.fm/user/piontekdd/
    --
    http://www.freelists.org/webpage/oracle-l
  • Dennis Williams at Jul 1, 2008 at 12:36 am
    Anurag,

    Which database version are you on? Exp/Imp can't change table name, so the
    simple answer to your question is "no". But there are probably at least a
    dozen ways to achieve what you desire, so why don't you describe your
    situation in a little more detail? Please include how much data you are
    moving. Is this a one-time task of something you need to do on a regular
    basis?

    Dennis Williams
  • Elliott, Patrick at Jul 1, 2008 at 2:28 pm
    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_at_freelists.org 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

    --
    http://www.freelists.org/webpage/oracle-l
  • Elliott, Patrick at Jul 1, 2008 at 2:40 pm
    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_at_freelists.org On Behalf Of Elliott, Patrick
    Sent: Tuesday, July 01, 2008 9:28 AM
    To: anuragdba_at_gmail.com; 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_at_freelists.org 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

    --
    http://www.freelists.org/webpage/oracle-l
  • Asif Momen at Jul 1, 2008 at 4:47 pm
    Hi,
    Can we export a table and import into another table (which has the same
    structure)?
    Both the tables are in the same schema.
    No, not into another table. Why not insert directly into the destination table like:
    insert into dest select * from source;
    Regards
    Asif Momenhttp://momendba.blogspot.com

    On Tue, 7/1/08, Elliott, Patrick wrote:
    From: Elliott, Patrick
    Subject: RE: A question on table export
    To: "Elliott, Patrick", "anuragdba_at_gmail.com", "ORACLE-L"
    Date: Tuesday, July 1, 2008, 7:40 AM

    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_at_freelists.org
    On Behalf Of Elliott,
    Patrick
    Sent: Tuesday, July 01, 2008 9:28 AM
    To:
    anuragdba_at_gmail.com; 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_at_freelists.org
    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

    --
    http://www.freelists.org/webpage/oracle-l
  • Blanchard William at Jul 1, 2008 at 4:48 pm
    Why not just do an insert?


    INSERT INTO SELECT * FROM;

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Elliott, Patrick
    Sent: Tuesday, July 01, 2008 9:40 AM
    To: Elliott, Patrick; anuragdba_at_gmail.com; 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_at_freelists.org
    On Behalf Of Elliott, Patrick
    Sent: Tuesday, July 01, 2008 9:28 AM
    To: anuragdba_at_gmail.com; 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_at_freelists.org
    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

    --
    http://www.freelists.org/webpage/oracle-l
  • Yaping Chen at Jul 2, 2008 at 1:47 pm
    FYI

    [oracle_at_chen ~]$ exp test/test tables=t2 file=t2.dmp silent=y

    About to export specified tables via Conventional Path ...

    . . exporting table T2 10 rows exported

    Export terminated successfully without warnings.

    Edit dump file t2.dmp with vi tools, change all T2 words to T8 in the file,
    then save it.
    [oracle_at_chen ~]$ vi t2.dmp
    ...
    TABLE "T2"

    CREATE TABLE "T2" ("ID" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS

    255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "TEST"

    LOGGING NOCOMPRESS

    INSERT INTO "T2" ("ID") VALUES (:1)

    ^A^@^B^@^V^@^@^@^@^@^B^@Ã^B^@^@^B^@Ã^C^@^@^B^@Ã^D^@^@^B^@Ã^E^@^@^B^@Ã^F^@^@^B^@Ã^G^@^@^B^@Ã^H^@^@^B^@Ã<%5EA%5E@%5EB%5E@%5EV%5E@%5E@%5E@%5E@%5E@%5EB%5E@Ã%5EB%5E@%5E@%5EB%5E@Ã%5EC%5E@%5E@%5EB%5E@Ã%5ED%5E@%5E@%5EB%5E@Ã%5EE%5E@%5E@%5EB%5E@Ã%5EF%5E@%5E@%5EB%5E@Ã%5EG%5E@%5E@%5EB%5E@Ã%5EH%5E@%5E@%5EB%5E@Ã>
    ^@^@^B^@Ã <%5E@%5E@%5EB%5E@Ã>
    ^@^@^B^@Ã^K^@^@ÿà <%5E@%5E@%5EB%5E@Ã%5EK%5E@%5E@ÿÃ>¿

    CREATE INDEX "IDX_T2_ID" ON "T2" ("ID" ) PCTFREE 10 INITRANS 2 MAXTRANS 255

    STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "TEST"

    LOGGING

    ...

    [oracle_at_chen ~]$ strings t2.dmp
    EXPORT:V09.02.00

    UTEST

    RTABLES

    8192

    Fri Jun 27 0:34:44 2008t2.dmp

    #C##
    #C##
    +08:00
    BYTE

    INTERPRETED

    TABLE "T8"

    CREATE TABLE "T8" ("ID" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS

    255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "TEST"

    LOGGING NOCOMPRESS

    INSERT INTO "T8" ("ID") VALUES (:1)

    CREATE INDEX "IDX_T8_ID" ON "T8" ("ID" ) PCTFREE 10 INITRANS 2 MAXTRANS 255

    STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "TEST"

    LOGGING

    EXIT

    EXIT

    [oracle_at_chen ~]$ imp test/test file=t2.dmp silent=y
    Export file created by EXPORT:V09.02.00 via conventional path
    . importing TEST's objects into TEST

    . . importing table "T8" 10 rows imported

    Import terminated successfully without warnings.

    2008/7/1 Anurag Verma :
    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
    --
    Regards,
    Chen Yaping

    http://freelists.spaces.live.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Gus Spier at Jul 2, 2008 at 2:02 pm
    That will work ... Unless the string you propose to change shows up in other
    places (like in a value in one of the columns) ...

    Be careful

    It remains unclear why "CREATE TABLE AS ..." is not the solution of choice.

    Regards,
    Gus

    On Wed, Jul 2, 2008 at 9:47 AM, Yaping Chen
    wrote:
    FYI
    [oracle_at_chen ~]$ exp test/test tables=t2 file=t2.dmp silent=y

    About to export specified tables via Conventional Path ...
    . . exporting table T2 10 rows exported
    Export terminated successfully without warnings.

    Edit dump file t2.dmp with vi tools, change all T2 words to T8 in the file,
    then save it.
    [oracle_at_chen ~]$ vi t2.dmp
    ...
    TABLE "T2"
    CREATE TABLE "T2" ("ID" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
    255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "TEST"
    LOGGING NOCOMPRESS
    INSERT INTO "T2" ("ID") VALUES (:1)
    ^A^@^B^@^V^@^@^@^@^@^B^@�^B^@^@^B^@�^C^@^@^B^@�^D^@^@^B^@�^E^@^@^B^@�^F^@^@^B^@�^G^@^@^B^@�^H^@^@^B^@�<%5EA%5E@%5EB%5E@%5EV%5E@%5E@%5E@%5E@%5E@%5EB%5E@%C3%83%5EB%5E@%5E@%5EB%5E@%C3%83%5EC%5E@%5E@%5EB%5E@%C3%83%5ED%5E@%5E@%5EB%5E@%C3%83%5EE%5E@%5E@%5EB%5E@%C3%83%5EF%5E@%5E@%5EB%5E@%C3%83%5EG%5E@%5E@%5EB%5E@%C3%83%5EH%5E@%5E@%5EB%5E@%C3%83>
    ^@^@^B^@� <%5E@%5E@%5EB%5E@%C3%83>
    ^@^@^B^@�^K^@^@ÿ� <%5E@%5E@%5EB%5E@%C3%83%5EK%5E@%5E@%C3%83%C2%BF%C3%83>�
    CREATE INDEX "IDX_T2_ID" ON "T2" ("ID" ) PCTFREE 10 INITRANS 2 MAXTRANS
    255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "TEST"
    LOGGING
    ...

    [oracle_at_chen ~]$ strings t2.dmp
    EXPORT:V09.02.00
    UTEST
    RTABLES
    8192
    Fri Jun 27 0:34:44 2008t2.dmp
    #C##
    #C##
    +08:00
    BYTE
    INTERPRETED
    TABLE "T8"
    CREATE TABLE "T8" ("ID" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
    255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "TEST"
    LOGGING NOCOMPRESS
    INSERT INTO "T8" ("ID") VALUES (:1)
    CREATE INDEX "IDX_T8_ID" ON "T8" ("ID" ) PCTFREE 10 INITRANS 2 MAXTRANS
    255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "TEST"
    LOGGING
    EXIT
    EXIT

    [oracle_at_chen ~]$ imp test/test file=t2.dmp silent=y
    Export file created by EXPORT:V09.02.00 via conventional path
    . importing TEST's objects into TEST
    . . importing table "T8" 10 rows imported
    Import terminated successfully without warnings.

    2008/7/1 Anurag Verma :
    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


    --
    Regards,
    Chen Yaping

    http://freelists.spaces.live.com
    --
    Gus
    703 304 5587 (mobile)
    540 687 3370 (home)

    gus.spier_at_gmail.com

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 30, '08 at 11:10p
activeJul 2, '08 at 2:02p
posts9
users8
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase