oracle优化（1）

数据据库各个表空间增长情况的检查：
SQL >SELECT A.TABLESPACE_NAME,( 1 -(A.TOTAL)/B.TOTAL)* 100 USED_PERCENT
FROM (SELECT TABLESPACE_NAME,SUM (BYTES) TOTAL FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) A,(SELECT TABLESPACE_NAME,SUM (BYTES) TOTAL FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME;

SQL >SELECT UPPER(F.TABLESPACE_NAME) " 表空间名 ",
D.TOT_GROOTTE_MB " 表空间大小 (M)",
        D.TOT_GROOTTE_MB - F.TOTAL_BYTES " 已使用空间 (M)" , TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100 , 2 ), '990.99' ) " 使用比 ",F.TOTAL_BYTES" 空闲空间 (M) ",
        F.MAX_BYTES " 最大块 (M) " FROM (SELECT TABLESPACE_NAME,
ROUND(SUM (BYTES) / ( 1024 * 1024 ), 2 ) TOTAL_BYTES,
ROUND(MAX (BYTES) / ( 1024 * 1024 ), 2 ) MAX_BYTES
FROM SYS .DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
        (SELECT DD.TABLESPACE_NAME,ROUND(SUM (DD.BYTES) / ( 1024 * 1024 ), 2 ) TOT_GROOTTE_MB FROM SYS .DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC ;

查看各个表空间占用磁盘情况 :
SQL >COL TABLESPACE_NAME FORMAT A20;
SQL >SELECT B.FILE_ID FILE_ID,
B.TABLESPACE_NAME TABLESPACE_NAME,
B.BYTES BYTES,
(B.BYTES-SUM (NVL(A.BYTES, 0 ))) USED,
SUM (NVL(A.BYTES, 0 )) FREE,
SUM (NVL(A.BYTES, 0 ))/(B.BYTES)* 100 PERCENT
       FROM DBA_FREE_SPACE A,DBA_DATA_FILES B
      WHERE A.FILE_ID=B.FILE_ID
      GROUP BY B.TABLESPACE_NAME,B.FILE_ID,B.BYTES
      ORDER BY B.FILE_ID;