Daily Archives: 2012 年 02 月 15 日

遭遇ORA-07445[kkdliac()+346]使用odu抢救数据

Posted on by

1.Oracle启动报错

-bash-2.05b$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on 星期三 2月 15 10:31:53 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1527846824 bytes
Fixed Size                   452520 bytes
Variable Size             385875968 bytes
Database Buffers         1140850688 bytes
Redo Buffers                 667648 bytes
Database mounted.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

2.alert文件

Wed Feb 15 10:32:06 2012
ALTER DATABASE   MOUNT
Wed Feb 15 10:32:10 2012
Successful mount of redo thread 1, with mount id 412256678
Wed Feb 15 10:32:10 2012
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE   MOUNT
Wed Feb 15 10:33:44 2012
alter database open
Wed Feb 15 10:33:44 2012
Beginning crash recovery of 1 threads
Wed Feb 15 10:33:44 2012
Started redo scan
Wed Feb 15 10:33:44 2012
Completed redo scan
 0 redo blocks read, 0 data blocks need recovery
Wed Feb 15 10:33:44 2012
Started recovery at
 Thread 1: logseq 2, block 3, scn 2862.4075508322
Wed Feb 15 10:33:44 2012
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
  Mem# 0 errs 0: /data1z/oracle/oradata/ahcx216/redo01.log
Wed Feb 15 10:33:44 2012
Completed redo application
Wed Feb 15 10:33:44 2012
Ended recovery at
 Thread 1: logseq 2, block 3, scn 2862.4075528323
 0 data blocks read, 0 data blocks written, 0 redo blocks read
Crash recovery completed successfully
Wed Feb 15 10:33:44 2012
Thread 1 advanced to log sequence 3
Thread 1 opened at log sequence 3
  Current log# 3 seq# 3 mem# 0: /data1z/oracle/oradata/ahcx216/redo03.log
Successful open of redo thread 1
Wed Feb 15 10:33:45 2012
SMON: enabling cache recovery
Wed Feb 15 10:33:45 2012
Errors in file /data1z/oracle/admin/ahcx216/udump/ahcx216_ora_21325.trc:
ORA-07445: exception encountered: core dump [kkdliac()+346] [SIGSEGV] [Address not mapped to object] [0x43] [] []

3.trace文件

/data1z/oracle/admin/ahcx216/udump/ahcx216_ora_21325.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /data1z/oracle/product/9.2
System name:	Linux
Node name:	aiserch1
Release:	2.4.21-27.ELsmp
Version:	#1 SMP Wed Dec 1 21:59:02 EST 2004
Machine:	i686
Instance name: ahcx216
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 21325, image: oracle@aiserch1 (TNS V1-V3)

*** SESSION ID:(11.13) 2012-02-15 10:33:44.739
Thread checkpoint rba:0x000002.00000002.0010 scn:0x0b2e.f2eb5261
Cache low rba is 0xffffffff.ffffffff.ffff
Use incremental checkpoint on-disk rba
Thread 1 recovery from rba:0x000002.00000003.0000 scn:0x0b2e.f2eb5262
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 0
Average hash chain = 0/0 = 0.0
Max compares per lookup = 0
Avg compares per lookup = 0/0 = 0.0
----------------------------------------------
*** 2012-02-15 10:33:44.766
KCRA: start recovery claims for 0 data blocks
*** 2012-02-15 10:33:44.766
KCRA: buffers claimed = 0/0, eliminated = 0
*** 2012-02-15 10:33:44.766
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 0
Average hash chain = 0/0 = 0.0
Max compares per lookup = 0
Avg compares per lookup = 0/0 = 0.0
----------------------------------------------
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x43, PC: [0x8880c00, kkdliac()+346]
Registers:
%eax: 0xa0f6b170 %ebx: 0x00000003 %ecx: 0xa0f6b170
%edx: 0x00000001 %edi: 0x00000000 %esi: 0x00000000
%esp: 0xbfff1cb8 %ebp: 0xbfff1cd8 %eip: 0x08880c00
%efl: 0x00010286
  kkdliac()+329 (0x8880bef) jnz 0x8880f61
  kkdliac()+335 (0x8880bf5) mov %edx,%eax
  kkdliac()+337 (0x8880bf7) mov 0xc0(%eax),%edx
  kkdliac()+343 (0x8880bfd) mov %edx,0xffffffe0(%ebp)
> kkdliac()+346 (0x8880c00) movzw 0x40(%ebx),%esi
  kkdliac()+350 (0x8880c04)  mov %si,0xffffffd4(%ebp)
  kkdliac()+354 (0x8880c08) movb 0x42(%ebx),%al
  kkdliac()+357 (0x8880c0b) movb %al,0xffffffd6(%ebp)
  kkdliac()+360 (0x8880c0e) movzw 0x8(%ebx),%edx
*** 2012-02-15 10:33:45.029
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [kkdliac()+346] [SIGSEGV] [Address not mapped to object] [0x43] [] []
Current SQL statement for this session:
create table bootstrap$ ( line#         number not null,   obj#           number not null,   
sql_text   varchar2(4000) not null)   storage (initial 50K objno 56 extents (file 1 block 377))
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedmp()+274         call     ksedst()             1 ? 0 ? 0 ? 1 ? 65252838 ?
                                                   2C297862 ?
ssexhd()+1113        call     ksedmp()             3 ? 0 ? 0 ? 0 ? 0 ? 0 ?
__pthread_sighandle  call     00000000             B ? B75CEC90 ? B75CED10 ? 0 ?
r_rt()+122                                         0 ? 0 ?
kkdliac()+346        signal   00000000             B ? B75CEC90 ? B75CED10 ?
ctcdrv()+1688        call     kkdlcob()            BFFF1DC8 ? 2 ? 0 ? 38 ? 0 ?
                                                   0 ? 0 ?
opiexe()+9647        call     ctcdrv()             B749CD78 ? BFFF3258 ?
                                                   BFFF33B0 ?
opiosq0()+1170       call     opiexe()             4 ? 0 ? BFFF379C ?
opiosq()+19          call     opiosq0()            3 ? F ? BFFF4418 ? 0 ?
opiodr()+1133        call     00000000             4A ? F ? BFFF4418 ?
__PGOSF163_rpidrus(  call     opiodr()             4A ? F ? BFFF4418 ? 2 ?
)+145                                              
skgmstack()+137      call     00000000             BFFF3ED0 ? 2 ? BFFF428C ?
                                                   BFFF3EE8 ? BFFF3ED0 ?
                                                   BFFF3EE8 ?
rpidru()+98          call     skgmstack()          BFFF3EE8 ? AF61BC0 ? F618 ?
                                                   821F376 ? BFFF3ED0 ?
rpiswu2()+315        call     00000000             BFFF428C ? 0 ? 0 ? 0 ?
                                                   BFFF3F64 ? F618 ?
rpidrv()+1087        call     rpiswu2()            9D7416FC ? 0 ? BFFF4394 ? 2 ?
                                                   BFFF43B4 ? 0 ?
rpisplu()+298        call     rpidrv()             2 ? 4A ? BFFF4418 ? 8 ?
                                                   9EA3F16C ? B749DE88 ?
rpispl()+28          call     rpisplu()            2 ? 0 ? 0 ? B749DE88 ? BC ?
                                                   0 ? 0 ?
kqlbebs()+781        call     rpispl()             2 ? 0 ? B749DE88 ? BC ? 0 ?
                                                   0 ?
kqlblfc()+106        call     kqlbebs()            0 ? BFFF5590 ?
adbdrv()+2220        call     kqlblfc()            0 ? BFFF5590 ? BFFF5590 ?
opiexe()+10319       call     adbdrv()             B74A022C ? AF5A904 ?
                                                   B74AD040 ? 1 ? 0 ? 1 ?
opiosq0()+1170       call     opiexe()             4 ? 0 ? BFFF6928 ?
kpooprx()+206        call     opiosq0()            3 ? E ? BFFF6A68 ? 24 ?
kpoal8()+599         call     kpooprx()            BFFF7604 ? BFFF7510 ? 13 ?
                                                   1 ? 0 ? 24 ?
opiodr()+1133        call     00000000             5E ? 14 ? BFFF7600 ?
ttcpip()+4250        call     00000000             5E ? 14 ? BFFF7600 ? 0 ?
opitsk()+1077        call     ttcpip()             AF5A900 ? 5E ? BFFF7600 ? 0 ?
                                                   BFFF87D8 ? BFFF87D4 ?
opiino()+1287        call     opitsk()             0 ? 0 ? AF5A900 ? CC349D8 ?
                                                   F1 ? 0 ?
opiodr()+1133        call     00000000             3C ? 4 ? BFFF9B9C ?
opidrv()+418         call     opiodr()             3C ? 4 ? BFFF9B9C ? 0 ?
sou2o()+30           call     opidrv()             3C ? 4 ? BFFF9B9C ?
main()+187           call     sou2o()              BFFF9B80 ? 3C ? 4 ?
                                                   BFFF9B9C ? 890710 ? 0 ?
__libc_start_main()  call     00000000             2 ? BFFF9C04 ? BFFF9C10 ?
+188                                               890518 ? 2 ? 82174E0 ?
 
--------------------- Binary Stack Dump ---------------------
从这里看出数据库是在open过程中，创建bootstrap$错误，这个错误对于现在的我来说，暂时没有办法去解决。
为了能够抢救出客户需要的其中一个用户下面的数据，我不得不采用odu来解决问题。

4.填写ontrol.txt文件

SQL> set pagesize 1000
SQL> set linesize 200
SQL> col name for a50
SQL> select file#,ts#,rfile#,name from v$datafile;

     FILE#        TS#     RFILE# NAME
---------- ---------- ---------- ----------------------------------------------
         1          0          1 /data1z/oracle/oradata/ahcx216/system01
         2          1          2 /data1z/oracle/oradata/ahcx216/undotbs01
         3          3          3 /data1z/oracle/oradata/ahcx216/CITY
         4          4          4 /data1z/oracle/oradata/ahcx216/DATATS
         5          5          5 /data1z/oracle/oradata/ahcx216/indx01
         6          6          6 /data1z/oracle/oradata/ahcx216/tools01
         7          7          7 /data1z/oracle/oradata/ahcx216/users
         8          4          8 /data1z/oracle/oradata/ahcx216/datats02
         9          4          9 /data1z/oracle/oradata/ahcx216/datats03
      ………………

5.登录odu

bash-2.05b$ ./odu

Oracle Data Unloader:Release 3.0.8

Copyright (c) 2008,2009 XiongJun. All rights reserved.

Web: http://www.laoxiong.net
Email: magic007cn@gmail.com

loading default config.......

byte_order little
block_size  8192
db_timezone -7
client_timezone 8
data_path   data
charset_name ZHS16GBK
ncharset_name AL16UTF16
output_format text
lob_storage infile
clob_byte_order little



load control file 'config.txt' successful
loading default control file ......


 ts#   fn  rfn bsize   blocks bf offset filename
---- ---- ---- ----- -------- -- ------ --------------------------------------------
   0    1    1  8192    32000 N       0 /data1z/oracle/oradata/ahcx216/system01
   1    2    2  8192   524288 N       0 /data1z/oracle/oradata/ahcx216/undotbs01
   3    3    3  8192   524288 N       0 /data1z/oracle/oradata/ahcx216/CITY
   4    4    4  8192   524288 N       0 /data1z/oracle/oradata/ahcx216/DATATS
   5    5    5  8192   524288 N       0 /data1z/oracle/oradata/ahcx216/indx01
………………
load control file 'control.txt' successful
loading dictionary data......

6.加载数据字典

ODU> unload dict
CLUSTER C_USER# file_no: 1 block_no: 89
TABLE OBJ$ file_no: 1 block_no: 121
CLUSTER C_OBJ# file_no: 1 block_no: 25
CLUSTER C_OBJ# file_no: 1 block_no: 25
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3
found TABPART$'s obj# 230
found TABPART$'s dataobj#:230,ts#:0,file#:1,block#:1657,tab#:0
found INDPART$'s obj# 234
found INDPART$'s dataobj#:234,ts#:0,file#:1,block#:1689,tab#:0
found TABSUBPART$'s obj# 240
found TABSUBPART$'s dataobj#:240,ts#:0,file#:1,block#:1737,tab#:0
found INDSUBPART$'s obj# 245
found INDSUBPART$'s dataobj#:245,ts#:0,file#:1,block#:1777,tab#:0
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3
found LOB$'s obj# 156
found LOB$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:6
found LOBFRAG$'s obj# 258
found LOBFRAG$'s dataobj#:258,ts#:0,file#:1,block#:1881,tab#:0

7.列出用户

ODU> list user

     USER#   USERNAME                      
----------   ------------------------------
       …………         
        20   HS_ADMIN_ROLE                 
        25   MAS_ADMIN                     
        24   HF_CX                         
        21   WMSYS                         
        22   WM_ADMIN_ROLE                 
        23   CITY                          
        26   BB_ADMIN                      
        27   AQ_ADMIN                      
        28   WH_ADMIN                      
        29   LA_ADMIN                      
       …………

8.列出用户表

          
ODU> list table TL_ADMIN 

      OBJ#   OBJECT_NAME                   
----------   ------------------------------
   2286779   WT_MX_201005                  
     32309   BD_DEALER_308                 
    126172   CDMA_FAVOUR_BAOYUE            
     79675   DAISHENG_TMP                  
     52634   RYSJ_T_308                    
     25577   FEE_ID_T                      
    931167   A136_20080304                 
   1514084   WANGTONG_MOKUAI_2009          
………………

10.导出用户下表

ODU> unload user TL_ADMIN
Unloading user TL_ADMIN's tables.

Unloading table: WT_MX_201005,object ID: 2286779
Unloading segment,storage(Obj#=2286779 DataObj#=2286779 TS#=3 File#=31 Block#=8571 Cluster=0)
25205 rows unloaded


Unloading table: BD_DEALER_308,object ID: 32309
Unloading segment,storage(Obj#=32309 DataObj#=32309 TS#=3 File#=31 Block#=17587 Cluster=0)
60 rows unloaded


Unloading table: CDMA_FAVOUR_BAOYUE,object ID: 126172
Unloading segment,storage(Obj#=126172 DataObj#=126172 TS#=3 File#=3 Block#=30899 Cluster=0)
31 rows unloaded


Unloading table: DAISHENG_TMP,object ID: 79675
Unloading segment,storage(Obj#=79675 DataObj#=342004 TS#=3 File#=31 Block#=19451 Cluster=0)
7504 rows unloaded
…………………………

11.查看导出来数据

-bash-2.05b$ ls -l
-rw-r--r--    1 oracle   dba           658  2月 15 08:43 TL_ADMIN_AMORTIZE.ctl
-rw-r--r--    1 oracle   dba           763  2月 15 08:43 TL_ADMIN_AMORTIZE_HM_COPY.ctl
-rw-r--r--    1 oracle   dba           683  2月 15 08:43 TL_ADMIN_AMORTIZE_HM_COPY.sql
-rw-r--r--    1 oracle   dba         45813  2月 15 08:43 TL_ADMIN_AMORTIZE_HM_COPY.txt
-rw-r--r--    1 oracle   dba           748  2月 15 08:43 TL_ADMIN_AMORTIZE_HM.ctl
-rw-r--r--    1 oracle   dba           686  2月 15 08:43 TL_ADMIN_AMORTIZE_HM.sql
-rw-r--r--    1 oracle   dba        263046  2月 15 08:43 TL_ADMIN_AMORTIZE_HM.txt
-rw-r--r--    1 oracle   dba           777  2月 15 08:43 TL_ADMIN_AMORTIZE_ID.ctl
-rw-r--r--    1 oracle   dba           696  2月 15 08:43 TL_ADMIN_AMORTIZE_ID.sql
……………………………………

12.创建表脚本

-bash-2.05b$ ls -l *.sql
-rw-r--r--    1 oracle   dba           312  2月 15 08:43 TL_ADMIN_A136_20080304.sql
-rw-r--r--    1 oracle   dba           683  2月 15 08:43 TL_ADMIN_AMORTIZE_HM_COPY.sql
-rw-r--r--    1 oracle   dba           686  2月 15 08:43 TL_ADMIN_AMORTIZE_HM.sql
-rw-r--r--    1 oracle   dba           696  2月 15 08:43 TL_ADMIN_AMORTIZE_ID.sql
-rw-r--r--    1 oracle   dba           147  2月 15 08:44 TL_ADMIN_AMORTIZE_OWE.sql
………………
复制这些文件列表，使用UltraEdit编辑文件（recover_create_tab.sql），生成如下脚本
@@TL_ADMIN_A136_20080304.sql
@@TL_ADMIN_AMORTIZE_HM_COPY.sql
@@TL_ADMIN_AMORTIZE_HM.sql
@@TL_ADMIN_AMORTIZE_ID.sql
@@TL_ADMIN_AMORTIZE_OWE.sql
@@TL_ADMIN_AMORTIZE_PAY.sql
然后使用sqlplus登录数据库，直接执行recover_create_tab.sql，创建了所有需要的表

13.加载数据脚本

-bash-2.05b$ ls -l *.ctl
-rw-r--r--    1 oracle   dba           507  2月 15 08:43 TL_ADMIN_A136_20080304.ctl
-rw-r--r--    1 oracle   dba           658  2月 15 08:43 TL_ADMIN_AMORTIZE.ctl
-rw-r--r--    1 oracle   dba           763  2月 15 08:43 TL_ADMIN_AMORTIZE_HM_COPY.ctl
-rw-r--r--    1 oracle   dba           748  2月 15 08:43 TL_ADMIN_AMORTIZE_HM.ctl
-rw-r--r--    1 oracle   dba           777  2月 15 08:43 TL_ADMIN_AMORTIZE_ID.ctl
-rw-r--r--    1 oracle   dba           344  2月 15 08:44 TL_ADMIN_AMORTIZE_OWE.ctl
-rw-r--r--    1 oracle   dba           342  2月 15 08:43 TL_ADMIN_AMORTIZE_PAY.ctl
也同样使用UltraEdit出来，生成文件（recover_load_data.sh）
sqlldr TL_ADMIN/password  control= TL_ADMIN_A136_20080304.ctl
sqlldr TL_ADMIN/password  control= TL_ADMIN_AMORTIZE.ctl
sqlldr TL_ADMIN/password  control= TL_ADMIN_AMORTIZE_HM_COPY.ctl
sqlldr TL_ADMIN/password  control= TL_ADMIN_AMORTIZE_HM.ctl
sqlldr TL_ADMIN/password  control= TL_ADMIN_AMORTIZE_ID.ctl
sqlldr TL_ADMIN/password  control= TL_ADMIN_AMORTIZE_OWE.ctl
sqlldr TL_ADMIN/password  control= TL_ADMIN_AMORTIZE_PAY.ctl
sqlldr TL_ADMIN/password  control= TL_ADMIN_BD_DEALER_308.ctl
sqlldr TL_ADMIN/password  control= TL_ADMIN_BRXZ.ctl
然后在shell中执行recover_load_data.sh脚本导入数据，至此数据恢复完成

注意：recover_create_tab.sql和recover_load_data.sh需要在当前抽取出来数据的目录中。

sqlplus版本不正确，导致ORA-12560

Posted on by

sqlplus登录数据库报ORA-12560

C:\Users\XIFENFEI>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 23:33:31 2012

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

ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

C:\Users\XIFENFEI>sqlplus -v

SQL*Plus: Release 11.2.0.2.0 Production

在这里发现sqlplus的版本信息不正确，我的数据库安装的11.2.0.1，这么怎么显示sqlplus是11.2.0.2，然后进入%ORACLE_HOME%/bin目录下面执行sqlplus，登录成功

C:\Users\XIFENFEI>cd e:\oracle\11_2_0\BIN

e:\oracle\11_2_0\BIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 2月 14 23:44:38 2012

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


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL>

出现了这样的问题，考虑应该是环境变量设置不当导致，查看环境变量发现下面一部分

E:\oracle\timesten\bin;E:\oracle\timesten\ttoracle_home\instantclient_11_2;e:\oracle\11_2_0\bin;

问题的答案就是在这里，因为我的电脑是先安装Oracle，前几天安装了TimesTen，导致环境变量发生了改变，使得我在默认情况下使用sqlplus的时候，自动调用了TimesTen中的sqlplus导致

C:\Users\XIFENFEI>cd E:\oracle\timesten\ttoracle_home\instantclient_11_2

E:\oracle\timesten\ttoracle_home\instantclient_11_2>dir sqlplus*
 驱动器 E 中的卷没有标签。
 卷的序列号是 38D0-2A35

 E:\oracle\timesten\ttoracle_home\instantclient_11_2 的目录

2010/11/19  00:50           655,360 sqlplus.exe
2011/11/08  04:12            84,524 sqlplus.sym
2011/11/08  04:12               554 SQLPLUS_README
               3 个文件        740,438 字节
               0 个目录  9,825,832,960 可用字节

清除掉shared pool中某条sql语句方法

Posted on by

在论坛中看到一个帖子，如何清除掉shared pool中某条sql语句，如果是在10g以前的版本，那估计会比较麻烦，为了删除一条sql语句记录，需要清空整个shared pool，在10g中提供了新的dbms_shared_pool包可以实现该功能(如果该包没有安装，可以通过?/rdbms/admin/dbmspool.sql安装)，下面我通过在11g中试验证明该问题

1.数据库版本信息

SQL> select * from v$version;

BANNER
----------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

2.模拟一条sql语句

SQL> create table xff as
  2  select * from dba_tables
  3  where rownum<10;

表已创建。

SQL> select count(*) from xff;

  COUNT(*)
----------
         9

SQL> col sql_text for a30
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';


ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff

3.打破神话一:权限操作
对表进行权限操作，可以清除该表在shared pool中关于该表记录

SQL> grant select on xff to chf;

授权成功。

SQL> col sql_text for a30
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';


ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff


SQL> revoke select on xff from chf;

撤销成功。

SQL> col sql_text for a30
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';

ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff

证明grant和revoke操作没有清除shared pool中关于该表的sql语句

4.打破神话二:ddl操作
对表进行ddl操作，可以清除该表在shared pool中关于该表记录

SQL> alter table xff add  owner1 varchar2(100);

表已更改。

SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';

ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff

事实证明ddl操作不能达到预期效果，没有清除掉这条sql语句

5.刷新shared pool

SQL> alter system flush shared_pool
  2  ;

系统已更改。

SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';

未选定行

把整个shared pool都刷新了，自然其中的一条sql语句也没有了，在10g前只能通过这种方法实现

6.使用dbms_shared_pool.purge

SQL> select count(*) from xff;

  COUNT(*)
----------
         9

SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';

ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff

SQL> exec dbms_shared_pool.purge('1EFB91B8, 3642190903','C');

PL/SQL 过程已成功完成。

SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';

未选定行

试验证明在shared pool中清除了一条sql记录

7.关于dbms_shared_pool.purge参数说明

purge(name varchar2, flag char DEFAULT 'P', heaps number DEFAULT 1);
--    name
--      The name of the object to keep.  There are two kinds of objects:
--      PL/SQL objects, triggers, sequences, types and Java objects,
--      which are specified by name, and 
--      SQL cursor objects which are specified by a two-part number
--      (indicating a location in the shared pool).  For example:
--        dbms_shared_pool.keep('scott.hispackage')
--      will keep package HISPACKAGE, owned by SCOTT.  The names for
--      PL/SQL objects follows SQL rules for naming objects (i.e., 
--      delimited identifiers, multi-byte names, etc. are allowed).
--      A cursor can be keeped by
--        dbms_shared_pool.keep('0034CDFF, 20348871', 'C')
--      The complete hexadecimal address must be in the first 8 characters.
--      The value for this identifier is the concatenation of the
--      'address' and 'hash_value' columns from the v$sqlarea view.  This
--      is displayed by the 'sizes' call above.
--      Currently 'TABLE' and 'VIEW' objects may not be keeped.
--
--    flag
--      This is an optional parameter.  If the parameter is not specified,
--        the package assumes that the first parameter is the name of a
--        package/procedure/function and will resolve the name.  Otherwise,
--        the parameter is a character string indicating what kind of object
--        to keep the name identifies.  The string is case insensitive.
--        The possible values and the kinds of objects they indicate are 
--        given in the following table:
--          Value        Kind of Object to keep
--          -----      ----------------------
--	      P          package/procedure/function
--	      Q          sequence
--	      R          trigger
--	      T          type
--            JS         java source
--            JC         java class
--	      JR         java resource
--	      JD         java shared data
--	      C          cursor
--      If and only if the first argument is a cursor address and hash-value,
--        the flag parameter should be set to 'C' (or 'c').
--
--    heaps
--      heaps to purge. e.g if heap 0 and heap 6 are to be purged. 
--      1<<0 | 1<<6 => hex 0x41 => decimal 65. so specify heaps=>65.
--      Default is 1 i.e heap 0 which means the whole object will be purged.