linux 修改表空间大小,resize缩小表空间或数据文件大小的问题

SQL> create tablespace data01 datafile '/u01/data01.dbf' size 22m;

SQL> create table a1 tablespace data01 as select * from dba_objects;  (a1表6M)

SQL> create table a2 tablespace data01 as select * from dba_objects;

SQL> create table a3 tablespace data01 as select * from dba_objects;

SQL> drop table a1 purge; 永久删除一个表(回收站也没有)

SQL> alter database datafile '/u01/data01.dbf' resize 16m;

alter database datafile '/u01/data01.dbf' resize 16m

*

ERROR at line 1:

ORA-03297: file contains used data beyond requested RESIZE value

上述报错空间不足？

SQL> select owner,substr(segment_name,1,20),segment_type,max(block_id) from dba_extents where tablespace_name='DATA01' group by owner,substr(segment_name,1,20),segment_type order by max(block_id);

OWNER                          SUBSTR(SEGMENT_NAME,1,20)

------------------------------ ----------------------------------------

SEGMENT_TYPE       MAX(BLOCK_ID)

------------------ -------------

SYS                            A2

TABLE                       1417

SYS                            A3

TABLE                       2185

SQL> select 1417*8192/1024/1024 from dual;

1417*8192/1024/1024

-------------------

11.0703125

SQL> select 2185*8192/1024/1024 from dual;

2185*8192/1024/1024

-------------------

17.0703125

由此可见删除的表位于最低位，最高为还保留着。

SQL> alter table a3 move tablespace users;

SQL> alter database datafile '/u01/data01.dbf' resize 16m;

SQL> alter table a3 move tablespace data01;  逻辑id不变，物理id一定变

SQL> select owner,substr(segment_name,1,20),segment_type,max(block_id) from dba_extents where tablespace_name='DATA01' group by owner,substr(segment_name,1,20),segment_type order by max(block_id);

OWNER                          SUBSTR(SEGMENT_NAME,1,20)

------------------------------ ----------------------------------------

SEGMENT_TYPE       MAX(BLOCK_ID)

------------------ -------------

SYS                            A3

TABLE                        649

SYS                            A2

TABLE                       1417

结论：当对象占用表空间中高位block_id号时，可能导致不能resize缩小表空间或数据文件。

可通过上述方法解决。