Disable all TRACING in 10g Database

[christofr]

Hi all,

I'm new to the DBA world, and I've got a 10g database which is generating gigabytes of Tracing Files (*.trc) every day.

I'd like to be able to disable all tracing for this database. How would I do this, using SQL Plus?

At this time, using Enterprise Manager is not an option.


Many Thanks.

[fraze]

Sounds like you've got SQL_TRACE set to TRUE at the database level. Check by using SHOW PARAMETER SQL_TRACE in SQLPlus. If its TRUE set it to FALSE

SQL>  sho parameter sql_trace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sql_trace                            boolean     TRUE
SQL>  alter system set sql_trace = false;

System altered.

[christofr]

Hi, thanks.

running SHOW PARAMETER SQL_TRACE tells me that tracing is indeed disabled. I've just found out that the database in question is used for Enterprise Manager.

The tracing files are deposited into a directory called bdump. Does this make a difference?

Are there any other parameters I can try?

Thanks for any help.

[fraze]

What platform is the DB running on?

What are the names of the trace files? Traces in the bdump area are associated with background processes like SMON and PMON etc. The trace file names should have the relevant process name in it e.g. test_smon_14905.trc

Also, have you checked the alert log for any relevant messages?

[christofr]

Hi again,

We're running 10g on Windows Server 2003.

Here is a typical name of one of the trace files:

emrep_j002_2368.trc

Most follow this format - with the numbers changing sequentially upwards. However, there are 3 files in the same directory -

emrep_mman_468.trc
emrep_mmon_2384.trc
emrep_pmon_1180.trc

It seems to be the emrep_j002_2368.trc type files which are building up, just today there are hundreds.


Thanks for your continuing help with this.

[fraze]

The j002 ones are from the jobs run via dbms_job as defined by job_queue_processes.

It sounds to me like you've got some jobs that are submitted via dbms_job (check dba_jobs) that could have tracing turned on as part of the SQL or PL/SQL code.

Do the trace files contain SQL? (Run tkprof against them and look at the output)

Search the tkprof output for "trace" or "alter session"

[Bore]

It seems it's time to log a service request with Oracle.
It is not normal to get generated so many mmon, pmon and mman trace files.
Log a SR and Oracle support will take a look at the trace files and hopefully clarify what's wrong

[PAVB]

fraze already asked you to check alert.log; what do you see in it?

Look for ORA- messages.