oracle学习笔记---ORACLE维护常用语句

 1、查看当前锁表情况 ：

     select Distinct 'alter system kill session '||chr(39)||b.sid||','||b.serial#||chr(39)||';'

     As cmd,b.username,b.logon_time  from v$locked_object a,v$session b
     where a.session_id=b.sid  order by b.logon_time;

   

     select *   from v$locked_object a,v$session b  where a.session_id=b.sid  order by b.logon_time;

2、查看表空间：

    SELECT   owner, tablespace_name, segment_name, segment_type, ROUND (SUM (BYTES) / 1024 / 1024, 2) "USED(M)"
    FROM dba_segments
    where  segment_type in('INDEX','TABLE') AND tablespace_name='ORAHEALTH' and segment_type='TABLE'
    GROUP BY owner, tablespace_name, segment_name, segment_type ORDER BY SUM (BYTES) DESC;

  

3、查看各表占用空间：

     select   a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024   "used MB",b.bytes/1024/1024 "free MB",

    round(((a.bytes-b.bytes)/a.bytes)*100,2)      "percent_used"  
    from  
    (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name)   a,  
    (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name)   b  
    where   a.tablespace_name=b.tablespace_name  
    order   by   ((a.bytes-b.bytes)/a.bytes)   desc