ALTER TABLE 문
• alter table ... add 컬럼
• alter table ... modify 컬럼
• alter table ... drop column 컬럼
• alter table ... drop[constraint] 제약조건
• alter table ... add [constraint] 제약조건 (컬럼)
이 문을 실행하려면 자신의 스키마에 대해 ALTER 객체 권한과 ALTER ANY TABLE 시스템 권한이 있어야 한다.

alter table 예제

ALTER 명령은 객체에 대한 모든 변경을 위해 사용된다.
ALTER TABLE 명령을 사용하여 새로운 컬럼을 추가, 삭제, 변경할 수 있다.
ALTER TABLE 명령을 사용하여 새로운 컬럼을 추가하면 기존의 테이블의 마지막 컬럼이 된다.
 추가수정삭제
컬럼가능가능가능
제약조건가능불가능가능

【형식】
ALTER TABLE [schema.]table 
[{alter_table_properties ¦
  column_clauses ¦
  constraint_clause ¦
  alter_table_partitioning ¦
  alter_external_table_clauses ¦
  move_table_clause}]

[{{enable_disable_clause} ¦

  {{ENABLE ¦ DISABLE} {TABLE LOCK ¦
                       ALL TRIGGERS} }...} ];

alter table 원형 자세히 보기

ALTER TABLE ... ADD 컬럼에 의한 컬럼 추가

ADD 문은 테이블에 컬럼 추가, 또는 컬럼에 제약조건이 없을 경우 제약조건을 추가하는데 사용한다.
컬럼 추가시 테이블의 행이 존재한다면, 새로 추가되는 컬럼은 이미 존재하는 모든 행의 값은 NULL로 초기화한다.

【형식】컬럼추가
	ALTER TABLE 테이블명
	ADD (컬럼명 datatype [DEFAULT 값]
	    [,컬럼명 datatype]...);

【형식】constraint추가
	ALTER TABLE 테이블명
	ADD (컬럼명 datatype CONSTRAINT constraint명 constraint실제값
	    [,컬럼명 datatype]...);
• 한번의 add 문으로 여러 개의 컬럼 추가가 가능하고, 하나의 컬럼만 추가하는 경우에는 괄호를 생략해도 된다.
• 제약조건에 대한 변경 여부의 확인은 user_constraints 뷰를 사용하여 확인할 수 있다.
• create table as...와 같은 서브쿼리하여 만든 테이블은 NOT NULL만 복사되며, primary key와 같은 제약조건은 복사되지 않는다.
• 그러므로 subquery로 만든 테이블에 제약조건을 추가해 주어야 한다.
• 추가된 컬럼은 테이블의 마지막 부분에 생성되며 사용자가 컬럼의 위치를 지정할 수 없다
• 추가된 컬럼에도 기본 값을 지정할 수 있다.
• 기존 데이터가 존재하면 추가된 컬럼 값은 NULL로 입력 되고, 새로 입력되는 데이터에 대해서만 기본 값이 적용된다.
【예제】
SQL> create table test   ☜ 연습을 위해 테이블 복사
  2  as select * from dept;
 
테이블이 생성되었습니다.
 
SQL> desc test;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)
 
SQL> alter table test    ☜ 컬럼 하나 추가, 하나인 경우 괄호 생략 가능
  2  ADD (tel number(20));
 
테이블이 변경되었습니다.
 
SQL> select * from test;   ☜ 기존 데이터는 NULL로 채워짐
 
    DEPTNO DNAME          LOC                  TEL
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK      빈공간
        20 RESEARCH       DALLAS        빈공간
        30 SALES          CHICAGO       빈공간
        40 OPERATIONS     BOSTON        빈공간



 
SQL> alter table test
  2  add(marriage char(8) constraint test_marriage_ck
  3  CHECK (marriage IN ('SINGLE','MARRIED','DIVORCED')));
 
테이블이 변경되었습니다.

SQL>
 ☜ 컬럼을 추가하면서 동시에 제약조건을 부여함
오른쪽처럼 두번에 나누어 실행하여도 됨

SQL> alter table test
  2  add marriage char(8);
 
테이블이 변경되었습니다.
 
SQL> alter table test
  2  add constraint test_marriage_ck
  3  check (marriage in('SINGLE','MARRIED','DIVORCED'));
 
테이블이 변경되었습니다.
 
SQL>



 
SQL> desc test;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)
 TEL                                                NUMBER(20)
 MARRIAGE                                           CHAR(8)
 
SQL> alter table test   ☜ 제약조건 추가
  2  ADD CONSTRAINT test_deptno_pk PRIMARY KEY(deptno);
 
테이블이 변경되었습니다.
 
SQL> select constraint_name,constraint_type
  2  from user_constraints
  3  where table_name='TEST';  ☜ 제약조건 확인
 
CONSTRAINT_NAME                C
------------------------------ -
TEST_MARRIAGE_CK               C
TEST_DEPTNO_PK                 P
 
SQL> 
【예제】  ☜ 제약조건중 서브쿼리에서 not null만 내려 받음 
SQL> create table aaa(
  2  id number(3) constraint aaa_id_pk PRIMARY KEY,
  3  name varchar2(10) NOT NULL);
 
테이블이 생성되었습니다.
 
SQL> create table bbb  ☜ PK와 NN중에서 NN만 이어 받음
  2  AS select * from aaa;
 
테이블이 생성되었습니다.
 
SQL> select constraint_name,constraint_type
  2  from user_constraints   ☜ 제약조건 확인
  3  where table_name='AAA';
 
CONSTRAINT_NAME                C
------------------------------ -
SYS_C005207                    C
AAA_ID_PK                      P
 
SQL> select constraint_name,constraint_type
  2  from user_constraints
  3  where table_name='BBB'; ☜ PK 제약조건은 없음
 
CONSTRAINT_NAME                C
------------------------------ -
SYS_C005209                    C
 
SQL>
ALTER TABLE ... MODIFY 컬럼에 의한 컬럼 변경

modify 문은 테이블의 컬럼을 변경하고자 할 때 사용한다.

【형식】
        ALTER TABLE 테이블명
        MODIFY (컬럼명 datatype [DEFAULT 값]
               [,컬럼명 datatype]...);
• 데이터의 type, size, default 값을 변경할 수 있다.
• 변경 대상 컬럼에 데이터가 없거나 null 값만 존재할 경우에는 size를 줄일 수 있다.
• 데이터 타입의 변경은 CHAR와 VARCHAR2 상호간의 변경만 가능하다.
• 컬럼 크기의 변경은 저장된 데이터의 크기보다 같거나 클 경우에만 가능하다.
• NOT NULL 컬럼인 경우에는 size의 확대만 가능하다.
• 컬럼의 기본값 변경은 그 이후에 삽입되는 행부터 영향을 준다.
• 컬럼이름의 직접적인 변경은 불가능하다.
• 컬럼이름의 변경은 서브쿼리를 통한 테이블 생성시 alias를 이용하여 변경이 가능하다.
• alter table ... modify문을 이용하여 제약조건을 수정할 수 없다.
데이터 타입 변경 가능사항SIZE
NULL 컬럼문자 ↔ 숫자 ↔ 날짜확대, 축소가능
NOT NULL 컬럼CHAR ↔ VARCHAR2확대만 가능

【예제】
SQL> desc test;
 이름                            널?      유형
 ------------------------------- -------- ----------------------------
 DEPTNO                          NOT NULL NUMBER(2)
 DNAME                                    VARCHAR2(14)
 LOC                                      VARCHAR2(13)
 TEL                                      NUMBER(20)
 MARRIAGE                                 CHAR(8)
 
SQL> alter table test
  2  modify (tel number(10));  ☜ 비어 있는 컬럼의 size축소
 
테이블이 변경되었습니다.
 
SQL> alter table test 
  2  modify (loc char(13));  ☜ 컬럼의 데이터 타입 변경
 
테이블이 변경되었습니다.
 
SQL> alter table test
  2  modify (loc char(10)); ☜ 비어 있는 않은 컬럼 축소는 않됨 
modify (loc char(10))
        *
2행에 오류:
ORA-01441: 일부 값이 너무 커서 열 길이를 줄일 수 없음
 
 
SQL> desc test;
 이름                            널?      유형
 ------------------------------- -------- ----------------------------
 DEPTNO                          NOT NULL NUMBER(2)
 DNAME                                    VARCHAR2(14)
 LOC                                      CHAR(13)
 TEL                                      NUMBER(10)
 MARRIAGE                                 CHAR(8)
 
SQL>
ALTER TABLE ... DROP [CONSTRAINT] 제약조건명

alter table ... drop 문은 테이블을 삭제하는 것이 아니라, 특정 테이블의 컬럼이나 constraint를 삭제할 때 사용한다.

【형식】
        ALTER TABLE 테이블명
        DROP [CONSTRAINT] constraint명;
• constraint는 user_constraints뷰를 이용하여 확인할 수 있다.
• 하나의 테이블에는 하나의 primary key만 존재하므로 primary key삭제에서 constraint 예약어를 생략해도 된다.
【예제】 
SQL> select constraint_name,constraint_type
  2  from user_constraints   ☜ 제약조건 확인
  3  where table_name='TEST';
 
CONSTRAINT_NAME                C
------------------------------ -
TEST_MARRIAGE_CK               C
TEST_DEPTNO_PK                 P
 
SQL> alter table test
  2  drop constraint test_deptno_pk;  ☜ 제약조건 삭제
 
테이블이 변경되었습니다.
 
SQL> alter table test  ☜ 연습으로 제약조건 추가 
  2  add constraint test_deptno_pk primary key(deptno);
 
테이블이 변경되었습니다.
 
SQL> alter table test
  2  drop primary key;  ☜ 테이블당 PK는 하나이므로 constraint라는 예약어는 생략가능
 
테이블이 변경되었습니다.
 
SQL> select constraint_name,constraint_type
  2  from user_constraints
  3  where table_name='TEST'; ☜ PK 생략 확인
 
CONSTRAINT_NAME                C
------------------------------ -
TEST_MARRIAGE_CK               C
 
SQL>
CASCADE 옵션을 사용하는 경우
• primary key가 다른 테이블에서 참조되는 자식 테이블이 있을 경우에는 삭제할 수 없다
• 부모와 자식 테이블로 구성된 경우의 primary key 삭제는 다음과 같이 cascade를 지정해야 한다.
【형식】
        ALTER TABLE 테이블명
        DROP PRIMARY KEY CASCADE; 

【예제】
SQL> select constraint_name,constraint_type,table_name,
  2  r_constraint_name
  3  from user_constraints;
 
CONSTRAINT_NAME      C TABLE_NAME R_CONSTRAINT_NAME
-------------------- - ---------- ------------------------------
FK_DEPTNO            R EMP        PK_DEPT
PK_DEPT              P DEPT
PK_EMP               P EMP
 
SQL> alter table dept
  2  drop primary key;  ☜ dept 테이블의 deptno 컬럼이 emp 테이블에서 FK로 참조되고 있음
alter table dept
*
1행에 오류:
ORA-02273: 유일/기본 키가 외부 키에 의해 참조되었습니다
 
 
SQL>
ALTER TABLE ... DROP COLUMN 컬럼에 의한 컬럼 삭제

alter table ... drop 문은 테이블을 삭제하는 것이 아니라, 특정 테이블의 컬럼이나 constraint를 삭제할 때 사용한다. 

【형식】
        ALTER TABLE 테이블명
        DROP COLUMN 컬럼명;
• 컬럼을 삭제하면 해당 컬럼에 저장된 데이터도 함께 삭제된다.
• 한번에 하나의 컬럼만 삭제할 수 있다.
• 삭제 후 테이블에는 적어도 하나의 컬럼은 존재해야 한다.
• 삭제된 컬럼은 복구할 수 없다.
【예제】
SQL> desc test;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                CHAR(13)
 TEL                                                NUMBER(10)
 MARRIAGE                                           CHAR(8)
 
SQL> alter table test
  2  drop column tel;
 
테이블이 변경되었습니다.
 
SQL> desc test;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                CHAR(13)
 MARRIAGE                                           CHAR(8)
 
SQL>
SET UNUSED 옵션을 사용하는 경우
set unused 옵션을 사용하여 지정한 컬럼을 사용하지 않겠다고 하는 것이다.
방법1방법2
【형식】
        ALTER TABLE 테이블명
        SET UNUSED (컬럼명); 

【형식】
        ALTER TABLE 테이블명
        SET UNUSED COLUMN 컬럼명;

• 실제로 컬럼이 삭제되는 것이 아니기 때문에 할당된 디스크 공ㅅ간은 해제되지 않는다.
• 여러 개의 컬럼을 동시에 UNUSED로 설정할 수 있다.
• UNUSED된 컬럼은 데이터를 포함하고 있더라도 삭제된 것처럼 간주한다.
• UNUSED된 컬럼은 access할 수 없다.
• UNUSED된 컬럼과 동일한 이름의 컬럼을 테이블에 추가할 수 있다.
• UNUSED된 컬럼을 디스크 할당에서 완전히 회수하려면, DROP UNUSED COLUMNS명령을 사용해야 한다.
• UNUSED된 컬럼은 desc나 select 문으로 확인할 수 없다.
• UNUSED된 컬럼은 확인할 수 없으며, 단지 data dictionary인 dba_unused_col_tabs를 통하여 누구 소유의 어떤 테이블에 몇개의 unused 컬럼이 있는지만 확인이 가능하다.

【예제】
SQL> desc test;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                CHAR(13)
 MARRIAGE                                           CHAR(8)
 
SQL> alter table test 
  2  SET UNUSED COLUMN marriage;  ☜ marriage 컬럼을 unused시킴
 
테이블이 변경되었습니다.
 
SQL> alter table test
  2  SET UNUSED (loc);  ☜ loc 컬럼을 unused 시킴
 
테이블이 변경되었습니다.
 
SQL> conn system/manager
Connected.
 
세션이 변경되었습니다.

SQL> desc test;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(2)
 DNAME                                              VARCHAR2(14)
 
SQL> select * from dba_unused_col_tabs;  ☜ 테이블이름과 unused수만 확인이 가능함
 
OWNER                          TABLE_NAME      COUNT
------------------------------ ---------- ----------
SCOTT                          TEST                2
 
SQL> conn scott/tiger
Connected.
 
세션이 변경되었습니다.
 
SQL> desc test;  ☜ desc로 unused된 컬럼이 표시되지 않음
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(2)
 DNAME                                              VARCHAR2(14)
 
SQL> ALTER TABLE test
  2  DROP UNUSED COLUMNS;  ☜ unused 된 컬럼을 완전히 삭제시킴
 
테이블이 변경되었습니다.
 
SQL> conn system/manager
Connected.
 
세션이 변경되었습니다.
 
SQL> select * from dba_unused_col_tabs;  ☜ unused된 컬럼이 없음
 
선택된 레코드가 없습니다.
 
SQL> 

 alter table ... allocate extent에 의한 extent 할당 


ALTER TABLE 테이블명 ALLOCATE EXTENT EXTENT 할당
ALTER TABLE 테이블명 SET UNUSED COLUMN 컬럼명 컬럼을 unused 시킴(히든으로 하여 숨김)
ALTER TABLE 테이블명 DROP UNUSED COLUMNS unused된 컬럼을 완전히 삭제함 (히든 컬럼을 삭제함)