Re: big table transfer, best way?

From: Ranganathan Chakravarthi <ranga_at_shell.one.net>
Date: 1997/05/19
Message-ID: <5lppl3$fds_at_news.one.net>#1/1

Kirill Richine (kirill_at_cs.ualberta.ca) wrote:
: I am wondering if there is a good way to transfer data from one
: schema to another, a slightly different one.
:
: Currently we try to do:
:
: insert into A_NEW (A_NEW_COL1, A_NEW_COL2)
: select A_OLD_COL1, A_OLD_COL2
: from A_OLD;
:
: This works fine for small tables. For big tables, however, there is a
: problem with the rollback segment size.

  You could bypass the redo-log entries by using the UNRECOVERABLE option

  CREATE TABLE A_NEW UNRECOVERABLE
                     ^^^^^^^^^^^^^

  AS
  SELECT a_new_col1, a_new_col2
    FROM a_old;

  Table creation is faster this way because redo log entries are not written   and the rollback segment is not used.
  You should also note that media recovery will not recreate the table.

:
: We also tried separating the command into two parts like
:
: ... where rownum < 1000...
: commit
: ....
: ... where rownum >= 1000 ...
: etc
:
: but it does not seem to work.
:
: Thank you.
: k&
:

  Hope this helps.

• Ranga

  ---

      If it doesn't work, don't worry; if it did, you are out of a job!

---

Ranga Chakravarthi                                      e-mail: ranga_at_one.net
-----------------------------------------------------------------------------

Received on Mon May 19 1997 - 00:00:00 CEST