예전에 정리 한글입니다.
참고하세요
업무상 테이블을 drop column 하면 시간이 너무 오래 걸리구요..
ddl 이지만 undo 를 사용해요 그래서 마킹만 하고
널널한 시간에 실제로 정리를 합니다.

참고 문서 :
Online Help "Alter Table" , 메타링크 < 358269.1>

□ ALTER TABLE .. DROP COLUMN
더이상 사용하지 않는 칼럼을 Drop 하여 Free Space 를 확보 하거나, ( DROP )
향후 실제적인 Drop 을 할 칼럼에 대해서 Marking 을 할 수 있다. ( SET UNUSED COLUMN )
- LOB 칼럼을 삭제 하면, LOB Data 및 LOB Index 가 삭제 됩니다.
- BFILE 칼럼을 삭제 하면, Locatoer 만 삭제 되고, 실제 화일은 삭제 되지 않습니다.

● SET UNUSED Clause
==> 단순히 해당 칼럼을 더 이상 사용하지 않는다고 Marking 만 하는것
지정된 칼럼을 실제로 삭제 되지는 않는다.
지정된 칼럼이 사용한 공간을 환원되지 않는다.
따라서, 가장 빠르게 Drop Column 을 수행할 수 있다.
USER_UNUSED_COL_TABS, DBA_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS 를 통해서
확인이 가능하다.
Unused Column은 Drop 된 것처럼 취급 된다.
칼럼이 Unused 로 marking 되면, Access 가 불가능합니다.
"SELECT * " 쿼리를 통해서 Unused Column 의 데이타를 Retrieve 받지 못한다.
DESC 테이블 을 통해서 Unused Column 을 볼수 없다.
Unused Column을 신규 칼럼으로 추가 할수 있다.
Unused Column Type Long 일 경우엔, Drop 후에나 Long 칼럼 추가가 가능하다.
( 테이블에 오직 하나의 Long 칼럼만 존재 할 수 있다.)

EX) ALTER TABLE <table_name> SET UNUSED COLUMN <col_name>
--> the set unused "flag" deletes the column from data dictionary.
It is effectively gone but the space is not reclaimed.
Data is not rewritten, so no undo/redo.
Column Drop 은 Redo 와 Undo 를 발생 시키고, 따라서 무지하게 수행 시간이 오래 걸린다.

● DROP Clause
==> 해당 칼럼을 실제로 Drop 하고 Free Space 를 환원한다.
칼럼이 Drop 될때,
- Drop 된 칼럼을 사용한 Index 는 모두 Drop 된다.
- Drop 된 칼럼을 사용한 Constriant 는 Remove 된다.
[ PK 칼럼이고, FK 칼럼이 있는 경우엔,
CASCADE CONSTRAINTS 를 사용하지 않으면, 에러가 발생한다.]

EX) ALTER TABLE <table_name> DROP COLUMN <col_name>
--> physically removes the column from the table.
It rewrites the entire table. Lots of undo/redo.

● DROP UNUSED COLUMNS CLAUSE
==> Unused 로 Marking 된 칼럼을 실제로 Drop 한다.
Marking 만 해 놓은 Unused 칼럼에 대해서 실제적인 Drop 작업
대상 테이블에 Unused Column 이 존재 하지 않아도, 에러가 발생하지 않는다.

EX) ALTER TABLE <table_name> DROP UNUSED COLUMNS
--> data will be rewritten. Lots of redo.

---------------------------------------------------------------------------------------------------------------------
-- 테스트 1 삭제 칼럼을 PK(INDEX) 로 생성 - DROP
DROP TABLE CHECK_DROP ;
CREATE TABLE CHECK_DROP ( A1 NUMBER, A2 VARCHAR2(100), A3 VARCHAR2(100)) ;

ALTER TABLE CHECK_DROP ADD CONSTRAINT XPK_CHECK_DROP PRIMARY KEY
( A1, A2 )
USING INDEX ;

INSERT INTO CHECK_DROP VALUES ( 1, 'A','B');
INSERT INTO CHECK_DROP VALUES ( 2, 'C','D');
INSERT INTO CHECK_DROP VALUES ( 3, 'E','F');
COMMIT ;
SELECT * FROM CHECK_DROP ;

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH,NULLABLE
FROM DBA_TAB_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';

-- 인덱스 관련 체크
SELECT * FROM DBA_INDEXES WHERE TABLE_NAME ='CHECK_DROP';
SELECT * FROM DBA_IND_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';

-- CONSTRAINTS 관련 체크
SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME ='CHECK_DROP';
SELECT * FROM DBA_CONS_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';

ALTER TABLE CHECK_DROP DROP COLUMN A3 ;
-- PK 로 사용 중으로 삭제가 되지 않는다.
ALTER TABLE CHECK_DROP DROP COLUMN A3 CASCADE CONSTRAINTS ;
-- CASCADE CONSTRAINTS 옵션을 사용하면 정상적으로 수행된다.

DESC CHECK_DROP ;

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH,NULLABLE
FROM DBA_TAB_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';

SELECT * FROM DBA_INDEXES WHERE TABLE_NAME ='CHECK_DROP';
SELECT * FROM DBA_IND_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';
-- PK INDEX 가 칼럼 삭제에 따라서 삭제 되었다.

SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME ='CHECK_DROP';
SELECT * FROM DBA_CONS_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';
-- PK CONSTRAINT 가 칼럼 삭제 에 따라서 삭제 되었다.

SELECT * FROM DBA_UNUSED_COL_TABS ;
-- 결과가 없다.

---------------------------------------------------------------------------------------------------------------------
-- 테스트 2 삭제 칼럼을 일반 INDEX 로 생성 - DROP
DROP TABLE CHECK_DROP ;
CREATE TABLE CHECK_DROP ( A1 NUMBER, A2 VARCHAR2(100), A3 VARCHAR2(100)) ;

CREATE INDEX XAK1_CHECK_DROP ON CHECK_DROP ( A1, A2, A3) ;

INSERT INTO CHECK_DROP VALUES ( 1, 'A','B');
INSERT INTO CHECK_DROP VALUES ( 2, 'C','D');
INSERT INTO CHECK_DROP VALUES ( 3, 'E','F');
COMMIT ;
SELECT * FROM CHECK_DROP ;

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH,NULLABLE
FROM DBA_TAB_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';

-- 인덱스 관련 체크
SELECT * FROM DBA_INDEXES WHERE TABLE_NAME ='CHECK_DROP';
SELECT * FROM DBA_IND_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';

-- CONSTRAINTS 관련 체크
SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME ='CHECK_DROP';
SELECT * FROM DBA_CONS_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';
-- 해당 사항 없음

ALTER TABLE CHECK_DROP DROP COLUMN A3 ;
-- 인덱스로만 사용시에는 쉽게 삭제 된다.

DESC CHECK_DROP ;

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH,NULLABLE
FROM DBA_TAB_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';

SELECT * FROM DBA_INDEXES WHERE TABLE_NAME ='CHECK_DROP';
SELECT * FROM DBA_IND_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';
-- INDEX가 칼럼 삭제에 따라서 삭제 되었다.

SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME ='CHECK_DROP';
SELECT * FROM DBA_CONS_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';
-- 해당 사항 없음

SELECT * FROM DBA_UNUSED_COL_TABS ;
-- 결과가 없다.


---------------------------------------------------------------------------------------------------------------------
-- 테스트 3 삭제 칼럼을 일반 INDEX 로 생성 - SET UNUSED
DROP TABLE CHECK_DROP ;
CREATE TABLE CHECK_DROP ( A1 NUMBER, A2 VARCHAR2(100), A3 VARCHAR2(100)) ;

CREATE INDEX XAK1_CHECK_DROP ON CHECK_DROP ( A1, A2, A3) ;

INSERT INTO CHECK_DROP VALUES ( 1, 'A','B');
INSERT INTO CHECK_DROP VALUES ( 2, 'C','D');
INSERT INTO CHECK_DROP VALUES ( 3, 'E','F');
COMMIT ;
SELECT * FROM CHECK_DROP ;

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH,NULLABLE
FROM DBA_TAB_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';

-- 인덱스 관련 체크
SELECT * FROM DBA_INDEXES WHERE TABLE_NAME ='CHECK_DROP';
SELECT * FROM DBA_IND_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';

-- CONSTRAINTS 관련 체크
SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME ='CHECK_DROP';
SELECT * FROM DBA_CONS_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';
-- 해당 사항 없음

ALTER TABLE CHECK_DROP SET UNUSED COLUMN A3 ;
-- 인덱스로만 사용시에는 쉽게 삭제 된다.

DESC CHECK_DROP ;

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH,NULLABLE
FROM DBA_TAB_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';

SELECT * FROM DBA_INDEXES WHERE TABLE_NAME ='CHECK_DROP';
SELECT * FROM DBA_IND_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';
-- INDEX가 칼럼 삭제에 따라서 삭제 되었다.

SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME ='CHECK_DROP';
SELECT * FROM DBA_CONS_COLUMNS WHERE TABLE_NAME ='CHECK_DROP';
-- 해당 사항 없음

SELECT * FROM DBA_UNUSED_COL_TABS ;
-- 결과가 있다. ㅇ.ㅇ

-- Unused Marking 된 칼럼 삭제 하기
ALTER TABLE CHECK_DROP DROP UNUSED COLUMNS ;
SELECT * FROM DBA_UNUSED_COL_TABS ;
-- 이제 결과가 없다.