Shulou(Shulou.com)06/01 Report--

1. Statistical information

(1) change the preservation date of historical statistics

Select dbms_stats.get_stats_history_retention from dual;exec dbms_stats.alter_stats_history_retention (10)

(2) to clear the data before a certain timestamp, you can use the PURGE_STATS procedure. For example:

Exec DBMS_STATS.PURGE_STATS (to_timestamp_tz ('01-01-2018 00:00:00 Europe/London','DD-MM-YYYY HH24:MI:SS TZR'))

Space occupied by each schema of 2.sysaux tablespace

The # v$sysaux_occupants view records the space occupied by each component of sysaux, and provides a stored procedure for removing components in move_procedure. Col Schema for a25col Item for move_procedure for A40 select occupant_name "Item", space_usage_kbytes / 1024 "Space Used (MB)", schema_name "Schema", move_procedureFROM v$sysaux_occupants

3.AWR cleanup section

(1) query awr settings

Col SNAP_INTERVAL for a20col RETENTION for a20select * from dba_hist_wr_control

(2) adjust awr settings

SQL > begindbms_workload_repository.modify_snapshot_settings (interval = > 60 ~ 14); end

(3) Delete a snapshot

# query system snapshot select * from dba_hist_snapshot;# Delete snapshot number 100q101. If you want to delete a specified snapshot, the low_snap_id is the same as high_snap_id: begindbms_workload_repository.drop_snapshot_range (low_snap_id = > 100PowerSnapshot ID = > 101Magi dbid = > 1546882763); end;/

(4) disable awr

The sampling interval can be adjusted to a maximum of 100 years, and the sampling data retention time can be adjusted to a minimum of 1 day to disable AWR.

SQL > BEGINDBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (interval = > 3650024060); END;/

4. If OEM is used, it is also the main occupant of sysaux tablespace

5. The following is a reference for different situations where sysaux occupies space

6. Reference documentation

Oracle Database Administrator Guide.

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.