SQL Scripts

----------------------------------------------------

Last Modified: 16-JUN-2015

Author : Amit Kumar Srivastava

----------------------------------------------------

Identify trace file

SET LINE 200 PAGES 200

COLUMN curr_trace_file FORMAT A90

SELECT s.sid,

       s.serial#,

       par.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) || '_ora_' || p.spid || '.trc' AS curr_trace_file

FROM   v$session s,

       v$process p,

       v$parameter par

WHERE par.name = 'user_dump_dest'

AND    s.paddr = p.addr

AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID') ;

----------------------------------------------------------------------

select sid, serial#, module, program, username

from v$session

where sid=(select sys_Context('USERENV','SID')

          FROM DUAL

       );

-------------------------------------------------------------

SELECT value

FROM   v$diag_info

WHERE name = 'Default Trace File';

-------------------------------------------------------------

SELECT p.tracefile

FROM   v$session s

       JOIN v$process p ON s.paddr = p.addr

WHERE s.sid =(select sys_Context('USERENV','SID')

          FROM DUAL

       );

Hidden / Undocumented Parameter in Oracle

COL NAME FOR A40
COL VALUE FOR A20
COL DESCRIPTION FOR A30
COL TYPE FOR A20
SET LIN200 PAGES 2000

select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
decode
(a.ksppity, 1,
'boolean', 2,
'string', 3,
'number', 4,
'file', a.ksppity) type,
a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm like '\_%' escape '\'
AND UPPER(a.ksppinm) LIKE UPPER('%&HIDD_PRM%')
order by name;

Monitoring

Backup Recovery

Oracle Certified Master's - Blogs

Home

SQL Scripts

No comments:

Post a Comment

Identify trace file	SET LINE 200 PAGES 200 COLUMN curr_trace_file FORMAT A90 SELECT s.sid, s.serial#, par.value \|\| '/' \|\| LOWER(SYS_CONTEXT('userenv','instance_name')) \|\| '_ora_' \|\| p.spid \|\| '.trc' AS curr_trace_file FROM v$session s, v$process p, v$parameter par WHERE par.name = 'user_dump_dest' AND s.paddr = p.addr AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID') ; ---------------------------------------------------------------------- select sid, serial#, module, program, username from v$session where sid=(select sys_Context('USERENV','SID') FROM DUAL ); ------------------------------------------------------------- SELECT value FROM v$diag_info WHERE name = 'Default Trace File'; ------------------------------------------------------------- SELECT p.tracefile FROM v$session s JOIN v$process p ON s.paddr = p.addr WHERE s.sid =(select sys_Context('USERENV','SID') FROM DUAL );
Hidden / Undocumented Parameter in Oracle	COL NAME FOR A40 COL VALUE FOR A20 COL DESCRIPTION FOR A30 COL TYPE FOR A20 SET LIN200 PAGES 2000 select a.ksppinm name, b.ksppstvl value, b.ksppstdf deflt, decode (a.ksppity, 1, 'boolean', 2, 'string', 3, 'number', 4, 'file', a.ksppity) type, a.ksppdesc description from sys.x$ksppi a, sys.x$ksppcv b where a.indx = b.indx and a.ksppinm like '\_%' escape '\' AND UPPER(a.ksppinm) LIKE UPPER('%&HIDD_PRM%') order by name;