🔴 Oracle 완전 가이드

-- 테이블스페이스 생성
CREATE TABLESPACE ts_app_data
DATAFILE '/u01/app/oracle/oradata/XE/ts_app_data.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G;

-- 사용자 생성 및 권한 부여
CREATE USER app_user IDENTIFIED BY password123
DEFAULT TABLESPACE ts_app_data;

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO app_user;
ALTER USER app_user QUOTA UNLIMITED ON ts_app_data;

-- 1. 딕셔너리 뷰를 통한 내 권한 및 롤 확인
SELECT * FROM USER_SYS_PRIVS; -- 나에게 부여된 시스템 권한
SELECT * FROM USER_TAB_PRIVS; -- 나에게 부여된 오브젝트 권한
SELECT * FROM USER_ROLE_PRIVS; -- 나에게 부여된 롤 정보

-- 2. 사전 정의된 주요 롤(Predefined Roles) 부여
-- CONNECT: 데이터베이스 접속 권한 (CREATE SESSION)
-- RESOURCE: 기본적인 객체 생성 권한 (CREATE TABLE, SEQUENCE 등)
-- DBA: 데이터베이스 모든 관리 권한
GRANT CONNECT, RESOURCE TO app_user;

-- 3. 'ANY' 권한의 리스크와 작동 방식
-- ANY 키워드가 붙은 권한은 소유자에 상관없이 모든 객체에 명령을 실행할 수 있어 극도로 위험합니다.
-- 예: SELECT ANY TABLE은 SYS, SYSTEM 등 관리자 스키마를 제외한 모든 사용자의 테이블을 조회할 수 있습니다.
GRANT SELECT ANY TABLE TO app_user;

-- 4. ANY 권한 및 롤 회수
REVOKE SELECT ANY TABLE FROM app_user;

-- 1. 데이터베이스 캐릭터셋 및 파라미터 확인
SELECT * FROM NLS_DATABASE_PARAMETERS 
WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET', 'NLS_LENGTH_SEMANTICS');
-- AL32UTF8(유니코드 한글 3바이트) 또는 KO16MS949(완성형 한글 2바이트)가 주로 사용됩니다.

-- 2. 세션 및 클라이언트 캐릭터셋 상태 확인
SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';

-- 3. BYTE 세맨틱스 vs CHAR 세맨틱스 차이와 적용
-- BYTE 세맨틱스(기본값): VARCHAR2(10 BYTE) -> AL32UTF8 환경에서 한글은 최대 3글자까지만 저장 가능
CREATE TABLE emp_byte (
    emp_name VARCHAR2(10 BYTE)
);

-- CHAR 세맨틱스: VARCHAR2(10 CHAR) -> 바이트 수에 상관없이 10글자 저장 가능 (한글 10자 저장)
CREATE TABLE emp_char (
    emp_name VARCHAR2(10 CHAR)
);

-- 4. 문자열의 바이트 길이와 글자 수 확인 비교
SELECT 
    LENGTH('한글에이전트') AS char_len,     -- 글자 수 (6글자)
    LENGTHB('한글에이전트') AS byte_len   -- 실제 디스크 바이트 수 (AL32UTF8 기준 6*3=18 Bytes, KO16MS949 기준 6*2=12 Bytes)
FROM DUAL;

-- 프로시저 생성 예시 (사원 급여 인상)
CREATE OR REPLACE PROCEDURE raise_salary (
    p_emp_id IN NUMBER,
    p_rate   IN NUMBER
) AS
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_rate)
    WHERE emp_id = p_emp_id;
    
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('사원을 찾을 수 없습니다.');
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

-- 12c 이상 표준 페이징 (권장)
SELECT emp_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- 11g 이하 ROWNUM 페이징 (레거시 유지보수용)
SELECT emp_name, salary
FROM (
    SELECT a.*, ROWNUM rnum
    FROM (
        SELECT emp_name, salary
        FROM employees
        ORDER BY salary DESC
    ) a
    WHERE ROWNUM <= 20
)
WHERE rnum > 10;

-- /*+ INDEX(테이블명/알리아스 인덱스명) */ 힌트 적용
SELECT /*+ INDEX(e idx_emp_salary) */ emp_name, salary
FROM employees e
WHERE salary > 50000;

-- 해시 조인 강제 힌트
SELECT /*+ USE_HASH(e d) */ e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;