RESIZE DATAFILE遇到ORA-03297的处理方法

ORA-03297: file contains blocks of data beyond requested RESIZE value

Or

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

The resize operation will fail at this point.

select value

from v$parameter

where name = 'db_block_size';

select 'alter database datafile ''' ||

file_name || ''' resize ' ||

ceil( (nvl(hwm,1)*8192)/1024/1024 )

|| 'm;' cmd

from dba_data_files a,

( select file_id,

max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

and a.TABLESPACE_NAME='TBS_ARK_DAT'

and ceil(blocks*8192/1024/1024)-

ceil((nvl(hwm,1)*

8192)/1024/1024 ) > 0

order by hwm

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22503721/viewspace-734874/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22503721/viewspace-734874/