相信很多使用plsql dev的朋友多遇到过类此如下面的提示：

Dynamic Performance Tables not accessible,
Automatic Statistics Disabled for this session
You can disable statistics in the preference menu,or obtanin select
priviliges on the v$session,v$sesstat and v$statname tables 

一、产生该提示原因
plsql dev在用户运行过程中，要收集用户统计信息，但是由于你现在登录的用户没有访问v$session,v$sesstat and v$statname视图的权限，所以不能收集当前用户的统计信息，和plsql dev工具中配置的Automatic Statistics相冲突，所以就出现了这个提示，试验验证：

[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 10 04:31:57 2011
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
sys@XFF>create user chf identified by xifenfei;
User created.
sys@XFF>grant create session,resource to chf;
Grant succeeded.
sys@XFF>conn chf/xifenfei
Connected.
chf@XFF>select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
10 rows selected.
chf@XFF>SELECT TABLE_NAME FROM USER_TAB_PRIVS;
no rows selected

创建一个chf用户，授权create session,resource，无v$session,v$sesstat and v$statname视图访问权限，使用plsql dev登录并查询user_tables表(登录时不会提示，只有用户执行了查询或者相关类此操作时候才会提示)

二、解决问题
根据警告提示，可以有两种方法解决这种警告
1、关闭plsql dev统计功能
在 Tools->Preferences->Options里 把Automatic Statistics前的那个勾子去掉，保存
2、给访问用户授权访问相关视图
授权访问v_$session,v_$sesstat,v_$statname,注意不能直接对v$视图进行授权

chf@XFF>conn / as sysdba
Connected.
sys@XFF>grant select on v_$session to chf;
Grant succeeded.
sys@XFF>grant select on v_$sesstat to chf;
Grant succeeded.
sys@XFF>grant select on v_$statname to chf;
Grant succeeded.
sys@XFF>conn chf/xifenfei
Connected.
chf@XFF>SELECT TABLE_NAME FROM USER_TAB_PRIVS;
TABLE_NAME
------------------------------
V_$SESSION
V_$SESSTAT
V_$STATNAME

三、问题分析
通过上面的解决方法，为什么授权访问v$session,v$sesstat and v$statname视图就可以Statistics用户的信息了呢？请见下面的两张图
图1：通过plsql dev中的tools–>session选项看用户统计信息

图2：通过sql语句查询用户统计信息

通过两张图的比较可能会发现，他们的数值有一点点出入，那是因为我先通过tools查询出用户统计信息，再通过sql查询，所以图1中的数据有些选项会比图2小那么一点点，通过对v$session,v$sesstat and v$statname视图分析，发现其实plsql dev就是通过下面sql实现统计功能，也从而进一步说明了，为什么plsql dev收集统计信息需要对v$session,v$sesstat and v$statname视图授于访问权限

SELECT C.NAME, B.STATISTIC#, B.VALUE
  FROM V$SESSION A, V$SESSTAT B, V$STATNAME C
 WHERE A.SID = B.SID
   AND A.AUDSID = USERENV('SESSIONID')
   AND B.STATISTIC# = C.STATISTIC#
   ORDER BY C.STATISTIC#;

1、dba_registry视图

SQL> set line 200
SQL> col comp_name for a35
SQL> col version for a12
SQL> col status for a6
SQL> select comp_name, version, status from dba_registry;
COMP_NAME                           VERSION      STATUS
----------------------------------- ------------ ------
Spatial                             10.2.0.5.0   VALID
Oracle interMedia                   10.2.0.5.0   VALID
OLAP Catalog                        10.2.0.5.0   VALID
Oracle Enterprise Manager           10.2.0.5.0   VALID
Oracle XML Database                 10.2.0.5.0   VALID
Oracle Text                         10.2.0.5.0   VALID
Oracle Expression Filter            10.2.0.5.0   VALID
Oracle Rule Manager                 10.2.0.5.0   VALID
Oracle Workspace Manager            10.2.0.5.0   VALID
Oracle Data Mining                  10.2.0.5.0   VALID
Oracle Database Catalog Views       10.2.0.5.0   VALID
Oracle Database Packages and Types  10.2.0.5.0   VALID
JServer JAVA Virtual Machine        10.2.0.5.0   VALID
Oracle XDK                          10.2.0.5.0   VALID
Oracle Database Java Packages       10.2.0.5.0   VALID
OLAP Analytic Workspace             10.2.0.5.0   VALID
Oracle OLAP API                     10.2.0.5.0   VALID
17 rows selected.
SQL> select dbms_metadata.get_ddl('VIEW','DBA_REGISTRY','SYS') FROM DUAL;
DBMS_METADATA.GET_DDL('VIEW','DBA_REGISTRY','SYS')
--------------------------------------------------------------------------------
  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_REGISTRY" ("COMP_ID", "COMP_NAME", "VERSION", "STATUS", "MODIFIED", "NAMESPAC
E", "CONTROL", "SCHEMA", "PROCEDURE", "STARTUP", "PARENT_ID", "OTHER_SCHEMAS") A
S
  SELECT r.cid, r.cname, r.version,
       SUBSTR(dbms_registry.status_name(r.status),1,11),
       TO_CHAR(r.modified,'DD-MON-YYYY HH24:MI:SS'),
       r.namespace, i.name, s.name, r.vproc,
       DECODE(bitand(r.flags,1),1,'REQUIRED',NULL), r.pid,
       dbms_registry.schema_list_string(r.cid)
FROM registry$ r, user$ s, user$ i
WHERE r.schema# = s.user# AND r.invoker#=i.user#
SQL> DESC registry$
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 CID                                                   NOT NULL VARCHAR2(30)
 CNAME                                                          VARCHAR2(255)
 SCHEMA#                                               NOT NULL NUMBER
 INVOKER#                                              NOT NULL NUMBER
 VERSION                                                        VARCHAR2(30)
 STATUS                                                NOT NULL NUMBER
 FLAGS                                                 NOT NULL NUMBER
 MODIFIED                                                       DATE
 PID                                                            VARCHAR2(30)
 BANNER                                                         VARCHAR2(80)
 VPROC                                                          VARCHAR2(61)
 DATE_INVALID                                                   DATE
 DATE_VALID                                                     DATE
 DATE_LOADING                                                   DATE
 DATE_LOADED                                                    DATE
 DATE_UPGRADING                                                 DATE
 DATE_UPGRADED                                                  DATE
 DATE_DOWNGRADING                                               DATE
 DATE_DOWNGRADED                                                DATE
 DATE_REMOVING                                                  DATE
 DATE_REMOVED                                                   DATE
 NAMESPACE                                             NOT NULL VARCHAR2(30)
 ORG_VERSION                                                    VARCHAR2(30)
 PRV_VERSION                                                    VARCHAR2(30)
SQL> SELECT BANNER,VERSION,modified,prv_version FROM SYS.registry$;
BANNER                                                                           VERSION      MODIFIED            PRV_VERSION
-------------------------------------------------------------------------------- ------------ ------------------- ----------------
Oracle Database Catalog Views Release 10.2.0.5.0 - 64bi                          10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Database Packages and Types Release 10.2.0.5.0 - Production               10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Workspace Manager Release 10.2.0.5.0 - Production                         10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.3
JServer JAVA Virtual Machine Release 10.2.0.5.0 - Production                     10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle XDK Release 10.2.0.5.0 - Production                                       10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Database Java Packages Release 10.2.0.5.0 - Production                    10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Expression Filter Release 10.2.0.5.0 - Production                         10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Data Mining Release 10.2.0.5.0 - Production                               10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Text Release 10.2.0.5.0 - Production                                      10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle XML Database Release 10.2.0.5.0 - Production                              10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Rule Manager Release 10.2.0.5.0 - Production                              10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle interMedia Release 10.2.0.5.0 - Production                                10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
OLAP Analytic Workspace Release 10.2.0.5.0 - Production                          10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle OLAP API Release 10.2.0.5.0 - Production                                  10.2.0.5.0   2011-11-03 14:07:35 10.2.0.4.0
OLAP Catalog Release 10.2.0.5.0 - Production                                     10.2.0.5.0   2011-11-03 14:07:35 10.2.0.4.0
Spatial Release 10.2.0.5.0 - Production                                          10.2.0.5.0   2011-11-03 14:07:35 10.2.0.4.0
Oracle Enterprise Manager Release 10.2.0.5.0 - Production                        10.2.0.5.0   2011-11-02 17:23:47 10.2.0.4.0
17 rows selected.

2、v$version或者PRODUCT_COMPONENT_VERSION视图

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> col product for a35
SQL> col product for a40
SQL> select * from PRODUCT_COMPONENT_VERSION;
PRODUCT                                  VERSION      STATUS
---------------------------------------- ------------ --------------
NLSRTL                                   10.2.0.5.0   Production
Oracle Database 10g Enterprise Edition   10.2.0.5.0   64bi
PL/SQL                                   10.2.0.5.0   Production
TNS for Linux:                           10.2.0.5.0   Production
SQL> set long 1000
SQL> set pages 0
SQL> select dbms_metadata.get_ddl('VIEW','PRODUCT_COMPONENT_VERSION','SYS') FROM DUAL;
CREATE OR REPLACE FORCE VIEW "SYS"."PRODUCT_COMPONENT_VERSION" ("PRODUCT", "VERSION", "STATUS") AS
(select
substr(banner,1, instr(banner,'Version')-1),
substr(banner, instr(banner,'Version')+8,
instr(banner,' - ')-(instr(banner,'Version')+8)),
substr(banner,instr(banner,' - ')+3)
from v$version
where instr(banner,'Version') > 0
and
((instr(banner,'Version') <   instr(banner,'Release')) or
instr(banner,'Release') = 0))
union
(select
substr(banner,1, instr(banner,'Release')-1),
substr(banner, instr(banner,'Release')+8,
instr(banner,' - ')-(instr(banner,'Release')+8)),
substr(banner,instr(banner,' - ')+3)
from v$version
where instr(banner,'Release') > 0
and
instr(banner,'Release') <   instr(banner,' - '))
SQL> COL object_name for a20
SQL> SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_objects where object_name='V$VERSION';
OWNER                          OBJECT_NAME          OBJECT_TYPE
------------------------------ -------------------- -------------------
PUBLIC                         V$VERSION            SYNONYM
SQL> SELECT TABLE_OWNER,TABLE_NAME FROM dba_synonyms a WHERE a.synonym_name='V$VERSION';
TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SYS                            V_$VERSION
SQL> SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_objects where object_name='V_$VERSION';
OWNER                          OBJECT_NAME          OBJECT_TYPE
------------------------------ -------------------- -------------------
SYS                            V_$VERSION           VIEW
SQL> select dbms_metadata.get_ddl('VIEW','V_$VERSION','SYS') FROM DUAL;
DBMS_METADATA.GET_DDL('VIEW','V_$VERSION','SYS')
-----------------------------------------------------------------------------
  CREATE OR REPLACE FORCE VIEW "SYS"."V_$VERSION" ("BANNER") AS
  select "BANNER" from v$version
SQL> select * from v$fixed_table where name LIKE '%V%VERSION%';
NAME                            OBJECT_ID TYPE   TABLE_NUM
------------------------------ ---------- ----- ----------
GV$VERSION                     4294951314 VIEW       65537
V$VERSION                      4294951045 VIEW       65537
SQL>  COL VIEW_DEFINITION FOR A80
SQL> select * from v$fixed_view_definition where view_name='V$VERSION';
VIEW_NAME                      VIEW_DEFINITION
------------------------------ ------------------------------------------------------------------
V$VERSION                      select  BANNER from GV$VERSION where inst_id = USERENV('Instance')
SQL> select * from v$fixed_view_definition where view_name='GV$VERSION';
VIEW_NAME                      VIEW_DEFINITION
------------------------------ -------------------------------------------------------
GV$VERSION                     select inst_id, banner from x$version
SQL> DESC x$version
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------
 ADDR                                                           RAW(8)
 INDX                                                           NUMBER
 INST_ID                                                        NUMBER
 BANNER                                                         VARCHAR2(64)
SQL> SET LINE 200
SQL> SELECT * FROM x$version;
ADDR                   INDX    INST_ID BANNER
---------------- ---------- ---------- ----------------------------------------------------------------
00002AB64240D028          0          1 Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
00002AB64240D028          1          1 PL/SQL Release 10.2.0.5.0 - Production
00002AB64240D028          2          1 CORE     10.2.0.5.0      Production
00002AB64240D028          3          1 TNS for Linux: Version 10.2.0.5.0 - Production
00002AB64240D028          4          1 NLSRTL Version 10.2.0.5.0 - Production

3、查看v$instance视图

SQL> select version from v$instance;
VERSION
------------
10.2.0.5.0
--通过同v$version同样操作，得出如下语句
 SELECT KS.INST_ID,
        KSUXSINS,
        KSUXSSID,
        KSUXSHST,
        KSUXSVER,
        KSUXSTIM,
        DECODE(KSUXSSTS,
               0,
               'STARTED',
               1,
               'MOUNTED',
               2,
               'OPEN',
               3,
               'OPEN MIGRATE',
               'UNKNOWN'),
        DECODE(KSUXSSHR, 0, 'NO', 1, 'YES', 2, NULL),
        KSUXSTHR,
        DECODE(KSUXSARC, 0, 'STOPPED', 1, 'STARTED', 'FAILED'),
        DECODE(KSUXSLSW,
               0,
               NULL,
               2,
               'ARCHIVE LOG',
               3,
               'CLEAR LOG',
               4,
               'CHECKPOINT',
               5,
               'REDO GENERATION'),
        DECODE(KSUXSDBA, 0, 'ALLOWED', 'RESTRICTED'),
        DECODE(KSUXSSHP, 0, 'NO', 'YES'),
        DECODE(KVITVAL,
               0,
               'ACTIVE',
               2147483647,
               'SUSPENDED',
               'INSTANCE RECOVERY'),
        DECODE(KSUXSROL,
               1,
               'PRIMARY_INSTANCE',
               2,
               'SECONDARY_INSTANCE',
               'UNKNOWN'),
        DECODE(QUI_STATE,
               0,
               'NORMAL',
               1,
               'QUIESCING',
               2,
               'QUIESCED',
               'UNKNOWN'),
        DECODE(BITAND(KSUXSDST, 1), 0, 'NO', 1, 'YES', 'NO')
   FROM X$KSUXSINST KS, X$KVIT KV, X$QUIESCE QU
  WHERE KVITTAG = 'kcbwst';
SQL> set line 90
SQL> desc X$KSUXSINST;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- --------------
 ADDR                                                           RAW(8)
 INDX                                                           NUMBER
 INST_ID                                                        NUMBER
 KSUXSINS                                                       NUMBER
 KSUXSSID                                                       VARCHAR2(16)
 KSUXSHST                                                       VARCHAR2(64)
 KSUXSVER                                                       VARCHAR2(17)
 KSUXSTIM                                                       DATE
 KSUXSSTS                                                       NUMBER
 KSUXSSHR                                                       NUMBER
 KSUXSTHR                                                       NUMBER
 KSUXSARC                                                       NUMBER
 KSUXSLSW                                                       NUMBER
 KSUXSDBA                                                       NUMBER
 KSUXSSHP                                                       NUMBER
 KSUXSSCN                                                       VARCHAR2(16)
 KSUXSROL                                                       NUMBER
 KSUXSDST                                                       NUMBER
SQL> SELECT  KSUXSVER FROM SYS.X$KSUXSINST;
KSUXSVER
-----------------
10.2.0.5.0

今天中午一朋友问我怎么清空一个用户下面所有的表的comment信息（估计是系统要发布或者买出去，不想让人知道表结构的含义），我当时的感觉就是直接去基表中去修改，这样可以一次性实现，于是就做了下面试验，并给他提供了相关sql语句

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Nov 8 12:17:24 2011
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--我们可以通过DBA_COL_COMMENTS视图（或者同义词）查询到表的comment信息
--那么我们通过这个表找到comment的基表是什么表
SQL> set long 100000
SQL> set pages 0
SQL> SELECT DBMS_METADATA.get_ddl('VIEW','DBA_COL_COMMENTS','SYS') FROM DUAL;
  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_COL_COMMENTS" ("OWNER", "TABLE_NAME",
  select u.name, o.name, c.name, co.comment$
from sys.obj$ o, sys.col$ c, sys.user$ u, sys.com$ co
where o.owner# = u.user#
  and o.type# in (2, 4)
  and o.obj# = c.obj#
  and c.obj# = co.obj#(+)
  and c.intcol# = co.col#(+)
  and bitand(c.property, 32) = 0 /* not hidden column */
--通过上面的语句，我们发现col$是存储commet的基表
SQL> desc sys.com$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#                                      NOT NULL NUMBER
 COL#                                               NUMBER
 COMMENT$                                           VARCHAR2(4000)
--查询CHF用户下面表的commet情况
SQL> col comment$ for a30
SQL> SELECT *
  2    FROM SYS.COM$ A
  3   WHERE EXISTS (SELECT 1
  4            FROM DBA_OBJECTS
  5           WHERE OWNER = 'CHF'
  6             AND OBJECT_TYPE LIKE 'TABLE%'
  7             AND OBJECT_ID = A.OBJ#)
  8     AND COMMENT$ IS NOT NULL;
      OBJ#       COL# COMMENT$
---------- ---------- ------------------------------
     67405          1 xifenfei1
     67405          2 xifenfei2
     67405          3 xifenfei3
     67405          8 惜分飞
     67405         13 chf
     67405         17 xifenfei88
     71926          1 feifei
     71926          2 chf
     71926          3 xff
     70870          1 xifenfei
10 rows selected.
--更新基表的comment$的信息为null
SQL> UPDATE SYS.COM$
  2     SET COMMENT$ = NULL
  3   WHERE EXISTS (SELECT 1
  4            FROM DBA_OBJECTS
  5           WHERE OWNER = 'CHF'
  6             AND OBJECT_TYPE LIKE 'TABLE%'
  7             AND OBJECT_ID = OBJ#)
  8     AND COMMENT$ IS NOT NULL;
10 rows updated.
SQL> commit;
Commit complete.
--验证更新成功，chf下面的所有comment信息都变成了null
SQL> SELECT *
  2    FROM SYS.COM$ A
  3   WHERE EXISTS (SELECT 1
  4            FROM DBA_OBJECTS
  5           WHERE OWNER = 'CHF'
  6             AND OBJECT_TYPE LIKE 'TABLE%'
  7             AND OBJECT_ID = A.OBJ#)
  8     AND COMMENT$ IS NOT NULL;
no rows selected
SQL> SELECT * FROM DBA_COL_COMMENTS WHERE comments IS NOT NULL AND owner='CHF';
no rows selected

一、验证不能通过修改用户的password实现登录不知道密码的用户

[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 7 12:22:46 2011
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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> grant create session to xff identified by xifenfei;
Grant succeeded.
SQL> conn xff/xifenfei
Connected.
SQL> conn / as sysdba
Connected.
SQL> grant create session to chf identified by xifenfei;
Grant succeeded.
SQL> conn chf/xifenfei
Connected.
SQL> conn / as sysdba
Connected.
SQL> desc user$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER#                                     NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(30)
 TYPE#                                     NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 DATATS#                                   NOT NULL NUMBER
 TEMPTS#                                   NOT NULL NUMBER
 CTIME                                     NOT NULL DATE
 PTIME                                              DATE
 EXPTIME                                            DATE
 LTIME                                              DATE
 RESOURCE$                                 NOT NULL NUMBER
 AUDIT$                                             VARCHAR2(38)
 DEFROLE                                   NOT NULL NUMBER
 DEFGRP#                                            NUMBER
 DEFGRP_SEQ#                                        NUMBER
 ASTATUS                                   NOT NULL NUMBER
 LCOUNT                                    NOT NULL NUMBER
 DEFSCHCLASS                                        VARCHAR2(30)
 EXT_USERNAME                                       VARCHAR2(4000)
 SPARE1                                             NUMBER
 SPARE2                                             NUMBER
 SPARE3                                             NUMBER
 SPARE4                                             VARCHAR2(1000)
 SPARE5                                             VARCHAR2(1000)
 SPARE6                                             DATE
SQL> select name,password from user$ where name in('XFF','CHF');
NAME                           PASSWORD
------------------------------ ------------------------------
CHF                            F3CF2F0CB35CB6CA
XFF                            1B60F4BFF1DAB500
SQL> alter user xff identified by values 'F3CF2F0CB35CB6CA';
User altered.
SQL> select name,password from user$ where name in('XFF','CHF');
NAME                           PASSWORD
------------------------------ ------------------------------
CHF                            F3CF2F0CB35CB6CA
XFF                            F3CF2F0CB35CB6CA
SQL> conn xff/xifenfei
ERROR:
ORA-01017: 用户名/口令无效; 登录被拒绝
Warning: You are no longer connected to ORACLE.
SQL> conn chf/xifenfei
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter user xff identified by values '1B60F4BFF1DAB500';
User altered.
SQL> conn xff/xifenfei
Connected.

注：这个实验使用11g证明，其实10g也是同样的结果；在oracle 9i中可以通过修改password的values值实现登录
二、使用orabf破解数据库密码
1、修改数据库密码

SQL> conn / as sysdba
Connected.
SQL> alter user xff identified by xff01;
User altered.
SQL> alter user chf identified by chf00;
User altered.
SQL> select name,password from user$ where name in('XFF','CHF');
NAME                           PASSWORD
------------------------------ ------------------------------
CHF                            05BD6F8AB28BD8CA
XFF                            A51B3879056B3DDD

2、orabf使用

C:\Users\XIFENFEI\Downloads\orabf-v0.7.6>orabf
orabf v0.7.6, (C)2005 orm@toolcrypt.org
---------------------------------------
usage: orabf [hash]:[username] [options]
options:
-c [num]  complexity: a number in [1..6] or a filename
   -      read words from stdin
   [file] read words from file
   1      numbers
   2      alpha
   3      alphanum
   4      standard oracle (alpha)(alpha,num,_,#,$)... (default)
   5      entire keyspace (' '..'~')
   6      custom (charset read from first line of file: charset.orabf)
-m [num]  max pwd len: must be in the interval [1..14] (default: 14)
-n [num]  min pwd len: must be in the interval [1..14] (default: 1)
-r        resume: tries to resume a previous session
C:\Users\XIFENFEI\Downloads\orabf-v0.7.6>orabf A51B3879056B3DDD:XFF
orabf v0.7.6, (C)2005 orm@toolcrypt.org
---------------------------------------
Trying default passwords...done
Starting brute force session using charset:
#$0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_
press 'q' to quit. any other key to see status
current password: D9X50
9229361 passwords tried. elapsed time 00:00:13. t/s:697938
current password: HI0QJ
18967617 passwords tried. elapsed time 00:00:27. t/s:698403
current password: OB#QD
34743632 passwords tried. elapsed time 00:00:49. t/s:698844
password found: XFF:XFF01
55826385 passwords tried. elapsed time 00:01:19. t/s:704047
C:\Users\XIFENFEI\Downloads\orabf-v0.7.6>orabf 05BD6F8AB28BD8CA:CHF -c 3 -n 4 -m 6
orabf v0.7.6, (C)2005 orm@toolcrypt.org
---------------------------------------
Trying default passwords...done
Starting brute force session using charset:
0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
press 'q' to quit. any other key to see status
password found: CHF:CHF00
22647601 passwords tried. elapsed time 00:00:31. t/s:719113

说明：-c 6不能正常运行，不清楚是不是因为我的win 7系统原因导致
三、使用ops_sse2破解数据库密码
1、sys用户的password

SQL> select password from user$ where name='SYS';
PASSWORD
------------------------------
18698BFD1A045BCC

2、ops_sse2使用

C:\Users\XIFENFEI\Downloads\ops_SIMD_win32>ops_sse2
Oracle passwords (DES) solver 0.3 (SSE2) -- Dennis Yurichev <dennis@conus.info>
Compiled @ Apr  5 2011 12:13:15
Demo version, supporting only SYS usernames.
Usage:
  ops_sse2.exe --hashlist=filename.txt
    [--min=min_password_length] [--max=max_password_length]
    [--first_symbol_charset=characters] [--charset=characters]
    [--results=filename.txt]
hashlist file format:
username:hash:comment_or_SID
By default, results are dumped to stdout.
This can be changed by setting --results option
Default values:
  min_password_length=1
  max_password_length=8
  first_symbol_charset=ABCDEFGHIJKLMNOPQRSTUVWXYZ
  charset=ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789#$_
#ops_file.txt内容
SYS:18698BFD1A045BCC:xff
C:\Users\XIFENFEI\Downloads\ops_SIMD_win32>ops_sse2 --hashlist=ops_file.txt --min=6  --charset=CDEFNHITX
Oracle passwords (DES) solver 0.3 (SSE2) -- Dennis Yurichev <dennis@conus.info>
Compiled @ Apr  5 2011 12:13:15
Demo version, supporting only SYS usernames.
username=SYS: 1 unsolved hash(es) left
Checking 6-symbol passwords for username SYS
overall progress=  0%
username=SYS: 1 unsolved hash(es) left
Checking 7-symbol passwords for username SYS
overall progress= 98% / time remaining:
time elapsed: 12s, ~ 1160449 passwords/hashes per second
username=SYS: 1 unsolved hash(es) left
Checking 8-symbol passwords for username SYS
overall progress= 91% / time remaining: 8s
time elapsed: 1m31s, ~ 1248875 passwords/hashes per second
SYS/xff: Found password: XIFENFEI
SYS:XIFENFEI:xff

说明：Demo version只能使用于破解sys用户的密码，而且秘密长度不能超过8.

综合说明的试验，虽然都有缺陷，但是相对而已还是orabf破解更加的给力点
orabf-v0.7.6下载
ops_SIMD_win32
ops_SIMD_linux86
参考：忘记oracle 用户密码怎么办？

一、Disable Oracle Database Vault 

最近一段时间，发现不少pub上不少新手都因为一时大意，添加数据文件名称不规范，然后想重命名该数据文件（或者想删除该数据文件然后重建），处理思路有些不妥，导致一些悲剧的发现，我这里通过实验提供一个自认为比较合理的处理思路：处理思路是数据文件离线重命名

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/oradata/test/archivelog
Oldest online log sequence     210
Next log sequence to archive   212
Current log sequence           212
--确认数据库是归档模式，使得数据库离线后，可以有归档日志恢复到在线状态
SQL> col name for a50
SQL> select name,file# from v$datafile;
NAME                                                    FILE#
-------------------------------------------------- ----------
/opt/oracle/oradata/test/system01.dbf                       1
/opt/oracle/oradata/test/undotbs01.dbf                      2
/opt/oracle/oradata/test/sysaux01.dbf                       3
/opt/oracle/oradata/test/users01.dbf                        4
/opt/oracle/oradata/test/user32g.dbf                        5
/opt/oracle/oradata/test/xifenfei01.dbf                     6
/opt/oracle/oradata/test/user02.dbf                         7
/opt/oracle/oradata/test/odu02.dbf                          8
/opt/oracle/oradata/test/odu01.dbf                          9
/opt/oracle/oradata/test/odu03.dbf                         10
10 rows selected.
SQL> alter tablespace xff add datafile '/opt/oracle/oradata/test/xifenfei02.chf'
   2    size 10m autoextend off;
Tablespace altered.
SQL>  select name,file# from v$datafile;
NAME                                                    FILE#
-------------------------------------------------- ----------
/opt/oracle/oradata/test/system01.dbf                       1
/opt/oracle/oradata/test/undotbs01.dbf                      2
/opt/oracle/oradata/test/sysaux01.dbf                       3
/opt/oracle/oradata/test/users01.dbf                        4
/opt/oracle/oradata/test/user32g.dbf                        5
/opt/oracle/oradata/test/xifenfei01.dbf                     6
/opt/oracle/oradata/test/user02.dbf                         7
/opt/oracle/oradata/test/odu02.dbf                          8
/opt/oracle/oradata/test/odu01.dbf                          9
/opt/oracle/oradata/test/odu03.dbf                         10
/opt/oracle/oradata/test/xifenfei02.chf                    11
11 rows selected.
SQL> create table chf.xff_test tablespace xff
  2  as
  3  select * from dba_objects;
select * from dba_objects
              *
ERROR at line 3:
ORA-01536: space quota exceeded for tablespace 'XFF'
SQL> alter user chf quota 100m on xff;
User altered.
SQL> create table chf.xff_test tablespace xff
  2  as
  3  select * from dba_objects;
Table created.
--需要重命名的数据文件内有数据，模拟数据库在生产环境中工作
SQL> alter database datafile 11 offline drop ;
Database altered.
--数据文件离线处理
SQL> !mv /opt/oracle/oradata/test/xifenfei02.chf /opt/oracle/oradata/test/xifenfei02.dbf
--系统级别把数据文件修改为正确名称
SQL> alter database rename file '/opt/oracle/oradata/test/xifenfei02.chf'
     2  to '/opt/oracle/oradata/test/xifenfei02.dbf';
Database altered.
--修改控制文件中数据文件名称
SQL> recover datafile 11;
Media recovery complete.
SQL> alter database datafile 11 online;
Database altered.
--恢复数据文件，并使其online
SQL>  select name,file# from v$datafile;
NAME                                                    FILE#
-------------------------------------------------- ----------
/opt/oracle/oradata/test/system01.dbf                       1
/opt/oracle/oradata/test/undotbs01.dbf                      2
/opt/oracle/oradata/test/sysaux01.dbf                       3
/opt/oracle/oradata/test/users01.dbf                        4
/opt/oracle/oradata/test/user32g.dbf                        5
/opt/oracle/oradata/test/xifenfei01.dbf                     6
/opt/oracle/oradata/test/user02.dbf                         7
/opt/oracle/oradata/test/odu02.dbf                          8
/opt/oracle/oradata/test/odu01.dbf                          9
/opt/oracle/oradata/test/odu03.dbf                         10
/opt/oracle/oradata/test/xifenfei02.dbf                    11
11 rows selected.

如果数据库满足以下条件，可以删除数据文件，重新添加：
1、The database must be open.
2、If a datafile is not empty, it cannot be dropped. If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.
3、You cannot drop the first or only datafile in a tablespace. This means that DROP DATAFILE cannot be used with a bigfile tablespace.
4、You cannot drop datafiles in a read-only tablespace.
5、You cannot drop datafiles in the SYSTEM tablespace.
6、If a datafile in a locally managed tablespace is offline, it cannot be dropped.
7、db version >= 10g R2

SQL> alter tablespace xff drop datafile 11;
alter tablespace xff drop datafile 11
*
ERROR at line 1:
ORA-03262: the file is non-empty
SQL> alter tablespace xff add datafile '/opt/oracle/oradata/test/xifenfei03.chf' size 10m autoextend off;
Tablespace altered.
SQL> alter tablespace xff drop datafile '/opt/oracle/oradata/test/xifenfei03.chf';
Tablespace altered.

oracle 修改设置密码复杂度渐增方法：
1、修改为常见密码（无特殊字符）
SQL> alter user chf identified by xifenfei;
User altered.
2、修改含一般特殊字符（如：$, %等）
SQL>
SQL> alter user chf identified by “xi%,fenfei”;
User altered.
SQL> conn chf/xi%,fenfei
Connected.
3、修改含”的特殊字符
3.1）修改制定用户密码（sys用户操作）
SQL> password chf
Changing password for chf
New password:
Retype new password:
Password changed
SQL> conn chf/aa””bb
Connected.
3.2）修改当前用户密码（需要有修改密码权限）
SQL> password
Changing password for CHF
Old password:
New password:
Retype new password:
Password changed
SQL>
注：因为一般特殊字符可以使用双引号处理，但是如果密码中含有双引号，就不能用双引号处理，可以直接使用password修改密码

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 17 23:37:51 2011
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter user sys identified by "ab$";
User altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ECP-UC-DB1 ~]$ sqlplus sys/ab$ as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 17 23:38:53 2011
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter name;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      test
db_unique_name                       string      test
global_names                         boolean     FALSE
instance_name                        string      test
lock_name_space                      string
log_file_name_convert                string
service_names                        string      test
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ECP-UC-DB1 ~]$ sqlplus sys/ab$abc as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 17 23:39:05 2011
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ECP-UC-DB1 ~]$ sqlplus sys/ab$@abc as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 17 23:40:06 2011
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter name;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      test
db_unique_name                       string      test
global_names                         boolean     FALSE
instance_name                        string      test
lock_name_space                      string
log_file_name_convert                string
service_names                        string      test
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ECP-UC-DB1 ~]$ sqlplus sys/ab$@abc11 as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 17 23:44:11 2011
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter name;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      test
db_unique_name                       string      test
global_names                         boolean     FALSE
instance_name                        string      test
lock_name_space                      string
log_file_name_convert                string
service_names                        string      test
SQL>  

通过以上sql发现，把sys的密码改为ab$后，无论是什么tns都可以登录数据库，而且都是本地数据库，是不是感觉很诡异，其实你仔细观察发现，密码中有了$，使得$@的操作都变成了无效的，其实就是sqlplus sys/123(随意) as sysdba方式登录本地数据库

[oracle@ECP-UC-DB1 ~]$ sqlplus sys/123 as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 17 23:48:23 2011
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

今天参照oracle官方文档和metalink文档，对Oracle常见后台进程及其功能汇总进行了一次汇总
Oracle常见后台进程及其功能汇总
http://www.xifenfei.com/wp-content/uploads/2011/10/Oracle_Process.pdf

1、为什么要使用Startup Migrate
STARTUP MIGRATE was introduced in 9.2 as a mechanism to be sure that most everything that needs to be done to run an upgrade script or a patch script is done automatically. In the past, customers were expected to adjust certain initialization parameters prior to beginning an upgrade or applying a a patch, but most of this is now done automatically by STARTUP MIGRATE. When a customer starts a database in MIGRATE mode, the following ALTER SYSTEM commands will be set automatically:
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET “_SYSTEM_TRIG_ENABLED”=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 SCOPE=MEMORY;
ALTER SYSTEM SET AQ_TM_PROCESSES=0 SCOPE=MEMORY;
这些我们可以从数据库的Startup Migrate命令启动数据库的日志中可以看出来

Sun Oct  9 21:53:04 2011
ALTER SYSTEM enable restricted session;
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
Autotune of undo retention is turned off.
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;
MMNL started with pid=12, OS id=8452
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
Sun Oct  9 21:53:04 2011
Resource Manager disabled during database migration: plan '' not set
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY;
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN MIGRATE

2、Startup Migrate主用作用
在9i，无论升级/降级 数据库都是startup migrate
10g后增加了upgrade参数，升级可直接用startup upgrade，降级仍是startup migrate
3、Startup Migrate辅助作用（解决部分ORA-00701）

SQL> alter index I_H_OBJ#_COL# rebuild;
alter index I_H_OBJ#_COL# rebuild
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup migrate;
ORACLE instance started.
Total System Global Area 139531744 bytes
Fixed Size 452064 bytes
Variable Size 121634816 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> alter index I_H_OBJ#_COL# rebuild;
Index altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 139531744 bytes
Fixed Size 452064 bytes
Variable Size 121634816 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.