过大,oracle表空间过大时候的处理"/>
oracle 表空间过大,oracle表空间过大时候的处理
SELECT Upper(F.TABLESPACE_NAME) "表空间名", D.TOT_GROOTTE_MB "表空间大小(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99') || '%' "使用比", F.TOTAL_BYTES "空闲空间(M)", F.MAX_BYTES "最大块(M)" FROM (SELECT TABLESPACE_NAME, Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES, Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 1 --查询使用空间比较大的前15个表 select a.* from( select owner,segment_name,SEGMENT_TYPE, round(bytes/1024/1024/1024,2) all_size from dba_segments where SEGMENT_TYPE='TABLE' order by all_size desc ) a where rownum <15; ----清理回收站 purge recyclebin; Purge recyclebin;--清空当前用户的回收站 Purge dba_recyclebin;--清空所有用户的回收站 ----从truncate sys.aud$想到的删除大表的方法 昨天,在11g数据库上发现了sys.aud$表增大到8G左右,清除方法如下: 第一步 EXEC DBMS_AUDIT_MGMT.INIT_CLEANUP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 12); 第二步 EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, FALSE); 不执行第一步会报ORA-46258错误。 但问题是,在第一次执行第一步的时候,系统会把AUD$表从SYSTEM 表空间move到SYSAUX表空间。 联机文档里的说明如下: INIT_CLEANUP Procedure This procedure sets up the audit management infrastructure and a default cleanup interval for the audit trail records. If the audit trail tables are in the SYSTEM tablespace, then the procedure moves them to the SYSAUX tablespace. 如果SYSAUX的空间不够,又没有设置autoextend on,就会报错ORA-4626。 ORA-46267: Insufficient space in 'SYSAUX' tablespace, cannot complete operation 于是又找了metalink notes How to Truncate, Delete, or Purge Rows from the Audit Trail Table AUD$ [ID 73408.1],可以对此表进行truncate。 但由于是正式库,怕truncate操作会带来其他的一些问题,不直接做truncate,而是执行以下命令: truncate table sys.aud$ reuse storage; alter table sys.aud$ deallocate unused keep 8640m; alter table sys.aud$ deallocate unused keep 7000m; alter table sys.aud$ deallocate unused keep 6000m; ... alter table sys.aud$ deallocate unused keep 10m; truncate过程1-2分钟之内结束,最后sys.aud$变成10M。 如果想删除大表也可以这么操作(delete操作耗资源),只是不一定那么快释放空间,这还要看具体情况了。 本文出自 “我的BO之旅-Basis和Oracle” 博客,请务必保留此出处 ---查看数据库审计情况 SQL> show parameter audit_trail NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_trail string DB ----最后直接处理 (1) truncate table aud$ ; 释放SYSTEM表空间的大小 SQL> alter table xx_xlog enable row movement; Table altered SQL> alter table xx_xlog shrink space compact; Table altered SQL> alter table xx_xlog shrink space; Table altered ---释放表所占用空间的大小 truncate table JXNCPC.XX_XLOG reuse storage; truncate table JXPC141224.XX_XLOG reuse storage; truncate table TEST001.XX_XLOG reuse storage; truncate table NCJXPC2013.XX_XLOG reuse storage; truncate table NCJXPC2014.XX_XLOG reuse storage; truncate table NCJXPCTEMP.XX_XLOG reuse storage; truncate table NCJXPCERP.XX_XLOG reuse storage; alter table JXNCPC.XX_XLOG enable row movement; alter table JXPC141224.XX_XLOG enable row movement; alter table TEST001.XX_XLOG enable row movement; alter table NCJXPC2013.XX_XLOG enable row movement; alter table NCJXPC2014.XX_XLOG enable row movement; alter table NCJXPCTEMP.XX_XLOG enable row movement; alter table NCJXPCERP.XX_XLOG enable row movement; alter table JXNCPC.XX_XLOG shrink space compact; alter table JXPC141224.XX_XLOG shrink space compact; alter table TEST001.XX_XLOG shrink space compact; alter table NCJXPC2013.XX_XLOG shrink space compact; alter table NCJXPC2014.XX_XLOG shrink space compact; alter table NCJXPCTEMP.XX_XLOG shrink space compact; alter table NCJXPCERP.XX_XLOG shrink space compact; alter table JXNCPC.XX_XLOG shrink space; alter table JXPC141224.XX_XLOG shrink space; alter table TEST001.XX_XLOG shrink space; alter table NCJXPC2013.XX_XLOG shrink space; alter table NCJXPC2014.XX_XLOG shrink space; alter table NCJXPCTEMP.XX_XLOG shrink space; alter table NCJXPCERP.XX_XLOG shrink space; ---- truncate table JXNCPC.SM_OPERATELOG; truncate table JXPC141224.SM_OPERATELOG; truncate table TEST001.SM_OPERATELOG; truncate table NCJXPCTEMP.SM_OPERATELOG; truncate table NCJXPCERP.SM_OPERATELOG; truncate table NCJXPC2013.SM_OPERATELOG; truncate table NCJXPC2014.SM_OPERATELOG; 释放表存储空间
更多推荐
oracle 表空间过大,oracle表空间过大时候的处理
发布评论