Re: Which events can be set, where's their output

rene.nyffenegger_at_gmx.ch (Rene Nyffenegger) wrote in message news:<Xns90FE531A35CEgnuegischgnueg_at_130.133.1.4>...
> Hi
>
> Until recently, I was unaware of 'ALTER SESSION SET EVENTS='$number $foo'.
> Searching oracle doc didn't help me much; the only thing I found was:
>
> 'EVENT is a parameter used only to debug the system. Do not alter the value
> of this parameter except at the direction of Oracle technical support
> personnel. '
>
> Asking google, I found many examples, but no list of which events
> can be set, what their exact syntax is, and how they would affect
> the database.
>
> Any help as always very appreciated
>
> Rene
>

In his book Practical Oracle 8i Jonathan Lewis documented 5 events associated with execution dumps: 10033 level 1 for sort runs, 10046 level 4 for bind variables , 10053 level 1 or 2 for details of CBO decision making, 10128 level 1, 2, or 4 for partition elimination information, and 10391 level N for PQO. Steve Adams may have mentioned one or two in his Oracle Internals book.

There are hundreds, maybe thousands, of them, but many of them are version specific and probably should in fact only be used under the guidence of support.

Under version 7 - 7.2 you could coalesce tablespaces using: alter session set events = 'immediate trace name coalesce level n'

where n was the file number of the file to be coalesced. With version 7.3 Oracle gave you a command to do this 'alter tablespace x coalesce;'

Under version 7 you can dump a file block using alter session set events 'immediate trace name blockdump level 10059';

where the number is the data block address, dba, which you can get for a file number and block from dbms_utility.make_data_block_address call.

But starting with version 8 there is an apparently undocumented in SQL manual command 'alter system dump file n block min x1 max x2' command that you can use instead.

• Mark D Powell --