一:使用下列语句查询表空间使用率 SELECT * FROM ( SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", BLOCKS "SUM_BLOCKS", SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)", ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)", FREE_SPACE || 'M' "FREE_SPACE(M)" FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", BLOCKS SUM_BLOCKS, USED_SPACE || 'M' "USED_SPACE(M)", ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ORDER BY 1) WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM','UNDOTBS1','TEMP');
二:查询SYSAUX表空间内各个分类项目占存储空间的比重,很明显可以看出来AWR快照占用了2G左右的空间,统计信息为149M左右,同时数据库关闭了审计audit_trail,所以审计表aud$不占空间 SELECT occupant_name "Item", space_usage_kbytes / 1048576 "Space Used (GB)", schema_name "Schema", move_procedure "Move Procedure" FROM v$sysaux_occupants ORDER BY 1
./opatch lsinventory -detail
@?/rdbms/admin/awrinfo
select dbms_stats.get_stats_history_retention from dual;
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;
Modify retention period: DBMS_STATS.ALTER_STATS_HISTORY_RETENTION Purge old statistics: DBMS_STATS.PURGE_STATS This procedure purges old versions of statistics saved in the dictionary. To run this procedure, you must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.
1. Stats Retention is set to 31 days. By Default it is 7 days. I suggest you can consider reducing the retention days to 10.
2. The number of rows exits in the SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY is 42 Million Rows , where the data exists from “09-JUN-11 01.52.06.895132 PM -05:00 ” to “22-AUG-11 02.53.34.754747 PM -05:00”
3. For other tables ie: SYS. WRI$_OPTSTAT_TAB_HISTORY, SYS.WRI$_OPTSTAT_HISTGRM_HISTORY, the data exists for a month, this is just because the retention set to 31.
ACTION PLAN: =========
A) Purge the Snapshot . Retain data for 10 days and then purge all the other data. (The number of days data to be kept depends on your Business needs)
SQL> SPOOL CHECK1.OUT
1) Try to force the execution of the purge operations :
SQL> alter session set “_swrf_test_action” = 72;
2) Purging snapshots :
SQL> exec dbms_stats.purge_stats(sysdate-&days);
using &days = n, n-1, n-2, …, n-x
3) Then again execute the below set of SQL’s and upload the spool output file
SQL> SELECT MIN(SAVTIME),MAX(SAVTIME) FROM WRI$_OPTSTAT_TAB_HISTORY; SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_TAB_HISTORY;
SQL> SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY; SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
SQL> SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY; SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
SQL> SPOOL OFF
B) Change the No. of Retention days from 31 to 10. For performing the same, use the below command : (The new retention time is specified in minutes.)
NOTE : The parameter value is in minutes so 10 daysx 24 hours x 60 minutes = 14400 minutes
C) Check the value of STATISTICS_LEVEL
If the above value is Set to ALL, then consider changing to TYPICAL.
The reason for requesting to change it to TYPICAL , is because statistics_level=ALL will gather lot of additional information in AWR repository which would consume more space.
Most of the cases, if the statistics_level is set to TYPICAL then the growth would be stopped.
Once the above actions performed, please keep us posted on the status of the same
BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production
--截取获取数据库报告的片段,正常是显示快照id
输入 num_days 的值: 1 Listing the last 1 days of Completed Snapshots --手工生成快照保存,很明显是表空间不足 SQL> exec dbms_workload_repository.create_snapshot(); BEGIN dbms_workload_repository.create_snapshot(); END; * 第 1 行出现错误: ORA-13509: 更新 AWR 表时出错 ORA-01683: 索引 ORA-01683: 索引 SYS.WRH$_ACTIVE_SESSION_HISTORY_PK 分区 WRH$_ACTIVE_1148453265_0 无法通过 8192 (在表空间 SYSAUX 中) 扩展 . 分区 无法通过 (在表空间 中) 扩展 ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 99 ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 122 ORA-06512: 在 line 1 --查询SYSAUX表空间的使用情况,消耗37G,快满了 SQL> SELECT Upper(F.TABLESPACE_NAME) "表空间名", D.TOT_GROOTTE_MB "表空间大小(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99') || '%' "使用比", F.TOTAL_BYTES "空闲空间(M)", F.MAX_BYTES "最大块(M)" FROM (SELECT TABLESPACE_NAME, Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES, Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME and D.tablespace_name = 'SYSAUX'; 表空间名 表空间大小(M) 已使用空间(M) 使用比 空闲空间(M) 最大块(M) --------- ------------- ------------- -------- ----------- ---------- SYSAUX 37887.98 37865.6 99.94% 22.38 1 --查看SYSAUX表空间表的使用情况 SQL> select * from (select segment_name, segment_type, bytes / 1024 / 1024 from dba_segments where tablespace_name = 'SYSAUX' and bytes / 1024 / 1024 >1000 order by bytes desc); SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024 ------------------------------------- ------------------ --------------- WRH$_ACTIVE_SESSION_HISTORY TABLE PARTITION 13479 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 2590 WRI$_OPTSTAT_HISTGRM_HISTORY TABLE 2242 WRH$_EVENT_HISTOGRAM_PK INDEX PARTITION 1856 WRH$_EVENT_HISTOGRAM TABLE PARTITION 1792 I_WRI$_OPTSTAT_H_ST INDEX 1544 WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 1472 WRH$_LATCH TABLE PARTITION 1155
Oracle Database - Enterprise Edition - Version 10.2.0.1 and later Information in this document applies to any platform.
SYMPTOMS
AWR tables are not being purged according to settings in sys.wrm$_wr_control. Because of this the tables are accumulating more and more rows and the segments associated with these tables become very large.
CAUSE
Oracle decides what rows need to be purged based on the retention policy. There is a special mechanism which is used in the case of the large AWR tables where we store the snapshot data in partitions. One method of purging data from these tables is by removing partitions that only contain rows that have exceeded the retention criteria. During the nightly purge task, we only drop the partition if all the data in the partition has expired. If the partition contains at least one row which, according to the retention policy shouldn't be removed, then the partition won't be dropped and as such the table will contain old data.
If partition splits do not occur (for whatever reason), then we can end up with a situation where we have to wait for the latest entries to expire before the partition that they sit in can be removed. This can mean that some of the older entries can be retained significantly past their expiry date. The result of this is that the data is not purged as expected.
SOLUTION
A potential solution to this issue is to manually split the partitions of the partitioned AWR objects such that there is more chance of the split partition being purged.You will still have to wait for all the rows in the new partitions to reach their retention time but with split partitions there is more chance of this happening. you can manually split the partitions using the following undocumented command:
alter session set "_swrf_test_action" = 72;
To perform a single split of all the AWR partitions.
Check the partition details for the offending table before the split:
SELECT owner, segment_name, partition_name, segment_type, bytes/1024/1024/1024 Size_GB FROM dba_segments WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';
Split the partitions so that there is more chance of the smaller partition being purged:
alter session set "_swrf_test_action" = 72;
NOTE: This command will split partitions for ALL partitioned AWR objects. It also initiates a single split; it does not need to be disabled and will need to be repeated if multiple splits are required.
Check the partition details for the offending table after the split:
SELECT owner, segment_name, partition_name, segment_type, bytes/1024/1024/1024 Size_GB FROM dba_segments WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';
With smaller partitions it is expected that some will be automatically removed when the retention period of all the rows within each partition is reached.
As an alternative, you could purge data based upon a snapshot range. Depending on the snapshots chosen, this may remove data that has not yet reached the retention limit so this may not be suitable for all cases.
The following output shows the min and max snapshot_id in each partition.
set serveroutput on declare CURSOR cur_part IS SELECT partition_name from dba_tab_partitions WHERE table_name = 'WRH$_ACTIVE_SESSION_HISTORY';
query1 varchar2(200); query2 varchar2(200);
TYPE partrec IS RECORD (snapid number, dbid number); TYPE partlist IS TABLE OF partrec;
Outlist partlist; begin dbms_output.put_line('PARTITION NAME SNAP_ID DBID'); dbms_output.put_line('--------------------------- ------- ----------');
for part in cur_part loop query1 := 'select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid'; execute immediate query1 bulk collect into OutList;
if OutList.count > 0 then for i in OutList.first..OutList.last loop dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid||' '||OutList(i).dbid); end loop; end if;
query2 := 'select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid'; execute immediate query2 bulk collect into OutList;
if OutList.count > 0 then for i in OutList.first..OutList.last loop dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid||' '||OutList(i).dbid); dbms_output.put_line('---'); end loop; end if;
end loop; end; /
Once you have split the partitions and identified a partition with a range of snap ids that can be deleted, you can free up the memory by dropping a snapshot range than matches the high and low snap_ids for the partition:
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE( low_snap_id IN NUMBER, high_snap_id IN NUMBER dbid IN NUMBER DEFAULT NULL);
其方法就是执行alter session set "_swrf_test_action" = 72; 让AWR相关的分区表都创建一个新的分区,等待老分区的数据过期,oracle就会自动删除。
1.检查是否有无效的ASH信息 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); 2.清理无效的ASH信息 delete 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); 3.对ASH表清理后的碎片整理 alter table sys.wrh$_active_session_history enable row movement; alter table sys.wrh$_active_session_history shrink space cascade; alter table sys.wrh$_active_session_history disable row movement; 4.收集碎片整理后表的统计信息 EXEC dbms_stats.gather_table_stats(ownname => ‘SYS’,tabname => ‘WRH$_ACTIVE_SESSION_HISTORY’,cascade => TRUE); 3.检查表空间可收缩的的位置 select a.FILE#, a.NAME, a.BYTES / 1024 / 1024 mb, ceil(HWM * A.BLOCK_SIZE) / 1024 / 1024 RESIZETO, ‘ALTER DATABASE DATAFILE ”’ || A.NAME || ”’ RESIZE ‘ || (trunc(CEIL(HWM * A.BLOCK_SIZE) / 1024 / 1024)+20) || ‘M;’ RESIZECMD from v$datafile a, (SELECT C.file_id, MAX(C.block_id + C.blocks – 1) HWM FROM DBA_EXTENTS C GROUP BY FILE_ID) B WHERE A.FILE# = B.FILE_ID AND a.tablespace=’SYSAUX’ ORDER BY 5;
三.SYSAUX清理后的检查
1.清理后的无效INDEX检查 select * from dba_indexes where status<>‘VALID’ AND STATUS<>‘N/A’; SELECT * FROM DBA_IND_PARTITIONS WHERE STATUS<>‘USABLE’ AND STATUS<>‘N/A’; SELECT * FROM DBA_IND_SUBPARTITIONS WHERE STATUS<>‘USABLE’; 上面语句应均无数据返回,如有则对这些INDEX进行重建 2.清理后的INDEX并行度检查 select * from dba_indexes where degree not in (’1′,’0′,’DEFAULT’);
oracle数据库清理和回收system和sysaux表空间
作者:dbdream
前几天和一个网友讨论了下SYSAUX表空间使用率过高的问题,今天有时间整理一下,正好我们的测试数据库也存在这个问题。本案例数据库版本为11.2.0.4.0。 SYSAUX表空间被称为系统辅助表空间,是10g版本开始推出的新功能,主要的目的是为SYSTEM表空间减负,Oracle对SYSTEM表空间的维护有一套独立的体系,对SYSTEM表空间操作会占用额外的CPU资源,而且效率低下,详见我之前发表的文章为什么不要把用户表存储到SYSTEM表空间。在10g版本,增加了SYSAUX辅助表空间,将EM、AWR等组件的表从SYSTEM表空间挪到了SYSAUX表空间中,这样大大减少了SYSTEM表空间的消耗,也减少了Oracle对SYSTEM表空间维护的成本。 但是有几个比较坑爹的组件需要的表并没有挪到SYSAUX表空间,比如常见的审计用到的AUD$表,很多DBA都可能遇到SYSTEM表空间使用率过高,查询发现是AUD$表很大导致的,我们的数据库正好打开了审计功能,正好可以拿来做实验。 下面先查询下SYSTEM和SYSAUX表空间的使用率。 sys@IVLDB> SELECT * FROM ( 2 SELECT D.TABLESPACE_NAME, 3 SPACE || 'M' "SUM_SPACE(M)", 4 BLOCKS "SUM_BLOCKS", 5 SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)", 6 ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' 7 "USED_RATE(%)", 8 FREE_SPACE || 'M' "FREE_SPACE(M)" 9 FROM ( SELECT TABLESPACE_NAME, 10 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 11 SUM (BLOCKS) BLOCKS 12 FROM DBA_DATA_FILES 13 GROUP BY TABLESPACE_NAME) D, 14 ( SELECT TABLESPACE_NAME, 15 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE 16 FROM DBA_FREE_SPACE 17 GROUP BY TABLESPACE_NAME) F 18 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 19 UNION ALL 20 SELECT D.TABLESPACE_NAME, 21 SPACE || 'M' "SUM_SPACE(M)", 22 BLOCKS SUM_BLOCKS, 23 USED_SPACE || 'M' "USED_SPACE(M)", 24 ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", 25 NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" 26 FROM ( SELECT TABLESPACE_NAME, 27 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 28 SUM (BLOCKS) BLOCKS 29 FROM DBA_TEMP_FILES 30 GROUP BY TABLESPACE_NAME) D, 31 ( SELECT TABLESPACE_NAME, 32 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, 33 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE 34 FROM V$TEMP_SPACE_HEADER 35 GROUP BY TABLESPACE_NAME) F 36 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 37 ORDER BY 1) 38 WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM');
sys@IVLDB> select * from ( 2 select segment_name,sum(bytes)/1024/1024 total_mb,tablespace_name from dba_segments where tablespace_name in ('SYSTEM','SYSAUX') group by segment_name,tablespace_name order by 2 desc) 3 where rownum <=20;
查询WRH$_ACTIVE_SESSION_HISTORY表会发现数据已经被删除了很多,从删除之前的16918966条记录变为删除后的4706条记录,但是表的大小没变,还是5GB多。 sys@IVLDB> select count(*) from WRH$_ACTIVE_SESSION_HISTORY; COUNT(*) ---------- 4706 下面通过MOVE操作回收这个表的水位线,来回收这部分被删除数据占用的空间。这个表是分区表,分区表不支持表级别的MOVE操作,直接对分区表进行MOVE操作会遇到ORA-14511错误。 sys@IVLDB> alter table WRH$_ACTIVE_SESSION_HISTORY move; alter table WRH$_ACTIVE_SESSION_HISTORY move * ERROR at line 1: ORA-14511: cannot perform operation on a partitioned object 查看这个表的分区信息,只有两个分区。 sys@IVLDB> select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY'; SEGMENT_NAME PARTITION_NAME GB ------------------------------ ------------------------------ ---------- WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1357933872_0 5.125 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN .000061035
下面按照分区进行MOVE操作,来回收空间。 sys@IVLDB> alter table WRH$_ACTIVE_SESSION_HISTORY move partition WRH$_ACTIVE_1357933872_0; Table altered. sys@IVLDB> alter table WRH$_ACTIVE_SESSION_HISTORY move partition WRH$_ACTIVE_SES_MXDB_MXSN; Table altered. 对分区表进行MOVE之后,需要重建索引,查看这个表的索引信息。 sys@IVLDB> select index_name from dba_indexes where table_name='WRH$_ACTIVE_SESSION_HISTORY'; INDEX_NAME ------------------------------ WRH$_ACTIVE_SESSION_HISTORY_PK
这个表只有一个主键,而且是分区索引,也不能对分区索引直接进行REBUILD操作,否则会遇到ORA-14086错误。 sys@IVLDB> alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild; alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild * ERROR at line 1: ORA-14086: a partitioned index may not be rebuilt as a whole
下面查看下这个索引的分区信息。 sys@IVLDB> select partition_name from dba_ind_partitions where index_name='WRH$_ACTIVE_SESSION_HISTORY_PK'; PARTITION_NAME ------------------------------ WRH$_ACTIVE_1357933872_0 WRH$_ACTIVE_SES_MXDB_MXSN 然后按照分区进行重建索引。 sys@IVLDB> alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild partition WRH$_ACTIVE_1357933872_0; Index altered. sys@IVLDB> alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild partition WRH$_ACTIVE_SES_MXDB_MXSN; Index altered. 再次查看,WRH$_ACTIVE_SESSION_HISTORY的空间已经回收。 sys@IVLDB> select sum(bytes)/1024/1024 from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY'; SUM(BYTES)/1024/1024 -------------------- 2.0625 sys@IVLDB> select sum(bytes)/1024/1024 from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY_PK'; SUM(BYTES)/1024/1024 -------------------- .3125 可见,这个表大小由之前的5248.0625MB将为2.0625MB,重建索引后,索引的大小也由之前的712.0625MB降为了0.3125MB,这一张表就回收了6GB左右的空间。 sys@IVLDB> SELECT occupant_name "Item", 2 space_usage_kbytes / 1048576 "Space Used (GB)", 3 schema_name "Schema", 4 move_procedure "Move Procedure" 5 FROM v$sysaux_occupants 6 where occupant_name='SM/AWR'; Item Space Used (GB) Schema Move Procedure -------------- --------------- -------------------- ------------------ SM/AWR 13.0436401 SYS
通过v$sysaux_occupants视图,可以查询到AWR占用空间由之前的将近19GB降为了不到13GB。按照同样的方法,回收下WRH$_EVENT_HISTOGRAM表的空间,这也是分区表表1794MB,只有一个索引(是主键)2499MB。 sys@IVLDB> select partition_name from dba_tab_partitions where table_name='WRH$_EVENT_HISTOGRAM';
PARTITION_NAME ------------------------------ WRH$_EVENT_HISTO_MXDB_MXSN WRH$_EVENT__1357933872_0 sys@IVLDB> alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT_HISTO_MXDB_MXSN; Table altered. sys@IVLDB> alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT__1357933872_0; Table altered. sys@IVLDB> select index_name from dba_indexes where table_name='WRH$_EVENT_HISTOGRAM'; INDEX_NAME ------------------------------ WRH$_EVENT_HISTOGRAM_PK sys@IVLDB> select partition_name from dba_ind_partitions where index_name='WRH$_EVENT_HISTOGRAM_PK'; PARTITION_NAME ------------------------------ WRH$_EVENT_HISTO_MXDB_MXSN WRH$_EVENT__1357933872_0 sys@IVLDB> alter index WRH$_EVENT_HISTOGRAM_PK rebuild partition WRH$_EVENT_HISTO_MXDB_MXSN; Index altered. sys@IVLDB> alter index WRH$_EVENT_HISTOGRAM_PK rebuild partition WRH$_EVENT__1357933872_0; Index altered.
清理WRH$_ACTIVE_SESSION_HISTORY和WRH$_EVENT_HISTOGRAM两张表,SYSAUX表空间就释放出10GB的空间,其他占用SYSAUX空间比较大的表也可以安装这样的方法去释放空间,这里就不一一演示了。 可能有人会问了,既然已经删除了数据,那么如果不回收这部分空间,ORACLE就不会再使用这部分空间了吗?为什么非要回收呢?这个高水位线的问题主要影响以下几个方面,不只是使用SYSAUX表空间的表,对所有碎片较多的表都适用。 1.影响查询速度,因为这样的表本身比较大,索引也会很大,查询会很慢。 2.消耗资源,因为表和索引都很大,查询时会消耗很多I/O资源。 3.空间占用,虽然大部分数据被DELETE掉了,但是这部分空间仍旧是这个段(SEGMENT)的区(EXTENT),即使可以再利用也只能是这个表的新增数据才可以使用,而且还得是所在表空间无法自动扩展或者没有足够的扩展空间的情况下,如果表空间可以自动扩展或者有足够的扩展空间,那么还是不会使用这部分空间的,这就导致表和索引会越来越大,占用的空间越来越大,而一旦将可扩展的空间用尽,那么其他表将无法扩展,DELETE掉的数据空间,其他对象是无法使用的。 除了AWR的保留策略会影响SYSAUX表空间的使用率外,AWR收集数据的级别也对SYSAUX表空间的使用率影响很大,AWR收集数据的级别由statistics_level参数控制,这个参数有三个值,BASIC、TYPICAL、ALL,BASIC表示关闭统计信息收集,TYPICAL表示普通收集级别,只收集够日常用的统计信息,ALL是最给力的,凡是ORACLE能收集的所有信息都要收集,所以生成数据量会很大,相对来说,对性能和占用空间的影响也是最大的,通常TYPICAL就已经够用了。 不同的版本statistics_level参数的默认值不同,有的版本默认值为ALL,有的版本默认值为TYPICAL,具体哪些版本使用ALL为默认值,我记不清了,如果您的数据库设置statistics_level参数的值为ALL,建议调整为TYPICAL。 sys@IVLDB> show parameter statistics_level NAME TYPE VALUE ------------------------------------ ----------- --------- statistics_level string TYPICAL 如果您的数据库也遇到了SYSAUX表空间很大的情况,建议在清理AWR数据时,回收这部分空间,对SYSAUX表空间的对象操作,基本不会影响数据库的正常使用,SYSAUX表空间存放的对象都是数据库运行非必须的对象,技术这个表空间损坏或者丢失,数据库一样可以正常运行。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战