Linux系统中

[oracle@report ~]$ echo $ORACLE_SID
wlwjj
[oracle@report ~]$ export ORACLE_SID=xifenfei
[oracle@report ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Nov 11 23:36:27 2011
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to an idle instance.
SQL> exit
Disconnected
[oracle@report ~]$  sqlplus abc/abc
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Nov 11 23:42:32 2011
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

Win系统

E:\oracle\11_2_0\NETWORK\ADMIN>ECHO %ORACLE_SID%
xff
E:\oracle\11_2_0\NETWORK\ADMIN>SET ORACLE_SID=xifenfei
E:\oracle\11_2_0\NETWORK\ADMIN>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期五 11月 11 23:31:14 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-12560: TNS: 协议适配器错误
请输入用户名:
E:\oracle\11_2_0\NETWORK\ADMIN>sqlplus chf/xifenfei
SQL*Plus: Release 11.2.0.1.0 Production on 星期五 11月 11 23:34:01 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-12560: TNS: 协议适配器错误
请输入用户名:

相信很多使用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 用户密码怎么办？