分类：oracle

RAC数据库出现ORA-27090错误

/u01/app/oracle/admin/yyjm/bdump/yyjm2_p000_6379.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:Linux
Node name:node6
Release:2.6.18-92.el5
Version:#1 SMP Tue Apr 29 13:16:15 EDT 2008
Machine:x86_64
Instance name: yyjm2
Redo thread mounted by this instance: 2
Oracle process number: 42
Unix process pid: 6379, image: oracle@node6 (P000)

*** SERVICE NAME:(SYS$BACKGROUND) 2013-05-04 07:19:48.850
*** SESSION ID:(716.11196) 2013-05-04 07:19:48.850
*** 2013-05-04 07:19:48.850
Start recovery for domain 0, valid = 0, flags = 0x1
*** 2013-05-04 07:19:49.082
KCRP: blocks claimed = 10, eliminated = 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 4194304
Longest hash chain = 1
Average hash chain = 10/10 = 1.0
Max compares per lookup = 0
Avg compares per lookup = 0/10 = 0.0
----------------------------------------------
ORA-27090: Message 27090 not found;  product=RDBMS; facility=ORA
Linux-x86_64 Error: 4: Interrupted system call
Additional information: 3
Additional information: 128
Additional information: 65536
----- Recovery Hash Table Statistics ---------
Hash table buckets = 4194304
Longest hash chain = 1
Average hash chain = 10/10 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 33/33 = 1.0
----------------------------------------------

该问题是发生在RAC一个节点崩溃后，所有的错误都发生在并行进程PNNN上。MOS上说这个问题是由于系统参数配置不当所致：ORA-27090: MESSAGE 27090 NOT FOUND; [ID 579108.1]。

内核参数的值：aio-max-nr设置太低，推荐设置为fs.aio-max-nr= 3145728。修改参数使用/sbin/sysctl -p重新加载参数后，重启数据库即可。
下面是OTN上对该问题的讨论
https://forums.oracle.com/forums/thread.jspa?threadID=859368

业务人员报告说不能登录系统，业务几乎停顿.

听到这个消息首先登录主机执行如下命令

[/@zzld03]#sar -u 1 10

 

HP-UX zzld03 B.11.23 U ia64   04/16/13

 

10:32:25    %usr    %sys    %wio   %idle

10:32:26      63       1      26      10

10:32:27      53       1      23      23

10:32:28      49       3      33      16

10:32:29      43       1      39      18

10:32:30      39       1      34      26

10:32:31      35       0      40      24

10:32:32      41       1      37      21

10:32:33      43       1      42      15

10:32:34      40       4      40      15

10:32:35      57      11      27       5

 

Average       46       2      34      17

发现cpu消耗很高

执行top命令检查

从上面的top命令的监控情况来看pid=9362的进程消耗了95.55%的cpu

select s.sid,s.username,s.event,s.wait_time,s.state,s.seconds_in_wait,p.PROGRAM,s.MACHINE

from v$session s,v$process p

where p.spid=9362 and s.PADDR=p.ADDR

根据进程9362找到的语句如下:

select tt.indi_id as indi_id,
       tt.folk_code as folk_code,
       tt.urban_type as urban_type,
       e.mt_pers_type as pers_type,
       tt.pers_type as pers_type_detail,
       tt.culture_code as culture_code,
       tt.housereg_type as housereg_type,
       tt.job_sta_code as job_sta_code,
       tt.city_code as city_code,
       tt.occu_grade_code as occu_grade_code,
       tt.indi_sta as indi_sta,
       tt.kindred_code as kindred_code,
       tt.insr_code as insr_code,
       tt.name as name,
       decode(tt.sex, '1', '男', '0', '女', '未知') as sex,
       to_char(tt.birthday, 'yyyy-mm-dd') as birthday,
       tt.idcard as idcard,
       tt.native as native,
       to_char(tt.job_date, 'yyyy-mm-dd') as job_date,
       tt.retire_date as retire_date,
       tt.telephone as telephone,
       tt.address as address,
       tt.post_code as post_code,
       tt.marri_sta as marri_sta,
       tt.pre_job_years as pre_job_years,
       tt.all_job_years as all_job_years,
       tt.host_indi_id as host_indi_id,
       tt.nothing_flag as nothing_flag,
       tt.speical_pers_flag as speical_pers_flag,
       tt.remark as remark,
       d.folk_name as folk_name,
       e.pers_name as pers_name,
       f.culture_name as culture_name,
       g.housereg_name as housereg_name,
       h.job_sta_name as job_sta_name,
       i.city_name as city_name,
       i.city_class as city_class,
       j.occu_grade_name as occu_grade_name,
       k.indi_sta_name as indi_sta_name,
       l.kindred_name as kindred_name,
       tt.insr_detail_code as insr_detail_code,
       tt.corp_id as corp_id,
       to_char(tt.begin_date, 'yyyy-mm-dd') as begin_date,
       to_char(tt.end_date, 'yyyy-mm-dd') as end_date,
       tt.indi_join_sta as indi_join_sta,
       tt.occu_code as occu_code,
       tt.freeze_sta as freeze_sta,
       tt.posi_code as posi_code,
       tt.hire_type as hire_type,
       tt.work_type as work_type,
       nvl(tt.official_code, '00') as official_code,
       tt.special_code as special_code,
       tt.indi_ins_no as indi_ins_no,
       tt.total_salary as total_salary,
       tt.indi_join_flag as indi_join_flag,
       m.occupation_name as occupation_name,
       n.position_name as position_name,
       o.hire_name as hire_name,
       p.work_type_name as work_type_name,
       q.special_name as special_name,
       tt.corp_name as corp_name,
       tt.corp_code as corp_code,
       tt.corp_type_code as corp_type_code,
       tt.corp_type_name as corp_type_name,
       tt.center_id as center_id,
       tt.veteran_benefit_name as veteran_benefit,
       decode(tt.official_code,
              '0',
              '一般干部',
              '1',
              '副厅以上',
              '2',
              '副厅以下',
              t.official_name) as official_name,
       u.center_name as center_name,
       nvl(v.last_balance, 0) as last_balance
  from (select /*+rule*/
         a.indi_id,
         a.folk_code,
         a.urban_type,
         a.pers_type,
         a.culture_code,
         a.housereg_type,
         a.job_sta_code,
         a.city_code,
         a.occu_grade_code,
         a.indi_sta,
         a.kindred_code,
         a.insr_code,
         a.name,
         a.sex,
         a.birthday,
         a.idcard,
         a.native,
         a.job_date,
         a.retire_date,
         a.telephone,
         a.address,
         a.post_code,
         a.marri_sta,
         a.pre_job_years,
         a.all_job_years,
         a.host_indi_id,
         a.nothing_flag,
         a.speical_pers_flag,
         decode(a.city_code,
                null,
                a.remark,
                (select city.city_name
                   From bs_city city
                  where city.city_code = a.city_code)) remark,
         (select bct.corp_type_name
            From bs_corp_type bct
           where bct.corp_type_code = s.corp_type_code
             and bct.center_id = s.center_id) corp_type_name,
         b.insr_detail_code,
         b.corp_id,
         b.begin_date,
         b.end_date,
         b.indi_join_sta,
         c.occu_code,
         decode(decode(nvl(p.freeze_sta, '0'),
                       '0',
                       nvl(b.freeze_sta, '0'),
                       '1',
                       '9'),
                '0',
                '基金未冻结',
                '1',
                '基金已冻结',
                '9',
                '单位已冻结') as freeze_sta,
         c.posi_code,
         c.hire_type,
         c.work_type,
         nvl(x.veteran_benefit_name, '非优抚对象') as veteran_benefit_name,
         decode(a.pers_type, '3', nvl(c.office_grade, '0'), c.official_code) as official_code,
         c.special_code,
         c.indi_ins_no,
         c.total_salary,
         c.indi_join_flag,
         s.corp_name,
         s.corp_code,
         s.corp_type_code,
         s.center_id
          from bs_corp            s,
               bs_pres_insur      b,
               bs_corp_insure     p,
               bs_corp_pres       c,
               bs_veteran_benefit x,
               bs_insured         a
         where (a.idcard = '430204850922611' or
               a.idcard = '430204198509226110')
           and a.indi_id = b.indi_id
           and a.veteran_benefit_type = x.veteran_benefit_type(+)
           and decode(b.insr_detail_code,
                      '12',
                      '2',
                      '21',
                      '2',
                      '17',
                      '2',
                      b.insr_detail_code) =
               DECODE(a.Sex || '~' || a.Pers_Type || '~' || C.INDI_JOIN_FLAG || '~' || '2',
                      '0~1~1~7',
                      '2',
                      '0~2~1~7',
                      '2',
                      '2')
           and decode(p.insr_detail_code,
                      '12',
                      '2',
                      '21',
                      '2',
                      '17',
                      '2',
                      p.insr_detail_code) =
               DECODE(a.Sex || '~' || a.Pers_Type || '~' || C.INDI_JOIN_FLAG || '~' || '2',
                      '0~1~1~7',
                      '2',
                      '0~2~1~7',
                      '2',
                      '2')
           and s.corp_id = c.corp_id
           and s.corp_id = p.corp_id
           and b.insr_detail_code = p.insr_detail_code
           and b.indi_id = c.indi_id
           and b.corp_id = s.corp_id) tt,
       bs_folk d,
       bs_person_type e,
       bs_culture_stac f,
       bs_housereg_type g,
       bs_job_stac h,
       bs_city i,
       bs_occupation_grade j,
       bs_pers_status k,
       bs_kindred l,
       bs_occupation m,
       bs_position n,
       bs_hired_type o,
       bs_work_type p,
       bs_special q,
       bs_official t,
       bs_center u,
       bs_mdi_indi_acc v
 where tt.folk_code = d.folk_code(+)
   and tt.pers_type = e.pers_type(+)
   and tt.center_id = e.center_id(+)
   and tt.culture_code = f.culture_code(+)
   and tt.housereg_type = g.housereg_type(+)
   and tt.job_sta_code = h.job_sta_code(+)
   and tt.city_code = i.city_code(+)
   and tt.occu_grade_code = j.occu_grade_code(+)
   and tt.indi_sta = k.indi_sta(+)
   and tt.kindred_code = l.kindred_code(+)
   and tt.occu_code = m.occu_code(+)
   and tt.posi_code = n.posi_code(+)
   and tt.hire_type = o.hire_type(+)
   and tt.work_type = p.work_type(+)
   and tt.special_code = q.special_code(+)
   and tt.official_code = t.official_code(+)
   and tt.indi_id = v.indi_id(+)
   and 1 = v.acco_sta(+)
   and tt.center_id = u.center_id
   and tt.center_id in ('430300')

后通过查看该语句的执行计划,发现没有选择合适的索引造成的,而没有选择合适的索引是因为在查询中使用了/*+rule */提示,将该提示去掉后,语句执行正常cpu的利用率也恢复正常,应用也恢复正常.

这就是典刑的一条sql语句拖垮整个系统的案例.

开发人员执行以下语句时报ora-01801错误

在oracle 11.2.0.1.0 在sql语句中使用with table as 写法时触发了ora-03113错误

select pay_bill_no,
       hospital_id,
       hospital_name,
       aaa027,
       insur_name as center_name,
       pers_name,
       aka035,
       indi_count as rc,
       count(pers_name) as ds,
       sum(bac004) as bac004,
       0 as bili,
       sum(total_pay) as total_money,
       sum(fund_pay) as fund_money,
       0 as fund_fee,
       0 as qa_fee,
       0 as offi_fee,
       sum(total_pay_tekun) as total_money_tekun,
       sum(fund_pay_tekun) as fund_money_tekun,
       (select nvl(max(akb070), 0) from kf21 where aaz272 = pay_bill_no) +
       (select nvl(sum(AKB069), 0) from kcb5 where aaz272 = pay_bill_no) akb070,
       (select nvl(max(bkb070), 0) from kf21 where aaz272 = pay_bill_no) bkb070
  from (with abcd as (select decode(a.bac004, '2', 1, 0) as bac004,
                             n.akb079,
                             k.aaa027,
                             n.aaz272 as pay_bill_no,
                             m.aaz107 as hospital_id,
                             b.aaz217 as serial_no,
                             z.aab069 as corp_name,
                             z.aab001 as corp_code,
                             k.aaa129 as insur_name,
                             a.aac003 as name,
                             a.aac002 as idcard,
                             d.aka121 as disease,
                             decode(b.aka042, 'E', '是', '否') as twice_inhosp_flag,
                             '否' as backbur_flag,
                             t.aaa103 as pers_name,
                             sum(c.aae019) as total_pay,
                             decode(a.bac004, '2', sum(c.aae019), 0) as total_pay_tekun,
                             sum(decode(c.aka002,
                                        311901,
                                        c.aae019,
                                        311902,
                                        c.aae019,
                                        311903,
                                        c.aae019,
                                        510900,
                                        c.aae019,
                                        0)) as fund_pay,
                             decode(a.bac004,
                                    '2',
                                    sum(decode(c.aka002,
                                               311901,
                                               c.aae019,
                                               311902,
                                               c.aae019,
                                               311903,
                                               c.aae019,
                                               510900,
                                               c.aae019,
                                               0)),
                                    0) as fund_pay_tekun,
                             a.aac001 as indi_id,
                             e.aab069 as hospital_name,
                             b.aae030 as begin_date,
                             b.aae031 as end_date,
                             to_char(n.aae015, 'yyyy-mm-dd') as check_date,
                             decode(b.aka042,
                                    '1',
                                    '普通住院',
                                    '2',
                                    '急诊或抢救住院',
                                    '3',
                                    '转院住院',
                                    'Q',
                                    '更正报销业务',
                                    'R',
                                    '连续住院',
                                    'Z',
                                    '转科住院',
                                    'B',
                                    '欠费补缴',
                                    'E',
                                    '二次返院',
                                    '') as aka042,
                             decode(y.aka035a,
                                    'A',
                                    '普通病种',
                                    'C',
                                    '特殊病种',
                                    'C1',
                                    '欠费补缴',
                                    'D',
                                    '综合病种',
                                    'E',
                                    '生育病种',
                                    d.aka121) as aka035,
                             y.bkb007,
                             y.bkb008,
                             y.bkb009,
                             y.bkb010,
                             y.bkb030,
                             y.bkb035,
                             y.aka035a,
                             decode(ceil(trunc(to_date(h.aae031, 'yyyymmdd')) -
                                         trunc(to_date(h.aae030, 'yyyymmdd'))),
                                    0,
                                    1,
                                    ceil(trunc(to_date(h.aae031, 'yyyymmdd')) -
                                         trunc(to_date(h.aae030, 'yyyymmdd')))) as days
                        from Ac01 a,
                             Kc21 b,
                             Kc27 c,
                             Ka06 d,
                             Ae10 e,
                             Kb01 f,
                             Kc19 h,
                             Kc25 m,
                             Kf20 n,
                             aa13 k,
                             Kcb4 z,
                             (select aaa102, aaa103
                                from aa23
                               where aae140 = '310'
                                 and aaa027 = 440999) t,
                             (select decode(y.aka120, '', y.aka035, y.aka120) as aka035a,
                                     sum(bkb007) as bkb007,
                                     sum(bkb008) as bkb008,
                                     sum(bkb009) as bkb009,
                                     sum(y.bkb010) as bkb010,
                                     sum(y.bkb030) as bkb030,
                                     sum(y.bkb035) as bkb035
                                from kcb5 y
                               where aaz272 = 8180
                               group by decode(y.aka120,
                                               '',
                                               y.aka035,
                                               y.aka120)) y
                       where b.aac001 = a.aac001
                         and b.aaz217 = c.aaz217
                         and d.aka120 = b.akc196
                         and f.aaz269 = e.Aaz001
                         and b.aaz217 = h.aaz217
                         and b.aaz217 = m.aaz217
                         and f.akb020 = n.akb020
                         and n.aaz272 = m.aaz272
                         and z.aaz217 = b.aaz217
                         and z.baa027 = k.aaa027
                         and b.aaa027 = f.aaa027
                         and n.aka028 = 2
                         and b.aae100 = 1
                         and c.aae100 = 1
                         and h.aae100 = 1
                         and m.aae100 = 1
                         and decode(z.aac066, '1', '311', z.aac066) =
                             t.aaa102(+)
                         and nvl(m.aae167, 0) >= 0
                         and (m.aka035 = y.aka035a or y.aka035a = d.aka120)
                         and m.aaz272 = 8180
                         and n.aaa027 in
                             ('440999', '440901', '440902', '440903')
                       group by n.aaz272,
                                b.aaz217,
                                a.aac003,
                                a.aac002,
                                d.aka121,
                                b.aka042,
                                t.aaa103,
                                e.aab069,
                                b.aae030,
                                b.aae031,
                                m.aaz107,
                                n.aae015,
                                z.aab001,
                                z.aab069,
                                k.aaa129,
                                n.akb079,
                                a.aac001,
                                y.aka035a,
                                y.bkb007,
                                y.bkb008,
                                y.bkb009,
                                y.bkb010,
                                y.bkb030,
                                y.bkb035,
                                k.aaa027,
                                m.aka035,
                                h.aae031,
                                a.bac004,
                                h.aae030
                      union all
                      select decode(a.bac004, '2', 1, 0) as bac004,
                             f.akb079,
                             k.aaa027,
                             d.aaz272 as pay_bill_no,
                             d.aaz107 as hospital_id,
                             0 as serial_no,
                             t1.aab069 as corp_name,
                             e.aaz001 as corp_code,
                             k.aaa129 as insur_name,
                             a.aac003 as name,
                             a.aac002 as idcard,
                             b.aka121 as disease,
                             decode(d.aka042, 'E', '是', '否') as twice_inhosp_flag,
                             '是' as backbur_flag,
                             t.aaa103 as pers_name,
                             d.aae198 as total_pay,
                             decode(a.bac004, '2', d.aae198, 0) as total_pay_tekun,
                             0 as fund_pay,
                             0 as fund_pay_tekun,
                             a.aac001 as indi_id,
                             e.aab069 as hospital_name,
                             d.aae041 as begin_date,
                             d.aae042 as end_date,
                             to_char(f.aae015, 'yyyy-mm-dd') as check_date,
                             decode(d.aka042,
                                    '1',
                                    '普通住院',
                                    '2',
                                    '急诊或抢救住院',
                                    '3',
                                    '转院住院',
                                    'Q',
                                    '更正报销业务',
                                    'R',
                                    '连续住院',
                                    'Z',
                                    '转科住院',
                                    'B',
                                    '欠费补缴',
                                    'E',
                                    '二次返院',
                                    '') as aka042,
                             decode(y.aka035a,
                                    'A',
                                    '普通病种',
                                    'C',
                                    '特殊病种',
                                    'C1',
                                    '欠费补缴',
                                    'D',
                                    '综合病种',
                                    'E',
                                    '生育病种',
                                    b.aka121) as aka035,
                             y.bkb007,
                             y.bkb008,
                             y.bkb009,
                             y.bkb010,
                             y.bkb030,
                             y.bkb035,
                             y.aka035a,
                             decode(ceil(trunc(to_date(d.aae042, 'yyyymmdd')) -
                                         trunc(to_date(d.aae041, 'yyyymmdd'))),
                                    0,
                                    1,
                                    ceil(trunc(to_date(d.aae042, 'yyyymmdd')) -
                                         trunc(to_date(d.aae041, 'yyyymmdd')))) as days
                        from Ac01 a,
                             Ka06 b,
                             Ae10 e,
                             Kb01 c,
                             Kcb1 d,
                             Kf20 f,
                             aa13 k,
                             (select aaa102, aaa103
                                from aa23
                               where aae140 = '310'
                                 and aaa027 = 440999) t,
                             (select decode(y.aka120, '', y.aka035, y.aka120) as aka035a,
                                     sum(bkb007) as bkb007,
                                     sum(bkb008) as bkb008,
                                     sum(bkb009) as bkb009,
                                     sum(y.bkb010) as bkb010,
                                     sum(y.bkb030) as bkb030,
                                     sum(y.bkb035) as bkb035
                                from kcb5 y
                               where aaz272 = 8180
                               group by decode(y.aka120,
                                               '',
                                               y.aka035,
                                               y.aka120)) y,
                             (select ac20.aac001, ae10.aab069
                                from ae10, ac20
                               where ae10.aaz001 = ac20.aab001
                                 and ac20.aae140 = '310') t1
                       where c.aaz269 = e.Aaz001
                         and f.aaz272 = d.aaz272
                         and d.aac001 = a.aac001
                         and b.aka120 = d.akc196
                         and d.aae100 = 1
                         and d.aaz107 = c.aaz107
                         and k.aaa027 = d.aaa027
                         and c.aaa027 = d.aaa027
                         and d.aac001 = t1.aac001
                         and f.aka028 = 2
                         and nvl(d.aae167, 0) >= 0
                         and d.aac066 = t.aaa102(+)
                         and (d.aka035 = y.aka035a or b.aka120 = y.aka035a)
                         and d.aaz272 = 8180), abc as (select count(distinct
                                                                    indi_id) indi_count,
                                                              insur_name,
                                                              pers_name,
                                                              aka035,
                                                              aaa027
                                                         from abcd
                                                        group by insur_name,
                                                                 pers_name,
                                                                 aka035,
                                                                 aaa027)
         select abc.indi_count,
                abc.insur_name,
                abc.pers_name,
                abc.aka035,
                abc.aaa027,
                abcd.hospital_id,
                abcd.hospital_name,
                abcd.bac004,
                abcd.pay_bill_no,
                abcd.total_pay,
                abcd.fund_pay,
                abcd.total_pay_tekun,
                abcd.fund_pay_tekun
           from abc, abcd
          where abc.insur_name = abcd.insur_name
            and abc.pers_name = abcd.pers_name
            and abc.aka035 = abcd.aka035
            and abc.aaa027 = abcd.aaa027)
          group by indi_count,
                   insur_name,
                   pers_name,
                   aka035,
                   aaa027,
                   hospital_id,
                   hospital_name,
                   pay_bill_no
          order by aaa027, pers_name desc;

上面是报ora-03113故障的语句使用了with table as 写法
经过修改语句去掉with table as 写法后正常执行
select pay_bill_no,
       hospital_id,
       hospital_name,
       aaa027,
       insur_name as center_name,
       pers_name,
       aka035,
       indi_count as rc,
       count(pers_name) as ds,
       sum(bac004) as bac004,
       0 as bili,
       sum(total_pay) as total_money,
       sum(fund_pay) as fund_money,
       0 as fund_fee,
       0 as qa_fee,
       0 as offi_fee,
       sum(total_pay_tekun) as total_money_tekun,
       sum(fund_pay_tekun) as fund_money_tekun,
       (select nvl(max(akb070), 0) from kf21 where aaz272 = pay_bill_no) +
       (select nvl(sum(AKB069), 0) from kcb5 where aaz272 = pay_bill_no) akb070,
       (select nvl(max(bkb070), 0) from kf21 where aaz272 = pay_bill_no) bkb070
  from (
         select abc.indi_count,
                abc.insur_name,
                abc.pers_name,
                abc.aka035,
                abc.aaa027,
                abcd.hospital_id,
                abcd.hospital_name,
                abcd.bac004,
                abcd.pay_bill_no,
                abcd.total_pay,
                abcd.fund_pay,
                abcd.total_pay_tekun,
                abcd.fund_pay_tekun
           from
           (
           select count(distinct
                                                                    indi_id) indi_count,
                                                              insur_name,
                                                              pers_name,
                                                              aka035,
                                                              aaa027
                                                         from
                                                         (
                                                         select decode(a.bac004, '2', 1, 0) as bac004,
                             n.akb079,
                             k.aaa027,
                             n.aaz272 as pay_bill_no,
                             m.aaz107 as hospital_id,
                             b.aaz217 as serial_no,
                             z.aab069 as corp_name,
                             z.aab001 as corp_code,
                             k.aaa129 as insur_name,
                             a.aac003 as name,
                             a.aac002 as idcard,
                             d.aka121 as disease,
                             decode(b.aka042, 'E', '是', '否') as twice_inhosp_flag,
                             '否' as backbur_flag,
                             t.aaa103 as pers_name,
                             sum(c.aae019) as total_pay,
                             decode(a.bac004, '2', sum(c.aae019), 0) as total_pay_tekun,
                             sum(decode(c.aka002,
                                        311901,
                                        c.aae019,
                                        311902,
                                        c.aae019,
                                        311903,
                                        c.aae019,
                                        510900,
                                        c.aae019,
                                        0)) as fund_pay,
                             decode(a.bac004,
                                    '2',
                                    sum(decode(c.aka002,
                                               311901,
                                               c.aae019,
                                               311902,
                                               c.aae019,
                                               311903,
                                               c.aae019,
                                               510900,
                                               c.aae019,
                                               0)),
                                    0) as fund_pay_tekun,
                             a.aac001 as indi_id,
                             e.aab069 as hospital_name,
                             b.aae030 as begin_date,
                             b.aae031 as end_date,
                             to_char(n.aae015, 'yyyy-mm-dd') as check_date,
                             decode(b.aka042,
                                    '1',
                                    '普通住院',
                                    '2',
                                    '急诊或抢救住院',
                                    '3',
                                    '转院住院',
                                    'Q',
                                    '更正报销业务',
                                    'R',
                                    '连续住院',
                                    'Z',
                                    '转科住院',
                                    'B',
                                    '欠费补缴',
                                    'E',
                                    '二次返院',
                                    '') as aka042,
                             decode(y.aka035a,
                                    'A',
                                    '普通病种',
                                    'C',
                                    '特殊病种',
                                    'C1',
                                    '欠费补缴',
                                    'D',
                                    '综合病种',
                                    'E',
                                    '生育病种',
                                    d.aka121) as aka035,
                             y.bkb007,
                             y.bkb008,
                             y.bkb009,
                             y.bkb010,
                             y.bkb030,
                             y.bkb035,
                             y.aka035a,
                             decode(ceil(trunc(to_date(h.aae031, 'yyyymmdd')) -
                                         trunc(to_date(h.aae030, 'yyyymmdd'))),
                                    0,
                                    1,
                                    ceil(trunc(to_date(h.aae031, 'yyyymmdd')) -
                                         trunc(to_date(h.aae030, 'yyyymmdd')))) as days
                        from Ac01 a,
                             Kc21 b,
                             Kc27 c,
                             Ka06 d,
                             Ae10 e,
                             Kb01 f,
                             Kc19 h,
                             Kc25 m,
                             Kf20 n,
                             aa13 k,
                             Kcb4 z,
                             (select aaa102, aaa103
                                from aa23
                               where aae140 = '310'
                                 and aaa027 = 440999) t,
                             (select decode(y.aka120, '', y.aka035, y.aka120) as aka035a,
                                     sum(bkb007) as bkb007,
                                     sum(bkb008) as bkb008,
                                     sum(bkb009) as bkb009,
                                     sum(y.bkb010) as bkb010,
                                     sum(y.bkb030) as bkb030,
                                     sum(y.bkb035) as bkb035
                                from kcb5 y
                               where aaz272 = 8180
                               group by decode(y.aka120,
                                               '',
                                               y.aka035,
                                               y.aka120)) y
                       where b.aac001 = a.aac001
                         and b.aaz217 = c.aaz217
                         and d.aka120 = b.akc196
                         and f.aaz269 = e.Aaz001
                         and b.aaz217 = h.aaz217
                         and b.aaz217 = m.aaz217
                         and f.akb020 = n.akb020
                         and n.aaz272 = m.aaz272
                         and z.aaz217 = b.aaz217
                         and z.baa027 = k.aaa027
                         and b.aaa027 = f.aaa027
                         and n.aka028 = 2
                         and b.aae100 = 1
                         and c.aae100 = 1
                         and h.aae100 = 1
                         and m.aae100 = 1
                         and decode(z.aac066, '1', '311', z.aac066) =
                             t.aaa102(+)
                         and nvl(m.aae167, 0) >= 0
                         and (m.aka035 = y.aka035a or y.aka035a = d.aka120)
                         and m.aaz272 = 8180
                         and n.aaa027 in
                             ('440999', '440901', '440902', '440903')
                       group by n.aaz272,
                                b.aaz217,
                                a.aac003,
                                a.aac002,
                                d.aka121,
                                b.aka042,
                                t.aaa103,
                                e.aab069,
                                b.aae030,
                                b.aae031,
                                m.aaz107,
                                n.aae015,
                                z.aab001,
                                z.aab069,
                                k.aaa129,
                                n.akb079,
                                a.aac001,
                                y.aka035a,
                                y.bkb007,
                                y.bkb008,
                                y.bkb009,
                                y.bkb010,
                                y.bkb030,
                                y.bkb035,
                                k.aaa027,
                                m.aka035,
                                h.aae031,
                                a.bac004,
                                h.aae030
                      union all
                      select decode(a.bac004, '2', 1, 0) as bac004,
                             f.akb079,
                             k.aaa027,
                             d.aaz272 as pay_bill_no,
                             d.aaz107 as hospital_id,
                             0 as serial_no,
                             t1.aab069 as corp_name,
                             e.aaz001 as corp_code,
                             k.aaa129 as insur_name,
                             a.aac003 as name,
                             a.aac002 as idcard,
                             b.aka121 as disease,
                             decode(d.aka042, 'E', '是', '否') as twice_inhosp_flag,
                             '是' as backbur_flag,
                             t.aaa103 as pers_name,
                             d.aae198 as total_pay,
                             decode(a.bac004, '2', d.aae198, 0) as total_pay_tekun,
                             0 as fund_pay,
                             0 as fund_pay_tekun,
                             a.aac001 as indi_id,
                             e.aab069 as hospital_name,
                             d.aae041 as begin_date,
                             d.aae042 as end_date,
                             to_char(f.aae015, 'yyyy-mm-dd') as check_date,
                             decode(d.aka042,
                                    '1',
                                    '普通住院',
                                    '2',
                                    '急诊或抢救住院',
                                    '3',
                                    '转院住院',
                                    'Q',
                                    '更正报销业务',
                                    'R',
                                    '连续住院',
                                    'Z',
                                    '转科住院',
                                    'B',
                                    '欠费补缴',
                                    'E',
                                    '二次返院',
                                    '') as aka042,
                             decode(y.aka035a,
                                    'A',
                                    '普通病种',
                                    'C',
                                    '特殊病种',
                                    'C1',
                                    '欠费补缴',
                                    'D',
                                    '综合病种',
                                    'E',
                                    '生育病种',
                                    b.aka121) as aka035,
                             y.bkb007,
                             y.bkb008,
                             y.bkb009,
                             y.bkb010,
                             y.bkb030,
                             y.bkb035,
                             y.aka035a,
                             decode(ceil(trunc(to_date(d.aae042, 'yyyymmdd')) -
                                         trunc(to_date(d.aae041, 'yyyymmdd'))),
                                    0,
                                    1,
                                    ceil(trunc(to_date(d.aae042, 'yyyymmdd')) -
                                         trunc(to_date(d.aae041, 'yyyymmdd')))) as days
                        from Ac01 a,
                             Ka06 b,
                             Ae10 e,
                             Kb01 c,
                             Kcb1 d,
                             Kf20 f,
                             aa13 k,
                             (select aaa102, aaa103
                                from aa23
                               where aae140 = '310'
                                 and aaa027 = 440999) t,
                             (select decode(y.aka120, '', y.aka035, y.aka120) as aka035a,
                                     sum(bkb007) as bkb007,
                                     sum(bkb008) as bkb008,
                                     sum(bkb009) as bkb009,
                                     sum(y.bkb010) as bkb010,
                                     sum(y.bkb030) as bkb030,
                                     sum(y.bkb035) as bkb035
                                from kcb5 y
                               where aaz272 = 8180
                               group by decode(y.aka120,
                                               '',
                                               y.aka035,
                                               y.aka120)) y,
                             (select ac20.aac001, ae10.aab069
                                from ae10, ac20
                               where ae10.aaz001 = ac20.aab001
                                 and ac20.aae140 = '310') t1
                       where c.aaz269 = e.Aaz001
                         and f.aaz272 = d.aaz272
                         and d.aac001 = a.aac001
                         and b.aka120 = d.akc196
                         and d.aae100 = 1
                         and d.aaz107 = c.aaz107
                         and k.aaa027 = d.aaa027
                         and c.aaa027 = d.aaa027
                         and d.aac001 = t1.aac001
                         and f.aka028 = 2
                         and nvl(d.aae167, 0) >= 0
                         and d.aac066 = t.aaa102(+)
                         and (d.aka035 = y.aka035a or b.aka120 = y.aka035a)
                         and d.aaz272 = 8180)
                                                        group by insur_name,
                                                                 pers_name,
                                                                 aka035,
                                                                 aaa027)
           abc,
           (select decode(a.bac004, '2', 1, 0) as bac004,
                             n.akb079,
                             k.aaa027,
                             n.aaz272 as pay_bill_no,
                             m.aaz107 as hospital_id,
                             b.aaz217 as serial_no,
                             z.aab069 as corp_name,
                             z.aab001 as corp_code,
                             k.aaa129 as insur_name,
                             a.aac003 as name,
                             a.aac002 as idcard,
                             d.aka121 as disease,
                             decode(b.aka042, 'E', '是', '否') as twice_inhosp_flag,
                             '否' as backbur_flag,
                             t.aaa103 as pers_name,
                             sum(c.aae019) as total_pay,
                             decode(a.bac004, '2', sum(c.aae019), 0) as total_pay_tekun,
                             sum(decode(c.aka002,
                                        311901,
                                        c.aae019,
                                        311902,
                                        c.aae019,
                                        311903,
                                        c.aae019,
                                        510900,
                                        c.aae019,
                                        0)) as fund_pay,
                             decode(a.bac004,
                                    '2',
                                    sum(decode(c.aka002,
                                               311901,
                                               c.aae019,
                                               311902,
                                               c.aae019,
                                               311903,
                                               c.aae019,
                                               510900,
                                               c.aae019,
                                               0)),
                                    0) as fund_pay_tekun,
                             a.aac001 as indi_id,
                             e.aab069 as hospital_name,
                             b.aae030 as begin_date,
                             b.aae031 as end_date,
                             to_char(n.aae015, 'yyyy-mm-dd') as check_date,
                             decode(b.aka042,
                                    '1',
                                    '普通住院',
                                    '2',
                                    '急诊或抢救住院',
                                    '3',
                                    '转院住院',
                                    'Q',
                                    '更正报销业务',
                                    'R',
                                    '连续住院',
                                    'Z',
                                    '转科住院',
                                    'B',
                                    '欠费补缴',
                                    'E',
                                    '二次返院',
                                    '') as aka042,
                             decode(y.aka035a,
                                    'A',
                                    '普通病种',
                                    'C',
                                    '特殊病种',
                                    'C1',
                                    '欠费补缴',
                                    'D',
                                    '综合病种',
                                    'E',
                                    '生育病种',
                                    d.aka121) as aka035,
                             y.bkb007,
                             y.bkb008,
                             y.bkb009,
                             y.bkb010,
                             y.bkb030,
                             y.bkb035,
                             y.aka035a,
                             decode(ceil(trunc(to_date(h.aae031, 'yyyymmdd')) -
                                         trunc(to_date(h.aae030, 'yyyymmdd'))),
                                    0,
                                    1,
                                    ceil(trunc(to_date(h.aae031, 'yyyymmdd')) -
                                         trunc(to_date(h.aae030, 'yyyymmdd')))) as days
                        from Ac01 a,
                             Kc21 b,
                             Kc27 c,
                             Ka06 d,
                             Ae10 e,
                             Kb01 f,
                             Kc19 h,
                             Kc25 m,
                             Kf20 n,
                             aa13 k,
                             Kcb4 z,
                             (select aaa102, aaa103
                                from aa23
                               where aae140 = '310'
                                 and aaa027 = 440999) t,
                             (select decode(y.aka120, '', y.aka035, y.aka120) as aka035a,
                                     sum(bkb007) as bkb007,
                                     sum(bkb008) as bkb008,
                                     sum(bkb009) as bkb009,
                                     sum(y.bkb010) as bkb010,
                                     sum(y.bkb030) as bkb030,
                                     sum(y.bkb035) as bkb035
                                from kcb5 y
                               where aaz272 = 8180
                               group by decode(y.aka120,
                                               '',
                                               y.aka035,
                                               y.aka120)) y
                       where b.aac001 = a.aac001
                         and b.aaz217 = c.aaz217
                         and d.aka120 = b.akc196
                         and f.aaz269 = e.Aaz001
                         and b.aaz217 = h.aaz217
                         and b.aaz217 = m.aaz217
                         and f.akb020 = n.akb020
                         and n.aaz272 = m.aaz272
                         and z.aaz217 = b.aaz217
                         and z.baa027 = k.aaa027
                         and b.aaa027 = f.aaa027
                         and n.aka028 = 2
                         and b.aae100 = 1
                         and c.aae100 = 1
                         and h.aae100 = 1
                         and m.aae100 = 1
                         and decode(z.aac066, '1', '311', z.aac066) =
                             t.aaa102(+)
                         and nvl(m.aae167, 0) >= 0
                         and (m.aka035 = y.aka035a or y.aka035a = d.aka120)
                         and m.aaz272 = 8180
                         and n.aaa027 in
                             ('440999', '440901', '440902', '440903')
                       group by n.aaz272,
                                b.aaz217,
                                a.aac003,
                                a.aac002,
                                d.aka121,
                                b.aka042,
                                t.aaa103,
                                e.aab069,
                                b.aae030,
                                b.aae031,
                                m.aaz107,
                                n.aae015,
                                z.aab001,
                                z.aab069,
                                k.aaa129,
                                n.akb079,
                                a.aac001,
                                y.aka035a,
                                y.bkb007,
                                y.bkb008,
                                y.bkb009,
                                y.bkb010,
                                y.bkb030,
                                y.bkb035,
                                k.aaa027,
                                m.aka035,
                                h.aae031,
                                a.bac004,
                                h.aae030
                      union all
                      select decode(a.bac004, '2', 1, 0) as bac004,
                             f.akb079,
                             k.aaa027,
                             d.aaz272 as pay_bill_no,
                             d.aaz107 as hospital_id,
                             0 as serial_no,
                             t1.aab069 as corp_name,
                             e.aaz001 as corp_code,
                             k.aaa129 as insur_name,
                             a.aac003 as name,
                             a.aac002 as idcard,
                             b.aka121 as disease,
                             decode(d.aka042, 'E', '是', '否') as twice_inhosp_flag,
                             '是' as backbur_flag,
                             t.aaa103 as pers_name,
                             d.aae198 as total_pay,
                             decode(a.bac004, '2', d.aae198, 0) as total_pay_tekun,
                             0 as fund_pay,
                             0 as fund_pay_tekun,
                             a.aac001 as indi_id,
                             e.aab069 as hospital_name,
                             d.aae041 as begin_date,
                             d.aae042 as end_date,
                             to_char(f.aae015, 'yyyy-mm-dd') as check_date,
                             decode(d.aka042,
                                    '1',
                                    '普通住院',
                                    '2',
                                    '急诊或抢救住院',
                                    '3',
                                    '转院住院',
                                    'Q',
                                    '更正报销业务',
                                    'R',
                                    '连续住院',
                                    'Z',
                                    '转科住院',
                                    'B',
                                    '欠费补缴',
                                    'E',
                                    '二次返院',
                                    '') as aka042,
                             decode(y.aka035a,
                                    'A',
                                    '普通病种',
                                    'C',
                                    '特殊病种',
                                    'C1',
                                    '欠费补缴',
                                    'D',
                                    '综合病种',
                                    'E',
                                    '生育病种',
                                    b.aka121) as aka035,
                             y.bkb007,
                             y.bkb008,
                             y.bkb009,
                             y.bkb010,
                             y.bkb030,
                             y.bkb035,
                             y.aka035a,
                             decode(ceil(trunc(to_date(d.aae042, 'yyyymmdd')) -
                                         trunc(to_date(d.aae041, 'yyyymmdd'))),
                                    0,
                                    1,
                                    ceil(trunc(to_date(d.aae042, 'yyyymmdd')) -
                                         trunc(to_date(d.aae041, 'yyyymmdd')))) as days
                        from Ac01 a,
                             Ka06 b,
                             Ae10 e,
                             Kb01 c,
                             Kcb1 d,
                             Kf20 f,
                             aa13 k,
                             (select aaa102, aaa103
                                from aa23
                               where aae140 = '310'
                                 and aaa027 = 440999) t,
                             (select decode(y.aka120, '', y.aka035, y.aka120) as aka035a,
                                     sum(bkb007) as bkb007,
                                     sum(bkb008) as bkb008,
                                     sum(bkb009) as bkb009,
                                     sum(y.bkb010) as bkb010,
                                     sum(y.bkb030) as bkb030,
                                     sum(y.bkb035) as bkb035
                                from kcb5 y
                               where aaz272 = 8180
                               group by decode(y.aka120,
                                               '',
                                               y.aka035,
                                               y.aka120)) y,
                             (select ac20.aac001, ae10.aab069
                                from ae10, ac20
                               where ae10.aaz001 = ac20.aab001
                                 and ac20.aae140 = '310') t1
                       where c.aaz269 = e.Aaz001
                         and f.aaz272 = d.aaz272
                         and d.aac001 = a.aac001
                         and b.aka120 = d.akc196
                         and d.aae100 = 1
                         and d.aaz107 = c.aaz107
                         and k.aaa027 = d.aaa027
                         and c.aaa027 = d.aaa027
                         and d.aac001 = t1.aac001
                         and f.aka028 = 2
                         and nvl(d.aae167, 0) >= 0
                         and d.aac066 = t.aaa102(+)
                         and (d.aka035 = y.aka035a or b.aka120 = y.aka035a)
                         and d.aaz272 = 8180)
           abcd
          where abc.insur_name = abcd.insur_name
            and abc.pers_name = abcd.pers_name
            and abc.aka035 = abcd.aka035
            and abc.aaa027 = abcd.aaa027)
          group by indi_count,
                   insur_name,
                   pers_name,
                   aka035,
                   aaa027,
                   hospital_id,
                   hospital_name,
                   pay_bill_no
          order by aaa027, pers_name desc;
          

在Oracle 11.2.0.3.0中执行awrrpt.sql生成awr报告报ora-06502错误

ERROR:ORA-06502:PL/SQL:numeric or value error:character string buffer
too small ORA-06502:at "YSY.DBMS_WORKLOAD_REPOSITORY", line 919
ORA-06502:at line 1

这是个bug再,也给出解决方法：

Bug 13527323 - ORA-6502 generating HTML AWR report using awrrpt.sql in Multibyte characterset database [ID 13527323.8]

解决方法：

update WRH$_SQLTEXT set sql_text = SUBSTR(sql_text, 1, 1000);
commit;

数据库是11.2.0.3.0 的rac

在使用impdp导入数据时导入进程终止了.
后检查告警日志发现如下信息

Time drift detected. Please check VKTM trace file for more details.
Thu Mar 28 05:45:49 2013
Time drift detected. Please check VKTM trace file for more details.
Thu Mar 28 07:28:47 2013
Time drift detected. Please check VKTM trace file for more details.
Thu Mar 28 09:06:15 2013
Time drift detected. Please check VKTM trace file for more details.
Thu Mar 28 09:44:25 2013
NOTE: ASMB terminating
Errors in file /oracle/diag/rdbms/xtybdata/xtybdata1/trace/xtybdata1_asmb_3802094.trc:
ORA-15064: communication failure with ASM instance
ORA-03113: end-of-file on communication channel

是asm实例出了故障
检查asm的告警日志

*** 2013-03-28 09:44:25.446
NOTE: ASMB terminating
ORA-15064: communication failure with ASM instance
ORA-03113: end-of-file on communication channel
Process ID:
Session ID: 961 Serial number: 3
error 15064 detected in background process
ORA-15064: communication failure with ASM instance
ORA-03113: end-of-file on communication channel
Process ID:
Session ID: 961 Serial number: 3
kjzduptcctx: Notifying DIAG for crash event
----- Abridged Call Stack Trace -----
ksedsts()+360< -kjzdssdmp()+240<-kjzduptcctx()+228<-kjzdicrshnfy()+100<-ksuitm()+5124<-ksbrdp()+4508<-opirip()+1624<-opidrv()+608<-sou2o()+136<-opimai_real()+188<-ssthrdmain()+268<-main()+204<-__start()+112
----- End of Abridged Call Stack Trace -----

*** 2013-03-28 09:44:25.473
ASMB (ospid: 3802094): terminating the instance due to error 15064

*** 2013-03-28 09:44:29.630
ksuitm: waiting up to [5] seconds before killing DIAG(4063788)

这也没有指出特定的原因,重启该节点,数据库起来了,但是问题的根本原因还是没找到

在linux系统下,将另一台aix机器上的存储mount到linux下通过nfs,然后在linux下使用expdp导出数据存储在mount到linux下的磁盘上报错

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 6

解决访问

Alter system set events '10298 trace name context forever,level 32'

执行这个命令，不需要重新启动,但在下次数据库重新启动后就失效了，需要再次手工执行。

如果想数据库重新启动后，自动执行这个命令，则执行下面的命令：

SQL>alter system set event='10298 trace name context forever,level 32' scope=spfile;

C:\Users\dell>exp ufgov/ufgov@orcl file=d:\hddatabackup\ufgov_20130228.dmp log=d:\hddatabackup\ufgov_20130228.log buffer=40960000


Export: Release 10.2.0.1.0 - Production on 星期四 2月 28 10:13:48 2013

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


EXP-00056:
EXP-00000: Export terminated unsuccessfully

这是一个很普通的错误，通常是由于EXP程序在执行某条命令错误所造成的，可以通过自己手动执行如下脚本解决，

@D:\Program_Files\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catmeta.sql
@D:\Program_Files\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catexp.sql

C:\Users\dell>exp ufgov/ufgov@orcl file=d:\hddatabackup\ufgov_20130228.dmp log=d:\hddatabackup\ufgov_20130228.log buffer=40960000

EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully

如果是测试库，可以尝试：

sqlplus /nolog

SQL> connect / as sysdba
SQL> @D:\Program_Files\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catmetx.sql
SQL> @D:\Program_Files\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql
SQL> exit

如果是生产库，先做备份，可以考虑升级，不行再尝试。

C:\Users\dell>exp ufgov/ufgov@orcl file=d:\hddatabackup\ufgov_20130228.dmp log=d:\hddatabackup\ufgov_20130228.log buffer=40960000

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的用户...
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 UFGOV 的外部函数库名
. 导出 PUBLIC 类型同义词
. 正在导出专用类型同义词
. 正在导出用户 UFGOV 的对象类型定义
即将导出 UFGOV 的对象...
. 正在导出数据库链接
. 正在导出序号
. 正在导出簇定义
. 即将导出 UFGOV 的表通过常规路径...
. . 正在导出表                ADJUST_FZYE_TEMP导出了           0 行
. . 正在导出表                ADJUST_KMYE_TEMP导出了           0 行
. . 正在导出表                       BBSRCYWLX导出了           0 行
. . 正在导出表                        BBYWLXDM导出了           0 行
. . 正在导出表                           BG_BB导出了           0 行
...............

生成sql trace可以有以下几种方式：

1、参数设置：非常传统的方法。
系统级别：
参数文件中指定: sql_trace=true
或

SQL> alter system set sql_trace=true;

注意：系统级别启用sql_trace，会产生大量trace文件，很容易耗尽磁盘空间，因此一般设置会话级别，并且及时关闭。
会话级别：

SQL> alter session set sql_trace=true;
SQL> 执行sql
SQL> alter session set sql_trace=false;

启用跟踪后，跟踪文件保存在user_dump_dest下
可以使用下面的查询来找到生成的跟踪文件

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /


TRACE_FILE_NAME
--------------------------------------------------------------------------------
/oracle/admin/RLZY/udump/rlzy_ora_721532.trc

也可以给要生成的跟踪文件指定标识符来让你更容易的找到跟文件

SQL> alter session set tracefile_identifier='jingyong';

2、使用10046事件：
10046事件级别：
Lv0 – 禁用sql_trace，等价于sql_trace=false
Lv1 – 启用标准的sql_trace功能,等价于sql_trace=true
Lv4 – Level 1 + 绑定变量值(bind values)
Lv8 – Level 1 + 等待事件跟踪(waits)
Lv12 – Level 1 + Level 4 + Level 8

全局设定：
参数文件中指定: event=”10046 trace name context forever,level 12″
或者

SQL> alter system set events '10046 trace name context forever, level 12';
SQL> alter system set events '10046 trace name context off';

注意：系统级别启用sql_trace，会产生大量trace文件，很容易耗尽磁盘空间，因此一般设置会话级别，并且及时关闭。

当前session设定：

SQL> alter session set events '10046 trace name context forever, level 12';
SQL> 执行sql
SQL> alter session set events '10046 trace name context off';

3、dbms_session包：只能跟踪当前会话，不能指定会话。
跟踪当前会话：

SQL> exec dbms_session.set_sql_trace(true);
SQL> 执行sql
SQL> exec dbms_session.set_sql_trace(false);

dbms_session.set_sql_trace相当于alter session set sql_trace，从生成的trace文件可以明确地看
alter session set sql_trace语句。
使用dbms_session.session_trace_enable过程，不仅可以看到等待事件信息还可以看到绑定变量信息，
相当于alter session set events ‘10046 trace name context forever, level 12’;语句从生成的trace文件可以确认。

SQL> exec dbms_session.session_trace_enable(waits=>true,binds=>true);
SQL> 执行sql
SQL> exec dbms_session.session_trace_enable();

4、dbms_support包：不应该使用这种方法，非官方支持。
系统默认没有安装这个包，可以手动执行$ORACLE_HOME/rdbms/admin/bmssupp.sql脚本来创建该包
跟踪当前会话：

SQL> exec dbms_support.start_trace
SQL> 执行sql
SQL> exec dbms_support.stop_trace

跟踪其他会话：等待事件+绑定变量，相当于level 12的10046事件。

SQL> select sid,serial#,username from v$session where ...;
SQL> exec dbms_support.start_trace_in_session(sid=>sid,serial=>serial#,waits=>true,binds=>true);
SQL> exec dbms_support.stop_trace_in_session(sid=>sid,serial=>serial#);

5、dbms_system包：
跟踪其他会话：
使用dbms_system.set_ev设置10046事件

SQL> select sid,serial#,username from v$session where ...;
SQL> exec dbms_system.set_ev(sid,serial#,10046,12,'');
SQL> exec dbms_system.set_ev(sid,serial#,10046,0,'');

但经过测试在10g中使用级别为8,12的跟踪并没有在跟踪文件中生产等待事件信息

6、dbms_monitor包：10g提供，功能非常强大。可在模块级别、动作级别、客户端级别、数据库级别、会话级别进行跟踪。oracle官方支持。
跟踪当前会话：

SQL> exec dbms_monitor.session_trace_enable;
SQL> 执行sql
SQL> exec dbms_monitor.session_trace_disable;

跟踪其他会话：

SQL> exec dbms_monitor.session_trace_enable(session_id=>sid,serial_num=>serial#,waits=>true,binds=>true);
SQL> exec dbms_monitor.session_trace_disable(session_id=>sid,serial_num=>serial#);

7、oradebug
这是sqlplus的工具，需要提供OSPID或者oracle PID。
跟踪当前会话：

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> 执行sql
SQL> oradebug tracefile_name
SQL> oradebug event 10046 trace name context off;
Statement processed.

跟踪其他会话：

SQL> select spid,pid2  from v$process
  2  where addr in (select paddr from v$session where sid=(select distinct sid from v$mystat));
SPID                PID
------------ ----------
1457                 313
SQL> oradebug setospid 1457;
Statement processed.
或者
SQL> oradebug setorapid 313;
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> oradebug tracefile_name
SQL> oradebug event 10046 trace name context off;
Statement processed.

数据库版本是10.2.0.1.0和10.2.0.4.0

SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.04.0 - Prod
SQL> alter session set events '10046 trace name context forever,level 12';

Session altered

SQL> select count(*) from obj$;

  COUNT(*)
----------
     51486

SQL>
SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_2487.trc

对当前会话使用10046级别为12级的跟踪可以看到等待事件信息

select count(*)
from
 obj$


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.04       0.06         23        133          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.04       0.06         23        133          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=133 pr=23 pw=0 time=61480 us)
  51486   INDEX FAST FULL SCAN I_OBJ1 (cr=133 pr=23 pw=0 time=1252937 us)(object id 36)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.01          0.01
  db file sequential read                         3        0.01          0.01
  db file scattered read                          7        0.00          0.00
********************************************************************************

下面来跟踪其它会话

SQL> exec dbms_system.set_ev(147,57,10046,12,'SYS');

PL/SQL procedure successfully completed

SQL> exec dbms_system.set_sql_trace_in_session(147,57,true);

PL/SQL procedure successfully completed

SQL>  exec dbms_system.set_sql_trace_in_session(147,57,false);

PL/SQL procedure successfully completed

SQL>  exec dbms_system.set_ev(147,57,10046,0,'SYS');

但是在跟踪文件中没有等待信息

select count(*)
from
 obj$


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.03       0.03          0        133          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.03       0.03          0        133          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=133 pr=0 pw=0 time=33599 us)
  51486   INDEX FAST FULL SCAN I_OBJ1 (cr=133 pr=0 pw=0 time=876016 us)(object id 36)

但是在全局启用10046级别为12级的跟踪在生成的跟文件中可以看到等待事件信息

select cols,audit$,textlength,intcols,property,flags,rowid
from
 view$ where obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      317      0.06       0.05          0         19          0           0
Execute    438      0.09       0.08          0          0          0           0
Fetch      438      0.08       0.33         56       1314          0         438
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1193      0.23       0.48         56       1333          0         438

Misses in library cache during parse: 20
Misses in library cache during execute: 20
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID VIEW$ (cr=3 pr=3 pw=0 time=20069 us)
      1   INDEX UNIQUE SCAN I_VIEW1 (cr=2 pr=2 pw=0 time=10796 us)(object id 99)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                        56        0.01          0.30
  SQL*Net message to client                      32        0.00          0.00
  SQL*Net message from client                    32        0.00          0.00
********************************************************************************

而使用dbms_support来进行跟踪在生成的跟踪文件中也能看到等待事件信息

SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\dbmssupp.sql

Package created
SQL> exec sys.dbms_support.start_trace_in_session(147,59,true,true);

PL/SQL procedure successfully completed

SQL> exec sys.dbms_support.stop_trace_in_session(147,59);

PL/SQL procedure successfully completed
 select count(*)
from
 col$


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.14       0.29        130        138          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.14       0.30        130        138          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=138 pr=130 pw=0 time=296924 us)
  56008   INDEX FAST FULL SCAN I_COL3 (cr=138 pr=130 pw=0 time=1431460 us)(object id 47)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.02          0.02
  db file sequential read                         1        0.02          0.02
  db file scattered read                         18        0.02          0.21

使用dbms_monitor来跟其他会话在生成的跟踪文件中也能看到等待事件

SQL> exec dbms_monitor.session_trace_enable(147,61,true,true);

PL/SQL procedure successfully completed

SQL> exec dbms_monitor.session_trace_disable(147,61);

PL/SQL procedure successfully completed

select count(*)
from
 ind$


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.03          2          9          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.04          2          9          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=9 pr=2 pw=0 time=37846 us)
   2345   INDEX FAST FULL SCAN I_IND1 (cr=9 pr=2 pw=0 time=107104 us)(object id 39)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.01          0.01
  db file sequential read                         2        0.03          0.03



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.00       0.00          0          0          0           0
Execute      5      0.00       0.00          0          0          0           2
Fetch        3      0.00       0.03          2          9          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       13      0.01       0.04          2          9          0           5

Misses in library cache during parse: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       8        0.00          0.00
  SQL*Net message from client                     7        4.37          4.40
  db file sequential read                         2        0.03          0.03