Thursday, January 8, 2009

Query For Locked Object
#######################################
select * from v$locked_object LOB,dba_objects db
where lob.OBJECT_ID=db.object_id
and db.object_name like upper('%&object_name%');

Query to find Spid/sql text from sid
#######################################
Select SPID,s.sid,s.serial#,s.SQL_ID,sl.SQL_FULLTEXT from v$session s,v$process p,v$sql sl
where s.paddr=p.addr and s.sid='&SID' and s.SQL_ID=sl.SQL_ID;

Query to find sid/sql text from Unix Process iD (spid)
##########################################################

Select p.SPID,s.sid,s.serial#,s.SQL_ID,sl.SQL_FULLTEXT from v$session s,v$process p,v$sql sl
where s.paddr=p.addr and p.spid='&unix_process_ID' and s.SQL_ID=sl.SQL_ID

The following Queries may help you, how much TEM tablespace is used and how much free
#####################################################################################

The following Queries may help you, how much TEM tablespace is used and how much free.

--SORT AREA SIZE

SELECT tablespace_name, extent_size, total_extents, used_extents,
free_extents, max_used_size
FROM v$sort_segment;

SELECT s.username, u.tablespace, u.contents, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr;

select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS
from v$sort_segment;

Solaris Comands
###########################################
How to identify your Solaris is 32bit or 64bit
Run the command

isainfo -v

If the system is running in 32 bit mode, you will see the following output:

32-bit sparc applications

On a 64 bit Solaris system, you'll see:

oracle@ids01 $ isainfo -v
64-bit sparcv9 applications
32-bit sparc applications

Compile APPS Schema Scripts
###############################
SELECT CASE object_type
WHEN 'PACKAGE' THEN
'ALTER '||object_type||' '||owner||'.'||object_name||' COMPILE;'
ELSE
'ALTER PACKAGE '||owner||'.'||object_name||' COMPILE BODY;'
END
FROM dba_objects
WHERE STATUS = 'INVALID'
AND object_type IN ('PACKAGE','PACKAGE

Cancelling a request from the backend for a perticular user.
################################################################
update fnd_concurrent_requests set status_code='D', phase_code='C' where requested_by='1563' and status_code!='D' and phase_code!='C' and CONCURRENT_PROGRAM_ID=45109;

commit;

TKProof command
#####################################################
tkprof sys=no explain=apps/ sort='(prsela,exeela,fchela)'

Mtime Command to find time bound files
####################################################
find . -name "*.trc" -mtime +1 -exec rm {} \;

No comments: