Oracle 12c bigfile表空间对象迁移

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_used100/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