两台数据库,一台11.2.0.3.0,一台11.2.0.4.0, 11.2.0.3.0的会自动分区,11.2.0.4.0的不会。情况如下,请高手指点:
目前我只能在11.2.0.4.0的库里面每天执行一下: alter session set "_swrf_test_action" = 72; 来强制oracle进行分区。然后随着分区过期oracle会自动drop过期分区以释放空间。
至于为什么11.2.0.3会自动执行,而11.2.0.4.0不会,就不得而知了,安装的时候我是一样的安装步骤。唯一区别是11.2.0.4.0目前处于归档模式,而11.2.0.3.0没有开启归档。其它都一样。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> select a.table_name,a.partition_name,a.tablespace_name
from dba_tab_partitions a
where a.table_name='WRH$_ACTIVE_SESSION_HISTORY' ;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2482032997_17806 SYSAUX
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2482032997_17830 SYSAUX
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2482032997_17854 SYSAUX
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2482032997_17878 SYSAUX
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2482032997_17902 SYSAUX
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2482032997_17926 SYSAUX
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2482032997_17950 SYSAUX
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2482032997_17974 SYSAUX
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2482032997_18012 SYSAUX
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN SYSAUX
10 rows selected.
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------------------ ------------------------------ ----------
2482032997 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
SQL>
SQL> select * from v$version;
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
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------------------ ------------------------------ ----------
2534326106 +00000 01:00:00.0 +00005 00:00:00.0 DEFAULT
SQL> select a.table_name,a.partition_name,a.tablespace_name
from dba_tab_partitions a
where a.table_name='WRH$_ACTIVE_SESSION_HISTORY' 2 3 ;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2534326106_3547 SYSAUX
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2534326106_3643 SYSAUX
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN SYSAUX
执行下面语句后,才会进行分区,之后随着时间,旧的分区过期被drop,sysaux被释放。
SQL> alter session set "_swrf_test_action" = 72;
Session altered.
SQL> select a.table_name,a.partition_name,a.tablespace_name
from dba_tab_partitions a
where a.table_name='WRH$_ACTIVE_SESSION_HISTORY' 2 3 ;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2534326106_3547 SYSAUX
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2534326106_3643 SYSAUX
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2534326106_3692 SYSAUX
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN SYSAUX
SQL>