oracle 9i数据库存在大量ora_j0**进程

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：oracle 9i数据库存在大量ora_j0**进程

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

1.发现多个ora_j0**进程
可以发现进程重启非常频繁,大概1分钟重启一次，启动ora_j0**的个数为20个

[prmsvr2:/home/oraeye]ps -ef|grep ora_j0|grep -v grep
oracle 213120 1 0 11:01:55 - 0:00 ora_j019_prmdb2
oracle 282808 1 0 11:01:55 - 0:00 ora_j012_prmdb2
oracle 299178 1 0 11:01:54 - 0:00 ora_j004_prmdb2
oracle 434212 1 0 11:01:55 - 0:00 ora_j014_prmdb2
oracle 475238 1 0 11:01:54 - 0:00 ora_j011_prmdb2
oracle 487562 1 0 11:01:54 - 0:00 ora_j008_prmdb2
oracle 614612 1 0 11:01:55 - 0:00 ora_j017_prmdb2
oracle 717008 1 0 11:01:54 - 0:00 ora_j009_prmdb2
oracle 721012 1 0 11:01:54 - 0:00 ora_j006_prmdb2
oracle 749618 1 0 11:01:54 - 0:00 ora_j007_prmdb2
oracle 770268 1 0 11:01:54 - 0:00 ora_j005_prmdb2
oracle 811114 1 0 11:01:55 - 0:00 ora_j015_prmdb2
oracle 831550 1 0 11:01:55 - 0:00 ora_j016_prmdb2
oracle 847940 1 0 11:01:55 - 0:00 ora_j013_prmdb2
oracle 938076 1 0 11:01:54 - 0:00 ora_j010_prmdb2
oracle 942224 1 0 11:01:54 - 0:00 ora_j002_prmdb2
oracle 974968 1 0 11:01:55 - 0:00 ora_j018_prmdb2
oracle 434602 1 0 11:01:54 - 0:00 ora_j000_prmdb2
oracle 668104 1 0 11:01:54 - 0:00 ora_j003_prmdb2
oracle 983424 1 0 11:01:54 - 0:00 ora_j001_prmdb2
[prmsvr2:/home/oraeye]ps -ef|grep ora_j0|grep -v grep
oracle 454676 1 1 11:02:56 - 0:00 ora_j012_prmdb2
oracle 696366 1 1 11:02:56 - 0:00 ora_j011_prmdb2
oracle 749628 1 1 11:02:56 - 0:00 ora_j019_prmdb2
oracle 770276 1 1 11:02:56 - 0:00 ora_j017_prmdb2
oracle 794824 1 1 11:02:56 - 0:00 ora_j010_prmdb2
oracle 827464 1 1 11:02:55 - 0:00 ora_j009_prmdb2
oracle 831560 1 1 11:02:56 - 0:00 ora_j016_prmdb2
oracle 847946 1 1 11:02:56 - 0:00 ora_j014_prmdb2
oracle 888972 1 1 11:02:55 - 0:00 ora_j007_prmdb2
oracle 934064 1 1 11:02:55 - 0:00 ora_j008_prmdb2
oracle 938080 1 1 11:02:56 - 0:00 ora_j013_prmdb2
oracle 942232 1 0 11:02:55 - 0:00 ora_j001_prmdb2
oracle 962760 1 1 11:02:55 - 0:00 ora_j006_prmdb2
oracle 434608 1 1 11:02:55 - 0:00 ora_j004_prmdb2
oracle 528810 1 2 11:02:56 - 0:00 ora_j015_prmdb2
oracle 549228 1 1 11:02:55 - 0:00 ora_j005_prmdb2
oracle 668112 1 1 11:02:55 - 0:00 ora_j003_prmdb2
oracle 709090 1 1 11:02:55 - 0:00 ora_j002_prmdb2
oracle 905720 1 2 11:02:56 - 0:00 ora_j018_prmdb2
oracle 971040 1 1 11:02:55 - 0:00 ora_j000_prmdb2
[prmsvr2:/home/oraeye]ps -ef|grep ora_j0|grep -v grep |wc -l
      20

2.其他参数

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
SQL> show parameter job;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     20
SQL> select count(*) from DBA_JOBS_RUNNING;
  COUNT(*)
----------
         0
SQL> SELECT count(*) FROM v$session_wait where event='jobq slave wait';
  COUNT(*)
----------
        20
SQL> select count(*) from dba_jobs;
  COUNT(*)
----------
         2
SQL> col name for a32
SQL> col value for a24
SQL> col description for a70
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  2    from x$ksppi a,x$ksppcv b
  3   where a.inst_id = USERENV ('Instance')
  4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name
  8  /
Enter value for param: _JOB_QUEUE_INTERVAL
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_JOB_QUEUE_INTERVAL%')
NAME                             VALUE
-------------------------------- ------------------------
DESCRIPTION
----------------------------------------------------------------------
_job_queue_interval              5
Wakeup interval in seconds for job queue co-ordinator

3.对cjq进程做10046

[prmsvr2:/oracle]ps -ef|grep cjq
  oracle  327870       1   0   Feb 10      - 770:39 ora_cjq0_prmdb2
  oracle  929872  794774   0 13:24:59  pts/2  0:00 grep cjq
SQL> oradebug setospid 327870
Oracle pid: 19, Unix process pid: 327870, image: oracle@prmsvr2 (CJQ0)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.
SQL>  oradebug event 10046 trace name context off;
Statement processed.
SQL> oradebug tracefile_name
/oracle/app/admin/prmdb/bdump/prmdb2_cjq0_327870.trc

4.查看cjq的10046文件
发现大量的process startup等待，而且两次批量运行之间的时间间隔在1分钟左右。

WAIT #0: nam='process startup' ela= 59247 p1=74 p2=0 p3=0
WAIT #0: nam='process startup' ela= 51486 p1=74 p2=1 p3=0
WAIT #0: nam='process startup' ela= 51629 p1=74 p2=2 p3=0
WAIT #0: nam='process startup' ela= 48205 p1=74 p2=3 p3=0
WAIT #0: nam='process startup' ela= 47625 p1=74 p2=4 p3=0
WAIT #0: nam='process startup' ela= 55945 p1=74 p2=5 p3=0
WAIT #0: nam='process startup' ela= 47633 p1=74 p2=6 p3=0
WAIT #0: nam='process startup' ela= 51809 p1=74 p2=7 p3=0
WAIT #0: nam='process startup' ela= 57371 p1=74 p2=8 p3=0
WAIT #0: nam='process startup' ela= 50249 p1=74 p2=9 p3=0
WAIT #0: nam='process startup' ela= 51683 p1=74 p2=10 p3=0
WAIT #0: nam='process startup' ela= 47933 p1=74 p2=11 p3=0
WAIT #0: nam='process startup' ela= 47699 p1=74 p2=12 p3=0
WAIT #0: nam='process startup' ela= 47800 p1=74 p2=13 p3=0
WAIT #0: nam='process startup' ela= 47947 p1=74 p2=14 p3=0
WAIT #0: nam='process startup' ela= 52071 p1=74 p2=15 p3=0
WAIT #0: nam='process startup' ela= 47776 p1=74 p2=16 p3=0
WAIT #0: nam='process startup' ela= 47764 p1=74 p2=17 p3=0
WAIT #0: nam='process startup' ela= 47684 p1=74 p2=18 p3=0
WAIT #0: nam='process startup' ela= 47790 p1=74 p2=19 p3=0

通过O记的大力帮助,终于找出了该问题的原因:Bug 4339922: CJQ PROCESS WAKE UP JOB QUEUE PROCESSES EVERY 1 MINUTES.(THERE IS NO JOBS).因为9i的版本oracle不再提供新补丁支持,ora_j0**相关进程不停重启不太占用系统和数据库资源,在不能升级数据库的情况下,可以考虑设置job_queue_processes到一个合适值,然后忽略该问题。

hint指定index的深入理解

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：hint指定index的深入理解

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

模拟环境
创建一个表，含有位图index和b-tree index

SQL> create table t_xifenfei as
  2  select object_id,object_name from dba_objects;
Table created.
SQL> create index b_tree_t_xifenfei on t_xifenfei(object_id);
Index created.
SQL> CREATE BITMAP INDEX  bitmap_t_xifenfei on t_xifenfei(object_name);
Index created.
SQL> BEGIN
  2  dbms_stats.gather_table_stats(USER,'T_XIFENFEI',cascade => true);
  3  END;
  4  /
PL/SQL procedure successfully completed.

无index hint

SQL> SET AUTOT TRACE EXPL STAT
SQL> SELECT OBJECT_ID FROM t_xifenfei;
845708 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |   841K|  4109K|   886   (3)| 00:00:11 |
|   1 |  TABLE ACCESS FULL| T_XIFENFEI |   841K|  4109K|   886   (3)| 00:00:11 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      60525  consistent gets
          0  physical reads
          0  redo size
   15543305  bytes sent via SQL*Net to client
     620649  bytes received via SQL*Net from client
      56382  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     845708  rows processed

这里因为object_id列可能有null值，所以不会使用b_tree_t_xifenfei索引，预料之中事件

index hint b_tree_t_xifenfei

SQL> SET  LINESIZE 150
SQL> SELECT /*+ INDEX(T b_tree_t_xifenfei) */object_id from t_xifenfei t;
845708 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1935372603
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |   841K|  4109K| 18940   (1)| 00:03:48 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T_XIFENFEI        |   841K|  4109K| 18940   (1)| 00:03:48 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                   |       |       |            |          |
|   3 |    BITMAP INDEX FULL SCAN    | BITMAP_T_XIFENFEI |       |       |            |          |
--------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      91537  consistent gets
          0  physical reads
          0  redo size
   42362633  bytes sent via SQL*Net to client
     620649  bytes received via SQL*Net from client
      56382  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     845708  rows processed

这里因为object_id列可能有null值，所以不会使用b_tree_t_xifenfei索引，这里的疑惑是：
就算不会使用b_tree_t_xifenfei index也不应该会使用BITMAP_T_XIFENFEI index，因为使用这个的cost会大于全表扫描

index hint 一个无效index

SQL> SELECT /*+ INDEX(T abc) */object_id from t_xifenfei t;
845708 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1935372603
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |   841K|  4109K| 18940   (1)| 00:03:48 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T_XIFENFEI        |   841K|  4109K| 18940   (1)| 00:03:48 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                   |       |       |            |          |
|   3 |    BITMAP INDEX FULL SCAN    | BITMAP_T_XIFENFEI |       |       |            |          |
--------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      91537  consistent gets
          0  physical reads
          0  redo size
   42362633  bytes sent via SQL*Net to client
     620649  bytes received via SQL*Net from client
      56382  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     845708  rows processed

这里使用了一个无效的index，也使用了BITMAP_T_XIFENFEI，让人更加的感觉奇怪

原因分析
If the INDEX hint specifies no indexes, then the optimizer considers the cost of a scan on each available index on the table and then performs the index scan with the lowest cost. The database can also choose to scan multiple indexes and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan.
如果我们使用hint指定了一个无效的index，优化器会扫描表中所有可以使用的index，然后选择cost最小的index或者index组合，而不会选择全表扫描。
因为我们hint指定b_tree_t_xifenfei index的时候，因为object_id可能有值为空(列没定义为not null)，所以不能使用该index，从而也就是相当于一个无效的index，从而扫描该表的其他可以使用的index，导致使用了位图索引(该类型index不排除null)，而不是全表扫描.
温馨提示:使用hint指定index的时候需要慎重，如果不合适或者无效，可能导致程序效率更低

通过dump分析undo镜像内容

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：通过dump分析undo镜像内容

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一.模拟表并插入数据

SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> create table t_xff(id number,name varchar2(10));
Table created.
SQL> insert into t_xff values(1,'a');
1 row created.
SQL> insert into t_xff values(2,'b');
1 row created.
SQL> insert into t_xff values(3,'c');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL>  select   rowid,
  2   dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3   dbms_rowid.rowid_block_number(rowid)blockno,
  4   dbms_rowid.rowid_row_number(rowid) rowno
  5   from t_xff;
ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAASfUAAEAAAACvAAA          4        175          0
AAASfUAAEAAAACvAAB          4        175          1
AAASfUAAEAAAACvAAC          4        175          2

二.dump当前表数据

SQL> alter system dump datafile 4 block 175;
System altered.
--表中数据
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 1]  61
tab 0, row 1, @0x1f88
tl: 8 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [ 1]  62     <---注意原始值(b)
tab 0, row 2, @0x1f80
tl: 8 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 04
col  1: [ 1]  63

三.修改表中数据(新建会话并不提交)

SQL> select * from t_xff;
        ID NAME
---------- --------------------
         1 a
         2 b
         3 c
SQL> update t_xff set name='F' where id=2;
1 row updated.
SQL> select * from t_xff;
        ID NAME
---------- --------------------
         1 a
         2 F
         3 c

四.dump修改后数据块

SQL> alter system checkpoint;
System altered.
SQL> alter system dump datafile 4 block 175;
System altered.
block_row_dump:
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 02
col  1: [ 1]  61
tab 0, row 1, @0x1f88
tl: 8 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 03
col  1: [ 1]  46     <--数据内容已经修改(由b改为了F)
tab 0, row 2, @0x1f80
tl: 8 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 04
col  1: [ 1]  63
end_of_block_dump

五.找出本次更新操作对应undo块
1.通过v$transaction视图找出

SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
         2         31        750       8155          3          6

2.通过更新块的XID信息找出

Block header dump:  0x010000af
 Object id on Block? Y
 seg/obj: 0x127d4  csc: 0x00.11216d  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x10000a8 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.003.000001ff  0x00c01748.009f.10  C---    0  scn 0x0000.00112130
0x02   0x0002.01f.000002ee  0x00c01fdb.00f5.06  ----    1  fsc 0x0000.00000000
bdba: 0x010000af
data_block_dump,data header at 0xb6ce9664
--这里可以看出Itl=0x02为锁信息
SQL> select name from v$rollname where usn=2;
NAME
------------------------------------------------------------
_SYSSMU2_4228238222$
SQL> alter system dump undo header "_SYSSMU2_4228238222$";
System altered.
 index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x02ee  0x0019  0x0000.0010cc90  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x01    9    0x00  0x02ee  0x0018  0x0000.0010cf00  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333662985
   0x02    9    0x00  0x02ee  0x0000  0x0000.0010cc84  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x03    9    0x00  0x02ee  0x0011  0x0000.00112094  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670810
   0x04    9    0x00  0x02ee  0x0012  0x0000.0010ccc1  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x05    9    0x00  0x02ee  0x0017  0x0000.0010cd13  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x06    9    0x00  0x02ee  0x0004  0x0000.0010ccb9  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x07    9    0x00  0x02ee  0xffff  0x0000.00112119  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670830
   0x08    9    0x00  0x02ee  0x0006  0x0000.0010ccab  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x09    9    0x00  0x02ee  0x000a  0x0000.0010ccf4  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x0a    9    0x00  0x02ee  0x0014  0x0000.0010ccf8  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x0b    9    0x00  0x02ee  0x001a  0x0000.0010d061  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333663886
   0x0c    9    0x00  0x02ee  0x0009  0x0000.0010ccdc  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x0d    9    0x00  0x02ee  0x0001  0x0000.0010ce1f  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333662386
   0x0e    9    0x00  0x02ee  0x001d  0x0000.00112113  0x00c01fdb  0x0000.000.00000000  0x00000001   0x00000000  1333670830
   0x0f    9    0x00  0x02ed  0x0002  0x0000.0010cc79  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x10    9    0x00  0x02ee  0x001e  0x0000.00112017  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1333670781
   0x11    9    0x00  0x02ed  0x000e  0x0000.001120dd  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670813
   0x12    9    0x00  0x02ee  0x000c  0x0000.0010ccd3  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x13    9    0x00  0x02ee  0x0016  0x0000.0010cd2e  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x14    9    0x00  0x02ee  0x0005  0x0000.0010cd0b  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x15    9    0x00  0x02ed  0x0020  0x0000.0010cc9d  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x16    9    0x00  0x02ee  0x000d  0x0000.0010cd33  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x17    9    0x00  0x02ee  0x0013  0x0000.0010cd20  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x18    9    0x00  0x02ee  0x000b  0x0000.0010d051  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333663886
   0x19    9    0x00  0x02ed  0x0015  0x0000.0010cc96  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x1a    9    0x00  0x02ed  0x001b  0x0000.0010d102  0x00c01fda  0x0000.000.00000000  0x00000002   0x00000000  1333664305
   0x1b    9    0x00  0x02ee  0x0010  0x0000.0010d13e  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333664453
   0x1c    9    0x00  0x02c5  0x000f  0x0000.0010cc72  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x1d    9    0x00  0x02ee  0x0007  0x0000.00112115  0x00c01fdb  0x0000.000.00000000  0x00000001   0x00000000  1333670830
   0x1e    9    0x00  0x02ee  0x0021  0x0000.00112035  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670797
   0x1f   10    0x80  0x02ee  0x0003  0x0000.00112157  0x00c01fdb  0x0000.000.00000000  0x00000001   0x00000000  0
   0x20    9    0x00  0x02ed  0x0008  0x0000.0010cca3  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x21    9    0x00  0x02ec  0x0003  0x0000.00112052  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670803
SQL> select to_number('00c01fdb','xxxxxxxxxxx') from dual;
TO_NUMBER('00C01FDB','XXXXXXXXXXX')
-----------------------------------
                           12591067
SQL> select dbms_utility.data_block_address_file(12591067) file#,
  2  dbms_utility.data_block_address_block(12591067) block  from dual;
     FILE#      BLOCK
---------- ----------
         3       8155

3.通过更新块的Uba信息找出

00c01fdb 对应的2进制为:
0000 0000  11 | 00 0000 0001 1111 1101 1011
2+1=3           4096+2048+1024+512+256+128+64+16+8+2+1=8155

六.dump 对应undo数据块

SQL> alter system dump datafile 3 block 8155;
System altered.
uba: 0x00c01fdb.00f5.04 ctl max scn: 0x0000.0010cc60 prv tx scn: 0x0000.0010cc6e
txn start scn: scn: 0x0000.00112028 logon user: 84
 prev brb: 12591059 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
Array Update of 1 rows:
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x010000af  hdba: 0x010000aa
itli: 2  ispac: 0  maxfr: 4858
vect = 3
col  1: [ 1]  62   <---以前的值(b)

试验说明:数据库的undo只是保存修改值的前镜像，而非修改数据块或者行记录的镜像

使用wrap加密pl/sql代码

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：使用wrap加密pl/sql代码

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

1.测试脚本

[oracle@bas ~]$ more pkg_wrap_xff.sql
create or replace package pkg_wrap_xff is
  procedure welcome_in(abc in varchar2);
END pkg_wrap_xff;
/
create or replace package body pkg_wrap_xff is
  procedure welcome_in(abc in varchar2) as
  begin
    dbms_output.put_line('welcome ' || abc);
  end;
END pkg_wrap_xff;
/

2.创建并测试包

SQL> @pkg_wrap_xff
Package created.
Package body created.
SQL> set serveroutput on
SQL> exec pkg_wrap_xff.welcome_in('xifenfei');
welcome xifenfei
PL/SQL procedure successfully completed.

3.查看包内容

SQL> SELECT TEXT FROM dba_source a WHERE a.name='PKG_WRAP_XFF';
TEXT
--------------------------------------------------------------
package pkg_wrap_xff is
  procedure welcome_in(abc in varchar2);
END pkg_wrap_xff;
package body pkg_wrap_xff is
  procedure welcome_in(abc in varchar2) as
  begin
    dbms_output.put_line('welcome ' || abc);
  end;
END pkg_wrap_xff;
9 rows selected.

4.使用wrap加密

[oracle@bas ~]$ wrap iname=pkg_wrap_xff.sql oname=pkg_wrap_xff.plb
PL/SQL Wrapper: Release 10.2.0.1.0- 64bit Production on Tue Apr 03 11:16:52 2012
Copyright (c) 1993, 2004, Oracle.  All rights reserved.
Processing pkg_wrap_xff.sql to pkg_wrap_xff.plb
[oracle@bas ~]$ ll pkg_wrap_xff*
-rw-r--r--  1 oracle oinstall 634 Apr  3 11:16 pkg_wrap_xff.plb
-rw-r--r--  1 oracle oinstall 273 Apr  3 10:58 pkg_wrap_xff.sql
[oracle@bas ~]$ more pkg_wrap_xff.plb
create or replace package pkg_wrap_xff wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
9
53 85
W36vGRTVGRHdbwYRR6PgEKn/uJgwg1zZf9OpynQ2Z/aHUmNhYcN/NpFphdvMis61lthVP41T
adMRoYz9KTALorx2DjxUFXms0VvEXmDignlfcQjICNxh0Rmhsp2KsCjohpTO
/
create or replace package body pkg_wrap_xff wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
96 ce
QzGnt9RcmxQyUfes+xtqkeZypV0wg1zwqNPWfI6iCq2Ve93Dd2BzFE8hHRRYZ/LM86Rx2o0E
y67F1TM7QgP9WoGRBCUelGt4QvSOmwMecLCe57PVPD5lbxyJLK26scjDS8soGPzcCDysP+WR
C0zeZ9lSlyLXqNex8XpUxi7tILux/gNr1FIOWaBRhYqgTZ754pVDNlG4SXE=
/

4.测试加密包

SQL> drop package PKG_WRAP_XFF;
Package dropped.
SQL> @pkg_wrap_xff.plb
Package created.
Package body created.
SQL> set serveroutput on
PL/SQL procedure successfully completed.
SQL> exec pkg_wrap_xff.welcome_in('www.xifenfei.com');
welcome www.xifenfei.com
PL/SQL procedure successfully completed.

查看加密包内容

SQL> set pagesize 1000
SQL> SELECT TEXT FROM dba_source a WHERE a.name='PKG_WRAP_XFF';
TEXT
--------------------------------------------------------------------------------
package pkg_wrap_xff wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
9
53 85
W36vGRTVGRHdbwYRR6PgEKn/uJgwg1zZf9OpynQ2Z/aHUmNhYcN/NpFphdvMis61lthVP41T
adMRoYz9KTALorx2DjxUFXms0VvEXmDignlfcQjICNxh0Rmhsp2KsCjohpTO
package body pkg_wrap_xff wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
96 ce
QzGnt9RcmxQyUfes+xtqkeZypV0wg1zwqNPWfI6iCq2Ve93Dd2BzFE8hHRRYZ/LM86Rx2o0E
y67F1TM7QgP9WoGRBCUelGt4QvSOmwMecLCe57PVPD5lbxyJLK26scjDS8soGPzcCDysP+WR
C0zeZ9lSlyLXqNex8XpUxi7tILux/gNr1FIOWaBRhYqgTZ754pVDNlG4SXE=

原则上来说wrap加密是不可逆的过程，所以可以通过使用wrap加密，实现了屏蔽代码的作用，确保了自己的知识产权。其实oracle本身很多的系统包也是通过这种方法进行加密处理，以保护oracle的产权。

通过hash_value获取sql语句执行计划

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：通过hash_value获取sql语句执行计划

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

当我们没有权限访问业务表，但是需要查看shared pool中部分sql语句的执行计划，原则上来说，查询v$sql_plan视图结合hash_value可以实现，但是因为这个是表格形式，看起来不太美观，和我们长看的执行计划有一定的出入，这里提供两个脚本，实现查看该种情况下的执行计划。
oracle 9i

[oracle@xifenfei ~]$ more get_plan.sql
set pagesize 0
set linesize 150
set serveroutput on size 10000
col plan_table_output format a125
undefine hash_value
set verify off feedback off
var hash_value varchar2(20)
begin
  :hash_value := '&hash_value';
end;
/
insert into plan_table
      (statement_id,timestamp,operation,options,object_node,object_owner,object_name,
       optimizer,search_columns,id,parent_id,position,cost,cardinality,bytes,other_tag,
       partition_start,partition_stop,partition_id,other,distribution,
       cpu_cost,io_cost,temp_space,access_predicates,filter_predicates
      )
select distinct hash_value,sysdate,operation,options,object_node,object_owner,object_name,
       optimizer,search_columns,id,parent_id,position,cost,cardinality,bytes,other_tag,
       partition_start,partition_stop,partition_id,other,distribution,
       cpu_cost,io_cost,temp_space,access_predicates,filter_predicates
  from v$sql_plan
 where hash_value = :hash_value
/
col piece noprint
select distinct piece,sql_text from v$sqltext where hash_value = :hash_value order by piece
/
@?/rdbms/admin/utlxplp.sql
set linesize 80
set verify on feedback on pagesize 1000

oracle 10g/11g

[oracle@xifenfei ~]$ more get_plan.sql
set pagesize 0
set linesize 150
set serveroutput on size 10000
col plan_table_output format a125
undefine hash_value
set verify off feedback off
var hash_value varchar2(20)
begin
  :hash_value := '&hash_value';
end;
/
insert into plan_table
      (statement_id,timestamp,operation,options,object_node,object_owner,object_name,
       optimizer,search_columns,id,parent_id,position,cost,cardinality,bytes,other_tag,
       partition_start,partition_stop,partition_id,other,distribution,
       cpu_cost,io_cost,temp_space,access_predicates,filter_predicates,
       plan_id,OBJECT_ALIAS,DEPTH,PROJECTION,TIME,QBLOCK_NAME
      )
select distinct hash_value,sysdate,operation,options,object_node,object_owner,object_name,
       optimizer,search_columns,id,parent_id,position,cost,cardinality,bytes,other_tag,
       partition_start,partition_stop,partition_id,other,distribution,
       cpu_cost,io_cost,temp_space,access_predicates,filter_predicates,
       :hash_value,OBJECT_ALIAS,DEPTH,PROJECTION,TIME,QBLOCK_NAME
  from v$sql_plan
 where hash_value = :hash_value
/
col piece noprint
select distinct piece,sql_text from v$sqltext where hash_value = :hash_value order by piece
/
@?/rdbms/admin/utlxplp.sql
set linesize 80
set verify on feedback on pagesize 1000

使用方法

SQL> SELECT hash_value FROM V$SQL WHERE SQL_TEXT
  2  LIKE 'SELECT * FROM SYS.SMON_SCN_TIME';
HASH_VALUE
----------
3019898357
SQL> @get_plan.sql
Enter value for hash_value: 3019898357
SELECT * FROM SYS.SMON_SCN_TIME
-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| SMON_SCN_TIME |     1 |  1163 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

补充说明
其实9i和10g/11g中得出执行计划的出入就是在plan_table表上
在9i中：plan_table表需要通过脚本创建并且授权

SQL> connect / as sysdba;
SQL> @?/rdbms/admin/utlxplan.sql;
SQL> create public synonym plan_table for plan_table; --建立同义词
SQL> grant all on plan_table to public;--授权所有用户

在10g/11g中：plan_table表系统自带，不需要创建。因为plan_table表中含有plan_id列，而得出执行计划时该列不能为空，所以上面脚本中对于10/11g数据库必须要填充plan_id值

通过修改基表(link$)让非public dblink变为public

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：通过修改基表(link$)让非public dblink变为public

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有些朋友创建了一个非public的dblink，现在该数据库的其他用户需要去使用该dblink，在正常情况下无访问权限，需要重新建一个dblink，或者将原dblink修改为public。但是由于忘记了原dblink的目标段的密码，使得创建或者修改dblink的步骤无法进行下去。这里通过修改基表(link$)，解决该问题。

创建dblink

SQL> show user;
USER is "SYS"
SQL> create database link "xff_dblink"
  2  connect to TEST
  3  identified by "test"
  4  using '11.1.1.1:1521/mcrm';
Database link created.
SQL> select * from dba_db_links where db_link like 'XFF_DBLINK%';
OWNER DB_LINK                                     USERN HOST               CREATED
----- ------------------------------------------- ----- ------------------ --------
SYS   XFF_DBLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM  TEST  11.1.1.1:1521/mcrm 29-MAR-12
SQL> select sysdate from dual@xff_dblink;
SYSDATE
---------
29-MAR-12
SQL> CONN TEST/TEST
Connected.
SQL> SELECT SYSDATE FROM DUAL@XFF_DBLINK;
SELECT SYSDATE FROM DUAL@XFF_DBLINK
                         *
ERROR at line 1:
ORA-02019: connection description for remote database not found
--该dblink不是public的，所以test用户无权访问

dblink变为public类型

SQL> CONN / AS SYSDBA
Connected.
SQL> set long 1000
SQL> select  text from dba_views where view_name='DBA_DB_LINKS';
TEXT
-------------------------------------------------------------------
select u.name, l.name, l.userid, l.host, l.ctime
from sys.link$ l, sys.user$ u
where l.owner# = u.user#
--查询出dblink相关的基表有link$和user$
SQL> desc sys.link$
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 OWNER#                        NOT NULL NUMBER
 NAME                          NOT NULL VARCHAR2(128)
 CTIME                         NOT NULL DATE
 HOST                                   VARCHAR2(2000)
 USERID                                 VARCHAR2(30)
 PASSWORD                               VARCHAR2(30)
 FLAG                                   NUMBER
 AUTHUSR                                VARCHAR2(30)
 AUTHPWD                                VARCHAR2(30)
 PASSWORDX                              RAW(128)
 AUTHPWDX                               RAW(128)
SQL> select owner# from sys.link$ where name like 'XFF_DBLINK%';
    OWNER#
----------
         0
--XFF_DBLINK对应的用户标识记录在link$.owner#中
SQL> SELECT USER#,NAME FROM USER$ WHERE name in ('SYS','PUBLIC');
     USER# NAME
---------- ------------------------------
         1 PUBLIC
         0 SYS
--现link$.owner#值为0，表示该dblink所属用户为SYS，现在让该dblink变为public
--现需要让该dblink变为public，需要做的是修改link$.owner#的值为1
SQL> UPDATE LINK$ SET OWNER#=1 WHERE name like 'XFF_DBLINK%';
1 row updated.
SQL> COMMIT;
Commit complete.
--需要刷新shared_pool
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
--查看dblink所属者，已经修改为public
SQL> select owner from dba_db_links where db_link like 'XFF_DBLINK%';
OWNER
----------
PUBLIC
--测试dblink是否成功
SQL> CONN TEST/TEST
Connected.
SQL> SELECT SYSDATE FROM DUAL@XFF_DBLINK;
SYSDATE
---------
29-MAR-12

关于9I中sga_max_size参数描述

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：关于9I中sga_max_size参数描述

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

不设置sga_max_size参数

SQL> show sga;
Total System Global Area  420549952 bytes
Fixed Size                   451904 bytes
Variable Size             201326592 bytes
Database Buffers          218103808 bytes
Redo Buffers                 667648 bytes
SQL> select sum(bytes)from v$sgastat;
SUM(BYTES)
----------
 420538688
SQL> !ipcs -m
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x9ba476a4 65536      oracle    640        440401920  65
SQL> alter system set db_cache_size=300M;
alter system set db_cache_size=300M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

1)当sga_max_size不设置时，数据库启动时，会使用数据库默认分配sga大小为初始化值
2)当sga_max_size不设置时，不能在线扩展组件内存大小(使得sga大于当前大小)

设置sga_max_size参数

SQL>  alter system set sga_max_size=600M scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  638654020 bytes
Fixed Size                   452164 bytes
Variable Size             419430400 bytes
Database Buffers          218103808 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> !ipcs -m
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x9ba476a4 98304      oracle    640        658505728  65
SQL> select sum(bytes)from v$sgastat;
SUM(BYTES)
----------
 420538948
SQL> alter system set db_cache_size=405M;
System altered.
SQL> select 638654020/1024/1024 from dual;
638654020/1024/1024
-------------------
         609.067936
说明sga中剩余空闲内存0.01M
SQL> select 609.067936-sum(bytes)/1024/1024 from v$sgastat;
609.067936-SUM(BYTES)/1024/1024
-------------------------------
                     .010742244
继续增加组件大小
SQL> alter system set db_cache_size=416M;
System altered.
SQL> select sum(bytes)/1024/1024 from v$sgastat;
SUM(BYTES)/1024/1024
--------------------
          609.057194
SQL>  select 638654020/1024/1024 from dual;
638654020/1024/1024
-------------------
         609.067936
SQL> alter system set db_cache_size=417M;
alter system set db_cache_size=417M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache
这里显示，当db_cache_size增加到415M的时候，sga只有0.01M剩余
但是直到db_cache_size增加到417的时候才报错

1)当sga有剩余时，可以动态调整sga中的部分组件(java_pool_size不能在线设置)
2)当sga没有剩余时，如果继续增加某组件的内存，在一定的范围内，sga会自动调整其他组件大小，以实用该值增加

关于sga_max_size总结
1)如果你的系统内存比较紧张，对停机时间要求不是特别严格，那可以不设置sga_max_size参数，这样在重启数据库设置sga组件的时候，不会因忘记设置sga_max_size而导致不能正常启动
2)如果你的系统内存充足，对停机有严格限制，那建议设置一个较大的sga_max_size，后续可以根据需求动态在线调整sga部分组件

关于SMON_SCN_TIME若干问题说明

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：关于SMON_SCN_TIME若干问题说明

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

1.SMON_SCN_TIME表基础知识
0)作用
由smon收集scn和time映射关系，用于flashback/查询scn和time对应关系等操作

1)保留条数
官方文档给出说明instance number N * 12 times per hour * 24 hours * 5 days = 1440N rows，因为每次的时间间隔不是非常准确的5分钟，所以在具体的条数在实际生产环境中有一定的出入

2)采集和删除
smon进程没5分钟采集一次插入到SMON_SCN_TIME表中，同时将删除历史数据(超过5天前数据)，采用下面语句
delete from smon_scn_time where thread=0 and time_mp = (select min(time_mp) from smon_scn_time where thread=0)，如果有时候index出了问题，导致该语句执行很慢

3)当查询scn对应time，如果scn超过SMON_SCN_TIME表范围，将提示错误；或者查询time对应的scn，如果超过范围也同样报错。

2.当SMON_SCN_TIME表出现问题时，truncate操作语句

--找出CLUSTER
Select dbms_metadata.get_ddl('TABLE','SMON_SCN_TIME','SYS') FROM DUAL ;
9i
truncate CLUSTER "SYS"."SMON_SCN_TO_TIME";
10g
truncate  CLUSTER "SYS"."SMON_SCN_TO_TIME";
11g
truncate CLUSTER "SYS"."SMON_SCN_TO_TIME_AUX";

3.停止/开启smon进程收集scn信息

stop
alter system set events '12500 trace name context forever, level 10';
start
alter system set events '12500 trace name context off';

4.index异常处理

--找出index
Select index_name,index_type,owner from dba_indexes where table_name='SMON_SCN_TIME';
--对应index创建语句
Select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_TIM_IDX','SYS') FROM DUAL;
--重建index
drop index smon_scn_time_scn_idx;
drop index smon_scn_time_tim_idx;
create unique index smon_scn_time_scn_idx on smon_scn_time(scn);
create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp);
analyze table smon_scn_time validate structure cascade online;
--rebuild index
alter index sys.smon_scn_time_scn_idx rebuild online;
alter index sys.smon_scn_time_tim_idx rebuild online;
analyze table smon_scn_time validate structure cascade online;

补充知识点scn计算方法SCN=(SCN_WRP * 4294967296) + SCN_BAS

授权用户访问数据字典三种方式

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：授权用户访问数据字典三种方式

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在很多时候，希望给用户最小的权限，让其访问系统数据字典，检查数据库的运行状态。这种事情在乙方的工作中非常常见。下面介绍三种方法处理这个问题
0.select any table权限
这里说明select any table不能直接访问数据字典

SQL> conn / as sysdba
Connected.
SQL> create user xff_any identified by xifenfei;
User created.
SQL> grant connect,select any table to xff_any;
Grant succeeded.
SQL> conn xff_any/xifenfei
Connected.
SQL>  select count(*) from dba_users;
 select count(*) from dba_users
                      *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from chf.t_xff;
  COUNT(*)
----------
      2770

select any table默认情况下，只能访问业务的表，但是不能访问数据字典的数据。所以单纯的这个属性不能满足需求。

1.SELECT ANY DICTIONARY权限

SQL> conn / as sysdba
Connected.
SQL> create user xff_DICTIONARY identified by xifenfei;
User created.
SQL> grant connect to xff_DICTIONARY;
Grant succeeded.
SQL> conn xff_DICTIONARY/xifenfei
Connected.
SQL> select count(*) from dba_users;
select count(*) from dba_users
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn / as sysdba
Connected.
SQL> grant SELECT ANY DICTIONARY to xff_DICTIONARY;
Grant succeeded.
SQL> conn xff_DICTIONARY/xifenfei
Connected.
SQL> select count(*) from dba_users;
  COUNT(*)
----------
        32
SQL> select count(*) from chf.t_xff;
select count(*) from chf.t_xff
                         *
ERROR at line 1:
ORA-00942: table or view does not exist

这里可以看出SELECT ANY DICTIONARY权限只能访问数据字典，不能访问业务的表，访问业务的表需要另外授权

2.SELECT_CATALOG_ROLE角色

SQL> conn / as sysdba
Connected.
SQL> create user xff_CATALOG identified by xifenfei;
User created.
SQL> grant connect,SELECT_CATALOG_ROLE to xff_CATALOG;
Grant succeeded.
SQL> conn xff_CATALOG/xifenfei
Connected.
SQL> select count(*) from dba_users;
  COUNT(*)
----------
        33
SQL> select count(*) from chf.t_xff;
select count(*) from chf.t_xff
                         *
ERROR at line 1:
ORA-00942: table or view does not exist

这里可以看出SELECT_CATALOG_ROLE权限只能访问数据字典，不能访问业务的表，访问业务的表需要另外授权

3.O7_DICTIONARY_ACCESSIBILITY参数

SQL> conn / as sysdba
Connected.
SQL> create user xff_O7 identified by xifenfei;
User created.
SQL> grant connect to xff_o7;
Grant succeeded.
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true;
alter system set O7_DICTIONARY_ACCESSIBILITY=true
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  368263168 bytes
Fixed Size                  1345016 bytes
Variable Size             306186760 bytes
Database Buffers           54525952 bytes
Redo Buffers                6205440 bytes
Database mounted.
Database opened.
SQL> conn xff_o7/xifenfei
Connected.
SQL> select count(*) from dba_users;
  COUNT(*)
----------
        34

这里通过O7_DICTIONARY_ACCESSIBILITY和SELECT ANY TABLE权限，实现访问业务数据和数据字典

跟踪ORACLE非当前会话

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：跟踪ORACLE非当前会话

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

使用oradebug

session 1
--查询出需要跟踪会话v$process.pid
session 2
SQL> oradebug SETORAPID 15
Unix process pid: 14851, image: oracle@xifenfei (TNS V1-V3)
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 4
Statement processed.
--执行session 1
session 1
SQL> oradebug EVENT 10046 trace name context off
Statement processed.
SQL> oradebug TRACEFILE_NAME
/u01/oracle/admin/xifenfei/udump/xff_ora_14851.trc

使用dbms_system.set_sql_trace_in_session

session 1
--查询出需要跟踪会话的sid，SERIAL#
session 2
SQL> exec dbms_system.set_sql_trace_in_session(12,130,true);
PL/SQL procedure successfully completed.
--session 1执行sql
session2关闭跟踪
SQL> exec dbms_system.set_sql_trace_in_session(12,130,false);
PL/SQL procedure successfully completed.
--通过session 1找出trace文件