select a.tablespace_name,a.bytes bytes_used,b.largest,round(((a.bytes - b.bytes)/a.bytes)*,) 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

查询所有表空间的总容量、已经使用、剩余、已经使用的百分比！

select a.tablespace_name,a.bytes// "Sum MB",(a.bytes-b.bytes)// "used MB",b.bytes// "free MB",

round(((a.bytes-b.bytes)/a.bytes)*,) "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

一般来说可以把上面的复杂的查询语句放入一个文件中，需要时再调用，或者创建一个试图，需要时可以查询。

  写入文件:#vi /home/mzl/percent_used_tablespace.sql

内容：

select a.tablespace_name,a.bytes// "Sum MB",(a.bytes-b.bytes)// "used MB",b.bytes// "free MB",

round(((a.bytes-b.bytes)/a.bytes)*,) "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

 导入：

SQL> @/home/mzl/percent_used_tablespace.sql

SQL> l

    select a.tablespace_name,a.bytes "Sum",a.bytes-b.bytes "used",b.bytes "free",

    round(((a.bytes-b.bytes)/a.bytes)*,) "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

SQL> /

或者创建视图：

create view percent

as

select a.tablespace_name,a.bytes// "Sum MB",(a.bytes-b.bytes)// "used MB",b.bytes// "free MB",

round(((a.bytes-b.bytes)/a.bytes)*,) "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;

SQL> select * from percent;

查看表空间的数据文件是否是自动扩展：

SQL>  select file_name,tablespace_name,autoextensible from dba_data_files

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

 rows selected.

比如表空间PIONEER_INDX已经用了83.％，数据文件不能自动扩展，可以修改成自动扩展，以免数据写满数据文件。

SQL> alter database  datafile '/u01/disk2/pioneer_indx.dbf'  autoextend on;

Database altered.

SQL> select file_name,tablespace_name,autoextensible from dba_data_files  where tablespace_name='PIONEER_INDX';

FILE_NAME                                     TABLESPACE_NAME                AUT

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

/u01/disk2/pioneer_indx.dbf                   PIONEER_INDX                   YES

或者给表空间多加一个自动扩展的数据文件,如果有多个硬盘，可以增加多个数据文件（这样多数据库系统的并发性比较好）

SQL> alter tablespace pioneer_indx add datafile size 30M;

Tablespace altered.

SQL> select file_name,tablespace_name,bytes// "MB"

     from dba_data_files

     where tablespace_name='PIONEER_INDX';