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 leveel

SELECT trace_type,primary_id,qualifier_id1 FROM dba_enabled_traces;

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

EXEC DBMS_MONITOR.serv_mod_act_trace_disable(service_name=>’ORCL’, module_nam
e=>’HOSTINTCOM.exe’);

You can use the following package to disable the traces:

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s