Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003

    Question Unanswered: moving existing data into new tablespaces


    A few weeks ago I posted a question regarding 8g limit that my index
    tablespace hit. I got some good advice from Greg & Alan and recreated
    my index tablespace with multiple datafiles with 3.9g limit on each.
    All is well with the indexes now, things are running smooth.

    I'd like to do the same with my actual data tables. I've got one
    installation that has reached 8g on the actual data. This is what I
    understand I'll have to do:

    1. add additional datafiles to the existing tablespace, each with
    3.9g limit and LEAVE the original 8g datafile in place along with
    the new smaller datafiles


    2. create a new tablespace with multiple datafiles with 3.9g limit
    and move the original tables with data into it. I don't know how to
    do this other than move the original data to temporary tables, drop
    the original tables and recreate them in the new tablespace, then
    move the saved temporary data back.

    I'd like to loose the original 8g datafile and have all new datafiles
    with 3.9g limits, but want to avoid the copying back and forth of
    step 2.

    Any suggestions or other ideas?

    Thanks in advance.


  2. #2
    Join Date
    Jul 2003
    What is the significance of the 3.9gig limit??

    TO move a table:
    PHP Code:
    alter table tablename move tablespace newtablespacename

    I recommend rebuilding all your indexes after you do this and then analyzing your schema.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Nov 2002
    Desk, slightly south of keyboard

    you can move tables between tablespaces with the following command

    ALTER TABLE <tablename> MOVE TABLESPACE <tablename>

    indexes for the table will need to be rebuilt afterwards.

    ALTER INDEX <indexname> REBUILD

    Check the docs for additional parameters for the tablespace move, index rebuild etc.


  4. #4
    Join Date
    Sep 2003
    I understood Oracle & NTFS to allow files to grow to the physical
    device limits. But, I had an installation that hit 8g and could not
    extend any further. The replies I got from my original post told
    me of problems that others had with files going over 4g using NT.
    Not sure if the problem I had was NT or Oracle (I'm using Oracle
    7.3 and hope to upgrade soon.)

    Thanks for your input. I appreciate it.


  5. #5
    Join Date
    Sep 2003
    Thanks Bill. I'll give this a shot. Thanks again.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts