08_DDL(ALTER, DROP) + DCL(GRANT, REVOKE)

 

ㅁ DDL들은 롤백이 불가능하다.

 

 

ㅁ < ALTER >

- 데이터베이스 객체(구조)를 변경하는 언어 (DDL)

 

 

ALTER 데이터베이스객체 객체명 변경할내용;

 

 

- 변경할 객체가 테이블일 경우 (지금은 테이블밖에 안배웠음)

 

ALTER TABLE 테이블명 변경할내용;

 

(1) 컬럼 추가/수정/삭제

(2) 제약조건 추가/삭제

(3) 컬럼명/제약조건명/테이블명 변경

 

 

 

(1) 컬럼 추가/수정/삭제

 

 

i) 컬럼 추가(ADD)

 

ALTER TABLE 테이블명  ADD 컬럼명 데이터타입 [DEFAULT 기본값]

 

- DEPT_COPY에 CNAME과 LNAME 컬럼 추가

 

ALTER TABLE DEPT_COPY ADD CNAME VARCHAR2(20);
ALTER TABLE DEPT_COPY ADD LNAME VARCHAR2(20) DEFAULT '한국';

 

 

 

ii) 컬럼 수정(MODIFY)

 

- 데이터타입 수정

 

ALTER TABLE 테이블명  MODIFY 컬럼명 바꾸고자하는데이터타입

 

- DEFAULT값 수정

 

ALTER TABLE 테이블명  MODIFY 컬럼명 DEFAULT 바꾸고자하는기본값

 

 

- 컬럼의 데이터 타입 수정

 

ALTER TABLE DEPT_COPY MODIFY DEPT_ID CHAR(3);     //   정상수행

ALTER TABLE DEPT_COPY MODIFY CNAME NUMBER;       //   정상수행

 

 

- 컬럼의 데이터 타입 수정시 오류나는 경우

 

ALTER TABLE DEPT_COPY MODIFY DEPT_ID NUMBER;

 

오류. column to be modified must be empty to change datatype.

CNAME은 비어있어서 문제 없는데 DEPT_ID는 이미 데이터 값이 담겨있어서 오류. 

단순히 데이터값이 이미 있어서 오류는 아니고 문자가 있어서 그런 듯.

 

 

ALTER TABLE DEPT_COPY MODIFY DEPT_TITLE VARCHAR2(10);

 

오류. cannot decrease column length because some value is too big

영문, 숫자, 특수문자는 각 1바이트. 한글은 3바이트.

"해외영업1부"면 이미 존재하는 바이트 값이 16이라서 줄일 수가 없다. (늘리는건 얼마든지 가능)

 

 

- 한 테이블의 여러 컬럼 동시 수정

 

ALTER TABLE DEPT_COPY  MODIFY DEPT_TITLE VARCHAR2(40)       //    컴마 없음!

                                                MODIFY LOCATION_ID VARCHAR2(2)

                                                MODIFY LNAME DEFAULT '미국';

 

 

LNAME 컬럼의 디폴트 설정값이 미국이 된거지 이미 존재하는 데이터들은 한국 그대로임.

 

 

 

iii) 컬럼 삭제(DROP)

 

 

ALTER TABLE 테이블명  DROP COLUMN 삭제할컬럼명;

 

 

- 여러 컬럼 동시 삭제 불가. 따로 따로 삭제해줘야 함.

- 그런데 모든 컬럼을 다 지울 수는 없음. 오류 남. 한 테이블에 최소 1개의 컬럼은 존재해야 함.

 

 

 

 

(2) 제약조건 추가/삭제

- 제약조건은 수정은 불가능 함. 추가, 삭제만 가능.

 

 

i) 제약조건 추가

 

ALTER TABLE 테이블명  ADD PRIMARY KEY(컬럼명)

ALTER TABLE 테이블명  ADD FROEIGN KEY(컬럼명) REFERENCES 참조할테이블

ALTER TABLE 테이블명  ADD UNIQUE(컬럼명)

ALTER TABLE 테이블명  ADD CHECK(컬럼에 대한 조건식)

 

ALTER TABLE 테이블명  MODIFY 컬럼명  NULL | NOT NULL

 

- 전부 공통적으로 제약조건명을 지정하고자 한다면 [CONSTRAINT 제약조건명] 제약조건.

 

 

ㅇ 제약조건 한번에 추가

 

CREATE TABLE DEPT_COPY AS SELECT *

                                                        FROM DEPARTMENT;

 

- DEPT_ID에 PRIMARY KEY 제약조건 추가

- DEPT_TITLE에 UNIQUE 제약조건 추가

- DEPT_TITLE에 NOT NULL 제약조건 추가 

 

ALTER TABLE DEPT_COPY  ADD CONSTRAINT DCOPY_PK PRIMARY KEY(DEPT_ID)   컴마 없음!

                                                ADD CONSTRAINT DCOPY_UQ UNIQUE(DEPT_TITLE)

                                                MODIFY DEPT_TITLE NOT NULL;

 

 

ii) 제약조건 삭제

 

 

ALTER TABLE 테이블명  DROP CONSTRAINT 제약조건명;

                               

ALTER TABLE 테이블명  MODIFY 컬럼명 NULL;

 

 

- 제약조건 삭제 한번에 가능.

 

ALTER TABLE DEPT_COPY  DROP CONSTRAINT DCOPY_PK

                                                DROP CONSTRAINT DCOPY_UQ

                                                MODIFY DEPT_TITLE NULL;

 

 

 

 

(3) 컬럼명/제약조건명/테이블명 변경 (RENAME)

 

 

i) 컬럼명 변경 

 

ALTER TABLE 테이블명  RENAME CLOLUMN 기존컬럼명 TO 바꿀컬럼명;

 

ALTER TABLE DEPT_COPY  RENAME COLUMN DEPT_TITLE TO DEPT_NAME;

 

ii) 제약조건명 변경

 

ALTER TABLE 테이블명  RENAME CONSTRAINT 기존제약조건명 TO 바꿀제약조건명;

 

ALTER TABLE DEPT_COPY  RENAME CONSTRAINT SYS_C007170 TO DCOPY_LID_NN;

 

iii) 테이블명 변경

 

ALTER TABLE 테이블명  RENAME [기존테이블명] TO 바꿀테이블명

 

ALTER TABLE DEPT_COPY RENAME TO DEPT_TEST;

 

 

 

 

====================================================================================

 

 

ㅁ < DROP >

- 데이터베이스 객체를 삭제시키는 구문

 

DROP 데이터베이스객체 객체명;

 

 

ㅇ 테이블 삭제시

 

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

 

- 그런데 삭제하려는 테이블이 외래키 제약조건 때문에 부모테이블인 경우 삭제가 안 될 수도 있음. 

그러면 자식테이블을 먼저 삭제한 후 부모테이블을 삭제하거나,

부모테이블을 바로 지우고자 할 경우에는 제약조건까지 같이 삭제한다(CASCADE CONSTRAINT).

 

DROP TABLE DEPT_TEST;

 

 

 

==============================================================

 

ㅁ < DCL >

- Data Control Language 데이터 제어어

- 특정 계정의 권한을 관리(권한부여, 권한회수)하는 구문

- GRANT(권한부여), REVOKE(권한회수)

 

 

ㅁ 권한 종류

- 권한종류는 엄청 많은데 다 알 필요는 없다.

권한종류는 크게 두 가지로 나뉨. 시스템 권한과 객체 권한.

 

(1) 시스템 권한

- CREATE SESSION : 접속할 수 있는 권한

- CREATE TABLE : 테이블을 생성할 수 있는 권한

- CREATE VIEW : 뷰를 생성할 수 있는 권한

- CREATE SEQUENCE : 시퀸스를 생성할 수 있는 권한

...

 

(2) 객체 권한

- 특정 객체에 접근해서 조작할 수 있는 권한

 

SELECT  |  TABLE, VIEW, SEQUENCE

INSERT   |  TABLE, VIEW

UPDATE   |  TABLE, VIEW

DELETE   |  TABLE, VIEW

....

 

 

 

ㅁ <GRANT>

- 권한을 부여하는 구문

 

GRANT 권한종류, 권한종류, 롤, ... TO 계정명;             //         시스템 권한 부여

 

GRANT 권한종류 ON 특정객체 TO 계정명;             //         객체 권한 부여

 

 

ㅇ 예시

 

- 관리자 계정) 샘플 계정 생성 

 

CREATE USER SAMPLE IDENTIFED BY SAMPLE;

 

 

- 관리자 계정) 접속을 위해 CREATE SESSION(CONNECT) 권한 부여

 

GRANT CREATE SESSION TO SAMPLE;

 

 

 

 

- SAMPLE 계정) 테이블 만들어보기

 

CREATE TABLE TEST ( TEST_ID NUMBER,
                                        TEST_NAME VARCHAR2(20) );

 

오류남. "insufficient privileges". 테이블생성권한 없음. 

 

 

- 관리자 계정) SAMPLE 계정에게 테이블 생성 권한 주기

 

GRANT CREATE TABLE TO SAMPLE;

 

 

- SAMPLE 계정)

 

CREATE TABLE TEST ( TEST_ID NUMBER,
                                        TEST_NAME VARCHAR2(20) );

 

오류남. "no privileges on tablespace '%s'"

테이블 SPACE에 대한 권한이 없다. 테이블이 생성되면 SPACE를 차지함.

추가로 테이블 SPACE까지 할당해 줘야 테이블을 만들 수 있음.

 

 

- 관리자 계정) SAMPLE 계정에 TABLESPACE 할당

 

ALTER USER SAMPLE QUOTA 2M ON SYSTEM;

 

 

- SAMPLE 계정)

 

CREATE TABLE TEST ( TEST_ID NUMBER,
                                        TEST_NAME VARCHAR2(20) );

 

드디어 테이블 생성 성공.

 

 

 

 

 

- 관리자 계정) SAMPLE 계정에게 BR 계정의 EMPLOYEE 테이블을 조회할 수 있는 권한 부여

 

GRANT SELECT ON BR.EMPLOYEE TO SAMPLE;

 

 

- SAMPLE 계정) BR계정의 EMPLOYEE 테이블 조회하기

 

SELECT *
FROM EMPLOYEE;

 

그냥 EMPLOYEE만 하면 내 계정에서 찾음.

 

SELECT *
FROM BR.EMPLOYEE;

 

이러면 찾아짐.

 

 

 

 

 

 

- 관리자 계정) BR계정의 DEPARTMENT 테이블에 데이터를 추가할 수 있는 권한 부여

 

GRANT INSERT ON BR.DEPARTMENT TO SAMPLE;

 

 

- SAMPLE 계정) BR계정의 DEPARTMENT 테이블을 조회하는 것은 안 됨.

 

SELECT *
FROM BR.DEPARTMENT;

 

오류. "insufficient privileges"

 

- SAMPLE 계정) 

 

INSERT INTO BR.DEPARTMENT VALUES('D0', '회계부', 'L1');

ROLLBACK;

 

정상적으로 실행됨. 테스트로 해본거라 롤백.

 

 

 

ㅁ < REVOKE >

- 권한을 회수하는 구문

 

 

REVOKE 권한 FROM 계정명;

 

 

- 관리자 계정) BR계정의 권한 뺏기

 

REVOKE SELECT ON BR.EMPLOYEE FROM SAMPLE;

 

BR 계정가서 EMPLOYEE 테이블 조회 시도하면 오류 발생.