最近一直在纠结数据库服务器空间的问题, 前段时间此服务器由于空间的问题调整过备份策略, 但是效果不是很明显,由于此库是一个类似数据仓库的系统, 里面有很多静态(用于只读的)的历史数据,由于是以前的老项目,增加硬件存储是非常地难, 你懂的。。。 呵呵 于是想到了数据库的压缩分离的方法,此方案主要有以下步骤:
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',
18 'TBL_STDT_HB14_MB_1010',
19 'TBL_STDT_HB14_MB_1011',
20 'TBL_STDT_HB14_MB_1012',
21 'TBL_STDT_HB14_MB_1101',
22 'TBL_STDT_HB14_MB_1102',
23 'TBL_STDT_HB16_MB_1007',
24 'TBL_STDT_HB16_MB_1008',
25 'TBL_STDT_HB16_MB_1009',
26 'TBL_STDT_HB16_MB_1010',
27 'TBL_STDT_HLJ09_MB_1007',
28 'TBL_STDT_HLJ09_MB_1008',
29 'TBL_STDT_HLJ09_MB_1009',
30 'TBL_STDT_HLJ09_MB_1010',
31 'TBL_STDT_HLJ09_MB_1011',
32 'TBL_STDT_HLJ09_MB_1012',
33 'TBL_STDT_HN15_MB_1007',
34 'TBL_STDT_HN15_MB_1008',
35 'TBL_STDT_HN15_MB_1009',
36 'TBL_STDT_HN15_MB_1010',
37 'TBL_STDT_HN15_MB_1011',
38 'TBL_STDT_HN15_MB_1012',
39 'TBL_STDT_HN17_MB_1101',
40 'TBL_STDT_JL18_MB_1007',
41 'TBL_STDT_JL18_MB_1008',
42 'TBL_STDT_JL18_MB_1009',
43 'TBL_STDT_JL18_MB_1010',
44 'TBL_STDT_JL18_MB_1011',
45 'TBL_STDT_JL18_MB_1012',
46 'TBL_STDT_JL18_MB_1101',
47 'TBL_STDT_JL18_MB_1102',
48 'TBL_STDT_JX20_MB_1007',
49 'TBL_STDT_JX20_MB_1008',
50 'TBL_STDT_JX20_MB_1009',
51 'TBL_STDT_JX20_MB_1010',
52 'TBL_STDT_JX20_MB_1011',
53 'TBL_STDT_JX20_MB_1012',
54 'TBL_STDT_JX20_MB_ACCT',
55 'TBL_STDT_JX20_MB_CUST');
SUM(BYTES)/1024/1024/1024 SUM(BLOCKS)
------------------------- -----------
92.4228515625 12114048
压缩后:
SQL> select sum(bytes)/1024/1024/1024,sum(blocks) from dba_segments where segment_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');
SUM(BYTES)/1024/1024/1024 SUM(BLOCKS)
------------------------- -----------
33.677734375 4414208
SQL> select count(*) from dba_indexes where owner='P_STDT' and status='UNUSABLE';
COUNT(*)
----------
0
整完后,一定记得把索引的并行度 恢复回去,否则在索引使用时会消耗大量资源。
SQL> select index_name,degree from user_indexes where degree =4;
INDEX_NAME DEGREE
------------------------------ ----------------------------------------
UI_STDT_HN15_MB_1012 4
UI_STDT_HN15_MB_1011 4
UI_STDT_HB14_MB_1010 4
UI_STDT_HB14_MB_1009 4
UI_STDT_GX03_MB_1007 4
INDEX_HN17_MB_1101 4
UI_STDT_JX20_MB_1012 4
UI_STDT_HB14_MB_1012 4
UI_STDT_HB14_MB_1011 4
UI_STDT_HN15_MB_1008 4
…………
SQL> select 'alter index '|| index_name || ' noparallel;' from user_indexes where degree =4;
'ALTERINDEX'||INDEX_NAME||'NOP
------------------------------------------------------
alter index UI_STDT_HN15_MB_1012 noparallel;
alter index UI_STDT_HN15_MB_1011 noparallel;
alter index UI_STDT_HB14_MB_1010 noparallel;
alter index UI_STDT_HB14_MB_1009 noparallel;
alter index UI_STDT_GX03_MB_1007 noparallel;
alter index INDEX_HN17_MB_1101 noparallel;
alter index UI_STDT_JX20_MB_1012 noparallel;
alter index UI_STDT_HB14_MB_1012 noparallel;
alter index UI_STDT_HB14_MB_1011 noparallel;
alter index UI_STDT_HN15_MB_1008 noparallel;
………
SQL> alter index UI_STDT_HN15_MB_1012 noparallel;
Index altered
SQL> alter index UI_STDT_HN15_MB_1011 noparallel;
Index altered
SQL> alter index UI_STDT_HB14_MB_1010 noparallel;
………
当然也可以使用exp/imp,expdp/impdp 完成以上工作。
整个静态数据分离压缩完毕后, 其空间的大小为:
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 50274864 383.566772460938
STDT_HIS 8688392 66.2871704101563
STDT_INDEX 10181032 77.6751098632813
STDT_HIS_INDEX 875160 6.67694091796875
此次的压缩 在不影响应用的情况下, 将数据库数据空间 缩减了 630G- 534G = 100G, 算上每个星期的两次备份, 为服务器节约了 300G 左右的空间, 此次compress没有涉及到partition表, clob字段 后期将对partition 表的压缩做一个实验。
完成以上操作后,对数据库做一个全备 然后通过dba_extens 的查询
SQL> select tablespace_name, file_id, sum(bytes)/1024/1024/1024 as GB from dba_extents where wner='P_STDT' group by tablespace_name, file_id order by GB ;
TABLESPACE_NAME FILE_ID GB
------------------------------ ---------- ----------
STDT_HIS_INDEX 34 6.67694091
STDT_INDEX 27 8.11401367
STDT 23 11.0874633
STDT 22 11.2519531
STDT 25 12.3560180
STDT 26 12.4826660
STDT 24 12.5075073
通过 resize datafile 的大小,把压缩空间释放出来, 整个过程到此结束。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8117479/viewspace-702309/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8117479/viewspace-702309/