Oracle 12cbigfile表空間物件遷移

Oracle bigfile表空間中物件移動到普通表空間，由於bigfile對日常的維護，管理和備份帶來一些不便，經過評估決定將bigfile中的物件移到普通表空間。

環境說明：資料庫 Oracle 12c-12.1 OS:HP-UX

以下為操作步驟：

在操作之前確保資料庫已完成rman備份，以便遇到問題時，可以及時進行恢復。

1、確定要移動的物件和物件大小

SQL>select object_name,object_type from dba_objects where tablespace_name='APP_TBS';
SQL>select owner,TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLESPACE_NAME='APP_TBS';
SQL>SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name
FROM DBA_SEGMENTS
WHERE 
SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION','INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY', 'LOBINDEX', 'LOBSEGMENT', 'LOB PARTITION')
AND TABLESPACE_NAME='APP_TBS' 
ORDER BY bytes DESC;

2、檢查表的並行度

SQL>select owner,TABLE_NAMETABLESPACE_NAME from dba_tables where TABLESPACE_NAME='APP_TBS';

3、檢查索引並行度

SQL>select index_name, degree,status from dba_indexes where table_name='&table' ;

4、生成移動物件的指令碼

(1)生成move table的指令碼

SQL>select 'ALTER TABLE '||owner||'.'||table_name||' move tablespace '||'APP_TBS_TBS;' from dba_tables where tablespace_name='APP_TBS';

如果表大於2G，可以使用parallel選項並並行移動表，

select 'ALTER TABLE '||owner||'.'||table_name||' move tablespace '||'APP_TBS_TBS parallel 8;' from dba_tables where tablespace_name='APP_TBS';

(2)生成移動分割槽表的指令碼

--生成移動空分割槽表的指令碼

SQL>select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,'ALTER TABLE '||TABLE_OWNER ||'.'||table_name||' MOVE PARTITION '||partition_name||' TABLESPACE APP_TBS_TBS ;' SQLT from dba_tab_partitions where TABLESPACE_NAME='APP_TBS' 
          and 
            PARTITION_NAME not in(SELECT segment_name
           FROM DBA_SEGMENTS
            WHERE
            SEGMENT_TYPE ='TABLE PARTITION'    AND TABLESPACE_NAME='APP_TBS');

--生成分割槽表的指令碼

SQL>select 'ALTER TABLE '||TABLE_OWNER ||'.'||table_name||' MOVE PARTITION '||partition_name||' TABLESPACE APP_TBS_TBS UPDATE INDEXES;' from dba_tab_partitions where TABLESPACE_NAME = 'APP_TBS';

(3)生成移動子分割槽的指令碼

SQL>SELECT 'ALTER TABLE ' ||TABLE_OWNER ||'.' ||TABLE_NAME ||' MOVE SUBPARTITION ' ||SUBPARTITION_NAME ||' TABLESPACE APP_TBS_TBS parallel 8 UPDATE INDEXES;' FROM DBA_TAB_SUBPARTITIONS where TABLESPACE_NAME = 'APP_TBS';"

(4)生成修改分割槽表的預設表空間指令碼

SELECT 'ALTER TABLE ' ||TABLE_OWNER ||'.' ||TABLE_NAME ||' MODIFY DEFAULT ATTRIBUTES FOR PARTITION ' ||PARTITION_NAME ||' TABLESPACE APP_TBS_TBS;' FROM DBA_tab_partitions WHERE tablespace_name='APP_TBS';

(5)生成移動lob欄位的指令碼

SQL>select 'ALTER TABLE'||owner||'.'||table_name||' MOVE tablespace APP_TBS_tbs LOB('||column_name||') STORE AS (TABLESPACE APP_TBS_TBS);' from dba_lobs where tablespace_name='APP_TBS';
SQL>select 'ALTER TABLE'||table_owner||'.'||table_name||'MOVE partition'||Partition_name||'lo b('||column_name||')'||'STORE AS (TABLESPACE APP_TBS_TBS) ;'from dba_lob_partitions 
where TABLESPACE_NAME = 'APP_TBS';

(6)在新表空間rebuild索引

select 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE '||'APP_TBS_IDX online parallel 8;' from dba_indexes where tablespace_name='APP_TBS';

(7)在新的表空間rebuild分割槽索引

SQL>select 
'alter index '||owner||'.'||segment_name||' rebuild partition '|| partition_name||' tablespace APP_TBS_IDX;'
from dba_segments where tablespace_name='APP_TBS' and segment_type='INDEX PARTITION';

(8)在新的表空間rebuild子分割槽分割槽索引

SQL>select 'alter index '||owner||'.'||segment_name||
' rebuild subpartition '|| partition_name||' tablespace APP_TBS_IDX online;'
from dba_segments where tablespace_name='APP_TBS' and segment_type='INDEX SUBPARTITION';
SQL>select 'alter index '||index_owner||'.'||index_name||'rebuild partition'||partition_name||'tablespace APP_TBS_TBS;' from dba_ind_partitions where tablespace_name='APP_TBS';

5、按照第4步生成的指令碼移動物件

6、檢查表空間使用率

SQL>select * from (
Select a.tablespace_name,
to_char(a.bytes/1024/1024,'99,999.999') total_bytes,
to_char(b.bytes/1024/1024,'99,999.999') free_bytes,
to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'99,999.999') use_bytes,
to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%' use
from (select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select c.tablespace_name,
to_char(c.bytes/1024/1024,'99,999.999') total_bytes,
to_char( (c.bytes-d.bytes_used)/1024/1024,'99,999.999') free_bytes,
to_char(d.bytes_used/1024/1024,'99,999.999') use_bytes,
to_char(d.bytes_used*100/c.bytes,'99.99') || '%' use
from
(select tablespace_name,sum(bytes) bytes
from dba_temp_files group by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool group by tablespace_name) d
where c.tablespace_name = d.tablespace_name

7、檢查物件和索引狀態

SQL>select object_name,object_type from dba_objects where tablespace_name='APP_TBS';
SQL>select owner,TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLESPACE_NAME='APP_TBS';
SQL>SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name
FROM DBA_SEGMENTS
WHERE 
SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION','INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY', 'LOBINDEX', 'LOBSEGMENT', 'LOB PARTITION')
AND TABLESPACE_NAME='APP_TBS' 
ORDER BY bytes DESC;

8、修改索引和表的並行度

SQL>alter table tablename parallel 1;
SQL>alter index indexname noparallel;

-the end