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

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

--链接：http://www.itpub.net/thread-2086505-1-1.html

由于文件太大，修改了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;
TRACE_TYPE            PRIMARY_ID      QUALIFIER_ID1        QUALIFIER_ID2            WAITS BINDS PLAN_STATS INSTANCE_NAME
--------------------- --------------- -------------------- ------------------------ ----- ----- ---------- ----------------
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'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SYS@book> exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(SERVICE_NAME=>'BOOK');
PL/SQL procedure successfully completed.

SYS@book> select * from dba_enabled_traces;
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');
PL/SQL procedure successfully completed.

SYS@book> select * from dba_enabled_traces;
TRACE_TYPE            PRIMARY_ID      QUALIFIER_ID1        QUALIFIER_ID2           WAITS BINDS PLAN_STATS INSTANCE_NAME
--------------------- --------------- -------------------- ----------------------- ----- ----- ---------- ----------------
SERVICE_MODULE_ACTION SYS$BACKGROUND  MMON_SLAVE           Automatic Report Flush  TRUE  TRUE  FIRST_EXEC
--//应该是全局启动跟踪，关闭就ok了。

==>补充测试：
SYS@book> exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(SERVICE_NAME=>'book',WAITS=>true,BINDS=>true,module_name=>'SQL*Plus');
PL/SQL procedure successfully completed.

SYS@book> select * from dba_enabled_traces;
TRACE_TYPE            PRIMARY_ID      QUALIFIER_ID1        QUALIFIER_ID2           WAITS BINDS PLAN_STATS INSTANCE_NAME
--------------------- --------------- -------------------- ----------------------- ----- ----- ---------- ----------------
SERVICE_MODULE        book            SQL*Plus                                     TRUE  TRUE  FIRST_EXEC
SERVICE               book                                                         TRUE  TRUE  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_ENABLE(SERVICE_NAME=>'book',WAITS=>true,BINDS=>true,module_name=>'SQL*Plus',ACTION_NAME=>'test');
PL/SQL procedure successfully completed.

SYS@book> select * from dba_enabled_traces;
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');
PL/SQL procedure successfully completed.

SYS@book> select * from dba_enabled_traces;
TRACE_TYPE            PRIMARY_ID      QUALIFIER_ID1        QUALIFIER_ID2           WAITS BINDS PLAN_STATS INSTANCE_NAME
--------------------- --------------- -------------------- ----------------------- ----- ----- ---------- ----------------
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

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

*
ERROR at line 1:
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');
PL/SQL procedure successfully completed.

SYS@book> select * from dba_enabled_traces;
TRACE_TYPE            PRIMARY_ID      QUALIFIER_ID1        QUALIFIER_ID2           WAITS BINDS PLAN_STATS INSTANCE_NAME
--------------------- --------------- -------------------- ----------------------- ----- ----- ---------- ----------------
SERVICE_MODULE_ACTION SYS$BACKGROUND  MMON_SLAVE           Automatic Report Flush  TRUE  TRUE  FIRST_EXEC

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2137287/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2137287/