题记：今天做系统检查时，发现一套数据库中的一表空间使用率超过了90%，可见，如果不扩大这个表空间，那么数据库很快将被撑挂，并且顺便将本次的操作过程记录下来，分享给大家，谢谢！

使用脚本检查：

SELECT d.status "Status",

d.tablespace_name "Name",

d.contents "Type",

d.extent_management "Extent Management",

to_char(nvl(a.bytes / 1024 / 1024, 0), '99999999.999') "Total Size (M)",

to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024,

'99999999.999') "Used (M)",

to_char(nvl(nvl(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') "Free (M)",

to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),

'990.00') "Used %"

FROM sys.dba_tablespaces d,

(SELECT tablespace_name, SUM(bytes) bytes

FROM dba_data_files

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM(bytes) bytes

FROM dba_free_space

GROUP BY tablespace_name) f

WHERE d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = f.tablespace_name(+)

AND NOT

(d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')

UNION ALL

SELECT d.status "Status",

d.tablespace_name "Name",

d.contents "Type",

d.extent_management "Extent Management",

to_char(nvl(a.bytes / 1024 / 1024, 0), '99999990.900') "Size (M)",

to_char(nvl(t.bytes, 0) / 1024 / 1024, '99999999.999') "Used (M)",

to_char((nvl(a.bytes / 1024 / 1024, 0)) -

(nvl(t.bytes, 0) / 1024 / 1024),

'99999999.999') "Free (M)",

to_char(nvl(t.bytes / a.bytes * 100, 0), '990.00') "Used %"

FROM sys.dba_tablespaces d,

(SELECT tablespace_name, SUM(bytes) bytes

FROM dba_temp_files

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM(bytes_cached) bytes

FROM v$temp_extent_pool

GROUP BY tablespace_name) t

WHERE d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = t.tablespace_name(+)

AND d.extent_management LIKE 'LOCAL'

AND d.contents LIKE 'TEMPORARY'

ORDER BY "Used %" DESC;

结果发现：

Status Name Type Extent Man Total Size (M Used (M) Free (M) Used %

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

ONLINE BILLING_DATA2 PERMANENT LOCAL 44500.000 41558.480 2941.520 93.39

SQL> select file_name,tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name='BILLING_DATA2';

FILE_NAME TABLESPACE_NAME BYTES/1024/1024

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

/dev/rlvsm_data2 BILLING_DATA2 20000

/dev/rlvsm_data3 BILLING_DATA2 24500

确定lvsm_data2、lvsm_data3属于哪一个VG：

GD_HYWG_cManager2_A:/>lslv -L lvsm_data2

LOGICAL VOLUME: lvsm_data2 VOLUME GROUP: datavg

LV IDENTIFIER: 00062d670000d6000000011aaec5d738.40 PERMISSION: read/write

VG STATE: active/complete LV STATE: opened/syncd

TYPE: raw WRITE VERIFY: off