Oracle常用语句

葛大爷 互联网 2017-01-14 926
  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);