在日常的数据库维护中，经常出现因为数据库登录审计的功能启动,导致system表空间被用满.从而出现异常,一般建议把aud$相关对象迁移到其他表空间,从而避免system被用完的风险.
人工move aud$相关对象

alter table AUDIT$ move tablespace users;
alter table AUDIT_ACTIONS move tablespace users;
alter table AUD$ move tablespace users;
alter table AUD$ move lob(SQLBIND) store as SYS_IL0000000384C00041$$ (tablespace users);
alter table AUD$ move lob(SQLTEXT) store as SYS_IL0000000384C00041$$ (tablespace users);
alter index I_AUDIT rebuild online tablespace users;
alter index I_AUDIT_ACTIONS rebuild online tablespace users;
--可能修改值(index和lob index)
SQL> select COLUMN_NAME,index_name from dba_lobs where owner='SYS' and table_name='AUD$';
COLUMN_NAME                              INDEX_NAME
---------------------------------------- ------------------------------
SQLBIND                                  SYS_IL0000000384C00040$$
SQLTEXT                                  SYS_IL0000000384C00041$$
SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUD$';
INDEX_NAME
------------------------------
SYS_IL0000000384C00040$$
SYS_IL0000000384C00041$$
SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUDIT$';
INDEX_NAME
------------------------------
I_AUDIT
SQL>  SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUDIT_ACTIONS';
INDEX_NAME
------------------------------
I_AUDIT_ACTIONS

DBMS_AUDIT_MGMT实现迁移

conn / as sysdba
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
audit_trail_location_value => 'USERS');
END;
/

验证DBMS_AUDIT_MGMT效果

SQL> select segment_name,tablespace_name from dba_segments where
  2  segment_name in('AUD$','SYS_IL0000000384C00040$$','SYS_IL0000000384C00041$$',
  3  'AUDIT$','I_AUDIT','AUDIT_ACTIONS','I_AUDIT_ACTIONS');
SEGMENT_NAME                                                                      TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
AUDIT_ACTIONS                                                                     SYSTEM
AUDIT$                                                                            SYSTEM
AUD$                                                                              SYSTEM
SYS_IL0000000384C00041$$                                                          SYSTEM
SYS_IL0000000384C00040$$                                                          SYSTEM
I_AUDIT_ACTIONS                                                                   SYSTEM
I_AUDIT                                                                           SYSTEM
SQL> BEGIN
  2  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
  3  audit_trail_location_value => 'USERS');
  4  END;
  5  /
PL/SQL procedure successfully completed.
SQL> select segment_name,tablespace_name from dba_segments where
  2   segment_name in('AUD$','SYS_IL0000000384C00040$$','SYS_IL0000000384C00041$$',
  3   'AUDIT$','I_AUDIT','AUDIT_ACTIONS','I_AUDIT_ACTIONS');
SEGMENT_NAME                                                                      TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
AUDIT_ACTIONS                                                                     SYSTEM
AUDIT$                                                                            SYSTEM
AUD$                                                                              USERS
SYS_IL0000000384C00041$$                                                          USERS
SYS_IL0000000384C00040$$                                                          USERS
I_AUDIT_ACTIONS                                                                   SYSTEM
I_AUDIT                                                                           SYSTEM

通过试验证明DBMS_AUDIT_MGMT就是迁移了AUD$表中相关对象,对于和审计相关的其他几个对象并未迁移到其他表空间

aud$相关说明
1.DBMS_AUDIT_MGMT版本支持情况

It is still not supported (but it works) to use it on 10.2.0.4 and 11.1.0.7 for non-Audit Vault Environment.
Using RDBMS with Audit Vault, it is supported  for 10.2.0.4.0 and 11.1.0.7.0
as the DBMS_AUDIT_MGMT Package is provided to be used with an Audit Vault Environment.

2.该包可以实现在线迁移，特别是在高业务的系统中,可以实现在线迁移,而人工的move操作不能实现在线处理
3.对于AUD$对象，如果登录审计数据不是非常重要,可以通过truncate来解决一时的问题，在业务高的系统，可能truncate不能马上操作成功，可以尝试使用11gr2的新特性alter session set ddl_lock_timeout = 10;来实现自动ddl尝试
4.如果确定不需要登录审计功能，可以通过设置audit_trail=none来关闭(需要重启实例)

今天网友问到了v$sgainfo中的Free SGA Memory Available的一些情况,开始我也比较迷糊,为什么会出现Free SGA Memory Available的值不为0,通过查询一些试验和测试,对这个问题进行了有力的说明
数据库版本10G

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

当前SGA各组件信息

SQL> select name,bytes/1024/1024 from v$sgainfo;
NAME                             BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size                        1.20853043
Redo Buffers                           6.7890625
Buffer Cache Size                            192
Shared Pool Size                              88
Large Pool Size                                4
Java Pool Size                                 4
Streams Pool Size                              8
Granule Size                                   4
Maximum SGA Size                             304
Startup overhead in Shared Pool               36
Free SGA Memory Available                      0
11 rows selected.

sga配置

SQL> show parameter sga;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 304M
sga_target                           big integer 304M

在10g中，当采用asmm管理时,如果sga_max_size=sga_target,则Free SGA Memory Available为0

修改sga_target

SQL> alter system set sga_target=290M;
System altered.

再次查询v$sgainfo

SQL> select name,bytes/1024/1024 from v$sgainfo;
NAME                             BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size                        1.20853043
Redo Buffers                           6.7890625
Buffer Cache Size                            180
Shared Pool Size                              88
Large Pool Size                                4
Java Pool Size                                 4
Streams Pool Size                              8
Granule Size                                   4
Maximum SGA Size                             304
Startup overhead in Shared Pool               36
Free SGA Memory Available                     12
11 rows selected.

再次查看sga_target值

SQL> show parameter sga;
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 304M
sga_target                           big integer 292M

在10g中，当采用asmm管理时,如果sga_max_size>sga_target,则Free SGA Memory Available为sga_max_size-sga_target

找出sga_target修改为292M而不是290M原因

SQL> select component, granule_size from v$sga_dynamic_components;
COMPONENT                                                        GRANULE_SIZE
---------------------------------------------------------------- ------------
shared pool                                                           4194304
large pool                                                            4194304
java pool                                                             4194304
streams pool                                                          4194304
DEFAULT buffer cache                                                  4194304
KEEP buffer cache                                                     4194304
RECYCLE buffer cache                                                  4194304
DEFAULT 2K buffer cache                                               4194304
DEFAULT 4K buffer cache                                               4194304
DEFAULT 8K buffer cache                                               4194304
DEFAULT 16K buffer cache                                              4194304
DEFAULT 32K buffer cache                                              4194304
ASM Buffer Cache                                                      4194304
13 rows selected.
SQL> select 4194304/1024/1024 from dual;
4194304/1024/1024
-----------------
                4
SQL> select 292/4 from dual;
     292/4
----------
        73

因为sga的内存分配是按照GRANULE为单位进行的,而在该库中sga对应组件的GRANULE为4M,所以我们修改的290M的最近的GRANULE整数倍为292M

sga_target为0的情况

SQL> show parameter sga_target;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
sga_target                           big integer 0
SQL> select name,bytes/1024/1024 from v$sgainfo;
NAME                             BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size                        1.20846176
Redo Buffers                           6.7890625
Buffer Cache Size                            180
Shared Pool Size                              88
Large Pool Size                                4
Java Pool Size                                 4
Streams Pool Size                              8
Granule Size                                   4
Maximum SGA Size                             292
Startup overhead in Shared Pool               36
Free SGA Memory Available                      0
11 rows selected.

在10g中，如果不采用asmm内存管理模式,Free SGA Memory Available为0

11g数据库版本

SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 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

查询v$sgainfo信息

SQL> select name,bytes/1024/1024 from v$sgainfo;
NAME                             BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size                        1.28236008
Redo Buffers                          6.03515625
Buffer Cache Size                             20
Shared Pool Size                             116
Large Pool Size                                4
Java Pool Size                                 4
Streams Pool Size                              8
Shared IO Pool Size                            0
Granule Size                                   4
Maximum SGA Size                      299.320313
Startup overhead in Shared Pool       52.2684898
Free SGA Memory Available                    140

oracle内存相关参数设置

SQL> show parameter memory;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 300M
memory_target                        big integer 300M
shared_memory_address                integer     0
SQL> show parameter pga;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
pga_aggregate_target                 big integer 0

数据库动态组件内存分配

SQL> select COMPONENT,CURRENT_SIZE/1024/1024 CURRENT_SIZE from V$MEMORY_DYNAMIC_COMPONENTS;
COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool                                                               116
large pool                                                                  4
java pool                                                                   4
streams pool                                                                8
SGA Target                                                                160
DEFAULT buffer cache                                                       20
KEEP buffer cache                                                           0
RECYCLE buffer cache                                                        0
DEFAULT 2K buffer cache                                                     0
DEFAULT 4K buffer cache                                                     0
DEFAULT 8K buffer cache                                                     0
DEFAULT 16K buffer cache                                                    0
DEFAULT 32K buffer cache                                                    0
Shared IO Pool                                                              0
PGA Target                                                                140
ASM Buffer Cache                                                            0
16 rows selected.

初步怀疑:在11g的amm内存管理模式下Free SGA Memory Available和PGA Target相等

尝试修改pga_aggregate_target值

SQL> alter system set pga_aggregate_target=150M;
System altered.

再次查询v$sgainfo

SQL>  select name,bytes/1024/1024 from v$sgainfo;
NAME                             BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size                        1.28236008
Redo Buffers                          6.03515625
Buffer Cache Size                              8
Shared Pool Size                             116
Large Pool Size                                4
Java Pool Size                                 4
Streams Pool Size                              8
Shared IO Pool Size                            0
Granule Size                                   4
Maximum SGA Size                      299.320313
Startup overhead in Shared Pool       52.2684898
Free SGA Memory Available                    152

查询动态组件内存分布

SQL> select COMPONENT,CURRENT_SIZE/1024/1024 CURRENT_SIZE from V$MEMORY_DYNAMIC_COMPONENTS;
COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool                                                               116
large pool                                                                  4
java pool                                                                   4
streams pool                                                                8
SGA Target                                                                148
DEFAULT buffer cache                                                        8
KEEP buffer cache                                                           0
RECYCLE buffer cache                                                        0
DEFAULT 2K buffer cache                                                     0
DEFAULT 4K buffer cache                                                     0
DEFAULT 8K buffer cache                                                     0
DEFAULT 16K buffer cache                                                    0
DEFAULT 32K buffer cache                                                    0
Shared IO Pool                                                              0
PGA Target                                                                152
ASM Buffer Cache                                                            0
16 rows selected.

进一步证明:在11g的amm内存管理模式下Free SGA Memory Available和PGA Target相等

设置pga_aggregate_target为150M,PGA Target为152M原因分析

SQL> select COMPONENT,GRANULE_SIZE from V$MEMORY_DYNAMIC_COMPONENTS;
COMPONENT                                                        GRANULE_SIZE
---------------------------------------------------------------- ------------
shared pool                                                           4194304
large pool                                                            4194304
java pool                                                             4194304
streams pool                                                          4194304
SGA Target                                                            4194304
DEFAULT buffer cache                                                  4194304
KEEP buffer cache                                                     4194304
RECYCLE buffer cache                                                  4194304
DEFAULT 2K buffer cache                                               4194304
DEFAULT 4K buffer cache                                               4194304
DEFAULT 8K buffer cache                                               4194304
DEFAULT 16K buffer cache                                              4194304
DEFAULT 32K buffer cache                                              4194304
Shared IO Pool                                                        4194304
PGA Target                                                            4194304
ASM Buffer Cache                                                      4194304
16 rows selected.

原理同上述10g中的sga_target分析，不再重复,主要就是:150M不能被4M整除，所以取最近的152M

整体总结
1.如果不采用asmm和amm,Free SGA Memory Available为0
2.如果采用asmm,当sga_max_size=sga_target,则Free SGA Memory Available为0
3.如果采用asmm,当sga_max_size>sga_target,则Free SGA Memory Available为sga_max_size-sga_target
4.如果采用amm,Free SGA Memory Available和PGA Target相等