ORACLE LIST

1. 检查锁语句：
SELECT lpad(' ',DECODE(request,0,0,1))||sid sess, id1, id2, lmode, request, type
FROM   v$lock
WHERE  id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0) ORDER BY id1, request;

RAC为 gv$lock


2. 检查表空间利用率：
SELECT upper(f.tablespace_name) tablespacename,d.Tot_grootte_Mb "total(M)",d.Tot_grootte_Mb - f.total_bytes "use(M)",to_char(round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2),'990.99') "shiyongbi",f.total_bytes "free(M)", f.max_bytes "maxblock(M)" FROM      
    (SELECT tablespace_name,
            round(SUM(bytes)/(1024*1024),2) total_bytes,
            round(MAX(bytes)/(1024*1024),2) max_bytes
      FROM sys.dba_free_space
     GROUP BY tablespace_name) f,
    (SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb
      FROM   sys.dba_data_files dd
      GROUP BY dd.tablespace_name) d
WHERE d.tablespace_name = f.tablespace_name    
ORDER BY 4 DESC;


3.　检查锁住的存储过程：
 select 'ALTER SYSTEM KILL SESSION '''||B.SID||','||B.SERIAL#||''';' kill_session
          --,MACHINE 
           from dba_ddl_locks a,v$session b
          where a.session_id=b.sid and a.name='PRO_CUX_MTL_TRN_FWMS_NEW';


4.　
select sql_text from v$sqlarea where sql_id in(select sql_id from v$session a, v$process b where a.paddr=b.addr and b.spid=$1);