缩小数据文件尺寸报ORA-03297的处理办法（国外英文资料）.docVIP

缩小数据文件尺寸报ORA-03297的处理办法（国外英文资料） Reducing the size of the data file to the ore-03297 ORA 03297: the file contains data that is used outside the requested RESIZE value Recently, there was a shortage of disk space in the historical database, and there was a table space with 50 G capacity, but the actual space was only 100 megabytes, Most data files can be adjusted after the ALTER TABLE TABLE SHRINK SPACE CASCAD, when trying to adjust one of the data file sizes Rd-03297: file contains used data beyond requested RESIZE value Note that this file cannot release space by reducing the HWM. * * * * * * * * * * * * * Database version: oracle 10.2.1 -- find the file number for the data file SQL select file#, name from v $datafile where name like % baseinfo.dbf 5 / data/eucpdb/eucpdb/baseinfo.dbf Find the largest block number in the file SQL select Max (block_id) from dba_extents where file_id = 5; * * * * * * * * 1213833 -- view the database block size SQL show parameter db_block_size Db_block_size integer 8192 Calculate the location of the maximum use block in the file SQL select 1213833 * 8/1024 from dual; 9483.0703125 M To verify the accuracy of the above, do an experiment The pre-adjusted data file size is 10000M Now adjust the database file to 9500M SQL ALTER DATABASE DATAFILE / data/eucpdb/eucpdb/baseinfo.dbf RESIZE 9500M 2 / The database has changed Adjust the file to 9400m SQL ALTER DATABASE DATAFILE / data/eucpdb/eucpdb/baseinfo.dbf RESIZE 9400M 2 / SQL ALTER DATABASE DATAFILE / data/eucpdb/eucpdb/baseinfo.dbf RESIZE 9400M. ALTER DATABASE DATAFILE / data/eucpdb/eucpdb/baseinfo.dbf RESIZE 9400M * Line 1 error: ORA 03297: the file contains data that is used outside the requested RESIZE value A: it seems that the calculation above is accurate SQL col segment_name format a30 SQL SET LIN, 200 SQL select segment_name, segment_type, tablespace_name, extent_id, bytes, blocks from dba_extents where file_id = 5 and block_id = 1213833; SEGMENT_NAME SEGMENT_TYPE TABLESPA