Oracle 7일차- ALTER,MODIFY, DROP, COMMENT,GRANT, REVOKE,

1.테이블 수정

▶컬럼 1개 추가

ALTER TABLE 테이블명

ADD 컬럼명 데이터타입;

ALTER TABLE SALESMAN1

ADD DNO NUMBER(2);

▶컬럼 M개 추가

ALTER TABLE SALESMAN1

ADD (JUMIN CHAR(14),ZIP CHAR(7));

ALTER TABLE 테이블명

ADD (컬럼명1 데이터타입1, 칼럼명2 데이터타입2, ….., 칼럼명M 데이터타입M);

▶기본값으로 DEFAULT에 있는 값을 넣어줌

ALTER TABLE 테이블명

ADD 컬럼명 데이터타입 DEFAULT(값);

ALTER TABLE SALESMAN1

ADD BIGO VARCHAR(10) DEFAULT('AA');

▶제약조건

ALTER TABLE 테이블명

ADD 컬럼명 데이터타입 CONSTRAINT 제약조건명 제약조건 (조건);

ALTER TABLE SALESMAN1

ADD(MARRIAGE CHAR(8) CONSTRAINT  SALESMAN1_MARRIAGE_CK

CHECK (MARRIAGE IN ('SINGLE','MARRIED'))); ▶CHECK KEY

ALTER TABLE SALESMAN1

ADD CONSTRAINT SALESMAN1_PNO_PK PRIMARY KEY(PNO); ▶PRIMARY KEY 추가

최악의 DB 모델링

◀L테이블 : NULL값이 들어간 상태에서 데이터 추가

◀계단형 테이블

컬럼수정

▶MODIFY

▶데이터가 없을때

   ○문자↔숫자↔날짜

   ○사이즈 확대,축소

▶데이터가 있을때

   ○CHAR↔VARCHAR2만 가능

   ○사이즈 확대만 가능(저장되어 있는 데이터보다 작게는 불가)

ALTER TABLE 테이블명

MODIFY 컬럼명 수정데이터타입;

ALTER TABLE MANAGER1

MODIFY PNAME VARCHAR2(16);

2.삭제(DROP)

▶DROP(컬럼,제약조건 삭제)

▶테이블은 그대로 있는 상태에서 제약조건이나 컬럼을 삭제

ALTER TABLE 테이블명

DROP CONSTRAINT 제약조건이름/제약조건;

ALTER TABLE MANAGER1

DROP CONSTRAINT MANAGER_PNO_PK;

ALTER TABLE MANAGER1

DROP PRIMARY KEY;

ALTER TABLE DIVISION

DROP CONSTRAINT DIVISION_DNO_PK; ▶고유/기본 키가 외부 키에 의해 참조되었습니다

▶참조키 삭제

ALTER TABLE PERSONNEL

DROP PERSONNEL_PNO_PK;

ALTER TABLE DIVISION

DROP PRIMARY KEY;

▼수정

ALTER TABLE DIVISION

DROP PRIMARY KEY CASCADE;

▶컬럼삭제

ALTER TABLE 테이블명

DROP COLUMN 컬럼명;

ALTER TABLE SALESMAN1

DROP COLUMN MARRIAGE;

▶SET UNUSED : 삭제는 하지 않지만 사용자에게 보여주지 않음

ALTER TABLE 테이블명

SET UNUSED COLUMN 컬럼명;

ALTER TABLE SALESMAN1

SET UNUSED COLUMN DNO;

SET UNUSED 확인

SELECT * FROM DBA_UNUSED_COL_TABS;

▶테이블 삭제

DROP TABLE 테이블명;

DROP TABLE MANAGER1;

▶휴지통

휴지통 확인

SELECT * FROM RECYCLEBIN;

삭제된 내용 확인

SELECT * FROM "BIN$J4KDdTJiRK2HS6VIZUEBrA==$0";

휴지통 복원

FLASHBACK TABLE 테이블명 TO BEFORE DROP;

FLASHBACK TABLE "쓰레기통안의 테이블명" TO BEFORE DROP;

FLASHBACK TABLE MANAGER1 TO BEFORE DROP;

FLASHBACK TABLE "BIN$J4KDdTJiRK2HS6VIZUEBrA==$0" TO BEFORE DROP;

휴지통 비우기

PURGE RECYCLEBIN;

바로 삭제

DROP TABLE 테이블명 PURGE;

DROP TABLE MANAGER1 PURGE;

▶TRUNCATE / DELETE의 차이(레코드삭제)

DELETE : 로그파일 저장O. 속도가 느림. ROLLBACK 가능

DELETE DIVISION;

TRUNCATE : 로그파일 저장X. 속도가 빠름. ROLLBACK 불가 → RECOVERY 해야함

TRUNCATE TABLE DIVISION;

3.RENAME, COMMENT

▶RENAME

RENAME 테이블명 TO 변경테이블명;

RENAME PAY3000 TO HIGHPAY;

▶COMMENT

SELECT * FROM USER_TAB_COMMENTS; → COMMENT 확인

COMMENT 추가

COMMENT ON TABLE 테이블명

IS ‘COMMENT 내용’;

COMMENT ON TABLE HIGHPAY

IS '연봉 많이 받는 사람...개부럽';

COMMENT 지움

COMMENT ON TABLE HIGHPAY

IS '';

컬럼 주석

COMMENT ON TABLE  테이블명.POSITION

IS 'COMMENT 내용';

COMMENT ON TABLE  BUSEO.POSITION

IS '부서 위치';

4.DCL(GRANT/REVOKE)

▶GRANT  : USER나 OBJECT에 권한을 부여할때 사용

▶REVOKE: USER나 OBJECT에 권한을 회수할때 사용

▶권한의 종류

1.SYSTEM 권한

   ○DATABASE의 OBJECT를 생성(CREATE),수정(ALTER),삭제(DROP)를 할 수 있는 권한

   ○DBA가 USERS에게 부여

2.OBJECT 권한

   ○OBJECT의 내용을 조작(추가(INSERT),변경(UPDATE),삭제(DELETE),검색(SELECT))할 수 있는 권한

   ○USER가 USERS에게 부여

▶SYSTEM권한검색

SELECT * FROM SYSTEM_PRIVILEGE_MAP;

▶권한부여(GRANT)

GRANT CREATE USER TO 사용자;

GRANT CREATE USER TO KIM;

GRANT CREATE SESSION,RESOURCE,UNLIMITED TABLESPACE TO TEST;

 → CREATE SESSION을 CONNECT로 변경 가능

 → 로그인 : CREATE SESSION

 → DML : RESOURCE

▶권한회수(REVOKE)

REVOKE RESOURCE FROM 사용자;

REVOKE RESOURCE FROM TEST;

REVOKE CREATE SESSION FROM TEST;

SYSTEM 권한

▶USER 생성 → SYS에서 가능

CREATE USER 사용자

IDENTIFIED BY 비밀번호

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP;

CREATE USER TEST

IDENTIFIED BY TEST

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP;

▶DB의 정보 확인

SELECT * FROM DBA_USERS;

OBJECT권한 : USER가 USERS에게 권한을 주기때문에 KIM에서 실행

GRANT 권한1, 권한2… ON  테이블명 TO 사용자;

GRANT SELECT,INSERT,DELETE ON  DIVISION1 TO PARK;

REVOKE 권한1, 권한2… ON  테이블명 FROM 사용자;

REVOKE SELECT,INSERT,DELETE ON  DIVISION1 FROM PARK;

▶SYS에서 실행시

GRANT SELECT ON 스키마.테이블명 TO PUBLIC; → 스키마 : 사용자(USER)

GRANT SELECT ON KIM.BUSEO TO PUBLIC;

  → PUBLIC : 모두에게 권한 부여

                       PUBLIC으로 권한 부여시 PUBLIC으로만 권한 회수 가능

▶권한 확인

SELECT * FROM USER_TAB_PRIVS_MADE;

5.ROLL

ROLE(역할) : 권한을 줄 때 사용

1.ROLE 만들기

  CREATE ROLE 롤이름;

2.GRANT 명령어로 ROLE에 권한 부여

  GRANT 권한 TO 롤;

3.ROLE을 USER에게 GRANT 명령어로 권한 부여

 GRANT 롤 TO 사용자;

CREATE ROLE MANAGER;

GRANT CREATE SESSION,CREATE TABLE,UNLIMITED TABLESPACE TO MANAGER;

GRANT MANAGER TO PARK,TEST;

권한회수

REVOKE MANAGER TO PARK;

▶OBJECT권한도 ROLE에 사용 가능

CREATE ROLE KIMOBJ;

GRANT SELECT,INSERT,DELETE ON KIM.DIVISION1 TO KIMOBJ;

GRANT KIMOBJ TO TEST;

▶ROLE에 부여된 SYSTEM 권한 확인

SELECT * FROM ROLE_SYS_PRIVS

WHERE ROLE=’롤’

▶ROLE에 부여된 TABLE 권한 확인

SELECT * FROM ROLE_TAB_PRIVS;

WHERE TABLE_NAME='테이블';

▶현재 USER에게 부여된 ROLE 권한 확인

SELECT * FROM USER_ROLE_PRIVS;

▶현재 USER가 소유하고 있는 OBJECT

SELECT * FROM USER_TAB_PRIVS_MADE;

▶현재 USER에게 소유된 OBJECT의 컬럼정보 확인

SELECT * FROM USER_TAB_PRIVS_MADE;