1.首先查询表空间利用情况
select t.t_name, t.t_size||'M' total_size, t.t_size-f.t_size used_size, (1-f.t_size/t.t_size) * 100 usage from
(select tablespace_name t_name, sum(blocks) * 8 / 1024 t_size from dba_data_files group by tablespace_name) t,
(select tablespace_name t_name, sum(blocks) * 8 / 1024 t_size from dba_free_space group by tablespace_name) f
where t.t_name = f.t_name and 1-f.t_size/t.t_size >=0.9;
2.明确需要添加数据文件的表空间,并查询该表空间已添加的数据文件情况,以TS_TEST表空间为例
select file_name from dba_data_files where tablespace_name='TS_TEST';
/dev/rHIS_10240M_013
。。。此处省略。。。。
/dev/rHIS_10240M_235
查询目前现有数据卷组情况,其中oradatavg1是oracle数据库数据文件专用卷组。
[root@ha1]#lsvg
rootvg
oradatavg1
arcvg1
backup_vg
查看可用逻辑卷,标示open/syncd字样的是在使用状态的卷;closed/syncd字样的是未使用的卷,可以被分配使用。
[root@ha1]#ls -l /dev/*HIS_10240M_243
brw-rw---- 1 oracle dba 81,389 Oct 01 2014 /dev/HIS_10240M_243
crw-rw---- 1 oracle dba 81,389 Oct 01 2014 /dev/rHIS_10240M_243
以r打头的文件,才是裸设备文件。此处需注意数据文件的属主及属组必须为oracle dba
[root@ha1]#lslv HIS_10240M_243
LOGICAL VOLUME: HIS_10240M_243 VOLUME GROUP: oradatavg1
LV IDENTIFIER: 00cf64a500004c0000000148cb140d3f.389 PERMISSION: read/write
VG STATE: active/complete LV STATE: closed/syncd
TYPE: raw WRITE VERIFY: off
MAX LPs: 512 PP SIZE: 128 megabyte(s)
COPIES: 1 SCHED POLICY: striped
LPs: 80 PPs: 80
STALE PPs: 0 BB POLICY: relocatable
INTER-POLICY: maximum RELOCATABLE: no
INTRA-POLICY: edge UPPER BOUND: 4
MOUNT POINT: N/A LABEL: None
MIRROR WRITE CONSISTENCY: off
EACH LP COPY ON A SEPARATE PV ?: yes (superstrict)
Serialize IO ?: NO
STRIPE WIDTH: 4
STRIPE SIZE: 1m
DEVICESUBTYPE : DS_LVZ
[root@ha2]#ls -l /dev/*HIS_10240M_243
brw-rw---- 1 oracle dba 81,389 Oct 01 2014 /dev/HIS_10240M_243
crw-rw---- 1 oracle dba 81,389 Oct 01 2014 /dev/rHIS_10240M_243
[root@ha2]#lslv HIS_10240M_243
LOGICAL VOLUME: HIS_10240M_243 VOLUME GROUP: oradatavg1
LV IDENTIFIER: 00cf64a500004c0000000148cb140d3f.389 PERMISSION: read/write
VG STATE: active/complete LV STATE: closed/syncd
TYPE: raw WRITE VERIFY: off
MAX LPs: 512 PP SIZE: 128 megabyte(s)
COPIES: 1 SCHED POLICY: striped
LPs: 80 PPs: 80
STALE PPs: 0 BB POLICY: relocatable
INTER-POLICY: maximum RELOCATABLE: no
INTRA-POLICY: edge UPPER BOUND: 4
MOUNT POINT: N/A LABEL: None
MIRROR WRITE CONSISTENCY: off
EACH LP COPY ON A SEPARATE PV ?: yes (superstrict)
Serialize IO ?: NO
STRIPE WIDTH: 4
STRIPE SIZE: 1m
DEVICESUBTYPE : DS_LVZ
3.登陆数据库,将裸设备文件添加到表空间作为数据文件。单节点执行即可。
ALTER TABLESPACE "TS_TEST" ADD DATAFILE '/dev/rHIS_10240M_243' size 10239M autoextend off;
此处需注意,并能将裸设备文件空间100%分配出去。