TABLE

• TABLE
  • 데이터 형
  • 오라클의 데이터형
  • DATE VS VARCHAR2
  • 12c NF
  • LOB
  • ROWID
  • 서브쿼리를 이용하여 테이블 생성하기
  • 테이블의 구조만 복사하기
  • 테이블 구조 변경하는 ALTER TABLE
  • 새로운 컬럼 추가하기
  • 기존 컬럼 속정 변경하기
  • 기존 컬럼 삭제
  • SET UNUSED 옵션 적용하기
  • 테이블 구조 삭제하는 DROP TABLE
  • 테이블의 모든 로우를 제거하는 TRUNCATE
  • 테이블 명을 변경하는 RENAME
  • 데이터 딕셔너리와 데이터 딕셔너리 뷰
  • USER_데이터 딕셔너리
  • ALL_데이터 딕셔너리
  • DBA_데이터 딕셔너리 뷰

데이터 형

이름	비고
CHAR(size)	고정 길이 문자 데이터. VARCHER2와 동일한 형태의 자료를 저장할 수 있고, 입력된 자료의 길이와는 상광없이 정해진 길이만큼 저장 영역 차지. 최소 크기는 1
VARCHAR2(size)	Up to 2000 Bytes 가변 길이 문자 데이터. 실제 입력된 문자열의 길이 만큼 저장 영역을 차지. 최대 크기는 명시해야 하며, 최소 크기는 1
NUMBER	Internal Number Format 최고 40자리까지 숫자를 저장할 수 있다. 이때 소수점이나 부호는 길이에 포함되지 않는다.
NUMBER(w)	W 자리까지의 수치로 최대 38자리까지 가능하다.(38자리가 유효 숫자이다.)
NUMBER(w, d)	W는 전체 길이, d는 소수점 이하 자릿수이다. 소수점은 자릿수에 포함되지 않는다.
DATA	BC 4712년 1월 1일 ~ AD 4712년 12월 31일까지의 날짜.
LONG	가변 길이의 문자형 데이터 타입, 최대 크기는 2GB
LOB	2GB까지의 가변 길이 바이너리 데이터를 저장시킬 수 있다. 이미지 문서, 실행 파일을 저장할 수 있다.
ROWID	ROWID는 Tree-piece Format을 갖는다. ROWID는 DB에 저장되어 있지 않으며, DB Data도 아니다.
BFILE	대용량의 바이너리 데이터를 파일 형태로 저장 최대 4GB

오라클의 데이터형

• NUMBER
  • NUMBER 데이터 형은 숫자 데이터를 저장하기 위해서 제공된다.
  • precision 은 소수점을 포함한 전체 자리수를 의미하며 scale 은 소수점 이하 자리수를 지정한다.
  • scale 을 생략한 채 precision 만 지정하면 소수점 이하는 반올림되어 정수 값만 저장된다.
  • precision 과 scale 을 모두 생략하면 입력한 데이터 값만큼 공간이 할당된다.
  • NUMBER(precision, scale)

데이터	TYPE	저장값
7456123.89	NUMBER	7456123.89
7456123.89	NUMBER(9)	7456124
7456123.89	NUMBER(9, 2)	7456123.89
7456123.89	NUMBER(9, 1)	7456123.9
7456123.89	NUMBER(6)	exceed precision
7456123.89	NUMBER(7, -2)	7456100
7456123.89	NUMBER(7, 2)	exceed precision

• DATE
  • DATE는 세기, 년, 월, 일, 시간, 분, 초의 날짜 및 시간 데이터를 저장하기 위한 데이터 형이다.
  • 이렇듯 날짜 타입 안에는 세기, 년, 월, 일, 시간, 분, 초, 요일 등 여러 가지 정보가 들어 있지만 별다른 설정이 없으면 년, 월 ,일만 출력한다.
  • 기본 날짜 형식은 “YY/MM/DD”형식으로 “년/월/일”로 출력된다.
  • 2005년 12월 14일은 “05/12/14”로 출력된다.
• CHAR
  • 문자 데이털를 저장하기 위한 자료형으로 CHAR가 있다. CHAR는 고정 길이 문자 데이터를 저장한다.
  • 입력된 자료의 길이와는 상관없이 정해진 길이만큼 저장 영역을 차지하며 최소 크기는 1이다.
  • CHAR는 주어진 크기만큼 저장공간이 할당되므로 편차가 심한 데이터를 입력할 경우 저장공간의 낭비를 초래한다.
  • 단점
    • 변수를 사용하면 조회가 되지 않는다.
    • VARCHAR2 형 컬럼과 조인에 실패한다.
    • VARCHAR2 가 SIZE, 성능이 더 좋다.

DATE VS VARCHAR2

• 일자 일럼에 DATE를 사용할 것인가? 아니면 VARCHAR2를 사용할 것인가?
  • SIZE 관점 : DATE WIN
  • 성능 관점 : DATE WIN
  • 데이터 품질 관점 : DATE WIN
  • 프로그래밍 편의성 : DATE WIN
  • DATE 형을 쓰면 시분초가 들어가기 때문에 조건으로 조회가 불가능 하다.
    • 저장 할때 시분초를 잘라 낸다
      • trunc(sysdate)

12c NF

• Increased Size Limit
  • PL/SQL 에서 사용하던 것과 같이 32K 까지 사용가능
  • Compatible = 12.0.0.0 이나 그 이상
  • MAX_STRING_SIZE Init parameter
  • ALTER SYSTEM set MAX_STRING_SIZE = EXTENDED scope = SPFILE
  • Clustered 나 Index Organized Table 에서는 사용 불가
  • 테이블 데이터와는 별도로 저장되지만 사용 시에는 Long String 으로 바로 사용 가능

LOB

• LOB(Large Object) 데이터 형은 텍스트, 그래픽 이미지, 동영상, 사운드와 같이 구조화되지 않은 대용량의 텍스트나 멀티미디어 데이터를 저장 하기 위한 데이터 형이다.
• 최대 4GB 까지 저장 가능하다. 오라클에서 제고되는 LOB 데이터 형은 BLOB,CLOB,NCLOB,BFILE 등이 있다.
• BLOB는 그래픽 이미지, 동영상, 사운드와 같은 구조화되지 않은 데이터를 저장하기 위해 사용된다.
• CLOB는 e-BOOK과 같은 대용량의 텍스트 데이터를 저장하기 위해서 사용된다.
• NCLOB는 국가별 문자셋 데이터를 저장하고, BFILE는 바이너리 데이터를 파일 형태로 저장한다.

ROWID

• ROWID 데이터 형은 테이블에서 행의 위치를 지정하는 논리적인 주소값이다.
• ROWID는 데이터베이스 전체에서 중복되지 않는 유일한 값으로 테이블에 새로운 행이 삽입되면 테이블 내부에서 의사 컬럼 형태로 자동으로 생성된다.
• ROWID는 테이블의 특정 레코드를 랜덤하게 접근하기 위해서 주로 사용된다.
• ROWID는 다음고 같은 형식으로 데이터를 저장한다.
  • 
• 데이터 객체번호는 테이블이나 인덱스와 같은 데이터 객체가 생성될 때 할당된다.
• 상대적인 파일번호는 데이터가 저장되는 물리적인 데이터 파일 번호로서 유일한 값을 가진다.
• 블럭번호 데이터 파일 내에서 행을 포함한 블록 위치이다.
• 행 번호는 블록 내에서 행 위치를 나타내는 번호이다.

서브쿼리를 이용하여 테이블 생성하기

• CREATE TABLE 문에서 서브쿼리를 사용하여 이미 존재하는 테이블과 동일한 구조와 내용을 갖는 새로운 테이블을 생성할 수 있다.
• CREATE TABLE 명령어 다음에 컬럼을 일일이 정의하는 대신 AS 절을 추가하여 EMP 테이블고 동일한 내용과 구조를 갖는 새로운 테이블을 생성할 수 있다.

CREATE TABLE EMP02 
AS
SELECT * FROM EMP;

• 기존 테이블에서 원하는 컬럼만 선택적으로 복사해서 생성할 수도 있다.
• 서브쿼리문의 SELECT 절에 * 대신 원하는 컬럼명을 명시하면 기존 테이블에서 일부 컬럼만 복사할 수 있다.

CREATE TABLE EMP03
AS
SELECT ENAME, SAL FROM EMP;

• 기존 테이블에서 원하는 행만 선택적으로 복사해서 생성할 수도 있다.
• 서브쿼리문의 SELECT 문을 구성할 때 WHERE 절을 추가하여 원하는 조건을 제시하면 기존 테이블에서 일부의 행만 복사한다.

CREATE TABLE EMP04
AS
SELECT * FROM EMP
WHERE DEPTNO = 10;

테이블의 구조만 복사하기

• 서브 쿼리를 이용하여 테이블을 복사하되 데이터는 복사하지 않고 기존 테이블의 구조만 복사하는 것을 살펴보자.
• 테이블의 구조만 복사하는 것은 별도의 명령이 있는 것이 아니다. 이 역시 서브쿼리를 이용해야 되는데 WHERE 조건 절에 항상 거짓이 되는 조건을 지정하게 되면 테이블에서 얻어질 수 있는 로우가 없게 되므로 빈 테이블이 생성되게 된다.

CREATE TABLE EMP05
AS
SELECT * FROM EMP
WHERE 1 = 0;

테이블 구조 변경하는 ALTER TABLE

• ALTER TABLE 명령문은 기존 테이블의 구조를 변경하기 위한 DDL 명령문이다. 테이블에 대한 주고 변경은 컬럼의 추가, 삭제, 컬럼의 타입이나 길이를 변경할 때 사용한다. 테이블의 구조를 변경하게되면 기존에 저장되어 있던 데이터에 영향을 주게 된다.
• ALTER TABLE로 컬러 추가, 삭제, 수정 하기 위해서는 다음과 같은 명령어를 사용한다.
  • ADD COLUMN : 컬럼 추가
  • DROP COLUMN : 컬럼 삭제
  • MODIFY COLUMN : 컬럼 수정

새로운 컬럼 추가하기

• ALTER TABLE ADD 문은 기존 테이블에 새로운 컬럼을 추가한다.
• 새로운 컬럼은 테이블 맨 마지막에 추가되므로 자신이 원하는 위치에 만들어 넣을 수 없다.
• 또한 이미 이전에 추가해 놓은 로우가 존재한다면 그 로우에도 컬럼이 추가되지만, 컬럼 값은 NULL 값으로 입력된다.
• ALTER TABLE table_name ADD (column_name, data_type expr, …)

기존 컬럼 속정 변경하기

• ALTER TABLE MODIFY 문을 다음과 같은 형식으로 사용하면 테이블에 이미 존재하는 컬럼을 변경할 수 있다.
  • ALTER TABLE table_name MODIFY (column_name data_type expr, …)
• 컬럼을 변경한다는 것은 컬럼에 대해서 데이터 타입이나 크기, 기본 값들을 변경한다는 의미이다.

기존 컬럼 삭제

• ALTER TABLE DROP COLUMN 문은 기존 테이블에 존재하는 컬럼을 삭제한다.

SET UNUSED 옵션 적용하기

• 특정 테이블에서 컬럼을 삭제하는 경우 다음과 같이 무조건 삭제하는 것은 위험하다.
• 테이블에 저장된 내용이 많을 경우(몇 만 건에 대한 자료) 해당 테이블에서 컬럼을 삭제하는 데 꽤 오랜 시간이 걸리게 될 것이다. 컬럼을 삭제하는 동안 다른 사용자가 해당 컬럼을 사용하려고 접근하게 되면 지금 현재 테이블이 사용되고 있기 때문에 다른 사용자는 해당 테이블을 이용할 수 없게 된다. 이런 경우 작업이 원할하게 진행되지 않고 락(lock)이 발생하게 된다.
• ALTER TABLE 에 SET UNUSED 옵션을 지정하면 컬럼을 삭제하는 것은 아니지만 컬럼의 사용을 논리적으로 제한할 수 있게 된다.
• SET UNUSED 옵션은 사용을 논리적으로 제한할 뿐 실제로 컬럼을 삭제하지 않기 때문에 작업 시간이 오래 걸지 않는다. 그렇게 때문에 락이 걸리는 일도 일어나지 않게 된다.
• 논리적으로 제한 하기

ALTER TABLE EMP02 SET UNUSED(JOB);

• 가장 사용빈도가 적은 시간에 실제적인 삭제 작업을 진행하기

ALTER TABLE EMP02 DROP UNUSED COLUMNS;

테이블 구조 삭제하는 DROP TABLE

• DROP TABLE 문은 기존 테이블을 제거한다.

DROP TABLE EMP02;

테이블의 모든 로우를 제거하는 TRUNCATE

• 기존에 사용하던 테이블의 모든 로우를 제거하기 위한 명령어로 TRUNCATE가 제공된다.

TRUNCATE TABLE EMP02;

테이블 명을 변경하는 RENAME

• 기존에 사용하던 테이블의 이름을 변경하기 위한 명령어로 RENAME가 제공된다.

RENAME EMP02 TO TEST;

데이터 딕셔너리와 데이터 딕셔너리 뷰

• 데이터베이스 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블을 데이터 딕셔너리라고 한다.
• 데이터 딕셔너리는 사용자가 테이블을 생성하거나 사용자를 변경하는 등의 작업을 할 때 데이터베이스 서버에 의해 자동으로 갱신되는 테이블로 사용자는 데이터 딕셔너리의 내용을 직접 수정하거나 삭제 할 수 없다.
• 이런한 데이터 딕셔너리를 사용자가 조회해 보면 시스템이 직접 관리하는 테이블이기에 암호 같은 기호만 보여질 뿐 내용을 알 수가 없다.
• 데이터 딕셔너리 원 테이블은 직접 조회하기란 거의 불가능한 일이다.
• 의미 있는 자료 조히가 불가능하기에 오라클은 사용자가 이해할 수 있는 데이터를 산출해 줄 수 있도록 하기 위해서 데이터 딕셔너리에서 파생한 데이터딕셔너리 뷰를 제공한다.
• 데이터딕셔너리뷰는 접두어 따라 다음의 세 종류가 있습니다.

접두어	의미
DBA_XXXX	데이터베이스 관리자만 접근 가능한 객체 등의 정보 조회(DBA는 모두 접근 가능하므로 결국 디비에 있는 모든 객체에 관한 조회)
ALL_XXXX	자신 계정 소유 또는 권한을 부여 받는 객체 등에 관한 정보 조회
USER_XXXX	자신의 계정이 소유한 객체 등에 관한 정보 조회
CDB_XXXX	12c New Feature

USER_데이터 딕셔너리

• 접두어로 USER가 붙은 데이터 딕셔너리는 자신의 계정이 소유한 객체등에 관한 정보를 조회한다.
• USER가 붙은 데이터 딕셔너리 중에서 자신이 생성한 테이블이나 인덱스나 뷰 등과 같은 자신 계정이 소유의 객체 정보를 저장한 USER_TABLES 데이터 딕셔너리를 사용할 수 있다.
• DESC 명령엉로 데이터 딕셔너리 뷰 USER_TABLES 의 구조를 살펴 볼 수 있다.
• 데이터 딕셔너리 USER_TABLES의 구조를 살펴보면 무수히 많은 컬럼으로 구성되었음을 알 수 있다. 이중에서 테이블의 이름을 알려주는 TABLE_NAME 컬럼의 내용을 살펴 보면 현재 사용자 계정이 SCOTT 이므로 SCOTT이 사용가능한 테이블의 이름만 알 수 있다.

SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = 'EMP';

ALL_데이터 딕셔너리

• 사용자 계정이 소유한 객체는 자신이 소유이므로 당연히 접금이 가능하다.
• 그러나 만일 자신의 계정이 아닌 다른 계정 소유의 테이블이나 시쿼스등은?
• 오라클에서는 타계정의 객체는 원천적으로 접근이 불가능하다.
• 하지만 객체의 소유자가 접근할 수 있도록 권한을 부여하면 타 계정의 객체에도 접근이 가능하다.
• ALL_ 데이터 딕셔너리 뷰는 현재 계정이 접근 가능한 객체, 즉 자신 계정의 소유이거나 접근 권한을 부여 받은 타계정의 객체 등을 조회 할 수 잇는 데이터 딕셔너리 뷰이다.
• 현재 계정이 접근 가능한 테이블의 정보를 조회하는 뷰이다.

DBA_데이터 딕셔너리 뷰

• DBA_ 데이터 딕셔너리는 DBA가 접근 가능한 객체 들을 조회 할 수 있는 뷰이다.
• 앞서도 언급했지만 DBA가 접근 불가능한 정보는 없기에 데이터베이스에 있는 모든 객체 등의 의미라 할 수 있다.
• USER_ 와 ALL_ 와 달리 DBA_ 데이터딕셔너리뷰는 DBA 시스템 권한을 가진 사용자만 접근할 수 있다.