如何禁止特定用户使用sqlplus或PL/SQL Developer等工具登陆?

最早想要实现禁止某些特定用户使用SQLPLUS或PL/SQL Developer等工具登陆是在2010年的3月,当时发现用户的一套数据库中有大量的用户使用老版本的PL/SQL Developer登陆,具体的版本号记不清楚了,大约是PL/SQL Developer 5的版本,是否正版授权不得而知, 反正就是一个办公室里有大量的阿姨、大叔都靠这个图形化工具访问数据库,做一些必要的数据操作,主要是一些SQL查询语句,有时候他们还会用工具栏查一些对象(search object),正是因为他们使用了老版本的PL/SQL Developer,造成在使用一些widget的时候会引起Oracle出现一些非致命的ORA-00600错误,虽然这些600错误不会导致严重的问题,但是只要是出现在告警日志Alert.log中的600还是需要我们去分析。

当时我的想法是直接从Oracle的角度禁止普通用户以PL/SQL Developer工具登陆,虽然当时没有真的这样做。 题外话,要真的这么做了,估计那一办公室的阿姨、叔叔都要找我的麻烦,他们可不会用SQLPLUS来登数据库;让他们升级PL/SQL Developer到高版本的想法也基本可以打住,让阿姨、叔叔们升级可要比登天还难。

Google了一番,没有找到太多有用的信息。

闲来无事,我在著名的Oracle-l Freelist邮件列表中发了一封邮件,集思广益:

 

Hello every,

           Anyone can advise how to ban plsql developer  connect to oracle?
The plsql developer search widget may cause  some ora-600 warning in alert
log . So I want to ban any connection using plsql developer.

 

Oracle-l Free List不愧是卧虎藏龙,第二天就收到了十分有用的信息,感谢这位 Coskan Gundogar的网友。

 

Damir-Vadas 在他的博客上提供了一种有效的方式,通过建立LOGON DATABASE的Trigger触发器,实现了仅允许拥有特定角色(Role)的用户通过sqlplus登陆实例。

 

这和我们的需求大致相仿,值得借鉴。我们的实际需求是: 针对某些已知的数据库用户账号,限制其使用SQLPLUS、PL/SQL Developer、Toad等工具登陆实例; 因为这里所要限制的用户账号和工具模块名(module)都是已知的,所以不必要如Damir-Vadas那样做成白名的形式,而只需要定义blacklist即可。实际这样做的一大目的是尽可能限制人为的登录; 我们知道当应用程序登录数据库时,根据应用程序的构成不同,可能使用JDBC Thin Client、Pro*C、ODBC等多种模块名(Module),我们不想限制应用程序的正常登录, 而仅仅想禁止人为地使用这些应用程序所使用的账号来登录实例(应用程序的账号信息可能被写在应用层中,或者为开发人员所知晓),一般我们只要限制SQLPLUS、PL/SQL Developer、Toad这几种主流的客户端程序,就可以限制人为地登录数据库了;当然这其实是防君子不防小人,实际如果hacker真的掌握了应用程序的账号密码的话,完全可以通过自己编写程序来访问数据库。

 

建立示例的,禁止以SQLPLUS和PL/SQL DEVELOPER登陆的用户账号TRY_LOGON_BY_TOOLS:

 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com  & www.askmaclean.com

SQL> create user TRY_LOGON_BY_TOOLS IDENTIFIED BY abc;

User created.

SQL> grant connect,resource to TRY_LOGON_BY_TOOLS;

Grant succeeded.

 

创建以下LOGON DATABASE Trigger即可限制TRY_LOGON_BY_TOOLS用户以SQLPLUS、Toad、PL/SQL DEVELOPER 三种软件登陆数据库,  但是该用户仍能以其他的程序模块登录,如:JDBC、ODBC等;实际就是限制了该账号的人为登录,而应用程序如Java、.Net、Pro*C等语言编写的程序没有使用这里已经禁止的模块名(Module),所以不会被禁止登录。

 

记得修改TRY_LOGON_BY_TOOLS为你需要的用户名列表

-- NAME: BLOCK_TOOLS_LOGON.SQL
-- ------------------------------------------------------------------------
--   Copyright 2011, www.askmaclean.com
--   LAST UPDATED: 12/05/11
--   Written By Maclean.Liu
-- Usage: @BLOCK_TOOLS_LOGON
-- ------------------------------------------------------------------------
-- PURPOSE:
--    This script is to be used to help dba protect database
--    from uninvited logon action
-- ------------------------------------------------------------------------
-- DISCLAIMER:
--    This script is provided for educational purposes only. It is NOT
--    supported by Maclean Liu.
--    The script has been tested and appears to work as intended.
--    You should always run new scripts on a test instance initially.
-- ------------------------------------------------------------------------
-- Script output is as follows:

drop trigger BLOCK_TOOLS_LOGON;

CREATE OR REPLACE TRIGGER BLOCK_TOOLS_LOGON
  AFTER LOGON ON DATABASE
DECLARE

  my_forced_exception EXCEPTION;
  PRAGMA EXCEPTION_INIT(MY_FORCED_EXCEPTION, -20101);
BEGIN
  IF (sys_context('USERENV', 'SESSION_USER') IN ('TRY_LOGON_BY_TOOLS')) -- add your username here
   THEN
    IF (UPPER(sys_context('USERENV', 'MODULE')) LIKE '%SQLPLUS%' OR --SQL*PLUS
       UPPER(sys_context('USERENV', 'MODULE')) LIKE '%TOAD%' OR --TOAD
       UPPER(sys_context('USERENV', 'MODULE')) LIKE '%PLSQLDEV%') --PL/SQL DEVELOPER
     THEN
      RAISE my_forced_exception;
    END IF;
  END IF;
EXCEPTION
  WHEN my_forced_exception THEN
    RAISE_APPLICATION_ERROR(-20101,
                            'USER ' ||
                            sys_context('USERENV', 'SESSION_USER') || ' ' ||
                            'MODULE ' ||
                            UPPER(sys_context('USERENV', 'MODULE')) || ' ' || '
                              Logon Action via tool is not allowed.
                              Please contact Maclean Liu to help you!
                              https://www.askmaclean.com/');
  WHEN OTHERS THEN
    null;
END;
/

 

以DBA身份用户登录并创建以上Trigger:

 

[oracle@vrh8 ~]$ sqlplus system/oracle

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 6 00:34:12 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE OR REPLACE TRIGGER BLOCK_TOOLS_LOGON
  2    AFTER LOGON ON DATABASE
  3  DECLARE
  4
  5    my_forced_exception EXCEPTION;
  6    PRAGMA EXCEPTION_INIT(MY_FORCED_EXCEPTION, -20101);
  7  BEGIN
  8    IF (sys_context('USERENV', 'SESSION_USER') IN ('TRY_LOGON_BY_TOOLS')) -- add your username here
  9     THEN
 10      IF (UPPER(sys_context('USERENV', 'MODULE')) LIKE '%SQLPLUS%' OR --SQL*PLUS
 11         UPPER(sys_context('USERENV', 'MODULE')) LIKE '%TOAD%' OR --TOAD
 12         UPPER(sys_context('USERENV', 'MODULE')) LIKE '%PLSQLDEV%') --PL/SQL DEVELOPER
 13       THEN
 14        RAISE my_forced_exception;
 15      END IF;
 16    END IF;
 17  EXCEPTION
 18    WHEN my_forced_exception THEN
 19      RAISE_APPLICATION_ERROR(-20101,
 20                              'USER ' ||
 21                              sys_context('USERENV', 'SESSION_USER') || ' ' ||
 22                              'MODULE ' ||
 23                              UPPER(sys_context('USERENV', 'MODULE')) || ' ' || '
 24                                Logon Action via tool is not allowed.
 25                                Please contact Maclean Liu to help you!
 26                                https://www.askmaclean.com/');
 27    WHEN OTHERS THEN
 28      null;
 29  END;
 30  /

Trigger created.

 

成功创建后 , 使用指定的TRY_LOGON_BY_TOOLS用户尝试SQLPLUS登录:

 

[oracle@vrh8 ~]$ sqlplus TRY_LOGON_BY_TOOLS/abc

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 6 01:18:47 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20101: USER TRY_LOGON_BY_TOOLS MODULE SQLPLUS@VRH8.ORACLE.COM (TNS V1-V3)
Logon Action via tool is not allowed.
Please contact Maclean Liu to help you!
了解 Maclean Liu 刘相兵 @诗檀软件
ORA-06512: at line 17

 

TRY_LOGON_BY_TOOLS用户以PL/SQL Developer登录:

 

 

 

以上我们通过建立LOGON触发器的形式达到了禁止特定用户以SQLPLUS、PL/SQL Developer登录实例的目的,但是请注意Trigger触发器会消耗额外的资源,对于登录频繁的系统,一个小小的登录触发器可能引发性能的瓶颈。  另外在一些容易hang住的场景中,Trigger可能会引起更多不良的反应,所以请谨慎地在产品数据库中部署这些小玩样。

LIBCLNTSH.SO: WRONG ELF CLASS: ELFCLASS32错误一例

某套数据库以dba组用户登录正常,但以非dba组用户登录时出现LIBCLNTSH.SO: WRONG ELF CLASS: ELFCLASS32错误,如下例子:


su  - maclean

sqlplus 

ld.so.1: sqlplus: fatal: /s01/orabase/product/10.2.0/dbhome_1/lib32/libclntsh.so.10.1 : wrong ELF class:  ELFCLASS32

这里可以看到引起wrong ELF class的库文件(libclntsh.so.10.1)位于lib32目录下,说明该库文件是32位的。

一般来说只有安装了64位oracle软件时会同时出现lib和lib32 这2个存放库文件的目录, 当安装了32位oracle软件时则只会显示lib一个目录。 这也是判断所安装的Oracle数据库软件是64 bit 还是 32 bit的一种方法。

以上问题一般是由于设置了不正确的LD_LIBRARY_PATH 环境变量 , 或者 lib 目录、libclntsh.so.10.1权限不正确引起的。

建议通过以下步骤排查问题:

1. 检查 sqlplus 命令相关的so共享库文件
[oracle@vrh1 ~]$ ldd `which sqlplus` 
linux-vdso.so.1 =>  (0x00007fff0bbfd000)
libsqlplus.so => /s01/orabase/product/10.2.0/dbhome_1/lib/libsqlplus.so (0x00002b450f6b4000)
libclntsh.so.10.1 => /s01/orabase/product/10.2.0/dbhome_1/lib/libclntsh.so.10.1 (0x00002b450f89b000)
libnnz10.so => /s01/orabase/product/10.2.0/dbhome_1/lib/libnnz10.so (0x00002b4510ec6000)
libdl.so.2 => /lib64/libdl.so.2 (0x0000003635c00000)
libm.so.6 => /lib64/libm.so.6 (0x0000003635800000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x0000003636000000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x0000003639800000)
libc.so.6 => /lib64/libc.so.6 (0x0000003635400000)
libaio.so.1 => /usr/lib64/libaio.so.1 (0x00002b45122ab000)
/lib64/ld-linux-x86-64.so.2 (0x0000003635000000)
2. 找出以上输出中与所报错误中文件名相符的记录,如错误中所报的so文件的路径名为
/s01/orabase/product/10.2.0/dbhome_1/lib32/libclntsh.so.10.1
而 64位 sqlplus 所需要的64位 so文件 的路径为
/s01/orabase/product/10.2.0/dbhome_1/lib/libclntsh.so.10.1
3. 以上说明可能是LD_LIBRARY_PATH 变量存在问题 , 检查该变量
echo $LD_LIBRARY_PATH 
/s01/orabase/product/10.2.0/dbhome_1/lib32:/s01/orabase/product/10.2.0/dbhome_1/lib
一般来说在64位的情况下 需要将 lib 目录 放置在 该LD_LIBRARY_PATH 变量的前面,如
export LD_LIBRARY_PATH= $ORACLE_HOME/lib:$ORACLE_HOME/lib32
4. 若检查发现LD_LIBRARY_PATH 变量没有问题, 那么可以进一步检查 lib 和lib32 这2个目录的权限 ,以及相关so 文件是否对 other组有必要的r-x权限。
也可以通过9.2.0.8 和 10g中自带的 changePerm.sh脚本来修复问题,该脚本一般位于$ORACLE_HOME/install目录下
su  - oracle
$ cd $ORACLE_HOME/install
$ ./changePerm.sh
Where is the script changePerm.sh?
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.8 to 10.2.0.5.0 - Release: 9.2 to 10.2
Information in this document applies to any platform.
Goal
Where is the script changePerm.sh?
Solution
During patch set installation, all new files and directories are created with restricted access, 
by default. Users or third party applications with a different group identifier from that of the database, 
which try to access client-side utilities or libraries in the database home, 
will see permission errors when trying to access these files or directories.
changePerm.sh is a script that allows users that do not belong to the database group to 
access and execute files in the ORACLE_HOME directory. Only run this script when absolutely 
required as it will reduce security. The script (borne shell) loops through a hard-coded set of 
files and directories, extracts the "group" permissions, and set the "other" permissions the 
same as the group's permissions. By doing so, "other" users are able to execute 
these files and change to these directories
The script is located in the ORACLE_HOME/install directory on Unix and Linux systems 
(there is no equivalent for Windows). 
This script ships with Oracle Database versions 9.2.0.8 and Oracle 10g releases. 
The changePerm.sh file is not available nor necessary for Oracle Database Server 11.1 
and above because in 11g, 
permissions under the Oracle Home (including "others") are set correctly/relaxed.
Output when the changePerm.sh script is executed:
$ cd $ORACLE_HOME/install
$ ./changePerm.sh
-------------------------------------------------------------------------------
Disclaimer: The purpose of this script is to relax permissions on some of the
files in the database Oracle Home so that all clients can access them.
Please note that Oracle Corporation recommends using the most restrictive file
permissions as possible for your given implementation. Running this script
should be done only after considering all security ramifications.
-------------------------------------------------------------------------------
Do you wish to continue (y/n) : y
Finished running the script successfully
Please see /tmp/changePerm_err.log for errors and /tmp/changePerm.log for the log of events
This script should be run by Oracle Software owner to relax the permission 
and can be run while database processes are active.
Note :
1. If you are patching Oracle RAC home, then you will need to run this script on all the nodes
2. Oracle recommends using the most restrictive file permissions 
possible for your given implementation. Run this script only after considering 
all security ramifications and only if you need to share this installation

解决sqlplus的segmentation fault或hang问题

sqlplus应当是DBA 1.0时代使用最为频繁的管理工具,经常有经验丰富的老DBA会提到自己敲过几万次的sqlplus:),但有的时候这个吃饭家伙也会不好用,偶尔还会出现Segmentation fault错误,亦或者彻底hang住。在这里我介绍几种应对sqlplus无法正常使用的应对方法:

1.出现Segmentation fault,这种情况下一般是sqlplus 2进制文件被损坏了,可以通过重新build一个sqlplus来解决问题

[oracle@rh2 bin]$ sqlplus
Segmentation fault
/* 使用$ORACLE_HOME/sqlplus/lib目录下的make文件,编译一个新的sqlplus */
[oracle@rh2 ~]$ make -f $ORACLE_HOME/sqlplus/lib/ins_sqlplus.mk  newsqlplus
Linking /s01/oracle/product/11.2.0/dbhome_1/sqlplus/bin/sqlplus
rm -f /s01/oracle/product/11.2.0/dbhome_1/sqlplus/bin/sqlplus
gcc -o /s01/oracle/product/11.2.0/dbhome_1/sqlplus/bin/sqlplus -m64
-L/s01/oracle/product/11.2.0/dbhome_1/sqlplus/lib/ -L/s01/oracle/product/11.2.0/dbhome_1/lib/
-L/s01/oracle/product/11.2.0/dbhome_1/lib/stubs/  /s01/oracle/product/11.2.0/dbhome_1/sqlplus/lib/s0afimai.o
-lsqlplus -lclntsh  `cat /s01/oracle/product/11.2.0/dbhome_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11
-ln11 -lnl11 -lnro11 `cat /s01/oracle/product/11.2.0/dbhome_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11
-ln11 -lnl11 -lnnz11 -lzt11 -lztkg11 -lztkg11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11 -lmm
-lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11
-lcore11 -lnls11 `cat /s01/oracle/product/11.2.0/dbhome_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11
-lnl11 -lnro11 `cat /s01/oracle/product/11.2.0/dbhome_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11
-lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11   -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11
-lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11 -lnnetd11  -lvsn11
-lcommon11 -lgeneric11 -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11
-lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11   `cat /s01/oracle/product/11.2.0/dbhome_1/lib/sysliblist`
-Wl,-rpath,/s01/oracle/product/11.2.0/dbhome_1/lib -lm -lpthread   `cat /s01/oracle/product/11.2.0/dbhome_1/lib/sysliblist`
-ldl -lm -lpthread  -L/s01/oracle/product/11.2.0/dbhome_1/lib
/bin/chmod 755 /s01/oracle/product/11.2.0/dbhome_1/sqlplus/bin/sqlplus
rm -f /s01/oracle/product/11.2.0/dbhome_1/bin/sqlplus
mv -f /s01/oracle/product/11.2.0/dbhome_1/sqlplus/bin/sqlplus /s01/oracle/product/11.2.0/dbhome_1/bin/sqlplus
/bin/chmod 751 /s01/oracle/product/11.2.0/dbhome_1/bin/sqlplus
rm -f /s01/oracle/product/11.2.0/dbhome_1/sqlplus/lib/libsqlplus.so
rm -rf /s01/oracle/product/11.2.0/dbhome_1/sqlplus/bin/sqlplus
[oracle@rh2 ~]$ sqlplus  / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed May 11 21:38:21 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

2.出现sqlplus之后hang住的现象,hang的原因存在多种可能:
1)instance hanging数据库实例hang住,这种情况下sqlplus无法正常登陆到正hang的实例,而登陆到其他实例是可以的;若在10g以后版本中可以使用-prelim选项登陆实例,使用该选项登陆后无法执行普通的SQL语句,但可以使用oradebug内部调试工具,通过oradebug收集必要的hanganalyze信息后,可以进一步判断hang住的原因并决定下一步的操作。

[oracle@rh2 ~]$ sqlplus  / as sysdba
.............................we suspend here!!!

[oracle@rh2 ~]$ sqlplus  -prelim / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed May 11 21:46:27 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump hanganalyze 4;
Statement processed.
SQL> oradebug dump systemstate 266;
Statement processed.
SQL> oradebug tracefile_name
/s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_ora_23436.trc   -- where dump resides

将以上trc文件提交给Oracle Support或者资深的Oracle技术人员,以便他们分析出实例hang住的原因,通过调整参数或者修复bug可以避免再次出现类似的状况。

2)一执行sqlplus就出现挂起现象,甚至没有登陆任何数据库。一般这种情况是在读取sqlplus 2进制文件或其相关的共享库文件(.so文件)时遇到了问题,或者是在实际system call系统调用execve(“sqlplus”)时遇到了错误,一般我们可以使用系统跟踪工具strace(Linux)或truss(Unix)工具来分析这种挂起现象:

/* Unix */
truss -o sqlplus_hang.log sqlplus
/* Linux */
strace -o sqlplus_hang.log sqlplus
head -10 sqlplus_hang.log
execve("/s01/db_1/bin/sqlplus", ["sqlplus"], [/* 28 vars */]) = -1 ENOEXEC (Exec format error)

可以看到以上strace记录中发现了调用execve函数(execve() executes the program pointed to by filename)运行sqlplus程序时出现了ENOEXEC错误,该ENOEXEC错误代码说明我们正在执行一个格式无效的可执行文件,具体的解释如下:

This error indicates that a request has been made to execute a file which, although it has the
appropriate permissions, does not start with a valid magic number. A magic number is the first two
bytes in a file, used to determine what type of file it is.
You tried to execute a file that is not in a valid executable format. The most common format for
binary programs under linux is called ELF. Note that your shell will run ascii files that have the
executable bit set as a shell script (ie run it as shell commands).
You can reproduce this by doing
$ dd if=/dev/random of=myfile bs=1k count=1 $ chmod +x myfile $ ./myfile zsh: exec format error: ./myfile
Note that there is a very slight possibility that you could create a valid program that does
something bad to your system!!
Note, you can have user defined ways of running programs using Linux's binfmt_misc. See
/usr/src/linux/Documentation/binfmt_misc.txt

to be continued …………

Oracle中如何追踪savepoint

Oracle中的savepoint是事务中的标示符,以帮助我们做到部分操作的回滚。

我们可以通过trace找出会话中自上次commit以来所有的savepoint保存点,要阅读这些trace内容你需要有user_dump_dest目录的相关权限。

SQL> oradebug setmypid;
已处理的语句
SQL>
SQL>
SQL> insert into  system.linktest values(2);

已创建 1 行。

SQL> savepoint a;

保存点已创建。

SQL> insert into system.linktest values(3);

已创建 1 行。

SQL> savepoint b;

保存点已创建。

SQL> insert into system.linktest values(4);

已创建 1 行。

SQL> savepoint d;

保存点已创建。

SQL> oradebug event immediate trace name savepoints level 1;

已处理的语句
SQL> oradebug tracefile_name;
e:\oracle\product\10.2.0\admin\orcln\udump\orcln_ora_1756.trc

其中 oradebug setmypid;  表示要trace的session为当前会话。

在非mts环境下,我们也可以通过如:

oradebug setospid OSPID;         — 通过指定OS中的进程号,即v$process中的spid

oradebug setorapid ORAPID;      — 通过指定Oracle 中的进程号,即v$process中的pid

来定制我们需要trace的相关session。

退回到命令行格式,该trace主要内容如下:

*** 2009-09-15 12:39:37.828
*** SERVICE NAME:(SYS$USERS) 2009-09-15 12:39:37.828
*** SESSION ID:(151.83) 2009-09-15 12:39:37.828
====================================================
SAVEPOINT FOR CURRENT PROCESS
——————————
flag: 0x1
name: D
dba: 0x800084, sequence #: 177, record #: 51, savepoint #: 81
status: VALID, next: 28236068
name: B
dba: 0x800084, sequence #: 177, record #: 50, savepoint #: 66
status: VALID, next: 282373F4
name: A
dba: 0x800084, sequence #: 177, record #: 49, savepoint #: 52
status: VALID, next: 00000000

metalink中的相关介绍如下:

Subject:

How To Find Out The Savepoint For Current Process

Doc ID:

108611.1

Type:

BULLETIN

Modified Date:

16-JUN-2004

Status:

PUBLISHED

PURPOSE

——-

This information shows you how to get the savepoints you have

issued since the last commit was issued.

SCOPE & APPLICATION

——————-

Any user can follow the steps to find out the savepoints in

their own session.  However, only the the user who have read

permission to the user_dump_dest directory can view the

output.

Steps to Retrieve the Savepoint Issued after the Last Commit

————————————————————

In the following illustration, two savepoints are created in

the same session.  Then a savepoints dump is issued to get

the savepoint information.  An example of the dump file is

included.

SQL> insert into emp (empno, ename)

2  values (9995, ‘vso’);

1 row created.

SQL> savepoint pt1;

Savepoint created.

SQL> insert into emp (empno, ename)

2  values (9994, ‘vso’);

1 row created.

SQL> savepoint pt2;

Savepoint created.

SQL> alter session set events

2  ‘immediate trace name savepoints level 1’;

Session altered.

A trace file is generated in the user_dump_directory.  The

content of the trace file is included in the following:

Dump file /u04/app/oracle/admin/R805/udump/r805_ora_18763.trc

Oracle8 Enterprise Edition Release 8.0.5.2.1 – Production

With the Partitioning and Objects options

PL/SQL Release 8.0.5.2.0 – Production

ORACLE_HOME = /u04/app/oracle/product/8.0.5

System name:    SunOS

Node name:      rtcsol1

Release:        5.6

Version:        Generic_105181-17

Machine:        sun4u

Instance name: R805

Redo thread mounted by this instance: 1

Oracle process number: 8

Unix process pid: 18763, image: oracleR805

*** 2000.04.18.14.11.37.000

*** SESSION ID:(7.391) 2000.04.18.14.11.37.000

====================================================

SAVEPOINT FOR CURRENT PROCESS

——————————

flag: 0x1

name: PT2

dba: 0x80020e, sequence #: 0, record #: 9, savepoint #: 131

status: VALID, next: 10a0868

name: PT1

dba: 0x80020e, sequence #: 0, record #: 7, savepoint #: 109

status: VALID, next: 0

沪ICP备14014813号

沪公网安备 31010802001379号