Oracle檢視錶空間使用率SQL指令碼

Oracle檢視錶空間使用率SQL指令碼：

 

=================================================================

SELECT D.TABLESPACE_NAME, 

       SPACE || 'M' "SUM_SPACE(M)", 

       BLOCKS "SUM_BLOCKS", 

       SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)", 

       ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' 

          "USED_RATE(%)", 

       FREE_SPACE || 'M' "FREE_SPACE(M)" 

  FROM (  SELECT TABLESPACE_NAME, 

                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 

                 SUM (BLOCKS) BLOCKS 

            FROM DBA_DATA_FILES 

        GROUP BY TABLESPACE_NAME) D, 

       (  SELECT TABLESPACE_NAME, 

                 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE 

            FROM DBA_FREE_SPACE 

        GROUP BY TABLESPACE_NAME) F 

 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 

UNION ALL                                                 --如果有臨時表空間 

SELECT D.TABLESPACE_NAME, 

       SPACE || 'M' "SUM_SPACE(M)", 

       BLOCKS SUM_BLOCKS, 

       USED_SPACE || 'M' "USED_SPACE(M)", 

       ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", 

       NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" 

  FROM (  SELECT TABLESPACE_NAME, 

                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 

                 SUM (BLOCKS) BLOCKS 

            FROM DBA_TEMP_FILES 

        GROUP BY TABLESPACE_NAME) D, 

       (  SELECT TABLESPACE_NAME, 

                 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, 

                 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE 

            FROM V$TEMP_SPACE_HEADER 

        GROUP BY TABLESPACE_NAME) F 

 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 

ORDER BY 1; 

 

=================================================================

 

 

幾個函式的作用：

 

NVL():

Syntax: NVL(expr1,expr2)

Purpose: NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

 

 

ROUND():

Syntax:  ROUND(n [,integer])

Purpose: 四捨五入到指定的位數

Example:

           SQL> select round(3.1415) from dual;

ROUND(3.1415)

-------------

            3

 

SQL> select round(3.1415,2) from dual;

ROUND(3.1415,2)

---------------

           3.14

 

||的作用：

用於連線字串。

 

 

(+)的作用：

作用類似於right outer join和left outer join。

Example:

SQL> select * from t1;

 

        ID NAME

---------- -----

         1 a

         2 b

 

SQL> select * from t2;

 

        ID NAME

---------- -----

         1 x

         3 y

 

SQL> select t1.id,t2.name from t1,t2 where t1.id(+)=t2.id;

 

        ID NAME

---------- -----

         1 x

           y

 

SQL> select t1.id,t2.name from t1 right outer join t2 on(t1.id=t2.id);

 

        ID NAME

---------- -----

         1 x

           y

 

SQL> select t1.id,t2.name from t1,t2 where t1.id=t2.id(+);

 

        ID NAME

---------- -----

         1 x

         2

 

SQL> select t1.id,t2.name from t1 left outer join t2 on(t1.id=t2.id);

 

        ID NAME

---------- -----

         1 x

         2