使用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文件

Oracle Database常用补丁集Patch号

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

标题:Oracle Database常用补丁集Patch号

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

Patchset / PSU Patch Number

11.2.0.4	13390677      11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER
11.2.0.3.8	16902043      DATABASE PATCH SET UPDATE 11.2.0.3.8 (INCLUDES CPUOCT2013)
11.2.0.3.7	16619892      DATABASE PATCH SET UPDATE 11.2.0.3.7 (INCLUDES CPUJUL2013)
11.2.0.3.6	16056266      DATABASE PATCH SET UPDATE 11.2.0.3.6 (INCLUDES CPUAPR2013)
11.2.0.3.5	14727310      DATABASE PATCH SET UPDATE 11.2.0.3.5 (INCLUDES CPUJAN2013):
11.2.0.3.4	14275605      DATABASE PATCH SET UPDATE 11.2.0.3.4 (INCLUDES CPUOCT2012)
11.2.0.3.3	13923374      DATABASE PATCH SET UPDATE 11.2.0.3.3 (INCLUDES CPU JUL2012):
11.2.0.3.2	13696216      DATABASE PATCH SET UPDATE 11.2.0.3.2 (INCLUDES CPU APR2012)
11.2.0.3.1	13343438      DATABASE PATCH SET UPDATE 11.2.0.3.1 (INCLUDES CPU JAN2012)
11.2.0.3	10404530      11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER
11.2.0.2.11	16619893      DATABASE PATCH SET UPDATE 11.2.0.2.11 (INCLUDES CPUJUL2013)
11.2.0.2.10	16056267      DATABASE PATCH SET UPDATE 11.2.0.2.10 (INCLUDES CPUAPR2013)
11.2.0.2.9	14727315      DATABASE PATCH SET UPDATE 11.2.0.2.9 (INCLUDES CPUJAN2013):
11.2.0.2.8	14275621      DATABASE PATCH SET UPDATE 11.2.0.2.8 (INCLUDES CPUOCT2012)
11.2.0.2.7	13923804      DATABASE PATCH SET UPDATE 11.2.0.2.7 (INCLUDES CPU JUL2012)
11.2.0.2.6	13696224      DATABASE PATCH SET UPDATE 11.2.0.2.6 (INCLUDES CPU APR2012)
11.2.0.2.5	13343424      DATABASE PATCH SET UPDATE 11.2.0.2.5 (INCLUDES CPU JAN2012)
11.2.0.2.4	12827726      DATABASE PSU 11.2.0.2.4 (INCLUDES CPUOCT2011)
11.2.0.2.3	12419331      DATABASE PSU 11.2.0.2.3 (INCLUDES CPUJUL2011)
11.2.0.2.2	11724916      DATABASE PSU 11.2.0.2.2 (INCLUDES CPUAPR2011)
11.2.0.2.1	10248523      DATABASE PSU 11.2.0.2.1
11.2.0.2	10098816      11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER
11.2.0.1.6	12419378       DATABASE PSU 11.2.0.1.6 (INCLUDES CPUJUL2011)
11.2.0.1.5	11724930      DATABASE PSU 11.2.0.1.5 (INCLUDES CPUAPR2011)
11.2.0.1.4	10248516      DATABASE PSU 11.2.0.1.4 (INCLUDES CPUJAN2011)
11.2.0.1.3	9952216      DATABASE PSU 11.2.0.1.3 (INCLUDES CPUOCT2010)
11.2.0.1.2	9654983      DATABASE PSU 11.2.0.1.2 (INCLUDES CPUJUL2010)
11.2.0.1.1	9352237      DATABASE PSU 11.2.0.1.1
11.1.0.7.16	16619896 [*]      DATABASE PATCH SET UPDATE 11.1.0.7.16 (INCLUDES CPUJUL2013)
11.1.0.7.15	16056268  [*]      DATABASE PATCH SET UPDATE 11.1.0.7.15 (INCLUDES CPUAPR2013)
11.1.0.7.14	14739378 [*]      DATABASE PATCH SET UPDATE 11.1.0.7.14 (INCLUDES CPUJAN2013)
11.1.0.7.13	14275623 [*]      DATABASE PATCH SET UPDATE 11.1.0.7.13 (INCLUDES CPUOCT2012)
11.1.0.7.12	13923474      DATABASE PATCH SET UPDATE 11.1.0.7.12 (INCLUDES CPU JUL2012)
11.1.0.7.11	13621679      DATABASE PATCH SET UPDATE 11.1.0.7.11 (INCLUDES CPU APR2012)
11.1.0.7.10	13343461      DATABASE PATCH SET UPDATE 11.1.0.7.10 (INCLUDES CPU JAN2012)
11.1.0.7.9	12827740      DATABASE PSU 11.1.0.7.9 (INCLUDES CPUOCT2011)
11.1.0.7.8	12419384      DATABASE PSU 11.1.0.7.8 (INCLUDES CPUJUL2011)
11.1.0.7.7	11724936      DATABASE PSU 11.1.0.7.7 (INCLUDES CPUAPR2011)
11.1.0.7.6	10248531      DATABASE PSU 11.1.0.7.6 (INCLUDES CPUJAN2011)
11.1.0.7.5	9952228      DATABASE PSU 11.1.0.7.5 (INCLUDES CPUOCT2010)
11.1.0.7.4	9654987      DATABASE PSU 11.1.0.7.4 (INCLUDES CPUJUL2010)
11.1.0.7.3	9352179      DATABASE PSU 11.1.0.7.3 (INCLUDES CPUAPR2010)
11.1.0.7.2	9209238      DATABASE PSU 11.1.0.7.2 (INCLUDES CPUJAN2010)
11.1.0.7.1	8833297      DATABASE PSU 11.1.0.7.1 (INCLUDES CPUOCT2009)
11.1.0.7	6890831      11.1.0.7.0 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.5.12	16619894 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.12 (INCLUDES CPUJUL2013):
10.2.0.5.11	16056270  [*]      DATABASE PATCH SET UPDATE 10.2.0.5.11 (INCLUDES CPUAPR2013)
10.2.0.5.10	14727319 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.10 (INCLUDES CPUJAN2013):
10.2.0.5.9	14275629 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.9 (INCLUDES CPUOCT2012)
10.2.0.5.8	13923855 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.8 (INCLUDES CPU JUL2012)
10.2.0.5.7	13632743 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.7 (INCLUDES CPU APR2012)
10.2.0.5.6	13343471 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.6 (INCLUDES CPU JAN2012)
10.2.0.5.5	12827745 [*]      DATABASE PSU 10.2.0.5.5 (INCLUDES CPUOCT2011)
10.2.0.5.4	12419392      DATABASE PSU 10.2.0.5.4 (INCLUDES CPUJUL2011)
10.2.0.5.3	11724962      DATABASE PSU 10.2.0.5.3 (INCLUDES CPUAPR2011)
10.2.0.5.2	10248542      DATABASE PSU 10.2.0.5.2 (INCLUDES CPUJAN2011)
10.2.0.5.1	9952230      DATABASE PSU 10.2.0.5.1 (INCLUDES CPUOCT2010)
10.2.0.5	8202632      10.2.0.5.0 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.4.17	16619897 [*]      DATABASE PSU 10.2.0.4.17 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2013):
10.2.0.4.16	16056269 [*]      DATABASE PSU 10.2.0.4.16 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2013)
10.2.0.4.15	14736542 [*]      DATABASE PSU 10.2.0.4.15 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJAN2013):
10.2.0.4.14	14275630 [**]      DATABASE PSU 10.2.0.4.14 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2012)
0.2.0.4.13	13923851 [*]      DATABASE PSU 10.2.0.4.13 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2012)
10.2.0.4.12	12879933 [*]
DATABASE PSU 10.2.0.4.12 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2012)
10.2.0.4.11	12879929 [*]      DATABASE PATCH SET UPDATE 10.2.0.4.11 (PRE-REQ 10.2.0.4.4 | INCLUDES CPUJAN2012)
10.2.0.4.10	12827778      DATABASE PSU 10.2.0.4.10 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2011)
10.2.0.4.9	12419397      DATABASE PSU 10.2.0.4.9 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2011)
10.2.0.4.8	11724977      DATABASE PSU 10.2.0.4.8 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2011)
10.2.0.4.7	10248636      DATABASE PSU 10.2.0.4.7 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJAN2011)
10.2.0.4.6	9952234      DATABASE PSU 10.2.0.4.6 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2010)
10.2.0.4.5	9654991      DATABASE PSU 10.2.0.4.5 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2010)    [overlay PSU]
10.2.0.4.4	9352164      DATABASE PSU 10.2.0.4.4 (INCLUDES CPUAPR2010)
10.2.0.4.3	9119284      DATABASE PSU 10.2.0.4.3 (INCLUDES CPUJAN2010)
10.2.0.4.2	8833280      DATABASE PSU 10.2.0.4.2 (INCLUDES CPUOCT2009)
10.2.0.4.1	8576156      DATABASE PSU 10.2.0.4.1 (INCLUDES CPUJUL2009)
10.2.0.4	6810189      10.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.3	5337014      10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.2	4547817      10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER
10.1.0.5	4505133      10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER
10.1.0.4	4163362      10.1.0.4 PATCH SET FOR ORACLE DATABASE SERVER
10.1.0.3	3761843      10.1.0.3 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.8	4547809      9.2.0.8 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.7	4163445      9.2.0.7 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.6	3948480      9.2.0.6 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.5	3501955      ORACLE 9I DATABASE SERVER RELEASE 2 - PATCH SET 4 VERSION 9.2.0.5.0
9.2.0.4	3095277      9.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.3	2761332      9.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.2	2632931      9.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER
9.0.1.5	3301544      9.0.1.5 PATCHSET
9.0.1.4	2517300      9.0.1.4 PATCH SET FOR ORACLE DATABASE SERVER
9.0.1.3	2271678      9.0.1.3. PATCH SET FOR ORACLE DATA SERVER
8.1.7.4	2376472      8.1.7.4 PATCH SET FOR ORACLE DATA SERVER
8.1.7.3	2189751      8.1.7.3 PATCH SET FOR ORACLE DATA SERVER
8.1.7.2	1909158      8.1.7.2.1 PATCH SET FOR ORACLE DATA SERVER

NOTE:
[*] 10.2.0.4 and 10.2.0.5 are now in extended support mode and PSU’s released after Aug 01,2011 will need ES License to download them.
参考:Quick Reference to Patchset Patch Numbers [ID 753736.1]

坏块分类与检测

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

标题:坏块分类与检测

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

很多人都对oracle都知道oracle坏块有逻辑坏块和物理坏块之分,其实根据物理和逻辑坏块的分类,还可以继续分下去;对于坏块的检查,很多人知道物理和逻辑坏块使用dbv和rman检测,那其他种类的坏块该怎么办呢?这篇文章整理自MOS,给大家整理个分析坏块的思路,在后续文章中,会给出各种坏块的解决思路
http://www.xifenfei.com/wp-content/uploads/2012/03/Corruption_block_type_and_analysis.pdf
附件:hout和hcheck

通过修改col$.col#改变列展示顺序

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

标题:通过修改col$.col#改变列展示顺序

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

有网友提出在表中新增加一列,然后让这个列在中间(大家都知道默认情况下,增加一列,这列会在其他列之后),通过修改col$基表可以实现改变列的显示顺序(增加一个新列是小儿科的事情不再研究)。我这里通过建立一个表,有id和name列,现在要改变这两列的默认展示顺序
1.创建模拟表

SQL> create table chf.t_xff (id number,name varchar2(100));
Table created.
SQL> insert into chf.t_xff values(1,'xifenfei');
1 row created.
SQL> insert into chf.t_xff values(2,'www.xifenfei.com');
1 row created.
SQL> commit;
Commit complete.
SQL> desc chf.t_xff;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(100)
SQL> col name for a30
SQL> select * from chf.t_xff;
        ID NAME
---------- ------------------------------
         1 xifenfei
         2 www.xifenfei.com

2.修改col$.col#

SQL> select COLUMN_ID,COLUMN_NAME from dba_tab_cols
2    where table_name='T_XFF' AND OWNER='CHF';
 COLUMN_ID COLUMN_NAME
---------- ------------------------------------------------------------
         2 NAME
         1 ID
SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='T_XFF';
 OBJECT_ID
----------
     75598
SQL> SELECT NAME,COL# FROM COL$ WHERE OBJ#=75598;
NAME                                 COL#
------------------------------ ----------
NAME                                    2
ID                                      1
SQL> UPDATE COL$ SET COL#=2 WHERE OBJ#=75598 AND NAME='ID';
1 row updated.
SQL> SELECT NAME,COL# FROM COL$ WHERE OBJ#=75598;
NAME                                 COL#
------------------------------ ----------
NAME                                    2
ID                                      2
SQL> UPDATE COL$ SET COL#=1 WHERE OBJ#=75598 AND NAME='NAME';
1 row updated.
SQL> SELECT NAME,COL# FROM COL$ WHERE OBJ#=75598;
NAME                                 COL#
------------------------------ ----------
NAME                                    1
ID                                      2
SQL> COMMIT;
Commit complete.

3.验证结果

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> select * from chf.t_xff;
NAME                                   ID
------------------------------ ----------
xifenfei                                1
www.xifenfei.com                        2
SQL> desc chf.t_xff
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 NAME                                               VARCHAR2(100)
 ID                                                 NUMBER