====== Oracle Database ======
  * [[database:oracle:jdbc|Oracle JDBC Driver]]

===== Docker Images =====
  * [[https://hub.docker.com/r/wnameless/oracle-xe-11g-r2|wnameless/oracle-xe-11g-r2]]
  * [[https://hub.docker.com/r/oracleinanutshell/oracle-xe-11g|oracleinanutshell/oracle-xe-11g - Docker Hub]]
    * [[https://www.sysnet.pe.kr/2/0/12191|개발 환경 구성: 483. docker - OracleXE 컨테이너 실행]]
  * [[https://hub.docker.com/r/jaspeen/oracle-11g|jaspeen/oracle-11g - Docker Hub]]
  * [[https://hub.docker.com/r/jaspeen/oracle-xe-11g|jaspeen/oracle-xe-11g - Docker Hub]] - https://github.com/jaspeen/docker-oracle-xe-11g
  * [[https://hub.docker.com/_/oracle-database-enterprise-edition|Oracle Database Enterprise Edition - Docker Hub]]
    * [[https://medium.com/@pakss328/docker-oracle-%EA%B8%B0%EB%B3%B8-%EC%85%8B%ED%8C%85-a09bf869cb59|docker oracle 기본 셋팅 - 박상수 - Medium]]
  * [[https://github.com/steveswinsburg/oracle12c-docker|steveswingsburg/oracle12c]]
  * [[https://emflant.tistory.com/237|(docker) oracle xe 18c 설치 및 sqlplus 접속하기 :: SourceBox]]
  * [[https://github.com/oracle/docker-images|oracle/docker-images: Official source for Docker configurations, images, and examples of Dockerfiles for Oracle products and projects]]

===== Docker 로 설치 =====
  * [[https://github.com/oracle/docker-images/issues/959|Docker 로 Oracle 설치시에 all_objects 조회가 엄청나게 느린 현상이 발생함]]
    * [[java:junit:dbunit|DBUnit]], [[java:junit:database_rider|database-rider]] 에서 문제됨.
    * docker 기반이 아닐 경우 1초 정도에 실행 끝남.
    * [[https://stackoverflow.com/questions/37722685/oracle-11g-docker-select-very-slow|sql - Oracle 11g docker SELECT very slow - Stack Overflow]] ''asynch_io'' 를 꺼야 성능이 좋아진다는 내용
  * [[https://hub.docker.com/r/wnameless/oracle-xe-11g-r2|wnameless/oracle-xe-11g-r2]] [[https://github.com/wnameless/docker-oracle-xe-11g|github]]<code sh>
docker run -d --name==oracle11gr2 \
 -p 49161:1521 \
 -p 49180:8080 \
 -e ORACLE_ALLOW_REMOTE=true
 -e ORACLE_DISABLE_ASYNCH_IO=true # 필수!! all_objects 조회를 위해서.
  wnameless/oracle-xe-11g-r2
</code>

  * 접속정보<code>
hostname: localhost
port: 49161 # container port 1521
sid: xe
username: system
password: oracle
</code>
  * APEX ''http://localhost:<dockerport>/apex/apex_admin'' 접속<code>
# container port 8080
username: ADMIN
password: admin
</code>

  * sqlplus<code sh>
docker exec -it -u oracle:dba oracle11gr2 /bin/bash
# sqlplus 실행
sqlplus "SYS/oracle" AS SYSDBA

</code>
===== Table Space와 사용자 추가 =====
  * [[https://blueray21.tistory.com/35|블루레이 :: [Oracle] CREATE TABLESPACE]]
  * [[https://12bme.tistory.com/300|[오라클] 테이블스페이스와 데이터파일]]

<code sql>
-- Table Sapace 추가
CREATE TABLESPACE <tablespace_name> 
    DATAFILE '<tablespace_name>.dbf' 
    SIZE 100M 
    AUTOEXTEND ON NEXT 10M;

CREATE USER <username> IDENTIFIED BY <password>
    DEFAULT TABLESPACE <tablespace_name>
    QUOTA UNLIMITED ON <tablespace_name>
    TEMPORARY TABLESPACE TEMP;


GRANT CONNECT, RESOURCE TO <username>;
</code>
  * 이제 ''<username>'' 사용자로 접속해서 작업한다.
  * 가급적 ''system'' 계정으로는 작업하지 말 것.
===== Version 확인 =====
  * [[https://community.oracle.com/thread/2250946|How to check the Oracle database version | Oracle Community]]

<code sql>
SELECT * FROM V$VERSION;
SELECT version FROM V$INSTANCE;
</code>

===== charset / characterset =====
  * 확인
<code sql>
select * from nls_database_parameters where parameter like '%CHARACTERSET%'; 
-- NLS_CHARACTERSET : AL32UTF8
-- NLS_NCHAR_CHARACTERSET : AL16UTF16

select VALUE$ from sys.props$ where name='NLS_LANGUAGE';
SELECT * FROM nls_database_parameters where parameter = 'NLS_LANGUAGE'
-- AMERICAN
</code>

  * SQL을 이용한 변경 - 아래는 ''KO16MSWIN949''로 변경하는 예인데, 되도록 ''AL32UTF8'' 사용할 것.
<code sql>
update props$ set value$='KO16MSWIN949' where name='NLS_CHARACTERSET';
update props$ set value$='AL16UTF16' where name='NLS_NCHAR_CHARACTERSET';
update sys.props$ set value$='KOREAN_KOREA.UTF8' where name='NLS_LANGUAGE';
commit;

-- 모든 데이터베이스에 일괄 적용. 아래 작업을 안하면 오류 발생했음.
-- ORA-06552: PL/SQL: Compilation unit analysis terminated
-- ORA-06553: PLS-553: 알 수 없는 문자 집합 이름입니다
-- ORA-06553: PLS-553: character set name is not recognized

ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;

ALTER DATABASE CHARACTER SET INTERNAL_USE KO16MSWIN949;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;

-- 재시작
shutdown immediate
startup
</code>

  * 문자셋 종류
    * ''KO16KSC5601'' : 완성형 한글, 한글2350글자. 한글 2bytes 차지
    * ''KO16MSWIN949'' : CP949, 모든 한글 표현, 한글 2bytes 차지
    * ''AL32UTF8'' : UTF-8, 한글 3bytes 차지
  * 참고
    * [[https://db.necoaki.net/134|db.necoaki.net :: Oracle Character set 에 관하여]] 문자셋 그 자체에 대한 설명
    * [[https://rocksea.tistory.com/241|[Oracle] Character set 변경.]]
    * [[https://jehna.tistory.com/34|[Oracle] 문자셋(characterset) 확인 및 변경 :: Jehna :)]]

===== TIMEZONE =====
  * [[https://www.carajandb.com/en/blog/2016/be-careful-when-setting-the-dbtimezone/|Be careful when setting the DBTIMEZONE! | CarajanDB]]
  * [[https://www.oracletutorial.com/oracle-date-functions/oracle-dbtimezone/|Oracle DBTIMEZONE]]

===== 권한 Grant =====
  * [[https://hello-nanam.tistory.com/1|나남나여 :: 오라클 테이블 권한 부여 ( GRANT )]]
  * [[https://m.blog.naver.com/PostView.nhn?blogId=heartflow89&logNo=221002112762&proxyReferer=https:%2F%2Fwww.google.com%2F|[Oracle/오라클] 권한 부여(GRANT) / 제거(REVOKE) / 조회 / 계정(사용자) 생성 : 네이버 블로그]]
  * [[https://jink1982.tistory.com/34|[Oracle] 권한 생성(GRANT), 권한 삭제(REVOKE) :: 돼민이]]
  * [[http://develop.sunshiny.co.kr/736|Oracle - 디폴트 롤, DBA, CONNECT, RESOURCE :: You've got to find what you love.]]

==== 기본 Role ====
  * ''CONNECT'' : DB 접속 권한
  * ''RESOURCE'' : 객체(생성, 수정, 삭제), 데이터(입력, 수정, 조회, 삭제) 권한
  * ''DBA'' : 절대 권력?

==== Table 단위 권한 부여 ====
<code>
-- 부여
GRANT SELECT, INSERT, DELETE, UPDATE ON [TABLE NAME] TO [USER];

-- 해제
REVOKE SELECT, INSERT, DELETE, UPDATE ON [TABLE NAME] FROM [USER];
</code>

===== Identifier too long =====
  * ''ORA-00972: identifier is too long(Oracle 데이터베이스 오류 972: ORA-00972: 식별자가 너무 깁니다)''
  * 컬럼명, 테이블명 등의 식별자 길이는 **30byte**로 제한돼 있다.
  * 한글 컬럼명 등의 경우 인코딩에따라 ''UTF-8''은 한 글자가 3byte 혹은 ''CP949'' 일 경우에는 2byte를 차지하기 때문에 생각보다 길어지게 된다.
  * JPA/Hibernate 에서 발생한다면 [[java:hibernate:dialect|Hibernate Dialect]] alias 참조