先查看所有表空间的数据使用情况
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;
—查表空间对应数据文件大小
select t1.name, t2.name,Round(t2.BYTES / ( 1024 * 1024 ), 2) "大小(M)"
from v$tablespace t1, v$datafile t2
where t1.ts# = t2.ts#
order by 1,3
**
开始处理
**
—-清理回收站
purge recyclebin;
Purge recyclebin;--清空当前用户的回收站
Purge dba_recyclebin;--清空所有用户的回收站
—-从truncate sys.aud$想到的删除大表的方法
—查看数据库审计情况(NONE为没有开启审计)
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;
释放表存储空间