AIX系统 -- 为Oracle扩大表空间
MyOracleFei 2011-10-25
题记：今天做系统检查时，发现一套数据库中的一表空间使用率超过了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;
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
MAX LPs: 512 PP SIZE: 128 megabyte(s)
COPIES: 1 SCHED POLICY: parallel
LPs: 240 PPs: 240
STALE PPs: 0 BB POLICY: relocatable
INTER-POLICY: minimum RELOCATABLE: yes
INTRA-POLICY: middle UPPER BOUND: 1024
MOUNT POINT: N/A LABEL: None
MIRROR WRITE CONSISTENCY: on/ACTIVE
EACH LP COPY ON A SEPARATE PV ?: yes
Serialize IO ?: NO
DEVICESUBTYPE : DS_LVZ
GD_HYWG_cManager2_A:/>lslv -L lvsm_data3
LOGICAL VOLUME: lvsm_data3 VOLUME GROUP: datavg
LV IDENTIFIER: 00062d670000d6000000011aaec5d738.45 PERMISSION: read/write
VG STATE: active/complete LV STATE: opened/syncd
TYPE: raw WRITE VERIFY: off
MAX LPs: 512 PP SIZE: 128 megabyte(s)
COPIES: 1 SCHED POLICY: parallel
LPs: 200 PPs: 200
STALE PPs: 0 BB POLICY: relocatable
INTER-POLICY: minimum RELOCATABLE: yes
INTRA-POLICY: middle UPPER BOUND: 1024
MOUNT POINT: N/A LABEL: None
MIRROR WRITE CONSISTENCY: on/ACTIVE
EACH LP COPY ON A SEPARATE PV ?: yes
Serialize IO ?: NO
DEVICESUBTYPE : DS_LVZ
由上面可见，lvsm_data2、lvsm_data3这两个LV均属于datavg：
GD_HYWG_cManager2_A:/dev>lsvg -l datavg | grep lvsm_data
lvsm_data1 raw 240 240 1 open/syncd N/A
lvsm_data2 raw 240 240 1 open/syncd N/A
lvsm_data3 raw 200 200 1 open/syncd N/A
那么接下来就确认datavg是否还有剩余可用空间：
GD_HYWG_cManager2_A:/dev>lspv
hdisk0 0001e6b91e911b61 rootvg active
hdisk1 0001f369e182ea0e rootvg active
hdisk2 00062d67aec5d1eb datavg active
hdisk3 00062d67aec5d3bb datavg active
hdisk4 0001e6b99995a385 billingbakvg active
hdisk5 0001e6b99995b755 billingarchvg active
hdisk6 0001e6b9020606ed billingvg active
GD_HYWG_cManager2_A:/dev>lsvg datavg
VOLUME GROUP: datavg VG IDENTIFIER: 00062d670000d6000000011aaec5d738
VG STATE: active PP SIZE: 128 megabyte(s)
VG PERMISSION: read/write TOTAL PPs: 7806 (999168 megabytes)
MAX LVs: 256 FREE PPs: 740 (94720 megabytes)
LVs: 45 USED PPs: 7066 (904448 megabytes)
OPEN LVs: 41 QUORUM: 2 (Enabled)
TOTAL PVs: 2 VG DESCRIPTORS: 3
STALE PVs: 0 STALE PPs: 0
ACTIVE PVs: 2 AUTO ON: no
MAX PPs per VG: 32768 MAX PVs: 1024
LTG size (Dynamic): 1024 kilobyte(s) AUTO SYNC: no
HOT SPARE: no BB POLICY: relocatable
由上面可知，datavg还有740个FREE PPs，于是我们可以从这个VG中划分出几个LV，用于扩大数据库的表空间。
