DBA Querys

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 {} \;