Oracle 11g收缩表空间报错 ORA-03297: file contains used data beyondr

Oracle 11g收缩表空间报错 ORA-03297: file contains used data beyondrequested RESIZE value

测试环境磁盘空间不足，所以drop一些无用的大表，但是发现空间没有变化，df -h还是没有释放出磁盘空间来。

SQL> set line 200
SQL> set pagesize 200
SQL> col name format A150

1，查看Oracle 11g表空间使用情况

SQL> SELECTUPPER(F.TABLESPACE_NAME) "表空间名", 
  2        　　D.TOT_GROOTTE_MB "表空间大小(M)", 
  3        　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", 
  4        　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比", 
  5        　　F.TOTAL_BYTES "空闲空间(M)", 
  6        　　F.MAX_BYTES "最大块(M)" 
  7        　　FROM (SELECT TABLESPACE_NAME, 
  8        　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, 
  9        　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES 
 10        　　FROM SYS.DBA_FREE_SPACE 
 11        　　GROUP BY TABLESPACE_NAME) F, 
 12        　　(SELECT DD.TABLESPACE_NAME, 
 13        　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB 
 14        　　FROM SYS.DBA_DATA_FILES DD 
 15        　　GROUP BY DD.TABLESPACE_NAME) D 
 16        　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME 
 17        　　ORDER BY 1; 
 
表空间名                            表空间大小(M)    已使用空间(M) 使用比      空闲空间(M)    最大块(M) 
------------------------------------------- ------------- -------- ----------- ---------- 
HELP                                    500          5.19    1.04%    494.81    494.81 
ORCTSTU                          32406.63      15545.69  47.97%    16860.94        72 
SYSAUX                                  900        689.94  76.66%      210.06    204.94 
SYSTEM                                  1110      1005.31  90.57%      104.69      95.44 
UAAP                                    500        143.37  28.67%      356.63    290.38 
UNDOTBS1                                6485        331.25    5.11%    6153.75      3534 
USERS                                461.25        394.44  85.52%      66.81      22.19 
10 rows selected 
 
SQL> 

看到ORCTSTU表空间只使用了49%，使用了15G空间，而ORCTSTU表空间占据的总磁盘空间为32G，所以我们可以收缩ORCTSTU到16G左右，这样就释放出了将近16G的空间了。

去查看下此表空间所在的数据文件，如下所示：

SQL> SELECT file_id,file_name FROM DBA_DATA_FILES D WHERED.TABLESPACE_NAME = 'ORCTSTU'; 
  FILE_ID FILE_NAME 
------------------------------------------------------------------------------------------ 
        5D:\ORACLE\ORASERVER\ORADATA\ORCTSTUEX\POWERDES\ORCTSTU01.DBF 
 
SQL> 

2，resize收缩报错：

准备收缩到18G，执行如下报错

SQL> alter database datafile'/home/oradata/powerdes/orctstu01.dbf' resize 1800M;

alter database datafile'/home/oradata/powerdes/orctstu01.dbf' resize 1800M

*

ERROR at line 1:

ORA-03297: file contains used data beyondrequested RESIZE value

SQL>

参考命令：

select file_id,max(block_id+blocks-1)HWM,block_id

from dba_extents

where file_id=5

group by file_id,block_id;

3，去分析情况这个数据文件

可以看到基本没有任何改变，但是根据我的直观感觉，确实没有多少表了，空间也确实都腾出来了。可以简单的验证一下,数据文件是5号，使用dba_extents可以看到占用的空间情况和对应的块的情况。

select file_id,max(block_id+blocks-1)HWM,block_id

            from dba_extents

            where file_id=5

            group by file_id,block_id;

                               

6519      5              4194047                4193920

3469      5              4187263                4186368

8137      5              4186367                4186240

3919      5              4186239                4186112

3033      5              4186111                4185984

9526      5              4185983                4185856

9113      5              4185855                4184832

9669      5              4184775                4184768

1166      5              4184767                4184760

2304      5              4184743                4184736

7215      5              4184735                4184728

4933      5              4184727                4184720

......

 

通过对比HWM和block_id的值，看到有很多都是空间占用差别比较大的。

4，，查看以下数据文件的最大的block_id值
我们来在这个基础上做一个简单的分析。首先得到5号数据文件中，块号最大的数据块block_id。

 

SQL> SELECT MAX(block_id) FROMdba_extents WHERE tablespace_name = 'ORCTSTU';

 

MAX(BLOCK_ID)

-------------

    4193920

 

SQL>   

值为：  4193920

再查看下一个block的容量大小

SQL> show parameter db_block_size;