Processing method of Oracle Tablespace usage too large

First, view the usage of table space in Oracle database

Select A.tablespace_name "tablespace name", Total         "tablespace size", free         "table space remaining Size",         (total-free) "Tablespace use size",         total/(1024 *1024*1024) "Table space Size (g)",         free/(1024*1024*1024) "Table space remaining size (g)",         (Total-free)/(1024*1024*1024) "Tablespace use Size (g)",         ROUND ((total-free)/Total, 4) * 100 "Utilization%"  From (select Tablespace_name, SUM (bytes) free from            dba_free_space           GROUP by Tablespace_name) A,         (select Tablespace_name, SUM (bytes) Total from           dba_data_files          GROUP by tablespace_name) b  WHERE A.tablespace_ name = B.tablespace_name;

Second, the system table space is too large (utilization rate of more than 95%)

1) Check aud$ table is size

--View database table size Sqlselect bytes,owner,segment_name from dba_segments where segment_type= ' table ' ORDER by bytes desc;--View aud$ Table size Sqlselect bytes,owner,segment_name from dba_segments where segment_type= ' table ' and segment_name = ' aud$ ';

2) If the aud$ is too large, clean the

After exporting the aud$ table, clean it with truncate

3) If the aud$ table is empty, the system table space usage is still 99%, it is recommended to increase the data file

A) Add data file Example statements

Annotations: Increase the size of the table space yourself

Alter tablespace system add datafile ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\SGTMS\SYSTEM02. DBF ' Size 50M autoextend on

b) Query validation file was added successfully

SELECT * from Dba_data_files;

Three, Sysaux table space (utilization rate of more than 95%)

1) Modify Statistics save time

A) Check statistics save time ( default should be 31 days)

Select Dbms_stats.get_stats_history_retention from dual;

bIf you change it to 7 days in 31 days

EXEC dbms_stats.alter_stats_history_retention (7);

c) Verify that the modification is successful

Select Dbms_stats.get_stats_history_retention from dual;

2) Delete the AWR report snapshot

Annotations: The snapshot in Oracle 10g is reserved for 7 days, 11g snapshots are reserved for 8 days, and the excess is automatically deleted. An awr snapshot can be imported from another database, and this part of the data is stored for a very long time. There are times when automatic snapshots are not automatically collected, and manually created snapshots can be successful, in which case you need to clean up the previous snapshot.

There are two ways to remove AWR: dbms_workload_repository,dbms_swrf_internal. Dbms_workload_repository can delete snapshots of local and other databases, can choose different snapshots to delete, dbms_swrf_internal can only take snapshots of other databases, and will kill all the snapshots directly.

a) Delete with Dbms_workload_repository package

Select dbid, retention from Dba_hist_wr_control; Select min (snap_id), Max (snap_id) from dba_hist_snapshot where dbid = ' obtained dbid value '; exec dbms_workload_repository.drop_snapshot_range (' Get min (snap_id) value ', ' Get Max (snap_id) value ', ' get dbid value '); SELECT * from dba_hist_snapshot where dbid = ' obtained dbid value ';

b) Delete with Dbms_swrf_internal package

Select dbid, retention from Dba_hist_wr_control; Select min (snap_id), Max (snap_id) from dba_hist_snapshot where dbid = ' obtained dbid value '; exec dbms_swrf_internal.unregister_database (' Get dbid value '); SELECT * from dba_hist_snapshot where dbid = ' obtained dbid value ';

Processing method of Oracle Tablespace usage too large