author：kamus
mail：kamus@itpub.net
date：2004-4
通过move tablespace来完成resize datafile。
hwm的概念就不在此阐述了。
测试环境为oracle10g for linux，其它版本的一样。
我们先创建两个表空间，分别为t_tbs和t_tbs1，分别有一个数据文件，大小都是5m
再创建一个test_user用户，给这个用户上述两个表空间的无限限额，并且设置默认表空间是t_tbs。
[zhangleyi@as zhangleyi]$ sqlplus / as sysdba
sql*plus: release 10.1.0.2.0 - production on tue apr 13 21:01:25 2004
copyright (c) 1982, 2004, oracle. all rights reserved.
connected to:
oracle database 10g enterprise edition release 10.1.0.2.0 - production
with the partitioning, olap and data mining options
sys at orcl10>alter user test_user default tablespace t_tbs;
user altered.
sys at orcl10>alter user test_user quota unlimited on t_tbs;
user altered.
sys at orcl10>alter user test_user quota unlimited on t_tbs1;
user altered
用test_user登录，创建表
test_user at orcl10>create table t_obj as select * from dba_objects where rownum<10000;
table created.
test_user at orcl10>insert into t_obj select * from t_obj;
9999 rows created.
test_user at orcl10>/
19998 rows created.
test_user at orcl10>/
insert into t_obj select * from t_obj
*
error at line 1:
ora-01653: unable to extend table test_user.t_obj by 128 in tablespace t_tbs
test_user at orcl10>commit;
commit complete.
test_user at orcl10>select sum(blocks) "total blocks",sum(bytes) "total size" from dba_extents where owner='test_user' and segment_name='t_obj';
total blocks total size
------------ ----------
512 4194304
好，上面我们创建了一个表，并且插入了很多数据，通过dba_extents视图我们可以看到总共用的block数和总共的大小。
下面我们用delete删除全部数据，并且插入新的9999条数据
test_user at orcl10>delete from t_obj;
39996 rows deleted.
test_user at orcl10>insert into t_obj select * from dba_objects where rownum<10000;
9999 rows created.
test_user at orcl10>commit;
commit complete.
test_user at orcl10>select sum(blocks) "total blocks",sum(bytes) "total size" from dba_extents
2 where owner='test_user' and segment_name='t_obj';
total blocks total size
------------ ----------
512 4194304
再次查看dba_extents视图，发现占用的空间并没有减少。
我们尝试resize这个数据文件，file#为6的是t_tbs表空间下面的数据文件
sys at orcl10>alter database datafile 6 resize 4m;
alter database datafile 6 resize 4m
*
error at line 1:
ora-03297: file contains used data beyond requested resize value
sys at orcl10>alter database datafile 6 resize 4500000;
database altered.
我们发现想resize到4m不可以，但是resize到4500000就可以了，因为上面查看出来的total size是4194304，这个值大于4m而小于4500000。
然后我们move这张表到t_tbs1表空间，这个表空间下面的数据文件file#是8
est_user at orcl10>alter table t_obj move tablespace t_tbs1;
table altered.
test_user at orcl10>select sum(blocks) "total blocks",sum(bytes) "total size" from dba_extents
2 where owner='test_user' and segment_name='t_obj';
total blocks total size
------------ ----------
128 1048576
我们检查dba_extents视图，发现total size已经变化了，此时已经可以说明move表是会重新进行block的整理的，同时也重置了hwm。
下面我们resize这个数据文件。
sys at orcl10>alter database datafile 8 resize 3m;
database altered.
sys at orcl10>host
[zhangleyi@as orcl10]$ cd /oracle/oradata/orcl10/datafile/
