oracle 查看锁表情况及数据库连接情况

--查询表空间名称和大小


  SELECT UPPER (F.TABLESPACE_NAME) "表空间名",


         D.TOT_GROOTTE_MB "表空间大小(M)",


         D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",


         TO_CHAR (ROUND ((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "使用比",


         F.TOTAL_BYTES "空闲空间(M)",


         F.MAX_BYTES "最大块(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;


 




--查看数据库引起锁表的SQL语句：


  SELECT A.USERNAME,


         A.MACHINE,


         A.PROGRAM,


         A.SID,


         A.SERIAL#,


         A.STATUS,


         C.PIECE,


         C.SQL_TEXT


    FROM V$SESSION A,


         V$SQLTEXT C


   WHERE     A.SID IN (SELECT DISTINCT T2.SID


                         FROM V$LOCKED_OBJECT T1,


                              V$SESSION T2


                        WHERE T1.SESSION_ID = T2.SID)


         AND A.SQL_ADDRESS = C.ADDRESS(+)


ORDER BY C.PIECE;


 


 


--查看数据库锁的情况必须要有DBA权限，可以使用一下SQL 语句：
SELECT object_id, session_id, locked_mode FROM v$locked_object;






 SELECT t2.username,


         t2.sid,


         t2.serial#,


         t2.logon_time


    FROM v$locked_object t1, v$session t2


   WHERE t1.session_id = t2.sid


ORDER BY t2.logon_time;


 


 


--查看被锁的表




SELECT P.SPID,
       
       A.SERIAL#,
       
       C.OBJECT_NAME,
       
       B.SESSION_ID,
       
       B.ORACLE_USERNAME,
       
       B.OS_USER_NAME


  FROM V$PROCESS P,
       
       V$SESSION A,
       
       V$LOCKED_OBJECT B,
       
       ALL_OBJECTS C


 WHERE P.ADDR = A.PADDR
      
   AND A.PROCESS = B.PROCESS
      
   AND C.OBJECT_ID = B.OBJECT_ID;


      


     
      


--杀掉进程


ALTER SYSTEM KILL SESSION 'sid,serial#';


 


--查看连接数


SELECT COUNT (*) FROM v$session;


 
--查看并发连接数


SELECT COUNT(*) FROM V$SESSION WHERE STATUS='ACTIVE';


 


--查看连接的进程


SELECT SID, SERIAL#, USERNAME, OSUSER FROM V$SESSION;


 


--查看数据库使用的裸设备


SELECT *
    FROM dba_data_files


ORDER BY file_name;


 


SELECT *


    FROM dba_temp_files


ORDER BY file_name;


 


SELECT *


    FROM v$controlfile


ORDER BY file_name;


 


  SELECT *


    FROM v$logfile;


 


--具体的方法是查询dba_data_files，dba_temp_files，v$controlfile和v$logfile看这四类文件具体占用的裸设备


 


--查询所有用户表使用大小的前三十名


select * from (select segment_name,bytes from dba_segments where owner = USER order by bytes desc ) where rownum <= 30;


 


--以下的SQL语句列出当前数据库建立的会话情况:


select sid,serial#,username,program,machine,status from v$session;




--如果DBA要手工断开某个会话，则执行:
alter system kill session 'SID,SERIAL#';