Re: Looking for DDL for reorganizing Indizes and Tables

When you perform an export, by default it will eport the table with constraints and triggger, if it has indexes, it will export the indexes, if it is a full export, it will export grants and synonyms. In this situation, you just need to export and inport your tables.

If you want to reorganize your table such as a change in the pctfree, pctused, inittrans, maxtrans and the storage parameters, you just change it before export. If you want to reorganize the table and put all data into one extent, export with compress=y. If you want to reorganize your indexes, you can export and import with indexes=n and indexfile=y. then run the script produced.

HTH John Chiu

Harald Wakonig <wakonig_at_compuserve.com> wrote in message news:38651081.B84B2393_at_compuserve.com...
> Hi,
>
> I need to reorganize (import-export) some big tables, which
> * have some constraints (PK and Foreign-Key)
> * Indizes (of course)
> * Snapshot-Log or are the tables corresponding tables on the replicated
> site.
>
> I am looking for Skripts which generate the DDL for re-creating the
> table in an intelligent way.
>
> (1) Create table
> (2) import data
> (3) add constraints (PK, unique key - which will create the index, FK)
> (4) add other indizes
> (5) add triggers
> (6) add grants, synonyme
> (7) recompile the objects which have become invalid
>
> and on the second server, the replication-site
> (8) create the snapshot
> (9) do a full-refresh
>
> * If there are third-party products which can help, it is also fine!
>
> If there are skripts and products which can do this for a whole
> tablespace, its fine too!
>
> Furthermore I look for a skript / stored procedure that will help me
> with Index-Maintainance;
>
> * drop and recreate all (or many) indizes
> - if the index has been created by adding a primary key constraint,
> the package should consider this, and recreate the constraint too!
>
> - it should be possible to change storage-parameters like ini_trans
> (not possible with rebuild!!!)
>
> Yes, I know about the "rebuild"-option, but I definitely want to drop
> and recreate the Index!
>
> Thank you for your help,
>
> Harald
>
>
>
>
Received on Sun Dec 26 1999 - 06:20:11 CST