Drop and Recreate Tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Drop and Recreate Tables

  1. #1
    Join Date
    Sep 2000
    Posts
    64
    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




    Sofiane

  2. #2
    Join Date
    Sep 2000
    Posts
    384
    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
    Radhakrishnan.M

  3. #3
    Join Date
    Sep 2000
    Posts
    64
    Hello,

    We are using Oracle 7.3.4.

    Thanks

    Sofiane

  4. #4
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    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

  5. #5
    Join Date
    Aug 2000
    Posts
    87
    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.

  6. #6
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    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

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