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