Aix系统下增加Oracle temp表空间(裸设备方式)
Aix系统下增加Oracle temp表空间(裸设备方式)
查询临时表空间使用情况:
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS, USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)", NVL(FREE_SPACE,0) "FREE_SPACE(M)" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE, ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);
经查询得知:temp表空间使用率99%
进入操作系统(AIX)
#lsvg
rootvg
xyyvg
查看vg使用情况
#lsvg -l xyyvg lv_xyy151_12g raw 48 48 12 open/syncd N/A lv_xyy152_12g raw 48 48 12 open/syncd N/A lv_xyy153_12g raw 48 48 12 open/syncd N/A lv_xyy154_12g raw 48 48 12 open/syncd N/A lv_xyy155_12g raw 48 48 12 open/syncd N/A lv_xyy156_12g raw 48 48 12 open/syncd N/A lv_xyy157_12g raw 48 48 12 open/syncd N/A lv_xyy158_12g raw 48 48 12 open/syncd N/A lv_xyy159_12g raw 48 48 12 open/syncd N/A lv_xyy160_12g raw 48 48 12 open/syncd N/A lv_xyy161_12g raw 48 48 12 closed/syncd N/A (此lv以下没有使用) lv_xyy162_12g raw 48 48 12 closed/syncd N/A lv_xyy163_12g raw 48 48 12 closed/syncd N/A lv_xyy164_12g raw 48 48 12 closed/syncd N/A lv_xyy165_12g raw 48 48 12 closed/syncd N/A lv_xyy166_12g raw 48 48 12 closed/syncd N/A lv_xyy167_12g raw 48 48 12 closed/syncd N/A lv_xyy168_12g raw 48 48 12 closed/syncd N/A lv_xyy169_12g raw 48 48 12 closed/syncd N/A lv_xyy170_12g raw 48 48 12 closed/syncd N/A lv_xyy171_12g raw 48 48 12 closed/syncd N/A lv_xyy172_12g raw 48 48 12 closed/syncd N/A lv_xyy173_12g raw 48 48 12 closed/syncd N/A lv_xyy174_12g raw 48 48 12 closed/syncd N/A lv_xyy175_12g raw 48 48 12 closed/syncd N/A lv_xyy176_12g raw 48 48 12 closed/syncd N/A lv_xyy177_12g raw 48 48 12 closed/syncd N/A lv_xyy178_12g raw 48 48 12 closed/syncd N/A lv_xyy179_12g raw 48 48 12 closed/syncd N/A
查看lv信息,与用户沟通,增加5个lv,每个lv留2M不创建
# lslv lv_xyy161_12g (查看lv_xyy161_12g为例子) LOGICAL VOLUME: lv_xyy161_12g VOLUME GROUP: xyyvg LV IDENTIFIER: 00c82ca000004c0000000127838b7269.203 PERMISSION: read/write VG STATE: active/complete LV STATE: closed/syncd TYPE: raw WRITE VERIFY: off MAX LPs: 512 PP SIZE: 256 megabyte(s) COPIES: 1 SCHED POLICY: striped LPs: 48 PPs: 48 STALE PPs: 0 BB POLICY: relocatable INTER-POLICY: maximum RELOCATABLE: no INTRA-POLICY: middle UPPER BOUND: 12 MOUNT POINT: N/A LABEL: None MIRROR WRITE CONSISTENCY: on/ACTIVE EACH LP COPY ON A SEPARATE PV ?: yes (superstrict) Serialize IO ?: NO STRIPE WIDTH: 12 STRIPE SIZE: 1m DEVICESUBTYPE : DS_LVZ lslv lv_xyy162_12g lslv lv_xyy163_12g lslv lv_xyy164_12g lslv lv_xyy165_12g 增加temp表空间temp_file alter tablespace temp add tempfile '/dev/rlv_xyy161_12g' size 12286m autoextend off; alter tablespace temp add tempfile '/dev/rlv_xyy162_12g' size 12286m autoextend off; alter tablespace temp add tempfile '/dev/rlv_xyy163_12g' size 12286m autoextend off; alter tablespace temp add tempfile '/dev/rlv_xyy164_12g' size 12286m autoextend off; alter tablespace temp add tempfile '/dev/rlv_xyy165_12g' size 12286m autoextend off;
增加完毕后,查看temp表空间使用情况