最近历史数据库磁盘空间不足，而有一个表空间有50个G容量，但是实际只占100m的空间，使用ALTER TABLE table SHRINK SPACE CASCAD后大部分数据文件可以调整，当试图调整其中一个数据文件尺寸的时候报ORA-03297: file contains used data beyond requested RESIZE value
说明这个文件不能通过降低hwm来释放空间了。
1
找到数据文件对应的文件号
SQL>select file#,name from v$datafile where namelike '%BASEINFO.dbf';
file# name
------ -------
5 /data/eucpdb/eucpdb/BASEINFO.dbf
2
找到文件中最大的块号
SQL>select max(block_id) from dba_extents wherefile_id=5;
max(block_id)
--------
1213833
3
查看数据库块大小
SQL>show parameter db_block_size;
db_block_size integer 8192
4
计算最大使用块占用的位置
SQL>select 1213833*8/1024 from dual;
9483.0703125 M
为了验证上面做法的准确性，下面做一个试验:
调整前数据文件大小为10000M
现在调整数据库文件为9500M
SQL>ALTER DATABASE DATAFILE'/data/eucpdb/eucpdb/BASEINFO.dbf' RESIZE 9500M;
数据库已更改
调整文件为 9400m
SQL>ALTER DATABASE DATAFILE'/data/eucpdb/eucpdb/BASEINFO.dbf' RESIZE 9400M;
*
第 1 行出现错误:
ORA-03297:文件包含在请求的 RESIZE 值以外使用的数据
看来上面的计算是准确的
SQL> col segment_name format a30
SQL> set linesize 200
SQL> selectsegment_name,segment_type,tablespace_name,extent_id,bytes,blocks from dba_extents where file_id=5 and block_id='1213833';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID BYTES BLOCKS
---------- ------ ------ ------ ------ ------
REGISTRYINFO TABLE BASEINFO 25 524288 64
SQL> ALTERTABLE eucpmanager.REGISTRYINFO SHRINK SPACE CASCADE;
表已更改。
5
创建新表空间
把block_id比较高的几个表移出表空间
SQL> CREATE SMALLFILE TABLESPACE"BASEINFO_BAK" DATAFILE '/data/eucpdb/eucpdb/baseinfo_bak.dbf' SIZE 100MLOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
表空间已创建。
SQL> alter user eucpmanager quota unlimited onBASEINFO_BAK;
用户已更改。
把block_id比较高的几个表移动到新的表空间
SELECT DISTINCT SEGMENT_NAME FROM DBA_EXTENTS WHERETABLESPACE_NAME='BASEINFO' AND FILE_ID=5 and block_id>1159985 andsegment_type='TABLE';
SQL> SELECT distinct 'alter table '|| SEGMENT_NAME||' movetablespace baseinfo_bak; ' FROM DBA_EXTENTS WHERE TABLESPACE_NAME='BASEINFO'AND FILE_ID=5 and block_id>1159985 and segment_type='TABLE' ;
'ALTERTABLE'||SEGMENT_NAME||'MOVETABLESPACEBASEINFO_BAK;'
-----------------------
alter table ENTERPRISESERVICEINFO move tablespacebaseinfo_bak;
alter table REGISTRYFEEDETAILEX move tablespacebaseinfo_bak;
alter table ENTERPRISEROUTE move tablespacebaseinfo_bak;
alter table REGISTRYAUTHINFO move tablespacebaseinfo_bak;
altertable ENTERPRISEBASEINFO move tablespace baseinfo_bak;
SQL> alter table ENTERPRISESERVICEINFO movetablespace baseinfo_bak;
alter table REGISTRYFEEDETAILEX move tablespacebaseinfo_bak;
alter table ENTERPRISEROUTE move tablespacebaseinfo_bak;
alter table REGISTRYAUTHINFO move tablespacebaseinfo_bak;
alter table ENTERPRISEBASEINFO move tablespacebaseinfo_bak;
告警日志中会出现下面的内容，索引需要重建
Thu SEP 24 14:20:21 2017
Someindexes or index [sub]partitions of table EUCPMANAGER.ENTERPRISEBASEINFO havebeen marked unusable
6
重建索引
用下面的执行结果的语句执行所有重建
SQL> SELECTdistinct 'alter INDEX '||SEGMENT_NAME||' REBUILD TABLESPACE BASEINFO_BAK; ' FROM DBA_EXTENTS WHERE TABLESPACE_NAME='BASEINFO'AND FILE_ID=5 and block_id>1159985 and segment_type='INDEX' ;
alter INDEX PK_ENTERPRISEBASEINFO REBUILD TABLESPACEBASEINFO_BAK;
alter INDEX PK_REGISTRYINFO REBUILD TABLESPACEBASEINFO_BAK;
alter INDEX PK_REGISTRYFEEDETAILEX REBUILD TABLESPACEBASEINFO_BAK;
alter INDEX UNI_REGISTRYAUTHINFO REBUILD TABLESPACEBASEINFO_BAK;
alter INDEX UNI_REGISTRYAUTHINFO REBUILD TABLESPACEBASEINFO_BAK;
alter INDEX UNI_REGISTRYAUTHINFO REBUILD TABLESPACEBASEINFO_BAK;
alter INDEX UNI_REGISTRYAUTHINFO REBUILD TABLESPACEBASEINFO_BAK;
alterINDEX UNI_REGISTRYAUTHINFO REBUILD TABLESPACE BASEINFO_BAK;
7
再次修改数据文件大小
SQL> selectmax(block_id) from dba_extents where file_id=5 ;
MAX(BLOCK_ID)
-------------
3209
SQL> ALTER DATABASE DATAFILE'/data/eucpdb/eucpdb/BASEINFO.dbf' RESIZE 1000m;
数据库已更改。
数据库文件的空间已经调整成功了。
8
把挪走的表挪回来
SQL> alter table ENTERPRISESERVICEINFO movetablespace baseinfo;
alter table REGISTRYFEEDETAILEX move tablespacebaseinfo;
alter table ENTERPRISEROUTE move tablespace baseinfo;
alter table REGISTRYAUTHINFO move tablespace baseinfo;
alter table ENTERPRISEBASEINFO move tablespacebaseinfo;
alter table registryinfo move tablespace baseinfo;
9
重建索引
SQL> ALTER INDEX PK_REGISTRYINFO REBUILD TABLESPACE BASEINFO;
ALTER INDEX AK_REGISTRYCODE_REGISTRYINFO REBUILDTABLESPACE BASEINFO;
ALTER INDEX PK_ENTERPRISEBASEINFO REBUILD TABLESPACEBASEINFO;
ALTER INDEX PK_ENTERPRISESERVICEINFO REBUILD TABLESPACEBASEINFO;
ALTER INDEX UNI_REGISTRYAUTHINFO REBUILD TABLESPACEBASEINFO;
ALTERINDEX PK_REGISTRYFEEDETAILEX REBUILD TABLESPACE BASEINFO;
索引已更改。
SQL> SELECT COUNT(*) FROM DBA_SEGMENTS WHERETABLESPACE_NAME='BASEINFO_BAK' ;
COUNT(*)
----------
0
已经没有对象在新建的这个表空间了。现在删除掉
SQL> drop tablespace baseinfo_bak;
表空间已删除.
到此调整已经结束了。
其实调整方法有很多，如用imp/exp等。
从官方角度，对resize缩小文件的场景。Oracle建议先将所有对象转移到一个临时位置上，之后缩小resize文件。最后再将对象转移回原表空间。
