Table Reoraganization
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Table Reoraganization

  1. #1
    Join Date
    Aug 2000
    Posts
    21

    Table Reoraganization

    Hi
    I am planning to reorganize a table because of space problem and this has not been organized for the last 2 years.

    Pls suggest me which way is a better/advantageous/safe one of

    1) EXP/IMP or
    2) DBMS_REDEFINITION.

    My table got BLOB columns, This table is assigned to a specific table space which has got 65 datafiles of size 2GB each file, NO partitions but got Indexes. has a primary key but no foreign keys.
    Pls suggest me a simple Idea/plan anybody
    It's windows 2003 server with Oracle 10gR1.
    and can not afford any down time


    Thanks and regards.
    BSR

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    You can do
    alter table move
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Why do you want to reorg?

  4. #4
    Join Date
    Jun 2007
    Location
    İstanbul/Turkey
    Posts
    25
    after moving the table,remember to rebuild the indexes on it.(alter index ... rebuild)

  5. #5
    Join Date
    Jun 2007
    Location
    İstanbul/Turkey
    Posts
    25
    and also you can move your blob column to another tablespace by this command,so your lob ll be separated to another tablespace but table stays in the original tablespace.

    ALTER TABLE test_lobtable
    MOVE LOB(image) STORE AS (
    TABLESPACE lob_data2
    )
    /

  6. #6
    Join Date
    Aug 2000
    Posts
    21
    Hi Malay & Balki
    Thanks for the suggestion. I have already done with this ALTER TABLE MOVE procedure and rebuilding the indexes, Thank you guys.

    Hi Tamil

    As I have already done this ALTER TABLE MOVE procedure for few tables. Do I still need to reorg these tables?? OR is it better to reorg the entire database. The size of the database is close to 970GB

    We have few more tables with these BLOB columns which are consuming the diskspace alarmingly. These tables are more transaction oriented, on an average there may be 30,000 thousand records will be inserted daily in each table and almost the same amount of old records will be purged. And this has been a routine work for the last 2 years, so I thought it is better to reorganize these tables rather going for the entire database. Could you please suggest me on this.

    Thanks in advance
    BSR

Posting Permissions

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



Click Here to Expand Forum to Full Width