当前位置:Gxlcms > mysql > Oracle11g收缩表空间报错ORA-03297:filecontainsuseddatabeyondr

Oracle11g收缩表空间报错ORA-03297:filecontainsuseddatabeyondr

时间:2021-07-01 10:21:17 帮助过:44人阅读

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;

人气教程排行