關於move table和rebuild index大量操作的記錄

來源:互聯網
上載者:User

關於move table和rebuild index大量操作的記錄

批量move oldtablespace資料表空間下的table到newtablespace,查詢並執行查詢結果即可

select 'alter table '||table_name||' move tablespace newtablespace'

from user_all_tables 

where table_space='oldtablespace';

批量rebuild oldtablespace資料表空間下的index到newtablespace,查詢並執行查詢結果即可

select 'alter index '|| index_name || ' rebuild  tablespace newtablespace;'
 from user_indexes
 where  tablespace_name='oldtablespace' ;

帶有lob欄位的表做move時lob欄位需要單獨move

ALTER TABLE AUDIT_RECORD MOVE LOB(lobrow1) STORE AS (TABLESPACE newtablespace);

ALTER TABLE AUDIT_RECORD MOVE LOB(lobrow2) STORE AS (TABLESPACE newtablespace);
 

ALTER TABLE AUDIT_RECORD MOVE TABLESPACE newtablespace;

 

或者
ALTER TABLE test2 MOVE

              TABLESPACE users

                  LOB (lobrow1) STORE AS lobsegment

              (TABLESPACE newtablespace);

另外exp/imp遷移帶有lob欄位的  在執行IMP時需要將原lob欄位所在的tablespace建好再匯入,匯入後再考慮move到其他tablespace

exped/imped可以用remap_tablespace參數指定將資料匯入到指定的資料表空間,無需考慮該項

本文永久更新連結地址:

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.