本文介绍如何查询Oracle数据库中各表空间的使用情况，包括总容量、已使用空间、剩余空间及使用率，并演示了如何调整表空间以实现自动扩展。
/* 2008/06/16 星期一
＊蒙昭良
*环境：linux5 + Oracle10gR2
*某系统出现空间不足，报错！
*/
查询所有表空间的总容量、已经使用、剩余、已经使用的百分比！
select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",
round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc
一般来说可以把上面的复杂的查询语句放入一个文件中，需要时再调用，或者创建一个试图，需要时可以查询。
1 写入文件:#vi /home/mzl/percent_used_tablespace.sql
内容：
select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",
round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc
2 导入：
SQL> @/home/mzl/percent_used_tablespace.sql
SQL> l
1 select a.tablespace_name,a.bytes "Sum",a.bytes-b.bytes "used",b.bytes "free",
2 round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
3 from
4 (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
5 (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
6 where a.tablespace_name=b.tablespace_name
7* order by ((a.bytes-b.bytes)/a.bytes) desc
SQL> /
TABLESPACE_NAME Sum used free percent_used
------------------------------ ---------- ---------- ---------- ------------
SYSTEM 513802240 510656512 3145728 99.39
SYSAUX 262144000 251199488 10944512 95.83
PIONEER_INDX 6291456 5242880 1048576 83.33
UNDOTBS2 52428800 40960000 11468800 78.13
PIONEER_DATA 8388608 6291456 2097152 75
EXAMPLE 104857600 71565312 33292288 68.25
USERS 5242880 3473408 1769472 66.25
PERFSTAT 524288000 150798336 373489664 28.76
PIONEER_UNDO 7340032 1376256 5963776 18.75
PAUL 5242880 65536 5177344 1.25
WENCHUAN 20971520 262144 20709376 1.25
11 rows selected.
或者创建视图：
SQL>create view percent
SQL>as
SQL>select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",
SQL>round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
SQL>from
SQL>(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
SQL>(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
SQL>where a.tablespace_name=b.tablespace_name
SQL>order by ((a.bytes-b.bytes)/a.bytes) desc;
SQL> select * from percent;
TABLESPACE_NAME Sum MB used MB free MB percent_used
------------------------------ ---------- ---------- ---------- ------------
SYSTEM 490 487 3 99.39
SYSAUX 250 239.5625 10.4375 95.83
PIONEER_INDX 6 5 1 83.33
UNDOTBS2 50 39.0625 10.9375 78.13
PIONEER_DATA 8 6 2 75
EXAMPLE 100 68.25 31.75 68.25
USERS 5 3.3125 1.6875 66.25
PERFSTAT 500 143.8125 356.1875 28.76
PIONEER_UNDO 7 1.3125 5.6875 18.75
PAUL 5 .0625 4.9375 1.25
WENCHUAN 20 .25 19.75 1.25
11 rows selected.
查看表空间的数据文件是否是自动扩展：
SQL> l
1* select file_name,tablespace_name,autoextensible from dba_data_files
SQL> /
FILE_NAME TABLESPACE_NAME AUT
--------------------------------------------- ------------------------------ ---
/u01/app/oracle/oradata/orcl/risenet.dbf RISENET
/u01/app/oracle/oradata/orcl/perfstat.dbf PERFSTAT NO
/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE YES
/u01/disk1/users01.dbf USERS YES
/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX YES
/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1
/u01/disk2/system01.dbf SYSTEM YES
/u01/app/oracle/oradata/orcl/undotbs02.dbf UNDOTBS2 NO
/u01/disk1/pioneer_data.dbf PIONEER_DATA YES
/u01/disk2/pioneer_indx.dbf PIONEER_INDX NO
/u01/disk3/pioneer_undo.dbf PIONEER_UNDO NO
FILE_NAME TABLESPACE_NAME AUT
--------------------------------------------- ------------------------------ ---
/u01/app/oracle/oradata/orcl/paul01.dbf PAUL NO
/u01/disk1/wenchuan.dbf WENCHUAN NO
13 rows selected.
比如表空间PIONEER_INDX已经用了83.33％，数据文件不能自动扩展，可以修改成自动扩展，以免数据写满数据文件。
SQL> alter database
2 datafile '/u01/disk2/pioneer_indx.dbf' autoextend on;
Database altered.
SQL> select file_name,tablespace_name,autoextensible from dba_data_files
2 where tablespace_name='PIONEER_INDX';
FILE_NAME TABLESPACE_NAME AUT
--------------------------------------------- ------------------------------ ---
/u01/disk2/pioneer_indx.dbf PIONEER_INDX YES
或者给表空间多加一个自动扩展的数据文件,如果有多个硬盘，可以增加多个数据文件（这样多数据库系统的并发性比较好）
SQL> alter tablespace pioneer_indx
2 add datafile size 30M;
Tablespace altered.
SQL> select file_name,tablespace_name,bytes/1024/1024 "MB" from dba_data_files
2 where tablespace_name='PIONEER_INDX';
FILE_NAME TABLESPACE_NAME
--------------------------------------------- ------------------------------
MB
----------
/u01/disk2/pioneer_indx.dbf PIONEER_INDX
6
/u01/disk5/ORCL/datafile/o1_mf_pioneer__45dpy PIONEER_INDX
fty_.dbf
30
＊蒙昭良
*环境：linux5 + Oracle10gR2
*某系统出现空间不足，报错！
*/
查询所有表空间的总容量、已经使用、剩余、已经使用的百分比！
select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",
round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc
一般来说可以把上面的复杂的查询语句放入一个文件中，需要时再调用，或者创建一个试图，需要时可以查询。
1 写入文件:#vi /home/mzl/percent_used_tablespace.sql
内容：
select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",
round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc
2 导入：
SQL> @/home/mzl/percent_used_tablespace.sql
SQL> l
1 select a.tablespace_name,a.bytes "Sum",a.bytes-b.bytes "used",b.bytes "free",
2 round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
3 from
4 (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
5 (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
6 where a.tablespace_name=b.tablespace_name
7* order by ((a.bytes-b.bytes)/a.bytes) desc
SQL> /
TABLESPACE_NAME Sum used free percent_used
------------------------------ ---------- ---------- ---------- ------------
SYSTEM 513802240 510656512 3145728 99.39
SYSAUX 262144000 251199488 10944512 95.83
PIONEER_INDX 6291456 5242880 1048576 83.33
UNDOTBS2 52428800 40960000 11468800 78.13
PIONEER_DATA 8388608 6291456 2097152 75
EXAMPLE 104857600 71565312 33292288 68.25
USERS 5242880 3473408 1769472 66.25
PERFSTAT 524288000 150798336 373489664 28.76
PIONEER_UNDO 7340032 1376256 5963776 18.75
PAUL 5242880 65536 5177344 1.25
WENCHUAN 20971520 262144 20709376 1.25
11 rows selected.
或者创建视图：
SQL>create view percent
SQL>as
SQL>select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",
SQL>round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
SQL>from
SQL>(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
SQL>(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
SQL>where a.tablespace_name=b.tablespace_name
SQL>order by ((a.bytes-b.bytes)/a.bytes) desc;
SQL> select * from percent;
TABLESPACE_NAME Sum MB used MB free MB percent_used
------------------------------ ---------- ---------- ---------- ------------
SYSTEM 490 487 3 99.39
SYSAUX 250 239.5625 10.4375 95.83
PIONEER_INDX 6 5 1 83.33
UNDOTBS2 50 39.0625 10.9375 78.13
PIONEER_DATA 8 6 2 75
EXAMPLE 100 68.25 31.75 68.25
USERS 5 3.3125 1.6875 66.25
PERFSTAT 500 143.8125 356.1875 28.76
PIONEER_UNDO 7 1.3125 5.6875 18.75
PAUL 5 .0625 4.9375 1.25
WENCHUAN 20 .25 19.75 1.25
11 rows selected.
查看表空间的数据文件是否是自动扩展：
SQL> l
1* select file_name,tablespace_name,autoextensible from dba_data_files
SQL> /
FILE_NAME TABLESPACE_NAME AUT
--------------------------------------------- ------------------------------ ---
/u01/app/oracle/oradata/orcl/risenet.dbf RISENET
/u01/app/oracle/oradata/orcl/perfstat.dbf PERFSTAT NO
/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE YES
/u01/disk1/users01.dbf USERS YES
/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX YES
/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1
/u01/disk2/system01.dbf SYSTEM YES
/u01/app/oracle/oradata/orcl/undotbs02.dbf UNDOTBS2 NO
/u01/disk1/pioneer_data.dbf PIONEER_DATA YES
/u01/disk2/pioneer_indx.dbf PIONEER_INDX NO
/u01/disk3/pioneer_undo.dbf PIONEER_UNDO NO
FILE_NAME TABLESPACE_NAME AUT
--------------------------------------------- ------------------------------ ---
/u01/app/oracle/oradata/orcl/paul01.dbf PAUL NO
/u01/disk1/wenchuan.dbf WENCHUAN NO
13 rows selected.
比如表空间PIONEER_INDX已经用了83.33％，数据文件不能自动扩展，可以修改成自动扩展，以免数据写满数据文件。
SQL> alter database
2 datafile '/u01/disk2/pioneer_indx.dbf' autoextend on;
Database altered.
SQL> select file_name,tablespace_name,autoextensible from dba_data_files
2 where tablespace_name='PIONEER_INDX';
FILE_NAME TABLESPACE_NAME AUT
--------------------------------------------- ------------------------------ ---
/u01/disk2/pioneer_indx.dbf PIONEER_INDX YES
或者给表空间多加一个自动扩展的数据文件,如果有多个硬盘，可以增加多个数据文件（这样多数据库系统的并发性比较好）
SQL> alter tablespace pioneer_indx
2 add datafile size 30M;
Tablespace altered.
SQL> select file_name,tablespace_name,bytes/1024/1024 "MB" from dba_data_files
2 where tablespace_name='PIONEER_INDX';
FILE_NAME TABLESPACE_NAME
--------------------------------------------- ------------------------------
MB
----------
/u01/disk2/pioneer_indx.dbf PIONEER_INDX
6
/u01/disk5/ORCL/datafile/o1_mf_pioneer__45dpy PIONEER_INDX
fty_.dbf
30
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12778571/viewspace-349398/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12778571/viewspace-349398/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
