Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to clean up SYSAUX tablespaces in Oracle. The content of the article is of high quality. Therefore, Xiaobian shares it with you as a reference. I hope you have a certain understanding of relevant knowledge after reading this article.

Oracle cleans SYSAUX table space SYSAUX table space: It is an auxiliary table space of SYSTEM table space, mainly storing EM-related contents and table statistics, AWR snapshot, audit information, etc. If the SYSAUX table space is full, it will not affect the main business, but if the database frequently collects statistical information, it may cause problems, even because the collection of statistical information hang-up, resulting in data unavailable, for SYSAUX table space, check regularly to ensure that there is still free space, if the space is full, you need to delete statistical information and AWR snapshots. ORA-1691: unable to extend lobsegment SYS.SYS_LOB00000208C0005 $$ by 128 in tablespace sysaux Query SYSAUX table space usage: COL Item FOR A30COL Schema FOR A30SELECT occupant_name "Item", space_usage_kbytes / 1048576 "Space Used (GB)", schema_name "Schema", move_procedure "Move Procedure"FROM ORDER v$sysaux_occupants BY 2 desc

SM/AWR: If its value is large, then AWR information capacity is large.

SM/OPTSTAT: If its value is large then the optimizer statistics capacity is large.

Query which table in the SYSAUX table space has the largest occupancy: select * from (select segment_name,SEGMENT_TYPE,sum(bytes)/1024/1024 total_mb from dba_segments where tablespace_name ='SYSAUX' group by segment_name,SEGMENT_TYPE order by 3 desc)where rownum =sysdate-9;SQL> select count(*) from mingshuo.ash_bak_20190610; COUNT(*)-------- 126481 row selected.2. Disable AWRexec dbms_workload_repository.modify_snapshot_settings(interval => 0);3. truncate WRH$_ACTIVE_SESSION_HISTORYTRUNCATE TABLE sys.WRH$_ACTIVE_SESSION_HISTORY;4. After truncate completes, rebuild the index of WRH$_ACTIVE_SESSION_HISTORY table and restore data from backup table insert into sys.WRH$_ACTIVE_SESSION_HISTORY select * from mingshuo.ash_bak_20190610;commit;5. set line 300 pages 200col owner for a20col index_name for a30select index_owner,index_name,partition_name,status,tablespace_name,last_analyzed from dba_ind_partitionswhere index_name in (select index_namefrom dba_indexes where table_name in ('WRH$_ACTIVE_SESSION_HISTORY')and table_owner ='SYS ');6. If the index fails rebuild the index (the index is not invalid at the time of testing, the index is the local index) alter index sys.WRH$_ACTIVE_SESSION_HISTORY_PK rebuild parallel 8 nlogging;alter index sys.WRH$_ACTIVE_SESSION_HISTORY_PK noparallel;7. Start AWRexec dbms_workload_repository.modify_snapshot_settings(interval => 60);8. Test awr and ash may generate correctly---manually generate snapshotexec dbms_workload_repository.create_snapshot;@?/ rdbms/admin/awrrpt@?/ rdbms/admin/ashrpt9. Delete backup table drop table mingshuo.ash_bak_20190610 purge; How to clean SYSAUX table space in Oracle is shared here. I hope the above content can be helpful to everyone and learn more knowledge. If you think the article is good, you can share it so that more people can see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.