ITPub博客

首页 > 数据库 > Oracle > oracle 静态数据压缩分离

oracle 静态数据压缩分离

原创 Oracle 作者:wangxiangtao 时间:2011-07-15 15:37:38 0 删除 编辑

最近一直在纠结数据库服务器空间的问题, 前段时间此服务器由于空间的问题调整过备份策略, 但是效果不是很明显,由于此库是一个类似数据仓库的系统, 里面有很多静态(用于只读的)的历史数据,由于是以前的老项目,增加硬件存储是非常地难, 你懂的。。。 呵呵 于是想到了数据库的压缩分离的方法,此方案主要有以下步骤:

A. A. 与项目经理会议,决定需要分离与压缩的对象

B. B. 建立新的历史表空间

C. C. 检查相关对象的索引 存储过程 包体 运行的job,以及将要运行的job

D. D. 先对数据进行迁移, 再对索引进行迁移

E. E. 将静态数据分割批量进行,每完成一批量,完成失效对象编译

$ORACLE_HOME/rdbms/admin/utlrp.sql

首先统计分离压缩前的数据大小:

[@more@]

最近一直在纠结数据库服务器空间的问题, 前段时间此服务器由于空间的问题调整过备份策略, 但是效果不是很明显,由于此库是一个类似数据仓库的系统, 里面有很多静态(用于只读的)的历史数据,由于是以前的老项目,增加硬件存储是非常地难, 你懂的。。。 呵呵 于是想到了数据库的压缩分离的方法,此方案主要有以下步骤:

A. 与项目经理会议,决定需要分离与压缩的对象

B. 建立新的历史表空间

C. 检查相关对象的索引 存储过程 包体 运行的job,以及将要运行的job

D. 先对数据进行迁移, 再对索引进行迁移

E. 将静态数据分割批量进行,每完成一批量,完成失效对象编译

$ORACLE_HOME/rdbms/admin/utlrp.sql

首先统计分离压缩前的数据大小:

SQL> select tablespace_name,sum(blocks),sum(bytes)/1024/1024/1024 from dba_segments where owner='P_STDT' group by tablespace_name;

TABLESPACE_NAME SUM(BLOCKS) SUM(BYTES)/1024/1024/1024

------------------------------ ----------- -------------------------

STDT 71815992 547.912536621094

STDT_INDEX 10784296 82.2776489257813

迁移前历史表空间的建立:

SQL> CREATE TABLESPACE STDT_HIS DATAFILE

2 '/data/app/oracle/oradata/mostdt4/stdt_his01.dbf' SIZE 25600M AUTOEXTEND OFF,

3 '/data/app/oracle/oradata/mostdt4/stdt_his02.dbf' SIZE 25600M AUTOEXTEND OFF,

4 '/data/app/oracle/oradata/mostdt4/stdt_his03.dbf' SIZE 25600M AUTOEXTEND OFF,

5 '/data/app/oracle/oradata/mostdt4/stdt_his04.dbf' SIZE 25600M AUTOEXTEND OFF

6 LOGGING

7 ONLINE

8 PERMANENT

9 EXTENT MANAGEMENT LOCAL AUTOALLOCATE

10 BLOCKSIZE 8K

11 SEGMENT SPACE MANAGEMENT AUTO

12 FLASHBACK ON;

Tablespace created

SQL> CREATE TABLESPACE STDT_HIS_INDEX DATAFILE

2 '/data/app/oracle/oradata/mostdt4/stdt_his_index01.dbf' SIZE 25600M AUTOEXTEND OFF

3 LOGGING

4 ONLINE

5 PERMANENT

6 EXTENT MANAGEMENT LOCAL AUTOALLOCATE

7 BLOCKSIZE 8K

8 SEGMENT SPACE MANAGEMENT AUTO

9 FLASHBACK ON;

Tablespace created

SQL> alter user p_stdt quota unlimited on stdt_his;

User altered

SQL> alter user p_stdt quota unlimited on stdt_his_index;

User altered

小试牛刀:

SQL> select count(*) from dba_indexes where table_name in('TBL_STDT_AH11_MB_1007','TBL_STDT_AH11_MB_1008', 'TBL_STDT_AH11_MB_1009', 'TBL_STDT_AH11_MB_1010','TBL_STDT_AH11_MB_1011');

COUNT(*)

----------

0

做压缩处理 并转移表空间

SQL> alter table TBL_STDT_AH11_MB_1007 move compress tablespace stdt_his;

Table altered

SQL>

SQL> alter table TBL_STDT_AH11_MB_1008 move compress tablespace stdt_his;

Table altered

SQL> alter table TBL_STDT_AH11_MB_1009 move compress tablespace stdt_his;

Table altered

SQL> alter table TBL_STDT_AH11_MB_1010 move compress tablespace stdt_his;

Table altered

SQL> alter table TBL_STDT_AH11_MB_1011 move compress tablespace stdt_his;

Table altered

压缩后:

SQL> select sum(bytes)/1024/1024/1024 from dba_segments where segment_name in ('TBL_STDT_AH11_MB_1007','TBL_STDT_AH11_MB_1008', 'TBL_STDT_AH11_MB_1009', 'TBL_STDT_AH11_MB_1010','TBL_STDT_AH11_MB_1011');

SUM(BYTES)/1024/1024/1024

-------------------------

1.4765625

没有出现什么问题, 继续。。。。。。

压缩前:

SQL> select count(*) from dba_indexes where table_name in('TBL_STDT_FJ08_MB_1006','TBL_STDT_FJ08_MB_1007','TBL_STDT_FJ08_MB_1008','TBL_STDT_GD01_MB_1007','TBL_STDT_GD01_MB_1008',

2 'TBL_STDT_GD01_MB_1009',

3 'TBL_STDT_GD01_MB_1010',

4 'TBL_STDT_GD01_MB_1011',

5 'TBL_STDT_GX03_MB_1007',

6 'TBL_STDT_GX03_MB_1008',

7 'TBL_STDT_GX03_MB_1009',

8 'TBL_STDT_GX03_MB_1010',

9 'TBL_STDT_GZ10_MB_1007',

10 'TBL_STDT_GZ10_MB_1008',

11 'TBL_STDT_GZ10_MB_1010',

12 'TBL_STDT_GZ10_MB_1011',

13 'TBL_STDT_GZ10_MB_1012',

14 'TBL_STDT_GZ10_MB_1013',

15 'TBL_STDT_GZ10_MB_1102',

16 'TBL_STDT_HB14_MB_1009',

17 'TBL_STDT_HB14_MB_1010',

18 'TBL_STDT_HB14_MB_1011',

19 'TBL_STDT_HB14_MB_1012',

20 'TBL_STDT_HB14_MB_1101',

21 'TBL_STDT_HB14_MB_1102',

22 'TBL_STDT_HB16_MB_1007',

23 'TBL_STDT_HB16_MB_1008',

24 'TBL_STDT_HB16_MB_1009',

25 'TBL_STDT_HB16_MB_1010',

26 'TBL_STDT_HLJ09_MB_1007',

27 'TBL_STDT_HLJ09_MB_1008',

28 'TBL_STDT_HLJ09_MB_1009',

29 'TBL_STDT_HLJ09_MB_1010',

30 'TBL_STDT_HLJ09_MB_1011',

31 'TBL_STDT_HLJ09_MB_1012',

32 'TBL_STDT_HN15_MB_1007',

33 'TBL_STDT_HN15_MB_1008',

34 'TBL_STDT_HN15_MB_1009',

35 'TBL_STDT_HN15_MB_1010',

36 'TBL_STDT_HN15_MB_1011',

37 'TBL_STDT_HN15_MB_1012',

38 'TBL_STDT_HN17_MB_1101',

39 'TBL_STDT_JL18_MB_1007',

40 'TBL_STDT_JL18_MB_1008',

41 'TBL_STDT_JL18_MB_1009',

42 'TBL_STDT_JL18_MB_1010',

43 'TBL_STDT_JL18_MB_1011',

44 'TBL_STDT_JL18_MB_1012',

45 'TBL_STDT_JL18_MB_1101',

46 'TBL_STDT_JL18_MB_1102',

47 'TBL_STDT_JX20_MB_1007',

48 'TBL_STDT_JX20_MB_1008',

49 'TBL_STDT_JX20_MB_1009',

50 'TBL_STDT_JX20_MB_1010',

51 'TBL_STDT_JX20_MB_1011',

52 'TBL_STDT_JX20_MB_1012',

53 'TBL_STDT_JX20_MB_ACCT',

54 'TBL_STDT_JX20_MB_CUST');

COUNT(*)

----------

29

准备好索引的迁移脚本:

SQL> select 'alter index '|| index_name || ' rebuild parallel 4 tablespace stdt_his_index;' from dba_indexes where table_name in('TBL_STDT_FJ08_MB_1006','TBL_STDT_FJ08_MB_1007','TBL_STDT_FJ08_MB_1008','TBL_STDT_GD01_MB_1007','TBL_STDT_GD01_MB_1008',

2 'TBL_STDT_GD01_MB_1009',

3 'TBL_STDT_GD01_MB_1010',

4 'TBL_STDT_GD01_MB_1011',

5 'TBL_STDT_GX03_MB_1007',

6 'TBL_STDT_GX03_MB_1008',

7 'TBL_STDT_GX03_MB_1009',

8 'TBL_STDT_GX03_MB_1010',

9 'TBL_STDT_GZ10_MB_1007',

10 'TBL_STDT_GZ10_MB_1008',

11 'TBL_STDT_GZ10_MB_1010',

12 'TBL_STDT_GZ10_MB_1011',

13 'TBL_STDT_GZ10_MB_1012',

14 'TBL_STDT_GZ10_MB_1013',

15 'TBL_STDT_GZ10_MB_1102',

16 'TBL_STDT_HB14_MB_1009',

17 'TBL_STDT_HB14_MB_1010',

18 'TBL_STDT_HB14_MB_1011',

19 'TBL_STDT_HB14_MB_1012',

20 'TBL_STDT_HB14_MB_1101',

21 'TBL_STDT_HB14_MB_1102',

22 'TBL_STDT_HB16_MB_1007',

23 'TBL_STDT_HB16_MB_1008',

24 'TBL_STDT_HB16_MB_1009',

25 'TBL_STDT_HB16_MB_1010',

26 'TBL_STDT_HLJ09_MB_1007',

27 'TBL_STDT_HLJ09_MB_1008',

28 'TBL_STDT_HLJ09_MB_1009',

29 'TBL_STDT_HLJ09_MB_1010',

30 'TBL_STDT_HLJ09_MB_1011',

31 'TBL_STDT_HLJ09_MB_1012',

32 'TBL_STDT_HN15_MB_1007',

33 'TBL_STDT_HN15_MB_1008',

34 'TBL_STDT_HN15_MB_1009',

35 'TBL_STDT_HN15_MB_1010',

36 'TBL_STDT_HN15_MB_1011',

37 'TBL_STDT_HN15_MB_1012',

38 'TBL_STDT_HN17_MB_1101',

39 'TBL_STDT_JL18_MB_1007',

40 'TBL_STDT_JL18_MB_1008',

41 'TBL_STDT_JL18_MB_1009',

42 'TBL_STDT_JL18_MB_1010',

43 'TBL_STDT_JL18_MB_1011',

44 'TBL_STDT_JL18_MB_1012',

45 'TBL_STDT_JL18_MB_1101',

46 'TBL_STDT_JL18_MB_1102',

47 'TBL_STDT_JX20_MB_1007',

48 'TBL_STDT_JX20_MB_1008',

49 'TBL_STDT_JX20_MB_1009',

50 'TBL_STDT_JX20_MB_1010',

51 'TBL_STDT_JX20_MB_1011',

52 'TBL_STDT_JX20_MB_1012',

53 'TBL_STDT_JX20_MB_ACCT',

54 'TBL_STDT_JX20_MB_CUST');

'ALTERINDEX'||INDEX_NAME||'REB

--------------------------------------------------------------------------------

alter index UI_STDT_HN15_MB_1008 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_FJ08_MB_1006 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_GX03_MB_1008 rebuild parallel 4 tablespace stdt_his_index;

alter index INDEX_HN17_MB_1101 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_GX03_MB_1007 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_GX03_MB_1009 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_GX03_MB_1010 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_JX20_MB_1007 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_HN15_MB_1007 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_JX20_MB_1011 rebuild parallel 4 tablespace stdt_his_index;

alter index I_GZ10_1010 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_JX20_MB_CUST rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_JX20_MB_ACCOUNT rebuild parallel 4 tablespace stdt_his_index

alter index UI_STDT_JX20_MB_1009 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_JX20_MB_1010 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_HN15_MB_1009 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_HN15_MB_1010 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_HB14_MB_1101 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_HB14_MB_1102 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_HB14_MB_1012 rebuild parallel 4 tablespace stdt_his_index;

'ALTERINDEX'||INDEX_NAME||'REB

--------------------------------------------------------------------------------

alter index INDEX_GZ1007 rebuild parallel 4 tablespace stdt_his_index;

alter index I_GZ10_1011 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_JX20_MB_1008 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_JX20_MB_1012 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_HB14_MB_1009 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_HB14_MB_1010 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_HN15_MB_1011 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_HN15_MB_1012 rebuild parallel 4 tablespace stdt_his_index;

alter index UI_STDT_HB14_MB_1011 rebuild parallel 4 tablespace stdt_his_index;

29 rows selected

--为了防止redo log对空间的冲击, 在进行大数据量的索引rebuild 时, 强烈建议使用

Nologging 的方式。

SQL> select sum(bytes)/1024/1024/1024,sum(blocks) from dba_segments where segment_name in

2 ('TBL_STDT_FJ08_MB_1006','TBL_STDT_FJ08_MB_1007','TBL_STDT_FJ08_MB_1008','TBL_STDT_GD01_MB_1007','TBL_STDT_GD01_MB_1008',

3 'TBL_STDT_GD01_MB_1009',

4 'TBL_STDT_GD01_MB_1010',

5 'TBL_STDT_GD01_MB_1011',

6 'TBL_STDT_GX03_MB_1007',

7 'TBL_STDT_GX03_MB_1008',

8 'TBL_STDT_GX03_MB_1009',

9 'TBL_STDT_GX03_MB_1010',

10 'TBL_STDT_GZ10_MB_1007',

11 'TBL_STDT_GZ10_MB_1008',

12 'TBL_STDT_GZ10_MB_1010',

13 'TBL_STDT_GZ10_MB_1011',

14 'TBL_STDT_GZ10_MB_1012',

15 'TBL_STDT_GZ10_MB_1013',

16 'TBL_STDT_GZ10_MB_1102',

17 'TBL_STDT_HB14_MB_1009',

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8117479/viewspace-1052521/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-08-18

  • 博文量
    26
  • 访问量
    97618