Add Oracle temp tablespace In the Aix System (bare device Mode)

Add the Oracle temp tablespace (raw device mode) in the Aix system to query the usage of the temporary tablespace:

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) BLOCKSFROM DBA_TEMP_FILESGROUP BY TABLESPACE_NAME) D,(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACEFROM V$TEMP_SPACE_HEADERGROUP BY TABLESPACE_NAME) FWHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

 

It is found that the temp tablespace usage rate is 99% and enters the Operating System (AIX) # lsvgrootvgxyyvg to check the vg usage.

# Lsvg-l sans raw 48 12 open/syncd N/Alv_xyy152_12g raw 48 12 open/syncd N/Alv_xyy153_12g raw 48 12 12 12 open/syncd N/Alv_xyy154_12g raw 48 12 open raw /syncd N/Alv_xyy155_12g raw 48 12 open/syncd N/Alv_xyy156_12g raw 48 48 12 open/syncd N/Alv_xyy157_12g raw 48 48 12 open/syncd N/426raw 48 12 open /syncd N/Alv_xyy159_12g raw 48 12 open/syncd N/Alv_xyy160_12g raw 48 48 12 open/syncd N/alv_xyy1621312g raw 48 12 12 closed/syncd N/A (No use) export raw 48 12 closed/syncd N/Alv_xyy163_12g raw 48 12 closed/syncd N/Alv_xyy164_12g raw 48 48 12 closed/syncd N/export raw 48 12 closed/syncd N/ export raw 48 12 closed/syncd N/Alv_xyy167_12g raw 48 12 closed/syncd N/export raw 48 12 closed/syncd N/Alv_xyy169_12g raw 48 12 12 closed/syncd N/ alv_xyy170_12g raw 48 12 closed/syncd N/alv_xyy1721312g raw 48 12 closed/syncd N/Alv_xyy172_12g raw 48 48 12 closed/syncd N/audio raw 48 12 closed/syncd N/ export raw 48 12 closed/syncd N/Alv_xyy175_12g raw 48 12 closed/syncd N/Alv_xyy176_12g raw 48 48 12 closed/syncd N/export raw 48 12 closed/syncd N/ alv_xyy178_12g raw 48 12 closed/syncd N/Alv_xyy179_12g raw 48 12 closed/syncd N/

 

View lv information and communicate with users. Add 5 lv numbers, and leave 2 MB for each lv.

# Lslv lv_xyy16000012g (see lv_xyy16000012g as an example) logical volume: lv_xyy16000012g volume group: xyyvgLV IDENTIFIER: 00c82ca000004c0000000127838b00009. 203 PERMISSION: read/writeVG STATE: active/complete lv state: closed/syncdTYPE: raw write verify: offMAX LPs: 512 pp size: 256 megabyte (s) COPIES: 1 sched policy: stripedLPs: 48 PPs: 48 STALE PPs: 0 bb policy: relocatableINTER-POLICY: maximum RELOCATABLE: noINTRA-P OLICY: middle upper bound: 12 mount point: N/a label: NoneMIRROR write consistency: on/active each lp copy on a separate pv?: Yes (superstrict) Serialize IO?: No stripe width: 12 stripe size: 1 m DEVICESUBTYPE: DS_LVZlslv limit increase temp tablespace temp_filealter tablespace temp add tempfile '/dev/rlv_xyy161_12g 'size 12286 m autoextend off; alter tablespace temp add tempfile '/dev/rlv_xyy162_12g' size 12286 m autoextend off; alter tablespace temp add tempfile '/dev/rlv_xyy163_12g' size 12286 m autoextend off; alter tablespace temp add tempfile '/dev/rlv_xyy164_12g' size 12286 m autoextend off; alter tablespace temp add tempfile '/dev/rlv_xyy165_12g' size 12286 m autoextend off;

 

After adding the table space, check the temp table space usage.