从11.1开始密码大小写敏感了，同时可以设置sec_case_sensitive_logon参数可以忽略大小写敏感。
通过朋友咨询的不设置sec_case_sensitive_logon参数让密码大小写不敏感的实验这篇文章疑惑,同时也感谢朋友让我学习到了新知识,我通过测试证明了如下结论：
1.password_versions的优先级大于sec_case_sensitive_logon
2.通过修改USER$.SPARE4为空实现了屏蔽ORACLE 11g密码大小写敏感

10g创建用户导出数据

SQL> create user ora10g identified by xifenfei;
User created.
SQL> grant connect to ora10g;
Grant succeeded.
C:\Documents and Settings\Administrator>expdp \"/ as sysdba \" DIRECTORY=exp_dp
DUMPFILE=chf.dmp schemas=ora10g
Export: Release 10.2.0.1.0 - Production on 星期三, 15 5月, 2013 22:59:45
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
启动 "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" DIRECTORY=exp_dp DUMPF
ILE=chf.dmp schemas=ora10g
正在使用 BLOCKS 方法进行估计...
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 0 KB
处理对象类型 SCHEMA_EXPORT/USER
处理对象类型 SCHEMA_EXPORT/ROLE_GRANT
处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE
处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
已成功加载/卸载了主表 "SYS"."SYS_EXPORT_SCHEMA_01"
******************************************************************************
SYS.SYS_EXPORT_SCHEMA_01 的转储文件集为:
  C:\CHF.DMP
作业 "SYS"."SYS_EXPORT_SCHEMA_01" 已于 23:00:19 成功完成

11g创建用户

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> show parameter logon
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL> create user ora11g identified by xifenfei;
User created.
SQL> grant connect to ora11g;
Grant succeeded.
SQL> conn ora11g/xifenfei
Connected.
SQL> conn ora11g/XIFENFEI
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

这里证明,在sec_case_sensitive_logon=true的情况下,数据库密码是大小写敏感

导入10g创建用户dmp文件

[oracle@localhost ~]$ impdp '"/ as sysdba"' directory=exp_dp dumpfile=CHF.DMP
Import: Release 11.2.0.3.0 - Production on Wed May 15 23:07:20 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=exp_dp dumpfile=CHF.DMP
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 23:07:21

查询ora10g和ora11g用户区别

SQL> conn ora10g/xifenfei
Connected.
SQL> conn ora10g/XIFENFEI
Connected.
SQL> conn / as sysdba
Connected.
SQL>  select t.username,t.account_status,t.password_versions from dba_users t where t.username in ('ORA11G','ORA10G');
USERNAME                       ACCOUNT_STATUS                   PASSWORD
------------------------------ -------------------------------- --------
ORA11G                         OPEN                             10G 11G
ORA10G                         OPEN                             10G
SQL> select name,password,spare4 from SYS.USER$ t where name in ('ORA11G','ORA10G');
NAME                           PASSWORD                       SPARE4
------------------------------ ------------------------------ ----------------------------------------------------------------
ORA10G                         F3CF2F0CB35CB6CA
ORA11G                         559D84354181EB8E               S:BFE2625310D9382E9AEA6EE0AA2988E82C17B3EA23E3DAC23800490C2621

这里可以发现我们从低版本(10g)导入到11g中的用户登录是不区分大小写,而11g本身创建的用户是区分大小写,而他们的区别仅仅是在dba_users.password_versions中有不一样,跟踪到基表发现就是USER$.SPARE4列不一样(10g的该列为空).
猜想:password_versions的优先级大于sec_case_sensitive_logon这个参数

验证猜想
如果是password_versions的优先级大于sec_case_sensitive_logon那么，如果我修改了USER$.SPARE4,使得dba_users.password_versions变成和10g导入的库一样,是否就可以实现不区分密码大小写的问题,如果不缺乏证明：password_versions的优先级大于sec_case_sensitive_logon这个参数,反之失败.

SQL> update SYS.USER$ t set SPARE4='' where name='ORA11G';
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> alter system flush shared_pool;
System altered.
SQL> conn ora11g/XIFENFEI
Connected.
SQL> conn ora11g/xifenfei
Connected.

由此得出两个结论:
1.password_versions的优先级大于sec_case_sensitive_logon
2.通过修改USER$.SPARE4为空实现了屏蔽ORACLE 11g密码大小写敏感

32-Bit Patches :

64-Bit x64 Patches :

64-Bit Itanium Patches :

在某些情况下,我们需要获得数据库用户的创建脚本(包含系统权限,对象权限,配额等相关语句),这些东西如果人工去做绝对是体力活,在asktom网站上看到相关脚本,做了测试和验证,确实很好

---------------------------------------------
-- ###########################################
---------------------------------------------
create or replace procedure GET_CREATE_USER_DDL
 as
cursor get_username is
select username
from dba_users
--where username IN('CHF','XIFENFEI')
;
begin
for l_user in get_username loop
DBMS_OUTPUT.PUT_LINE('-----------------------');
DBMS_OUTPUT.PUT_LINE('select (case');
DBMS_OUTPUT.PUT_LINE('        when ((select count(*)');
DBMS_OUTPUT.PUT_LINE('               from   dba_users');
DBMS_OUTPUT.PUT_LINE('               where  username = '''||l_user.username||''') > 0)');
DBMS_OUTPUT.PUT_LINE('        then  dbms_metadata.get_ddl (''USER'', '''||l_user.username||''')');
DBMS_OUTPUT.PUT_LINE('        else  to_clob (''   -- Note: User not found!'')');
DBMS_OUTPUT.PUT_LINE('        end ) "--Extracted_DDL" from dual');
DBMS_OUTPUT.PUT_LINE('UNION ALL');
DBMS_OUTPUT.PUT_LINE('-----------------------');
DBMS_OUTPUT.PUT_LINE('select (case');
DBMS_OUTPUT.PUT_LINE('        when ((select count(*)');
DBMS_OUTPUT.PUT_LINE('               from   dba_ts_quotas');
DBMS_OUTPUT.PUT_LINE('               where  username = '''||l_user.username||''') > 0)');
DBMS_OUTPUT.PUT_LINE('        then  dbms_metadata.get_granted_ddl (''TABLESPACE_QUOTA'',
'''||l_user.username||''')');
DBMS_OUTPUT.PUT_LINE('        else  to_clob (''   -- Note: No TS Quotas found!'')');
DBMS_OUTPUT.PUT_LINE('        end )  from dual');
DBMS_OUTPUT.PUT_LINE('UNION ALL');
DBMS_OUTPUT.PUT_LINE('-----------------------');
DBMS_OUTPUT.PUT_LINE('select (case');
DBMS_OUTPUT.PUT_LINE('        when ((select count(*)');
DBMS_OUTPUT.PUT_LINE('               from   dba_role_privs');
DBMS_OUTPUT.PUT_LINE('               where  grantee = '''||l_user.username||''') > 0)');
DBMS_OUTPUT.PUT_LINE('        then  dbms_metadata.get_granted_ddl (''ROLE_GRANT'',
'''||l_user.username||''')');
DBMS_OUTPUT.PUT_LINE('        else  to_clob (''   -- Note: No granted roles found!'')');
DBMS_OUTPUT.PUT_LINE('        end ) from dual');
DBMS_OUTPUT.PUT_LINE('UNION ALL');
DBMS_OUTPUT.PUT_LINE('-----------------------');
DBMS_OUTPUT.PUT_LINE('select (case');
DBMS_OUTPUT.PUT_LINE('        when ((select count(*)');
DBMS_OUTPUT.PUT_LINE('               from   dba_sys_privs');
DBMS_OUTPUT.PUT_LINE('               where  grantee = '''||l_user.username||''') > 0)');
DBMS_OUTPUT.PUT_LINE('        then  dbms_metadata.get_granted_ddl (''SYSTEM_GRANT'',
'''||l_user.username||''')');
DBMS_OUTPUT.PUT_LINE('        else  to_clob (''   -- Note: No System Privileges found!'')');
DBMS_OUTPUT.PUT_LINE('        end ) from dual');
DBMS_OUTPUT.PUT_LINE('UNION ALL');
DBMS_OUTPUT.PUT_LINE('-----------------------');
DBMS_OUTPUT.PUT_LINE('select (case');
DBMS_OUTPUT.PUT_LINE('        when ((select count(*)');
DBMS_OUTPUT.PUT_LINE('               from   dba_tab_privs');
DBMS_OUTPUT.PUT_LINE('               where  grantee = '''||l_user.username||''') > 0)');
DBMS_OUTPUT.PUT_LINE('        then  dbms_metadata.get_granted_ddl (''OBJECT_GRANT'',
'''||l_user.username||''')');
DBMS_OUTPUT.PUT_LINE('        else  to_clob (''   -- Note: No Object Privileges found!'')');
DBMS_OUTPUT.PUT_LINE('        end ) from dual');
DBMS_OUTPUT.PUT_LINE('/');
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------');
end loop;
end;
/
---------------------------------------------
--###########################################
---------------------------------------------
The above proc when called with the foll. will give the SQLs for all users:
---------------------------------------------
-- ###########################################
---------------------------------------------
set pages 50000
set serveroutput on size unlimited
spool /tmp/exec_GET_CREATE_USER_DDL.sql
exec GET_CREATE_USER_DDL
spool off
---------------------------------------------
-- ###########################################
---------------------------------------------
These SQLs generated can in turn be run as follows to get the master-list of all the grants in the database:
---------------------------------------------
-- ###########################################
---------------------------------------------
spool /tmp/GET_CREATE_USER_DDL.sql
conn / as sysdba
set long 1000000000
set pages 50000
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
@/tmp/exec_GET_CREATE_USER_DDL.sql
spool off
---------------------------------------------
-- ###########################################
---------------------------------------------

通过这个脚本,我们可以在游标地方限制我们需要获得脚本的用户,而最终得到的/tmp/GET_CREATE_USER_DDL.sql就是我们需要的创建用户的对应脚本.

如果只需要获得一个用户创建相关脚本,只需要执行如下sql即可

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
select (case
        when ((select count(*)
               from   dba_users
               where  username = '&&Username') > 0)
        then  dbms_metadata.get_ddl ('USER', '&&Username')
        else  to_clob ('   -- Note: User not found!')
        end ) Extracted_DDL from dual
UNION ALL
select (case
        when ((select count(*)
               from   dba_ts_quotas
               where  username = '&&Username') > 0)
        then  dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&&Username')
        else  to_clob ('   -- Note: No TS Quotas found!')
        end ) from dual
UNION ALL
select (case
        when ((select count(*)
               from   dba_role_privs
               where  grantee = '&&Username') > 0)
        then  dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&&Username')
        else  to_clob ('   -- Note: No granted Roles found!')
        end ) from dual
UNION ALL
select (case
        when ((select count(*)
               from   dba_sys_privs
               where  grantee = '&&Username') > 0)
        then  dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&&Username')
        else  to_clob ('   -- Note: No System Privileges found!')
        end ) from dual
UNION ALL
select (case
        when ((select count(*)
               from   dba_tab_privs
               where  grantee = '&&Username') > 0)
        then  dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&&Username')
        else  to_clob ('   -- Note: No Object Privileges found!')
        end ) from dual
/

在很多使用,因为各种原因,我们需要定时批量的kill一部分session,用来释放数据库部分资源,这里是因为bug导致temp不能正常释放,也可能是因为bug导致pga不释放,还有可能是因为太多inactive占用资源等等.我这里提供了两种方法来实现该功能
存储过程实现kill session

--创建记录表
CREATE TABLE kill_session_record
(
   kill_time        DATE,
   kill_statement   VARCHAR2 (1000)
)
/
--创建kill session存储过程
CREATE OR REPLACE PROCEDURE kill_inactive_session
IS
   CURSOR c
   IS
      SELECT sid, serial#
        FROM v$session s
       WHERE s.status = 'INACTIVE' AND s.username = 'XIFENFEI';
   k_sid      NUMBER;
   k_serial   NUMBER;
BEGIN
   OPEN c;
   FETCH c
   INTO k_sid, k_serial;
   WHILE c%FOUND
   LOOP
      BEGIN
         EXECUTE IMMEDIATE
               'ALTER SYSTEM DISCONNECT SESSION '''
            || k_sid
            || ','
            || k_serial
            || ''' IMMEDIATE';
         INSERT INTO kill_session_record (kill_time, kill_statement)
              VALUES (
                        SYSDATE,
                           'ALTER SYSTEM DISCONNECT SESSION '''
                        || k_sid
                        || ','
                        || k_serial
                        || ''' IMMEDIATE');
      EXCEPTION
         WHEN OTHERS
         THEN
            INSERT INTO kill_session_record (kill_time, kill_statement)
                 VALUES (
                           SYSDATE,
                              'Failure:ALTER SYSTEM DISCONNECT SESSION '''
                           || k_sid
                           || ','
                           || k_serial
                           || ''' IMMEDIATE');
            COMMIT;
      END;
      FETCH c
      INTO k_sid, k_serial;
   END LOOP;
   COMMIT;
   CLOSE c;
END;
/
--设置job定时运行
DECLARE
   job   NUMBER;
BEGIN
   sys.DBMS_JOB.submit (job,
                        what        => 'kill_inactive_session;',
                        next_date   => SYSDATE,
                        interval    => 'TRUNC(SYSDATE + 1) +7/24');
   COMMIT;
   DBMS_OUTPUT.put_line (job);
END;
/

如果是10GR2之前版本,需要把ALTER SYSTEM DISCONNECT SESSION 换成ALTER SYSTEM KILL SESSION

shell kill session

--shell脚本
# more kill_inactive_session.sh
#!/bin/sh
tmpfile0=/tmp/.kill_inactive_0
tmpfile1=/tmp/.kill_inactive_1
tmpfile2=/tmp/.kill_inactive_2
sqlplus / as sysdba <<EOF
spool $tmpfile1
select 'kill time:'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') execute_time from dual;
select p.spid,s.sid,s.serial# from v\$process p,v\$session s
where s.paddr=p.addr
and username='XIFENFEI'
and s.status='INACTIVE';
spool off
EOF
cat $tmpfile1>>$tmpfile0
grep "^[0123456789]" $tmpfile1 |awk '{print $1}'>$tmpfile2
for x in `cat $tmpfile2`
do
kill -9 $x
done
rm $tmpfile1 $tmpfile2
--contab 调度
00 07 * * * /u01/script/kill_inactive_session.sh

两个脚本都可以在where中加一些限制条件,来实现你需要kill的会话.数据库级别kill相对系统级别来说更加温和点,建议优先考虑数据库级别kill session.如果要求立即释放资源,可能需要考虑系统级别.两中kill方式对于未提交且是inactive session都会被kill掉,然后回滚事务.

需要定时kill非inactive session，一种做法是通过编写脚本or脚本定时运行,从而实现该功能;另外一种方法是通过设置profile中的idle_time来实现该功能,但是这其中有两个细节问题需要注意：1.v$session.status=SNIPED最好做清理，2.未提交事务超时可能强制回滚
使用ORACLE PROFILE准备

SQL> CREATE PROFILE KILLIDLE LIMIT IDLE_TIME 1;
Profile created.
SQL> select * from dba_profiles where profile='KILLIDLE';
PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ------------
KILLIDLE                       COMPOSITE_LIMIT                  KERNEL   DEFAULT
KILLIDLE                       SESSIONS_PER_USER                KERNEL   DEFAULT
KILLIDLE                       CPU_PER_SESSION                  KERNEL   DEFAULT
KILLIDLE                       CPU_PER_CALL                     KERNEL   DEFAULT
KILLIDLE                       LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT
KILLIDLE                       LOGICAL_READS_PER_CALL           KERNEL   DEFAULT
KILLIDLE                       IDLE_TIME                        KERNEL   1
KILLIDLE                       CONNECT_TIME                     KERNEL   DEFAULT
KILLIDLE                       PRIVATE_SGA                      KERNEL   DEFAULT
KILLIDLE                       FAILED_LOGIN_ATTEMPTS            PASSWORD DEFAULT
KILLIDLE                       PASSWORD_LIFE_TIME               PASSWORD DEFAULT
KILLIDLE                       PASSWORD_REUSE_TIME              PASSWORD DEFAULT
KILLIDLE                       PASSWORD_REUSE_MAX               PASSWORD DEFAULT
KILLIDLE                       PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT
KILLIDLE                       PASSWORD_LOCK_TIME               PASSWORD DEFAULT
KILLIDLE                       PASSWORD_GRACE_TIME              PASSWORD DEFAULT
16 rows selected.
SQL> ALTER USER CHF PROFILE KILLIDLE;
User altered.
SQL> SELECT USERNAME,PROFILE FROM DBA_USERS where username='CHF';
USERNAME                       PROFILE
------------------------------ ------------------------------
CHF                            KILLIDLE
SQL> SHOW PARAMETER resource_limit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
resource_limit                       boolean     FALSE
SQL> ALTER SYSTEM SET resource_limit=TRUE;
System altered.

如果要profile生效,需要修改resource_limit=true,IDLE_TIME单位为分钟

测试IDLE_TIME

--session 1
SQL> show user;
USER is "CHF"
SQL> select * from t_xifenfei;
        ID
----------
         1
--删除一条记录
SQL> delete from t_xifenfei;
1 row deleted.
--查询sid
SQL> select sid from v$mystat where rownum=1;
       SID
----------
        20
--开始不操作该会话时间
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2013-02-12 22:30:02
--session 2
SQL> show user;
USER is "SYS"
--查询时间
SQL> select status,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from v$session where sid=20;
STATUS   TO_CHAR(SYSDATE,'YY
-------- -------------------
INACTIVE 2013-02-12 22:31:00
--session 1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again
----已经报会话超时
--session 2
SQL> select status,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from v$session where sid=20;
STATUS   TO_CHAR(SYSDATE,'YY
-------- -------------------
SNIPED   2013-02-12 22:34:40
----会话状态为sniped
--session 1
SQL> conn chf/xifenfei
Connected.
SQL> select * from t_xifenfei;
        ID
----------
         1
----事务回滚

SNIPED – An inactive session that has exceeded some configured limits (for example, resource limits specified for the resource manager consumer group or idle_time specified in the user’s profile). Such sessions will not be allowed to become active again.
因为SNIPED的session只有当该session的终端发一个连接信息给数据库，然后终端才会终止连接，如果该客户端一直不发送类似访问，则该连接一直存在，数据库就很可能因为会话数目超过了数据库参数配置从而出现了ORA-00018错误，业务不能正常运行.出现该问题可以通过如下脚本kill -9 pid解决

kill SNIPED session 脚本

#!/bin/sh
tmpfile=/tmp/.kill_sniped
sqlplus system/manager <<EOF
spool $tmpfile
select p.spid from v\$process p,v\$session s
where s.paddr=p.addr
and s.status='SNIPED';
spool off
EOF
for x in `cat $tmpfile | grep "^[0123456789]"`
do
kill -9 $x
done
rm $tmpfile

另外补充说明，IDLE_TIME是对于空闲时间超过了它的配置时间就会去强制终止会话,如果该会话中存在事务,但是inactive时间超过了IDLE_TIME配置时间，数据库依然会强制终止会话，并且回滚事务

为什么不建议客户把业务数据存放到SYSTEM表空间中,一直想通过试验的数据来说明问题,今天见老熊的邮件和同事的blog(为什么不要把用户表存储到SYSTEM表空间)来说明把业务数据存放在SYSTEM表空间中效率的影响
数据库版本

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> conn chf/xifenfei
Connected.
SQL> create table t_xifenfei_u(id number) tablespace users;
Table created.
SQL> create table t_xifenfei_s(id number) tablespace system;
Table created.
SQL>  select table_name,tablespace_name from user_tables;
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T_XIFENFEI_U                   USERS
T_XIFENFEI_S                   SYSTEM

非系统表空间测试

SQL> select STATISTIC#,NAME from v$statname where name='CPU used by this session';
STATISTIC# NAME
---------- ----------------------------------------------------------------
        17 CPU used by this session
SQL> select * from v$mystat where STATISTIC#=17;
       SID STATISTIC#      VALUE
---------- ---------- ----------
       189         17         33
SQL> set timing on
SQL> begin
  2  for i in 1..200000 loop
  3  insert into t_xifenfei_u values(i);
  4  end loop;
  5  end;
  6  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.97
SQL> select * from v$mystat where STATISTIC#=17;
       SID STATISTIC#      VALUE
---------- ---------- ----------
       189         17        629
Elapsed: 00:00:00.00

测试结果显示，非系统表空间中的表插入200000条记录，使用时间为5.97秒；使用CPU为629-33=596

系统表空间测试

SQL> begin
  2  for i in 1..200000 loop
  3  insert into t_xifenfei_s values(i);
  4  end loop;
  5  end;
  6  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:14.00
SQL> select * from v$mystat where STATISTIC#=17;
       SID STATISTIC#      VALUE
---------- ---------- ----------
       189         17       2019
Elapsed: 00:00:00.00

测试结果显示，对系统表空间中的表插入200000条记录，使用时间为14秒；使用CPU为2019-629=1390，基本上可以看出来无论是CPU消耗还是执行时间上，系统表空间占用都是非系统表空间两倍以上

分析原因

SQL> conn / as sysdba
Connected.
SQL> select * from (SELECT i.ksppinm NAME, i.ksppity TYPE, v.ksppstvl VALUE,
  2  v.ksppstdf isdefault FROM x$ksppi i, x$ksppcv v WHERE i.indx = v.indx AND
  3  i.ksppinm LIKE '/_%%' ESCAPE '/') where name like '%db_alw%';
NAME                                 TYPE VALUE           ISDEFAULT
------------------------------ ---------- --------------- ---------
_db_always_check_system_ts              1 TRUE            TRUE
SQL> alter system set "_db_always_check_system_ts"=false;
System altered.
SQL> conn chf/xifenfei
Connected.
SQL>  select * from v$mystat where STATISTIC#=17;
       SID STATISTIC#      VALUE
---------- ---------- ----------
       127         17          1
Elapsed: 00:00:00.01
SQL> begin
  2  for i in 1..200000 loop
  3  insert into t_xifenfei_s values(i);
  4  end loop;
  5  end;
  6  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.03
SQL> select * from v$mystat where STATISTIC#=17;
       SID STATISTIC#      VALUE
---------- ---------- ----------
       127         17        582

通过这里可以发现,修改_db_always_check_system_ts=false之后,system表空间的操作基本上和非系统表空间所差无几(执行时间6.01秒,占用CPU 581=582-1)
在数据库默认情况下db_block_checking和db_block_checksum的值无论如何设置都不能对于SYSTEM表空间生效，也就是说SYSTEM表空间在没有修改_db_always_check_system_ts=false之前，对所有的块操作都要进行db_block_checking和db_block_checksum验证,从而使得数据块的操作效率较非SYSTEM表空间低下。对于一些插入较为频繁的aud$、FGA_LOG$、DEF$_AQCALL等表建议迁移到其他表空间

备注说明
DB_BLOCK_CHECKING

DB_BLOCK_CHECKING specifies whether or not Oracle performs block checking for database blocks.
Values:
OFF or FALSE
No block checking is performed for blocks in user tablespaces. However,
semantic block checking for SYSTEM tablespace blocks is always turned on.
LOW
Basic block header checks are performed after block contents change in memory
(for example, after UPDATE or INSERT statements, on-disk reads, or
inter-instance block transfers in Oracle RAC).
MEDIUM
All LOW checks and full semantic checks are performed for all objects except indexes
(whose contents can be reconstructed by a drop+rebuild on encountering a corruption).
FULL or TRUE
All LOW and MEDIUM checks and full semantic checks are performed for all objects.
Oracle checks a block by going through the data in the block, making sure it is logically
self-consistent. Block checking can often prevent memory and data corruption. Block checking
typically causes 1% to 10% overhead,depending on workload and the parameter value.
The more updates or inserts in a workload, the more expensive it is to turn on block checking.
You should set DB_BLOCK_CHECKING to FULL if the performance overhead is acceptable.
For backward compatibility, the use of FALSE (implying OFF) and TRUE (implying FULL) is preserved.

DB_BLOCK_CHECKSUM

DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a checksum
(a number calculated from all the bytes stored in the block) and store it in the cache header
of every data block when writing it to disk. Checksums are verified when a block is read -
only if this parameter is TYPICAL or FULL and the last write of the block stored a checksum.
In FULL mode, Oracle also verifies the checksum before a change application from update/delete
statements and recomputes it after the change is applied. In addition, Oracle gives every log block
a checksum before writing it to the current log.
Starting with Oracle Database 11g, most of the log block checksum is done by the generating foreground
processes, while the LGWR performs the rest of the work, for better CPU and cache efficiency.
Prior to Oracle Database 11g, the LGWR solely performed the log block checksum.
If this parameter is set to OFF, DBWn calculates checksums only for the SYSTEM tablespace,
but not for user tablespaces. In addition, no log checksum is performed when this parameter is set to OFF.
Checksums allow Oracle to detect corruption caused by underlying disks, storage systems, or I/O systems.
If set to FULL, DB_BLOCK_CHECKSUM also catches in-memory corruptions and stops them from making it to the disk.
Turning on this feature in TYPICAL mode causes only an additional 1% to 2% overhead. In the FULL mode it causes
4% to 5% overhead. Oracle recommends that you set DB_BLOCK_CHECKSUM to TYPICAL.
For backward compatibility the use of TRUE (implying TYPICAL) and FALSE (implying OFF) values is preserved.

在一次测试中,需要模拟在归档模式下,数据库发生多次redo切换而这些redo并未被归档的情景,一般来说这样的情况只有在归档目录满的时候会遇到.但是在日常测试中,这样的归档目录满的模拟不太现实,可以通过oradebug SUSPEND来实现该功能,让arcn进程挂起
配置log_archive_max_processes为1(可以配置多个，但是1个更加方便测试)

SQL> show parameter log_archive_max_processes;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes            integer     1

该配置可以在线修改,但是不重启数据库不一定完全生效(测试环境本来是4,修改为1之后,还有arc0和arc1进程)

查找arcn进程

[oracle@localhost trace]$ ps -ef|grep ora_arc
oracle    3686     1  0 21:07 ?        00:00:00 ora_arc0_test

oradebug进程(session 1)

SQL> oradebug  setospid  3686
Oracle pid: 57, Unix process pid: 3686, image: oracle@localhost.localdomain (ARC0)
SQL> oradebug SUSPEND
Statement processed.

alert日志

Tue Apr 16 21:09:42 2013
Unix process pid: 3686, image: oracle@localhost.localdomain (ARC0) flash frozen [ command #1 ]

切换日志(session 2)

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     53
Next log sequence to archive   55
Current log sequence           55
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> alter system checkpoint;
System altered.
SQL> set lines 134
SQL> col member for a40
SQL>   SELECT thread#,
  2           a.sequence#,a.ARCHIVED,
  3           a.group#,
  4           TO_CHAR (first_change#, '9999999999999999') "SCN",
  5           a.status,
  6           MEMBER
  7      FROM v$log a, v$logfile b
  8     WHERE a.group# = B.GROUP#
  9  ORDER BY a.sequence# DESC;
   THREAD#  SEQUENCE# ARC     GROUP# SCN               STATUS           MEMBER
---------- ---------- --- ---------- ----------------- ---------------- ----------------------------
         1         57 NO           3            261053 CURRENT          /data/oracle/oradata/test/redo03.log
         1         56 NO           2            261046 INACTIVE         /data/oracle/oradata/test/redo02.log
         1         55 NO           1            260856 INACTIVE         /data/oracle/oradata/test/redo01.log
SQL> alter system switch logfile;--hang住

此时alert日志

Tue Apr 16 21:10:19 2013
Thread 1 advanced to log sequence 56 (LGWR switch)
  Current log# 2 seq# 56 mem# 0: /data/oracle/oradata/test/redo02.log
Tue Apr 16 21:10:36 2013
Thread 1 advanced to log sequence 57 (LGWR switch)
  Current log# 3 seq# 57 mem# 0: /data/oracle/oradata/test/redo03.log
Tue Apr 16 21:13:13 2013
ORACLE Instance test - Can not allocate log, archival required
Thread 1 cannot allocate new log, sequence 58
All online logs needed archiving
  Current log# 3 seq# 57 mem# 0: /data/oracle/oradata/test/redo03.log

oradebug RESUME(session 1)

SQL> oradebug RESUME
Statement processed.

alert日志

Tue Apr 16 21:14:23 2013
Unix process pid: 3686, image: oracle@localhost.localdomain (ARC0) resumed
Archived Log entry 2 added for thread 1 sequence 55 ID 0x7dd4ccb7 dest 1:
Archived Log entry 3 added for thread 1 sequence 56 ID 0x7dd4ccb7 dest 1:

hang住会话继续执行(session 2)

SQL> alter system switch logfile;
System altered.