Useful SQL statements

1. datafiles
SELECT ‘cp ‘||NAME ||‘   /localdisk/oradb/’||SUBSTR(NAME,INSTR(NAME,‘/’,-1,1)+1 )||‘ &’
from (
       select name from v$datafile
       union all
       select name from v$controlfile
       union all
       select member from v$logfile
)
 
2. Rename Datafiles 
SELECT ‘ALTER DATABASE RENAME FILE ”’||NAME ||”’   TO  ”/localdisk/oradb/’||SUBSTR(NAME,INSTR(NAME,‘/’,-1,1)+1 )||”’;’
FROM (
       SELECT NAME FROM V$DATAFILE
       UNION ALL
       SELECT MEMBER FROM V$LOGFILE
)
 
 
3.    datafiles 
SELECT ‘cp ‘||NAME ||‘   [...]

Read Full Post »

SQL stetment of a USER

Below SQL query is used to find the SQL statement of a USER:
select sess.sid, sqltext.piece, sqltext.sql_text, sqltext.command_type
from v$sqltext sqltext, v$session sess
where sqltext.address = sess.sql_address
and sqltext.hash_value = sess.sql_hash_value
and sess.username = ‘NAIKH‘
order by 2;

Read Full Post »

Bloking Locks

SQL query to identify blocking locks:
1.select s.username “Blocking Username”,l1.sid “Blocking SID”, l2.sid “Blocked SID”,s2.username “Blocked Username”from v$lock l1, v$lock l2,v$session s,v$session s2where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2and l1.sid=s.sidand l2.sid=s2.sid;
2.select l.sid SID, decode(l.type,’TM’,’DML’,’TX’,‘Trans’,’UL’,’User’,l.type) Lock_Type,decode(l.lmode,0,’None’,1,’Null’,2,’Row-S’,3,’Row-X’, 4,’Share’,5,’S/Row-X’,6,’Exclusive’, l.lmode) Lock_Held_In,decode(l.request,0,’None’,1,’Null’,2,’Row-S’,3,’Row-X’, 4,’Share’,5,’S/Row-X’,6,’Exclusive’,l.request) Lock_Req_In, l.ctime Duration_Seconds, decode(l.block,0,’NO’,1,’YES’) Blocking from v$lock lwhere l.request != 0 or l.block [...]

Read Full Post »