UNDO TABLESPACE 교체 방법

이번에 대략 6000천만건 데이터를 입력하다가 어느순간 데이터가 입력되지 않았다.
에러 로그를 보니 UNDO 테이블 스페이스가 꽉찼다고 한다.

롤백 데이터들이 계속 쌓인거란다.

UNDO테이블을 비워주는 명령어가 있으면 좋지만,
안타깝게도 현재는 그런게 없으니

1. 대체할 UNDO 테이블 스페이스를 하나 만들어서
2. 현재 사용중인 UNDO 테이블 스페이스와 교체하고
3. 기존에 사용중이던 것은 삭제한다.

--1.새로 만들고
create undo tablespace undotbs2 datafile 'C:\oraclexe\app\oracle\oradata\XE\undotbs02.dbf' size 5000M reuse autoextend on next 5M MAXSIZE UNLIMITED;

--2. 교체하고
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;

--3. 삭제한다.
DROP TABLESPACE UNDOTBS1;

테이블 스페이스 확인하는건 여기를 참고한다.

오라클을 spfile로 관리하지 않고 pfile로 한다면 pfile도 손을 봐야하는데
그건 검색해보자;;;;;;(구찬 ㅋㅋ)

참고 사이트 : http://jhroom.co.kr/21658

테이블 스페이스 사용현황 확인

SELECT
        DISTINCT D.FILE_ID FILE#
      , D.TABLESPACE_NAME TS_NAME
      , D.BYTES /1024 / 1024 MB
      , D.BYTES / 8192 TOTAL_BLOCKS
      , SUM(E.BLOCKS) USED_BLOCKS
      , TO_CHAR( NVL( ROUND( SUM(E.BLOCKS)/(D.BYTES/8192), 4),0) *100,'09.99') || ' %' PCT_USED
FROM    DBA_EXTENTS E
      , DBA_DATA_FILES D
WHERE   1 = 1
AND     D.FILE_ID = E.FILE_ID(+)
GROUP BY D.FILE_ID , D.TABLESPACE_NAME , D.BYTES
ORDER BY 1,2 ;

SELECT * FROM dba_data_files;
SELECT * FROM dba_tablespaces;

쿼리작성 : 칼찬요정님(http://blog.naver.com/osunzip)

유저생성 / 테이블 스페이스 생성 쿼리

--유저 삭제
DROP USER dev CASCADE;

--테이블 스페이스 삭제
drop tablespace ORACLE
including contents and datafiles
cascade constraints;

-- 테이블스페이스 정보 조회
SELECT * FROM dba_data_files;
SELECT * FROM dba_tablespaces;

CREATE TABLESPACE MY_DATA DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\MY_DATA01.dbf' SIZE 3G AUTOEXTEND ON;
ALTER TABLESPACE MY_DATA ADD DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\MY_DATA02.dbf' SIZE 3G AUTOEXTEND ON;

ALTER DATABASE DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\MY_DATA01.dbf' RESIZE 5G;
ALTER DATABASE DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\MY_DATA02.dbf' RESIZE 5G;

CREATE USER dev IDENTIFIED BY tester
DEFAULT TABLESPACE MY_DATA
PROFILE DEFAULT
QUOTA UNLIMITED ON MY_DATA;

GRANT connect, RESOURCE, DBA TO dev;    --> 모든 권한 주기

GRANT CREATE SESSION TO             dev;        --> 데이터베이스에 접근할 수 있는 권한
GRANT CREATE DATABASE LINK TO       dev;
GRANT CREATE MATERIALIZED VIEW TO   dev;
GRANT CREATE PROCEDURE TO           dev;
GRANT CREATE PUBLIC SYNONYM TO      dev;
GRANT CREATE ROLE TO                dev;
GRANT CREATE SEQUENCE TO            dev;
GRANT CREATE SYNONYM TO             dev;
GRANT CREATE TABLE TO               dev;        --> 테이블을 생성할 수 있는 권한
GRANT DROP ANY TABLE TO             dev;        --> 테이블을 제거할 수 있는 권한
GRANT CREATE TRIGGER TO             dev;
GRANT CREATE TYPE TO                dev;
GRANT CREATE VIEW TO                dev;

쿼리작성 : 칼찬요정님(http://blog.naver.com/osunzip)