DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: change tablespace for objects

  1. #1
    Join Date
    May 2001
    Posts
    285

    Arrow

    After creating the db objects, how can I change the tablespace for them? Do I have to use Export/Import utility? Also, shall we always put data and indexes into different tablespaces for performance consideration?

    Thanks a lot!

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Yes, data and indexes should be put in seperate tablespaces.

    In 8.1.x, you can use alter table xyz move tablespace new_tbs for tables and alter index xyz_pk rebuild tablespace new_tbs for indexes.
    Jeff Hunter

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Be careful when you define the PK of a table: if you do not include USING INDEX clause, Oracle will build it in the defualt tablespace which is often the DATA, not the INDEX tablespace.

    Here is an example:

    ALTER TABLE table_name
    ADD CONSTRAINT pk_column_name PRIMARY KEY (sid)
    USING INDEX
    PCTFREE 10
    INITRANS 4
    MAXTRANS 255
    TABLESPACE indx
    STORAGE (FREELIST GROUPS 2 FREELISTS 4)
    /

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