Drop and Recreate Tables

[Sofiane]

Hello,

Suppose that you would like to redefine the storage paremters of table extents (Modifiy the initial and next params), you have to drop and recreate the table (am I right ?).

Suppose that you have constraints, indexes, grants. Many views, synonyms and procedures are depended of the table.

To keep the same structure, the constraints, indexes and grants must be redefined. The views, synonyms and procedures must be recompiled.

(So, before dropping the initial table, many script must be developed, using oracle dico, ...).

Is there a better (quicker) way to do all this ?

All you suggestions are welcomed.

NB : WHAT I INTEND TO DO IS TO REDEFINE THE 'INITIAL' AND 'NEXT' params for the table with extents more than 5).

Awaiting for reply, thank you in advance.

Sofiane

[omegamark]

If your database is 8i .Then you need not drop the table and recreate them.Only thing is you have to rebuild the index.

Just alter table tablename move tablespace tablespace name storage (-----);
will do the work for you.

after doing analyze the table , rebuild the index and triggers and alalyze the indexes

[Sofiane]

Hello,

We are using Oracle 7.3.4.

Thanks

[ligang]

I could not find out a better solutions , You can rebuild index
any way , But I am afraid you should do some thing different
to tables

[vinodkmg]

Hi,

Before u drop the table ,take the export of the table.Then,drop the table and recreate the table with different storage clause and import.

Cheers,
Vinod.

[dknight]

Moving the tablespace has caused some problems for me.

I noticed that some of the views owned by MDSYS are now invalid.

Perhaps, I have failed to recompile packages.

Any suggestions?

Thanks