更改Oracle資料庫表的資料表空間

oracle|資料|資料庫
在Oracle資料庫管理系統中，建立庫表（table）時要分配一個資料表空間（tablespace），如果未指定資料表空間，則使用系統使用者確省的資料表空間。

在Oracle實際應用中，我們可能會遇到這樣的問題。處於效能或者其他方面的考慮，需要改變某個表或者是某個使用者的所有表的資料表空間。通常的做法就是首先將表刪除，然後重建立表，在建立表時將資料表空間指定到我們需要改變的資料表空間。如果該使用者已經儲存了大量資料，這種辦法就就顯得不是很方便，因為有大量資料需要提前備份出來。下面介紹一種利用資料庫的匯出/匯入功能來實現重新組織資料庫資料表空間的方法。

下面是一個簡單的例子，假定要將使用者oa下的全部表從資料表空間A轉換到資料表空間B，具體步驟（在Oracle 9i for linux環境）如下：
1.1. 匯出db_zgxt下的所有表（Dos控制台下） 匯出db_zgxt下的所有表（Dos控制台下）1. 匯出db_zgxt下的所有表（Dos控制台下）
EXP oa/password@pararmount_server FILE=d:\10_27_oa.dmp LOG=d:\10_27_oa.LOG


2. 刪除oa下的所有表（在SQL/PLUS中）

可以採用批處理的方式刪除掉db_zgxt下的所有表，產生批處理的語句如下：
--其中set head off將表頭資訊去掉
SET HEAD OFF
SPOOL c:\drop_tables.sql
select 'drop table '||table_name||';' from user_tables;
spool off;
@c:\drop_tables.sql;
sql >@drop_tables.sql
3. 採用匯入參數 INDEXFILE匯入oa使用者下的所有表（Dos控制台下）
把建表和索引的語句匯出到檔案,其中建表語句是加註釋的，並沒有實際匯入

IMP oa/password@paramount_server FULL=Y FILE=d:\10_27_oa.dmp INDEXFILE=d:\altertablespace_table_index.SQL LOG=d:\altertablespace.LOG


其中，指定參數INDEXFILE後，系統就將建立表和索引的語句寫到一個檔案，這裡是altertablespace_table_index.SQL 中。該檔案中包含了所有建立索引（CREATE INDEX）語句和建立表（CREATETABLE）語句，但是這裡所有建立表的語句均加了注釋標誌。在任何文字編輯器中開啟並編輯該檔案，去掉所有建立表語句的注釋標誌，將所有的資料表空間名稱由A替換為B，同時對所有的建立索引語句加上注釋標誌。這些工作作完以後，在SQL/PLUS中運行該指令檔，這些表就被建立，其資料表空間由A變為B。
採用匯入參數INDEXES=N 和IGNORE=Y將db_zgxt使用者的表資料匯入庫中（Dos控制台下）
4. 採用匯入參數INDEXES=N 和IGNORE=Y將oa使用者的表資料匯入庫中（Dos控制台下）

IMP oa/password@paramount_server FULL=Y INDEXES=N FILE=d:\10_27_oa.dmp  IGNORE=Y LOG=d:\altertablespace.LOG

其中，參數INDEXES=N是指將資料匯入資料庫中時不加索引。IGNORE=Y是指在匯入資料過程中，忽略表已經存在（table already exists）的錯誤。這樣Oralce就將資料和一些約束條件匯入到第3步建立的表中。


5. 建立索引

在文字編輯器中重新開啟在第3步中建立的altertablespace_table_index.SQL 指令檔，這次，將所有建立表（CREATE TABLE）的語句加上注釋標誌，然後將所有的建立索引（CREATE INDEX）語句去掉注釋標誌。在SQL/PLUS中再次運行該指令檔。

至此，我們就成功完成了將oa使用者下的全部表從資料表空間A轉換到資料表空間B的工作。當然你可以只匯入一部分表。

註：本文參考網上搜到的一篇文章，本人在更新的平台（oracle 9i)上實際操作後修改成此文。如果侵犯到誰的著作權，請與我聯絡。