10046 TRACE file

How to Generate 10046 TRACE file from Oracle Database
-----------------------------------------------------

Database Details
----------------
sqlplus "/as sysdba"

set pages 50000 lines 32767
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME, INSTANCE_NAME, HOST_NAME, DATABASE_ROLE,
OPEN_MODE, version DB_VERSION, LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"
from v$database,gv$instance;

Generate 10046 TRACE file at session level
------------------------------------------
alter session set timed_statistics=true;
alter session set statistics_level=all;
alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier='TEST10046';

alter session set events '10046 trace name context forever, level 12';
--execute the statement which fails, here
alter session set events '10046 trace name context off';

Trace File Location
-------------------
set pages 50000 lines 32767
col NAME for a30
col VALUE for a100
select * from v$diag_info where name like '%Diag Trace';    -------------- 11G

INST_ID  NAME        VALUE
-------  ----------  ---------
         Diag Trace  /trace/file/location/

exit

Listing Trace Files
-------------------
cd /trace/file/location/
ls -lrt | grep -i TEST10046