eygle.com   eygle.com
eygle.com  
 

« December 12, 2004 | Blog首页 | December 19, 2004 »


December 16, 2004

使用触发器实现数据库级守护,防止DDL操作

作者:eygle

出处:http://blog.eygle.com

不管是有意还是无意的,你可能会遇到数据库中重要的数据表等对象被drop掉的情况,这可能会给我们带来巨大的损失.

通过触发器，我们可以实现对于表等对象的数据库级守护，禁止用户drop操作.

以下是一个简单的范例，供参考:

REM this script can be used to monitor a object
REM deny any drop operation on it.
CREATE OR REPLACE TRIGGER trg_dropdeny
   BEFORE DROP ON DATABASE
BEGIN
   IF LOWER (ora_dict_obj_name ()) = 'test'
   THEN
      raise_application_error (num      => -20000,
                               msg      =>    '你疯了,想删除表 '
                                           || ora_dict_obj_name ()
                                           || ' ?!!!!!'
                                           || '你完了，警察已在途中.....'
                              );
   END IF;
END;
/

测试效果:

 

SQL> connect scott/tiger
Connected.
SQL> create table test as select * from dba_users;

Table created.

SQL> connect / as sysdba
Connected.
SQL> create or replace trigger trg_dropdeny
  2     before drop on database   
  3  begin
  4        if lower(ora_dict_obj_name()) = 'test'        
  5        then
  6        raise_application_error(
  7           num => -20000,
  8           msg => '你疯了,想删除表 ' || ora_dict_obj_name() || ' ?!!!!!' ||'你完了，警察已在途中.....');
  9        end if;
 10     end;
 11  /

Trigger created.

SQL> connect scott/tiger
Connected.
SQL> drop table test;
drop table test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: 你疯了,想删除表 TEST ?!!!!!你完了，警察已在途中.....
ORA-06512: at line 4

 

Oracle从Oracle8i开始,允许实施DDL事件trigger，可是实现对于DDL的监视及控制,以下是一个进一步的例子:

create or replace trigger ddl_deny
before create or alter or drop or truncate on database
declare
  l_errmsg varchar2(100):= 'You have no permission to this operation';
begin
  if ora_sysevent = 'CREATE' then
     raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
  elsif ora_sysevent = 'ALTER' then
    raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
  elsif ora_sysevent = 'DROP' then
    raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
  elsif ora_sysevent = 'TRUNCATE' then
    raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
  end if;

exception
  when no_data_found then
    null;
end;
/

 

我们看一下效果:


[oracle@jumper tools]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Sun Oct 31 11:38:25 2004

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

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> set echo on
SQL> @ddlt
SQL> create or replace trigger ddl_deny
2 before create or alter or drop or truncate on database
3 declare
4 l_errmsg varchar2(100):= 'You have no permission to this operation';
5 begin
6 if ora_sysevent = 'CREATE' then
7 raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
8 elsif ora_sysevent = 'ALTER' then
9 raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
10 elsif ora_sysevent = 'DROP' then
11 raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
12 elsif ora_sysevent = 'TRUNCATE' then
13 raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
14 end if;
15
16 exception
17 when no_data_found then
18 null;
19 end;
20 /

Trigger created.

SQL>
SQL>
SQL> connect scott/tiger
Connected.
SQL> create table t as select * from test;
create table t as select * from test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: SCOTT.T You have no permission to this operation
ORA-06512: at line 5

SQL> alter table test add (id number);
alter table test add (id number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: SCOTT.TEST You have no permission to this operation
ORA-06512: at line 7

SQL> drop table test;
drop table test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: SCOTT.TEST You have no permission to this operation
ORA-06512: at line 9

SQL> truncate table test;
truncate table test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: SCOTT.TEST You have no permission to this operation
ORA-06512: at line 11

 

我们可以看到,ddl语句都被禁止了,如果你不是禁止,可以选择把执行这些操作的用户及时间记录到另外的临时表中.以备查询.

 

 

Posted by eygle at 10:49 PM | Comments (3)

如何获得跟踪文件名称

作者:eygle

出处:http://blog.eygle.com

当我们使用sql_trace/10046等事件进行进程跟踪时，会生成跟踪文件.跟踪文件名称由以下几部分组成:

<sid>_ora_<pid>.trc

以下脚本用户获得跟踪文件名称:

For Unix:

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Oct 8 12:08:09 2004

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


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> set echo on
SQL> @gettrcnameunix
SQL> SELECT       d.VALUE
  2         || '/'
  3         || LOWER (RTRIM (i.INSTANCE, CHR (0)))
  4         || '_ora_'
  5         || p.spid
  6         || '.trc' trace_file_name
  7    FROM (SELECT p.spid
  8            FROM v$mystat m, v$session s, v$process p
  9           WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
 10         (SELECT t.INSTANCE
 11            FROM v$thread t, v$parameter v
 12           WHERE v.NAME = 'thread'
 13             AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
 14         (SELECT VALUE
 15            FROM v$parameter
 16           WHERE NAME = 'user_dump_dest') d
 17  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/opt/oracle/admin/hsbill/udump/hsbill_ora_29630.trc

For Nt:

 

 
SQL> SELECT    d.VALUE
  2         || '\'
  3         || LOWER (RTRIM (i.INSTANCE, CHR (0)))
  4         || '_ora_'
  5         || p.spid
  6         || '.trc' trace_file_name
  7    FROM (SELECT p.spid
  8            FROM v$mystat m, v$session s, v$process p
  9           WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
 10         (SELECT t.INSTANCE
 11            FROM v$thread t, v$parameter v
 12           WHERE v.NAME = 'thread'
 13             AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
 14         (SELECT VALUE
 15            FROM v$parameter
 16           WHERE NAME = 'user_dump_dest') d
 17  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
e:\oracle\admin\eygle\udump\eygle_ora_3084.trc

 

你可以在这里下载以上脚本:ForUnix ForNt

 

Posted by eygle at 7:59 PM | Comments (0)

使用dbms_rowid包获得rowid的详细信息

作者:eygle

出处:http://blog.eygle.com

Rowid中包含了记录的详细信息，通过dbms_rowid包可以获得这些信息.本文通过一个定义自定义函数介绍该package的使用.

create or replace function get_rowid
(l_rowid in varchar2)
return varchar2
is
ls_my_rowid  varchar2(200);          
rowid_type  number;          
object_number  number;          
relative_fno  number;          
block_number  number;          
row_number  number;  
begin
 dbms_rowid.rowid_info(l_rowid,rowid_type,object_number,relative_fno, block_number, row_number);          
 ls_my_rowid := 'Object# is      :'||to_char(object_number)||chr(10)||
  'Relative_fno is :'||to_char(relative_fno)||chr(10)||
  'Block number is :'||to_char(block_number)||chr(10)||
  'Row number is   :'||to_char(row_number);
 return ls_my_rowid ;
end;          
/

 

我们看一下其用法:

 

[oracle@jumper tools]$ sqlplus scott/tiger

SQL*Plus: Release 9.2.0.4.0 - Production on Sun Nov 7 12:30:19 2004

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


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> set echo on
SQL> @f_get_rowid
SQL> create or replace function get_rowid
  2  (l_rowid in varchar2)
  3  return varchar2
  4  is
  5  ls_my_rowid        varchar2(200);
  6  rowid_type number;
  7  object_number      number;
  8  relative_fno       number;
  9  block_number       number;
 10  row_number number;
 11  begin
 12   dbms_rowid.rowid_info(l_rowid,rowid_type,object_number,relative_fno, block_number, row_number);
 13   ls_my_rowid := 'Object# is         :'||to_char(object_number)||chr(10)||
 14                  'Relative_fno is :'||to_char(relative_fno)||chr(10)||
 15                  'Block number is :'||to_char(block_number)||chr(10)||
 16                  'Row number is   :'||to_char(row_number);
 17   return ls_my_rowid ;
 18  end;
 19  /

Function created.

SQL> 
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select rowid,a.* from dept a;

ROWID                  DEPTNO DNAME          LOC
------------------ ---------- -------------- -------------
AAABiPAABAAAFRSAAA         10 ACCOUNTING     NEW YORK
AAABiPAABAAAFRSAAB         20 RESEARCH       DALLAS
AAABiPAABAAAFRSAAC         30 SALES          CHICAGO
AAABiPAABAAAFRSAAD         40 OPERATIONS     BOSTON


SQL> col row_id for a60
SQL> select get_rowid('AAABiPAABAAAFRSAAA') row_id from dual;

ROW_ID
------------------------------------------------------------
Object# is      :6287
Relative_fno is :1
Block number is :21586
Row number is   :0


SQL> select get_rowid('AAABiPAABAAAFRSAAB') row_id from dual;

ROW_ID
------------------------------------------------------------
Object# is      :6287
Relative_fno is :1
Block number is :21586
Row number is   :1


SQL>

-The End-

Posted by eygle at 3:40 PM | Comments (0)

Oracle进程内存结构-如何察看Oracle进程消耗的内存

作者:eygle

出处:http://blog.eygle.com

经常有人问到如何在Unix下确定进程消耗的内存资源，以及为何Top工具的显示非常高。
有人说Top的输出不精确，这种说法是不确切的。实际上是Top输出显示的Oracle进程内存使用，包含了SGA部分。这也是SGA的意义所在。
SGA可以被共享，可以被所有进程所访问，在进程的寻址空间里就包含了SGA的大小。

至于如何更为精确的确定进程的内存消耗，本文简要介绍如下（在QuickIO下，你可能无法看到本文描述情况）：

1.系统平台及数据库版本

$ uname -a
SunOS billing 5.8 Generic_108528-23 sun4u sparc SUNW,Ultra-4

Sun Microsystems Inc.   SunOS 5.8       Generic Patch   October 2001
$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Sun Nov 28 10:48:00 2004

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


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Solaris: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> show sga

Total System Global Area  253200688 bytes
Fixed Size                   731440 bytes
Variable Size             167772160 bytes
Database Buffers           83886080 bytes
Redo Buffers                 811008 bytes
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

 

2.Top输出

 

$ top

load averages:  0.12,  0.05,  0.04         billing                10:49:52
54 processes:  52 sleeping, 1 stopped, 1 on cpu
CPU states: 99.3% idle,  0.0% user,  0.3% kernel,  0.4% iowait,  0.0% swap
Memory: 2.0G real, 1.2G free, 404M swap in use, 5.1G swap free

   PID USERNAME THR PR NCE  SIZE   RES STATE   TIME FLTS    CPU COMMAND
 10027 oracle     1 58   0 2568K 1680K cpu03   0:00    0  0.09% top
 10052 oracle     1 58   0  322M  275M sleep  21:18    0  0.01% oracle
 11264 oracle 22 58  0  329M 275M sleep  59:01 0  0.00% oracle
 10056 oracle     1 58   0  322M  275M sleep   3:04    0  0.00% oracle
 11262 oracle    14 58   0  331M  270M sleep  73:55    0  0.00% oracle
 11260 oracle   258 59   0  333M  274M sleep  52:46    0  0.00% oracle
 11266 oracle     1 58   0  325M  276M sleep  26:50    0  0.00% oracle
 10054 oracle     1 59   0  322M  275M sleep  18:27    0  0.00% oracle
 23519 oracle     1 55   0  322M  278M sleep   0:50    0  0.00% oracle
 11258 oracle     1 59   0  323M  271M sleep   0:31    0  0.00% oracle
 11285 oracle     1 58   0 17.3M 3848K sleep   0:20    0  0.00% tnslsnr
 11270 oracle     1 58   0  322M  271M sleep   0:06    0  0.00% oracle
  2990 oracle     1 59   0  322M  276M sleep   0:04    0  0.00% oracle
  9872 oracle     1 48   0 1880K 1192K sleep   0:00    0  0.00% ksh
 23498 oracle     1 58   0  323M  278M sleep   0:00    0  0.00% oracle

 

3.Pmap输出及进程内存计算

 

$ ps -ef|grep ora_
  oracle  9881  9872  0 10:49:57 pts/2    0:00 grep ora_
  oracle 11258     1  0   Jul 19 ?        0:31 ora_pmon_hsbill
  oracle 11260     1  0   Jul 19 ?       52:47 ora_dbw0_hsbill
  oracle 11262     1  0   Jul 19 ?       73:55 ora_lgwr_hsbill
  oracle 11264     1  0   Jul 19 ?       59:01 ora_ckpt_hsbill
  oracle 11266     1  0   Jul 19 ?       26:50 ora_smon_hsbill
  oracle 11268     1  0   Jul 19 ?        0:00 ora_reco_hsbill
  oracle 11270     1  0   Jul 19 ?        0:06 ora_cjq0_hsbill
  oracle 10054     1  0   Nov 02 ?       18:27 ora_j001_hsbill
  oracle 10052     1  0   Nov 02 ?       21:19 ora_j000_hsbill
  oracle  2990     1  0   Nov 25 ?        0:05 ora_j003_hsbill
  oracle 10056     1  0   Nov 02 ?        3:04 ora_j002_hsbill
  oracle  8879     1  0 09:54:32 ?        0:00 ora_j004_hsbill


$ pmap 11264
11264:  ora_ckpt_hsbill
0000000100000000  53824K read/exec         /opt/oracle/product/9.2.0/bin/oracle
000000010358E000    872K read/write/exec   /opt/oracle/product/9.2.0/bin/oracle
0000000103668000   7968K read/write/exec     [ heap ]
0000000380000000 266240K read/write/exec/shared  [ ism shmid=0x64 ]
FFFFFFFF7C802000      8K read/write/exec     [ anon ]
FFFFFFFF7C814000      8K read/write/exec     [ anon ]
FFFFFFFF7C826000      8K read/write/exec     [ anon ]
FFFFFFFF7C838000      8K read/write/exec     [ anon ]
FFFFFFFF7C84A000      8K read/write/exec     [ anon ]
FFFFFFFF7C85C000      8K read/write/exec     [ anon ]
FFFFFFFF7C86E000      8K read/write/exec     [ anon ]
FFFFFFFF7C880000      8K read/write/exec     [ anon ]
FFFFFFFF7C892000      8K read/write/exec     [ anon ]
FFFFFFFF7C8A4000      8K read/write/exec     [ anon ]
FFFFFFFF7C8B6000      8K read/write/exec     [ anon ]
FFFFFFFF7C8C8000      8K read/write/exec     [ anon ]
FFFFFFFF7C8D6000      8K read/write/exec     [ anon ]
FFFFFFFF7C8DA000      8K read/write/exec     [ anon ]
FFFFFFFF7C8E8000      8K read/write/exec     [ anon ]
FFFFFFFF7C8EC000      8K read/write/exec     [ anon ]
FFFFFFFF7C8FA000      8K read/write/exec     [ anon ]
FFFFFFFF7C8FE000      8K read/write/exec     [ anon ]
FFFFFFFF7C90C000      8K read/write/exec     [ anon ]
FFFFFFFF7C910000      8K read/write/exec     [ anon ]
FFFFFFFF7C91E000      8K read/write/exec     [ anon ]
FFFFFFFF7CA02000      8K read/write/exec     [ anon ]
FFFFFFFF7CA10000      8K read/write/exec     [ anon ]
FFFFFFFF7CA14000      8K read/write/exec     [ anon ]
FFFFFFFF7CA22000      8K read/write/exec     [ anon ]
FFFFFFFF7CA26000      8K read/write/exec     [ anon ]
FFFFFFFF7CA34000      8K read/write/exec     [ anon ]
FFFFFFFF7CA38000      8K read/write/exec     [ anon ]
FFFFFFFF7CA46000      8K read/write/exec     [ anon ]
FFFFFFFF7CA4A000      8K read/write/exec     [ anon ]
FFFFFFFF7CA58000      8K read/write/exec     [ anon ]
FFFFFFFF7CA5C000      8K read/write/exec     [ anon ]
FFFFFFFF7CA6A000      8K read/write/exec     [ anon ]
FFFFFFFF7CA6E000      8K read/write/exec     [ anon ]
FFFFFFFF7CA7C000      8K read/write/exec     [ anon ]
FFFFFFFF7CA80000      8K read/write/exec     [ anon ]
FFFFFFFF7CA8E000      8K read/write/exec     [ anon ]
FFFFFFFF7CA92000      8K read/write/exec     [ anon ]
FFFFFFFF7CAA0000      8K read/write/exec     [ anon ]
FFFFFFFF7CAA4000      8K read/write/exec     [ anon ]
FFFFFFFF7CAB2000      8K read/write/exec     [ anon ]
FFFFFFFF7CAB6000      8K read/write/exec     [ anon ]
FFFFFFFF7CAC4000      8K read/write/exec     [ anon ]
FFFFFFFF7CAC8000      8K read/write/exec     [ anon ]
FFFFFFFF7CAD6000      8K read/write/exec     [ anon ]
FFFFFFFF7CADA000      8K read/write/exec     [ anon ]
FFFFFFFF7CAE8000      8K read/write/exec     [ anon ]
FFFFFFFF7CAEC000      8K read/write/exec     [ anon ]
FFFFFFFF7CAFA000      8K read/write/exec     [ anon ]
FFFFFFFF7CAFE000      8K read/write/exec     [ anon ]
FFFFFFFF7CB0C000      8K read/write/exec     [ anon ]
FFFFFFFF7CB10000      8K read/write/exec     [ anon ]
FFFFFFFF7CB1E000      8K read/write/exec     [ anon ]
FFFFFFFF7CC00000      8K read/write/exec     [ anon ]
FFFFFFFF7CD00000     24K read/exec         /usr/lib/sparcv9/nss_files.so.1
FFFFFFFF7CE06000      8K read/write/exec   /usr/lib/sparcv9/nss_files.so.1
FFFFFFFF7CF00000      8K read/write          [ anon ]
FFFFFFFF7CF10000      8K read/write          [ anon ]
FFFFFFFF7CF50000      8K read/write          [ anon ]
FFFFFFFF7CF60000     16K read/write          [ anon ]
FFFFFFFF7CF68000     32K read/write          [ anon ]
FFFFFFFF7D000000     16K read/exec         /usr/platform/sun4u/lib/sparcv9/libc_psr.so.1
FFFFFFFF7D100000     16K read/exec         /usr/lib/sparcv9/libmp.so.2
FFFFFFFF7D204000      8K read/write/exec   /usr/lib/sparcv9/libmp.so.2
FFFFFFFF7D300000      8K read/write/exec     [ anon ]
FFFFFFFF7D400000     88K read/exec         /usr/lib/sparcv9/libm.so.1
FFFFFFFF7D516000      8K read/write/exec   /usr/lib/sparcv9/libm.so.1
FFFFFFFF7D600000      8K read/exec         /usr/lib/sparcv9/libkstat.so.1
FFFFFFFF7D702000      8K read/write/exec   /usr/lib/sparcv9/libkstat.so.1
FFFFFFFF7D800000     32K read/exec         /usr/lib/sparcv9/librt.so.1
FFFFFFFF7D908000      8K read/write/exec   /usr/lib/sparcv9/librt.so.1
FFFFFFFF7DA00000     32K read/exec         /usr/lib/sparcv9/libaio.so.1
FFFFFFFF7DB08000      8K read/write/exec   /usr/lib/sparcv9/libaio.so.1
FFFFFFFF7DC00000    720K read/exec         /usr/lib/sparcv9/libc.so.1
FFFFFFFF7DDB4000     56K read/write/exec   /usr/lib/sparcv9/libc.so.1
FFFFFFFF7DDC2000      8K read/write/exec   /usr/lib/sparcv9/libc.so.1
FFFFFFFF7DF00000      8K read/write/exec     [ anon ]
FFFFFFFF7E000000     32K read/exec         /usr/lib/sparcv9/libgen.so.1
FFFFFFFF7E108000      8K read/write/exec   /usr/lib/sparcv9/libgen.so.1
FFFFFFFF7E200000    672K read/exec         /usr/lib/sparcv9/libnsl.so.1
FFFFFFFF7E3A8000     56K read/write/exec   /usr/lib/sparcv9/libnsl.so.1
FFFFFFFF7E3B6000     40K read/write/exec   /usr/lib/sparcv9/libnsl.so.1
FFFFFFFF7E400000   5328K read/exec         /opt/oracle/product/9.2.0/lib/libjox9.so
FFFFFFFF7EA32000    384K read/write/exec   /opt/oracle/product/9.2.0/lib/libjox9.so
FFFFFFFF7EA92000      8K read/write/exec   /opt/oracle/product/9.2.0/lib/libjox9.so
FFFFFFFF7EB00000     56K read/exec         /usr/lib/sparcv9/libsocket.so.1
FFFFFFFF7EC0E000     16K read/write/exec   /usr/lib/sparcv9/libsocket.so.1
FFFFFFFF7ED00000      8K read/write/exec     [ anon ]
FFFFFFFF7EE00000     32K read/exec         /opt/oracle/product/9.2.0/lib/libskgxn9.so
FFFFFFFF7EF06000      8K read/write/exec   /opt/oracle/product/9.2.0/lib/libskgxn9.so
FFFFFFFF7F000000      8K read/exec         /opt/oracle/product/9.2.0/lib/libskgxp9.so
FFFFFFFF7F100000      8K read/write/exec   /opt/oracle/product/9.2.0/lib/libskgxp9.so
FFFFFFFF7F200000      8K read/exec         /opt/oracle/product/9.2.0/lib/libodmd9.so
FFFFFFFF7F300000      8K read/write/exec   /opt/oracle/product/9.2.0/lib/libodmd9.so
FFFFFFFF7F400000      8K read/exec         /usr/lib/sparcv9/libdl.so.1
FFFFFFFF7F500000      8K read/write/exec     [ anon ]
FFFFFFFF7F600000    152K read/exec         /usr/lib/sparcv9/ld.so.1
FFFFFFFF7F724000     16K read/write/exec   /usr/lib/sparcv9/ld.so.1
FFFFFFFF7FFFA000     24K read/write          [ stack ]
         total   337360K
$

 

计算后台进程使用的内存资源:

337360K - 266240K = 71,120k

这就是一个进程所消耗的内存.

4.用户进程内存使用举例

 

$ ps -ef|grep LOCAL
  oracle 10080  9872  0 11:00:25 pts/2    0:00 grep LOCAL
  oracle 23498     1  0   Nov 25 ?        0:00 oraclehsbill (LOCAL=NO)
  oracle 23519     1  0   Nov 25 ?        0:50 oraclehsbill (LOCAL=NO)
$ pmap 23519
23519:  oraclehsbill (LOCAL=NO)
0000000100000000  53824K read/exec         /opt/oracle/product/9.2.0/bin/oracle
000000010358E000    872K read/write/exec   /opt/oracle/product/9.2.0/bin/oracle
0000000103668000    848K read/write/exec     [ heap ]
0000000380000000 266240K read/write/exec/shared  [ ism shmid=0x64 ]
FFFFFFFF7CC00000      8K read/write          [ anon ]
FFFFFFFF7CC10000      8K read/write          [ anon ]
FFFFFFFF7CC50000    136K read/write          [ anon ]
FFFFFFFF7CC76000     40K read/write          [ anon ]
FFFFFFFF7CD00000      8K read/write/exec     [ anon ]
FFFFFFFF7CE00000     24K read/exec         /usr/lib/sparcv9/nss_files.so.1
FFFFFFFF7CF06000      8K read/write/exec   /usr/lib/sparcv9/nss_files.so.1
FFFFFFFF7D000000     16K read/exec         /usr/platform/sun4u/lib/sparcv9/libc_psr.so.1
FFFFFFFF7D100000     16K read/exec         /usr/lib/sparcv9/libmp.so.2
FFFFFFFF7D204000      8K read/write/exec   /usr/lib/sparcv9/libmp.so.2
FFFFFFFF7D300000      8K read/write/exec     [ anon ]
FFFFFFFF7D400000     88K read/exec         /usr/lib/sparcv9/libm.so.1
FFFFFFFF7D516000      8K read/write/exec   /usr/lib/sparcv9/libm.so.1
FFFFFFFF7D600000      8K read/exec         /usr/lib/sparcv9/libkstat.so.1
FFFFFFFF7D702000      8K read/write/exec   /usr/lib/sparcv9/libkstat.so.1
FFFFFFFF7D800000     32K read/exec         /usr/lib/sparcv9/librt.so.1
FFFFFFFF7D908000      8K read/write/exec   /usr/lib/sparcv9/librt.so.1
FFFFFFFF7DA00000     32K read/exec         /usr/lib/sparcv9/libaio.so.1
FFFFFFFF7DB08000      8K read/write/exec   /usr/lib/sparcv9/libaio.so.1
FFFFFFFF7DC00000    720K read/exec         /usr/lib/sparcv9/libc.so.1
FFFFFFFF7DDB4000     56K read/write/exec   /usr/lib/sparcv9/libc.so.1
FFFFFFFF7DDC2000      8K read/write/exec   /usr/lib/sparcv9/libc.so.1
FFFFFFFF7DF00000      8K read/write/exec     [ anon ]
FFFFFFFF7E000000     32K read/exec         /usr/lib/sparcv9/libgen.so.1
FFFFFFFF7E108000      8K read/write/exec   /usr/lib/sparcv9/libgen.so.1
FFFFFFFF7E200000    672K read/exec         /usr/lib/sparcv9/libnsl.so.1
FFFFFFFF7E3A8000     56K read/write/exec   /usr/lib/sparcv9/libnsl.so.1
FFFFFFFF7E3B6000     40K read/write/exec   /usr/lib/sparcv9/libnsl.so.1
FFFFFFFF7E400000   5328K read/exec         /opt/oracle/product/9.2.0/lib/libjox9.so
FFFFFFFF7EA32000    384K read/write/exec   /opt/oracle/product/9.2.0/lib/libjox9.so
FFFFFFFF7EA92000      8K read/write/exec   /opt/oracle/product/9.2.0/lib/libjox9.so
FFFFFFFF7EB00000     56K read/exec         /usr/lib/sparcv9/libsocket.so.1
FFFFFFFF7EC0E000     16K read/write/exec   /usr/lib/sparcv9/libsocket.so.1
FFFFFFFF7ED00000      8K read/write/exec     [ anon ]
FFFFFFFF7EE00000     32K read/exec         /opt/oracle/product/9.2.0/lib/libskgxn9.so
FFFFFFFF7EF06000      8K read/write/exec   /opt/oracle/product/9.2.0/lib/libskgxn9.so
FFFFFFFF7F000000      8K read/exec         /opt/oracle/product/9.2.0/lib/libskgxp9.so
FFFFFFFF7F100000      8K read/write/exec   /opt/oracle/product/9.2.0/lib/libskgxp9.so
FFFFFFFF7F200000      8K read/exec         /opt/oracle/product/9.2.0/lib/libodmd9.so
FFFFFFFF7F300000      8K read/write/exec   /opt/oracle/product/9.2.0/lib/libodmd9.so
FFFFFFFF7F400000      8K read/exec         /usr/lib/sparcv9/libdl.so.1
FFFFFFFF7F500000      8K read/write/exec     [ anon ]
FFFFFFFF7F600000    152K read/exec         /usr/lib/sparcv9/ld.so.1
FFFFFFFF7F724000     16K read/write/exec   /usr/lib/sparcv9/ld.so.1
FFFFFFFF7FFF2000     56K read/write          [ stack ]
         total   329968K
$

从PMAP的输出中我们可以看到进程所调用的库文件等，及其读写状态。


-The End-

Posted by eygle at 3:11 PM | Comments (1)

使用Oracle Wrap工具加密你的代码

作者:eygle

出处:http://blog.eygle.com

Oracle提供Wrap工具，可以用于加密你的Package等.
不过需要注意的是，加密后的代码无法解密，你需要保管好你的源代码。

以下是个例子:

1.源代码

create or replace function get_rowid
(l_rowid in varchar2)
return varchar2
is
ls_my_rowid     varchar2(200);          
rowid_type      number;          
object_number   number;          
relative_fno    number;          
block_number    number;          
row_number      number;  
begin
 dbms_rowid.rowid_info(l_rowid,rowid_type,object_number,relative_fno, block_number, row_number);          
 ls_my_rowid := 'Object# is      :'||to_char(object_number)||chr(10)||
                'Relative_fno is :'||to_char(relative_fno)||chr(10)||
                'Block number is :'||to_char(block_number)||chr(10)||
                'Row number is   :'||to_char(row_number);
 return ls_my_rowid ;
end;          
/

 

2.代码功能测试

 

[oracle@jumper tools]$ sqlplus scott/tiger

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Nov 15 21:56:36 2004

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


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> @f_get_rowid

Function created.

SQL> select rowid from dept where deptno=10;

ROWID
------------------
AAABiPAABAAAFRSAAA

SQL> select get_rowid('AAABiPAABAAAFRSAAA') from dual;                               

GET_ROWID('AAABIPAABAAAFRSAAA')
------------------------------------------------------------------
Object# is      :6287
Relative_fno is :1
Block number is :21586
Row number is   :0


SQL> !
[oracle@jumper tools]$ ls
ct.sql  ddlt.sql  f_get_rowid.sql  getevent.sql

3.使用wrap加密及加密后的代码

 

[oracle@jumper tools]$ wrap iname=f_get_rowid.sql oname=f_get_rowid.plb

PL/SQL Wrapper: Release 9.2.0.4.0- Production on Mon Nov 15 21:59:39 2004

Copyright (c) Oracle Corporation 1993, 2001.  All Rights Reserved.

Processing f_get_rowid.sql to f_get_rowid.plb
     
[oracle@jumper tools]$ cat f_get_rowid.plb 
create or replace function get_rowid wrapped 
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
3
8
9200000
1
4
0 
17
2 :e:
1FUNCTION:
1GET_ROWID:
1L_ROWID:
1VARCHAR2:
1RETURN:
1LS_MY_ROWID:
1200:
1ROWID_TYPE:
1NUMBER:
1OBJECT_NUMBER:
1RELATIVE_FNO:
1BLOCK_NUMBER:
1ROW_NUMBER:
1DBMS_ROWID:
1ROWID_INFO:
1Object# is      :::
1||:
1TO_CHAR:
1CHR:
110:
1Relative_fno is :::
1Block number is :::
1Row number is   :::
0

0
0
83
2
0 a0 8d 8f a0 b0 3d b4
:2 a0 2c 6a a3 a0 51 a5 1c
81 b0 a3 a0 1c 81 b0 a3
a0 1c 81 b0 a3 a0 1c 81
b0 a3 a0 1c 81 b0 a3 a0
1c 81 b0 :2 a0 6b :6 a0 a5 57
a0 6e 7e :2 a0 a5 b b4 2e
7e a0 51 a5 b b4 2e 7e
6e b4 2e 7e :2 a0 a5 b b4
2e 7e a0 51 a5 b b4 2e
7e 6e b4 2e 7e :2 a0 a5 b
b4 2e 7e a0 51 a5 b b4
2e 7e 6e b4 2e 7e :2 a0 a5
b b4 2e d :2 a0 65 b7 a4
b1 11 68 4f 1d 17 b5 
83
2
0 3 7 23 1f 1e 2b 1b
30 34 38 3c 59 44 48 4b
4c 54 43 75 64 68 70 40
8d 7c 80 88 63 a9 98 9c
a4 60 c1 b0 b4 bc 97 dd
cc d0 d8 94 c8 e4 e8 eb
ef f3 f7 fb ff 103 104 109
10d 112 115 119 11d 11e 120 121
126 129 12d 130 131 133 134 139
13c 141 142 147 14a 14e 152 153
155 156 15b 15e 162 165 166 168
169 16e 171 176 177 17c 17f 183
187 188 18a 18b 190 193 197 19a
19b 19d 19e 1a3 1a6 1ab 1ac 1b1
1b4 1b8 1bc 1bd 1bf 1c0 1c5 1c9
1cd 1d1 1d5 1d7 1db 1dd 1e9 1ed
1ef 1f0 1f9 
83
2
0 1 a 2 d :2 2 :2 1 8
:3 1 e 17 16 :2 e :2 1 :3 d :2 1
:3 10 :2 1 :3 f :2 1 :3 f :2 1 :3 d 1
2 :2 d 18 20 2b 39 47 55
:3 2 11 24 26 2e :2 26 :2 11 3c
3e 42 :2 3e :2 11 45 3 :2 11 16
18 20 :2 18 :2 11 2d 2f 33 :2 2f
:2 11 36 3 :2 11 16 18 20 :2 18
:2 11 2d 2f 33 :2 2f :2 11 36 3
:2 11 16 18 20 :2 18 :2 11 :2 2 9
2 :9 1 
83
4
0 :2 1 :5 2 :2 3
:2 1 :7 5 :5 6 :5 7
:5 8 :5 9 :5 a :b c
:11 d e :2 d :5 e
:2 d :5 e :2 d e
f :2 d :5 f :2 d
:5 f :2 d f 10
:2 d :5 10 :3 d :3 11
:2 b :7 1 
1fb
4
:3 0 1 :3 0 2
:a 0 7e 1 :7 0
5 :2 0 3 4
:3 0 3 :7 0 5
4 :3 0 5 :3 0
4 :3 0 7 9
0 7e 2 a
:2 0 b 60 0
9 4 :3 0 7
:2 0 7 d f
:6 0 12 10 0
7c 0 6 :6 0
f 94 0 d
9 :3 0 14 :7 0
17 15 0 7c
0 8 :6 0 9
:3 0 19 :7 0 1c
1a 0 7c 0
a :6 0 13 c8
0 11 9 :3 0
1e :7 0 21 1f
0 7c 0 b
:6 0 9 :3 0 23
:7 0 26 24 0
7c 0 c :6 0
e :3 0 9 :3 0
28 :7 0 2b 29
0 7c 0 d
:6 0 f :3 0 2c
2d 0 3 :3 0
8 :3 0 a :3 0
b :3 0 c :3 0
d :3 0 15 2e
35 :2 0 7a 6
:3 0 10 :4 0 11
:2 0 12 :3 0 a
:3 0 1c 3a 3c
1e 39 3e :3 0
11 :2 0 13 :3 0
14 :2 0 21 41
43 23 40 45
:3 0 11 :2 0 15
:4 0 26 47 49
:3 0 11 :2 0 12
:3 0 b :3 0 29
4c 4e 2b 4b
50 :3 0 11 :2 0
13 :3 0 14 :2 0
2e 53 55 30
52 57 :3 0 11
:2 0 16 :4 0 33
59 5b :3 0 11
:2 0 12 :3 0 c
:3 0 36 5e 60
38 5d 62 :3 0
11 :2 0 13 :3 0
14 :2 0 3b 65
67 3d 64 69
:3 0 11 :2 0 17
:4 0 40 6b 6d
:3 0 11 :2 0 12
:3 0 d :3 0 43
70 72 45 6f
74 :3 0 37 75
0 7a 5 :3 0
6 :3 0 78 :2 0
7a 48 7d :3 0
7d 4c 7d 7c
7a 7b :6 0 7e
:2 0 2 a 7d
81 :3 0 80 7e
82 :8 0 
53
4
:3 0 1 3 1
6 1 e 1
c 1 13 1
18 1 1d 1
22 1 27 6
2f 30 31 32
33 34 1 3b
2 38 3d 1
42 2 3f 44
2 46 48 1
4d 2 4a 4f
1 54 2 51
56 2 58 5a
1 5f 2 5c
61 1 66 2
63 68 2 6a
6c 1 71 2
6e 73 3 36
76 79 6 11
16 1b 20 25
2a 
1
4
0 
81
0
1
14
1
8
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 
3 1 0
22 1 0
2 0 1
c 1 0
13 1 0
27 1 0
18 1 0
1d 1 0
0

/

4.测试加密后的代码

 

[oracle@jumper tools]$ exit
exit

SQL> drop function get_rowid;

Function dropped.

SQL> @f_get_rowid.plb

Function created.

SQL> select get_rowid('AAABiPAABAAAFRSAAA') from dual;

GET_ROWID('AAABIPAABAAAFRSAAA')
-----------------------------------------------------------------
Object# is      :6287
Relative_fno is :1
Block number is :21586
Row number is   :0


SQL>

注意，如果出现如下错误：

kgepop: no error frame. to pop to for error 1801


则需要设置正确的环境变量：NLS_LANG
例如在WIndows下可以如下设置：
C:\oracle\ora92\bin>set NLS_LANG=CHINESE_CHINA.ZHS16GBK




Posted by eygle at 3:03 PM

如何获得当前数据库的SCN值

作者:eygle

出处:http://blog.eygle.com

SCN(System Change Number) ，也就是通常我们所说的系统改变号，是数据库中非常重要的一个数据结构。

它定义数据库在某个确切时刻提交的版本。在事物提交时，它被赋予一个唯一的标示事物的 SCN 。 SCN 提供 Oracle 的内部时钟机制，
可被看作逻辑时钟，这对于恢复操作是至关重要的 （ Oracle 仅根据 SCN 执行恢复）。

一直以来，对于 SCN 有很多争议，很多人认为 SCN 是指， System Commit Number ，而通常 SCN 在提交时才变化，所以很多时候，
这两个名词经常被交替使用。

到底是哪个词其实不是最重要的，重要的是我们知道 SCN 是 Oracle 内部的时钟机制， Oracle 通过 SCN 来维护数据库的一致性，并通过
SCN 实施 Oracle 至关重要的恢复机制。

我们来看一下获得当前SCN的几种方式:

1.在Oracle9i中，可以使用dbms_flashback.get_system_change_number来获得

例如:


SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2982184

2.在Oracle9i之前

可以通过查询x$ktuxe获得

X$KTUXE-------------[K]ernel [T]ransaction [U]ndo Transa[x]tion [E]ntry (table)

 

SQL> select max(ktuxescnw*power(2,32)+ktuxescnb) from x$ktuxe;

MAX(KTUXESCNW*POWER(2,32)+KTUXESCNB)
------------------------------------
                             2980613

 

 

Posted by eygle at 12:24 PM | Comments (0)

CopyRight © 2004-2008 eygle.com, All rights reserved.