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

今天给表空间resize 时候报错


SQL> alter database datafile '/u01/app/oracle/oradata/test/tlog.dbf' resize 500m;
alter database datafile '/u01/app/oracle/oradata/test/tlog.dbf' resize 500m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value




查看表空间使用情况


ts-name                        ts-bytes(m) ts-used (m ts-free(m) ts-per
------------------------------ ----------- ---------- ---------- ---------------


USERS                             53246.25   50824.37    2421.88   95.45
SYSAUX                                 730     687.31      42.69   94.15
SYSTEM                                 830     765.69      64.31   92.25
UNDOTBS1                               305      12.56     292.44    4.12
TPCC                                  1024          1       1023    0.10
TLOG                              17338.69          1   17337.69    0.01




很明显  TLOG 表空间没有被使用


select * from dba_segments where tablespace_name = 'TLOG';


SYSTEM BIN$RmTd0pLqVJvgU5WvhwpB2w==$0 TABLE ASSM TLOG 10 2113666 192937984 23552 94 16384 8192 1 2147483645 2147483645 10 DEFAULT DEFAULT DEFAULT




经查看，发现回收站内存在内容




SQL> PURGE RECYCLEBIN;


Recyclebin purged.


手动清理回收站


SQL>  alter database datafile '/u01/app/oracle/oradata/test/tlog.dbf' resize 500m;


Database altered.




问题解决




今天再次遇到报错 上述方法无法解决


使用命令进行查看


select file_id,max(block_id+blocks-1)HWM,block_id from dba_extents where file_id=4 group by file_id,block_id;


SELECT MAX(block_id) FROM dba_extents WHERE tablespace_name = 'USERS';
3391616


select segment_name,owner from dba_extents where block_id=3391616;


DSYSB13C_H SYSTEM




将对象删除或者进行move 操作


SQL> drop table system.DSYSB13C_H;


Table dropped.




尝试resize 依然报错


select segment_name,owner,segment_type from dba_extents where block_id=2299120;


REPCAT$_GROUPED_COLUMN_PK SYSTEM INDEX


将index进行删除操作


SQL> drop index system.REPCAT$_GROUPED_COLUMN_PK;
drop index system.REPCAT$_GROUPED_COLUMN_PK
                  *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key


定位index 信息


select * from dba_indexes i where i.index_name = 'REPCAT$_GROUPED_COLUMN_PK' 


SYSTEM REPCAT$_GROUPED_COLUMN_PK NORMAL SYSTEM REPCAT$_GROUPED_COLUMN TABLE UNIQUE DISABLED USERS 2 255 65536 1048576 1 2147483645 10 YES 0 0 0 0 0 0 VALID 0 0 2017/1/5 17:01:05 1 1 NO N N N DEFAULT DEFAULT DEFAULT NO NO NO NO NO VISIBLE YES




将表删除


drop table system.REPCAT$_GROUPED_COLUMN;


SQL> drop table system.REPCAT$_GROUPED_COLUMN;


Table dropped.




SQL> PURGE DBA_RECYCLEBIN;


DBA Recyclebin purged.


SQL> alter database datafile '/u01/app/oracle/oradata/test/users01.dbf' resize 500m;


Database altered.


成功