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;


释放表存储空间