lxj831

3 积分	0 好友	0 主题

发消息

Oracle 如何缩小数据文件{实验}

1^#

发表于 2011-11-15 14:34:09 | 查看: 9496| 回复: 3

昨天在群里听到这样一个问题，某个表空间A曾经使用了很大的空间，现在A上面已经没有多少数据，而硬盘空间吃紧。能否缩小表空间A的大小，释放一些空间出来。
我当时的回答是，通过enable row movement，然后 shrink space。最后resize表空间A所在的数据文件，就可以了。最后也许还需要重建索引，因为row movement后，rowid将发生变化。
现在，我们就来实际测试一下。
1.创建测试表空间：

create tablespace test
datafile ‘/e:/oracle/test/test.dbf’ size 10m
autoextend on;

2.创建测试用户：

create user test1 idenitfied by test1
default tablespace test;

grant connect,resource to test1;

3.查看TEST表空间大小：

select b.name,sum(a.bytes/1024/1024) "MB"
from v$datafile a,v$tablespace b
where a.ts#=b.ts#
and b.name='TEST'
group by b.name;
NAME MB
------------------------------ ----------
TEST 10

4.用test1用户登录，并创建测试表：

conn test1/test1;

create table a
as
select * from all_objects;
insert into a
select * from a;
insert into a
select * from a;
insert into a
select * from a;
多次插入。

5.再次查询

select tablespace_name,sum(bytes)/1024/1024 “MB”
from dba_free_space
where tablespace_name=’TEST’
group by tablespace_name;
TABLESPACE_NAME MB
------------------------------ ----------
TEST 144

6.删除表a中的部分类容：

查看是否运行行移动：
select owner,table_name,row_movement from dba_tables
where table_name='A';

OWNER TABLE_NAME ROW_MOVEMENT
------------------------------ ------------------------------ ------------
TEST1 A DISABLED

查询表的总行数：
select count(*) from a;
  COUNT(*)
----------
1304864
查询表所占空间大小：
select owner,segment_name,segment_type,bytes/1024/1024 "MB"
from dba_segments
where segment_name='A';

OWNER SEGMENT_NAME    SEGMENT_TYPE       MB
--------    ------------             ------------ ----------
TEST1          A                      TABLE             144

删除99999行，并提交：
delete from a where rownum<100000;
已删除99999行。
commit;
删除99999行，并提交：
delete from a where rownum<100000;
已删除99999行。
commit;

删除99999行，并提交：
delete from a where rownum<900000;
已删除899999行。
commit;

再次查询表所占空间大小：
select owner,segment_name,segment_type,bytes/1024/1024 "MB"
from dba_segments
where segment_name='A';

OWNER SEGMENT_NAME    SEGMENT_TYPE       MB
--------    ------------             ------------ ----------
TEST1          A                      TABLE             144

删除99999行后，我们发现表A占用的空间大小还是144MB。

7.启用row movement，并shrink space：

conn / as sysdba
alter table test1.a enble row movement;
alter table test1.a shrink space;

8.再次查看表所占空间大小：

select owner,segment_name,segment_type,bytes/1024/1024 "MB"
from dba_segments
where segment_name='A';

OWNER SEGMENT_NAME    SEGMENT_TYPE       MB
--------    ------------             ------------ ----------
TEST1          A                      TABLE             22.75
通过允许行移动，并缩减空间后，重新查看得知A表现在占用表空间为22.75MB。

9.缩减数表空间：

由于表空间TEST只有一个数据文件，而且表空间TEST中只有一个对象A，现在A所占空间为22.75M，那么我们可以将test.dbf resize为30MB。这样，表空间TEST就只占用30MB的大小了。
alter database datafile 'e:/oracle/test/test.dbf' resize 30m;
database datafile 'E:/ORACLE/TEST/TEST.DBF'已变更。

10.再次查询表空间大小：

select b.name,sum(a.bytes/1024/1024) "MB"
from v$datafile a,v$tablespace b
where a.ts#=b.ts#
and b.name='TEST'
group by b.name;
NAME MB
------------------------------ ----------
TEST 30

至此，成功缩减了表空间大小。

———————————————————————————————————————————
———————————————————————————————————————————
那到底缩减表空间后，rowid是否会发生变化呢？我们继续测试。
1.查看表A的总行数：
SQL> select count(*) from a;
  COUNT(*)
----------
245545
2.查看object_id=52880的数据行的rowid：
SQL> select rowid,object_id from a where object_id=52880 order by 1;
ROWID             OBJECT_ID
------------------ ----------
AAAM7gAAGAAAAFOAAa    52880
AAAM7gAAGAAAAMXABA    52880
AAAM7gAAGAAAAaqAAn    52880
AAAM7gAAGAAAAo4AAL    52880
AAAM7gAAGAAABkvAAL    52880
3.删除object_id<52880的行，并提交。
SQL> delete from a where object_id<52880;
已删除245399行。
SQL> commit;
提交完成。
SQL> select count(*) from a;
  COUNT(*)
----------
   146
4.缩减表A占用空间。
SQL> alter table a shrink space;
表已更改。
5.再次查询object_id=52880的行的rowid
SQL> select rowid,object_id from a where object_id=52880 order by 1;
ROWID             OBJECT_ID
------------------ ----------
AAAM7gAAGAAAAAUAAC    52880
AAAM7gAAGAAAAAUAAv    52880
AAAM7gAAGAAAAAUAA8    52880
AAAM7gAAGAAAAAVAAY    52880
AAAM7gAAGAAAAAVAA0    52880

可以看到，shrink space后，rowid发生了变化。所以，当进行了row movement，并 shrink space后，需要重建索引。以便索引能指向正确的数据行。

最后，补充一下。delete并不会降低高水位线，虽然删除了数据内容，但并不会释放空间。只有启用row movement，并 shrink space后，才能降低高水位线。另外，即使在没有启用row movement的情况下，truncate也会降低高水位线。不过truncate会将表中所有数据全部清除。

分享0