오라클 UNDO 재생성 / UNDO 다른위치로 이동
UNDO 재생성 / UNDO 다른위치로 이동
undo 재생성은 빈번하게 있는 일이다
언두사이즈가 급증 하는일이 있는데 대량의 DML과 더불어 ORA-01555 Snapshop too old 방지를 위해 나온 undo_retention 때문에 라고 간략하게 설명하고 넘어간다..
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
조회를 해보면 UNDOTBS2가 보인다
TABLESPACE_NAME FILE_NAME SIZE(MB) USED(MB) free SIZE(MB) free % EXTENC
--------------- ------------------------------- -------- ---------- ------------- ---------- ------
SYSAUX C:\ORADATA\ORA11\SYSAUX01.DBF 600 340.9375 259.0625 43.17 YES
SYSTEM C:\ORADATA\ORA11\SYSTEM01.DBF 700 280.625 419.375 59.91 YES
UNDOTBS2 C:\ORADATA\ORA11\UNDOTBS02.DBF 41.8125 24 17.8125 42.60 YES
USERS C:\ORADATA\ORA11\USERS01.DBF 5 1.0625 3.9375 78.75 YES
create undo tablespace UNDOTBS1 datafile 'c:\APP\UNDOTBS01.DBF' size 10M autoextend on;
alter system set undo_tablespace='UNDOTBS1';
drop tablespace undotbs2 including contents and datafiles;
......
......
여기서 drop이 안되는 분들이 분명히 10명중 3명은 있을것이다..
그거슨...
누군가가 DML을 하고 commit이나 rollback을 안해서 그렇다
SQL> select segment_name, tablespace_name, status from dba_rollback_segs where status='ONLINE' order by 2;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------------------------------------ ------------------------------------------------------------ ------------------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 ONLINE
_SYSSMU10$ UNDOTBS1 ONLINE
_SYSSMU9$ UNDOTBS1 ONLINE
_SYSSMU2$ UNDOTBS1 ONLINE
_SYSSMU3$ UNDOTBS1 ONLINE
_SYSSMU4$ UNDOTBS1 ONLINE
_SYSSMU5$ UNDOTBS1 ONLINE
_SYSSMU6$ UNDOTBS1 ONLINE
_SYSSMU7$ UNDOTBS1 ONLINE
_SYSSMU8$ UNDOTBS1 ONLINE
_SYSSMU20$ UNDOTBS2 ONLINE
12 개의 행이 선택되었습니다.
SQL> drop tablespace undotbs2 including contents and datafiles;
drop tablespace undotbs2 including contents and datafiles
*
1행에 오류:
ORA-30013: 실행 취소 테이블스페이스 'UNDOTBS2'은(는) 현재 사용 중임
언두를 undotbs1로 바꾸고 undotbs2를 drop하려 했으나 _SYSSMU20$ 요놈이 online이라 사용중으로 처리가 되어 drop이 안됨
그러면 어디서 물고있는지 확인부터 하자
set line 200
col sid_serial for a10
col orauser for a10
col program for a20
col osuser for a20
col machine for a20
col terminal for a10
col tablespace_name for a10
col undoseg for a10
col undo for a10
col start_time for a20
SELECT TO_CHAR (s.sid) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser,
s.program, s.osuser, s.machine, s.terminal,RS.TABLESPACE_NAME,
r.name undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) /1024 || 'K' "Undo",t.start_time
FROM sys.v_$rollname r,
sys.v_$session s,
sys.v_$transaction t,
sys.v_$parameter x,
sys.dba_rollback_segs rs
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size'
and rs.segment_name = r.name;
-- and r.name = '원하는 세그먼트 이름'
-- and ts.tablespace_name='언두이름'
;
SID_SERIAL ORAUSER PROGRAM MACHINE TERMINAL TABLESPACE UNDOSEG Undo START_TIME
---------- ---------- -------------------- -------------------- ---------- ---------- ---------- ----- --------------------
138,426 SYSTEM Toad.exe WORKGROUP\SEANS SEANS UNDOTBS2 _SYSSMU20$ 8K 08/27/14 08:20:27
흠 toad를 이용해서 system유저로 접속한 WORKGROUP\SEANS머신을 이용하는 유저분이 commit을 안해주셨다
alter system kill session '138,426';
로 세션을 죽여주고 다시 drop을 하도록 한다