Oracle

특징

1. 관계형 데이터베이스(RDBMS)
   데이터를 테이블(행과 열) 구조로 저장

   SQL(Structured Query Language)을 사용해 데이터 처리

2. 확장성과 안정성
   수십 TB 이상의 대용량 데이터도 안정적으로 처리

   클러스터링(RAC), 백업/복구 등 고가용성 기능이 강력

3. PL/SQL 지원
   Oracle 전용의 절차적 SQL 언어

   반복문, 조건문, 예외처리 등 프로그래밍 구조 제공

4. 고급 기능
   파티셔닝(partitioning): 대용량 테이블을 분할해 성능 향상

   인덱스, 뷰, 시노님, 트리거, 시퀀스 등 고급 객체 지원

   고급 보안: Transparent Data Encryption, Virtual Private Database 등

5. 멀티 유저 환경
   수많은 사용자의 동시 접속 및 처리 지원

   트랜잭션 제어(ACID 원칙 준수): 원자성, 일관성, 독립성, 지속성

• Oracle의 최적화 방법
  1. Rule-based : 내부 규칙에 근거하여 최적화한다 → 성능이 떨어져 이제 사용하지 않는다
  2. Cost-based : 통계정보를 분석하여 가장 비용이 적게 드는 방식으로 엑세스 경로를 설정한다

테이블 스페이스

데이터파일의 논리적 집합으로, 사용자 데이터나 인덱스를 저장하는 논리적 저장 단위

여러 사용자가 하나의 테이블 스페이스를 사용할 수 있으며 스키마 라는 논리적 단위로 구분하게 된다

물리적으로는 하나 이상의 파일에 블록 단위로 나뉘어 저장된다 (하나의 파일은 하나의 테이블 스페이스에만 대응된다 → 한 파일에 여러 테이블 스페이스의 데이터가 존재할 수 없음)

Oracle의 데이터 저장 단위

테이블스페이스 → 세그먼트 → 익스텐트 → 블록

블록은 논리적 저장단위이지만 물리 디스크의 최소 단위로 대응된다

Oracle Block = 디스크 I/O의 최소 단위 (보통 8KB, 설정 가능)

장점(vs. MySQL)

MySQL은 논리적 저장 구조가 부분적으로 존재하긴 하지만 사용자가 직접 제어하지 않는다

1. 유저/업무/기능 단위로 저장 공간을 논리적으로 분리 가능
   Oracle에서는 테이블스페이스를 사용자, 업무, 기능별로 나누어 설정할 수 있음
   예: HR_TS, FINANCE_TS, LOG_TS 등

   이렇게 분리하면 백업, 복구, 모니터링, I/O 분산 등에서 매우 유리함

   * MySQL은 대부분 단일 테이블스페이스(ibdata1) 또는 테이블별 .ibd 파일 수준에서만 제어 가능

2. 스토리지 관리 유연성
   테이블스페이스당 여러 데이터파일 지정 가능, 각기 다른 디스크에 저장 가능

   예: I/O 분산, 디스크 용량 한계 극복

   자동 확장(AUTOEXTEND), 최대 크기 지정 등으로 세밀한 공간 제어 가능

   * MySQL은 테이블당 파일 1개, AUTOEXTEND는 있지만 디스크 분산이나 사용자 지정 구조가 복잡함

3. 백업, 복구, 모니터링이 훨씬 정교함
   테이블스페이스 단위로 백업 및 복구가 가능 (e.g. RMAN 사용)

   특정 업무 테이블스페이스만 백업/복구할 수 있어 운영 중 부분 복구도 가능

   DBA_TABLESPACES, DBA_DATA_FILES, V$TABLESPACE 등 다양한 뷰로 모니터링

   * MySQL은 대부분 전체 DB 단위로 백업/복구 (물리적 복사 또는 mysqldump)

4. 보안 및 접근 제어 측면에서도 유리
   사용자마다 기본 테이블스페이스를 지정하면 데이터 저장 경로를 제한 가능

   접근권한도 객체 단위뿐 아니라 저장공간 수준에서도 논리적으로 분리 가능

   * MySQL은 파일 시스템 권한 외에는 이런 구분이 없음

명명 규칙

1. 테이블명과 컬럼명은 반드시 문자로 시작하고 최대 30자까지 허용한다
2. A-Z, a-z, 0-9, _, $, # 만으로 구성된다
3. 동일한 사용자가 소유한 다른 객체의 이름과 중복되지 않아야 한다
4. 예약어는 사용할 수 없다
5. 기본적으로 대소문자는 구분하지 않는다 ("MyTable" 과 같이 따옴표로 감싸서 이름으로 정의하면 구분할 수 있다)

객체별 권장 명명 관례 (Naming Convention)

객체 유형	권장 접두어 / 스타일 예시	설명
테이블	tbl_, 또는 그대로 CUSTOMER	명사형, 복수형 여부는 팀 기준
컬럼	소문자 + snake_case (first_name)	의미 명확하게 작성
PK 제약조건	pk_테이블명 (pk_customer)	Primary Key 식별자
FK 제약조건	fk_자식_부모 (fk_order_customer)	Foreign Key 식별자
인덱스	idx_테이블_컬럼 (idx_customer_name)	검색 성능용 인덱스
시퀀스	seq_테이블 (seq_invoice)	자동 증가 번호 생성기
뷰	vw_ 접두어 (vw_sales_summary)	VIEW 객체 구분용
프로시저/함수	proc_, fn_ (proc_calc_salary)	기능 명확하게 구분
패키지	pkg_ (pkg_order_mgmt)	패키지 단위 관리 시 사용

명명 스타일 가이드

규칙	예시	설명
단어는 _로 구분	user_id, created_date	snake_case 사용 권장
약어 사용 최소화	employee_name (X: emp_nm)	가능한 전체 단어 사용
일관성 유지	created_by, updated_by	공통 컬럼은 모든 테이블에 동일한 이름 사용
테이블 이름 단수형 사용	user, order	보통은 단수 사용, 팀 내 규칙에 따라 다름

---

Oracle DB 의 계정

기본 계정

계정명	용도	특징
SYS	데이터베이스의 최상위 관리자 계정	모든 권한 보유, Oracle 내부 객체 소유자 (필수 시스템 테이블 등)
SYSTEM	일반적인 DBA(관리자)용 계정	관리 작업 가능하지만 SYS보다 낮은 수준의 권한
SCOTT	예제 계정	EMP, DEPT 등 테스트 테이블 보유. 기본적으로 잠김
HR	인사(Human Resource) 관련 예제 계정	SQL 학습용으로 자주 사용됨. 기본적으로 잠김
DBSNMP	Oracle Enterprise Manager에서 사용	네트워크 모니터링 용도
OUTLN	쿼리 실행 계획 저장 용도	실행 계획 고정에 사용됨
XS$NULL	내부 시스템용	직접 사용하지 않음
APEX_PUBLIC_USER	Oracle APEX 관련 계정	APEX 웹 애플리케이션용
FLOWS_xxx	Oracle APEX 관련 계정	APEX 버전에 따라 생성되는 내부 계정
ORDDATA	Oracle Multimedia 등 기능용 계정	일반 사용자가 직접 쓸 일 없음
ORDPLUGINS	Oracle Multimedia 관련	외부 플러그인 관리용

사용자 계정 생성

CREATE USER user_name
IDENTIFIED [BY password | EXTERNALLY]
[DEFAULT TABLESPACE tablespace_name]
[TEMPORARY TABLESPACE tablespace_name]
[QUOTA {integer [K | M] | UNLIMITED} ON tablespace_name]
[PASSWORD EXPIRE]
[ACCOUNT {LOCK | UNLOCK}]
[PROFILE {profile | DEFAULT}]

• user_name : 생성할 사용자 이름
• BY password : 사용자 비밀번호
• EXTERNALLY : 사용자가 운영체제에 의해 인증되도록 지정
• DEFAULT TABLESPACE : 사용자 스키마를 위한 기본 테이블 스페이스 지정
• TEMPORARY TABLESPACE : 사용자의 임시 테이블 스페이스 지정
• QUOTA : 테이블 스페이스의 영역 할당
• PASSWORD EXPIRE : 사용자가 SQL*Plus를 사용해서 데이터베이스에 로그인할 때 암호를 재설정하도록 함
• ACCOUNT LOCK/UNLOCK : 사용자 계정을 명시적으로 잠그거나 풀 때 사용
• PROFILE : 자원 사용을 제어하고 사용자에게 사용되는 암호 제어 처리 방식을 지정

사용자 권한

시스템 권한 (System Privileges)

권한	설명
CREATE SESSION	데이터베이스에 접속할 수 있는 권한 (로그인 허용)
CREATE TABLE	테이블을 생성할 수 있음
CREATE VIEW	뷰 생성 가능
CREATE USER	새로운 사용자 계정 생성 가능
DROP USER	사용자 삭제
ALTER SYSTEM	시스템 수준 설정 변경
GRANT ANY PRIVILEGE	다른 사용자에게 어떤 권한이든 부여 가능

객체 권한 (Object Privileges)

권한	대상 객체	설명
SELECT	테이블, 뷰	데이터 조회 가능
INSERT	테이블	데이터 삽입 가능
UPDATE	테이블	데이터 수정 가능
DELETE	테이블	데이터 삭제 가능
EXECUTE	프로시저, 함수	실행 가능
REFERENCES	테이블	외래키 참조 가능

롤(ROLE)

롤 이름	포함된 권한	설명
CONNECT	CREATE SESSION 등	기본적인 접속 및 객체 생성 가능
RESOURCE	CREATE TABLE, CREATE PROCEDURE 등	개발자용 권한
DBA	모든 권한 포함	최고 관리자 역할 (슈퍼유저)

권한 부여 및 회수 예시

GRANT [privilege | role] TO [user | role | PUBLIC] [WITH ADMIN OPTION]
REVOKE [privilege | role] FROM [user | role | PUBLIC]

GRANT SELECT ON emp TO user1; -- 권한 부여
REVOKE SELECT ON emp FROM user1; -- 권한 회수
GRANT CONNECT TO user1; -- 롤 부여
REVOKE CONNECT FROM user1; -- 롤 회수

• PUBLIC : 모든 사용자에게 부여할 때 사용
• WITH ADMIN OPTION : 부여 받은 권한을 다른 사용자 또는 역할에게 부여, 변경, 삭제할 수 있게 된다.

---

Pseudo Column

Oracle 데이터베이스가 제공하는 시스템 제공 컬럼
실제 테이블에 존재하지 않지만, SELECT 문 등에서 컬럼처럼 사용할 수 있다

ROWID

테이블에 있는 각 행의 물리적인 주소를 고유하게 지정한다

해당 행이 존재하는 동안은 변하지 않는다

ROWID의 비트 단위 구성

ROWID는 10바이트(= 80비트)로 구성된다
ROWID = [ 데이터 오브젝트 번호 | 상대 파일 번호 | 블록 번호 | 행 번호 ]

필드	비트 수	설명
데이터 오브젝트 번호	32비트	행이 속한 테이블 또는 인덱스의 OBJECT ID
상대 파일 번호	10비트	테이블스페이스 내 상대적인 데이터 파일 번호
블록 번호	22비트	행이 저장된 데이터 블록의 번호
행 번호 (슬롯 번호)	16비트	해당 블록 내의 행 위치 (0부터 시작)

이 구조는 Oracle 내부에서 이진 형태로 저장되며, SQL에서는 Base64로 인코딩되어 18자리 문자열로 표시된다

ROWNUM

쿼리 결과로 반환되는 행들에 대해 테이블에서 선택된 순서대로 ROWNUM 이 지정된다

ROWNUM 은 ORDER BY 전에 지정되므로 정렬 결과는 적용되지 않는다

ROWNUM을 사용한 Top-N

SELECT *
FROM (
  SELECT *
  FROM 테이블명
  ORDER BY 정렬기준 DESC
)
WHERE ROWNUM <= N;

위 설명과 같이 정렬이 적용되지 않으므로 inner 쿼리에서 정렬한 후 outer 쿼리에서 ROWNUM 을 사용해야 한다

SELECT *
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

Oracle 12c 이상 버전에서는 FETCH FIRST 를 사용해서 Top-N을 구현할 수 있다

그 외 pseudo column

Pseudo Column	설명
LEVEL	계층형 쿼리에서 계층 깊이를 나타냄 (1부터 시작). CONNECT BY 절과 함께 사용
SYSDATE	현재 시스템의 날짜와 시간 (DATE 타입). 시간까지 포함되지만 소수초는 포함되지 않음
SYSTIMESTAMP	타임존 및 소수초까지 포함된 정밀한 시스템 시간 (TIMESTAMP WITH TIME ZONE 타입)
USER	현재 접속 중인 Oracle 사용자 이름을 반환
UID	현재 사용자의 내부 사용자 ID (NUMBER)
CURRVAL	시퀀스의 현재 값을 반환 (시퀀스명.CURRVAL). NEXTVAL 호출 이후에만 사용 가능
NEXTVAL	시퀀스의 다음 값을 반환하며, 호출 시 시퀀스가 증가됨 (시퀀스명.NEXTVAL)