HowTo SQLs

How To Check the Hidden Parameters
**********************************

[Oracle Version - 11.1.0.7]

Here is a simple SQL that you can use to look at the hidden parameters of Oracle. You can not neither see these paramers using "SHOW PARAMETERS" or from V$PARAMETER view. Be extra careful before changing these parameters.

SELECT X.KSPPINM NAME, DECODE(BITAND(KSPPIFLG/256, 1), 1, 'TRUE', 'FALSE') SESMOD,
DECODE( BITAND(KSPPIFLG/65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 3, 'IMMEDIATE', 'FALSE' )
SYSMOD, KSPPDESC DESCRIPTION
FROM SYS.X$KSPPI X
WHERE X.INST_ID = USERENV('INSTANCE') AND TRANSLATE(KSPPINM,'_','#') LIKE '#%'
ORDER BY 1 ;

OR

COLUMN parameter FORMAT a45
COLUMN description FORMAT a50 WORD_WRAPPED
COLUMN "Session VALUE" FORMAT a10
COLUMN "Instance VALUE" FORMAT a10
SELECT a.ksppinm "Parameter", a.ksppdesc "Description",
b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE '%_max_cr_dba';

How To Check the SID and Serial# of an OS Process
*************************************

set linesize 999
col MACHINE format a15
col PROGRAM format a20

select a.sid,a.serial#,a.machine,a.program
from v$session a, v$process b
where a.PADDR=b.ADDR and b.spid=:OS_ID;

(:OS_ID is your OS Process ID)

This one shows SQL that is currently "ACTIVE":-

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
/

This shows locks. Sometimes things are going slow, but it's because it is blocked waiting for a lock:

select
object_name,
object_type,
session_id,
type, -- Type or system/user lock
lmode, -- lock mode in which session holds lock
request, block,
ctime -- Time since current mode was granted
from v$locked_object, all_objects, v$lock
where
v$locked_object.object_id = all_objects.object_id
AND v$lock.id1 = all_objects.object_id
AND v$lock.sid = v$locked_object.session_id
order by session_id, ctime desc, object_name
/

This is a good one for finding long operations (e.g. full table scans). If it is because of lots of short operations, nothing will show up.

COLUMN percent FORMAT 999.99
SELECT sid, to_char(start_time,'hh24:mi:ss') stime, message,( sofar/totalwork)* 100 percent
FROM v$session_longops WHERE sofar/totalwork <>
/