从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配置时间，数据库依然会强制终止会话，并且回滚事务