[20170414]产生很大trace文件.txt

[20170414]产生很大trace文件.txt

由于文件太大，修改了max_dump_file_size,于是开始提示由于trace限制过小，而无法写入的错误

on critical error ORA-48913 caught while writing to trace file "f:\app\administrator\diag\rdbms\pdm\pdm\trace\pdm_ora_7488.trc"

Error message: ORA-48913: 鍐欏叆璺熻釜鏂囦欢澶辫触, 杈惧埌浜嗘枃浠跺ぇ灏忛檺鍒?[10485760]

select * from dba_enabled_traces;

TRACE_TYPE            PRIMARY_ID      QUALIFIER_ID1        QUALIFIER_ID2            WAITS BINDS PLAN_STATS INSTANCE_NAME

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

SERVICE               pdm                                                           TRUE  FALSE FIRST_EXEC       

--//我在我的机器上执行：

SYS@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER

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

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> column QUALIFIER_ID1 format a20

SYS@book> select * from dba_enabled_traces;

SERVICE_MODULE_ACTION SYS$BACKGROUND  MMON_SLAVE           Automatic Report Flush   TRUE  TRUE  FIRST_EXEC

--从现象看是否启动某种跟踪，而且是全局的。

SYS@book> exec DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(SERVICE_NAME=>'BOOK');

BEGIN DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(SERVICE_NAME=>'BOOK'); END;

      *

ERROR at line 1:

ORA-06550: line 1, column 7:

PLS-00306: wrong number or types of arguments in call to 'SERV_MOD_ACT_STAT_ENABLE'

PL/SQL: Statement ignored

SYS@book> exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(SERVICE_NAME=>'BOOK');

PL/SQL procedure successfully completed.

TRACE_TYPE            PRIMARY_ID      QUALIFIER_ID1        QUALIFIER_ID2           WAITS BINDS PLAN_STATS INSTANCE_NAME

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

SERVICE               BOOK                                                         TRUE  FALSE FIRST_EXEC

SERVICE_MODULE_ACTION SYS$BACKGROUND  MMON_SLAVE           Automatic Report Flush  TRUE  TRUE  FIRST_EXEC

SYS@book> exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_disable(SERVICE_NAME=>'BOOK');

--//应该是全局启动跟踪，关闭就ok了。

==>补充测试：

SYS@book> exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(SERVICE_NAME=>'book',WAITS=>true,BINDS=>true,module_name=>'SQL*Plus');

SERVICE_MODULE        book            SQL*Plus                                     TRUE  TRUE  FIRST_EXEC

SERVICE               book                                                         TRUE  TRUE  FIRST_EXEC

SYS@book> exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(SERVICE_NAME=>'book',WAITS=>true,BINDS=>true,module_name=>'SQL*Plus',ACTION_NAME=>'test');

TRACE_TYPE            PRIMARY_ID     QUALIFIER_ID1        QUALIFIER_ID2           WAITS BINDS PLAN_STATS INSTANCE_NAME

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

SERVICE               book                                                        TRUE  TRUE  FIRST_EXEC

SERVICE_MODULE_ACTION book           SQL*Plus             test                    TRUE  TRUE  FIRST_EXEC

SERVICE_MODULE_ACTION SYS$BACKGROUND MMON_SLAVE           Automatic Report Flush  TRUE  TRUE  FIRST_EXEC

--//说明module_name对应QUALIFIER_ID1,ACTION_NAME=>QUALIFIER_ID2.

SYS@book> exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_disable(SERVICE_NAME=>'book');

SERVICE_MODULE_ACTION book            SQL*Plus             test                    TRUE  TRUE  FIRST_EXEC

BEGIN DBMS_MONITOR.SERV_MOD_ACT_TRACE_disable(SERVICE_NAME=>'book'); END;

*

ORA-13852: Tracing for service(module/action) book is not enabled

ORA-06512: at "SYS.DBMS_MONITOR", line 105

ORA-06512: at line 1

SYS@book> exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_disable(SERVICE_NAME=>'book',MODULE_NAME=>'SQL*Plus',ACTION_NAME=>'test');

--//我的测试机器不知道为什么开启了这个，还给仔细看看。