实现trigger集中记录所有库ddl操作

今天客户说了一个我感觉有意思的需求:在一个库上的一张表记录所有库的ddl操作,实现方式:在一个库上建立表和触发器,其他库上通过dblink+同义词+触发器实现ddl操作记录到远程的表中.他当时写了一个触发器,但是有错误,想让我协助解决.在我们的一起努力下,解决了该触发器在dblink同义词的库上出错的问题.我这里测试使用的是10g的库做为存储所有库的ddl记录的库,11g库做为一个通过dblink插入ddl操作记录的库.
在10g数据库库中操作
1.创建记录ddl操作表

SQL> conn chf/xifenfei
Connected.
SQL> create table t_ddl_audit(
  2  db_name varchar2(30),
  3  login_user varchar2(30),
  4  ddl_time date,
  5  ip_address varchar2(20),
  6  audsid varchar2(20),
  7  schema_user varchar2(30),
  8  schema_object varchar2(40),
  9  login_tool varchar2(40),
 10  os_user varchar2(40),
 11  ddl_sql varchar2(4000));

Table created.

2.创建触发器

SQL> create or replace trigger tri_ddl_audit
  2    before ddl on database
  3  declare
  4    n           number;
  5    str_stmt    varchar2(4000);
  6    sql_text    ora_name_list_t;
  7    l_trace     number;
  8    v_module    varchar2(50);
  9    v_action    varchar2(50);
 10    str_session v$session%rowtype;
 11  begin
 12    n := ora_sql_txt(sql_text);
 13    for i in 1 .. n loop
 14      str_stmt := substr(str_stmt || sql_text(i), 1, 3000);
 15    end loop;
 16    dbms_application_info.READ_MODULE(v_module, v_action);
 17    INSERT INTO chf.t_ddl_audit
 18      (db_name,
 19       login_user,
 20       ddl_time,
 21       ip_address,
 22       audsid,
 23       schema_user,
 24       schema_object,
 25       login_tool,
 26       os_user,
 27       ddl_sql)
 28    VALUES
 29      (sys_context('USERENV', 'db_name'),
 30       ora_login_user,
 31       SYSDATE,
 32       sys_context('USERENV', 'IP_ADDRESS'),
 33       userenv('SESSIONID'),
 34       ora_dict_obj_owner,
 35       ora_dict_obj_name,
 36       v_module,
 37       sys_context('userenv', 'os_user'),
 38       str_stmt);
 39  exception
 40    when no_data_found then
 41      null;
 42  end;
 43  /

Trigger created.

3.测试触发器

SQL> conn chf/xifenfei
Connected.
SQL> create table t_xff as select * from dba_tables where rownum=1;

Table created.

SQL> select db_name,login_user,ddl_sql from t_ddl_audit;

DB_NAME                        LOGIN_USER
------------------------------ ------------------------------
DDL_SQL
-----------------------------------------------------------------
XFF                            CHF
create table t_xff as select * from dba_tables where rownum=1

在11g数据库中操作
1.创建dblink和同义词

SQL> create database link "ora10g_dblink"
  2   connect to chf
  3    identified by "xifenfei"
  4     using 'ora10g';

Database link created.

SQL> create  synonym t_ddl_audit for t_ddl_audit@ora10g_dblink;

Synonym created.

2.第一次创建触发器

SQL> create or replace trigger tri_ddl_audit
  2    before ddl on database
  3  declare
  4    n           number;
  5    str_stmt    varchar2(4000);
  6    sql_text    ora_name_list_t;
  7    l_trace     number;
  8    v_module    varchar2(50);
  9    v_action    varchar2(50);
 10    str_session v$session%rowtype;
 11  begin
 12    n := ora_sql_txt(sql_text);
 13    for i in 1 .. n loop
 14      str_stmt := substr(str_stmt || sql_text(i), 1, 3000);
 15    end loop;
 16    dbms_application_info.READ_MODULE(v_module, v_action);
 17    INSERT INTO t_ddl_audit
 18      (db_name,
 19       login_user,
 20       ddl_time,
 21       ip_address,
 22       audsid,
 23       schema_user,
 24       schema_object,
 25       login_tool,
 26       os_user,
 27       ddl_sql)
 28    VALUES
 29      (sys_context('USERENV', 'db_name'),
 30       ora_login_user,
 31       SYSDATE,
 32       sys_context('USERENV', 'IP_ADDRESS'),
 33       userenv('SESSIONID'),
 34       ora_dict_obj_owner,
 35       ora_dict_obj_name,
 36       v_module,
 37       sys_context('userenv', 'os_user'),
 38       str_stmt);
 39  exception
 40    when no_data_found then
 41      null;
 42  end;
 43  /

Trigger created.

3.测试触发器

SQL> create table t_xff as select * from dba_objects where rownum<10;
create table t_xff as select * from dba_objects where rownum<10
                                    *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02070: database  does not support  in this context
ORA-06512: at line 15

出现ORA-02070错误,估计是类此sys_context(‘userenv’, ‘os_user’)导致。

4.第二次创建触发器

SQL> create or replace trigger tri_ddl_audit
  2    before ddl on database
  3  declare
  4    n           number;
  5    str_stmt    varchar2(4000);
  6    sql_text    ora_name_list_t;
  7    l_trace     number;
  8    v_module    varchar2(50);
  9    v_action    varchar2(50);
 10    v_db_name   varchar2(50);
 11    v_ip_addr   varchar2(50);
 12    v_os        varchar2(50);
 13    v_session_id varchar2(50);
 14    str_session v$session%rowtype;
 15  begin
 16    n := ora_sql_txt(sql_text);
 17    for i in 1 .. n loop
 18      str_stmt := substr(str_stmt || sql_text(i), 1, 3000);
 19    end loop;
 20    dbms_application_info.READ_MODULE(v_module, v_action);
 21    v_db_name :=sys_context('USERENV', 'db_name');
 22    v_ip_addr :=sys_context('USERENV', 'IP_ADDRESS');
 23    v_os:=sys_context('userenv', 'os_user');
 24    v_session_id:=userenv('SESSIONID');
 25    INSERT INTO t_ddl_audit
 26      (db_name,
 27       login_user,
 28       ddl_time,
 29       ip_address,
 30       audsid,
 31       schema_user,
 32       schema_object,
 33       login_tool,
 34       os_user,
 35       ddl_sql)
 36    VALUES
 37      (v_db_name,
 38       ora_login_user,
 39       SYSDATE,
 40       v_ip_addr,
 41      v_session_id,
 42       ora_dict_obj_owner,
 43       ora_dict_obj_name,
 44       v_module,
 45       v_os,
 46       str_stmt);
 47  exception
 48    when no_data_found then
 49      null;
 50  end;
 51  /

Trigger created.

5.继续测试触发器

SQL> drop table t3;
drop table t3
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02069: global_names parameter must be set to TRUE for this operation
ORA-06512: at line 23

根据ORA-02069,查询资料发现是通过dblink插入数据使用了变量和常量的方式混合使用导致该错误,修改触发器全部使用变量方式

6.第三次创建触发器

SQL> create or replace trigger tri_ddl_audit
  2    before ddl on database
  3  declare
  4    n           number;
  5    str_stmt    varchar2(4000);
  6    sql_text    ora_name_list_t;
  7    l_trace     number;
  8    v_module    varchar2(50);
  9    v_action    varchar2(50);
 10    v_db_name   varchar2(50);
 11    v_ip_addr   varchar2(50);
 12    v_os        varchar2(50);
 13    v_session_id varchar2(50);
 14    v_loginuser    varchar2(50);
 15     v_obj_name varchar2(50);
 16    v_owner    varchar2(50);
 17    str_session v$session%rowtype;
 18  begin
 19    n := ora_sql_txt(sql_text);
 20    for i in 1 .. n loop
 21      str_stmt := substr(str_stmt || sql_text(i), 1, 3000);
 22    end loop;
 23    dbms_application_info.READ_MODULE(v_module, v_action);
 24    v_db_name :=sys_context('USERENV', 'db_name');
 25    v_ip_addr :=sys_context('USERENV', 'IP_ADDRESS');
 26    v_os:=sys_context('userenv', 'os_user');
 27    v_session_id:=userenv('SESSIONID');
 28    v_loginuser:= ora_login_user;
 29    v_owner:=ora_dict_obj_owner;
 30    v_obj_name:=ora_dict_obj_name;
 31    INSERT INTO t_ddl_audit
 32      (db_name,
 33       login_user,
 34       ddl_time,
 35       ip_address,
 36       audsid,
 37       schema_user,
 38       schema_object,
 39       login_tool,
 40       os_user,
 41       ddl_sql)
 42    VALUES
 43      (v_db_name,
 44       v_loginuser,
 45       SYSDATE,
 46       v_ip_addr,
 47      v_session_id,
 48       v_owner,
 49       v_obj_name,
 50       v_module,
 51       v_os,
 52       str_stmt);
 53  exception
 54    when no_data_found then
 55      null;
 56  end;   
 57  /

Trigger created.

7.测试触发器

SQL> create table t_xff11 as select * from dba_tables where rownum<10;

Table created.

SQL> select db_name,login_user,ddl_sql from t_ddl_audit;

DB_NAME                        LOGIN_USER
------------------------------ ------------------------------
DDL_SQL
-----------------------------------------------------------------
ora11g                         CHF
create table t_xff11 as select * from dba_tables where rownum<10

XFF                            CHF
create table t_xff as select * from dba_tables where rownum=1

补充说明
这个方案个人感觉是一个实验室中的方案,在实际的生成环境中很难应用上
1.trigger记录ddl操作本身效率不高
2.如果某个库不能访问存储ddl操作的表的数据库,将导致该数据库所有ddl操作hang住,从而可能使得该数据库hang住的风险.

ORACLE 十进制与二进制互转函数

十进制转换二进制

CREATE OR REPLACE FUNCTION NUMBER_TO_BIT(V_NUM NUMBER) 
RETURN VARCHAR IS V_RTN VARCHAR(8);--注意返回列长度
  V_N1  NUMBER;
  V_N2  NUMBER;
BEGIN
V_N1 := V_NUM;
    LOOP
      V_N2  := MOD(V_N1, 2);
      V_N1  := ABS(TRUNC(V_N1 / 2));
      V_RTN := TO_CHAR(V_N2) || V_RTN;
      EXIT WHEN V_N1 = 0;
    END LOOP;
--返回二进制长度
 SELECT lpad(V_RTN,8,0) 
    INTO   V_RTN
    FROM dual;
return V_RTN;
end;

SQL> select NUMBER_TO_BIT(208) from dual;

NUMBER_TO_BIT(208)
-----------------------------
11010000

二进制转换十进制

CREATE OR REPLACE FUNCTION BIT_TO_NUMBER(P_BIN IN VARCHAR2) RETURN NUMBER AS
  V_SQL    VARCHAR2(30000) := 'SELECT BIN_TO_NUM(';
  V_RETURN NUMBER;
BEGIN
  IF LENGTH(P_BIN) >= 256 THEN
    RAISE_APPLICATION_ERROR(-20001, 'INPUT BIN TOO LONG!');
  END IF;
  IF LTRIM(P_BIN, '01') IS NOT NULL THEN
    RAISE_APPLICATION_ERROR(-20002, 'INPUT STR IS NOT VALID BIN VALUE!');
  END IF;
  FOR I IN 1 .. LENGTH(P_BIN) LOOP
    V_SQL := V_SQL || SUBSTR(P_BIN, I, 1) || ',';
  END LOOP;
  V_SQL := RTRIM(V_SQL, ',') || ') FROM DUAL';
  EXECUTE IMMEDIATE V_SQL
    INTO V_RETURN;
  RETURN V_RETURN;
END;

SQL> SELECT BIT_TO_NUMBER('11010000') FROM DUAL;

BIT_TO_NUMBER('11010000')
-------------------------
                      208

lob类型数据转换为系统文件

1.插入一条blob数据

SQL> create directory ULTLOBDIR as '/home/oracle';

Directory created.

SQL> create table blobtest(col1 BLOB);

Table created.

SQL> declare
a_blob BLOB;
  2    3  bfile_name BFILE := BFILENAME('ULTLOBDIR','tt.txt.bak');
  4  begin
  5  insert into blobtest values (empty_blob())
  6  returning col1 into a_blob;
  7  dbms_lob.fileopen(bfile_name);
  8  dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name));
  9  dbms_lob.fileclose(bfile_name);
 10  commit;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select dbms_lob.getlength(col1) from blobtest;

DBMS_LOB.GETLENGTH(COL1)
------------------------
                    4829

SQL> !pwd
/home/oracle

SQL> !ls -l tt.txt.bak
-rw-r--r-- 1 oracle oinstall 4829 03-19 17:26 tt.txt.bak

2.创建存储过程

CREATE OR REPLACE PROCEDURE RETRIEVE_LOB_TO_FILE(TEMP_BLOB IN BLOB,
                                                 FILE_PATH IN VARCHAR2,
                                                 FILE_NAME IN VARCHAR2) IS
  DATA_BUFFER   RAW(32767);
  POSITION      INTEGER := 1;
  FILEHANDLE    UTL_FILE.FILE_TYPE;
  ERROR_NUMBER  NUMBER;
  ERROR_MESSAGE VARCHAR2(100);
  BLOB_LENGTH   INTEGER;
  CHUNK_SIZE    BINARY_INTEGER := 32767;
BEGIN
  BLOB_LENGTH := DBMS_LOB.GETLENGTH(TEMP_BLOB);
  FILEHANDLE  := UTL_FILE.FOPEN(FILE_PATH, FILE_NAME, 'wb', 1024);
  WHILE POSITION < BLOB_LENGTH LOOP
    DBMS_LOB.READ(TEMP_BLOB, CHUNK_SIZE, POSITION, DATA_BUFFER);
    UTL_FILE.PUT_RAW(FILEHANDLE, DATA_BUFFER);
    POSITION    := POSITION + CHUNK_SIZE;
    DATA_BUFFER := NULL;
  END LOOP;
  UTL_FILE.FCLOSE(FILEHANDLE);
EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      ERROR_NUMBER  := SQLCODE;
      ERROR_MESSAGE := SUBSTR(SQLERRM, 1, 100);
      DBMS_OUTPUT.PUT_LINE('Error #: ' || ERROR_NUMBER);
      DBMS_OUTPUT.PUT_LINE('Error Message: ' || ERROR_MESSAGE);
      UTL_FILE.FCLOSE_ALL;
    END;
END;
/

3.测试读取blob到系统

SQL> declare 
  2  tmp_blob blob default empty_blob(); 
  3  begin 
  4  dbms_lob.createtemporary(tmp_blob, true); 
  5  select col1 into tmp_blob from blobtest; 
  6  retrieve_lob_to_file (tmp_blob, 'ULTLOBDIR','xifenfei.txt'); 
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> !pwd
/home/oracle

SQL> !ls -l xifenfei.txt
-rw-r--r-- 1 oracle oinstall 4829 03-20 23:44 xifenfei.txt

LNNVL函数使用

LNNVL官方解释
LNNVL provides a concise way to evaluate a condition when one or both operands of the condition may be null. The function can be used only in the WHERE clause of a query. It takes as an argument a condition and returns TRUE if the condition is FALSE or UNKNOWN and FALSE if the condition is TRUE. LNNVL can be used anywhere a scalar expression can appear, even in contexts where the IS [NOT] NULL, AND, or OR conditions are not valid but would otherwise be required to account for potential nulls. Oracle Database sometimes uses the LNNVL function internally in this way to rewrite NOT IN conditions as NOT EXISTS conditions. In such cases, output from EXPLAIN PLAN shows this operation in the plan table output. The condition can evaluate any scalar values but cannot be a compound condition containing AND, OR, or BETWEEN.

LNNVL官方解释翻译
lnnvl用于某个语句的where子句中的条件,如果条件为true就返回false;如果条件为UNKNOWN或者false就返回true。该函数不能用于复合条件如AND, OR, or BETWEEN中。

模拟测试环境

SQL> create table xifenfei(name varchar2(20),year number);

Table created.

SQL> insert into xifenfei values('xifenfei2001',2001);

1 row created.

SQL> insert into xifenfei values('xifenfei2002',2002);

1 row created.

SQL> insert into xifenfei values('xifenfei2003',2003);

1 row created.

SQL> insert into xifenfei values('xifenfei2004',2004);

1 row created.

SQL> insert into xifenfei values('xifenfei2005',2005);

1 row created.

SQL> insert into xifenfei values('xifenfei2006',2006);

1 row created.

SQL> insert into xifenfei values('xifenfei2007',2007);

1 row created.

SQL> insert into xifenfei values('xifenfei2008',null);

1 row created.

SQL> insert into xifenfei values('xifenfei2009',2009);

1 row created.

SQL> insert into xifenfei values('xifenfei2010',2010);

1 row created.

SQL> insert into xifenfei values('xifenfei2011',2011);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xifenfei;

NAME                       YEAR
-------------------- ----------
xifenfei2001               2001
xifenfei2002               2002
xifenfei2003               2003
xifenfei2004               2004
xifenfei2005               2005
xifenfei2006               2006
xifenfei2007               2007
xifenfei2008
xifenfei2009               2009
xifenfei2010               2010
xifenfei2011               2011

11 rows selected.

几种情况测试说明

--年份小于2009(lnnvl表示年份大于或者2009包含null)
SQL> select * from xifenfei where lnnvl(year<2009);

NAME                       YEAR
-------------------- ----------
xifenfei2008
xifenfei2009               2009
xifenfei2010               2010
xifenfei2011               2011

--year不为null(lnnvl表示年份为null)
SQL> select * from xifenfei where lnnvl(year is not null);

NAME                       YEAR
-------------------- ----------
xifenfei2008

--年份为null(lnnvl表示年份不为null)
SQL> select * from xifenfei where lnnvl(year is  null);

NAME                       YEAR
-------------------- ----------
xifenfei2001               2001
xifenfei2002               2002
xifenfei2003               2003
xifenfei2004               2004
xifenfei2005               2005
xifenfei2006               2006
xifenfei2007               2007
xifenfei2009               2009
xifenfei2010               2010
xifenfei2011               2011

10 rows selected.

--年份为12345(lnnvl表示年份不为12345)
SQL> select * from xifenfei where lnnvl(year =12345);

NAME                       YEAR
-------------------- ----------
xifenfei2001               2001
xifenfei2002               2002
xifenfei2003               2003
xifenfei2004               2004
xifenfei2005               2005
xifenfei2006               2006
xifenfei2007               2007
xifenfei2008
xifenfei2009               2009
xifenfei2010               2010
xifenfei2011               2011

11 rows selected.

--年份不为12345(lnnvl表示年份为12345或者null)
SQL> select * from xifenfei where lnnvl(year !=12345);

NAME                       YEAR
-------------------- ----------
xifenfei2008

创建含sysdate的函数index

1.模拟环境
创建表插入数据库

[oracle@node1 ~]$ sqlplus chf/xifenfei

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 9 16:27:19 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> create table t_xifenfei(id number,intime date);

Table created.

SQL> DECLARE
  2  i NUMBER;
  3  BEGIN
  4    FOR i IN 1..1000 LOOP
  5      INSERT INTO t_xifenfei VALUES(i,SYSDATE-i);
  6      END LOOP;
  7      COMMIT;
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
      1000


SQL> exec dbms_stats.gather_table_stats(USER,'T_XIFENFEI',cascade => TRUE);

PL/SQL procedure successfully completed.

2.无index查询

SQL> set autot trace exp stat
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |    10 |   120 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |    10 |   120 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NVL("INTIME",SYSDATE@!)>=TO_DATE(' 2011-12-31 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        770  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

SQL> set autot off

这里只是做了一个简单的查询,因为这个nvl(intime,sysdate)的条件,无法使用正常的index,所以没有建立intime索引的测试。

3.尝试创建index

SQL>  create index in_t_xifenfei on t_xifenfei (nvl(intime,sysdate)) online nologging;
 create index in_t_xifenfei on t_xifenfei (nvl(intime,sysdate)) online nologging
                                                      *
ERROR at line 1:
ORA-01743: only pure functions can be indexed


SQL> !oerr ora 1743
01743, 00000, "only pure functions can be indexed"
// *Cause: The indexed function uses SYSDATE or the user environment.
// *Action: PL/SQL functions must be pure (RNDS, RNPS, WNDS, WNPS).  SQL
//          expressions must not use SYSDATE, USER, USERENV(), or anything
//          else dependent on the session state.  NLS-dependent functions
//          are OK.
--因为含有sysdate创建函数index失败


SQL> CREATE OR REPLACE FUNCTION f_xifenfei (itime DATE)
  2  RETURN DATE
  3  IS
  4  otime DATE;
  5  BEGIN 
  6    otime:=NVL(itime,SYSDATE);
  7    RETURN otime;
  8  END;
  9  /

Function created.
--想采用自定义函数屏蔽掉sysdate在创建index时候的影响

SQL>  create index in_t_xifenfei on t_xifenfei (f_xifenfei(intime)) online nologging;
 create index in_t_xifenfei on t_xifenfei (f_xifenfei(intime)) online nologging
                                           *
ERROR at line 1:
ORA-30553: The function is not deterministic


SQL> !oerr ora 30553
30553, 00000, "The function is not deterministic" 
// *Cause:  The function on which the index is defined is not deterministic 
// *Action: If the function is deterministic, mark it DETERMINISTIC.  If it
//          is not deterministic (it depends on package state, database state,
//          current time, or anything other than the function inputs) then
//          do not create the index.  The values returned by a deterministic
//          function should not change even when the function is rewritten or
//          recompiled.
--因为函数缺少deterministic不能使用于index上

SQL> CREATE OR REPLACE FUNCTION f_xifenfei (itime DATE)
  2  RETURN DATE deterministic
  3  IS
  4  otime DATE;
  5  BEGIN 
  6    otime:=NVL(itime,SYSDATE);
  7    RETURN otime;
  8  END;
  9  /

Function created.

SQL> create index in_t_xifenfei on t_xifenfei (f_xifenfei(intime)) online nologging;

Index created.
--创建函数index成功

SQL> exec dbms_stats.gather_table_stats(USER,'T_XIFENFEI',cascade => TRUE);

PL/SQL procedure successfully completed.

4.再次查询
确定已经使用函数index,达到在index中使用sysdate函数index的目的。

SQL> set autot on  exp stat
SQL> select * from t_xifenfei where f_xifenfei(intime)>=to_date('2011-12-31','yyyy-mm-dd');

Execution Plan
----------------------------------------------------------
Plan hash value: 2005404611

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |    10 |   200 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI    |    10 |   200 |     3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IN_T_XIFENFEI |    10 |       |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CHF"."F_XIFENFEI"("INTIME")>=TO_DATE(' 2011-12-31 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        770  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

5.总结说明
5.1)通过函数屏蔽函数index的时候,不能使用sysdate
5.2)在创建函数时,需要指定deterministic关键字