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
-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?
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.
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...
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
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.