AWR 통계정보 수집을 위해 statistics_level을 ALL로 변경하였다가 이후 LEVEL을 낮췄음에도
SYSAUX tablespace에 저장된 정보들로인해 디스크 full 상태가 발생한 이슈가 있었다.
SYSAUX tablespace에 있는 WRI$_ 로 시작하는 테이블들에 AWR 수집 데이터 값이 남아있어 발생한 이슈로
해당 테이블을 $ORACLE_HOME/rdbms/admin 에 있는 스크립트를 수행하여 삭제 후 재생성할 수 있다.
1. AWR 통계정보 수집 기능을 비활성화를 위해서 statistics level을 Basic으로 변경한다.
SQL> show parameter statistics_level
NAME TYPE VALUE
———————————— ———– ——————————
statistics_level string TYPICAL
alter system set statistics_level=basic scope=spfile;
System altered.
2. AWR repository를 삭제하는 동안 트랜잭션이 일어나는 것을 방지하기 위하여 DB를 shutdown 후 restrict 모드로 재기동한다.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict
ORACLE instance started.
Total System Global Area 542871552 bytes
Fixed Size 2254904 bytes
Variable Size 218105800 bytes
Database Buffers 314572800 bytes
Redo Buffers 7938048 bytes
Database mounted.
Database opened.
SQL> show parameter statistics
NAME TYPE VALUE
———————————— ———– ——————————
optimizer_use_pending_statistics boolean FALSE
statistics_level string BASIC
timed_os_statistics integer 0
timed_statistics boolean FALSE
3. AWR 테이블 삭제 및 재생성을 위해 아래의 스크립트 차례로 수행한다.
SQL> @catnoawr
SQL> @catnomwn
SQL> @catnomtr
SQL> @catnoalr
SQL> @catnofus
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush shared_pool;
System altered.
11g의 경우 추가적으로 아래의 스크립트를 수행한다.
SQL> @dbmsslrt
SQL> @catawrtb
SQL> @execsvrm
4. 모두 삭제됐다면 아래의 쿼리를 통해 정상적으로 삭제되었는지 확인한다.
여전히 존재한다면 수동으로 삭제해준다.
SQL> select table_name from dba_tables where table_name like ‘WRM$_%’ or table_name like ‘WRH$_%’;
SQL> drop type AWR_OBJECT_INFO_TABLE_TYPE;
drop type AWR_OBJECT_INFO_TYPE;
drop table WRH$_PLAN_OPERATION_NAME;
drop table WRH$_PLAN_OPTION_NAME;
drop table WRH$_MV_PARAMETER;
drop table WRH$_MV_PARAMETER_BL;
drop table WRH$_DYN_REMASTER_STATS;
drop table WRH$_PERSISTENT_QMN_CACHE;
drop table WRH$_DISPATCHER;
drop table WRH$_SHARED_SERVER_SUMMARY;
drop table WRM$_WR_USAGE
drop table WRM$_SNAPSHOT_DETAILS
5. 모두 삭제되었다면 statistics level 기존 값으로 수정한 후 인스턴스를 재기동한다.
SQL> alter system set statistics_level=TYPICAL scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 542871552 bytes
Fixed Size 2254904 bytes
Variable Size 218105800 bytes
Database Buffers 314572800 bytes
Redo Buffers 7938048 bytes
Database mounted.
Database opened.
SQL> show parameter statistics_level
NAME TYPE VALUE
———————————— ———– ——————————
statistics_level string TYPICAL