{"id":764,"date":"2015-11-30T16:34:19","date_gmt":"2015-11-30T16:34:19","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=764"},"modified":"2015-11-30T16:34:19","modified_gmt":"2015-11-30T16:34:19","slug":"sysaux-table-shrink","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=764","title":{"rendered":"SYSAUX Table Shrink"},"content":{"rendered":"

1. \uc2a4\ub0c5\uc0f7 \uc124\uc815 \ud655\uc778 <\/SPAN><\/P>
\n

select * from dba_hist_wr_control ; <\/SPAN><\/P>
\n

   <\/P>
\n

2. \ud604\uc7ac \uc2a4\ub0c5\uc0f7 \uc870\ud68c <\/SPAN><\/P>
\n

col startup_time for a30 <\/SPAN><\/P>
\n

col begin_interval_time for a30 <\/SPAN><\/P>
\n

select snap_id, startup_time, begin_interval_time, snap_level from dba_hist_snapshot order by 3 ; <\/SPAN><\/P>
\n

   <\/P>
\n

3. SYSAUX \uc0ac\uc6a9\ub7c9 \uc870\ud68c <\/SPAN><\/P>
\n

select df.tablespace_name “Tablespace”, <\/SPAN><\/P>
\n

round(df.TBS_byte \/1048576,2) “Total(MB)”, <\/SPAN><\/P>
\n

round((df.TBS_byte – fs.Free_byte)\/1048576,2) “Used(MB)”, <\/SPAN><\/P>
\n

round(fs.Free_byte \/1048576,2) “Free(MB)”, <\/SPAN><\/P>
\n

round((fs.Free_byte\/df.TBS_byte) *100,0) “Free(%)”, <\/SPAN><\/P>
\n

fs.pieces “Pieces”, <\/SPAN><\/P>
\n

round(fs.Max_free \/1048576,2) “MaxFree(MB)”, <\/SPAN><\/P>
\n

db.EXTENT_MANAGEMENT <\/SPAN><\/P>
\n

from ( select tablespace_name, sum(bytes) TBS_byte <\/SPAN><\/P>
\n

from dba_data_files group by tablespace_name ) df, <\/SPAN><\/P>
\n

( select tablespace_name, max(bytes) Max_free, sum(bytes) Free_byte, count(*) pieces <\/SPAN><\/P>
\n

from dba_free_space group by tablespace_name ) fs, <\/SPAN><\/P>
\n

( select tablespace_name, initial_extent, next_extent,EXTENT_MANAGEMENT <\/SPAN><\/P>
\n

from dba_tablespaces ) db <\/SPAN><\/P>
\n

where df.tablespace_name = db.tablespace_name <\/SPAN><\/P>
\n

and df.tablespace_name = fs.tablespace_name(+) <\/SPAN><\/P>
\n

and df.tablespace_name = ‘SYSAUX’ <\/SPAN><\/P>
\n

order by 5 <\/SPAN><\/P>
\n

\/ <\/SPAN><\/P>
\n

   <\/P>
\n

4. v$SYSAUX_OCCUPANTS \uc870\ud68c <\/SPAN><\/P>
\n

col OCCUPANT_NAME for a30 <\/SPAN><\/P>
\n

col SCHEMA_NAME for a20 <\/SPAN><\/P>
\n

select OCCUPANT_NAME,SCHEMA_NAME,SPACE_USAGE_KBYTES from v$sysaux_occupants order by 3 ; <\/SPAN><\/P>
\n

   <\/P>
\n

5. SYSAUX SEGMENT \uc870\ud68c <\/SPAN><\/P>
\n

col OWNER for a20 <\/SPAN><\/P>
\n

col SEGMENT_NAME for a30 <\/SPAN><\/P>
\n

col SEGMENT_TYPE for a20 <\/SPAN><\/P>
\n

col TABLESPACE_NAME for a20 <\/SPAN><\/P>
\n

select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES\/1024\/1024 “MB” from dba_segments where tablespace_name=’SYSAUX’ order by 6 ; <\/SPAN><\/P>
\n

##select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES\/1024\/1024 “MB” from dba_segments where tablespace_name=’SYS1′ order by 6 ; <\/SPAN><\/P>
\n

   <\/P>
\n

6. \uc2a4\ub0c5\uc0f7 min, max \uc870\ud68c <\/SPAN><\/P>
\n

select min(snap_id), max(snap_id) from WRM$_SNAPSHOT ; <\/SPAN><\/P>
\n

   <\/P>
\n

7. \uc2a4\ub0c5\uc0f7\uc774 \uc5c6\ub294 orphaned \ud589 \uc870\ud68c <\/SPAN><\/P>
\n

##select count(*) From WRH$_LATCH_CHILDREN where snap_id < 842 ; <\/SPAN><\/P>
\n

select count(*) From WRH$_LATCH_CHILDREN where snap_id < min(snap_id) ; <\/SPAN><\/P>
\n

   <\/P>
\n

8. orphaned \uc0ad\uc81c, \uc0ac\uc774\uc988 \uccb4\ud06c <\/SPAN><\/P>
\n

   <\/P>
\n

DELETE <\/SPAN><\/P>
\n

FROM WRH$_LATCH_CHILDREN a <\/SPAN><\/P>
\n

WHERE NOT EXISTS <\/SPAN><\/P>
\n

(SELECT 1 <\/SPAN><\/P>
\n

FROM wrm$_snapshot <\/SPAN><\/P>
\n

WHERE snap_id = a.snap_id <\/SPAN><\/P>
\n

AND dbid = a.dbid <\/SPAN><\/P>
\n

AND instance_number = a.instance_number <\/SPAN><\/P>
\n

); <\/SPAN><\/P>
\n

   <\/P>
\n

9. \ud14c\uc774\ube14 \uc26c\ub9c1\ud06c, \uc0ac\uc774\uc988 \uccb4\ud06c <\/SPAN><\/P>
\n

alter table WRH$_LATCH_CHILDREN shrink space ; <\/SPAN><\/P>
\n

alter index WRH$_LATCH_CHILDREN_PK rebuild partition ‘partition_name’ ; <\/SPAN><\/P>
\n

   <\/P>
\n

10. \ud655\uc778 <\/SPAN><\/P>
\n

select min(snap_id), max(snap_id) from WRM$_SNAPSHOT ; <\/SPAN><\/P>
\n

select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES\/1024\/1024 “MB” from dba_segments where tablespace_name=’SYSAUX’ order by 6 ; <\/SPAN><\/P>
\n

   <\/P>
\n

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

1. \uc2a4\ub0c5\uc0f7 \uc124\uc815 \ud655\uc778 select * from dba_hist_wr_control ;     2. \ud604\uc7ac \uc2a4\ub0c5\uc0f7 \uc870\ud68c col startup_time for a30 col begin_interval_time for a30 select snap_id, startup_time, begin_interval_time, snap_level from dba_hist_snapshot order by 3 ;     3. SYSAUX \uc0ac\uc6a9\ub7c9 \uc870\ud68c select df.tablespace_name “Tablespace”, round(df.TBS_byte \/1048576,2) “Total(MB)”, round((df.TBS_byte – fs.Free_byte)\/1048576,2) “Used(MB)”, round(fs.Free_byte \/1048576,2) “Free(MB)”, round((fs.Free_byte\/df.TBS_byte) *100,0) […]<\/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":[9],"tags":[138,237,445,446],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/764"}],"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=764"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/764\/revisions"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=764"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=764"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=764"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}