oracle如何清理sysaux表空间,清理SYSAUX表空间

清理核心SYSAUX表空间

一、检查SYSAUX表空间使用情况；如下：

1.当前空间总大小大约180G左右，日增大约350M左右；

TABLESPACE_NAME SIZE_USED_MB DATA_SIZE_MB FREE_RATE MAX_SIZE_MB

FREE_MB MAX_FREE_RATE AVG_USED_PER_DAY_MB TBS_EXHAUST_DAYS

------------------------------ ------------ ------------ ---------- -----------

---------- ------------- ------------------- ----------------

SYSAUX 186005 217087 14 217087

31082 14 343.3 91

2.检查SYSAUX中个部分大小，发现SM/AWR这一部分占了95%以上的空间；如下：

set lines 9999 pages 999

col Item for a30

col schema for a20

col Move_Procedure for a30

SELECT occupant_name "Item",

space_usage_kbytes / 1048576 "Space Used (GB)",

schema_name "Schema",

move_procedure "Move Procedure"

FROM v$sysaux_occupants

ORDER BY 1;

Item Space Used (GB) Schema Move

Procedure

------------------------------ --------------- --------------------

------------------------------------

AO .035461426 SYS

DBMS_AW.MOVE_AWMETA

AUDIT_TABLES 0 SYS

DBMS_AUDIT_MGMT.move_dbaudit_tables

AUTO_TASK .000305176 SYS

EM .044921875 SYSMAN

emd_maintenance.move_em_tblspc

EM_MONITORING_USER .001525879 DBSNMP

EXPRESSION_FILTER .003540039 EXFSYS

JOB_SCHEDULER .001037598 SYS

LOGMNR .011962891 SYSTEM

SYS.DBMS_LOGMNR_D.SET_TABLESPACE

LOGSTDBY .001342773 SYSTEM

SYS.DBMS_LOGSTDBY.SET_TABLESPACE

ORDIM .000427246 ORDSYS

ordsys.ord_admin.move_ordim_tblspc

ORDIM/ORDDATA .013183594 ORDDATA

ordsys.ord_admin.move_ordim_tblspc

ORDIM/ORDPLUGINS 0 ORDPLUGINS

ordsys.ord_admin.move_ordim_tblspc

ORDIM/SI_INFORMTN_SCHEMA 0 SI_INFORMTN_SCHEMA

ordsys.ord_admin.move_ordim_tblspc

PL/SCOPE .001525879 SYS

SDO .07220459 MDSYS

MDSYS.MOVE_SDO

SM/ADVISOR .449890137 SYS

SM/AWR 179.906677 SYS

SM/OPTSTAT .797363281 SYS

SM/OTHER .011291504 SYS

SMON_SCN_TIME .014282227 SYS

SQL_MANAGEMENT_BASE .002868652 SYS

STATSPACK 0 PERFSTAT

STREAMS .000976563 SYS

TEXT .003540039 CTXSYS

DRI_MOVE_CTXSYS