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缩小表空间或数据文件。
可通过上述方法解决。
转载请保留固定链接： https://linuxeye.com/database/660.html |