Re: Database reorg question

I definitley will not drop the table. IT IS ERROR PRONE, trying to recreate everything.
You can solve this without losing your triggers and views.

1. Identify the tables that need to be reorganized
2. CREATE tablename_TEMP for each table as SELECT * from original table
3. Truncate the original table, with DROP STORAGE option (no DELETE TRIGGERs will be fired)
4. Manually allocate a large extent (which will be the second extent for that table), large enough to hold the entire data in the first 2 extents.(remember, TRUNCATE...DROP STORAGE, still will retain the INITIAL extent) Use the ALTER TABLE with the ALLOCATE EXTENT option to do this.
5. Now that your table has decent storage parameters, (and you have not lost your views, constraints and triggers) INSERT INTO original_table as SELECT * FROM tablename_TEMP. If you have INSERT triggers, you should disable them temporarily.
6. Before step(3)- TRUNCATE step - you may want to query on the constraints that reference the table and disable them. After INSERTing data back in to the table, you can enable them.

Hope this helps. If you use this approach, please let me know if you ran into any difficulties.

Regards,
CM

In article <352A07CE.CF2513A8_at_yahoo.com>,   hpdba_at_yahoo.com wrote:
>
> I would like to automate our database reorgs, mainly for tables that
> have many extents or chained rows. I think I can come up with a process
> that identifies the tables that need reorganized and what action to take
> on them. The problem is that the process usually requires an export,
> drop table, and import. When the table is dropped you loose triggers on
> the table, views become invalid and need recreated, and referential
> constraints from other tables need rebuilt. I want to write scripts that
> query the data dictionary and dynamically create the scripts for
> recreating views, triggers, and constraints.
>
> Based on your experience, do you think this is do-able? Is it dangerous
> because of the possibility of missing something? How do you normally
> handle these kinds of reorgs? Do you normally have a set of scripts that
> you use to rebuild the constraints, views, and triggers for each table?
> Is it unrealistic to expect to automate this process in the manner I
> have described?
>
> Thanks!
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Thu Apr 09 1998 - 16:26:23 CDT