오라클 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을 하도록 한다