如何修改oracle11g的sys口令,修改Oracle11g初始化配置-口令有效期

数据库用户的密码有效期”PASSWORD_LIFE_TIME“为180天，过了180天应用就启动不了，修改如下

[oracle@zhongwc11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 28 16:02:33 2012

Copyright (c) 1982, 2011, Oracle. 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

SQL> SELECT resource_type,limit FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

RESOURCE LIMIT

-------- ----------------------------------------

PASSWORD 180

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

SQL> SELECT resource_type,limit FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

RESOURCE LIMIT

-------- ----------------------------------------

PASSWORD UNLIMITED

在Oracle11g默认配置下，如果单个帐户连续10次登录失败，系统将会锁定帐户，只能通过对帐户解锁。如下可以设置单个用户连续登录失败的次数为不限制。

[oracle@zhongwc11g ~]$ sqlplus sys/oracle@joy as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 28 16:04:41 2012

Copyright (c) 1982, 2011, Oracle. 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

SQL> select LIMIT from dba_profiles where PROFILE='DEFAULT' and RESOURCE_NAME='FAILED_LOGIN_ATTEMPTS';

LIMIT

----------------------------------------

10

SQL> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

Profile altered.

SQL> select LIMIT from dba_profiles where PROFILE='DEFAULT' and RESOURCE_NAME='FAILED_LOGIN_ATTEMPTS';

LIMIT

----------------------------------------

UNLIMITED

SQL>

在Oracle11g中，创建数据库时默认会打开审计功能，默认的audit会记录session登录数据库的信息、数据库关闭/启动和用户授权等信息。大量审计记录有可能写满系统表空间，导致数据库异常。

若audit_trail的值为DB表示进行审计，并将审计记录存储在数据库中，默认记录在sys.aud$表中。详细的审计记录信息可以查看DBA_AUDIT_TRAIL和DBA_COMMON_AUDIT_TRAIL这两个数据字典视图。

[oracle@zhongwc11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 28 16:08:56 2012

Copyright (c) 1982, 2011, Oracle. 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

SQL> show parameter audit_trail;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

audit_trail string DB

SQL> select action_name, count(*) from dba_audit_trail group by action_name;

ACTION_NAME COUNT(*)

---------------------------- ----------

LOGON 11

LOGOFF 11

SQL> truncate table sys.aud$; --清空审计表

Table truncated.

SQL> alter system set audit_trail=none scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 3340451840 bytes

Fixed Size 2232960 bytes

Variable Size 1811942784 bytes

Database Buffers 1509949440 bytes

Redo Buffers 16326656 bytes

Database mounted.

Database opened.

SQL> show parameter audit_trail;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

audit_trail string NONE

SQL> select action_name, count(*) from dba_audit_trail group by action_name;

no rows selected

SQL>

在Oraclellg中，系统自带回收站。删除的表、视图，都会备存放回收站，时间久了，回收站就会比较乱，对数据库的其他操作可能会造成影响。删除表或视图时，就会永久删除，不存放在回收站。

[oracle@zhongwc11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 28 16:11:35 2012

Copyright (c) 1982, 2011, Oracle. 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

SQL> show parameter recyclebin

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

recyclebin string on

SQL> alter system set recyclebin= off scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 3340451840 bytes

Fixed Size 2232960 bytes

Variable Size 1811942784 bytes

Database Buffers 1509949440 bytes

Redo Buffers 16326656 bytes

Database mounted.

Database opened.

SQL> show parameter recyclebin

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

recyclebin string OFF