Oracle 입문

ORACLE DATABASE · FOUNDATION COURSE

Oracle 입문

엔터프라이즈 데이터베이스의 표준 — 인스턴스와 데이터베이스 구조부터 SQL, 트랜잭션, 사용자·권한, 백업·복구까지
처음 Oracle을 배우는 사람과 다른 DBMS 경험자를 위한 단계별 가이드

난이도 · 입문학습기간 · 10주기준 · Oracle 19c / 23ai연계 · SQLD / OCP

STEP 1

개념 · 설치

Oracle의 특징, Free 버전 설치, SQL*Plus 접속

STEP 2

아키텍처

인스턴스(SGA·PGA·프로세스), 데이터베이스 파일, CDB/PDB

STEP 3

SQL · 트랜잭션

데이터 타입, DDL·DML, Undo·Redo, 읽기 일관성

STEP 4

운영 · 관리

사용자·권한, 기동·종료, 백업·복구, 모니터링

1. Oracle Database란

Oracle Database는 1979년 최초의 상용 SQL 기반 RDBMS로 출시된 이후, 금융·통신·공공·제조 등 대규모 기간 시스템에서 가장 널리 쓰이는 관계형 데이터베이스다. 안정성, 동시성 처리, 고가용성(RAC·Data Guard), 풍부한 관리 도구가 강점이다.

menu_book

한 줄 정의

대용량·고동시성 업무를 위한 엔터프라이즈 표준 RDBMS — 강력한 읽기 일관성과 복구 능력이 핵심

1.1 주요 특징

CONSISTENCY

읽기 일관성

Undo를 이용해 읽기는 쓰기를 막지 않고, 쓰기도 읽기를 막지 않는다. 조회는 항상 시작 시점 기준의 일관된 데이터를 본다.

AVAILABILITY

고가용성

RAC(여러 서버가 하나의 DB를 공유), Data Guard(원격 대기 DB)로 장애에도 서비스를 유지한다.

RECOVERY

강력한 복구

Redo·Archive Log와 RMAN으로 특정 시점 복구, Flashback으로 실수 데이터 되돌리기가 가능하다.

MULTITENANT

멀티테넌트

하나의 컨테이너 DB(CDB) 안에 여러 플러거블 DB(PDB)를 두고 독립적으로 관리한다 (12c+).

1.2 에디션과 버전

구분	내용
Enterprise Edition (EE)	모든 기능 + 옵션(Partitioning, RAC, Diagnostics/Tuning Pack 등은 별도 라이선스)
Standard Edition 2 (SE2)	중소 규모용, 소켓 수 제한, 일부 기능 미지원
Free (구 Express Edition)	학습·개발용 무료 버전 (CPU·메모리·저장 용량 제한)
19c	장기 지원(Long Term Release) 버전 — 현재 운영 환경에서 가장 많이 쓰인다
23ai	최신 장기 지원 버전 — JSON 관계형 이중성, 벡터 검색, BOOLEAN 타입, SELECT without FROM DUAL 등

1.3 다른 DBMS 경험자를 위한 비교

구분	Oracle	PostgreSQL	MySQL
스키마	사용자 = 스키마	DB 안의 별도 객체	데이터베이스 = 스키마
테이블 구조 (기본)	힙 테이블	힙 테이블	PK 클러스터드 (InnoDB)
MVCC 이전 버전	Undo 테이블스페이스	테이블 안 (VACUUM)	Undo 로그
기본 격리 수준	READ COMMITTED	READ COMMITTED	REPEATABLE READ
빈 문자열	'' = NULL	NULL 아님	NULL 아님
DDL	자동 커밋	롤백 가능	자동 커밋
페이징	FETCH FIRST n ROWS ONLY, ROWNUM	LIMIT	LIMIT

2. 설치와 접속

2.1 가장 빠른 방법 — Oracle Database Free (Docker)

docker run -d --name oracle-lab \
  -p 1521:1521 \
  -e ORACLE_PWD=Lab12345 \
  -v oradata:/opt/oracle/oradata \
  container-registry.oracle.com/database/free:latest

docker logs -f oracle-lab          # "DATABASE IS READY TO USE!" 메시지가 나올 때까지 대기

# 컨테이너 안에서 SYSDBA로 접속
docker exec -it oracle-lab sqlplus / as sysdba

info

기본 접속 정보 (Free 이미지)
리스너 포트 1521, CDB 서비스명 FREE, 기본 PDB 서비스명 FREEPDB1. 실습 테이블은 CDB가 아니라 PDB에 접속해서 만든다.

2.2 접속 방법

# EZConnect (호스트:포트/서비스명)
sqlplus system/Lab12345@//localhost:1521/FREEPDB1

# tnsnames.ora 별칭 사용
sqlplus scott/tiger@ORCLPDB

# $ORACLE_HOME/network/admin/tnsnames.ora
ORCLPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = orclpdb)))

info

GUI 도구
무료 공식 도구인 SQL Developer, VS Code의 Oracle SQL Developer Extension이 있고, DBeaver, Toad, Orange 등도 많이 쓴다.

2.3 SQL*Plus 필수 명령

명령	의미
CONN user/pw@서비스	다른 사용자로 재접속
SHOW USER / SHOW CON_NAME	현재 사용자 / 현재 컨테이너(CDB·PDB)
ALTER SESSION SET CONTAINER = FREEPDB1;	SYSDBA 세션에서 PDB로 전환
DESC 테이블명	테이블 구조
SET LINESIZE 200 PAGESIZE 100	출력 폭·페이지 설정
COL 컬럼명 FORMAT A30	컬럼 출력 폭 지정
SET TIMING ON / SET AUTOTRACE ON	실행 시간 / 실행계획·통계 표시
@script.sql	스크립트 실행
SPOOL result.txt … SPOOL OFF	결과를 파일로 저장
SHOW PARAMETER 이름	초기화 파라미터 확인

3. 아키텍처

3.1 인스턴스와 데이터베이스

vpn_key가장 먼저 구분할 두 개념

playlist_add_check

인스턴스(Instance) = 메모리(SGA) + 백그라운드 프로세스, 데이터베이스(Database) = 디스크의 파일(데이터 파일·컨트롤 파일·리두 로그). 인스턴스가 데이터베이스를 열어(OPEN) 사용한다. RAC에서는 여러 인스턴스가 하나의 데이터베이스를 공유한다.

 ┌──────────────────────────── 인스턴스 ─────────────────────────────┐
 │  SGA (공유 메모리)                                                 │
 │  ┌──────────────┐ ┌────────────────────┐ ┌──────────────┐        │
 │  │ Shared Pool  │ │ Database Buffer    │ │ Redo Log     │        │
 │  │ (SQL·딕셔너리)│ │ Cache (데이터 블록) │ │ Buffer       │        │
 │  └──────────────┘ └────────────────────┘ └──────────────┘        │
 │  백그라운드 프로세스: PMON · SMON · DBWn · LGWR · CKPT · ARCn · MMON  │
 └───────────────────────────────────────────────────────────────────┘
   ▲ 서버 프로세스 (접속마다, PGA: 정렬·해시 작업 영역)
   │
 ┌──────────────────────────── 데이터베이스 (파일) ──────────────────┐
 │  Data Files (.dbf) · Control Files (.ctl) · Online Redo Log Files │
 │  (부가) Parameter File(spfile) · Password File · Archive Log Files│
 └───────────────────────────────────────────────────────────────────┘

3.2 주요 백그라운드 프로세스

DBWn
변경 블록을 데이터 파일에 기록

LGWR
리두 버퍼를 리두 로그에 기록

CKPT
체크포인트 정보 갱신

SMON
인스턴스 복구

PMON
실패 프로세스 정리

ARCn
리두 로그 아카이브

3.3 멀티테넌트 (CDB / PDB)

 CDB (컨테이너 데이터베이스)
  ├─ CDB$ROOT   ← 오라클 메타데이터, 공통 사용자(C##)
  ├─ PDB$SEED   ← 새 PDB를 만들 때 쓰는 템플릿 (읽기 전용)
  ├─ PDB1       ← 업무 DB (로컬 사용자·테이블)
  └─ PDB2

• 업무 사용자와 테이블은 PDB 안에 만든다. 21c부터는 멀티테넌트 구조만 지원한다.

3.4 논리 저장 구조

테이블스페이스
데이터 파일의 논리 묶음

세그먼트
테이블·인덱스 하나

익스텐트
연속된 블록 묶음

블록
I/O 최소 단위 (보통 8KB)

테이블스페이스	용도
SYSTEM / SYSAUX	데이터 딕셔너리, AWR 등 오라클 내부용
UNDOTBS1	변경 전 이미지 (롤백·읽기 일관성)
TEMP	정렬·해시 임시 작업 공간
USERS 및 업무용	사용자 테이블·인덱스

4. 기본 SQL과 데이터 타입

4.1 주요 데이터 타입

용도	타입	메모
가변 문자	VARCHAR2(n)	최대 4000바이트 (확장 시 32767), n CHAR로 문자 수 지정 가능
숫자	NUMBER(p,s)	정수·소수 모두. NUMBER(10)은 정수
날짜	DATE	시·분·초까지 포함
일시	TIMESTAMP, TIMESTAMP WITH TIME ZONE	소수 초, 시간대
대용량	CLOB, BLOB	LONG은 사용하지 않음
반정형	JSON (21c+) / CLOB IS JSON (19c)
참/거짓	BOOLEAN (23ai+)	이전 버전은 CHAR(1) + CHECK

priority_high

중요

Oracle은 빈 문자열('')을 NULL로 취급한다. WHERE col = ''는 항상 거짓이므로 WHERE col IS NULL로 써야 한다.

4.2 테이블 만들기

CREATE TABLE customer (
    cust_id     NUMBER GENERATED ALWAYS AS IDENTITY,    -- 12c+ (이전에는 SEQUENCE + 트리거)
    cust_nm     VARCHAR2(100 CHAR) NOT NULL,
    email       VARCHAR2(200),
    grade_cd    VARCHAR2(10) DEFAULT 'NORMAL' NOT NULL,
    created_at  DATE DEFAULT SYSDATE NOT NULL,
    CONSTRAINT pk_customer PRIMARY KEY (cust_id),
    CONSTRAINT uk_customer_email UNIQUE (email)
);

CREATE TABLE orders (
    order_id    NUMBER GENERATED ALWAYS AS IDENTITY,
    cust_id     NUMBER NOT NULL,
    order_dt    DATE   NOT NULL,
    amount      NUMBER(12,2) NOT NULL,
    CONSTRAINT pk_orders PRIMARY KEY (order_id),
    CONSTRAINT fk_orders_cust FOREIGN KEY (cust_id) REFERENCES customer (cust_id),
    CONSTRAINT ck_orders_amt CHECK (amount >= 0)
);
CREATE INDEX ix_orders_01 ON orders (cust_id, order_dt);   -- FK 컬럼 인덱스는 직접 만든다

COMMENT ON TABLE customer IS '고객';

4.3 자주 쓰는 SQL

-- DUAL: 테이블 없이 식 계산 (23ai부터는 FROM DUAL 생략 가능)
SELECT SYSDATE, USER FROM DUAL;

-- 시퀀스
CREATE SEQUENCE sq_order_no START WITH 1 INCREMENT BY 1 CACHE 100;
SELECT sq_order_no.NEXTVAL FROM DUAL;

-- MERGE (UPSERT)
MERGE INTO customer t
USING (SELECT 'hong@example.com' AS email, '홍길동' AS cust_nm FROM DUAL) s
   ON (t.email = s.email)
 WHEN MATCHED THEN UPDATE SET t.cust_nm = s.cust_nm
 WHEN NOT MATCHED THEN INSERT (cust_nm, email) VALUES (s.cust_nm, s.email);

-- 페이징 (12c+)
SELECT * FROM orders ORDER BY order_dt DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

-- 계층 조회
SELECT LEVEL, emp_name FROM emp
 START WITH mgr_id IS NULL
CONNECT BY PRIOR emp_id = mgr_id;

-- 자주 쓰는 함수
SELECT NVL(email, '-'), DECODE(grade_cd, 'VIP', '우수', '일반'),
       TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS')
  FROM customer;

5. 트랜잭션, Undo와 Redo

구분	Undo	Redo
기록 내용	변경 전 데이터	변경 내역 (변경 후 재현 정보)
목적	롤백, 읽기 일관성, Flashback	장애 복구 (인스턴스·미디어 복구)
저장 위치	Undo 테이블스페이스	Online Redo Log → (아카이브 모드) Archive Log
관련 오류	ORA-01555 snapshot too old	리두 로그 스위치 대기, 아카이브 공간 부족

menu_book

COMMIT 시점에는 데이터 파일이 아니라 리두 로그만 디스크에 기록된다 (LGWR). 데이터 블록은 나중에 DBWn이 기록한다. 그래서 커밋은 빠르고, 장애가 나도 리두로 복구할 수 있다.

-- DML은 명시적으로 COMMIT / ROLLBACK
UPDATE orders SET amount = amount - 1000 WHERE order_id = 10;
SAVEPOINT sp1;
DELETE FROM orders WHERE order_id = 11;
ROLLBACK TO sp1;      -- DELETE만 취소
COMMIT;

-- 주의: DDL(CREATE, ALTER, TRUNCATE 등)은 실행 전후로 자동 COMMIT 된다

priority_high

중요

DELETE는 Undo를 남기고 롤백할 수 있지만, TRUNCATE는 DDL이라 롤백할 수 없다. 운영 데이터 삭제 전에는 반드시 백업 또는 조건을 다시 확인한다.

5.1 Flashback — 실수 되돌리기

-- 10분 전 데이터 조회 (Undo 보존 범위 안에서)
SELECT * FROM orders AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE)
 WHERE order_id = 10;

-- 실수로 DROP한 테이블 복원 (휴지통)
FLASHBACK TABLE orders TO BEFORE DROP;

6. 데이터 딕셔너리와 동적 성능 뷰

접두어	범위	예
USER_	내가 소유한 객체	USER_TABLES, USER_INDEXES, USER_CONSTRAINTS
ALL_	내가 접근 가능한 객체	ALL_TAB_COLUMNS
DBA_	DB 전체 (DBA 권한)	DBA_SEGMENTS, DBA_DATA_FILES, DBA_USERS
CDB_	모든 컨테이너	CDB_TABLES
V$	인스턴스 실시간 상태 (메모리)	V$SESSION, V$SQL, V$INSTANCE, V$DATABASE
GV$	RAC 전체 인스턴스	GV$SESSION

7. 인덱스와 실행계획 맛보기

• 기본 인덱스는 B*Tree — 조건 컬럼, 조인 컬럼, 정렬 컬럼에 만든다.
• 결합 인덱스는 = 조건 컬럼을 앞에, 범위 조건 컬럼을 뒤에 둔다.
• 인덱스 컬럼을 가공하면(TO_CHAR(col), col + 1) 인덱스를 쓰지 못한다.

SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM orders WHERE cust_id = 1001;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

-- 옵티마이저 통계 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');

arrow_forward다음 단계

실행계획과 인덱스·조인 튜닝은 SQL 튜닝 입문 → 실행계획 읽기 실습 → 인덱스 튜닝 심화에서 자세히 다룬다.

8. 사용자·권한

-- PDB에 접속한 상태에서
CREATE USER app_user IDENTIFIED BY "Change_me1"
  DEFAULT TABLESPACE users QUOTA 1G ON users;

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE TO app_user;   -- 시스템 권한

CREATE ROLE app_read;
GRANT SELECT ON app_user.orders TO app_read;                                     -- 객체 권한
GRANT app_read TO report_user;

-- 권한 확인
SELECT * FROM DBA_SYS_PRIVS WHERE grantee = 'APP_USER';
SELECT * FROM DBA_TAB_PRIVS WHERE grantee = 'APP_READ';

info

스키마 = 사용자
Oracle에서는 사용자를 만들면 같은 이름의 스키마가 생긴다. 다른 스키마의 테이블은 app_user.orders처럼 소유자를 붙여 접근하거나 동의어(SYNONYM)를 만든다.

9. 기동과 종료

SHUTDOWN
인스턴스 없음

NOMOUNT
파라미터 파일 읽고 인스턴스 시작

MOUNT
컨트롤 파일 열기

OPEN
데이터 파일·리두 로그 열기

$> sqlplus / as sysdba;
-- SYSDBA로
STARTUP;                         -- NOMOUNT → MOUNT → OPEN
SHUTDOWN IMMEDIATE;              -- 진행 중 트랜잭션 롤백 후 정상 종료 (가장 많이 사용)

ALTER PLUGGABLE DATABASE ALL OPEN;               -- 모든 PDB 열기
ALTER PLUGGABLE DATABASE ALL SAVE STATE;         -- 재기동 시 자동으로 열리도록 저장

SELECT instance_name, status FROM v$instance;
SELECT name, open_mode, log_mode FROM v$database;

info

Alert Log
기동·종료, 오류(ORA-), 로그 스위치 등 DB의 중요한 사건이 기록되는 파일이다. 위치는 SELECT value FROM v$diag_info WHERE name = 'Diag Trace';로 확인한다. 장애가 나면 가장 먼저 본다.

10. 백업과 복구

방식	도구	특징
물리 백업	RMAN (Recovery Manager)	온라인 백업, 증분 백업, 블록 손상 검사, 시점 복구 — 운영 표준
논리 백업	Data Pump (expdp / impdp)	스키마·테이블 단위 이관, 버전 간 이동
아카이브 로그 모드	ARCHIVELOG	온라인 백업과 시점 복구의 전제 조건
실수 복구	Flashback Query / Table / Drop	Undo·휴지통으로 빠르게 되돌리기
재해 대비	Data Guard	원격 대기 DB로 리두 전송

# RMAN 전체 백업 (아카이브 로그 포함)
rman target /
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> LIST BACKUP SUMMARY;

# Data Pump 스키마 내보내기 / 가져오기
expdp system@FREEPDB1 schemas=APP_USER directory=DATA_PUMP_DIR dumpfile=app.dmp logfile=app_exp.log
impdp system@FREEPDB1 schemas=APP_USER directory=DATA_PUMP_DIR dumpfile=app.dmp remap_schema=APP_USER:APP_TEST

11. 모니터링 기본 쿼리

-- 활성 세션과 대기 이벤트
SELECT sid, serial#, username, status, sql_id, event, seconds_in_wait
  FROM v$session
 WHERE type = 'USER' AND status = 'ACTIVE';

-- 락 대기 (블로킹 세션)
SELECT sid, blocking_session, event, sql_id
  FROM v$session
 WHERE blocking_session IS NOT NULL;

-- 테이블스페이스 사용률
SELECT tablespace_name, ROUND(used_percent, 1) AS used_pct
  FROM dba_tablespace_usage_metrics
 ORDER BY used_percent DESC;

-- 크기가 큰 세그먼트
SELECT owner, segment_name, segment_type, ROUND(bytes/1024/1024) AS mb
  FROM dba_segments
 ORDER BY bytes DESC FETCH FIRST 10 ROWS ONLY;

12. 학습 커리큘럼 (10주)

설치 → 아키텍처 → SQL → 트랜잭션 → 운영 ·한 주에 이론 3시간 + 실습 3시간SQL 기초가 약하다면 4주차에 시간을 더 쓰세요.

주차	단계	주제	세부 내용	실습
1	기초	Oracle 소개와 설치	역사·에디션·버전, Free 설치, SQL*Plus·SQL Developer, 접속 방식	실습 DB 구축, PDB 접속
2		아키텍처 ①	인스턴스와 데이터베이스, SGA·PGA, 백그라운드 프로세스	V$ 뷰로 구조 확인
3	구조	아키텍처 ②	CDB/PDB, 테이블스페이스·세그먼트·익스텐트·블록, 데이터 딕셔너리	테이블스페이스·사용자 생성
4		데이터 타입과 SQL	VARCHAR2·NUMBER·DATE, 제약조건, IDENTITY·시퀀스, 함수, MERGE	쇼핑몰 테이블 설계 (데이터 모델링 입문 연계)
5	트랜잭션	트랜잭션과 읽기 일관성	COMMIT·ROLLBACK, Undo·Redo, 락, ORA-01555	두 세션으로 락·일관성 재현
6		Flashback과 휴지통	Flashback Query·Table·Drop, Undo 보존	실수 삭제 복구
7	성능 기초	인덱스	B*Tree 구조, 결합 인덱스, 인덱스를 못 쓰는 경우	인덱스 유무 비교
8		실행계획과 통계	DBMS_XPLAN, 옵티마이저 통계 (SQL 튜닝 입문 연계)	실행계획 읽기
9	운영	사용자·권한·기동	시스템·객체 권한, 롤, 프로파일, STARTUP·SHUTDOWN, Alert Log	읽기 전용 계정 설계
10		백업과 복구	ARCHIVELOG, RMAN 백업·복구, Data Pump	RMAN 백업, 스키마 이관

13. 입문자 체크리스트

SELF CHECK

이 정도면 입문 완료

□ SQL*Plus로 PDB에 접속하고 현재 사용자·컨테이너를 확인할 수 있다
□ 인스턴스와 데이터베이스, SGA와 PGA의 차이를 설명할 수 있다
□ 테이블스페이스 → 세그먼트 → 익스텐트 → 블록 구조를 설명할 수 있다
□ Undo와 Redo의 역할 차이, DDL 자동 커밋을 설명할 수 있다
□ 사용자를 만들고 시스템·객체 권한을 부여할 수 있다
□ RMAN 백업과 Data Pump 이관을 한 번씩 해 보았다

14. 용어집

용어	설명
인스턴스 (Instance)	SGA + 백그라운드 프로세스
SGA	모든 세션이 공유하는 메모리 (버퍼 캐시, 공유 풀, 리두 버퍼)
PGA	서버 프로세스 전용 메모리 (정렬·해시 작업)
CDB / PDB	컨테이너 DB / 플러거블 DB
테이블스페이스	데이터 파일을 묶는 논리 저장 단위
세그먼트	저장 공간을 차지하는 객체 하나 (테이블, 인덱스)
Undo	변경 전 데이터 (롤백·읽기 일관성)
Redo	변경 내역 로그 (장애 복구)
SCN	시스템 변경 번호, DB의 논리적 시각
ARCHIVELOG	리두 로그를 보관하는 운영 모드
RMAN	Oracle 공식 백업·복구 도구
Data Pump	논리 내보내기·가져오기 도구 (expdp/impdp)
리스너 (Listener)	클라이언트 접속 요청을 받아 서버 프로세스로 연결하는 프로세스

15. 참고 자료

• Oracle Database 19c Documentation — https://docs.oracle.com/en/database/oracle/oracle-database/19/
• Oracle Database Concepts (19c) — 아키텍처 학습의 기본서
• Oracle Database Free — https://www.oracle.com/database/free/
• Oracle Live SQL — 설치 없이 브라우저에서 SQL 실습

arrow_back이전 글데이터 모델링 입문

arrow_forward다음 글SQL 튜닝 입문