오라클 테이블 생성, CREATE TABLE, 이론 및 실습

 

오라클 테이블 생성, CREATE TABLE, 이론 및 실습

10.2. 테이블 생성

테이블을 생성하려면 CREATE TABLE 시스템 권한과, 테이블스페이스에 저장할 권한 또는 UNLIMITED TABLESPACE 시스템 권한이 있어야 합니다. 

테이블 생성은 테이블에 대한 구조(컬럼 및 데이터타입, 제약조건)를 정의하고 저장공간을 할당하는 과정입니다. 테이블을 생성하려면 DDLdata definition language 명령인 CREATE TABLE 명령을 사용합니다.

[기본형식]

CREATE [GLOBAL TEMPORARY] TABLE [SCHEMA.]table_name (    column1 datatype [ DEFAULT expr ][constraints],    column2 datatype [ DEFAULT expr ][constraints], ... ); GLOBAL TEMPORARY : 임시 테이블. 구조는 모든 세션에서 볼 수 있지만 데이터는 임시 테이블을 생성한 세션에서만 확인할 수 있습니다. SCHEMA : 오라클사용자 계정 table_name : 생성하고자 하는 테이블 이름 column1, column2 : 열 이름 datatype : 열 데이터 타입 DEFAULT expr : 열에 값이 입력되지 않는 경우 컬럼 기본값 지정 constraints : 컬럼의 제약조건(NOT NULL, CHECK, PRIMARY KEY 등)

실습

테이블 생성을 위한 기본 문법을 바탕으로 컬럼의 데이터 타입을 정의하면서 실제 테이블을 생성하여 보겠습니다. 

NAME, ADDRESS, AGE 컬럼을 가지는 ADDRBOOK1 테이블을 생성하세요. NAME 컬럼은 VARCHAR2(10),  not null 제약조건을 사용 ADDRESS 컬럼은 VARCHAR2(50) AGE 컬럼은 NUMBER(3), 기본값을 0으로

CREATE TABLE ADDRBOOK1 (    NAME     VARCHAR2(10) NOT NULL,    ADDRESS VARCHAR2(50),    AGE        NUMBER DEFAULT 0 );

<실행결과>

Table ADDRBOOK1이(가) 생성되었습니다.

실습

CREATE TABLE 명령에 어느 테이블스페이스에 생성할 것인지 옵션을 주지 않으면 해당 사용자의 기본 테이블스페이스에 테이블을 만듭니다. 물론 사용자 생성 시 기본 테이블스페이스를 지정하지 않았다면 오라클 12C 이상에서는 USERS 테이블스페이스가 기본적으로 할당됩니다. 현재 접속한 사용자인 SCOTT 사용자도 생성 시 기본 테이블스페이스를 지정하지 않았으므로 USER 테이블스페이스에 테이블이 생성됩니다.

USER_TABLES 딕셔너리뷰를 통해  현재 사용자가 소유(작성)한 테이블의 목록을 확인 할 수 있습니다. 

USER_TABLES 딕셔너리 뷰를 통해 ADDRBOOK1 테이블이 어느 테이블스페이스에 생성 되었는지 확인 합시다.

SELECT TABLE_NAME             ,  TABLESPACE_NAME  FROM     USER_TABLES WHERE TABLE_NAME = 'ADDRBOOK1';

<실행결과>

	TABLE_NAME	TABLESPACE_NAME
1	ADDRBOOK1	USERS

실습

TAB 뷰를 통해서도 테이블 정보 조회가 가능한데, TAB 은 현재 사용자가 소유 한 테이블, 뷰 및 동의어에 대한 정보를 제공합니다. 

ADDRBOOK1 테이블이 어느 테이블스페이스에 생성 되었는지 조회해 봅시다.

SELECT TNAME             ,  TABTYPE FROM    TAB  WHERE TNAME = 'ADDRBOOK1';

<실행결과>

	TNAME	TABTYPE
1	ADDRBOOK1	TABLE

TAB 뷰를 이용한 테이블 정보 조회시 주의 할 부분이 있는데요, 오라클에서 휴지통(리사이클빈) 기능이 활성화 될 경우  테이블이 삭제되면 휴지통(리사이클빈)에 들어 가는데 이름이 ‘BIN$M+6XUqkuTSqHmaL81Olcgg==$0’ 식으로 변형되어 들어 갑니다. TAB 뷰는 이런것 들도 조회된다는 것 입니다. 이런 것들이 보이면 놀라지 마시고 “삭제된 테이블 이구나” 라고 이해해 주세요. 

실습

USER_CATALOG 딕셔너리 뷰는 상점에서 물건을 진열하듯이 현재 사용자가 소유 한 테이블,  인덱스,뷰, 클러스터, 동의어 및 시퀀스를 나열 합니다.  ALL_CATALOG는 USER_CATALOG와 통일한 컬럼을 가지며 사용자가 접근 가능한 것들을 나열 합니다.

USER_CATALOG에서 ADDRBOOK1 테이블을 조회 합니다.

SELECT   * FROM      USER_CATALOG  WHERE   TABLE_NAME = 'ADDRBOOK1';

<실행결과>

	TABLE_NAME	TABLE_TYPE
1	ADDRBOOK1	TABLE

실습

DESC 명령으로 생성된 테이블 구조를 확인할 수 있습니다..

DESC ADDRBOOK1;

<실행결과>

이름               널?                  유형           

-------            ----------------   --------------------- 

NAME          NOT NULL    VARCHAR2(10) 

ADDRESS                         VARCHAR2(50) 

AGE             NUMBER

실습

실무에서 테이블이 어떤 컬럼으로 구성되어 있는지를 확인하는 것은 아주 중요합니다. 개발을 하거나쿼리를 작성할 때 그걸 모르고서는 어렵습니다. 아래 3개의 쿼리 문장은 테이블의 컬럼의 구조를 확인할 수 있는 쿼리문으로 결과는 동일 합니다. 하지만 가장 편하고 간단히 사용하는 것은 위 DESC 구문 입니다.

USER_TAB_COLUMNS, USER_TAB_COLS, COLS  뷰를 통해서도 생성된 테이블 구조를 확인할 수 있습니다.

SELECT TABLE_NAME         ,  COLUMN_NAME         ,  DATA_TYPE         ,  DATA_LENGTH         ,  NULLABLE         ,  DATA_DEFAULT FROM   USER_TAB_COLUMNS WHERE TABLE_NAME = 'ADDRBOOK1'; SELECT TABLE_NAME         ,  COLUMN_NAME         ,  DATA_TYPE         ,  DATA_LENGTH         ,  NULLABLE         ,  DATA_DEFAULT FROM   USER_TAB_COLS WHERE TABLE_NAME = 'ADDRBOOK1'; SELECT TABLE_NAME         ,  COLUMN_NAME         ,  DATA_TYPE         ,  DATA_LENGTH         ,  NULLABLE         ,  DATA_DEFAULT FROM   COLS WHERE TABLE_NAME = 'ADDRBOOK1';

<실행결과>

	TABLE_NAME	COLUMN_NAME	DATA_TYPE	DATA_LENGTH	NULLABLE	DATA_DEFAULT
1	ADDRBOOK1	NAME	VARCHAR2	10	N
2	ADDRBOOK1	ADDRESS	VARCHAR2	50	Y
3	ADDRBOOK1	AGE	NUMBER	22	Y	0

USER_TAB_COLUMNS, USER_TAB_COLS, COLS 컬럼을 확인하는 뷰는 일반적인 컬럼에서는 같습니다. 반면 Virtual Column, Unued Column을 확인할 때는 조금 차이가 있습니다. 테이블에 사용 안 한다고 정의한 컬럼(Unused Column)이 있으면 해당 컬럼을 USER_TAB_COLS는 보여주지만 USER_TAB_COLUMNS, COLS는 보여주지 않습니다. 

USER_TAB_COLS는 HIDDEN_COLUMN 외에 컬럼 4개를 더 가지고 있어 더 정교한 컬럼 정보를 보여줍니다. 

COLS와 USER_TAB_COLUMNS는 실제 USER_TAB_COLUMNS 테이블과 동의어입니다. 정말로 같은지 쿼리문을 실행해 확인해보겠습니다.

SELECT SYNONYM_NAME, TABLE_OWNER, TABLE_NAME FROM    ALL_SYNONYMS WHERE SYNONYM_NAME IN ('COLS', 'USER_TAB_COLS', 'USER_TAB_COLUMNS') ORDER BY TABLE_NAME;

<실행결과>

	SYNONYM_NAME	TABLE_OWNER	TABLE_NAME
1	USER_TAB_COLS	SYS	USER_TAB_COLS
2	USER_TAB_COLUMNS	SYS	❶USER_TAB_COLUMNS
3	COLS	SYS	❷USER_TAB_COLUMNS

SYNONYMN_NAME 컬럼은 동의어를 나타내는데 ❶, ❷에서 USER_TAB_COLUMNS, COLS 두 동의어의 실제 테이블인 TABLE_NAME 컬럼의 값은 USER_TAB_COLUMNS로 통일 합니다. 즉 COLS, USER_TAB_COLUMNS는 동일한 것 입니다.

 

#테이블생성, #CREATETABLE, #ORACLE, #오라클, #오라클테이블, #오라클강좌​