Temporary tablespace drop 관련 체크

Temporary tablespace drop  관련 체크

테스트 환경 : 8i

 

 # Temp Tablespace 생성
SQL> create temporary tablespace temp tempfile '/backup_dw/ORACLE/CRAFT/CRAFTtemp' size 50m
     extent management local uniform size 1m ;
Tablespace created.

 

# Temp Tablespace 삭제

SQL> drop tablespace temp ;
Tablespace dropped.
 

# 삭제된 Temp 를 temporary tablespace 지정한 유저 체크
SQL> select USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users
     where username ='SCOTT';

USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
SCOTT                          USERS                          TEMP

 

# 체크한 유저로 temp tablespace 사용되는 작업 실시  1 - rebuild

SQL> alter index XPK_CHECK_REBUILD rebuild tablespace tools ;
alter index XPK_CHECK_REBUILD rebuild tablespace tools
*
ERROR at line 1:
ORA-00959: tablespace 'TEMP' does not exist

 

# 체크한 유저로 temp tablespace 사용되는 작업 실시  2 - rebuild online

SQL> alter index XPK_CHECK_REBUILD rebuild online  tablespace tools ;
alter index XPK_CHECK_REBUILD rebuild online  tablespace tools
*
ERROR at line 1:
ORA-00959: tablespace 'TEMP' does not exist

 

# 체크한 유저로 temp tablespace 사용되는 작업 실시  3 - order by

SQL> select a1 from CHECK_REBUILD order by a1 ;
select a1 from CHECK_REBUILD order by a1
               *
ERROR at line 1:
ORA-00959: tablespace 'TEMP' does not exist

 

!! rebuild / rebuild online 에 상관없이 해당 유저의 temporary tablespace 로
   지정된 temp tablespace 가 삭제되어 에러를 return 한다.

 

## temporary tablespace 를 원복 하였음 추가로 temp1 이라는 temporary tablespace 도
    추가 하고, scott 의 temporary tablespace 를 temp1 으로 지정, temp tablespace 삭제
    다시 temp 를 사용하는 sql 문장 실행 해보기

# Temp tablespace 생성 하기
SQL> create temporary tablespace temp tempfile '/backup_dw/ORACLE/CRAFT/CRAFTtemp' size 50m
     extent management local uniform size 1m ;
Tablespace created.

# Temp1 tablespace 생성 하기
SQL> create temporary tablespace temp1 tempfile '/backup_dw/ORACLE/CRAFT/CRAFTtemp1'  size 100m
    extent management local uniform size 1m ;

Tablespace created.

 

# 유저의 temporary tablespace 변경 하기

SQL> alter user scott temporary tablespace temp1 ;
User altered.

 

# 유저의 temporary tablespace 확인
SQL> select USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users
  2  where username ='SCOTT';
USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
SCOTT                          USERS                          TEMP1

 

# 해당 유저로 temp tablespace 사용되는 작업 실시  1 - rebuild

SQL> alter index XPK_CHECK_REBUILD rebuild tablespace tools ;
Index altered.

 

# 해당 유저로 temp tablespace 사용되는 작업 실시  2 - order by

SQL> select a1 from CHECK_REBUILD order by a1 ;
정상적으로 나옴 ㅡㅡ+

 

# temporary tablespace drop

SQL> drop tablespace temp1 ;
==> Hang 발생
Tablespace dropped.

 

# order by 를 실행한 세션에서 commit 이후에야 "drop tablespace temp1" 이 완료 됨
SQL> commit ;
Commit complete.
==> Test 진행한 세션이 commit 후에야 temporary tablespace drop 이 되었음

INCLUDING CONTENTS

# temp 를 사용하는 sql 작업 실행
SQL> select a1 from check_Rebuild order by a1 ;

# temp 사용 현황 체크

SQL> !more check_temp.sql

rem
rem SQL*Plus script to display sort usage by user with join to v$session
rem to get session information (and correct user in some ver due to bug)
rem
rem  20010130  Mark D Powell   Saved version
rem  20020227  Mark D Powell   Add segtype to show temp tbl, hash use vs sort
rem
column tablespace format a12
column username   format a12

break on username nodup skip 1

select   se.username
        ,se.sid
        ,su.extents
        ,su.blocks * to_number(rtrim(p.value)) as Space
        ,tablespace
        ,segtype
from     v$sort_usage su
        ,v$parameter  p
        ,v$session    se
where    p.name          = 'db_block_size'
and      su.session_addr = se.saddr
order by se.username, se.sid
/

# 사용중임이 체크 됨

SQL> @check_temp.sql

USERNAME            SID    EXTENTS      SPACE TABLESPACE   SEGTYPE
------------ ---------- ---------- ---------- ------------ ---------
SCOTT                26         24   25165824 TEMP         SORT

 

# 사용중일때 drop tablespae 시도

SQL> drop tablespace temp ;
===> Hang 발생
===> CTRL + C
SQL> drop tablespace temp ;
^Cdrop tablespace temp
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

다시 drop tablespace 이번엔 including contents 로

# 사용중일때 drop tablespae .. including contents 로  시도

SQL> drop tablespace temp including contents ;
^Cdrop tablespace temp including contents
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

!! Drop tablespace 로 tempoaray tablespace 삭제 시
   drop tablespace temp / drop tablespace temp including contents 를 하여도
   현재 temporary tablespace 를 사용 중이라면 drop 되지 않는다.
   [ including content 를 사용하더라도 ]
   결국 사전에 temporary tablespace 를 추가하고, 유저별로 temporary tablespace
   변경 후 일정 시간 경과후 drop tablespace 가 가능할것으로 보여진다.
  

 

Temporary tablespace 에 Datafile 추가 예제

 add tempfile 이다. [ add datafile 이 ]

  alter tablespace TEMP
  add tempfile '/u02b/ORACLE/ARTDOM/ARTDOM/temp02.dbf' size 10M
  autoextend on next 10M maxsize 100M ;

Temporary tablespace 에 Datafile 삭제 예제

alter database tempfile '/u02b/ORACLE/ARTDOM/ARTDOM/temp03.dbf' drop ;

==> 사용 중이라면 오류가 발생한다.

 

[출처] Temporary tablespace drop 관련 체크|작성자 타락천사