oracle缩小表空间文件,Oracle 如何缩小数据文件{实验} - Oracle数据库管理 - Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusData诗檀...

昨天在群里听到这样一个问题，某个表空间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                                  104.用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                                    1446.删除表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会将表中所有数据全部清除。