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

What are the differences between the contents stored in SYSTEM and SYSAUX tablespaces? What should I do if SYSAUX takes up too much table space?

Answer: Under normal circumstances, business data generated by an enterprise should be stored in a separate data table space, rather than using table spaces that already exist in the system. In particular, business data cannot be stored in the SYSTEM and SYSAUX table spaces. Therefore, DBAs need to pay attention to the occupation of the SYSTEM and SYSAUX table spaces.

Oracle Server uses the SYSTEM tablespace to manage the entire database. This table space contains the system's data dictionary and administrative information about the database, which is contained in the SYS schema and accessible only to SYS users or other administrative users with the required permissions. SYSTEM tablespaces are used for core functionality, such as data dictionary tables.

SYSAUX is a secondary tablespace to the SYSTEM tablespace. Some components and products in earlier versions of Oracle DB that used the SYSTEM tablespace or their own tablespaces now use the SYSAUX tablespace instead. Every Oracle Database 10g (or later) database must have a SYSAUX tablespace. Secondary tablespaces SYSAUX are used for additional database components, such as OEM libraries (Oracle Enterprise Manager Repository), AWR snapshot repositories, statistics, audit information, and so on.

SYSTEM and SYSAUX tablespaces are required tablespaces created when the database is created. These tablespaces must be online. In the OPEN state, SYSAUX tablespaces can be taken offline for tablespace recovery, but SYSTEM tablespaces cannot, and neither can be set to read-only state. In the MOUNT state, any table space can be taken offline.

SYSTEM tablespaces generally don't change much in size, while SYSAUX tablespaces get larger and larger over time by default if left unconfigured. Therefore, if the SYSAUX table space is too large, it should be cleaned up in time.

For SYSTEM table space, if the occupation is too large, it is generally caused by the audit table (SYS.AUD$) being too large. You need to move the audit table to another table space and clean up the audit table (TRUNCATE TABLE SYS.AUD$). It should be noted that if the audit table is too large, it should be cleaned up separately. For detailed steps, please refer to the audit section.

For SYSAUX table space, if it occupies too much, it is generally caused by AWR information or object statistics not cleaned up in time. The specific reason can be queried through the following SQL statement:

SELECT OCCUPANT_NAME "Item",

SPACE_USAGE_KBYTES / 1048576 "Space Used (GB)",

SCHEMA_NAME "Schema",

MOVE_PROCEDURE "Move Procedure"

FROM V$SYSAUX_OCCUPANTS

WHERE SPACE_USAGE_KBYTES > 1048576

ORDER BY "Space Used (GB)" DESC;

If OCCUPANT_NAME is listed as SM/AWR (Server Managibility- Automatic Workload Repository), AWR information is too busy; if it is listed as SM/OPTSTAT (Server Managibility- Optimizer Statistics History), optimizer statistics are too busy.

You can also query the DBA_SEGMENTS view directly for information:

SELECT D.SEGMENT_NAME, D.SEGMENT_TYPE,SUM(BYTES)/1024/1024 SIZE_M

FROM DBA_SEGMENTS D

WHERE D.TABLESPACE_NAME = 'SYSAUX'

GROUP BY D.SEGMENT_NAME, D.SEGMENT_TYPE

ORDER BY SIZE_M DESC;

Then query the table that occupies a large space, you can get the reasons for the large space, which are discussed separately below.

(i) AWR information is too large

If it is confirmed that the AWR information takes up too much space, you can also use the following SQL script to obtain the details of the AWR information:

sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awrinfo.sql

If AWR information takes up too much space, the storage space of AWR information can be reduced by setting the retention time of AWR. The retention time of AWR can be obtained by the following SQL statement:

SELECT * FROM DBA_HIST_WR_CONTROL;

AWR information can be set to be retained for 7 days (7*24*60) by using the following SQL statement, and AWR information is collected every 1 hour:

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>60, RETENTION=>7*24*60);

Note that in Oracle 10g, AWR is reserved for 7 days by default, and in Oracle 11g, AWR is reserved for 8 days by default.

After the above settings are completed, you can delete the AWR snapshot information that is not needed, thus freeing up the SYSAUX table space. The related SQL statement is as follows:

SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT;

SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_ACTIVE_SESS_HISTORY;

BEGIN

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(

LOW_SNAP_ID => 1,

HIGH_SNAP_ID => 36768,

DBID => 1148453265);

END;

If DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE executes too slowly, you can perform TRUNCATE first:

select distinct 'truncate table '||segment_name||';',s.bytes/1024/1024

from dba_segments s

where s.segment_name like 'WRH$%'

and segment_type in ('TABLE PARTITION', 'TABLE')

and s.bytes/1024/1024>100

order by s.bytes/1024/1024/1024 desc;

After the TRUNCATE operation, execute DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE. Note that TRUNCATE above clears all information in AWR. Therefore, you need to confirm that you need to release these AWR information first. Of course, you can also export the AWR information you need first, and then clear the above AWR information.

It should be noted that DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE is a completely clean up of work through the Delete operation. Therefore, after execution, the SYSAUX table space is not actually released. At this point, a MOVE or TRUNCATE operation should be performed on the associated table. When MOVE operation is performed, since all tables of AWR information are partitioned tables, MOVE operation cannot be performed on all tables of partitioned tables, so MOVE operation needs to be performed on partitions separately, for example:

ALTER TABLE WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION partition name;

After the MOVE operation, the index needs to be rebuilt. Similarly, for partitioned indexes, only a single index of a partition can be rebuilt, not an entire index:

ALTER INDEX WRH$_ACTIVE_SESSION_HISTORY_PK REBUILD PARTITION partition name;

Note that you can add an "UPDATE GLOBAL INDEXES" clause to the above SQL to keep the global index from being invalidated.

(ii) Statistical information is too large

If statistics take up too much space, you can modify the retention time of statistics. Statistics are retained for 31 days by default, and expired statistics are automatically deleted.

SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL; --Retention time of query statistics

EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7); --Sets the retention time of statistics

If statistics are found to take up a lot of space on SYSAUX, consider using the DBMS_STATS.PURGE_STATS procedure to implement cleanup.

The following SQL statement is useful for diagnosing SYSAUX table space occupancy:

SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM WRI$_OPTSTAT_TAB_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_IND_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_AUX_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_TAB_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_IND_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_AUX_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_OPR;

The following SQL can query invalid ASH information:

SELECT COUNT(*)

FROM SYS.WRH$_ACTIVE_SESSION_HISTORY A

WHERE NOT EXISTS (SELECT 1

FROM SYS.WRM$_SNAPSHOT B

WHERE A.SNAP_ID = B.SNAP_ID

AND A.DBID = B.DBID

AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER);

A final note is that the background process responsible for collecting and cleaning AWR information is MMON, and the implicit parameter "_swrf_test_action" can debug MMON behavior, which can be used in conjunction with the 10046 event. MMON process will automatically refresh a certain amount of AWR data to disk every minute. By default, MMON will clean up AWR information every 30 minutes. You can see the words "MMON Auto-Purge cycle" in the trace file.

Description:

For more information about SYSTEM and SYSAUX, please refer to my BLOG: blog.itpub.net/26736162/viewspace-2152868/

For more information on auditing, please refer to my BLOG: blog.itpub.net/26736162/viewspace-2140644/

For more information about database operations that cause index failures, please refer to my BLOG: blog.itpub.net/26736162/viewspace-2152871/

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.