Aix系统下增加Oracle temp 表空间（裸设备方式）

来源：互联网发布：华泰软件工程有限公司编辑：程序博客网时间：2024/06/04 05:02

查询临时表空间使用情况：

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表空间使用情况

Aix系统下增加Oracle temp 表空间 （裸设备方式）

Aix系统下增加Oracle temp 表空间（裸设备方式）