ORACLE修改資料表空間方法

標籤：

一、使用imp/exp。先匯出源庫，再建立新庫把資料表空間建立好，然後再匯入。（據說這樣可以，前提是新的庫裡面不能有與源庫相同名字的資料表空間。有待驗證！） 

二、使用指令碼進行修改。據目前所瞭解，正長情況下需要修改表的空間和表的索引的空間，如果涉及到BOLB欄位的表，修改的方式又不一樣了！ 
正常情況下的修改指令碼： 
1.修改表的空間 
alter table TABLE_NAME move tablespace TABLESPACENAME 

查詢目前使用者下的所有表 
select ‘alter table  ‘|| table_name ||‘  move tablespace tablespacename;‘  from user_all_tables; 

2.修改表的索引的空間 
alter index INDEX_NAME rebuild tablespace TABLESPACENAME 

查詢目前使用者下的所有索引 
select ‘alter index ‘|| index_name ||‘ rebuild tablespace tablespacename;‘ from user_indexes; 

可以使用指令碼執行查詢的結果，這樣就可以批量處理！ 

不正常情況即含有BLOB欄位的表: 
參考下面的文檔 

本人已經執行過，是可行的，但是不知道有沒有漏掉資料庫中其它對象。是否還存在有對象的資料表空間需要修改，需要進一步發現或有資料庫高手幫忙講解則不勝感激！目前的修改在項目中還沒有出現問題！ 

在移植看注意研究了下ORACLE ALTER TABLE MOVE 的文法: 

       ALTER TABLE table_name MOVE [ONLINE] tablespace_name; 
通過上面的語句可以移植表到新資料表空間, 

如果要移植LOB字典需要參考以下文法: 
ALTER TABLE table_name LOB (lob_item) STORE AS [lob_segment] 
      ( 
        TABLESPACE tablespace_name 
                   (STORAGE.....) 
           ENABLE|DISABLE STORAGE IN ROW 
           CHUNK integer 
           PCTVERSION integer 
            RETENTION 
            FREEPOOLS integer 
            CACHE|NOCACHE|CACHE READS 
           INDEX lobindexname 

(TABLESPACE tablesapce_name 

((STORAGE.....)) 
) 

.... 

註解： 

LOB (lob_item):表中的lob欄位 
STORE AS [lob_segment]:每個lob欄位在表建立後系統都會自動單獨建立一個段,可以通過這個參數手動指定一個段名 
   tablespace_name:LOB欄位新的儲存資料表空間 
(STORAGE.....):指定tablespace_name的儲存屬性 
    ENABLE STORAGE IN ROW:如果設定了enable storage in row 那麼oracle會自動將小於4000bytes的資料存放區在行內, 這是ORACLE的預設值，對於大於4000位元組的lob欄位儲存在lob段（同disable storage in row），在表段將保留36-84位元組的控制資訊。對於disable storage in row，Oracle將lob欄位分開儲存在lob段中，而僅僅在行位置保留20位元組的指標。對於相當於disable storage in row的這部分（也就是單獨儲存在LOB段的這部分資料），UNDO僅僅是記錄指標與相關lob索引改變，如果發生更新操作等DML操作，未經處理資料將保留在LOB段。 

    DISABLE STORAGE IN ROW:如果DISABLE這個屬性,那麼lob資料會在行外儲存,行內只儲存該lob值得指標,而且這個屬性在表 
建立後只能在MOVE表時才可以被改變 
    CHUNK:是一個很特別的屬性,對一次LOB資料的操作(插入或更新),因該分配多少儲存空間,指定的值最好是資料庫塊的倍數,而且指定的值不能大於資料表空間區間中NEXT的值, 要不然ORACLE會return一個錯誤,如果以前已經設定這個值了,那麼在後期指定的值是不能被改變的。 

storage as ( CHUNK bytes )表示對於disable storage in row的這部分，最小的LOB塊的大小，必須是資料庫塊(DB_BLOCK_SIZE)的整數倍。一個chunk最多隻保留一行LOB資料，也就是說，如果你設定了32K的CHUNK，但是如果LOB欄位大小隻有4K，也將佔用32K的空間 

storage as(cache|nocahce)表示是否允許lob段經過buffer cache並緩衝。預設是nocache，表示直接讀與直接寫，不經過資料庫的data buffer。所以，預設情況下，對於單獨儲存在LOB段的這部分資料，在發生物理讀的時候，是直接讀，如direct path read (lob) 

storage as(nocache logging |nocache nologging)，logging/nologging屬性只對nocache方式生效，預設是logging，如果是nologging方式，對於 儲存在行外的log部分，在update等DML操作時將不記錄redo日誌。 

    PCTVERSION integer、RETENTION:都是ORACLE用來管理LOB欄位鏡像資料的。在LOB 資料的更新過程中, 
ORACLE沒有用UNDO TABLESPACE空間，而是從LOB欄位所在的資料表空間裡劃分一段空間來做鏡像空間的, 
這個空間的大小由PCTVERSION參數控制,預設值為10,代表劃分資料表空間的10%作為鏡像空間, 
每個鏡像空間的單元大小由CHUNK參數指定,pctversion可以使用在manual undo mode和automatic undo mode 環境中. 
retention應用了automatic undo mode中的undo_retention通過時間來管理lob鏡像空間. 
pctversion和retention不能同時被指定.建議資料庫在automatic undo mode下使用retention參數。 
FREEPOOLS integer:給LOG segment指定free list.RAC環境下integer為執行個體的個數.單一實例環境下為1.在automatic undo mode下oracle預設採用 
FREEPOOLS來管理空閑塊列表。除非我們在表的storage配置中指定了freelist groups參數. 
CACHE|NOCACHE|CACHE READS:指定lob塊是否在database buffer中緩衝. 
INDEX lobindexname (TABLESPACE tablesapce_name ((STORAGE.....):給lob列指定索引儲存參數 
舉例: 
SQL> show parameter db_create_file_dest 

SQL> create tablespace test datafile size 100M autoextend off; 
SQL> create table test(a varchar2(100), b clob, d blob) pctfree 10 tablespace test; 

SQL> desc test 
SQL> SELECT segment_name,tablespace_name,segment_type FROM dba_segments WHERE tablespace_name=‘TEST‘; 

我們發現每個LOB欄位單專屬一個LOGSEGMENT和LOBINDEX; 

SQL> set linesize 200 
col table_name format a5 
col column_name format a5 
SELECT b.table_name, 
a.segment_name, 
b.index_name, 
a.segment_type, 
b.column_name, 
a.tablespace_name, 
b.chunk, 
b.cache, 
b.freepools, 
b.pctversion, 
b.retention 
FROM dba_segments a,dba_lobs b 
WHERE a.segment_name = b.segment_name 
AND a.tablespace_name = ‘TEST‘ 
/ 

SQL> 

從上面的結果我們可以觀察到LOB欄位的各個屬性. 
下面我們對LOB欄位move到另一個資料表空間 

SQL> create tablespace lob_test datafile size 100M autoextend off; 

SQL> ALTER TABLE TEST MOVE LOB(B) STORE AS TEST_B ( 
TABLESPACE lob_test 
DISABLE STORAGE IN ROW 
CHUNK 16384 
RETENTION 
FREEPOOLS 1 
NOCACHE); 
SQL> ALTER TABLE TEST MOVE LOB(D) STORE AS TEST_D ( 
TABLESPACE lob_test 
DISABLE STORAGE IN ROW 
CHUNK 16384 
RETENTION 
FREEPOOLS 1 
NOCACHE); 
SQL> SELECT segment_name,tablespace_name,segment_type FROM dba_segments WHERE tablespace_name=‘TEST‘; 

SQL> set linesize 200 
col table_name format a5 
col column_name format a5 
SELECT b.table_name, 
a.segment_name, 
b.index_name, 
a.segment_type, 
b.column_name, 
a.tablespace_name, 
b.chunk, 
b.cache, 
b.freepools, 
b.pctversion, 
b.retention 
FROM dba_segments a,dba_lobs b 
WHERE a.segment_name = b.segment_name 
AND a.tablespace_name = ‘LOB_TEST‘ 
/SQL> 

在一些複雜情況下可能需要連表一起移植 
alter table table_name move [tablespace_name] lob (lob_item) store as [lobsegmentname] (tablespace tablespace_name.....); 
移植分區中lob 
alter table table_name move partition [partition_name] lob (lob_item) store as [logsegmentname] (tablespace_name.....); 
移植分區表 
alter table table_name move partition [partition_name] tablespace_name lob (lob_item) store as [logsegmentname] (tablespace_name.....); 
如果不需要修改lobsegmentname,可以同時移植多個列 
alter table table_name move lob (lob_item1,lob_item2,lob_item3...) store as [lobsegmentname] (tablespace tablespace_name.....); 



    LOB段也可以利用move來重整資料，以下的語句會將表與lob欄位move到指定的資料表空間： 

   alter table table_name move [tablespace tbs_name] 

lob(lob_field1,lob_field2) store as (tablespace new_tbs_name); 

如果LOB欄位在分區表中，則增加partition關鍵字，如 

   alter table table_name move [partition partname] [tablespace tbs_name] 

lob(field) store as (tablespace new_tbs_name); 



在資料庫中合理的儲存LOB列,不僅可以提升效能,而且還可以有效管理儲存空間.

ORACLE修改資料表空間方法