Oracle常用语句

葛大爷 互联网 2017-01-14 2458

  1. 查看被锁的表

    select b.owner, b.object_name, a.session_id, a.locked_mode
      from v$locked_object a, djects b
     where b.ject_id = a.object_id;
    
  2. 查看被锁的表对应的sesiion

    select b.username, b.sid, b.serial#, logon_time
      from v$locked_object a, v$session b
     where a.session_id = b.sid
     order by b.logon_time;
    
  3. 查看表空间使用情况

    select sum(SumMB) / 1024,
           sum(usedMB) / 1024,
           sum(freeMB) / 1024,
           (sum(usedMB) / 1024 + sum(freeMB) / 1024)
      from (select a.tablespace_name,
                   a.bytes / 1024 / 1024 SumMB,
                   (a.bytes - b.bytes) / 1024 / 1024 usedMB,
                   b.bytes / 1024 / 1024 freeMB,
                   round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
              from (select tablespace_name, sum(bytes) bytes
                      from dba_data_files
                     where tablespace_name = 'PORTALS'
                     group by tablespace_name) a,
                   (select tablespace_name, sum(bytes) bytes, max(bytes) largest
                      from dba_free_space
                     where tablespace_name = 'PORTALS'
                     group by tablespace_name) b
             where a.tablespace_name = b.tablespace_name);