频道栏目
首页 > 数据库 > Oracle > 正文
Aix系统下增加Oracle temp表空间(裸设备方式)
2013-09-09 11:37:50           
收藏   我要投稿

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程序全局区(PGA)
下一篇:Why does DBA_TAB_MODIFICATIONS sometimes have no values [ID 762738.1]
相关文章
图文推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站