디비랑[dɪ'bɪraŋ]

/********************************************************************************************
-- Title : [8i] 테이블 생성과 관리(OLN)
-- Reference : OLN
-- Key word : set unused rename comment
********************************************************************************************/

/****************************************************************************************
-- 사용자가 소유한 테이블 기술
****************************************************************************************/
SQL> SELECT *
  2  FROM USER_TABLES;

-- 사용자가 소유한 별개의 다른 객체 유형 기술
SQL> SELECT DISTINCT object_type
  2  FROM   USER_OBJECTS;

OBJECT_TYPE
------------------
INDEX
TABLE

/****************************************************************************************
-- 사용자가 소유한 테이블, 뷰, 동의어 그리고 시퀀스 기술
****************************************************************************************/
SQL> SELECT *
  2  FROM USER_CATALOG;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
BONUS                          TABLE
DEPT                           TABLE
DEPT_1                         TABLE
EMP                            TABLE
SALGRADE                       TABLE

/****************************************************************************************
-- 주석문 보기 위한 데이터 사전 뷰
****************************************************************************************/
   - ALL_COL_COMMENTS
   - ALL_TAB_COMMENTS
   - USER_COL_COMMENTS
   - USER_TAB_COMMENTS

/****************************************************************************************
-- 사용자 단위 테이블 주석 보기
****************************************************************************************/
SQL> SELECT *
  2  FROM USER_TAB_COMMENTS;

TABLE_NAME          TABLE_TYPE         COMMENTS
------------------- ------------------ ----------------------------------
EMP                 TABLE Employee     Information

/****************************************************************************************
-- 테이블 생성
****************************************************************************************/
SQL> CREATE TABLE dept_1
  2  ( deptno NUMBER(2)
  3  , dname  VARCHAR2(14)
  4  , loc    VARCHAR2(13)
  5  );

Table created.

/****************************************************************************************
-- 테이블 생성 확인
****************************************************************************************/
SQL> DESC dept_1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)
 
 
/****************************************************************************************
-- 서브쿼리를 사용한 테이블 생성
****************************************************************************************/
SQL> CREATE TABLE dept30
  2  AS
  3  SELECT empno, ename, sal*12 ANNSAL, hiredate
  4  FROM emp
  5  WHERE deptno = 30;

Table created.

/****************************************************************************************
-- 기존 테이블에 열 추가
****************************************************************************************/
SQL> ALTER TABLE dept30
  2  ADD ( job VARCHAR2(9));

Table altered.

/****************************************************************************************
-- 기존 테이블의 열 수정
****************************************************************************************/
SQL> ALTER TABLE dept30
  2  MODIFY (ename VARCHAR2(15));

Table altered.

/****************************************************************************************
-- 기존 테이블의 열 삭제
****************************************************************************************/
SQL> ALTER TABLE dept30
  2  DROP COLUMN job;

Table altered.

/****************************************************************************************
-- SET UNUSED 옵션을 사용한 삭제
****************************************************************************************/
SQL> ALTER TABLE dept30
  2  SET UNUSED (ename);

Table altered.

SQL> ALTER TABLE dept30
  2  DROP UNUSED COLUMNS;

Table altered.

/****************************************************************************************
-- 테이블 삭제
****************************************************************************************/
SQL> DROP TABLE dept30;

Table dropped.

/****************************************************************************************
-- 객체 이름 변경
****************************************************************************************/
SQL> RENAME dept TO department;

Table renamed.

/****************************************************************************************
-- 테이블 모든 행 삭제
****************************************************************************************/
SQL> TRUNCATE TABLE department;
TRUNCATE TABLE department
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

/****************************************************************************************
-- 테이블에 주석문 추가
****************************************************************************************/
SQL> COMMENT ON TABLE emp
  2  IS 'Employee Information';

Comment created.

-- 컬럼 주석 추가
SQL> COMMENT ON COLUMN scott.emp.sal
2 IS '컬럼 주석 추가';
Comment created.

/****************************************************************************************
-- 주석 지우기
****************************************************************************************/
SQL> COMMENT ON TABLE emp
  2  IS '';

Comment created.