Oracle 表压缩与表空间迁移

最新推荐文章于 2023-12-22 19:12:13 发布

原创最新推荐文章于 2023-12-22 19:12:13 发布 · 953 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #表空间迁移

oracle 专栏收录该内容

104 篇文章

订阅专栏

针对Oracle数据库因未做容量规划导致的空间不足问题，通过表压缩和迁移到新表空间的方法来减少磁盘占用并提高数据检索性能。文章详细介绍了新建表空间、单表及批量压缩的具体步骤，并提供了压缩前后效果对比。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle 表压缩与表空间迁移

概述

1、前期系统上线时，未进行容量规划，后期数据库膨胀速度很快，磁盘空间即将占满；
2、数据库服务器主要消耗的是IO资源，CPU资源平均利用率不到5%；
3、本文通过将表压缩、迁移到新表空间，减少磁盘空间占用，提高数据检索性能；
4、经过对比压缩前后的表空间使用情况，压缩比约为 40%；
5、数据无价，应用到生产库前，请做好备份；

分析待压缩的表

select table_name,
       tablespace_name,
       num_rows,
       blocks,
       row_movement,
       compression,
       compress_for,
       last_analyzed
  from user_tables
  where num_rows>0
 order by 2, 1;

新建表空间

create tablespace ts_data logging datafile 'd:\database\db_name\ts_data_01.dat' size 10m autoextend on next 100m maxsize 32767m extent management local default compress for oltp;
alter tablespace ts_data add datafile 'd:\database\db_name\ts_data_01.dat' size 10m autoextend on next 100m maxsize 32767m;
alter tablespace ts_data add datafile 'd:\database\db_name\ts_data_01.dat' size 10m autoextend on next 100m maxsize 32767m;

create tablespace ts_index logging datafile 'd:\database\db_name\ts_index_01.dat' size 10m autoextend on next 100m maxsize 32767m extent management local default compress for oltp;
alter tablespace ts_index add datafile 'd:\database\db_name\ts_index_02.dat' size 10m autoextend on next 100m maxsize 32767m;
alter tablespace ts_index add datafile 'd:\database\db_name\ts_index_03.dat' size 10m autoextend on next 100m maxsize 32767m;

单表压缩算法

1.压缩数据表

alter table tab_name move tablespace ts_data compress for oltp

2.压缩索引

alter index index_name rebuild tablespace ts_index compress

批量压缩算法

/*数据表压缩算法*/
select move_type, tablespace_name, table_name, index_name, size_g, status,move_sql from (
  -- 数据表
  select '1.table' as move_type,
         tablespace_name,
         table_name,
         '' as index_name,
         '' as status,
         cast(blocks * 8 / 1024 / 1024 as number(18, 2)) as size_g,
         'alter table ' || table_name || ' move tablespace ts_data compress for oltp;' as move_sql
    from user_tables a
   where a.table_name like 'TEST%'

  union all
  -- 索引表
  select '2.index' as move_type,
         a.tablespace_name,
         a.table_name,
         a.index_name as index_name,
         a.status,
         cast(a.leaf_blocks * 8 / 1024 / 1024 as number(18, 2)) as size_g,
         case
           when b.index_name is not null or a.index_type ='BITMAP' then
            'alter index ' || a.index_name || ' rebuild tablespace ts_index;'
           else
            'alter index ' || a.index_name || ' rebuild tablespace ts_index compress;'
         end as move_sql
    from user_indexes a 
         left join user_constraints b 
              on a.index_name = b.index_name and b.constraint_type = 'P'
   where a.table_name like 'TEST%'
) order by 1, 2, 3,4

更新表统计信息

select  table_name, 'call dbms_stats.gather_table_stats('''|| user ||''','''|| table_name ||''');' as sqls, 3 as t
from user_tables 
where table_name like 'TEST%'

表状态验证

select table_name, status, t from (
select  table_name,status, 1 as t from user_tables 
union all 
select index_name,status, 2 as t from user_indexes
) a
where status <> 'VALID'
and table_name like 'TEST%'
order by 3,1;

至此，本文结束。