rebuild table with TOAD or use truncate/import?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: rebuild table with TOAD or use truncate/import?

  1. #1
    Join Date
    Aug 2000
    Location
    Alberta
    Posts
    82
    I have 4 table(s) in a database that are showing a high number of chained rows, should I :

    -open the database in mount mode, export the table(s), truncate the table & import the data back in
    or
    -open the database in mount mode, use the TOAD rebuild table utility to build the script, and run the script?

    The latter seems like a lot simpler/faster process, but are there any potential pitfalls?

    What is the best method to determine the optimal PCTFREE for these tables to prevent this in the future?


    thanks in advance

  2. #2
    Join Date
    Feb 2001
    Posts
    17
    Hi,

    I have not worked on TOAD, so I'll not comment on that.I suggest the following :-

    1) Analyze the table with chained rows option.Prior to executing this command, the chained_rows table should be created.If my memory goes right, there is a scirpt called utlchain.sql which creates this chained_rows table.After the table is created, execute 'analyze table <tablename> list chained rows'.This will insert the rowid's of the chained rows into the chained rows table.Based on these rowid's, extract them into some temporary table, delete these rows from the original table and reinsert them back from the temporary table.

    Second Alternative : Take an export of the table (No need to bring the database to a mount stage to do this.) Recreate the table with the revised pctfree setting.I don't think there is a hard and fast rule to find out the exact pctfree.You may need to make an approximate based on the expected update activity on the table.Reimport the data back.
    Ramki

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Well, no matter which method you choose you'll have to *open* the database, not just mount it....

    If you choose to rebuild with TOAD you'll have to have enough additional free space in your tablespace and you'll have to recreate all the indexes, constraints, grants, triggers, recompile all PL/SQL objects and views....

    Export method seem more straightforward to me...

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Nov 2000
    Posts
    15
    The solution put forth by ramk_vv is the Oracle solution, and is very straight forward and simple to implement. The TOAD solution should not be an option in this case unless you are a SQL Server DBA and can only relate to GUIs.
    Michael Auer
    Oracle DBA/Developer
    Oracle8i OCP

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