查询所有表空间的总容量、已经使用、剩余、已经使用的百分比,增加容量!...

本文介绍如何查询Oracle数据库中各表空间的使用情况,包括总容量、已使用空间、剩余空间及使用率,并演示了如何调整表空间以实现自动扩展。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

/* 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



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12778571/viewspace-349398/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/12778571/viewspace-349398/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值