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

原创 2015年05月22日 20:28:58

 

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

SQL> set line 200

SQL> set pagesize 200

SQL> col name format A150

 

 

1,查看表空间使用情况

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>

 ----------------------------------------------------------------------------------------------------------------
<
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!>
原博客地址:    http://blog.csdn.net/mchdba/article/details/45921045
原作者:黄杉 (mchdba)
----------------------------------------------------------------------------------------------------------------

 

 

2resize收缩报错:

准备收缩到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;

 

NAME                                                         TYPE           VALUE

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

db_block_size                                           integer       8192

SQL>

 

5,根据这个值查看对于的数据文件所占据的磁盘空间大小

 SELECT 4193920*8192/1024/1024 FROM dual;

SQL> SELECT 4193920*8192/1024/1024/1024FROM dual;

 

4193920*8192/1024/1024/1024

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

                                 31.9970703

 

SQL>

计算出来大概是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_SHARE

ORCTSTU

 

 

SQL>

 

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 

logging 

datafile '/home/oradata/powerdes/orctstu02.dbf'

size 50m 

autoextend 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 failed

re-validation

ORA-01940: cannot drop a user that iscurrently connected

 

 

SQL>

 

解决办法:直接lsnrctl stop;然后重新执行drop user操作

                 

                 

 

 

 

                                                 

                                                 

参考文章地址:                                              http://mp.weixin.qq.com/s?__biz=MjM5ODEzNDA4OA==&mid=210054237&idx=1&sn=a8a10b9275233876cc218616c7075422&scene=1&key=c468684b929d2be2b0ffb1ce3cccad13f8704c151f3b15e9fea067c200d7b5bb313c06d9c65bf99921aa10386672b9ea&ascene=1&uin=MTAwMDg2MTU4Mw%3D%3D&devicetype=webwx&version=70000001&pass_ticket=%2BXWq8edLrEPcVZeIuoKStojLYf546G8W1JvUNaVQXBTp02zaoqaiSoTTOdqwc3IG


相关文章推荐

Oracle调整表空间大小——ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

Setpagesize 1000 colFILE_NAME for a40 col"used%" for 99.99 col"free%" for 99.99 SELECTa.FILE_ID,...

ORACLE收缩表空间

最近导一个空库到数据库后,发现占用的表空间非常大,执行表收缩(SHRINK SPACE CASCADE)后,发现实际占用的空间不到1%。 但是收缩表空间大小提示错误     ALTER DATAB...

Oracle 11g 新特性 -- 临时表空间收缩 说明

一. 临时表空间收缩 1.1 说明关于Oracle 的临时表空间,之前有整理过一篇Blog:Oracle Temp 临时表空间http://blog.csdn.net/tianlesoftware/a...

Oracle调整表空间大小——ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

将备份的一个大数据库还原到本机以后,删除里面的大表(比如文件表、日志表等),Oracle数据文件在有数据的情况下能自动扩展,却不能自动收缩,造成存储空间的浪费。 如果直接修改数据文件的大小,可能会遇...

oracle11g--ORA-01653: 表 ZWLSUSER1d3d.HCALC 无法通过 8192 (在表空间 ZWLS_DATA 中) 扩展

上个公司写的一个每周给指定用户返款的程序,今天经理打电话说不能执行了,刚开始一看日志提示“ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01000: 超出打开游标的最大数”,出现这个...

oracle 11g RAC 更改remote_listener参数后EM日志报错:ORA-12514, TNS:listener does not currently know of service

环境:oracle 11g + RAC + ASM + raw + 2node + redhat5.7 本人配置 服务端 基于vip的负载均衡,更改remote_listener参数后,EM登入后总是...

oracle11g dataguard主库备份恢复到测试环境单库报错:ORA-19502: write error on file

1,启动oracle实例报错:SQL> startup nomount ORA-32004: obsolete or deprecated parameter(s) specified for RDB...
  • mchdba
  • mchdba
  • 2015年12月03日 21:59
  • 3649

oracle 11g R2安装报错ORA-00604及ORA-06553的原因及解决方法

 10月31日PO主打算装oracle 11g R2,于是通过QQ旋风离线下载功能从oracle官网的链接下载了win32_11gR2_database_1of2.zip和win32_11gR2...

oracle 11g的audit导致system表空间快速增长的问题

11gr2版本,oracle把参数audit_trail 自动设置为DB级别,导致很多数据库的操作被记录在审计表sys.aud$中,导致sys.aud$所在的表空间快速增长。可以通过TRUNCATE清...
  • hijk139
  • hijk139
  • 2012年03月23日 16:20
  • 8963

Oracle 11g Undo 表空间切换

--1、查看实例当前所用 undo 表空间及 undo 相关参数 SQL> show parameter undo NAME                            ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ORACLE 11G收缩表空间报错 ORA-03297: file contains used data beyondrequested RESIZE value
举报原因:
原因补充:

(最多只允许输入30个字)