oracle 相关手册

1. 查看表空间所在位置

select t1.name,t2.name from v$tablespace t1,v$datafile t2  where t1.ts# = t2.ts#;

2. 查看表空间剩余空间,总空间

select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

3. 统计表的大小

SELECT SEGMENT_NAME              TABLE_NAME
      ,SUM(BLOCKS)               BLOCKS
      ,SUM(BYTES)/(1024*1024*1024)    "TABLE_SIZE[g]"
FROM USER_SEGMENTS
WHERE  SEGMENT_TYPE='TABLE'
GROUP BY SEGMENT_NAME
order by    "TABLE_SIZE[g]" desc;

4. 查看死锁的

a. 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;

b. select session_id from v$locked_object;