ORACLE数据库表分区压缩说明

数据库压缩开始
--1、最开始先执行数据的导出和删除
--2、执行下面语句查看空间压缩情况
select a.file#,
a.name,
a.bytes / 1024 / 1024 CurrentMB,
ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,
(a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,
'alter database datafile ''' || a.name || ''' resize ' ||
ceil(HWM * a.block_size / 1024 / 1024) || 'M;' ResizeCMD
from v$datafile a,
(select file_id, max(block_id + blocks - 1) HWM
from dba_extents
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM * block_size) > 0
and a.NAME like '%TBS_PART_01%' --根据月份修改数值，当前情况是月份，二月份修改为 and a.NAME like '%TBS_PART_02%'
order by 2;

--3、复制2查询的字段‘ResizeCMD’的结果，如果 resize 后面的数字小于1024M，则改为 1024M
alter database datafile 'E:ORADATA_PARTTBS01TBS_PART_0101.DBF' resize 1M;
--修改为
alter database datafile 'E:ORADATA_PARTTBS01TBS_PART_0101.DBF' resize 1024M;
--4、执行修改后的语句。
--数据库压缩完成。

--如果第3部中的 resize 后面的数字大于1024M，则往下运行步骤。。。。。
--如果第3部中的 resize 后面的数字大于1024M，则往下运行步骤。。。。。
--如果第3部中的 resize 后面的数字大于1024M，则往下运行步骤。。。。。
--如果第3部中的 resize 后面的数字大于1024M，则往下运行步骤。。。。。
--下面假如有这种情况： alter database datafile 'E:ORADATA_PARTTBS01TBS_PART_0131.DBF' resize 1524M;

--6、移动表前先对表空间做整理
alter tablespace TBS_PART_0131 coalesce;

--7、找到2步骤中 ResizeCMD 为 alter database datafile 'E:ORADATA_PARTTBS01TBS_PART_0131.DBF' resize 1524M;的行的 ‘a.file#’值

--8、修改下面file_id= 的值为 找到的 ‘a.file#’ 值。（假设找到的值为 861）

set heading off
set echo off
set feedback off
set termout on
spool d:aaa.sql
--移动表
select DISTINCT 'alter table '|| segment_name || ' move tablespace BK_SPACE;' from dba_extents where segment_type='TABLE' and owner = 'XM_RC_DBCENTER' and file_id=861;
--移动索引
select DISTINCT 'alter index '|| segment_name || ' rebuild tablespace BK_SPACE;' from dba_extents where segment_type='INDEX' and owner = 'XM_RC_DBCENTER' and file_id=861;
--移动分区表
select DISTINCT 'alter table '|| segment_name || ' move partition '|| partition_name || ' tablespace BK_SPACE;' from dba_extents where segment_type='TABLE PARTITION' and owner = 'XM_RC_DBCENTER' and file_id=861;
--移动分区索引
select DISTINCT 'alter index '|| segment_name || ' rebuild partition '|| partition_name || ' tablespace BK_SPACE;' from dba_extents where segment_type='INDEX PARTITION' and owner = 'XM_RC_DBCENTER' and file_id=861;

spool off
set heading off
set echo off
set feedback off
set termout on
spool d:bb.sql
--移动表
select DISTINCT 'alter table '|| segment_name || ' move tablespace BK_SPACE;' from dba_extents where segment_type='TABLE' and owner = 'XM_RT_DBCENTER' and file_id=861;
--移动索引
select DISTINCT 'alter index '|| segment_name || ' rebuild tablespace BK_SPACE;' from dba_extents where segment_type='INDEX' and owner = 'XM_RT_DBCENTER' and file_id=861;
--移动分区表
select DISTINCT 'alter table '|| segment_name || ' move partition '|| partition_name || ' tablespace BK_SPACE;' from dba_extents where segment_type='TABLE PARTITION' and owner = 'XM_RT_DBCENTER' and file_id=861;
--移动分区索引
select DISTINCT 'alter index '|| segment_name || ' rebuild partition '|| partition_name || ' tablespace BK_SPACE;' from dba_extents where segment_type='INDEX PARTITION' and owner = 'XM_RT_DBCENTER' and file_id=861;
spool off

--9、复制上面步骤8修改后的语句，在plsql工具中打开“Command Window” 窗口，黏贴语句。

--10、找到d盘下的aaa.sql文件，复制内容到plsql工具中，点击运行，（内容如下）
alter table TRRT_BUS_INOUT_GPS_HIS move partition TRRT_BUS_INOUT_HIS_20160131 tablespace BK_SPACE;
alter index IND_TRRT_BUS_INOUT_GPS_HIS_1 rebuild partition TRRT_BUS_INOUT_HIS_20160131 tablespace BK_SPACE;

--11、然后修改语句再执行压缩表空间的语句
alter database datafile 'E:ORADATA_PARTTBS01TBS_PART_0131.DBF' resize 1524M;
--修改为
alter database datafile 'E:ORADATA_PARTTBS01TBS_PART_0131.DBF' resize 1024M;


--12、最后再把步骤10中aaa.sql文件的语句修改，（把BK_SPACE改回原来的TBS_PART_0131）再运行
alter table TRRT_BUS_INOUT_GPS_HIS move partition TRRT_BUS_INOUT_HIS_20160131 tablespace TBS_PART_0131;
alter index IND_TRRT_BUS_INOUT_GPS_HIS_1 rebuild partition TRRT_BUS_INOUT_HIS_20160131 tablespace TBS_PART_0131;

--数据库压缩完成
开始
--1、最开始先执行数据的导出和删除
--2、执行下面语句查看空间压缩情况
select a.file#,
a.name,
a.bytes / 1024 / 1024 CurrentMB,
ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,
(a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,
'alter database datafile ''' || a.name || ''' resize ' ||
ceil(HWM * a.block_size / 1024 / 1024) || 'M;' ResizeCMD
from v$datafile a,
(select file_id, max(block_id + blocks - 1) HWM
from dba_extents
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM * block_size) > 0
and a.NAME like '%TBS_PART_01%' --根据月份修改数值，当前情况是月份，二月份修改为 and a.NAME like '%TBS_PART_02%'
order by 2;

--3、复制2查询的字段‘ResizeCMD’的结果，如果 resize 后面的数字小于1024M，则改为 1024M
alter database datafile 'E:ORADATA_PARTTBS01TBS_PART_0101.DBF' resize 1M;
--修改为
alter database datafile 'E:ORADATA_PARTTBS01TBS_PART_0101.DBF' resize 1024M;
--4、执行修改后的语句。
--数据库压缩完成。

--如果第3部中的 resize 后面的数字大于1024M，则往下运行步骤。。。。。
--如果第3部中的 resize 后面的数字大于1024M，则往下运行步骤。。。。。
--如果第3部中的 resize 后面的数字大于1024M，则往下运行步骤。。。。。
--如果第3部中的 resize 后面的数字大于1024M，则往下运行步骤。。。。。
--下面假如有这种情况： alter database datafile 'E:ORADATA_PARTTBS01TBS_PART_0131.DBF' resize 1524M;

--6、移动表前先对表空间做整理
alter tablespace TBS_PART_0131 coalesce;

--7、找到2步骤中 ResizeCMD 为 alter database datafile 'E:ORADATA_PARTTBS01TBS_PART_0131.DBF' resize 1524M;的行的 ‘a.file#’值

--8、修改下面file_id= 的值为 找到的 ‘a.file#’ 值。（假设找到的值为 861）

set heading off
set echo off
set feedback off
set termout on
spool d:aaa.sql
--移动表
select DISTINCT 'alter table '|| segment_name || ' move tablespace BK_SPACE;' from dba_extents where segment_type='TABLE' and owner = 'XM_RC_DBCENTER' and file_id=861;
--移动索引
select DISTINCT 'alter index '|| segment_name || ' rebuild tablespace BK_SPACE;' from dba_extents where segment_type='INDEX' and owner = 'XM_RC_DBCENTER' and file_id=861;
--移动分区表
select DISTINCT 'alter table '|| segment_name || ' move partition '|| partition_name || ' tablespace BK_SPACE;' from dba_extents where segment_type='TABLE PARTITION' and owner = 'XM_RC_DBCENTER' and file_id=861;
--移动分区索引
select DISTINCT 'alter index '|| segment_name || ' rebuild partition '|| partition_name || ' tablespace BK_SPACE;' from dba_extents where segment_type='INDEX PARTITION' and owner = 'XM_RC_DBCENTER' and file_id=861;

spool off
set heading off
set echo off
set feedback off
set termout on
spool d:bb.sql
--移动表
select DISTINCT 'alter table '|| segment_name || ' move tablespace BK_SPACE;' from dba_extents where segment_type='TABLE' and owner = 'XM_RT_DBCENTER' and file_id=861;
--移动索引
select DISTINCT 'alter index '|| segment_name || ' rebuild tablespace BK_SPACE;' from dba_extents where segment_type='INDEX' and owner = 'XM_RT_DBCENTER' and file_id=861;
--移动分区表
select DISTINCT 'alter table '|| segment_name || ' move partition '|| partition_name || ' tablespace BK_SPACE;' from dba_extents where segment_type='TABLE PARTITION' and owner = 'XM_RT_DBCENTER' and file_id=861;
--移动分区索引
select DISTINCT 'alter index '|| segment_name || ' rebuild partition '|| partition_name || ' tablespace BK_SPACE;' from dba_extents where segment_type='INDEX PARTITION' and owner = 'XM_RT_DBCENTER' and file_id=861;
spool off

--9、复制上面步骤8修改后的语句，在plsql工具中打开“Command Window” 窗口，黏贴语句。

--10、找到d盘下的aaa.sql文件，复制内容到plsql工具中，点击运行，（内容如下）
alter table TRRT_BUS_INOUT_GPS_HIS move partition TRRT_BUS_INOUT_HIS_20160131 tablespace BK_SPACE;
alter index IND_TRRT_BUS_INOUT_GPS_HIS_1 rebuild partition TRRT_BUS_INOUT_HIS_20160131 tablespace BK_SPACE;

--11、然后修改语句再执行压缩表空间的语句
alter database datafile 'E:ORADATA_PARTTBS01TBS_PART_0131.DBF' resize 1524M;
--修改为
alter database datafile 'E:ORADATA_PARTTBS01TBS_PART_0131.DBF' resize 1024M;


--12、最后再把步骤10中aaa.sql文件的语句修改，（把BK_SPACE改回原来的TBS_PART_0131）再运行
alter table TRRT_BUS_INOUT_GPS_HIS move partition TRRT_BUS_INOUT_HIS_20160131 tablespace TBS_PART_0131;
alter index IND_TRRT_BUS_INOUT_GPS_HIS_1 rebuild partition TRRT_BUS_INOUT_HIS_20160131 tablespace TBS_PART_0131;

--数据库压缩完成