1、验证密码文件已经修改

SQL> create user xff01 identified by xifenfei;
User created.
SQL> grant sysdba to xff01;
Grant succeeded.
SQL> !md5sum $ORACLE_HOME/dbs/orapwora11g
04cedb56b62d94fd7e14124619722348  /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g
SQL> !strings /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC
XFF01
D32693095588EF4F
SQL> revoke sysdba from xff01;
Revoke succeeded.
SQL> !strings /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC
XFF01
D32693095588EF4F
SQL> !md5sum $ORACLE_HOME/dbs/orapwora11g
1f6d120acb913a1877cfb0ab57702744  /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g

2、查看基表

SQL> col owner for a20
SQL> col object_name for a30
SQL> SELECT owner,object_name,object_type FROM DBA_objects WHERE object_NAME ='V$PWFILE_USERS';
OWNER                OBJECT_NAME                    OBJECT_TYPE
-------------------- ------------------------------ -------------------
PUBLIC               V$PWFILE_USERS                 SYNONYM
SQL> SELECT table_owner,TABLE_name FROM Dba_Synonyms a WHERE a.synonym_name='V$PWFILE_USERS';
TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SYS                            V_$PWFILE_USERS
SQL> SELECT owner,object_name,object_type FROM dba_objects WHERE object_name='V_$PWFILE_USERS';
OWNER                OBJECT_NAME                    OBJECT_TYPE
-------------------- ------------------------------ -------------------
SYS                  V_$PWFILE_USERS                VIEW
SQL> set long 1000
SQL> set line 200
SQL> SELECT dbms_metadata.get_ddl('VIEW','V_$PWFILE_USERS','SYS') FROM DUAL;
DBMS_METADATA.GET_DDL('VIEW','V_$PWFILE_USERS','SYS')
--------------------------------------------------------------------------------
  CREATE OR REPLACE FORCE VIEW "SYS"."V_$PWFILE_USERS" ("USERNAME", "SYSDBA", "SYSOPER", "SYSASM") AS
  select "USERNAME","SYSDBA","SYSOPER","SYSASM" from v$pwfile_users
SQL> select * from v$fixed_table where name ='V$PWFILE_USERS';
NAME                            OBJECT_ID TYPE   TABLE_NUM
------------------------------ ---------- ----- ----------
V$PWFILE_USERS                 4294951116 VIEW       65537
SQL> select VIEW_DEFINITION from v$fixed_view_definition where view_name='V$PWFILE_USERS';
VIEW_DEFINITION
--------------------------------------------------------------------------------------------------------
select  USERNAME , SYSDBA , SYSOPER, SYSASM from GV$PWFILE_USERS where inst_id = USERENV('Instance')
SQL> select VIEW_DEFINITION from v$fixed_view_definition where view_name='GV$PWFILE_USERS';
VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------
select inst_id,username,decode(sysdba,1,'TRUE','FALSE'),  decode(sysoper,1,'TRUE','FALSE'),
 decode(sysasm,1,'TRUE','FALSE')  from x$kzsrt where valid=1  and username != 'INTERNAL'
SQL> set line 100
SQL> desc x$kzsrt
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ADDR                                                           RAW(8)
 INDX                                                           NUMBER
 INST_ID                                                        NUMBER
 USERNAME                                                       VARCHAR2(30)
 SYSDBA                                                         NUMBER
 SYSOPER                                                        NUMBER
 SYSASM                                                         NUMBER
 VALID                                                          NUMBER
SQL> col username for a10
SQL> select * from x$kzsrt;
ADDR                   INDX    INST_ID USERNAME       SYSDBA    SYSOPER     SYSASM      VALID
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
00002B9563678690          0          1 INTERNAL            1          1          0          1
00002B9563678690          1          1 SYS                 1          1          0          1
00002B9563678690          2          1 XFF01               0          0          0          0

从这里可以看出V$PWFILE_USERS视图的基表是x$kzsrt，这里和我们查看的密码文件一样，这些记录都存在，而是在密码文件中有不被strings显示的字符表示了账号是否启用，x$kzsrt.valid用1和0标示
上篇:V$PWFILE_USERS和密码文件关系

分布式事务，简单来说，是指一个事务在本地和远程执行，本地需要等待确认远程的事务结束后，进行下一步本地的操作。如通过dblink update远程数据库的一行记录，如果在执行过程中网络异常，或者其他事件导致本地数据库无法得知远程数据库的执行情况，此时就会发生in doublt的报错。此时需要dba介入，且需要分多种情况进行处理。分布式事务在commit提交时候，会经历3个阶段：
1.PREPARE PHASE
1.1 决定哪个数据库为commit point site。（注，参数文件中commit_point_strength值高的那个数据库为commit point site）
1.2 全局协调者（Global Coordinator）要求所有的点（除commit point site外）做好commit或者rollback的准备。此时，对分布式事务的表加锁。
1.3 所有分布式事务的节点将它的scn告知全局协调者。
1.4 全局协调者取各个点的最大的scn作为分布式事务的scn。（eygle在这篇文章中也测试过）
至此，所有的点都完成了准备工作，我们开始进入COMMIT PHASE阶段，此时除commit point site点外所有点的事务均为in doubt状态，直到COMMIT PHASE阶段结束。
如果数据库在此阶段出现问题，我们查询（假设远程数据库为commit point site，且本地数据库为Global Coordinator）：
本地：

select local_tran_id,state from dba_2pc_pending；
LOCAL_TRAN_ID          STATE
---------------------- ----------------
2.12.64845              collecting

远程：

select local_tran_id,state from dba_2pc_pending；
no rows selected

即表示本地数据库要求其他点做好commit或者rollback准备，现在正在“收集”其他点的数据库的返回信息，但是远程数据库未知状态（in doubt）。我们需要将本地的Global Coordinator的状态清除掉：

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

或者我们在查询的时候发现是如下的状态：
本地：

select local_tran_id,state from dba_2pc_pending；
LOCAL_TRAN_ID          STATE
---------------------- ----------------
2.12.64845             prepared

远程：

select local_tran_id,state from dba_2pc_pending；
no rows selected

即表示本地Global Coordinator已经做好准备，已经将分布式锁放到各个事务的表上，但是远程数据库的状态再次未知（in doubt），我们需要手工的将本地的transaction rollback掉，并且清除分布式事务信息：
本地：

rollback force 'local_tran_id';
execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

2.COMMIT PHASE
2.1 Global Coordinator将最大scn传到commit point site，要求其commit。
2.2 commit point尝试commit或者rollback。分布式事务锁释放。
2.3 commit point通知Global Coordinator已经commit。
2.4 Global Coordinator通知分布式事务的所有点进行commit。
如果数据库在此阶段出现问题，我们查询
本地：

select local_tran_id,state from dba_2pc_pending；
LOCAL_TRAN_ID          STATE
---------------------- ----------------
2.12.64845             prepared

远程：

select local_tran_id,state from dba_2pc_pending；
LOCAL_TRAN_ID          STATE
---------------------- ----------------
1.92.66874             commited

即远程数据库可能已经commit，但是本地Global Coordinator未知远程数据库的状态，还是处于prepare的状态。我们需要在如下处理：
本地：

commit force 'local_tran_id';
execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

远程：

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

或者我们在查询的时候发现是如下的状态：
本地：

select local_tran_id,state from dba_2pc_pending；
LOCAL_TRAN_ID          STATE
---------------------- ----------------
2.12.64845            commited

远程：

select local_tran_id,state from dba_2pc_pending；
LOCAL_TRAN_ID          STATE
---------------------- ----------------
1.92.66874             commited

即远程数据库和本地数据库均已经完成commit，但是分布式事务的信息尚未清除，我们需要在本地和远程运行：
本地：

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

远程：

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

3.FORGET PHASE
3.1 参与的点通知commit point site他们已经完成commit，commit point site就能忘记（forget）这个事务。
3.2 commit point site在远程数据库上清除分布式事务信息。
3.3 commit point site通知Global Coordinator可以清除本地的分布式事务信息。
3.4 Global Coordinator清除分布式事务信息。
此时如果出现问题，我们查询：
本地：

select local_tran_id,state from dba_2pc_pending；
LOCAL_TRAN_ID          STATE
---------------------- ----------------
2.12.64845            commited

远程：

select local_tran_id,state from dba_2pc_pending；
no rows selected

即远程commit point site已经完成commit，通知Global Coordinator清除本地的分布式事务信息，但是Global Coordinator没有收到该信息。我们需要这样处理：
本地：

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

综上，分布式事务的依次状态为：

phase       local_state    remote_state       action
----------- ---------- ------------------  --------------------------------------------
prepare     collecting       /              本地DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY
            prepared         /              本地rollback force后PURGE_LOST_DB_ENTRY
commit      prepared        commited        本地commit force后本地和远程均PURGE
            commited        commited        本地和远程均PURGE_LOST_DB_ENTRY
forget      commited         /              本地PURGE_LOST_DB_ENTRY

另，当我们遇到使用rollback/commit force的时候，无法正常的清除分布式事务的信息，会报错ORA-02058: no prepared transaction found with ID X.XX.XXXXX时，我们需要通过手工方式来清除该信息。
(注，以下方式修改数据字典，存在风险，使用前请备份好你的数据库)
情况1,在dba_2pc表中还有事务记录，但是实际已经不存在该事务了：

select local_tran_id, state from dba_2pc_pending;
LOCAL_TRAN_ID          STATE
---------------------- ----------------
1.92.66874             prepared

(注:’1.92.66874′的结构为rbs#, slot#, wrap#,此事务在rollback segment #1)
我们再用如下语句找出使用rollback segment #1且状态是active的transaction：

SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
       KTUXESTA Status,
       KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!='INACTIVE'
      AND ktuxeusn= 1; <== 这是rollback segment#，即rbs#
no rows selected

因此我们在rollback force的时候会报错：
ORA-02058: no prepared transaction found with ID 1.92.66874
我们需要如下处理：

set transaction use rollback segment SYSTEM;
delete from sys.pending_trans$
  where local_tran_id = '1.92.66874';
delete from sys.pending_sessions$ where local_tran_id = '1.92.66874';
delete from sys.pending_sub_sessions$ where local_tran_id = '1.92.66874';
commit;

情况2，这种情况比较少见，在dba_2pc表中无法查到分布式事务信息，但是实际上却是存在该分布式事务的：
我们在alertlog中可以看到：
ORA-1591: lock held by in-doubt distributed transaction 1.92.66874
我们查询dba_2pc的表，发现没有分布式事务信息：

select local_tran_id, state from dba_2pc_pending
where local_tran_id='1.92.66874';
no rows selected

但是去查实际的rollback segment信息，却发现有prepared状态的分布式事务存在：

SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
       KTUXESTA Status,
       KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!='INACTIVE'
      AND ktuxeusn= 1;
  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS
---------- ---------- ---------- ---------------- ------------------------
         1         92      66874 PREPARED         SCO|COL|REV|DEAD

我们无法做commit force或者rollback force：

rollback force '1.92.66874';
ORA-02058: no prepared transaction found with ID 1.92.66874

我们用如下的方式手工清理：

alter system disable distributed recovery;
insert into pending_trans$ (
    LOCAL_TRAN_ID,
    GLOBAL_TRAN_FMT,
    GLOBAL_ORACLE_ID,
    STATE,
    STATUS,
    SESSION_VECTOR,
    RECO_VECTOR,
    TYPE#,
    FAIL_TIME,
    RECO_TIME)
values( '1.92.66874', /* <== 此处为你的local tran id */
    306206,                  /*                                         */
    'XXXXXXX.12345.1.2.3',   /*  这些值不必更改，   */
    'prepared','P',          /*  是静态参数，可以直接   */
    hextoraw( '00000001' ),  /*  在这个sql中使用                             */
    hextoraw( '00000000' ),  /*                                         */
    0, sysdate, sysdate );
insert into pending_sessions$
values( '1.92.66874',/* <==此处为你的local tran id  */
    1, hextoraw('05004F003A1500000104'),
    'C', 0, 30258592, '',
    146
  );
commit;
commit force '1.92.66874';

此时如果commit force还是出现报错，需要继续执行：

delete from pending_trans$ where local_tran_id='1.92.66874';
delete from pending_sessions$ where local_tran_id='1.92.66874';
commit;
alter system enable distributed recovery;

此时如果没有报错，则执行以下语句：

alter system enable distributed recovery;
and purge the dummy entry from the dictionary, using
connect / as sysdba
alter session set "_smu_debug_mode" = 4;
--注：如果使用auto的undo管理方式，需要执行此步骤，此步骤能避免在后续执行
--purge_lost_db_entry出现ORA-01453 的报错，详细信息可见Bug 2191458
commit;
exec dbms_transaction.purge_lost_db_entry('1.92.66874');

原文:http://www.dbifan.com/200812/how-to-deal-with-distributed-transaction.html

一、V$PWFILE_USERS定义

V$PWFILE_USERS lists all users in the password file, and indicates whether the user has been granted the SYSDBA, SYSOPER, and SYSASM privileges.
Column	    Datatype	Description
USERNAM    VARCHAR2(30)	Name of the user that is contained in the password file
SYSDBA	   VARCHAR2(5)	Indicates whether the user can connect with SYSDBA privileges (TRUE) or not (FALSE)
SYSOPER	   VARCHAR2(5)	Indicates whether the user can connect with SYSOPER privileges (TRUE) or not (FALSE)
SYSASM	   VARCHAR2(5)	Indicates whether the user can connect with SYSASM privileges (TRUE) or not (FALSE)

二、v$pwfile_users与密码文件关系

[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 4 19:08:06 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> select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
SQL> show parameter instance_name;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
instance_name                        string      ora11g
--查看系统级别查看密码文件内容
SQL> !strings $ORACLE_HOME/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC
--创建新sysdba用户，查看视图和密码文件变化
SQL> create user xff01 identified by xifenfei;
User created.
SQL> grant sysdba to xff01;
Grant succeeded.
SQL>  select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
XFF01                          TRUE  FALSE FALSE
SQL>  !strings $ORACLE_HOME/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC
XFF01
D32693095588EF4F
--删除密码文件
SQL> ! mv $ORACLE_HOME/dbs/orapwora11g $ORACLE_HOME/dbs/orapwora11g_bak
SQL> !ls $ORACLE_HOME/dbs/orapwora11g
ls: /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g: 没有那个文件或目录
--查看视图
SQL>  select * from v$pwfile_users;
no rows selected
SQL>  ! mv $ORACLE_HOME/dbs/orapwora11g_bak $ORACLE_HOME/dbs/orapwora11g
SQL> !ls $ORACLE_HOME/dbs/orapwora11g
/opt/oracle/product/11.2.0/db_1/dbs/orapwora11g
SQL> select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
XFF01                          TRUE  FALSE FALSE
--改变sysdba用户权限，视图内容变化
SQL> grant sysoper to xff01;
Grant succeeded.
SQL>  select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
XFF01                          TRUE  TRUE  FALSE
--密码文件内容无变化
SQL> !strings $ORACLE_HOME/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC
XFF01
D32693095588EF4F
SQL> revoke sysdba from xff01;
Revoke succeeded.
SQL> select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
XFF01                          FALSE TRUE  FALSE
SQL> revoke sysoper  from xff01;
Revoke succeeded.
SQL> select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
XFF01                          FALSE FALSE FALSE
SQL> revoke sysoper  from xff01;
Revoke succeeded.
--回收sysdba，sysoper权限后，视图记录消失
SQL> select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
--密码文件内容无变化
SQL>  !strings $ORACLE_HOME/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC
XFF01
D32693095588EF4F
--删除其中sysdba用户
SQL> drop user xff01;
User dropped.
--密码文件内容还是无变化
SQL>  !strings $ORACLE_HOME/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC
XFF01
D32693095588EF4F
--重启数据库密码文件依然无变化
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !strings $ORACLE_HOME/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC
XFF01
D32693095588EF4F

1、如果密码文件不存在或者名称错误，查询v$pwfile_users将得到空记录
2、添加sysdba等权限用户，会记录到密码文件和v$pwfile_users中
3、到回收sysdba等权限用户，密码文件记录依然存在，但是v$pwfile_users中无对应记录

三、远程登录测试

--密码文件记录存在，视图不记录不存在，登录失败
[oracle@node1 ~]$ sqlplus xff01/xifenfei@ora11g as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Sun Dec 4 19:42:26 2011
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name
--密码文件视图记录均存在，登录成功
[oracle@node1 ~]$ sqlplus sys/xifenfei@ora11g as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Sun Dec 4 19:42:10 2011
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to an idle instance.

是否能远程登录，依照v$pwfile_users为准

四、创建密码文件

win：
orapwd file=%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora entries=3 password=manager force=y
linux:
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID entries=3 password=manager force=y

下篇:V$PWFILE_USERS和密码文件关系(续)

一、执行的schema不同，操作的对象也不同

C:\Users\XIFENFEI>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期日 11月 20 20:39:06 20
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Productio
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> create user xffa identified by xifenfei;
用户已创建。
SQL> grant connect,resource to xffa;
授权成功。
SQL> create user xffb identified by xifenfei;
用户已创建。
SQL> grant connect,resource to xffb;
授权成功。
SQL> conn xffa/xifenfei
已连接。
xffa>create table tmp(str varchar2(50));
表已创建。
xffa>insert into tmp values
  2  ('my name is xffa');
已创建 1 行。
xffa>commit;
提交完成。
xffa>create or replace procedure definer_proc as
  2      begin
  3        for x in (select sys_context('userenv', 'current_user') current_user,
  4                         sys_context('userenv', 'session_user') session_user,
  5                         sys_context('userenv', 'current_schema') current_sch
ema,
  6                         str
  7                    from tmp) loop
  8        dbms_output.put_line('Current User:   ' || x.current_user);
  9        dbms_output.put_line('Session User:   ' || x.session_user);
 10        dbms_output.put_line('Current Schema: ' || x.current_schema);
 11        dbms_output.put_line('Tables Value:    ' || x.str);
 12      end loop;
 13    end;
 14    /
过程已创建。
xffa>create or replace procedure invoker_proc AUTHID CURRENT_USER as
  2      begin
  3        for x in (select sys_context('userenv', 'current_user') current_user,
  4                         sys_context('userenv', 'session_user') session_user,
  5                         sys_context('userenv', 'current_schema') current_sch
ema,
  6                         str
  7                    from tmp) loop
  8        dbms_output.put_line('Current User:   ' || x.current_user);
  9        dbms_output.put_line('Session User:   ' || x.session_user);
 10        dbms_output.put_line('Current Schema: ' || x.current_schema);
 11        dbms_output.put_line('Tables Value:    ' || x.str);
 12      end loop;
 13    end;
 14    /
过程已创建。
xffa>grant execute on definer_proc to xffb;
授权成功。
xffa>grant execute on invoker_proc to xffb;
授权成功。
xffa>set serveroutput on
xffa>exec definer_proc;
Current User:   XFFA
Session User:   XFFA
Current Schema: XFFA
Tables Value:    my name is xffa
PL/SQL 过程已成功完成。
xffa>exec invoker_proc;
Current User:   XFFA
Session User:   XFFA
Current Schema: XFFA
Tables Value:    my name is xffa
PL/SQL 过程已成功完成。
xffa>conn xffb/xifenfei
已连接。
xffb>exec xffa.definer_proc;
PL/SQL 过程已成功完成。
xffb>set serveroutput on
xffb>exec xffa.definer_proc;
Current User:   XFFA
Session User:   XFFB
Current Schema: XFFA
Tables Value:    my name is xffa
PL/SQL 过程已成功完成。
--除了session是当前用户的，其他都是这个过程所属用户
xffb>exec xffa.invoker_proc;
BEGIN xffa.invoker_proc; END;
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
ORA-06512: 在 "XFFA.INVOKER_PROC", line 3
ORA-06512: 在 line 1
--根据这个提示，很容易知道是tmp表不存在，也就是说明这个没有调用xffa.tmp表
xffb>create table tmp(str varchar2(50));
表已创建。
xffb>insert into tmp values
  2  ('my name is xffb');
已创建 1 行。
xffb>commit;
提交完成。
xffb>exec xffa.invoker_proc;
Current User:   XFFB
Session User:   XFFB
Current Schema: XFFB
Tables Value:    my name is xffb
PL/SQL 过程已成功完成。
--这个可以看出当时调用者的时候，执行的完全是当前用户下面的对象

在定义者(definer)权限下，执行的用户操作的schema为定义者，所操作的对象是定义者在编译时指定的对象。
在调用者(invoker)权限下，执行的用户操作的schema为当前用户，所操作的对象是当前模式下的对象。

二、执行的权限不同

xffb>create or replace procedure createtbl_definer as
  2      begin
  3        execute immediate 'create table xff_definer (id number)';
  4      end;
  5      /
过程已创建。
xffb>create or replace procedure createtbl_invoker AUTHID CURRENT_USER  as
  2      begin
  3        execute immediate 'create table xff_invoker (id number)';
  4      end;
  5      /
xffb>grant execute on createtbl_definer to xffa;
授权成功。
xffb>grant execute on createtbl_invoker to xffa;
授权成功。
xffb>exec createtbl_definer;
BEGIN createtbl_definer; END;
*
第 1 行出现错误:
ORA-01031: 权限不足
ORA-06512: 在 "XFFB.CREATETBL_DEFINER", line 3
ORA-06512: 在 line 1
--提示权限不足，很明显是缺少创建表的权限
xffb>exec createtbl_invoker;
PL/SQL 过程已成功完成。
xffb>desc xff_invoker;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
--调用者创建成功
xffb>conn xffa/xifenfei
已连接。
xffa>exec xffb.createtbl_definer;
BEGIN xffb.createtbl_definer; END;
*
第 1 行出现错误:
ORA-01031: 权限不足
ORA-06512: 在 "XFFB.CREATETBL_DEFINER", line 3
ORA-06512: 在 line 1
--定义者同样提示没有权限创建表，通过1中的试验，我们知道
--定义者执行的这个过程定义者的对象，也就是说，是因为xffb无权创建表导致该提示
xffa>exec xffb.createtbl_invoker;
PL/SQL 过程已成功完成。
xffa>desc xff_invoker;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
xffa>conn / as sysdba
已连接。
SQL>select owner,table_name from dba_tables where table_name=upper('xff_invoker');
OWNER                          TABLE_NAME
------------------------------ ------------------------------
XFFB                           XFF_INVOKER
XFFA                           XFF_INVOKER
--xffa用户执行成功，并且在自己的schema下面创建了表，证明了1的正确
SQL>GRANT CREATE TABLE TO XFFB;
授权成功。
SQL>conn xffb/xifenfei
已连接。
xffb>exec createtbl_definer;
PL/SQL 过程已成功完成。
xffb>desc xff_definer;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
--说明直接授权是的定义者操作成功，但是角色(resource)不能使其操作成功
xffb>conn xffa/xifenfei
已连接。
xffa>exec xffb.createtbl_definer;
BEGIN xffb.createtbl_definer; END;
*
第 1 行出现错误:
ORA-00955: 名称已由现有对象使用
ORA-06512: 在 "XFFB.CREATETBL_DEFINER", line 3
ORA-06512: 在 line 1
--因为定义者执行的是过程创建者的对象，因为XFFB.CREATETBL_DEFINER已经创建成功

在定义者(definer)权限下，当前用户的权限为角色无效情况下所拥有的权限。
在调用者(invoker)权限下，当前用户的权限为当前所拥有的权限(含角色)。

三、执行的效率不同
在定义者(definer)权限下，过程被静态编译静态执行(相对而言)，所执行sql语句在共享区池中是可被共享使用的
在调用者(invoker)权限下，过程静态编译，但动态执行，虽然执行的语句相同，但不同用户执行，其sql语句在共享池中并不能共享。

参考:http://www.itpub.net/thread-935634-1-1.html

DBA_SYS_PRIVS 用户所拥有的系统权限

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Nov 16 13:26:09 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> create user xff identified by xifenfei;
User created.
SQL> grant connect to xff;
Grant succeeded.
SQL> select * from DBA_SYS_PRIVS where grantee='XFF';
no rows selected
SQL> REVOKE CONNECT FROM XFF;
Revoke succeeded.
SQL> grant create session to xff;
Grant succeeded.
SQL>  select * from DBA_SYS_PRIVS where grantee='XFF';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
XFF                            CREATE SESSION                           NO
SQL> grant select  on chf.t_1 to xff;
Grant succeeded.
SQL>  select * from DBA_SYS_PRIVS where grantee='XFF';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
XFF                            CREATE SESSION                           NO
--说明只能查询系统权限，不能查询角色，不能查询用户权限

DBA_SYS_PRIVS 用户所拥有的角色

SQL> grant resource to xff;
授权成功。
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='XFF';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
XFF                            RESOURCE                       NO  YES

ROLE_SYS_PRIVS 角色所拥有的系统权限

SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='CONNECT';
ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE SESSION                           NO
SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='RESOURCE';
no rows selected
SQL> SELECT * FROM SESSION_ROLES;
ROLE
------------------------------
CONNECT
SQL> CONN / AS SYSDBA
Connected.
SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='RESOURCE';
ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO
8 rows selected.
--很多时候只能使用sysdba(或者具体特定权限)才能够查询角色有哪些系统权限

ROLE_ROLE_PRIVS: 角色被赋予的角色

SQL> SELECT *FROM ROLE_ROLE_PRIVS WHERE ROLE='DBA';
ROLE                           GRANTED_ROLE                   ADM
------------------------------ ------------------------------ ---
DBA                            OLAP_DBA                       NO
DBA                            SCHEDULER_ADMIN                YES
DBA                            DELETE_CATALOG_ROLE            YES
DBA                            EXECUTE_CATALOG_ROLE           YES
DBA                            WM_ADMIN_ROLE                  NO
DBA                            EXP_FULL_DATABASE              NO
DBA                            SELECT_CATALOG_ROLE            YES
DBA                            JAVA_DEPLOY                    NO
DBA                            GATHER_SYSTEM_STATISTICS       NO
DBA                            JAVA_ADMIN                     NO
DBA                            XDBADMIN                       NO
ROLE                           GRANTED_ROLE                   ADM
------------------------------ ------------------------------ ---
DBA                            IMP_FULL_DATABASE              NO
DBA                            XDBWEBSERVICES                 NO
13 rows selected.

SESSION_PRIVS 当前用户所拥有的全部权限

SQL> conn xff/xifenfei
Connected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
SQL> conn / as sysdba
Connected.
SQL> revoke create session from xff;
Revoke succeeded.
SQL> grant connect to xff;
Grant succeeded.
SQL> conn xff/xifenfei
Connected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
--只能查看系统权限或者角色中包含的系统权限，不能查看用户权限

SESSION_ROLES: 当前用户被激活的角色

SQL> SELECT * from SESSION_ROLES;
no rows selected
SQL> show user;
USER is "SYS"
SQL> conn xff/xifenfei
Connected.
SQL> SELECT *FROM SESSION_ROLES;
ROLE
------------------------------
CONNECT
--sysdba查询无role选项，全部是由系统权限构成

查询某用户的所有系统权限

SQL> SELECT PRIVILEGE, ADMIN_OPTION
  2    FROM DBA_SYS_PRIVS
  3   WHERE GRANTEE = &USERNAME
  4  UNION
  5  --角色转换为权限
  6  SELECT PRIVILEGE, ADMIN_OPTION
  7    FROM ROLE_SYS_PRIVS
  8   WHERE ROLE IN
  9         (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = &USERNAME)
 10  UNION
 11  --角色的角色转为权限
 12  SELECT PRIVILEGE, ADMIN_OPTION
 13    FROM ROLE_SYS_PRIVS
 14   WHERE ROLE IN (SELECT GRANTED_ROLE
 15                    FROM ROLE_ROLE_PRIVS
 16                   WHERE ROLE IN (SELECT GRANTED_ROLE
 17                                    FROM DBA_ROLE_PRIVS
 18                                   WHERE GRANTEE = &USERNAME));
输入 username 的值:  'XFF'
原值    3:  WHERE GRANTEE = &USERNAME
新值    3:  WHERE GRANTEE = 'XFF'
输入 username 的值:  'XFF'
原值    9:        (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = &USERNAME)
新值    9:        (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'XFF')
输入 username 的值:  'XFF'
原值   18:                                  WHERE GRANTEE = &USERNAME))
新值   18:                                  WHERE GRANTEE = 'XFF'))
PRIVILEGE                                ADM
---------------------------------------- ---
CREATE CLUSTER                           NO
CREATE INDEXTYPE                         NO
CREATE OPERATOR                          NO
CREATE PROCEDURE                         NO
CREATE SEQUENCE                          NO
CREATE SESSION                           NO
CREATE TABLE                             NO
CREATE TRIGGER                           NO
CREATE TYPE                              NO
UNLIMITED TABLESPACE                     NO
已选择10行。

表相关权限视图

SELECT *FROM TABLE_PRIVILEGES;
SELECT * FROM dba_TAB_PRIVS;
SELECT * FROM ROLE_TAB_PRIVS;

一、sql操作

[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 15 15:00:15 2011
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             285216048 bytes
Database Buffers          121634816 bytes
Redo Buffers                8466432 bytes
Database mounted.
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode
SQL> alter system enable restricted session;
System altered.
SQL> drop database;
Database dropped.
Disconnected from 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

二、alert日志内容

Tue Nov 15 15:00:18 2011
Adjusting the default value of parameter parallel_max_servers
from 320 to 135 due to the value of parameter processes (150)
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
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.
ORACLE_HOME = /opt/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      node1.srtcloud.com
Release:        2.6.18-238.19.1.el5
Version:        #1 SMP Fri Jul 15 07:31:24 EDT 2011
Machine:        x86_64
Using parameter settings in server-side spfile /opt/oracle/product/11.2.0/db_1/dbs/spfilet1.ora
System parameters with non-default values:
  processes                = 150
  memory_target            = 400M
  control_files            = "/opt/oracle/oradata/t1/control01.ctl"
  control_files            = "/opt/oracle/fast_recovery_area/t1/control02.ctl"
  db_block_size            = 8192
  compatible               = "11.2.0.0.0"
  db_recovery_file_dest    = "/opt/oracle/fast_recovery_area"
  db_recovery_file_dest_size= 4122M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=t1XDB)"
  local_listener           = "LISTENER_T1"
  audit_file_dest          = "/opt/oracle/admin/t1/adump"
  audit_trail              = "DB"
  db_name                  = "t1"
  open_cursors             = 300
  diagnostic_dest          = "/opt/oracle"
Tue Nov 15 15:00:22 2011
PMON started with pid=2, OS id=26704
Tue Nov 15 15:00:22 2011
PSP0 started with pid=3, OS id=26706
Tue Nov 15 15:00:23 2011
VKTM started with pid=4, OS id=26708 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Tue Nov 15 15:00:23 2011
GEN0 started with pid=5, OS id=26712
Tue Nov 15 15:00:23 2011
DIAG started with pid=6, OS id=26714
Tue Nov 15 15:00:23 2011
DBRM started with pid=7, OS id=26716
Tue Nov 15 15:00:23 2011
DIA0 started with pid=8, OS id=26718
Tue Nov 15 15:00:23 2011
MMAN started with pid=9, OS id=26720
Tue Nov 15 15:00:23 2011
DBW0 started with pid=10, OS id=26722
Tue Nov 15 15:00:23 2011
LGWR started with pid=11, OS id=26724
Tue Nov 15 15:00:23 2011
CKPT started with pid=12, OS id=26726
Tue Nov 15 15:00:23 2011
SMON started with pid=13, OS id=26728
Tue Nov 15 15:00:23 2011
RECO started with pid=14, OS id=26730
Tue Nov 15 15:00:23 2011
MMON started with pid=15, OS id=26732
Tue Nov 15 15:00:23 2011
MMNL started with pid=16, OS id=26734
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /opt/oracle
Tue Nov 15 15:00:23 2011
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 2578048199
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Tue Nov 15 15:00:33 2011
drop database
ORA-12719 signalled during: drop database...
Tue Nov 15 15:00:47 2011
Stopping background process MMNL
Stopping background process MMON
Starting background process MMON
Tue Nov 15 15:00:49 2011
MMON started with pid=15, OS id=26788
Starting background process MMNL
Tue Nov 15 15:00:49 2011
MMNL started with pid=16, OS id=26790
ALTER SYSTEM enable restricted session;
Tue Nov 15 15:01:06 2011
drop database
Deleted file /opt/oracle/oradata/t1/system01.dbf
Deleted file /opt/oracle/oradata/t1/sysaux01.dbf
Deleted file /opt/oracle/oradata/t1/undotbs01.dbf
Deleted file /opt/oracle/oradata/t1/users01.dbf
Deleted file /opt/oracle/oradata/t1/redo01.log
Deleted file /opt/oracle/oradata/t1/redo02.log
Deleted file /opt/oracle/oradata/t1/redo03.log
Deleted file /opt/oracle/oradata/t1/temp01.dbf
Deleted file /opt/oracle/product/11.2.0/db_1/dbs/snapcf_t1.f
Shutting down archive processes
Archiving is disabled
Create Relation ADR_CONTROL
Create Relation ADR_INVALIDATION
Create Relation INC_METER_IMPT_DEF
Create Relation INC_METER_PK_IMPTS
USER (ospid: 26761): terminating the instance
Instance terminated by USER, pid = 26761
Tue Nov 15 15:01:18 2011
Deleted file /opt/oracle/oradata/t1/control01.ctl
Deleted file /opt/oracle/fast_recovery_area/t1/control02.ctl
Completed: drop database
Shutting down instance (abort)
License high water mark = 1
Tue Nov 15 15:01:32 2011
Instance shutdown complete

三、后续工作
1、清除相关日志trace文件$ORACLE_BASE/diag
2、删除fast_recovery_area文件$ORACLE_BASE/fast_recovery_area
3、删除归档日志(根据配置)
4、删除/etc/oratab中关于该数据库的记录(t1:/opt/oracle/product/11.2.0/db_1:N)

四、补充说明
1、在能够使用dbca删除数据库的情况下，应该选择dbca，这个删除的更加干净
2、dbca删除数据库也需要清理部分文件(如：归档日志)
3、如果对数据库的存储结构比较了解，可以人工关闭数据库后，手工删除相关文件
4、drop database使用于10g及其以上版本

虽然自己对java不熟悉，但是做为一个dba，经常会遇到java和数据库连接的配置文件，参考朋友网站，记录下来以便以后查找

格式一:  Oracle JDBC Thin using a ServiceName
jdbc:oracle:thin:@//<host>:<port>/<service_name>
Example: jdbc:oracle:thin:@//192.168.2.1:1521/xifenfei
格式二: Oracle JDBC Thin using an SID
jdbc:oracle:thin:@<host>:<port>:<SID>
Example: jdbc:oracle:thin:192.168.2.1:1521:xff
--注意这里的格式，@后面有//, 这是与使用SID的主要区别。
格式三：Oracle JDBC Thin using a TNSName
jdbc:oracle:thin:@<TNSName>
Example: jdbc:oracle:thin:@GL
--Support for TNSNames was added in the driver release 10.2.0.1

来源:Java JDBC Thin Driver 连接 Oracle 三种方法说明

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: 协议适配器错误
请输入用户名: