如何Shrink Undo表空间,释放过度占用的空间建站知
导读网站优化seo培训:1建站知识如何Shrink Undo表空间,释放过度占用的空间seo网站排名优化软件营销型网站建设。
环境:OS:Red Hat Enterprise Linux AS release 4 (Nahant)DB:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production 一台Oracle10gR2数据库报出如下错误:ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in tablespace SYSAUX ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in tablespace SYSAUX ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in tablespace SYSAUX ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in tablespace SYSAUX ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in tablespace SYSAUX
登陆检查,发现是SYSAUX表空间空间用尽,不能扩展,尝试手工扩展SYSAUX表空间: alter database datafile '+ORADG/danaly/datafile/sysaux.266.600173881' resize 800mTue Nov 29 23:31:38 2005ORA-1237 signalled during: alter database datafile '+ORADG/danaly/datafile/sysaux.266.600173881' resize 800m...
出现ORA-1237错误,提示空间不足。这时候我才认识到是磁盘空间可能被用完了.是谁"偷偷的"用了那么多空间呢(本来有几十个G的Free磁盘空间的)?检查数据库表空间占用空间情况:SQL> select tablespace_name,sum(bytes)/1024/1024/1024 GB 2 from dba_data_files group by tablespace_name 3 union all 4 select tablespace_name,sum(bytes)/1024/1024/1024 GB 5 from dba_temp_files group by tablespace_name order by GB;TABLESPACE_NAME GB------------------------------ ----------USERS .004882813UNDOTBS2 .09765625SYSTEM .478515625SYSAUX .634765625WAPCM_TS_VISIT_DETAIL .9765625HY_DS_DEFAULT 1MINT_TS_DEFAULT 1MMS_TS_DATA2 1.375MMS_IDX_SJH 2MMS_TS_DEFAULT 2IVRCN_TS_DATA 2TABLESPACE_NAME GB------------------------------ ----------MMS_TS_DATA1 2CM_TS_DEFAULT 5TEMP 20.5498047UNDOTBS1 27.158203115 rows selected.不幸的发现,UNDO表空间已经扩展至27G,而TEMP表空间也扩展至20G,这2个表空间加起来占用了47G的磁盘空间,导致了空间不足。显然曾经有大事务占用了大量的UNDO表空间和Temp表空间,Oracle的AUM(Auto Undo Management)从出生以来就经常出现只扩展,不收缩(shrink)的情况(通常我们可以设置足够的UNDO表空间大小,然后取消其自动扩展属性).现在我们可以采用如下步骤回收UNDO空间:1.确认文件SQL> select file_name,bytes/1024/1024 from dba_data_files 2 where tablespace_name like 'UNDOTBS1';FILE_NAME--------------------------------------------------------------------------------BYTES/1024/1024---------------+ORADG/danaly/datafile/undotbs1.265.600173875 278102.检查UNDO Segment状态SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks 2 from v$rollstat order by rssize; USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS---------- ---------- --------------------- ---------------------- ---------- 0 0 .000358582 .000358582 0 2 0 .071517944 .071517944 0 3 0 .13722229 .13722229 0 9  网seo优化趋势; 0 .236984253 .236984253 0 10 0 .625144958 .625144958 0 5 1 1.22946167 1.22946167 0 8 0 1.27175903 1.27175903 0 4 1 1.27895355 1.27895355 0 7 0 1.56770325 1.56770325 0 1 0 2.02474976 2.02474976 0 6 0 2.9671936 2.9671936 011 rows selected.3.创建新的UNDO表空间SQL> create undo tablespace undotbs2 ;(经测试,在9i环境下后面还要加上datafile '/opt/..../undotbs2.dbf' size 1024M)Tablespace created.4.切换UNDO表空间为新的UNDO表空间SQL> alter system set undo_tablespace=undotbs2 scope=both;System altered.创建了新的UNDO表空间以后,如果不知道系统使用的是pfile还是spfile文件,应使用参数both,会同时修改spfile文件,避免出现冲突。
声明: 本文由我的SEOUC技术文章主页发布于:2023-05-22 ,文章如何Shrink Undo表空间,释放过度占用的空间建站知主要讲述空间,标签,如何Shrink Undo表空间,释放过度占用的网站建设源码以及服务器配置搭建相关技术文章。转载请保留链接: https://www.seouc.com/article/web_5003.html