使用movetable收缩表空间

来源：互联网发布：波形数据产生器编辑：程序博客网时间：2024/04/30 02:43

目的：收回目前没有使用过的表空间，减小数据文件大小。

难点：插入的历史数据可能分布在不同的磁盘位置上，所以无法确定resize datafile能不能成功执行。

解决方式：通过move一个tablespace上的所有对象到一个新的临时表空间上来降低表的hwm，从而减少数据占用的实际磁盘大小。

select tablespace_name,all_bytes,use_bytes, round(100*use_bytes/all_bytes,2),
free_bytes, round(100*free_bytes/all_bytes,2)
from(
select /*+ rule use_hash(a,b,c) */ a.tablespace_name,
c.bytes/1024/1024 all_bytes,
a.bytes/1024/1024 use_bytes,
b.bytes/1024/1024 free_bytes
from
(select tablespace_name,sum(bytes) bytes from dba_extents group by tablespace_name) a,
( select b.tablespace_name,sum(b.bytes) bytes from dba_free_space b
group by b.tablespace_name
) b,
(select tablespace_name,sum(bytes) bytes
from dba_data_files
group by tablespace_name) c
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = c.tablespace_name
)

表空间

总大小

对象占用空间

占用比率

free大小

free比率

CTL

500

5.875

1.18

494.0625

98.81

CWMLITE

17.6875

88.44

2.25

11.25

DP23

2.5

7.4375

74.38

DRSYS

9.625

48.13

10.3125

51.56

DWD

4000

337.1875

8.43

3662.75

91.57

DWI

6144

212.1875

3.45

5931.625

96.54

EXAMPLE

139.375

138.9375

99.69

0.375

0.27

FBI

200

120.875

60.44

79.0625

39.53

500

93.125

18.63

406.8125

81.36

ODM

9.3125

46.56

10.625

53.13

ODSD

22528

1737.8125

7.71

20789.5

92.28

ODSI

12288

835.9375

6.8

11451.69

93.19

RPTD

1024

100.5

9.81

923.4375

90.18

RPTI

1024

222

21.68

801.9375

78.31

SYSTEM

1040

1033.6875

99.39

6.25

0.6

TCLKING

4.875

97.5

0.0625

1.25

TODSD

10240

1212.0625

11.84

9027.625

88.16

TODSI

10240

401.1875

3.92

9838.5

96.08

UNDOTBS1

27055

731.578125

2.7

26323.19

97.3

USERS

82.5

82.375

99.85

0.0625

0.08

XDB

46.875

44.25

94.4

2.5625

5.47

测试步骤

1，建立测试用表空间

SQL> create tablespace HWM

2 datafile '/oradata/HWM01.dbf' size 5000M;

2， move table对索引的影响

首先，赋权给将需要move的用户DW。

SQL> alter user DW quota unlimited on HWM;

检查对象当前大小

select de.owner,de.segment_name,de.segment_type,de.tablespace_name,sum(de.blocks),sum(de.bytes)
from dba_extents de
where de.tablespace_name like 'DW_' and SEGMENT_name like '%CREDIT%'
group by de.owner,de.segment_name,de.segment_type,de.tablespace_name

用户

对象

类型

表空间

blocks

bytes

F_CREDIT

TABLE

DWD

4736

38797312

F_CREDIT_U1

INDEX

DWI

6400

52428800

2.1 移动普通表对象到新建表空间HWM

SQL> alter table F_CREDIT move tablespace HWM;

Move一个表到另外一个表空间时，索引不会跟着一块move，而且会失效。（LOB类型例外）

在创建失效的索引之前，使用到索引的查询语句将会报错。失效的索引需要使用rebuild来重现创建。

Alter index index_name rebuild;

Alter index pk_name rebuild;

如果我们需要move索引到另外一个表空间，则需要使用rebuild

Alter index index_name rebuild tablespace tbs_name;

Alter index pk_name rebuild tablespace tbs_name;

2.2 move分区表及索引

和普通表一样，索引也会失效，区别的仅仅是语法而已。

分区表move基本语法

如果是单级分区，则使用关键字partition，如果是多级分区，则使用subpartition替代partition。如果分区或分区索引比较大，可以使用并行move或rebuild，parallel（degree 2）。

select 'alter table '||owner||'.'||segment_name||' move partition ' ||partition_name||' tablespace HWM; ',bytes
from dba_segments where segment_name = 'F_DISTRIBUTION'and segment_type = 'TABLE PARTITION'

alter table DW.F_DISTRIBUTION move partition F_DISTRIBUTION_P00 tablespace HWM;
alter table DW.F_DISTRIBUTION move partition F_DISTRIBUTION_P01 tablespace HWM;
alter table DW.F_DISTRIBUTION move partition F_DISTRIBUTION_P02 tablespace HWM;
alter table DW.F_DISTRIBUTION move partition F_DISTRIBUTION_P03 tablespace HWM;
alter table DW.F_DISTRIBUTION move partition F_DISTRIBUTION_P04 tablespace HWM;
alter table DW.F_DISTRIBUTION move partition F_DISTRIBUTION_P05 tablespace HWM;
alter table DW.F_DISTRIBUTION move partition F_DISTRIBUTION_P06 tablespace HWM;
alter table DW.F_DISTRIBUTION move partition F_DISTRIBUTION_P07 tablespace HWM;
alter table DW.F_DISTRIBUTION move partition F_DISTRIBUTION_P08 tablespace HWM;
alter table DW.F_DISTRIBUTION move partition F_DISTRIBUTION_P09 tablespace HWM;
alter table DW.F_DISTRIBUTION move partition F_DISTRIBUTION_P10 tablespace HWM;
alter table DW.F_DISTRIBUTION move partition F_DISTRIBUTION_P11 tablespace HWM;
alter table DW.F_DISTRIBUTION move partition F_DISTRIBUTION_P12 tablespace HWM;
alter table DW.F_DISTRIBUTION move partition F_DISTRIBUTION_P13 tablespace HWM;
alter table DW.F_DISTRIBUTION move partition F_DISTRIBUTION_PMAX tablespace HWM;

重建全局索引

Alter index global_index rebuild;

或

Alter index global_index rebuild tablespace tbs_name;

重建局部索引

Alter table tab_name modify partition partition_name rebuild unusable local indexes;

或

Alter index local_index_name rebuild partition partition_name tablespace tbs_name;

提示：
USER_PART_TABLES
USER_IND_PARTITIONS
USER_IND_SUBPARTITIONS
USER_LOB_PARTITIONS
USER_LOB_SUBPARTITIONS
USER_PART_INDEXES
USER_PART_LOBS
可查询分区相关内容，同时，分区对象，也是segment，所以也可在dba_segments里查的到。

三：move LONG,LOB类型
据说DBMS_REDEFINITION包可以提供一些方便，没用过。
IONG类型
long类型不能通过MOVE来传输
特别提示，尽量不要用LONG类型，特难管理。
参考：http://www.anysql.net/2005/12/long_vs_lob.html
1，LONG不能使用insert into ... select ...等带select的模式。
如
create table t123 (id int,en long);
则
insert into t123(id,en) select * from t123;
报告错误，可以用pl/sql来帮助解决，如：
declare
cursor cur_t123 is select * from t123;
use_t123 cur_t123%rowtype;
begin

open cur_t123;
loop
fetch cur_t123 into use_t123;
exit when cur_t123%notfound;

insert into t123(id,en) values (use_t123.id,use_t123.en);

end loop;
close cur_t123;

end;
/

对有LONG类型字段的表的转移，可以使用：
create新表的方法。
* create一个新的表，存储在需要转移的表空间。
* 创建新的索引（使用tablespace 子句指定新的表空间）。
* 把数据转移过来
方法一：用COPY的方法：
copy from bigboar/bigboar@bigboar_sid insert t123(id,en) using select id,en from t123;
方法二：PL/SQL（如上）
方法三：直接就把LONG转换成CLOB类型
create table t321(id int,en clob) tablespace users;
insert into t321(id,en) select id,to_lob(en) from t123;
方法四：exp/imp
exp bigboar/bigboar file=a.dat tables=t123
imp bigboar/bigboar file=a.dat full=y IGNORE =y
* drop掉旧表。
* rename 新表为旧表表名。

IOB类型
在建立含有lob字段的表时，oracle会自动为lob字段建立两个单独的segment,一个用来存放数据（segment_type=LOBSEGMENT），另一个用来存

放索引（segment_type=LOBINDEX）。默认它们会存储在和表一起的表空间。
我们对表MOVE时，LOG类型字段和该字段的索引不会跟着MOVE，必须要单独来进行MOVE，语法如下如：
alter table t321 move tablespace users;
alter table t321 move lob(en) store as (tablespace users);

3，对一个表空间下的所有数据执行move table

CTL表空间的文件