{"id":410,"date":"2015-08-24T05:10:46","date_gmt":"2015-08-24T05:10:46","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=410"},"modified":"2015-08-24T05:10:46","modified_gmt":"2015-08-24T05:10:46","slug":"sysaux-tablespace-%ec%a4%84%ec%9d%b4%eb%8a%94-%eb%b0%a9%eb%b2%95","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=410","title":{"rendered":"SYSAUX tablespace \uc904\uc774\ub294 \ubc29\ubc95"},"content":{"rendered":"

sysaux tablespace\ub294 10g\uc5d0\uc11c \uc0c8\ub85c \ucd94\uac00\ub41c system default tablespace\uc758 \ud558\ub098\ub85c \uae30\uc874\uc5d0 system tablespace\uc5d0<\/p>\n

 <\/p>\n

\uc800\uc7a5\ub418\ub358 \uac01\uc885 ORACLE OPTION\ub4e4\uc758 schema\uac00 \uc800\uc7a5\ub418\uba70 10g\uc758 new feature\uc778 AWR(auto workload repository) \ub370\uc774\ud130\ub4e4\uc774 \uc800\uc7a5\ub418\ub294 tablespace \uc785\ub2c8\ub2e4.<\/span><\/p>\n

 <\/p>\n

awr\uc815\ubcf4\ub294 default\ub85c 1\uc2dc\uac04\uc5d0 \ud55c\ubc88\uc529 data\ub97c gathering\ud558\uace0 \uadf8 \uc815\ubcf4\ub97c 7\uc77c\ub3d9\uc548 \uc800\uc7a5\ud558\uac8c \ub418\uc5b4 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

7\uc77c\uc774 \uc9c0\ub098\uba74 \uac00\uc7a5 \uc624\ub798\ub41c awr\uc815\ubcf4\ub97c \uc790\ub3d9\uc73c\ub85c \uc0ad\uc81c\ud558\uac8c\ub054 \ub418\uc5b4 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

\uc704\uc758 \ub0b4\uc6a9\ub300\ub85c \ub77c\uba74 \ud2b9\ubcc4\ud55c ORACLE OPTION\ub4e4\uc744 \uc0ac\uc6a9\ud558\uc9c0 \uc54a\ub294 \ud55c awr\uc815\ubcf4\ub97c \uc800\uc7a5\ud558\ub294 sysaux tablespace\uc758 \ud06c\uae30\ub294<\/p>\n

 <\/p>\n

\uc77c\uc815\uae30\uac04 \ub298\uc5b4\ub098\ub2e4\uac00(retention\uae30\uac04) \uadf8 \uc77c\uc815\uae30\uac04\uc774 \uc9c0\ub098\uba74 \ud06c\uae30\uc5d0 \ubcc0\ud654\uac00 \uac70\uc758 \uc5c6\uc5b4\uc57c \uc815\uc0c1\uc77c \uac83\uc774\ub77c \uc0dd\uac01\ub429\ub2c8\ub2e4\ub9cc…<\/p>\n

 <\/p>\n

sysaux tablespace\uac00 \uc9c0\uc18d\uc801\uc73c\ub85c \uc810\uc810 \ucee4\uc9c0\ub294 \ud604\uc0c1\uc774 \ubc1c\uc0dd \ud588\uc2b5\ub2c8\ub2e4.<\/span><\/p>\n

 <\/p>\n

\ucc98\uc74c \uc124\uce58\uc2dc 1G\ub85c \ud06c\uae30\ub97c \uc124\uc815\ud588\ub294\ub370 \uc2dc\uac04\uc774 \uc9c0\ub0a0 \uc218\ub85d \uc810\uc810 \ub298\uc5b4\ub098\ub354\ub2c8 \uc9c0\uae08\uc740 4G\uac00 \ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

\uadf8\ub798\uc11c \uc774\ub798\uc800\ub798 \ucc3e\uc544\ubcf4\ub2c8 awr\uc815\ubcf4\ub294 7\uc77c\uac04 \uc800\uc7a5\ub418\uc9c0\ub9cc \uc8fc\uae30\uc801\uc73c\ub85c \uc2e4\ud589\ud558\ub294 table analyze \uc815\ubcf4\ub294<\/p>\n

 <\/p>\n

default\ub85c 31\uc77c \ub3d9\uc548 \uc800\uc7a5\uc774 \ub41c\ub2e4\uace0 \ud569\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

31\uc77c \ub3d9\uc548 data\uac00 \uc800\uc7a5\ub418\uace0 \uc0ad\uc81c\ub418\ub294 \uacfc\uc815\uc5d0\uc11c index\uc758 \ud06c\uae30\uac00 \uc9c0\uc18d\uc801\uc73c\ub85c \ub298\uc5b4\ub098\ub294\uac83\uc774<\/p>\n

 <\/p>\n

sysaux\uc758 \ud06c\uae30\uac00 \uc99d\uac00\ud558\ub294 \uc6d0\uc778\uc774 \ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

<\/p>\n

\ud574\uacb0\ucc45<\/p>\n

 <\/p>\n

## SYSAUX tablespace\ub97c \uc5b4\ub5a4 owner\uac00 \uc5bc\ub9c8\ub9cc\ud07c\uc758 \ud06c\uae30\ub97c \ucc28\uc9c0\ud558\uace0 \uc788\ub294\uc9c0 \ub2e4\uc74c\uc758 query\ub85c \ud655\uc778 \ud569\ub2c8\ub2e4.<\/strong><\/p>\n

<\/strong> <\/p>\n

SQL> select occupant_name, space_usage_kbytes\/1024 “MB”
   from v$sysaux_occupants
   order by space_usage_kbytes<\/p>\n

 <\/p>\n

OCCUPANT_NAME                          MB                                                             
—————————— ———-                                                             
XDB                                            0                                                             
XSAMD                                       0                                                             
ODM                                           0                                                             
STATSPACK                                0                                                             
ORDIM                                        0                                                             
ORDIM\/SQLMM                           0                                                             
TEXT                                          0                                                             
ULTRASEARCH_DEMO_USER        0                                                             
EM_MONITORING_USER               0                                                             
EXPRESSION_FILTER                   0                                                             
ULTRASEARCH                            0                                                             
EM                                             0                                                             
ORDIM\/PLUGINS                          0                                                             
SDO                                            0                                                             
TSM                                           .25                                                             
JOB_SCHEDULER                         .375                                                             
STREAMS                                    .5                                                             
AO                                              .75                                                             
XSOQHIST                                   .75                                                             
LOGSTDBY                                  .875                                                             
SM\/OTHER                                4.9375                                                             
LOGMNR                                   5.875                                                             
WM                                           6.1875                                                             
SM\/ADVISOR                          110.9375                                                             
SM\/AWR                                 501.875                                                             
SM\/OPTSTAT                        3198.25  => SM\/OPTSTAT \uac00 \uac00\uc7a5 \ub9ce\uc740 \uacf5\uac04 \ucc28\uc9c0(\ud1b5\uacc4\uc815\ubcf4 \uad00\ub828 schema)<\/span><\/font><\/p>\n

 <\/p>\n

## SYSAUX tablespace\uc5d0 \uc5b4\ub5a4 segment\uac00 \uac00\uc7a5 \ub9ce\uc740 \uacf5\uac04\uc744 \ucc28\uc9c0\ud558\ub294\uc9c0 \uc870\uc0ac<\/strong><\/p>\n

 <\/p>\n

select owner, segment_name, segment_type, bytes\/1024\/1024<\/p>\n

from dba_segments
where tablespace_name = ‘SYSAUX’
order by bytes<\/p>\n

….
….<\/p>\n

SYS    I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST    INDEX                          152<\/p>\n

SYS    SYS_LOB0000788324C00004$$    LOBSEGMENT                     200<\/p>\n

SYS    WRI$_OPTSTAT_HISTHEAD_HISTORY    TABLE                          219<\/p>\n

SYS    I_WRI$_OPTSTAT_H_ST                INDEX                          653<\/p>\n

SYS<\/span>    <\/span>WRI$_<\/span>OPTSTAT_HISTGRM_HISTORY    <\/span>TABLE                          840<\/span><\/font><\/p>\n

SYS    <\/span>I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST    <\/span>INDEX                         1219<\/span><\/font><\/p>\n

 <\/p>\n

\uc870\ud68c \uacb0\uacfc optstat\uad00\ub828\ub41c table\uacfc index\uac00 \ub9ce\uc740 \uacf5\uac04\uc744 \ucc28\uc9c0 \ud558\uace0 \uc788\uc74c.<\/p>\n

 <\/p>\n

## \ud1b5\uacc4\uc815\ubcf4\ub97c \ubcf4\uad00\ud558\ub294 \uc8fc\uae30\ub97c \ud655\uc778<\/strong><\/p>\n

 <\/p>\n

SQL> select dbms_stats.get_stats_history_retention from dual;<\/p>\n

 <\/p>\n

GET_STATS_HISTORY_RETENTION
—————————
                         31<\/p>\n

 <\/p>\n

## \ubcf4\uad00\uc8fc\uae30\ub97c \uc904\uc778\ub2e4. 7\uc77c\ub85c \uc904\uc77c\uacbd\uc6b0<\/strong><\/p>\n

 <\/p>\n

SQL> exec dbms_stats.alter_stats_history_retention(7);<\/p>\n

 <\/p>\n

SQL> select dbms_stats.get_stats_history_retention from dual;<\/p>\n

 <\/p>\n

GET_STATS_HISTORY_RETENTION
—————————
                         7<\/p>\n

 <\/p>\n

## SYSAUX\uc5d0 \uc800\uc7a5\ub41c \uae30\uc874\uc758 old \ud1b5\uacc4\uc815\ubcf4\ub97c purge\ud55c\ub2e4.<\/strong><\/p>\n

 <\/p>\n

(\uc608\uc81c\ub294 2006\ub144 10\uc6d4 10\uc77c \uc774\uc804\uc758 data\ub97c \uc0ad\uc81c\ud568)<\/p>\n

 <\/p>\n

SQL> exec DBMS_STATS.PURGE_STATS(to_timestamp_tz(’10-10-2006 00:00:00 Asia\/Seoul’,’DD-MM-YYYY HH24:MI:SS TZR’));<\/p>\n

 <\/p>\n

\ud1b5\uacc4\uc815\ubcf4 data\ub97c purge\ud558\ub354\ub77c\ub3c4 \uae30\uc874\uc5d0 \ud560\ub2f9\ub41c segment\uc758 \ud06c\uae30\ub294 \uc904\uc5b4\ub4e4\uc9c0 \uc54a\uae30 \ub54c\ubb38\uc5d0 SYSAUX\uc758 free space\ub97c \ud655\ubcf4\ud558\ub824\uba74<\/p>\n

table\uacfc index\ub97c reorg\ud574\uc57c \ub41c\ub2e4.<\/p>\n

\uc5ec\uae30\uc11c 10g\uc758 \uc0c8\ub85c\uc6b4 \uae30\ub2a5\uc778 segment shrink\ub97c \uc0ac\uc6a9\ud558\uba74 \ub41c\ub2e4.<\/p>\n

 <\/p>\n

alter table WRI$_OPTSTAT_HISTGRM_HISTORY enable row movement;<\/p>\n

 <\/p>\n

alter table WRI$_OPTSTAT_HISTGRM_HISTORY shrink space;<\/p>\n

 <\/p>\n

–> WRI$_OPTSTAT_HISTGRM_HISTORY table\uc5d0 function-based index\uac00 \uac78\ub824 \uc788\uc5b4<\/span>
    shrink space \uba85\ub839\uc774 \uc815\uc0c1 \uc2e4\ud589\ub418\uc9c0 \uc54a\uc2b5\ub2c8\ub2e4.<\/span><\/font><\/p>\n

 <\/p>\n

\uc77c\ub2e8 WRI$_OPTSTAT_HISTGRM_HISTORY table\uc5d0 \uac78\ub824\uc788\ub294 function-based index\uc778<\/p>\n

I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST\uc640 I_WRI$_OPTSTAT_H_ST\ub97c drop\ud558\uace0 table shrink space\uba85\ub839\uc744 \uc2e4\ud589\ud55c \ud6c4<\/p>\n

function-based index\uc778 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST\uc640 I_WRI$_OPTSTAT_H_ST\ub97c \uc7ac\uc0dd\uc131 \ud574 \uc90d\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

1. drop indexes<\/p>\n

 <\/p>\n

drop\uc804\uc5d0 index\ub97c \uc0dd\uc131\ud558\ub294 \uad6c\ubb38\uc744 \ubbf8\ub9ac \ubf51\uc544 \ub193\uc740\ub2e4.<\/p>\n

 <\/p>\n

\uc778\ub371\uc2a4 \uc0dd\uc131 \uad6c\ubb38\uc740 dbms_metadata\ub97c \uc0ac\uc6a9\ud558\uc5ec \ubf51\uc544\ub0c4.<\/p>\n

 <\/p>\n

set long 10000<\/p>\n

select DBMS_METADATA.GET_DDL(‘INDEX’,’I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST’,’SYS’) from dual;<\/p>\n

select DBMS_METADATA.GET_DDL(‘INDEX’,’I_WRI$_OPTSTAT_H_ST’,’SYS’) from dual;<\/p>\n

DROP INDEX “SYS”.”I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST” ;<\/p>\n

DROP INDEX “SYS”.”I_WRI$_OPTSTAT_H_ST” ;<\/p>\n

 <\/p>\n

2.shrink table<\/p>\n

 <\/p>\n

alter table WRI$_OPTSTAT_HISTGRM_HISTORY enable row movement;<\/p>\n

alter table WRI$_OPTSTAT_HISTGRM_HISTORY shrink space;<\/p>\n

alter table WRI$_OPTSTAT_HISTGRM_HISTORY disable row movement;<\/p>\n

 <\/p>\n

## \uc2e4\uc81c\ub85c \ud06c\uae30\uac00 \uc904\uc5c8\ub294\uc9c0 \ud655\uc778<\/strong><\/p>\n

SQL> select occupant_name, space_usage_kbytes\/1024 “MB”
   from v$sysaux_occupants
   order by space_usage_kbytes<\/p>\n

OCCUPANT_NAME                                                            MB
—————————————————————- ———-
XDB                                                                          0
XSAMD                                                                     0
ODM                                                                         0
STATSPACK                                                              0
ORDIM                                                                      0
ORDIM\/SQLMM                                                         0
TEXT                                                                        0
ULTRASEARCH_DEMO_USER                                      0
EM_MONITORING_USER                                             0
EXPRESSION_FILTER                                                 0
ULTRASEARCH                                                          0
EM                                                                           0
ORDIM\/PLUGINS                                                        0
SDO                                                                          0
TSM                                                                        .25
JOB_SCHEDULER                                                     .375
STREAMS                                                                .5
AO                                                                          .75
XSOQHIST                                                               .75
LOGSTDBY                                                              .875
SM\/OTHER                                                             4.9375
LOGMNR                                                                5.875
WM                                                                        6.1875
SM\/ADVISOR                                                       110.9375
SM\/AWR                                                              511.4375
SM\/OPTSTAT                                                       667.125 –> 3G\uc5d0\uc11c 670M\ub85c \uc904\uc5b4\ub4ec.<\/span><\/font><\/p>\n

3. recreate indexes<\/p>\n

 <\/p>\n

alter session set workarea_size_policy=manual;<\/p>\n

alter session set sort_area_size=104857600<\/p>\n

;<\/p>\n

CREATE INDEX “SYS”.”I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST” 
ON “SYS”.”WRI$_OPTSTAT_HISTGRM_HISTORY” (“OBJ#”, “INTCOL#”, SYS_EXTRACT_UTC(“SAVTIME”))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 
TABLESPACE “SYSAUX” nologging;<\/p>\n

 <\/p>\n

CREATE INDEX “SYS”.”I_WRI$_OPTSTAT_H_ST” 
ON “SYS”.”WRI$_OPTSTAT_HISTGRM_HISTORY” (SYS_EXTRACT_UTC(“SAVTIME”))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “SYSAUX” nologging;<\/p>\n

 <\/p>\n

SQL> select occupant_name, space_usage_kbytes\/1024 “MB”
   from v$sysaux_occupants
   order by space_usage_kbytes<\/p>\n

OCCUPANT_NAME                                                            MB
—————————————————————- ———-
XDB                                                                          0
XSAMD                                                                     0
ODM                                                                         0
STATSPACK                                                              0
ORDIM                                                                      0
ORDIM\/SQLMM                                                         0
TEXT                                                                        0
ULTRASEARCH_DEMO_USER                                      0
EM_MONITORING_USER                                             0
EXPRESSION_FILTER                                                 0
ULTRASEARCH                                                          0
EM                                                                           0
ORDIM\/PLUGINS                                                        0
SDO                                                                          0
TSM                                                                         .25
JOB_SCHEDULER                                                      .375
STREAMS                                                                 .5
AO                                                                           .75
XSOQHIST                                                                .75
LOGSTDBY                                                               .875
SM\/OTHER                                                             4.9375
LOGMNR                                                                5.875
WM                                                                       6.1875
SM\/ADVISOR                                                      110.9375
SM\/AWR                                                             511.4375
SM\/OPTSTAT                                                      947.125  index \uc7ac\uc0dd\uc131 \ud6c4 670M\uc5d0\uc11c 950M\ub85c \ubcc0\uacbd<\/span><\/font><\/p>\n

 <\/p>\n

## segment \ud06c\uae30 \uc870\ud68c<\/strong><\/p>\n

select owner, segment_name, segment_type, bytes\/1024\/1024
from dba_segments
where tablespace_name = ‘SYSAUX’
order by bytes<\/p>\n

 <\/p>\n

SYS    I_WRI$_OPTSTAT_H_ST                INDEX                          120<\/p>\n

SYS    I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST    INDEX                          155<\/p>\n

SYS    I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST    <\/span>INDEX                          160<\/span><\/font><\/p>\n

SYS    <\/span>WRI$_OPTSTAT_HISTGRM_HISTORY    <\/span>TABLE                      177.875<\/span><\/font><\/p>\n

SYS    SYS_LOB0000788324C00004$$    LOBSEGMENT                     200<\/p>\n

SYS    WRI$_OPTSTAT_HISTHEAD_HISTORY    TABLE                          219<\/p>\n

## SYSAUX TABLESPACE\uc758 Free space \uc870\ud68c<\/strong><\/p>\n

<\/strong> <\/p>\n

select tablespace_name, sum(bytes)\/1024\/1024
from dba_free_space
where tablespace_name = ‘SYSAUX’
group by tablespace_name;<\/p>\n

 <\/p>\n

TABLESPACE_NAME                SUM(BYTES)\/1024\/1024
—————————— ——————–
SYSAUX                                    2892.9375<\/p>\n

<\/div>\n

<\/p>\n","protected":false},"excerpt":{"rendered":"

sysaux tablespace\ub294 10g\uc5d0\uc11c \uc0c8\ub85c \ucd94\uac00\ub41c system default tablespace\uc758 \ud558\ub098\ub85c \uae30\uc874\uc5d0 system tablespace\uc5d0   \uc800\uc7a5\ub418\ub358 \uac01\uc885 ORACLE OPTION\ub4e4\uc758 schema\uac00 \uc800\uc7a5\ub418\uba70 10g\uc758 new feature\uc778 AWR(auto workload repository) \ub370\uc774\ud130\ub4e4\uc774 \uc800\uc7a5\ub418\ub294 tablespace \uc785\ub2c8\ub2e4.   awr\uc815\ubcf4\ub294 default\ub85c 1\uc2dc\uac04\uc5d0 \ud55c\ubc88\uc529 data\ub97c gathering\ud558\uace0 \uadf8 \uc815\ubcf4\ub97c 7\uc77c\ub3d9\uc548 \uc800\uc7a5\ud558\uac8c \ub418\uc5b4 \uc788\uc2b5\ub2c8\ub2e4.   7\uc77c\uc774 \uc9c0\ub098\uba74 \uac00\uc7a5 \uc624\ub798\ub41c awr\uc815\ubcf4\ub97c \uc790\ub3d9\uc73c\ub85c \uc0ad\uc81c\ud558\uac8c\ub054 \ub418\uc5b4 \uc788\uc2b5\ub2c8\ub2e4.   \uc704\uc758 \ub0b4\uc6a9\ub300\ub85c \ub77c\uba74 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[14],"tags":[138,139,140],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/410"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=410"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/410\/revisions"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=410"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=410"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=410"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}