sysaux表空间处理流程

1.查看节点1表空间情况

set line 200;

set pagesize 20000;

set feedback off;

col tablespace_name for a20;

col c_free_percent for a12;

col c_used_percent for a12;

col m_free_percent for a12;

col m_USED_PERCENT for a12;

select d.tablespace_name,round(d.MB_current_Bytes,2) Curr_Size_MB,round(f.f ree_mb_bytes,2) Free_Szie_MB,round(d.MB_maxbytes,2) MAX_Size_MB,round((f.free_mb _bytes/d.MB_current_Bytes)*100,2) c_free_percent,round((d.MB_current_Bytes-f.fr ee_mb_bytes)/d.MB_current_Bytes,4)*100 || '%' c_used_percent,round(((d.MB_maxbyt es-d.MB_current_Bytes+f.free_mb_bytes)/d.MB_maxbytes)*100,2) m_free_percent,ro und((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_maxbytes,4)*100 || '%' m_used_perc ent

2 from (select tablespace_name,sum(bytes/1024/1024) MB_current_Bytes,sum(max bytes/1024/1024) MB_maxbytes from dba_data_files group by tablespace_name ) d,(s elect tablespace_name,sum(bytes/1024/1024) free_mb_bytes from dba_free_space gro up by tablespace_name) f

3 where d.tablespace_name=f.tablespace_name

4 order by c_free_percent ;

TABLESPACE_NAME CURR_SIZE_MB FREE_SZIE_MB MAX_SIZE_MB C_FREE_PERCENT C_USED_PERCE M_FREE_PERCENT M_USED_PERCE

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

SYSTEM 5470 28.75 32767.98 ########## 99.47% ########## 16.61%

SYSAUX 32767.98 984.88 32767.98 ########## 96.99% ########## 96.99%

UPRR_SP 13300 680.56 32767.98 ########## 94.88% ########## 38.51%

FITECH 43007.98 4595.88 98303.95 ########## 89.31% ########## 39.07%

UNDOTBS1 3640 3587.25 32767.98 ########## 1.45% ########## .16%

UNDOTBS2 3975 3938.38 32767.98 ########## .92% ########## .11%

USERS 2856.25 2854.94 32767.98 ########## .05% ########## 0%

SQL>

sysaux表空间使用率96.99%,需要进行清理否则会影响数据库正常运行。

2.查看V$SYSAUX_OCCUPANTS视图情况

SET LINES 120 pagesize 199;

COL OCCUPANT_NAME FORMAT A30;

SELECT * FROM (SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES/1024/1024 GB FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC) WHERE ROWNUM<=5;

--AUDSYS 审计数据较多

SQL> SELECT * FROM (SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES/1024/1024 GB FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC) WHERE ROWNUM<=5;

OCCUPANT_NAME GB

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

AUDSYS 22.7640381

SM/ADVISOR 6.1661377

SM/AWR 1.06622314

SM/OPTSTAT .482543945

XDB .061401367

2.1清理审计数据

begin

dbms_audit_mgmt.clean_audit_trail(

audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

use_last_arch_timestamp => FALSE);

end;

--清理完成

SQL> SELECT * FROM (SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES/1024/1024 GB FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC) WHERE ROWNUM<=5;

OCCUPANT_NAME GB

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

SM/ADVISOR 6.1661377

SM/AWR 1.06622314

SM/OPTSTAT .482543945

XDB .061401367

SM/OTHER .051574707

SQL>

2.2SM/ADVISOR优化任务需要清理

--清理优化任务

SQL> DECLARE

v_tname VARCHAR2(32767);

BEGIN

v_tname := 'AUTO_STATS_ADVISOR_TASK';

DBMS_STATS.DROP_ADVISOR_TASK(v_tname);

END;

alter table WRI$_ADV_OBJECTS move;

alter index WRI$_ADV_OBJECTS_PK rebuild;

alter index WRI$_ADV_OBJECTS_IDX_01 rebuild;

alter index WRI$_ADV_OBJECTS_IDX_02 rebuild;

SQL> DECLARE

2 v_tname VARCHAR2(32767);

3 BEGIN

4 v_tname := 'AUTO_STATS_ADVISOR_TASK';

5 DBMS_STATS.DROP_ADVISOR_TASK(v_tname);

6 END;

7 /

SQL> alter table WRI$_ADV_OBJECTS move;

SQL> alter index WRI$_ADV_OBJECTS_PK rebuild;

SQL> alter index WRI$_ADV_OBJECTS_IDX_01 rebuild;

SQL> alter index WRI$_ADV_OBJECTS_IDX_02 rebuild;

SQL> SELECT * FROM (SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES/1024/1024 GB FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC) WHERE ROWNUM<=5;

OCCUPANT_NAME GB

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

SM/AWR 1.06634521

SM/OPTSTAT .482971191

SM/ADVISOR .103149414

XDB .061401367

SM/OTHER .051574707

SQL>

3.清理后sysaux表空间情况

SQL> set line 200;

SQL> set pagesize 20000;

SQL> set feedback off;

SQL> col tablespace_name for a20;

SQL> col c_free_percent for a12;

SQL> col c_used_percent for a12;

SQL> col m_free_percent for a12;

SQL> col m_USED_PERCENT for a12;

SQL> select d.tablespace_name,round(d.MB_current_Bytes,2) Curr_Size_MB,round(f.free_mb_bytes,2) Free_Szie_MB,round(d.MB_maxbytes,2) MAX_Size_MB,round((f.free_mb_bytes/d.MB_current_Bytes)*100,2) c_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_current_Bytes,4)*100 || '%' c_used_percent,round(((d.MB_maxbytes-d.MB_current_Bytes+f.free_mb_bytes)/d.MB_maxbytes)*100,2) m_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_maxbytes,4)*100 || '%' m_used_percent

2 from (select tablespace_name,sum(bytes/1024/1024) MB_current_Bytes,sum(maxbytes/1024/1024) MB_maxbytes from dba_data_files group by tablespace_name ) d,(select tablespace_name,sum(bytes/1024/1024) free_mb_bytes from dba_free_space group by tablespace_name) f

3 where d.tablespace_name=f.tablespace_name

4 order by c_free_percent ;

TABLESPACE_NAME CURR_SIZE_MB FREE_SZIE_MB MAX_SIZE_MB C_FREE_PERCENT C_USED_PERCE M_FREE_PERCENT M_USED_PERCE

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

SYSTEM 5470 28.75 32767.98 ########## 99.47% ########## 16.61%

UPRR_SP 13300 679.56 32767.98 ########## 94.89% ########## 38.51%

FITECH 43007.98 4595.88 98303.95 ########## 89.31% ########## 39.07%

UNDOTBS1 3640 579.19 32767.98 ########## 84.09% ########## 9.34%

SYSAUX 32767.98 30502.25 32767.98 ########## 6.91% ########## 6.91%

UNDOTBS2 3975 3937.31 32767.98 ########## .95% ########## .12%

USERS 2856.25 2854.94 32767.98 ########## .05% ########## 0%

清理后的sysaux表空间使用率已由之前的99.69%变成6.91%。

4.查看二节点sysaux表空间情况

set line 200;

set pagesize 20000;

set feedback off;

col tablespace_name for a20;

col c_free_percent for a12;

col c_used_percent for a12;

col m_free_percent for a12;

col m_USED_PERCENT for a12;

select d.tablespace_name,round(d.MB_current_Bytes,2) Curr_Size_MB,round(f.free_mb_bytes,2) Free_Szie_MB,round(d.MB_maxbytes,2) MAX_Size_MB,round((f.free_mb_bytes/d.MB_current_Bytes)*100,2) c_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_current_Bytes,4)*100 || '%' c_used_percent,round(((d.MB_maxbytes-d.MB_current_Bytes+f.free_mb_bytes)/d.MB_maxbytes)*100,2) m_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_maxbytes,4)*100 || '%' m_used_percent

from (select tablespace_name,sum(bytes/1024/1024) MB_current_Bytes,sum(maxbytes/1024/1024) MB_maxbytes from dba_data_files group by tablespace_name ) d,(select tablespace_name,sum(bytes/1024/1024) free_mb_bytes from dba_free_space group by tablespace_name) f

where d.tablespace_name=f.tablespace_name

order by c_free_percent ;

TABLESPACE_NAME CURR_SIZE_MB FREE_SZIE_MB MAX_SIZE_MB C_FREE_PERCENT C_USED_PERCE M_FREE_PERCENT M_USED_PERCE

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

SYSTEM 5470 28.75 32767.98 ########## 99.47% ########## 16.61%

UPRR_SP 13300 679.56 32767.98 ########## 94.89% ########## 38.51%

FITECH 43007.98 4595.88 98303.95 ########## 89.31% ########## 39.07%

UNDOTBS1 3640 1093.19 32767.98 ########## 69.97% ########## 7.77%

SYSAUX 32767.98 24294.44 32767.98 ########## 25.86% ########## 25.86%

UNDOTBS2 3975 3937.38 32767.98 ########## .95% ########## .11%

USERS 2856.25 2854.94 32767.98 ########## .05% ########## 0%

SQL>