Oracle - ynjch97/YNJCH_WIKI GitHub Wiki

참고 사이트

1. Oracle 설치

참고 : https://backendcode.tistory.com/266
하단의 데이터베이스 > Database Enterprise/Standard Edition 클릭
Oracle Database Enterprise Edition > Oracle Database 19c for Microsoft Windows x64 (64-bit) ZIP 다운로드 및 압축 해제
Oracle 환경 변수 설정
- 사용자 변수 > ORACLE_BASE: C:\Users\YNJCH\Oracle\Oracle / ORACLE_HOME: C:\Users\YNJCH\Oracle\Oracle\product\19.3.0\dbhome
setup.exe 실행 (관리자 권한으로 실행 - 54% 멈춤 현상 방지)
단일 인스턴스 데이터베이스 생성 및 구성 > 서버 클래스 > 표준 설치 > 가상 계정 사용
- Oracle Base : C:\Users\YNJCH\Oracle\Oracle
- 저장 영역 유형 : 파일 시스템
- 전역 데이터베이스 이름 : orcl
- 비밀번호 : 1111
Oracle 기본 Port : 1521 / SID : orcl
아래 명령어로 설치 및 접속 완료 확인

sqlplus system/1111

아래 명령어로 기본 설정 확인

-- SID 확인하기
select name from v$database;
-- Port 번호 확인하기
select dbms_xdb.gethttpport() from dual;
-- 1521이 아니면 Port 번호를 1521로 변경
exec dbms_xdb.sethttpport(포트번호);

1-1. Oracle 설치 삭제

C:\Users\YNJCH\Oracle\Oracle\WINDOWS.X64_193000_db_home\deinstall > deinstall.bat
- [LISTENER]에서 구성을 해제할 단일 인스턴스 리스너 모두 지정 → 아무값도 입력하지 않고 엔터를 입력한다.
- 이 Oracle 홈에 구성된 데이터베이스 이름 목록을 지정하십시오. [ORCL] → ORCL 입력
- 데이터베이스의 진단 대상 위치를 지정하십시오. [C:\app\hojun.jung\diag\rdbms\orcl] → 자신의 orcl 경로 입력 ( C:\app\hojun.jung\diag\rdbms\orcl )
- ASM|FS 데이터베이스에서 사용하는 저장 영역 유형을 지정하십시오. → FS 입력
- 공유 파일 시스템에 데이터베이스 파일이 존재할 경우 디렉토리 목록을 지정하십시오. 'ORCL' 하위 디렉토리가 발견될 경우 삭제됩니다. 그렇지 않을 경우 지정된 디렉토리가 삭제됩니다. 또는 전체 경로와 함께 데이터베이스 파일 목록을 지정할 수도 있습니다. → 공백 후 엔터
- 플래시 복구 영역 위치(파일 시스템에 구성된 경우)를 지정하십시오. 'ORCL' 하위 디렉토리가 발견될 경우 삭제됩니다. → 그냥 엔터
- 데이터베이스 spfile 위치를 지정하십시오. → 공백 후 엔터
- 계속하겠습니까(y - 예, n - 아니오)? [n] → y 입력
경로 내 폴더 및 파일 삭제 C:\Users\YNJCH\Oracle\oradiag_ynjch\diag\clients
레지스트리 편집기 삭제
- HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE 삭제
- HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Service\Oralce 키워드 삭제
- HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Service\Oracle 키워드 삭제
- HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle 키워드 삭제
- HKEY_CLASSES_ROOT에서 Ora로 시작되는 모든 것 삭제
서비스 삭제

sc delete OracleJobSchedulerORCL
sc delete OracleOraDB19Home1TNSListener
sc delete OracleRemExecServiceV2
sc delete OracleServiceORCL
sc delete OracleVssWriterORCL

2. 사용자 생성 및 권한 부여

Oracle 설치 및 데이터베이스 생성 이후, 사용자 생성 단계
관리자 권한으로 cmd 창 실행

C:\Windows\system32> sqlplus

사용자명 입력: SYSTEM
비밀번호 입력: [비밀번호 입력]

2-1. 사용자 생성

사용자 ID와 PW를 입력

CREATE USER [ID] IDENTIFIED BY [PW];
-- 생성된 사용자 확인
SELECT USERNAME FROM DBA_USERS;
-- 사용자 비밀번호 변경
ALTER USER [ID] IDENTIFIED BY [PW];

생성된 아이디로 접속하여 SHOW USER; 입력 시, 사용자 확인 가능
- sqlplus 에서는 CONN [ID] 를 이용해 SYSTEM 계정에서 타 계정으로 접속 가능

2-1-1. 사용자 삭제

CASCADE : 사용자와 객체를 모두 삭제할 경우

DROP USER [ID] ['CASCADE'];

2-2. 권한 부여

부여할 권한을 콤마(,)로 구분하여 나열
WITH ADMIN OPTION : 현재 GRANT 문을 통해 부여받은 권한을 다른 사용자에게 부여할 수 있는 권한을 부여
- 현재 사용자 권한이 사라져도 권한을 재부여한 다른 사용자 권한은 유지됨

GRANT [권한] TO [ID] [WITH ADMIN OPTION]; /* GRANT CONNECT, RESOURCE, DBA TO [ID]; */

2-2-1. 권한 종류

CONNECT : 접속 권한
RESOURCE : 객체 및 데이터 조작 권한
- 시스템 권한의 일정 부분을 부여
- CREATE TABLE, CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TRIGGER, CREATE TYPE 부여 가능
DBA : DB 시작, 종료 권한을 제외한 모든 시스템 권한
- 유저 생성 및 권한 부여, 스키마 오브젝트 생성, 수정, 삭제 등

2-2-2. 권한 취소

REVOKE [권한] FROM [ID];  /* REVOKE CONNECT, RESOURCE, DBA FROM [ID]; */

2-2-3. 객체 권한 부여

객체 권한 : 콤마(,)로 구분하여 여러 권한을 부여하거나 ALL PRIVILEGES 를 이용하여 모든 권한 부여

GRANT [객체 권한/ALL PRIVILEGES]
ON [스키마, 객체 이름]
TO [ID]
[WITH GRANT OPTION];

-- 예시) ynjch에게 USER_INFO 테이블의 SELECT, INSERT 권한 부여
GRANT SELECT, INSERT ON USER_INFO TO ynjch;

2-3. 적용 사항 확인

아래 명령어로 commit 후, 생성된 사용자 확인

COMMIT;
SELECT * FROM ALL_USERS;

3. 테이블

3-1. 테이블 생성

CREATE TABLE [테이블명] 
(
    [컬럼명] NUMBER(10) NOT NULL 
    , [컬럼명] VARCHAR2(20)
);
-- 삭제 : DROP TABLE USER_INFO;

3-1-1. 기본키 생성

테이블을 생성하면서 PK 지정 (컬럼 옆에 옵션 추가)

CREATE TABLE TAB1(
    COL1 NUMBER NOT NULL PRIMARY KEY,
    COL2 VARCHAR2(10)
);

테이블을 생성하면서 PK 지정 (하단에 제약 조건 추가)

CREATE TABLE TAB1(
    COL1 NUMBER NOT NULL,
    COL2 VARCHAR2(10),
    CONSTRAINT PK_TAB1 PRIMARY KEY(COL1)
    -- CONSTRAINT [PK명] PRIMARY KEY([PK 컬럼명])
);

테이블 생성 후 PK 지정

ALTER TABLE TAB1
ADD CONSTRAINT PK_TAB1 PRIMARY KEY(COL1);

3-1-2. 기본키 삭제

ALTER TABLE TAB1
DROP CONSTRAINT PK_TAB1;

3-1-3. 외래키 생성

테이블을 생성하면서 FK 지정

/*
CREATE TABLE TAB1(
    COL1 NUMBER NOT NULL PRIMARY KEY,
    COL2 VARCHAR2(10)
);
*/
CREATE TABLE TAB2 (
    COL3 NUMBER NOT NULL PRIMARY KEY,
    COL4 NUMBER NOT NULL,
    COL5 VARCHAR2(10),
    CONSTRAINT FK_TAB2 FOREIGN KEY(COL4) REFERENCES TAB1 (COL1)
    -- CONSTRAINT [FK명] FOREIGN KEY([FK 컬럼명]) REFERENCES [PK가 있는 테이블] ([PK 컬럼명])
);

테이블 생성 후 FK 지정

ALTER TABLE TAB2
ADD CONSTRAINT FK_TAB2 FOREIGN KEY(COL4) REFERENCES TAB1 (COL1);

3-1-4. 외래키 삭제

ALTER TABLE TAB2
DROP CONSTRAINT FK_TAB2;

3-1-5. 제약조건 확인

SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME = '[테이블명]';

-- 기본키(PK) 정보 조회
SELECT A.TABLE_NAME
     , A.CONSTRAINT_NAME
     , B.COLUMN_NAME     
     , B.POSITION
  FROM ALL_CONSTRAINTS  A
     , ALL_CONS_COLUMNS B
 WHERE A.TABLE_NAME      = '[테이블명]'
   AND A.CONSTRAINT_TYPE = 'P' 
   AND A.OWNER           = B.OWNER
   AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
 ORDER BY B.POSITION;

3-2. 컬럼 데이터 타입

3-2-1. 문자 데이터 타입

CHAR(n) : 고정길이 문자(최대 2000byte, 디폴트값 1byte)
VARCHAR2(n) : 가변길이 문자(최대 4000byte, 디폴트값 1byte)
NCHAR(n) : 고정길이 유니코드 문자(최대 2000byte, 디폴트값 1byte, 다국어 입력 가능)
NVARCHAR(n) : 가변길이 유니코드 문자(최대 2000byte, 디폴트값 1byte, 다국어 입력 가능)
LONG : 최대 2GB 크기의 가변길이 문자형
CLOB : 대용량 텍스트 데이터 타입(최대 4GB)
NCLOB : 대용량 텍스트 유니코드 데이터 타입(최대 4GB)

3-2-2. 숫자 데이터 타입

NUMBER(P,S) : 가변숫자(최대 22byte, P : 1~38, 디폴트값 38 / S : -84~127, 디폴트값 0)
FLOAT(P) : NUMBER 의 하위 타입(최대 22byte, 이진수 기준, P : 1~128, 디폴트값 128)
BINARY_FLOAT : 32비트 부동소수점 수(최대 4byte)
BINARY_DOUBLE : 64비트 부동소수점 수(최대 8byte)

3-2-3. 날짜 데이터 타입

DATE : 연, 월, 일, 시, 분, 초까지 입력(BC 4712년 1월 1일부터 9999년 12월 31일)
TIMESTAMP : 연, 월, 일, 시, 분, 초 + 밀리초까지 입력 가능

3-2-4. LOB 데이터 타입

대용량 데이터를 저장할 수 있는 데이터 타입을 LOB(Large Object) 이라고 함
CLOB : 문자형 대용량 객체 고정길이와 가변길이 문자집합 지원
NCLOB : 유니코드를 지원하는 문자형 대용량 객체
BLOB : 이진형 대용량 객체(그래픽, 이미지, 동영상 등의 데이터 저장 시 사용)
BFILE : 대용량 이진 파일에 대한 위치, 이름 저장

3-3. 컬럼 제어

컬럼 추가 : ALTER TABLE [테이블명] ADD([컬럼명] ([데이터타입], [사이즈]));
컬럼 수정 : ALTER TABLE [테이블명] MODIFY([컬럼명] ([데이터타입], [사이즈]));
컬럼 삭제 : ALTER TABLE [테이블명] DROP COLUMN [컬럼명];
컬럼명 변경 : ALTER TABLE [테이블명] RENAME COLUMN [기존 컬럼명] TO [변경 컬럼명];

ALTER TABLE TAB1 ADD (COL1 NUMBER(10,0));

3-3-1. 컬럼 순서 제어

COL2, COL3 컬럼 뒤에 추가된 COL1 컬럼을 COL2 앞으로 위치시키고 싶을 때
COL2 이하의 컬럼들을 모두 INVISIBLE 처리했다가 VISIBLE 처리함으로서 순서 제어 가능

ALTER TABLE TAB1 MODIFY COL2 INVISIBLE;
ALTER TABLE TAB1 MODIFY COL3 INVISIBLE;
ALTER TABLE TAB1 MODIFY COL2 VISIBLE;
ALTER TABLE TAB1 MODIFY COL3 VISIBLE;

4. 시퀀스

4-1. 시퀀스 생성

CREATE SEQUENCE [시퀀스명]
START WITH 1   /* 시작값 */
INCREMENT BY 1  /* 증가값 */
MINVALUE 1 /* 최소값 */
MAXVALUE 10000000 /* 최대값 */
CYCLE /* 시퀀스 최대값 도달 시 최소값부터 다시 시작할지 여부 (CYCLE, NOCYCLE) */
NOCACHE /* CACHE 사용 여부 (CACHE, NOCACHE) */
;

4-2. 시퀀스 조회

시퀀스 정보를 조회하거나, 시퀀스 일련번호를 조회할 수 있음
증가 없이 현재 시퀀스 순번을 가져오려면 [시퀀스명].CURRVAL 사용

SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = '[시퀀스명]';
SELECT [시퀀스명].CURRVAL FROM DUAL;
SELECT [시퀀스명].NEXTVAL FROM DUAL;

4-2-1. 시퀀스를 이용한 INSERT

INSERT INTO [테이블명] values([시퀀스명].NEXTVAL, 'test');

4-3. 시퀀스 수정

시퀀스 순번 초기화 시 아래 내용 입력

ALTER SEQUENCE [시퀀스명] INCREMENT BY -1000;
ALTER SEQUENCE [시퀀스명] INCREMENT BY 1;

시퀀스 증가값, 최대값 등을 조정할 수 있음

ALTER SEQUENCE [시퀀스명] INCREMENT BY [증가값];
ALTER SEQUENCE [시퀀스명] MAXVALUE [최대값];

4-4. 시퀀스 삭제

DROP SEQUENCE [시퀀스명];

5. 인덱스(Index)

데이터를 빠르게 찾기 위해 오름차순으로 정렬된 주소 체계
속도 향상을 위해 사용됨
- Full Scan 으로 테이블의 모든 데이터를 가져오는 방법이 아닌 ROOT - BRANCH - LEAF - DATA BLOCK 총 4번의 IO를 통해 접근 가능
모든 테이블에는 ROWID 컬럼이 존재하며, 인덱스는 ROWID를 통해 DATA BLOCK에 접근
- ROWID = FILE 번호 + BLOCK 번호 + ROW 번호

5-1. 인덱스 구조

ROOT, BRANCH, LEAF로 구성되어 있는 계층적 구조
- 오라클 서버에서 FULL SCAN보다 INDEX SCAN이 유리하다고 판단되었을 때 INDEX의 ROOT부터 찾음
- ROOT에는 BRANCH 블럭의 시작점 정보를, BRANCH에는 LEAF 블럭의 시작점 정보를, LEAF에는 데이터의 ROWID를 알 수 있음
- 찾고자 하는 데이터의 위치를 빠르게 검색 가능 !인덱스 구조

5-2. 인덱스 속도

오라클 서버는 FULL SCAN, INDEX SCAN 중 더 유리한 방법으로 Scan 작업을 진행
INDEX 생성 시 저장 공간이 필요 (많은 컬럼을 인덱스 설정한다고 좋은 것은 아님)
- 지속적인 INSERT가 이루어지는 테이블은 인덱스의 크기도 커짐
SELECT 구문에서 검색 속도를 향상시켜주는 역할

5-2-1. 인덱스 사용

검색하려는 모든 데이터가 고유한 값(PK)이면 가장 최적화된 상태
WHERE 절에 사용되지 않는 컬럼은 굳이 인덱스 생성할 필요가 없음
- 중복되는 데이터여도 WHERE 절에 자주 사용되는 컬럼이라면 INDEX SCAN 이 효과적
대량의 데이터 중 적은 양의 데이터(특정 조건)가 필요할 때 사용

5-3. 인덱스 생성 및 조회

인덱스 생성

CREATE INDEX 인덱스명
ON 스키마명.테이블명(컬럼1, 컬럼2, ...);

인덱스 조회

SELECT
    A.TABLE_OWNER
    , A.TABLE_NAME 
    , A.INDEX_NAME 
    , A.COLUMN_NAME 
FROM ALL_IND_COLUMNS A 
WHERE A.TABLE_NAME = '테이블명'
ORDER BY A.INDEX_NAME, A.COLUMN_POSITION

5-4. 인덱스 용량 조회

DBA_SEGMENTS 에 INDEX, TABLE 을 넣어 인덱스, 테이블의 용량 조회 가능
SEGMENT_NAME 에 인덱스명 또는 테이블명 기재

/* 유저, 인덱스 별 용량 확인 */	
SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,SUM(BYTES)/1024/1024 AS MB	
FROM DBA_SEGMENTS	
WHERE SEGMENT_TYPE='INDEX' -- TABLE or INDEX
AND OWNER = '[스키마명]'
AND SEGMENT_NAME = '[인덱스명 또는 테이블명]'
GROUP BY OWNER,SEGMENT_NAME,SEGMENT_TYPE;

6. 테이블 및 컬럼 정보

현재 데이터베이스 조회 : SELECT NAME FROM V$DATABASE;

-- OBJECT 조회
SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = [오브젝트명];
-- SYNONYM 조회
SELECT * FROM ALL_SYNONYMS WHERE SYNONYM_NAME = [시노님명];
-- INDEX 조회
SELECT * FROM USER_INDEXES WHERE INDEX_NAME = [인덱스명];
-- INDEX 열 조회
SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME = [인덱스명];
-- 제약조건이 있는 열 조회
SELECT * FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = [제약조건명];
-- 뷰 테이블 조회
SELECT * FROM USER_VIEWS WHERE VIEW_NAME = [뷰테이블명];

6-1. 테이블 정보 조회

테이블 목록 조회

SELECT * FROM ALL_TABLES;
SELECT * FROM DBA_TABLES;
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'TABLE';

접속한 계정의 테이블 목록 조회

SELECT * FROM TABS;
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE';
SELECT * FROM USER_TABLES;
-- 특정 테이블 조회
SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE '%%';

6-2. 컬럼 정보 조회

SELECT * FROM COLS WHERE TABLE_NAME LIKE '%%';
SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME LIKE '%%'; /* 또는 USER_TAB_COLUMNS */

6-3. 테이블 및 컬럼 코멘트

COMMENT ON TABLE [테이블명] IS '코멘트';
COMMENT ON COLUMN [테이블명].[컬럼명] IS '코멘트';

코멘트 조회

-- 테이블 코멘트 조회
SELECT * FROM ALL_TAB_COMMENTS
WHERE TABLE_NAME LIKE '%USER_INFO%';

-- 테이블 컬럼 코멘트 조회
SELECT * FROM ALL_COL_COMMENTS
WHERE TABLE_NAME LIKE '%USER_INFO%';

6-3-1. 테이블, 컬럼 정보 조회 쿼리

SELECT 
    TC.TABLE_NAME
    , TC.COMMENTS
    , CC.COLUMN_NAME
    , CC.COMMENTS
    , C.DATA_TYPE
    , C.DATA_LENGTH
    , C.NULLABLE
    , C.DATA_DEFAULT
FROM ALL_TAB_COMMENTS TC
LEFT JOIN ALL_TAB_COLUMNS C
ON TC.OWNER = C.OWNER
    AND TC.TABLE_NAME = C.TABLE_NAME
LEFT JOIN ALL_COL_COMMENTS CC
ON TC.OWNER = CC.OWNER
    AND TC.TABLE_NAME = CC.TABLE_NAME
    AND C.COLUMN_NAME = CC.COLUMN_NAME
WHERE TC.TABLE_NAME LIKE '[테이블명]';

7. 데이터 변환

7-1. 문자열 숫자 변환

TO_NUMBER() : 문자열을 정수, 실수로 변환
CAST() : 자릿수를 정의하여 정수, 실수로 변환

SELECT TO_NUMBER('123') FROM DUAL; /* 123 */
SELECT CAST('123' AS NUMBER(3)) FROM DUAL; /* 123 */
SELECT TO_NUMBER('123.5') FROM DUAL; /* 123.5 */
SELECT CAST('123.55' AS NUMBER(3)) FROM DUAL; /* 124 */
SELECT CAST('123.55' AS NUMBER(5, 2)) FROM DUAL; /* 123.55 */

7-1-1. 문자열 숫자 변환 오류 처리

문자열에 변환 불가한 문자가 포함되어 있으면 오류가 발생함

SELECT TO_NUMBER('13,000,000') FROM DUAL;

REPLACE() 함수 또는 REGEXP_REPLACE() 정규식 함수를 이용하여 해당 문자열 제거
REGEXP_REPLACE() 는 Oracle 10g 이상부터 사용 가능

-- 직접 해당 문자열을 제거 (마지막 인자 생략 가능)
SELECT REPLACE(REPLACE('* 13,000,000', ','), '*') FROM DUAL;
-- 정규식으로 숫자(소수점, 음수 포함) 외 문자 모두 제거
SELECT REGEXP_REPLACE('* -13,000,000', '[^0-9.-]', '') FROM DUAL;

7-1-2. 문자열 숫자 변환 가능 여부 체크

TRANSLATE('문자열', '대상문자', '변환문자') : 문자열 치환 함수(Oracle 8i 이상 사용 가능)
- 대상문자와 변환문자가 1:1로 변환되며, 대상문자에는 있지만 변환문자에 없는 문자는 제거됨
- 아래의 경우 대상문자 'A'는 그대로 'A'로 변환되지만, 숫자와 부호들은 변환문자에 없기 때문에 제거됨

SELECT 
    CASE WHEN TRANSLATE('-123.456', 'A1234567890.+-', 'A') IS NULL 
    THEN TO_NUMBER('-123.456') END
FROM DUAL;

REGEXP_INSTR('문자열', '패턴') : 특정 패턴이 출현하는 첫 위치 값을 반환(Oracle 10g 이상 사용 가능)

SELECT 
    CASE WHEN REGEXP_INSTR('-123.456','^[+-]?\d*(\.?\d*)$') = 1 /* 숫자일 경우 1 반환 */
    THEN TO_NUMBER('-123.456') END
FROM DUAL;

VALIDATE_CONVERSION() : Oracle 12c 부터 생긴 형변환 체크 함수

SELECT 
    CASE WHEN VALIDATE_CONVERSION('-123.456' AS NUMBER) = 1
    THEN TO_NUMBER('-123.456') END
FROM DUAL;

7-1-3. 문자열 숫자 변환 오류 대처

형변환 오류 발생 시 지정한 대체 값을 출력하도록 함

SELECT 
    TO_NUMBER('12345' DEFAULT -1 ON CONVERSION ERROR) AS res1 /* 12345 */
    , TO_NUMBER('12345A' DEFAULT -1 ON CONVERSION ERROR) AS res2 /* -1 */
    , CAST('12345' AS NUMBER(10) DEFAULT -1 ON CONVERSION ERROR) AS res3 /* 12345 */
    , CAST('12345A' AS NUMBER(10) DEFAULT -1 ON CONVERSION ERROR) AS res4 /* -1 */
FROM DUAL;

7-1-4. 문자열로 숫자 조회

TO_CHAR([컬럼명], [포맷]) 함수 이용

SELECT TO_CHAR('123', 'FM00000') FROM DUAL; /* 00123 */
SELECT TO_CHAR('100000', 'FM999,999,999,999') FROM DUAL; /* 100,000 */
SELECT TO_CHAR('100000', 'FML999,999,999') FROM DUAL; /* ￦100,000 */
SELECT TO_CHAR('3.1415926', 'FM990.99') FROM DUAL; /* 3.14 */

7-2. 문자열 날짜 변환

현재 일시 출력 : SELECT SYSDATE FROM DUAL;
TO_DATE([문자열], [날짜 포맷]) 를 이용하여 문자열을 날짜 데이터로 변환 가능
- 날짜 포맷은 대소문자 구분 없음
- 12시간 단위는 HH, 24시간 단위는 HH24로 표기

SELECT 
    TO_DATE('2022-08-12 13:50:00', 'YYYY-MM-DD HH24:MI:SS')
    , TO_DATE('11.08.2022', 'DD.MM.YYYY')
    , TO_DATE('220810', 'YYMMDD')
FROM DUAL;

시스템 설정 날짜 형식과 동일하면 날짜 포맷을 입력하지 않아도 됨

SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT'; /* 결과 : RR/MM/DD */
SELECT TO_DATE('2022/08/12') FROM DUAL; /* 정상 출력 */
SELECT TO_DATE('12/08/2022') FROM DUAL; /* 오류 발생 */

7-2-1. 문자열로 날짜 조회

다음과 같이 저장한 데이터를 출력

-- 데이터 INSERT
INSERT INTO TAB3 VALUES (TO_DATE('2022-08-12 13:50:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TAB3 VALUES (TO_DATE('2022-08-11', 'YYYY-MM-DD'));

날짜와 시간 조회
- FM 사용하여 날짜의 '0' 표기 생략 가능

SELECT TO_CHAR(COL4, 'YYYY-MM-DD HH24:MI:SS') FROM TAB3; 
    -- 2022-08-12 13:50:00 / 2022-08-11 00:00:00
SELECT TO_CHAR(COL4, 'YYYY-FMMM-DD') FROM TAB3;
    -- 2022-8-12 / 2022-8-11

오전, 오후 값 조회
- TO_CHAR([컬럼명], 'AM') 를 세 번째 인자 없이 사용 시 Oracle의 nls_date_language 설정 형식에 맞는 데이터가 출력됨

SELECT TO_CHAR(COL4, 'PM HH:MI:SS') FROM TAB3;
    -- 오후 01:50:00 / 오전 12:00:00
SELECT TO_CHAR(COL4, 'AM', 'nls_date_language=american') FROM TAB3;
    -- PM / AM
SELECT TO_CHAR(COL4, 'AM', 'nls_date_language=korean') FROM TAB3;
    -- 오후 / 오전

요일 값 조회
- 일요일부터 '1'으로 시작하며, Oracle 언어 설정에 맞는 데이터가 출력됨

SELECT TO_CHAR(COL4, 'D') FROM TAB3;
    -- 6 / 5
SELECT TO_CHAR(COL4, 'DY') FROM TAB3;
    -- 금 / 목
SELECT TO_CHAR(COL4, 'DAY') FROM TAB3;
    -- 금요일 / 목요일

기타 조회

SELECT TO_CHAR(COL4, 'DDD') FROM TAB3; /* 224 (365일 기준 224일 째) */
SELECT TO_CHAR(COL4, 'WW') FROM TAB3; /* 32 (1년 기준 32주 째) */
SELECT TO_CHAR(COL4, 'Q') FROM TAB3; /* 3 (3분기) */
SELECT TO_CHAR(COL4, 'MON') FROM TAB3; /* 8월 */
SELECT TO_CHAR(COL4, 'DL') FROM TAB3; /* 2022년 8월 12일 금요일 */

7-2-2. 날짜 데이터

SYSDATE : 년월일시분초 표현
SYSTIMESTAMP : 밀리세컨드까지 표현 가능
- 컬럼 데이터 타입을 TIMESTAMP로 지정해야 사용 가능

SELECT SYSDATE FROM DUAL; /* 22/08/18 */
SELECT TO_CHAR(SYSDATE, 'YYYY.MM.DD HH24:MI:SS') FROM DUAL; /* 2022.08.18 11:34:44 */
SELECT SYSTIMESTAMP FROM DUAL; /* 22/08/18 11:47:08.541000000 +09:00 */
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY.MM.DD HH24:MI:SS:FF') FROM DUAL; /* 2022.08.18 11:47:52:329000 */

날짜를 더하거나 뺄 수 있음

SELECT SYSDATE + 1 FROM DUAL; /* 22/08/19 */
SELECT TO_DATE('1997.10.16 15:00:30', 'YYYY.MM.DD HH24:MI:SS') + 3 FROM DUAL; /* 97/10/19 */
SELECT TO_CHAR(SYSDATE - 1/24 * 2, 'YYYY.MM.DD HH24:MI:SS') FROM DUAL; /* 2022.08.18 09:46:11 (2시간 전) */
SELECT TO_CHAR(SYSDATE + 1/24/60, 'YYYY.MM.DD HH24:MI:SS') FROM DUAL; /* 2022.08.18 11:45:32 (1분 뒤) */
SELECT TO_CHAR(SYSDATE + 1/24/60/60 * 8, 'YYYY.MM.DD HH24:MI:SS') FROM DUAL; /* 2022.08.18 11:45:32 (8초 뒤) */

7-2-3. 문자열 날짜 변환 오류 처리

REGEXP_INSTR() 정규식 함수 사용(Oracle 10g 이상)
- 날짜 형식 체크는 가능하지만, 유효한 값인지 판단하지 못함
VALIDATE_CONVERSION() 형식 체크 함수 사용(Oracle 12c R2 이상)
- 날짜 형식이 맞으면 '1', 틀리면 '0' 반환

SELECT REGEXP_INSTR('2022-08-08', '^\d{4}-\d{2}-\d{2}') FROM DUAL;
SELECT VALIDATE_CONVERSION('2022-08-12' AS DATE, 'YYYY-MM-DD') FROM DUAL;

IS_DATE() 사용자 정의 함수 사용

-- DATE 형식이 맟을 경우 '1'을 반환하도록 함
CREATE FUNCTION IS_DATE(dStr VARCHAR2, dFormat VARCHAR2)
    RETURN NUMBER
IS
    resDate DATE;
BEGIN
    resDate := TO_DATE(dStr, dFormat);
    RETURN 1;
EXCEPTION
    WHEN OTHERS THEN
        RETURN 0;
END;
/

-- 테스트
SELECT IS_DATE('2022-08-12', 'YYYY-MM-DD') FROM DUAL;
SELECT IS_DATE('2022-08-12', 'YYYYMMDD') FROM DUAL;

-- 함수 조회
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'FUNCTION';
DROP FUNCTION IS_DATE_TEST7;

7-2-4. 프로시저에서 날짜 변환 오류 처리

TO_DATE, TO_CHAR 함수 사용 시 두 번째 인자인 날짜 포맷을 지정하지 않을 경우
- ORA-01861: literal does not match format string(리터럴이 형식 문자열과 일치하지 않음) 오류 발생
- TO_DATE('2023-09-22', 'YYYY-MM-DD') 이와 같이 날짜 포맷 지정 필요

8. 트랜잭션

일관성 있는 상호작용을 위한 단위
데이터베이스 시스템은 ACID(Atomicity, Consistency, Isolation, Durability)를 보장하기 위해 트랜잭션을 논리적 작업 단위로 사용
트랜잭션을 통해 데이터베이스는 항상 일관성 있는 상태를 유지해야 함

8-1. COMMIT

데이터베이스 연산이 정확히 종료되어 변경 내역을 실제 데이터베이스에 반영
커밋하기 전, 서버에 롤백 세그먼트 버퍼가 만들어지며 영향 받은 행들이 LOCK 됨
- SELECT문을 사용하여 트랜잭션의 효과를 볼 수 있음
커밋 후, 영향 받은 행에 수용된 LOCK이 해제되며 서버 내부 트랜잭션 테이블이 시스템 변경 번호를 생성, 트랜잭션에 대입, 테이블에 저장함

8-2. ROLLBACK

데이터베이스 연산 중 문제 발생 직전의 COMMIT 지점까지 되돌림
ROLLBACK TO SAVEPOINT를 이용해 일부만 ROLLBACK 가능

ROLLBACK [WORK] [TO [SAVEPOINT] 세이브포인트명];

8-2-1. SAVEPOINT

ROLLBACK 수행 시 마지막 COMMIT 지점까지 되돌아감
SAVEPOINT 지정하면 그 부분에서 트랜잭션 취소 가능

9. 세션(Session)

세션은 사용자가 오라클 데이터베이스에 접속하여 종료할 때까지 계속 유지됨
각 세션에는 고유한 SID(Session identifier)와 SERIAL#(serial number)을 가짐
락(Lock) : 특정 세션이 테이블의 데이터를 변경하고 COMMIT/ROLLBACK 하기 전까지 다른 세션이 조작 불가능한 상태
데드락(DeadLock) : 교착 상태, 두 개 이상의 작업이 서로의 트랜잭션이 완료될 때까지 무한정 대기하는 상태

9-1. 세션 조회

현재 접속 수 : SELECT * FROM V$RESOURCE_LIMIT WHERE RESOURCE_NAME IN ('processes', 'sessions', 'transactions');
세션 확인 : SELECT * FROM V$SESSION;
세션 KILL : ALTER SYSTEM KILL SESSION '[SID], [SERIAL#]';

9-1-1. 세션 사용 중인 테이블 조회

-- 세션 사용 중인 테이블 명 확인 가능
SELECT O.OBJECT_NAME, S.STATUS, S.SID, S.SERIAL#, P.SPID, S.PROGRAM, SQ.SQL_FULLTEXT, S.LOGON_TIME
FROM V$LOCKED_OBJECT L,
     DBA_OBJECTS O,
     V$SESSION S,
     V$PROCESS P,
     V$SQL SQ
WHERE L.OBJECT_ID = O.OBJECT_ID
  AND L.SESSION_ID = S.SID
  AND S.PADDR = P.ADDR
  AND S.SQL_ADDRESS = SQ.ADDRESS;
-- 아래 쿼리로도 조회 가능
SELECT /*+ PARALLEL(64) */
  a.sid,       -- SID
  a.serial#,   -- 시리얼번호
  a.status,    -- 상태정보
  a.process,   -- 프로세스정보
  a.username,  -- 유저
  a.osuser,    -- 접속자의 OS 사용자 정보
  b.sql_text,  -- sql
  c.program    -- 접속 프로그램
FROM
  v$session a,
  v$sqlarea b,
  v$process c
WHERE
  a.sql_hash_value=b.hash_value
  AND a.sql_address=b.address
  AND a.paddr=c.addr
  AND a.status='ACTIVE';

9-1-2. 실행 중인 쿼리의 진행 상태 조회

SQL_ID 를 이용해 SQL Monitoring List 조회

-- 찾으려는 쿼리의 SQL_ID 확인
select dbms_sqltune.report_sql_monitor_list(type=>'TEXT', report_level=>'ALL', active_since_sec=>60) as report from dual;
-- SQL_ID 입혁하여 실행상태 확인 가능
select DBMS_SQLTUNE.report_sql_monitor(sql_id => '&sql_id', type=>'TEXT' , report_level => 'ALL') from dual;

DBA_SEGMENTS 테이블에서 조회

SELECT * FROM DBA_SEGMENTS WHERE SEGMENT_NAME = '테이블명';