oracle查询表空间脚本,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 expr1is 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