ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

에러 : ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

delete 작업을 하던 중 ORA-30036 에러 발생

전체 row수가 8,000,000인 db에서 rownum이 4,000,000 이상이면 삭제하는 쿼리를 수행

1 2 3 4

SQL> DELETE HWMTEST1 WHERE ROWNUM<=4000000        * ERROR at line 1: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

해결 방법 : undo tablespace를 확장, 새undo 생성 또는 쿼리문의 로직을 작은 단위로 잘라서 실행

1. undo tablespace에 datafile 을 추가하여 확장시켜줌

1	SQL> alter tablespace UNDOTBS1 add datafile '/oracle/app/oracle/oradata/undotbs02.DBF' size 500m;

2. 새로운 undo tablespace 생성

SQL> create undo tablespace undotbs2 datafile '/oracle/app/oracle/oradata/undotbs03.dbf' size 500M;

새로 추가한 undo tablespace 를 default undo tablespace로 지정

SQL> alter system set undo_tablespace=undotbs2;

기존 undo tablespace 쓰지 않을예정이라면 삭제

SQL> drop tablespace undotbs1;

3. 쿼리문의 로직을 작은 단위로 잘라서 실행

아래와 같이  로직을 나눠서 실행(undo를 적게 쓰기 위함)

rownum이 4,000,000 이상이면 삭제하는 쿼리를

rownum이 7,000,000 이상이면 삭제하는 쿼리,rownum이 6,000,000 이상이면 삭제하는 쿼리,

rownum이 5,000,000 이상이면 삭제하는 쿼리,rownum이 4,000,000 이상이면 삭제하는 쿼리로 나누어서 실행함 

(1,000,000 건씩 나누어 삭제하게끔 실행)

SQL> DELETE HWMTEST1 WHERE ROWNUM<=7000000;
commit;
SQL> DELETE HWMTEST1 WHERE ROWNUM<=6000000;
commit;
SQL> DELETE HWMTEST1 WHERE ROWNUM<=5000000;
commit;
SQL> DELETE HWMTEST1 WHERE ROWNUM<=4000000;
commit;

원인 : dml을 통해 한번에 실행되는 데이터 양이 많아서 undo tablespace가 모자라서 발생하는 것

참조 :