10053事件處理步驟

1.到user_dump_dest目錄下
$cd /oracle/app/diag/rdbms/da/da2/trace
2.開啟10053事件

[@more@]

/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/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