Grizzly`s Cave: TABLE의 수정 II

2009년 5월 29일 금요일

TABLE의 수정 II

COLUMN의 수정

ALTER TABLE TBL_DEPT
ADD CONSTRAINT TBL_DEPT_ZIP_NN ZIPCODE VARCHAR2(15) NOT NULL;

위의 쿼리문은 TABLE에 대한 CONSTRAINT를 추가하는 일이다. 기본적으로 NOT NULL만 생성해도 기본적인 NOT NULL CONSTRAINT은 생성되나 이는 NUMBERING에 의해 자동 설정되기 때문에, 사용자가 구분하기 힘들기 때문에 특정 규칙에 의해 명시 하는 것이 편리하다.

INSERT INTO TBL_DEPT VALUES (10, '관리부','서울','150-123');
INSERT INTO TBL_DEPT VALUES (20, '생산부','인천','230-098');
INSERT INTO TBL_DEPT VALUES (30, '인사부','서울','151-345');
INSERT INTO TBL_DEPT VALUES (40, '개발부','대전','345-123');

SELECT * FROM TBL_DEPT;

ALTER TABLE TBL_DEPT
ADD TEL VARCHAR2 (15) DEFAULT ' '
CONSTRAINT TBL_DEPT_TEL_NN NOT NULL;
-- DEFAULT 설정으로 기존 데이터가 있을때 추가가 가능하도록 함!
SELECT * FROM TBL_DEPT;

/* 대용량 테이블이고 오라클 클라이언트가 오라클 서버에 아주 많이 접속되어 있을 때에 특정 컬럼을 제거하자

제거하고자 하는 특정 컬럼을 오라클 데이터 사전에서 제거를 시키자 대용량 서버에서 클라이언트가 많이 접속되어 있다면 성능이 떨어진다 .
오라클 서버에 접속되어진 클라이언트 수가 현격히 떨어지는 새벽이나 야간시간대에 실제 해당 컬럼의 데이터를 제거하자.
*/
ALTER TABLE TBL_DEPT
SET UNUSED(ZIPCODE);

ALTER TABLE TBL_DEPT
SET UNUSED COLUMN ZIPCODE;

SELECT * FROM TBL_DEPT;

SELECT *
FROM USER_UNUSED_COL_TABS;
--USUSED된 컬럼의 갯수를 알려준다.

ALTER TABLE TBL_DEPT
DROP UNUSED COLUMNS;

SELECT *
FROM USER_UNUSED_COL_TABS;
--F5

-- 컬럼 추가시 위치 변경!

SELECT *
FROM TBL_DEPT

ALTER TABLE TBL_DEPT
DROP COLUMN TEL;

ALTER TABLE TBL_DEPT
ADD TEL VARCHAR2(15) ;
-- 기본적으로 맨 뒤에 들어감
--F8

ALTER TABLE TBL_DEPT
MODIFY LOC DEFAULT '서울';

CREATE INDEX IDX_TBL_DEPT_DNAME
ON TBL_DEPT(DNAME)
TABLESPACE PROJECTTBS;

--1 변경하고자 하는 컬럼의 위치대로 쿼리 작성
SELECT DEPTNO, DNAME, TEL,LOC
FROM TBL_DEPT;

-- F9

--2. 위를 토대로 새 테이블 생성
CREATE TABLE TBL_DEPT_COPY
AS
SELECT DEPTNO, DNAME, TEL,LOC
FROM TBL_DEPT;

SELECT * FROM TBL_DEPT_COPY;
--F10

--3. 원본의 제약, 인덱스, DEFAULT는 복사가 안됨
단, NOT NULL 제약은 제약조건에 이름이 SYS_####로
복사가 되어짐. 그러므로 원본의 나머지 제약, 인덱스, 디폴트를 추가한다.

SELECT B.COLUMN_NAME, A.CONSTRAINT_TYPE,A.CONSTRAINT_NAME,A.SEARCH_CONDITION,A.STATUS
FROM USER_CONSTRAINTS A JOIN USER_CONS_COLUMNS B
ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE A.TABLE_NAME = 'TBL_DEPT';

SELECT *
FROM USER_INDEXES
WHERE TABLE_NAME = 'TBL_DEPT';

SELECT *
FROM USER_INDEXES
WHERE TABLE_NAME = 'TBL_DEPT_COPY';
--F12

SELECT COLUMN_NAME, DATA_DEFAULT
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'TBL_DEPT';

SELECT COLUMN_NAME, DATA_DEFAULT
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'TBL_DEPT_COPY';
-F13

SELECT *
FROM USER_IND_COLUMNS
WHERE TABLE_NAME = 'TBL_DEPT';

SELECT *
FROM USER_IND_COLUMNS
WHERE TABLE_NAME = 'TBL_DEPT_COPY';

--F14

4. 원본에 조재하던 제약,인덱,디폴을 복사된 테이블에 추가~!
추후 이름이 변경될 것을 감안하여 기존의 테이블에서 가져올 정보를 조회 후 원본을 날려야 한다

DROP TABLE TBL_DEPT;

RENAME TBL_DEPT_COPY TO TBL_DEPT;

-- PK 설정
ALTER TABLE TBL_DEPT
ADD CONSTRAINT TBL_DEPT_DEPTNO_PK
PRIMARY KEY(DEPTNO);

제약조건 이름 변경
SELECT B.COLUMN_NAME, A.CONSTRAINT_TYPE,A.CONSTRAINT_NAME,A.SEARCH_CONDITION,A.STATUS
FROM USER_CONSTRAINTS A JOIN USER_CONS_COLUMNS B
ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE A.TABLE_NAME = 'TBL_DEPT';
-- F15