问题
Oracle数据库用户被锁了，如何查看什么原因导致被锁的呢
结论
1,dba_users是由底层表user$,profile$,profname$相关表构成，当然还有ts$
2,dba_users与账户锁定或过期相关的列有：created,account_status,lock_date,expiry_date,profile
3,具体如何分析到底是dba_profiles中的哪个参数为用户锁定的原因，要结合上述这些列的数据，以及dba_profiles相关记录的含义
进行一一排除
4,查找profile的含义方法为：在oracle sql rererence找create profile即可
或者从administrator guide找resource plan,也可以导航到create profile即可
5,profile说白了，就是控制资源如何使用的，具体细节大家可参考官方手册
测试
--oracle version
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
--用户字典表
SQL> desc dba_users;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE --账户锁定时间
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(8)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
SQL> create user test_user identified by system account unlock;
User created.
可见默认创建用户使用default profile
SQL> select username,password,account_status,lock_date,profile from dba_users where lower(username)='test_user';
USERNAME PASSWORD ACCOUNT_ST LOCK_DATE PROFILE
-------------------- ------------------------------ ---------- ------------ ------------------------------------------------------------
TEST_USER OPEN DEFAULT
看看default profile
关于profile的含义，自己可以查查官方手册即知
SQL> select profile,resource_name,resource_type,limit from dba_profiles where profile='DEFAULT';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
------------------------------ -------------------------------------------------- ---------------- --------------------------------------------------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 --注意下与password相关的记录
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180