INSTEAD OF触发器实现视图DML操作

Posted on 2011 年 12 月 18 日 by 惜分飞

有网友询问了，一个多表关联视图，怎么实现dml操作，其实这个可以使用INSTEAD OF触发器实现
1、准备实验环境
创建两个表和一个视图(两表union关联)，并各自插入一条模拟数据

C:\Users\XIFENFEI>sqlplus chf/xifenfei

SQL*Plus: Release 11.2.0.1.0 Production on 星期日 12月 18 10:57:05 2011

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


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

SQL> CREATE TABLE XFF_T1 (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

表已创建。

SQL> CREATE TABLE XFF_T2 (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

表已创建。

SQL> CREATE VIEW V_XFF_T AS SELECT * FROM XFF_T1 UNION ALL SELECT * FROM XFF_T2;


视图已创建。

SQL> INSERT INTO XFF_T1 VALUES (1, 'XFF_T1');

已创建 1 行。

SQL> INSERT INTO XFF_T2 VALUES (2, 'XFF_T2');

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT * FROM V_XFF_T;

        ID NAME                           
---------- ------------------------------ 
         1 XFF_T1                         
         2 XFF_T2

2、尝试dml操作视图
插入、删除、更新dml操作全部失败

SQL> INSERT INTO V_XFF_T VALUES (3, 'V_XFF_T', );
INSERT INTO V_XFF_T VALUES (3, 'V_XFF_T')
            *
第 1 行出现错误:
ORA-01732: 此视图的数据操纵操作非法


SQL> DELETE FROM V_XFF_T WHERE ID=2;
DELETE FROM V_XFF_T WHERE ID=2
            *
第 1 行出现错误:
ORA-01732: 此视图的数据操纵操作非法


SQL> UPDATE V_XFF_T SET NAME='XIFENFEI' WHERE ID=3;
UPDATE V_XFF_T SET NAME='XIFENFEI' WHERE ID=2
       *
第 1 行出现错误:
ORA-01732: 此视图的数据操纵操作非法

3、创建INSTEAD OF触发器

SQL> CREATE OR REPLACE TRIGGER INSTEADOF_T
  2  INSTEAD OF INSERT OR UPDATE OR DELETE ON V_XFF_T
  3  FOR EACH ROW
  4  BEGIN
  5  IF INSERTING THEN
  6  INSERT INTO XFF_T2 VALUES (:NEW.ID, :NEW.NAME);
  7  ELSIF UPDATING THEN
  8  UPDATE XFF_T2 SET ID = :NEW.ID, NAME = :NEW.NAME
  9  WHERE ID = :OLD.ID;
 10  ELSIF DELETING THEN
 11  DELETE XFF_T2 WHERE ID = :OLD.ID;
 12  END IF;
 13  END;
 14  /

触发器已创建

4、重试dml操作
使用基于INSTEAD OF触发器实现了对复杂视图的dml操作

SQL> INSERT INTO V_XFF_T VALUES (3, 'V_XFF_T');

已创建 1 行。

SQL> SELECT * FROM V_XFF_T;

        ID NAME                           
---------- ------------------------------ 
         1 XFF_T1                         
         2 XFF_T2                         
         3 V_XFF_T                        

SQL> DELETE FROM V_XFF_T WHERE ID=2;

已删除 1 行。

SQL> SELECT * FROM V_XFF_T;

        ID NAME                           
---------- ------------------------------ 
         1 XFF_T1                         
         3 V_XFF_T                        

SQL> UPDATE V_XFF_T SET NAME='XIFENFEI' WHERE ID=3;

已更新 1 行。

SQL> SELECT * FROM V_XFF_T;

        ID NAME                           
---------- ----------------------------- 
         1 XFF_T1                         
         3 XIFENFEI    

SQL> COMMIT;

提交完成。

5、查询基表情况
因为编写的INSTEAD OF触发器是对XFF_T2表作用，所以所有关于该视图的操作，都映射到XFF_T2表中

SQL> SELECT * FROM XFF_T1;

        ID NAME                           
---------- ------------------------------ 
         1 XFF_T1                         

SQL> SELECT * FROM XFF_T2;

        ID NAME                           
---------- ------------------------------ 
         3 XIFENFEI

sysaux数据文件异常恢复

Posted on 2011 年 12 月 17 日 by 惜分飞

案例背景:在我接手这个库之前，因为某种原因sysaux表空间的数据文件离线，该库非归档模式，无备份
一、sysaux数据文件离线原因

Mon Jun  7 03:03:22 2010
KCF: write/open error block=0x67009 online=1
     file=3 /opt/app/oracle/oradata/BAS/sysaux01.dbf
     error=27072 txt: 'Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 421897
Additional information: -1'
Automatic datafile offline due to write error on
file 3: /opt/app/oracle/oradata/BAS/sysaux01.dbf

因为该数据库是非归档模式，估计以前的dba也是一段时间后发现sysaux被离线，因为不是归档模式，无法恢复，就一直放置着，让库处于这样的状态中。

二、sysaux数据文件online
1、使用bbed修改sysaux数据文件的scn，见:bbed 修改datafile header

2、尝试online过程日志如下

Sat Dec 17 19:33:36 2011
ORACLE Instance BAS (pid = 17) - Error 376 encountered while recovering transaction (70, 41) on object 8964.
Sat Dec 17 19:33:36 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_smon_27197.trc:
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/opt/app/oracle/oradata/BAS/sysaux01.dbf'
Sat Dec 17 19:33:37 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_smon_27197.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 4571)
ORA-01110: data file 1: '/opt/app/oracle/oradata/BAS/system01.dbf'
Sat Dec 17 19:38:38 2011
ORACLE Instance BAS (pid = 17) - Error 376 encountered while recovering transaction (70, 41) on object 8964.
Sat Dec 17 19:38:38 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_smon_27197.trc:
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/opt/app/oracle/oradata/BAS/sysaux01.dbf'
Sat Dec 17 19:38:38 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_smon_27197.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 4571)
ORA-01110: data file 1: '/opt/app/oracle/oradata/BAS/system01.dbf'
Sat Dec 17 19:39:47 2011
ALTER DATABASE RECOVER  datafile 3  
Sat Dec 17 19:39:47 2011
Media Recovery Start
 parallel recovery started with 7 processes
Sat Dec 17 19:39:47 2011
Recovery of Online Redo Log: Thread 1 Group 6 Seq 13545 Reading mem 0
  Mem# 0 errs 0: /opt/app/oracle/oradata/BAS/redo0602.log
  Mem# 1 errs 0: /opt/app/oracle/oradata/BAS/redo0601.log
Sat Dec 17 19:39:47 2011
Recovery of Online Redo Log: Thread 1 Group 7 Seq 13546 Reading mem 0
  Mem# 0 errs 0: /opt/app/oracle/oradata/BAS/redo0702.log
  Mem# 1 errs 0: /opt/app/oracle/oradata/BAS/redo0701.log
Sat Dec 17 19:39:47 2011
Media Recovery Complete (BAS)
Completed: ALTER DATABASE RECOVER  datafile 3  
Sat Dec 17 19:39:58 2011
alter database datafile 3 online
Sat Dec 17 19:39:58 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_smon_27197.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/app/oracle/oradata/BAS/sysaux01.dbf'
Sat Dec 17 19:39:58 2011
Completed: alter database datafile 3 online

这个过程虽然在sqlplus中提示online成功，但是alert中的错误警告，以及smon进程占用100%的cup资源，最终导致数据库hang住。

2、分析alert日志和trace文件

alert日志中
Sat Dec 17 19:38:38 2011
ORACLE Instance BAS (pid = 17) - Error 376 encountered while recovering transaction (70, 41) on object 8964.

bas_smon_27197.trc中
[oracle@bas bdump]$ more /opt/app/oracle/admin/BAS/bdump/bas_smon_27197.trc
/opt/app/oracle/admin/BAS/bdump/bas_smon_27197.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      bas
Release:        2.6.9-78.ELsmp
Version:        #1 SMP Wed Jul 9 15:46:26 EDT 2008
Machine:        x86_64
Instance name: BAS
Redo thread mounted by this instance: 1
Oracle process number: 17
Unix process pid: 27197, image: oracle@bas (SMON)

*** SERVICE NAME:() 2011-12-17 19:23:33.179
*** SESSION ID:(5490.1) 2011-12-17 19:23:33.179
SMON: about to recover undo segment 70
ORACLE Instance BAS (pid = 17) - Error 376 encountered while recovering transaction (70, 41) on object 8964.
*** 2011-12-17 19:23:33.188
ksedmp: internal or fatal error
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/opt/app/oracle/oradata/BAS/sysaux01.dbf'

通过这些证明smon在利用undo segment 70在回滚sysaux中的内容，但是因为某种原因该回滚段异常，不能进行回滚，是的smon一直尝试回滚，但是始终不成功，最后数据库hang住，需要解决sysaux的问题，首先需要解决这个回滚段问题(删除异常回滚段)

3、删除异常回滚段,online datafile 3

强制kill掉smon进程，重启数据库
[oracle@bas bdump]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Dec 17 19:52:14 2011

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 8589934592 bytes
Fixed Size                  2034520 bytes
Variable Size            1124074664 bytes
Database Buffers         7398752256 bytes
Redo Buffers               65073152 bytes
Database mounted.

--为了数据库不hang掉，先offline datafile 3
SQL> alter database datafile 3 offline;

Database altered.

SQL> select segment_name,status from dba_rollback_segs;
select segment_name,status from dba_rollback_segs
                                *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only


SQL> alter database open;

Database altered.

SQL> select segment_name,status from dba_rollback_segs where status='NEEDS RECOVERY';

SEGMENT_NAME                   STATUS
------------------------------ ----------------
_SYSSMU70$                     NEEDS RECOVERY

SQL> create pfile='/tmp/pfile' from spfile;

File created.

关闭数据库，在pfile中增加
*._corrupted_rollback_segments=(_SYSSMU70$)

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 8589934592 bytes
Fixed Size                  2034520 bytes
Variable Size            1124074664 bytes
Database Buffers         7398752256 bytes
Redo Buffers               65073152 bytes
Database mounted.
Database opened.
SQL> drop  rollback segment "_SYSSMU70$";

Rollback segment dropped.

SQL> alter database datafile 3 online;
alter database datafile 3 online
*
ERROR at line 1:
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: '/opt/app/oracle/oradata/BAS/sysaux01.dbf'


SQL> recover  datafile 3 ;
Media recovery complete.
SQL> alter database datafile 3 online;

Database altered.

三、解决坏块问题
1、alert日志中坏块记录

Sat Dec 17 20:33:31 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_smon_27772.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 4571)
ORA-01110: data file 1: '/opt/app/oracle/oradata/BAS/system01.dbf'
Sat Dec 17 20:33:54 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28027.trc:
Sat Dec 17 20:43:32 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_smon_27772.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 4571)
ORA-01110: data file 1: '/opt/app/oracle/oradata/BAS/system01.dbf'

2、查询坏块对象

SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  2    FROM DBA_EXTENTS A
 WHERE FILE_ID = &FILE_ID
   AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;  3    4  
Enter value for file_id: 1
old   3:  WHERE FILE_ID = &FILE_ID
new   3:  WHERE FILE_ID = 1
Enter value for block_id: 4571
old   4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
new   4:    AND 4571 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
                                           
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE       TABLESPACE_NAME                PARTITION_NAME
------------------ ------------------------------ ------------------------------
SYS
SMON_SCN_TIME_SCN_IDX
INDEX              SYSTEM

SQL> alter index SMON_SCN_TIME_SCN_IDX rebulid online;
alter index SMON_SCN_TIME_SCN_IDX rebulid online
                                  *
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

SQL> select table_name from dba_indexes where index_name='SMON_SCN_TIME_SCN_IDX';

TABLE_NAME
------------------------------
SMON_SCN_TIME

3、解决坏块问题

SQL> truncate table SMON_SCN_TIME;
truncate table SMON_SCN_TIME
               *
ERROR at line 1:
ORA-03292: Table to be truncated is part of a cluster

SQL> truncate  cluster SMON_SCN_TIME;
truncate  cluster SMON_SCN_TIME
                  *
ERROR at line 1:
ORA-00943: cluster does not exist

SQL> SELECT dbms_metadata.get_ddl('TABLE','SMON_SCN_TIME','SYS') FROM dual;
 
DBMS_METADATA.GET_DDL('TABLE','SMON_SCN_TIME','SYS')
--------------------------------------------------------------------------------
 
  CREATE TABLE "SYS"."SMON_SCN_TIME"
   (    "THREAD" NUMBER,
        "TIME_MP" NUMBER,
        "TIME_DP" DATE,
        "SCN_WRP" NUMBER,
        "SCN_BAS" NUMBER,
        "NUM_MAPPINGS" NUMBER,
        "TIM_SCN_MAP" RAW(1200),
        "SCN" NUMBER DEFAULT 0,
        "ORIG_THREAD" NUMBER DEFAULT 0           /* for downgrade */
   ) CLUSTER "SYS"."SMON_SCN_TO_TIME" ("THREAD")

SQL> truncate cluster smon_scn_to_time;

Cluster truncated.

SQL> alter system flush buffer_cache;

System altered.

四、解决AUTO_SPACE_ADVISOR_JOB引起bug

Sat Dec 17 21:00:38 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28144.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_1], [0], [0], [1], [], [], [], []
Sat Dec 17 21:00:41 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28144.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_1], [0], [0], [1], [], [], [], []
Sat Dec 17 21:00:44 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28144.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []
Sat Dec 17 21:00:47 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28144.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_1], [0], [0], [1], [], [], [], []
Sat Dec 17 21:00:50 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28144.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_1], [0], [0], [1], [], [], [], []
Sat Dec 17 21:00:54 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28144.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []
Sat Dec 17 21:00:57 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28144.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []
Sat Dec 17 21:01:00 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28144.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []
Sat Dec 17 21:01:03 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28144.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []

查看MOS发现(430223.1,785899.1)Segment advisor带来的buffer坏块，可以禁用AUTO_SPACE_ADVISOR_JOB并清空buffer来解决，最终解决办法，升级数据库

SQL> exec dbms_scheduler.disable('AUTO_SPACE_ADVISOR_JOB'); 

PL/SQL procedure successfully completed.

SQL> alter system flush buffer_cache;

System altered.

至此这次数据库sysaux数据文件异常恢复完全结束。再次提醒各位，数据库一定要做好备份和归档工作。

Oracle 10g绕过密码登陆数据库

Posted on 2011 年 12 月 16 日 by 惜分飞

0、数据库版本

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

1、Oracle 10g密码加密猜测
user$表中的password=hash(user||password)

SQL> create user xff identified by xifenfei;

User created.

SQL> create user xf identified by fxifenfei;

User created.

SQL> select name,password from user$ where name in('XF','XFF');

NAME                           PASSWORD
------------------------------ ------------------------------
XF                             1B60F4BFF1DAB500
XFF                            1B60F4BFF1DAB500

2、测试通过修改user$.password饶过oracle密码登陆

--创建两个可以登陆用户
SQL> grant connect to ab identified by xifenfei;

Grant succeeded.


SQL> grant connect to abc identified by xifenfei;

Grant succeeded.

--查看用户名和password内容
SQL> select user#,name,password from user$ where name in ('AB','ABC');

     USER# NAME                           PASSWORD
---------- ------------------------------ ------------------------------
        63 AB                             7AF07A2EFB054758
        64 ABC                            40C0E6EE497444B7

--修改ab用户的password内容和abc相同，即ab用户对应的密码应该为cxifenfei
SQL> update user$ set password='40C0E6EE497444B7' where user#=63;

1 row updated.

SQL> commit;

Commit complete.

SQL> select user#,name,password from user$ where name in ('AB','ABC');

     USER# NAME                           PASSWORD
---------- ------------------------------ ------------------------------
        63 AB                             40C0E6EE497444B7
        64 ABC                            40C0E6EE497444B7

--修改后登陆失败
SQL> conn ab/cxifenfei
ERROR:
ORA-01017: 用户名/口令无效; 登录被拒绝


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
--ab的user$.password被重设为原先值
SQL> select user#,name,password from user$ where name in ('AB','ABC');

     USER# NAME                           PASSWORD
---------- ------------------------------ ------------------------------
        63 AB                             7AF07A2EFB054758
        64 ABC                            40C0E6EE497444B7

SQL>  update user$ set password='40C0E6EE497444B7' where user#=63;

1 row updated.

SQL> commit;     

Commit complete.

SQL> select user#,name,password from user$ where name in ('AB','ABC');

     USER# NAME                           PASSWORD
---------- ------------------------------ ------------------------------
        63 AB                             40C0E6EE497444B7
        64 ABC                            40C0E6EE497444B7

--刷新databuffer和shared_pool
SQL> alter system flush buffer_cache ;

System altered.

SQL> alter system flush shared_buffer;
alter system flush shared_buffer
*
ERROR at line 1:
ORA-02000: missing SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT keyword


SQL> alter system flush shared_pool;

System altered.

--修改ab的密码为cxifenfei成功
SQL> conn ab/cxifenfei
Connected.
SQL> show user;
USER is "AB"

3、绕过密码登陆数据库方法
1)建立一个和你需要登陆用户相似用户(一般是末尾多一个或者几个字符)
2)查询建立用户的user$.password，并修改你需要的用户的password
3)刷新data buffer和shared pool
4)使用你建立的用户多出在字符串+你建立用户的密码登陆你需要登陆用户

in/exists和not in/not exists执行效率

Posted on 2011 年 12 月 16 日 by 惜分飞

一、IN 与EXISTS
1、理解
IN的执行流程
SELECT * FROM T1 WHERE X IN (SELECT Y FROM T2)
事实上可以理解为：
SELECT * FROM T1, (SELECT DISTINCT Y FROM T2) T2 WHERE T1.X = T2.Y
从这里可以看出，IN需要先处理T2表，然后再和T1进行关联

EXISTS的执行流程

SELECT * FROM T1 WHERE EXISTS (SELECT NULL FROM T2 WHERE Y = X)
--可以理解为：
for x in ( select * from t1 ) LOOP
    if ( exists ( select null from t2 where y = x.x )THEN 
        OUTPUT THE RECORD
    end if
end loop

从这里看出，EXISXTS会先查询T1表，然后再LOOP处理T2表

2、结论
对于in 和 exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了。
综合以上对IN/EXISTS的讨论，我们可以得出一个基本通用的结论：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

二、NOT IN 与NOT EXISTS
1、理解
NOT IN的执行流程
SELECT * FROM T1 WHERE X NOT IN (SELECT Y FROM T2)
事实上可以理解为：
SELECT * FROM T1, (SELECT DISTINCT Y FROM T2) T2 WHERE T1.X != T2.Y

NOT EXISTS的执行流程

SELECT .. .. .
  FROM ROLLUP R
 WHERE NOT EXISTS
 (SELECT 'Found' FROM TITLE T WHERE R.SOURCE_ID = T.TITLE_ID);
--可以理解为:
for x in ( select * from rollup )
       loop
           if ( not exists ( that query ) ) then
                  OUTPUT
           end if;
        end;

注意:NOT EXISTS 与 NOT IN 不能完全互相替换，看具体的需求。如果选择的列可以为空，则不能被替换。具体见:in/exists和not in/not exists语意探讨

2、结论
not in 只有当子查询中，select 关键字后的字段有not null约束或者有这种暗示时用not in,另外如果主查询中表大，子查询中的表小但是记录多，则应当使用not in,并使用anti hash join.如果主查询表中记录少，子查询表中记录多，并有索引，可以使用not exists,另外not in最好也可以用/*+ HASH_AJ */或者外连接+is null.一般情况下建议使用not exists

--比如:
SELECT .. .. ..
  FROM ROLLUP R
 WHERE NOT EXISTS
 (SELECT 'Found' FROM TITLE T WHERE R.SOURCE_ID = T.TITLE_ID);
--改成
SELECT .. .. ..
  FROM TITLE T, ROLLUP R
 WHERE R.SOURCE_ID = T.TITLE_ID(+)
   AND T.TITLE_ID IS NULL;
--或者
SELECT /*+ HASH_AJ */
.. .. ..
  FROM ROLLUP R
 WHERE OURCE_ID NOT IN
       (SELECT OURCE_ID FROM TITLE T WHERE OURCE_ID IS NOT NULL);

hosts中缺少localhost.localdomain导致监听启动时间超长

Posted on 2011 年 12 月 15 日 by 惜分飞

0、基本信息

[oracle@localhost ~]$ uname -a
Linux localhost.localdomain 2.6.18-194.el5PAE #1 SMP Tue Mar 16 22:00:21 EDT 2010 i686 i686 i386 GNU/Linux
[oracle@localhost ~]$ hostname
localhost.localdomain
[oracle@localhost ~]$ /sbin/ifconfig 
eth0      Link encap:Ethernet  HWaddr 00:14:22:10:96:AE  
          inet addr:192.168.9.66  Bcast:192.168.11.255  Mask:255.255.252.0
          inet6 addr: fe80::214:22ff:fe10:96ae/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:6597875 errors:0 dropped:0 overruns:0 frame:0
          TX packets:9785915 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:100 
          RX bytes:2526369384 (2.3 GiB)  TX bytes:295978844 (282.2 MiB)

eth0:1    Link encap:Ethernet  HWaddr 00:14:22:10:96:AE  
          inet addr:外网ip  Bcast:211.155.227.175  Mask:255.255.255.240
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

eth1      Link encap:Ethernet  HWaddr 00:14:22:10:96:AF  
          inet addr:192.168.11.50  Bcast:192.168.11.255  Mask:255.255.252.0
          inet6 addr: fe80::214:22ff:fe10:96af/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:999903 errors:0 dropped:0 overruns:0 frame:0
          TX packets:283 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:100 
          RX bytes:104688608 (99.8 MiB)  TX bytes:22777 (22.2 KiB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:8816738 errors:0 dropped:0 overruns:0 frame:0
          TX packets:8816738 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:2726275798 (2.5 GiB)  TX bytes:2726275798 (2.5 GiB)
[oracle@localhost ~]$ more /etc/sysconfig/network
NETWORKING=yes
NETWORKING_IPV6=yes
HOSTNAME=localhost.localdomain

1、hosts文件

[oracle@localhost ~]$ more /etc/hosts
127.0.0.1                 localhost

2、监听文件

[oracle@localhost ~]$ more /opt/oracle/product/10g/network/admin/listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/10g/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = gaxt)
      (ORACLE_HOME = /opt/oracle/product/10g)
      (SID_NAME = gaxt)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 外网IP)(PORT = 1521)) 
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
  )

3、启动监听

[oracle@localhost ~]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 15-DEC-2011 23:27:32

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting /opt/oracle/product/10g/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /opt/oracle/product/10g/network/admin/listener.ora
Log messages written to /opt/oracle/product/10g/network/log/listener.log
Trace information written to /tmp/listener_trc.trc
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=外网IP)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=外网IP)(PORT=1521)))
--这里卡了很久很久才显示成功
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                15-DEC-2011 23:27:32
Uptime                    0 days 0 hr. 3 min. 9 sec
Trace Level               support
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/10g/network/admin/listener.ora
Listener Log File         /opt/oracle/product/10g/network/log/listener.log
Listener Trace File       /tmp/listener_trc.trc
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=外网IP)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "gaxt" has 1 instance(s).
  Instance "gaxt", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

4、开启监听trace

--在/opt/oracle/product/10g/network/admin/listener.ora中加入下列参数
TRACE_LEVEL_LISTENER = 16
TRACE_FILE_LISTENER= LISTENER_TRC
TRACE_DIRECTORY_LISTENER=/tmp

6、重启监听查看trace文件

[15-DEC-2011 23:33:32:938] --- TRACE CONFIGURATION INFORMATION FOLLOWS ---
[15-DEC-2011 23:33:32:938] New trace stream is /tmp/listener_trc.trc
[15-DEC-2011 23:33:32:938] New trace level is 16
…………………………
[15-DEC-2011 23:33:32:981] nsnainconn: no native services in use - returning
[15-DEC-2011 23:33:32:981] nsnainconn: signalling that calling function should not continue
[15-DEC-2011 23:33:32:982] nsnainconn: normal exit
[15-DEC-2011 23:33:32:982] nsnaconn: normal exit
[15-DEC-2011 23:33:32:982] nsaccept: exit (0)
[15-DEC-2011 23:36:41:965] nsglma: WARNING - SNMP master agent is not running OR snmp.ora file does not exist.
[15-DEC-2011 23:36:41:965] nsevwait: entry
[15-DEC-2011 23:36:41:965] nsevwait: 3 registered connection(s)
[15-DEC-2011 23:36:41:965] nsevwait: 0 pre-posted event(s)
[15-DEC-2011 23:36:41:965] nsevwait: waiting for transport event (1 thru 4)...
[15-DEC-2011 23:36:41:965] nsevwait: 1 newly-posted event(s)

这里可以看出监听启动的过程中，在这个地方等待了3分钟之久
MOS:Lsnrctl Start is Extremely Slow or Appears to Hang

7、修改hosts文件

[oracle@localhost ~]$ more /etc/hosts
127.0.0.1               localhost.localdomain localhost

8、重启监听查看trace文件

[15-DEC-2011 23:43:44:555] --- TRACE CONFIGURATION INFORMATION FOLLOWS ---
[15-DEC-2011 23:43:44:555] New trace stream is /tmp/listener_trc.trc
……………………
[15-DEC-2011 23:43:44:627] nsevwait: entry
[15-DEC-2011 23:43:44:627] nsevwait: 3 registered connection(s)
[15-DEC-2011 23:43:44:627] nsevwait: 0 pre-posted event(s)
[15-DEC-2011 23:43:44:627] nsevwait: waiting for transport event (1 thru 4)...

这里可以看出监听启动一共使用时间为:几十毫秒

9、结论
在主机名为localhost.localdomain的hosts文件中缺少127.0.0.1 localhost.localdomain,哪怕监听中配置的是ip地址,也将导致监听启动时间非常长。如果主机名不是localhost.localdomain，那hosts中缺少localhost.localdomain没有影响监听启动。