10053事件处理步骤

10053事件处理步骤
===========================================================

1.到user_dump_dest目录下
$cd /oracle/app/diag/rdbms/da/da2/trace
2.打开10053事件

oracle@cpda02 /oracle/app/diag/rdbms/da/da2/trace $ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Aug 20 11:43:54 2010

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

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

SQL> alter session set events '10053 trace name context forever';

Session altered.

3.执行语句
SQL> select 201007, t1.prov_id, t2.prov_name, t2.pay_act_num
2 from vgopdw.tb_det_prov t1,
3 (select nvl(tb.prov_name, 'H+9z') prov_name,
4 count(distinct ta.serv_num) pay_act_num
5 from vgopdw.tdw_mtv_uord_cmon_stat_m ta,
6 vgopdw.tdw_number_segment_new tb
7 where exists (select ''
8 from vgopdw.tdw_mtv_user_use_d tc
9 where ta.serv_num = tc.serv_num
10 and tc.deduct_type <> 0
11 and tc.statis_month = 201007)
12 and substr(ta.serv_num, 1, 7) = tb.segment
13 and ord_stat <> '4'
14 and statis_month = 201007
15 group by rollup(prov_name)) t2
16 where t1.prov_name = t2.prov_name;

201007 PROV_ PROV_NAME PAY_ACT_NUM
---------- ----- -------------------- -----------
201007 11000 安徽 29480
201007 10100 北京 20427
201007 11100 福建 26273
201007 11300 甘肃 16637
201007 12300 广东 217329
201007 12500 广西 25043
201007 10500 贵州 23581
201007 11500 海南 12879
201007 10800 河北 35442
201007 10900 河南 29566
201007 11600 黑龙江 12096

201007 PROV_ PROV_NAME PAY_ACT_NUM
---------- ----- -------------------- -----------
201007 10600 湖北 30872
201007 12600 湖南 19303
201007 11800 吉林 11987
201007 11700 江苏 68809
201007 12700 江西 21257
201007 12400 辽宁 21800
201007 12800 内蒙古 13273
201007 11900 宁夏 5314
201007 11200 青海 3007
201007 12000 山东 31038
201007 12100 山西 22654

201007 PROV_ PROV_NAME PAY_ACT_NUM
---------- ----- -------------------- -----------
201007 10700 陕西 18157
201007 10200 上海 20062
201007 13000 四川 54050
201007 10300 天津 7706
201007 13100 西藏 2915
201007 12200 新疆 17649
201007 12900 云南 34933
201007 11400 浙江 38045
201007 10400 重庆 11579

31 rows selected.
4.结束trace

SQL> alter session set events '10053 trace name context off';

Session altered.

5.查找trace文件
SQL>exit
oracle@cpda02 /oracle/app/diag/rdbms/da/da2/trace $ls -lrt
-rw-rw---- 1 oracle oinstall 102588 Aug 20 11:45 da2_ora_7013.trc
-rw-rw---- 1 oracle oinstall 25776 Aug 20 11:45 da2_ora_7013.trm