Sysaux table space usage high problem handling

Sysaux table space as a secondary table space for the system table space, mainly for the content of EM and table statistics, AWR snapshots, audit information, and so on, and if the Sysaux table space under the default conditions you do not make any configuration, over time, will expand more and more large.

After a few times the continuous expansion of the Sysaux table space has been 20G, so now is the time to consider weight loss.

1. View Table Space usage

Sql>

Select Tablespace_name,

Sum_m,

Max_m,

Count_blocks free_blk_cnt,

Sum_free_m,

To_char (+ * sum_free_m/sum_m, ' 99.9999 ') | | '% ' as Pct_free,

100-to_char (+ * sum_free_m/sum_m, ' 99.9999 ') | | '% ' as pct_used

From (Select tablespace_name, Sum (Bytes)/1024/1024 assum_m

From Dba_data_files

Group by Tablespace_name)

Left Join

(Select Tablespace_name as Fs_ts_name,

Max (Bytes)/1024/1024 as Max_m,

Count (Blocks) as Count_blocks,

Sum (bytes/1024/1024) as Sum_free_m

From Dba_free_space

Group bytablespace_name)

On tablespace_name = Fs_ts_name

ORDER by Sum_free_m/sum_m;

Tablespace_name sum_m max_m free_blk_cnt sum_free_mpct_free pct_used

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

Sysaux 21652 1031 13 1032.5 4.7686% 95.2314%

nticket_db 235520 7798 4922 46894 19.9108% 80.0892%

Nticket_index 112640 2418 764 25382 22.5337% 77.4663%

SYSTEM 2798 1884 7 1916.125 68.482% 31.518%

USERS 5 3.6875 1 3.6875 73.75% 26.25%

Portalmail 200 168.125 2 170 85% 15%

UNDOTBS2 10265 3596 200 9936.8125 96.8028% 3.1972%

UNDOTBS3 10265 3593 248 9983 97.2528% 2.7472%

UNDOTBS1 24455 3968 184 24280.625 99.287%. 713%

Strategy 20480 3968 6 20478 99.9902%. 98%

2. View the Sysaux table space in the specific proportion of the storage space, you can see the awr snapshot occupies nearly 20G of space, statistics of about 300M, while the database closed audit Audit_trail, so the audit table aud$ not occupy space

Sql> Col Item for A30

sql> col "Space Used (GB)" for A10

Sql> Col Schema for A20

sql> col "Moveprocedure" for A200

Sql>

Sql> Select Occupant_name "Item",

Round (space_usage_kbytes/1024/1024,3) "Space Used (GB)",

Schema_name "Schema",

Move_procedure "Moveprocedure"

From V$sysaux_occupants

ORDER by 2 Desc;

Item Space Usedschema Move Procedure

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

Sm/awr 19.103 SYS

Sm/optstat 0.311 SYS

Sm/advisor 0.248 SYS

XDB 0.124 XDB XDB. Dbms_xdb. Movexdb_tablespace

SDO 0.073MDSYS Mdsys. Move_sdo

EM 0.045SYSMAN EMD_MAINTENANCE.MOVE_EM_TBLSPC

Xsoqhist 0.037 SYS Dbms_xsoq. Olapimoveproc

AO 0.037SYS Dbms_aw. Move_awmeta

Ordim/orddata 0.013 orddata ORDSYS.ORD_ADMIN.MOVE_ORDIM_TBLSPC

LOGMNR 0.013 SYSTEM SYS. Dbms_logmnr_d.set_tablespace

Audit_tables 0 SYS Dbms_audit_mgmt.move_dbaudit_tables

3. Change the hold time of the statistics, the default is 31 days, this is changed to 7 days, the expired statistics will be deleted automatically

Sql> selectdbms_stats.get_stats_history_retention from dual;

Get_stats_history_retention

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

31

sql> exec dbms_stats.alter_stats_history_retention (15);

PL/SQL Procedure successfullycompleted

Sql> selectdbms_stats.get_stats_history_retention from dual;

Get_stats_history_retention

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

15

4. Changes to the Awr snapshot are saved for 7 days (7*24*60), collected once per hour, and can be viewed and modified via the EM interface

--Check the current system retention time is 8 days, 1 hours sampling

sql> SELECT * Fromdba_hist_wr_control;

DBID Snap_interval RETENTION Topnsql

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

1494575446 +0000001:00:00.0 +0000800:00:00.0 DEFAULT

--

Sql>

Begin

Dbms_workload_repository.modify_snapshot_settings (

Interval = 60,

retention = 10080,--min

Topnsql = 100

);

End

ORA-13541: System Move window baseline size (691200) greater than retention time (604800)

ORA-06512: In "SYS." Dbms_workload_repository ", line 174

ORA-06512: In "SYS." Dbms_workload_repository ", Line 222

ORA-06512: On line 2

Here 691200 (8*24*60*60), 604800 (7*24*60*60) are in seconds.

An error was found because the current system move window is larger than the time window now set.

--View the current moving_window_size of the system,

Sql> selectdbid,baseline_name,baseline_type,moving_window_size from Dba_hist_baseline;

DBID Baseline_name Baseline_typemoving_window_size

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

1494575446system_moving_window Moving_window 8

--Change its size to 7, or 7 days.

Sql> Execdbms_workload_repository.modify_baseline_window_size (7);

PL/SQL Procedure successfullycompleted

--Re-executing the save time to modify the Awr snapshot

Sql> execdbms_workload_repository. Modify_snapshot_settings (interval=>60,retention=> 7*24*60);

PL/SQL Procedure successfullycompleted

Note: The AWR settings can also be modified with EM

5. Delete the awr snapshot and view the Sysaux tablespace usage again.

--Query minimum and maximum snapshot ID

Sql> selectmin (snap_id), Max (snap_id) from Dba_hist_snapshot;

MIN (snap_id) MAX (snap_id)

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

8188 8397

--Delete The earliest 24 awr snapshots, which are the earliest 24-hour snapshots.

Sql> Execdbms_workload_repository.drop_snapshot_range (low_snap_id =>8188,high_snap_id = 8188+24);

Finally, look again at the tablespace discovery usage has decreased.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Sysaux table space usage high problem handling