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 A1501，查看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:ORACLEORASERVERORADATAORCTSTUEXPOWERDESORCTSTU01.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 valueSQL>参考命令：select file_id,max（block_id+blocks-1）HWM,block_idfrom dba_extentswhere file_id=5group 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 41939203469 5 4187263 41863688137 5 4186367 41862403919 5 4186239 41861123033 5 4186111 41859849526 5 4185983 41858569113 5 4185855 41848329669 5 4184775 41847681166 5 4184767 41847602304 5 4184743 41847367215 5 4184735 41847284933 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; NAME TYPE VALUE----------------------------------------------- ------------------------------db_block_size integer 8192SQL>5，根据这个值查看对于的数据文件所占据的磁盘空间大小
SELECT 4193920*8192/1024/1024 FROM dual;SQL> SELECT 4193920*8192/1024/1024/1024FROM dual;4193920*8192/1024/1024/1024--------------------------- 31.9970703SQL>计算出来大概是32G左右，再去磁盘看实际的数据文件大小[oracle@edustu4 ~]$ ll -h/home/oradata/powerdes/orctstu01.dbf-rw-r-----. 1 oracle oinstall 32G May 1618:06 /home/oradata/powerdes/orctstu01.dbf[oracle@edustu4 ~]$也是32G左右，和实际的磁盘的数据文件的大小一致，看看这个最大4193920的数据块所在的extent对应的segment信息是否是已经被drop到的table？select segment_name,owner from dba_extentswhere block_id=3507584; SQL> select segment_name,owner fromdba_extents where block_id=1942656; SEGMENT_NAME--------------------------------------------------------------------------------OWNER------------------------------RES_APPROVE_SHAREORCTSTUSQL>6，分析问题所在
这个表不是一件被drop的哪些表记录，表RES_APPROVE_SHARE正在被应用程序使用着，也就说明了报错，所以resize的时候报错ORA-03297: file contains used data beyond requested RESIZE value，block不能释放掉，因为正在被使用。这个时候通过正常的resize已经不能解决问题了，怎么办？可以采用expdp、impdp的方式重新生成新的表空间和数据文件，然后删除旧的表空间和数据文件。7，开始新建表空间
create tablespace ORCTSTU_2 loggingdatafile "/home/oradata/powerdes/orctstu02.dbf"size 50mautoextend on next 50m ;alter table RES_APPROVE_SHARE move ORCTSTU_2; ORA-14133: ALTER TABLE MOVE cannot becombined with other operations alter table orctstu.RES_APPROVE_SHARE movetablespace ORCTSTU_2; 8，使用expdp导出数据
先建立管道目录 CREATEOR REPLACE DIRECTORY dir_dump_t1 AS"/home/oracle/expdpimpdp"; 开始导出export[oracle@edustu4~]$ expdp orctstu/testpd2015@PD1directory=dir_dump_t1 schemas=orctstu dumpfile=TEST2_PD_20150518.dmp

Export:Release 11.2.0.1.0 - Production on Mon May 18 17:06:42 2015

Copyright（c） 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bitProduction
Withthe Partitioning, OLAP, Data Mining and Real Application Testing options
Starting"ORCTSTU"."SYS_EXPORT_SCHEMA_02": orctstu/********@PD1 directory=dir_dump_t1schemas=orctstu dumpfile=TEST2_PD_20150518.dmp
Estimatein progress using BLOCKS method...
Processingobject type SCHEMA_EXPORT/TABLE/TABLE_DATA
Totalestimation using BLOCKS method: 7.483 GB
Processingobject type SCHEMA_EXPORT/USER
Processingobject type SCHEMA_EXPORT/SYSTEM_GRANT
.....................................................
Processingobject type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
.. exported "ORCTSTU"."RES_APPROVE_CONTENT" 44.01 MB 350923 rows
.. exported "ORCTSTU"."RECEIPT_BILL" 569.3 MB 2064823 rows
.....................................................
.. exported "ORCTSTU"."ZS_PLAN_LEAVE_MESSAGE" 0 KB 0 rows
.. exported "ORCTSTU"."ZS_PLAN_MESSAGE" 0 KB 0 rows
Mastertable "ORCTSTU"."SYS_EXPORT_SCHEMA_02" successfullyloaded/unloaded
******************************************************************************
Dumpfile set for ORCTSTU.SYS_EXPORT_SCHEMA_02 is:
/home/oracle/expdpimpdp/TEST2_PD_20150518.dmp
Job"ORCTSTU"."SYS_EXPORT_SCHEMA_02" successfully completed at17:11:13

[oracle@edustu4~]$ 9，开始使用import导入数据
9.1 清理旧数据
删除用户 drop user orctstu cascade; 删除表空间 drop tablespace orctstu including contents anddatafiles; 然后重启oracle shutdown immediate startup 查看磁盘空间，已经释放出来了 [oracle@edustu4 expdpimpdp]$ df -h Filesystem Size Used Avail Use% Mounted on /dev/sda3 57G 21G 34G 38% / tmpfs 12G 2.1G 10G 18% /dev/shm /dev/sda1 194M 32M 153M 18% /boot /dev/mapper/vg001-lv001 63G 12G 49G 20% /home/oradata df:`/root/.gvfs": Permission denied [oracle@edustu4expdpimpdp]$ 9.2开始建立新用户
重新创建用户 createtablespace ORCTSTU logging datafile"/home/oradata/powerdes/orctstu01.dbf" size50m autoextendon next50m extentmanagement local; CREATEUSER orctstu PROFILE "DEFAULT" IDENTIFIED BY "testpd2015" DEFAULT TABLESPACE ORCTSTU ACCOUNTUNLOCK; GRANTconnect,resource TO orctstu; grantdba to orctstu; SQL>create tablespace ORCTSTU logging datafile"/home/oradata/powerdes/orctstu01.dbf" size50m autoextendon next50m extentmanagement local; 2 3 4 5 6 7 Tablespacecreated. SQL> SQL>CREATE USER orctstu PROFILE "DEFAULT" IDENTIFIED BY "testpd2015"DEFAULT TABLESPACE ORCTSTU ACCOUNT UNLOCK; Usercreated. SQL>GRANT connect,resource TO orctstu; Grantsucceeded. SQL>grant dba to orctstu; Grantsucceeded. SQL> 9.3 开始导入备份的数据
导入命令： impdporctstu/testpd2015@PD1 directory=dir_dump_t1 dumpfile=TEST2_PD_20150518.dmpnologfile=y 导入过程如下： ...... Processingobject type SCHEMA_EXPORT/EVENT/TRIGGER ORA-39082:Object type TRIGGER:"ORCTSTU"."LOGON_DENIED_TO_ALERT" createdwith compilation warnings ORA-39082:Object type TRIGGER:"ORCTSTU"."LOGON_DENIED_TO_ALERT"created with compilation warnings Processingobject type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX Processingobject typeSCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS Processingobject type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processingobject type SCHEMA_EXPORT/JOB Processingobject type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ Job"ORCTSTU"."SYS_IMPORT_FULL_01" completed with 45 error（s）at 20:36:08 [oracle@edustu4admin]$ PS:impdp导入的时候，是从最大的表开始导入的，先导入数据，最后重建索引，导入各种其它objects。10，报错记录
SQL> drop user orctstu cascade;drop user orctstu cascade*ERROR at line 1:ORA-04098: trigger "ORCTSTU.LOGON_DENIED_TO_ALERT"is invalid and failedre-validationORA-01940: cannot drop a user that iscurrently connectedSQL>解决办法：直接lsnrctl stop;然后重新执行drop user操作更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址