Check database DBA enabled trace in Oracle

Check the trace is enable or disable on Oracle Database

DBA_ENABLED_TRACES view to used by oracle to display that trace is enabled or disable.

DBA_ENABLED_TRACES View

SQL> SELECT * FROM dba_enabled_traces;

OR

SQL>SELECT * FROM sys.wri$_tracing_enabled

Note: If you forget to disable the trace then this view help you to identified at which level trace is enable and you can closed it.

DBA_ENABLED_TRACE column give you more details at which level trace is enabled.

TRACE_TYPE : Level at which trace is enabled like DATABASE, SERVICE MODULE, SERVICE MODULE ACTION, SERVICE and Client ID.
PRIMARY_ID : specific client identifier or service name
QUALIFIER_ID1: specific module name
QUALIFIER_ID2: specific action name
WAITS : wait is traced or not
BINDS : Binds is traced or not
INSTANCE_NAME: Instance name for restricted to named instances.

Example:
No trace is enabled in Oracle

SQL> SELECT * FROM dba_enabled_traces;
no rows selected

For Disable trace at service level and module level

SELECT trace_type,primary_id,qualifier_id1 FROM dba_enabled_traces;

TRACE_TYPE      PRIMARY_ID  QUALIFIER_ID1
--------------  ----------  -------------
SERVICE_MODULE  ORCL        HOSTINTCOM.EXE


You can use the following package to disable the traces:

EXEC DBMS_MONITOR.serv_mod_act_trace_disable(
service_name=>'ORCL',
module_name=>'HOSTINTCOM.exe');

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s