Trace session problem?

Toad for Oracle & DevOps Toolkit
1

Toad 10.5 GA.

I’m tracing a session in an Oracle 9208 database. I have checked the
oracle user on the server for ulimit:

$ ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 2097152
stack(kbytes) 131072
memory(kbytes) unlimited
coredump(blocks) 4194303

The database parameter MAX_DUMP_FILE_SIZE = 10240.

When starting the trace, I requested UNLIMITED dumpfile size.

The dumpfile is truncated with a message telling me that I’m limited to
“10,485,760 bytes”.

Running SQL Tracker, I see the following:

begin
sys.dbms_system.set_bool_param_in_session(12,
4174,‘timed_statistics’, true);
sys.dbms_system.set_int_param_in_session(12, 4174,‘timed_os_statistics’,
0);
sys.dbms_system.set_int_param_in_session(12,4174,‘max_dump_file_size’,
2147483647);
sys.dbms_system.set_ev(12,4174, 10046, 12, ‘’);
end;

I assume that 2,147,483,647 is the biggest int parameter that can be
set. (In a signed 32 bit integer.)

I thought I’d been silly and forgotten to choose the unlimited dump file
size, so I traced it again. Same result.

ALTER SYSTEM SET MAX_DUMP_FILE_SIZE=unlimited SCOPE=BOTH;

Worked a treat. I’m now tracing away merrily and have already got a huge
(201,094,010 byte) trace file. (That’s going to test my mettle at
reading trace files!)

Anyone got any clues as to why the dump file was limited to 10,485,760
bytes when everything I requested (and apparently set) was no-where near
this limit. Except, maybe, the MAX_DUMP_FILE_SIZE = 10240 (OS Blocks)
which matches exactly if the OS Block size is 1,024.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

2

Maybe try alter session versus alter system so that it’s not database wide
– as unlimited trace/log files can quickly overwhelm a disk J

3

Hi Bert,

Maybe try alter session versus alter system so that it's not
database wide - as unlimited trace/log files can quickly
overwhelm a disk J
I'd normally agree 100%, however, in this case the session was not my
own and is part of an application that we have just had an upgrade to -
and it's running like the proverbial dog nailed to the floor!

One other thing, I'm probably the only DBA here who does traces. Ah, the
joy of hex! :wink:

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

4

Why not then a post-connect database trigger such as this:

CREATE OR REPLACE TRIGGER QUEST_MOUSE_TRIGGER1 AFTER LOGON ON DATABASE BEGIN
TRIGGER1_CODE; END;

PROCEDURE TRIGGER1_CODE

IS

v_project_name VARCHAR2(32);

v_max_dump_size INTEGER;

v_file_name VARCHAR2(512);

BEGIN

IF (quest_mouse.user_exists(USER))

THEN

EXECUTE IMMEDIATE ‘SELECT project_name, max_dump_size FROM QUEST_MOUSE_CONTROL’
INTO v_project_name, v_max_dump_size;

v_file_name := USER||’_’||v_project_name;

EXECUTE IMMEDIATE ‘ALTER SESSION SET tracefile_identifier =
‘’’||v_file_name||’’’’;

IF (v_max_dump_size = 0) THEN

EXECUTE IMMEDIATE ‘ALTER SESSION SET max_dump_file_size = 0’;

ELSE

EXECUTE IMMEDIATE ‘ALTER SESSION SET max_dump_file_size =
‘’’||to_char(v_max_dump_size)||‘M’’’;

END IF;

EXECUTE IMMEDIATE 'INSERT INTO quest_mouse_trace_files

SELECT pa.value || ‘’’’ ||

LOWER(SYS_CONTEXT(’‘userenv’’,’‘instance_name’’)) ||

‘‘ora’’ || p.spid || ‘’_’’ || :1 || ‘’.trc’’

FROM v$session s,

v$process p,

v$parameter pa

WHERE pa.name = ‘‘user_dump_dest’’

AND s.paddr = p.addr

AND s.audsid = SYS_CONTEXT(’‘USERENV’’, ‘‘SESSIONID’’)’

USING v_file_name;

COMMIT;

DBMS_SESSION.SESSION_TRACE_ENABLE (waits => FALSE, binds => TRUE);

END IF;

EXCEPTION

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR(gc_fail_trig_start, gm_fail_trig_start);

END;

5

Morning Bert,

Why not then a post-connect database trigger such as this:
I have used those in the past as well. Thanks. (For exactly this
purpose.)

Unfortunately, this is a database in our test environment, and the
change control rules are simple, we cannot make any changes, without
authority to any test or production databases without following due
process. That takes a minimum of 5 days! :frowning: :frowning: :frowning:

So, I resorted to a quick scope=both, which was actually incorrect! It
should have simply been scope=memory and all would be well again.
Anyway, I have 222 MB of trace to analyse and the database is back to
how it was.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk