Oracle中一些字典表的用法

--数据库空间检查
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
       T.TOTAL_BYTES "表空间大小(M)",
       T.TOTAL_BYTES - F.FREE_BYTES "已使用空间(M)",
       F.FREE_BYTES "空闲空间(M)",
       TO_CHAR(ROUND((T.TOTAL_BYTES - F.FREE_BYTES) / T.TOTAL_BYTES * 100,
                     2),
               '90.99') || '%' "使用率"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES
          FROM SYS.DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) T
 WHERE T.TABLESPACE_NAME = F.TABLESPACE_NAME
 ORDER BY F.TABLESPACE_NAME;

--查看表大小
select t.segment_name, sum(bytes) / (1024 * 1024 * 1024) as "size(G)"
  from dba_segments t
 where t.segment_name like 'SAV_ACCT_BAL_D_STAT%'
 group by t.segment_name
 order by 1
 ;

--检查数据库锁
select distinct t.SESSION_ID, t2.SERIAL#, t1.OBJECT_NAME
from v$locked_object t, dba_objects t1, v$session t2
where t.OBJECT_ID=t1.OBJECT_ID
and t.SESSION_ID=t2.SID;

--根据锁查进程号
select pro.spid from v$session ses,v$process pro where ses.sid=459 and ses.paddr=pro.addr;

--杀掉进程 sid,serial#
alter system kill session'210,11562';

--查询正在执行的SQL-----A
SELECT DISTINCT A.ADDR,
                A.PID,
                A.SPID,
                B.SID,
                B.SERIAL#,
                B.USERNAME,
                B.STATUS,
                C.SQL_ID,
                C.SQL_TEXT
  FROM GV$PROCESS A, GV$SESSION B, GV$SQL C
 WHERE A.ADDR = B.PADDR
   AND B.SQL_HASH_VALUE = C.HASH_VALUE
   AND B.STATUS = 'ACTIVE'
   order by B.SID;

--查看SQL执行计划------B
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('36cxhfthyq9rq'));

--查看表统计信息——————————D
SELECT * FROM USER_TAB_STATISTICS  V WHERE V.TABLE_NAME = 'T03_AGREEMENT';