当前位置:数据库 > Oracle >>

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表空间使用情况
 
Oracle
MySQL
Access
SQLServer
DB2
Excel
SQLite
SYBASE
Postgres
如果你遇到数据库难题:
请访问www.zzzyk.com 试试
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,