Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

3

积分

0

好友

0

主题
1#
发表于 2011-11-15 14:34:09 | 查看: 9494| 回复: 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会将表中所有数据全部清除。
2#
发表于 2011-11-15 14:34:59
昨天群里面讨论到的,做了下测试。
写的不是很通顺,还望大家多多指点。

回复 只看该作者 道具 举报

3#
发表于 2011-11-15 15:41:33

回复 2# 的帖子

Very Good!

回复 只看该作者 道具 举报

4#
发表于 2011-11-16 09:43:14
支持实验
:victory: :victory: :victory: :victory:

回复 只看该作者 道具 举报

您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-23 19:34 , Processed in 0.055662 second(s), 22 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569