[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

--//我的測試機器不知道為什麼開啟了這個，還給仔細看看。