|
环境: OS:Red Hat Enterprise Linux AS release (Nahant) DB:Oracle Database g Enterprise Edition Release Production 一台OraclegR数据库报出如下错误: ORA: unable to extend table SYSMANMGMT_SYSTEM_ERROR_LOG by in tablespace SYSAUX ORA: unable to extend table SYSMANMGMT_SYSTEM_ERROR_LOG by in tablespace SYSAUX ORA: unable to extend table SYSMANMGMT_SYSTEM_ERROR_LOG by in tablespace SYSAUX ORA: unable to extend table SYSMANMGMT_SYSTEM_ERROR_LOG by in tablespace SYSAUX ORA: unable to extend table SYSMANMGMT_SYSTEM_ERROR_LOG by in tablespace SYSAUX 登陆检查发现是SYSAUX表空间空间用尽不能扩展尝试手工扩展SYSAUX表空间: alter database datafile +ORADG/danaly/datafile/sysaux resize mTue Nov :: ORA signalled during: alter database datafile +ORADG/danaly/datafile/sysaux resize m 出现ORA错误提示空间不足这时候我才认识到是磁盘空间可能被用完了 是谁偷偷的用了那么多空间呢(本来有几十个G的Free磁盘空间的)? 检查数据库表空间占用空间情况: SQL> select tablespace_namesum(bytes)/// GB from dba_data_files group by tablespace_name union all select tablespace_namesum(bytes)/// GB from dba_temp_files group by tablespace_name order by GB;TABLESPACE_NAME GB USERS UNDOTBS SYSTEM SYSAUX WAPCM_TS_VISIT_DETAIL HY_DS_DEFAULT MINT_TS_DEFAULT MMS_TS_DATA MMS_IDX_SJH MMS_TS_DEFAULT IVRCN_TS_DATA TABLESPACE_NAME GB MMS_TS_DATA CM_TS_DEFAULT TEMP UNDOTBS rows selected 不幸的发现UNDO表空间已经扩展至G而TEMP表空间也扩展至G这个表空间加起来占用了G的磁盘空间导致了空间不足 显然曾经有大事务占用了大量的UNDO表空间和Temp表空间Oracle的AUM(Auto Undo Management)从出生以来就经常出现只扩展不收缩(shrink)的情况(通常我们可以设置足够的UNDO表空间大小然后取消其自动扩展属性) 现在我们可以采用如下步骤回收UNDO空间: 确认文件 SQL> select file_namebytes// from dba_data_files where tablespace_name like UNDOTBS;FILE_NAMEBYTES//+ORADG/danaly/datafile/undotbs 检查UNDO Segment状态 SQL> select usnxactsrssize///hwmsize///shrinks from v$rollstat order by rssize; USN XACTS RSSIZE/// HWMSIZE/// SHRINKS rows selected 创建新的UNDO表空间 SQL> create undo tablespace undotbs;Tablespace created 切换UNDO表空间为新的UNDO表空间 SQL> alter system set undo_tablespace=undotbs scope=both;System altered 此处使用spfile需要注意以前曾经记录过这样一个案例:Oracle诊断案例Spfile案例一则 等待原UNDO表空间所有UNDO SEGMENT OFFLINE SQL> select usnxactsstatusrssize///hwmsize///shrinks from v$rollstat order by rssize; USN XACTS STATUS RSSIZE/// HWMSIZE/// SHRINKS ONLINE ONLINE ONLINE ONLINE ONLINE ONLINE ONLINE ONLINE ONLINE ONLINE ONLINE USN XACTS STATUS RSSIZE/// HWMSIZE/// SHRINKS PENDING OFFLINE rows selected 再看: :: SQL> / USN XACTS STATUS RSSIZE/// HWMSIZE/// SHRINKS ONLINE ONLINE ONLINE ONLINE ONLINE ONLINE ONLINE ONLINE ONLINE ONLINE ONLINE rows selectedElapsed: :: 删除原UNDO表空间 :: SQL> drop tablespace undotbs including contents;Tablespace droppedElapsed: :: 检查空间情况 由于我使用的ASM管理可以使用gR提供的信工具asmcmd来察看空间占用情况 [oracle@danaly ~]$ export ORACLE_SID=+ASM[oracle@danaly ~]$ asmcmdASMCMD> du Used_MB Mirror_used_MB ASMCMD> exit 空间已经释放 |
