퐁숑퐁숑

윈도우10에 오라클 12c를 설치

오라클사이트

winx64_12102_database_1of2

winx64_12102_database_2of2

Windows10환경에서 오라클12c를 설치를 해보았다.

전자메일 무시하고 다음으로 넘기자

메일제공하기싫으면 무시하고 다음 예  

데이터베이스 생성 및 구성

서버클래스

단일 인스턴스 데이터베이스 설치

고급설치

영어/한국어

 

엔터프라이즈 에디션

Oracle 홈 사용자 지정(Microsoft Windows 운영 체제 전용)
 

Windows 내장 계정을 사용하거나 표준 Windows 사용자 계정(관리자 계정 아님)을 지정하여 Oracle 홈을 설치하고 구성하십시오. 이 계정은 Oracle 홈에 대한 Windows 서비스를 실행하는 데 사용됩니다. 관리 작업을 수행하기 위해 이 계정으로 로그인하지 마십시오.

기존 Windows 사용자 사용

계정은 Windows 로컬 사용자, Windows 도메인 사용자 또는 Windows MSA(관리 서비스 계정)일 수 있습니다. Windows 로컬 또는 도메인 사용자에 대해 사용자 이름과 비밀번호를 제공해야 합니다. 관리 도메인 계정인 MSA 계정의 경우 사용자 이름만 제공하면 됩니다.

Oracle RAC Database 및 Oracle Grid Infrastructure 설치의 경우 Windows 도메인 사용자 계정만 사용할 수 있습니다.

새 Windows 사용자 생성

Oracle Universal Installer를 사용하여 생성할 Windows 로컬 사용자의 사용자 이름과 비밀번호를 제공합니다. 비밀번호를 확인합니다. 새로 생성된 사용자는 Windows 컴퓨터에 대화식 로그온 권한이 거부되어 있습니다. 그러나 Windows 관리자가 다른 Windows 계정과 마찬가지로 이 계정을 관리할 수 있습니다.

Windows 내장 계정 사용

사용자 이름이나 비밀번호가 필요하지 않습니다. Oracle은 Windows 내장 계정(LocalSystem 또는 LocalService)을 사용하여 Windows 서비스를 생성합니다.

데이터베이스 서버 설치의 경우, Oracle은 보안 향상을 위해 Oracle 홈 사용자로 (Windows 내장 계정 대신) 표준 Windows 사용자 계정을 사용할 것을 권장합니다.

다양한 유형의 Windows 계정에 대한 자세한 내용은 Microsoft 설명서를 참조하십시오

예

기본적 위치는 권한때문에 D로 잡힐것이다.

D로 설치해도 무난하다.

일반용/트랜잭션 처리

다음
 

자동메모리 관리를 사용으로 설정 해도 상관없음

문자집합은 기본값 사용

 

다음

다음

다음

다음

 

모든 계정에 동일한 비빌먼호 사용

예

설치

설치 도중 에러가 발생할수있다.

파일이 없다고 나올경우

winx64_12102_database_2of2 에있는

winx64_12102_database_2of2\database\stage 아래에있는 파일을 복사해서

winx64_12102_database_1of2\database\stage\Components 에 넣어주자

확인

닫기

설치완료

이제부터 가장 중요한 확인 순서

​

cmd 창을 관리자 권한으로 접속

대소문자 구분 없애기

alter system set sec_case_sensitive_logon=false;
  

listenr.ora설정

설치도중 ora-28547를 만날수 있다. 

리스너에 HOST를 자신의 PC이름으로 변경하자

위치 : 제어판\시스템 및 보안\시스템

컴퓨터 이름: DESKTOP-XXXX

ORACLE SQL DEBELOPER

테스트 접속 성공

1년 전에 학원에서 DB 만질 때 이후로 DB는 설치만 하고 건들지 않았는데 

모처럼 한 번 해보기로 했다.

학원과의 차이점은 그 때는 오라클 11g를 사용했는데, 이제는 오라클 12c를 사용한다는 것이다.

처음 접속하는 것부터 까먹었는데 cmd에서 오라클로 접속하기 위해서는 sqlplus를 입력해야 한다.

그런데 나는 새 계정을 만들어야 하기 때문에 

명령어를 입력하여 

sys 계정으로 접속한다.

그러면 sys 계정에 접속됨고 동시에 sql 명령어를 입력할 수 있게 된다.

계정을 만들기 위해서는 다음과 같은 sql 명령어를 입력한다.

반드시 마지막에 세미콜론을 붙여야 한다. 

그렇지 않으면 명령어가 끝나지 않은 것으로 인식해서 더 받을려고 하며 

이를 그냥 넘기면 결국 실행되지 않는다.

그런데 여기에 문제가 있었는데 

"ORA-65096: 공통 사용자 또는 롤 이름이 부적합합니다."란 오류가 떴었다.

검색해보니 12g에서는 로컬 사용자가 아닌 공통 사용자 생성시에는 

계정이름 앞에 c##을 붙여줘야 한다고 한다.

이를 붙여주니까 계정이 정상적으로 생성이 되었다.

계정을 생성했다고 해서 바로 쓸 수 있는 것은 아니다.

먼저 계정의 잠금을 풀어줘야 한다.

다음으로 계정에 로그인 및 테이블 생성을 할 수 있게 접근 권한 및 자원을 부여한다.

계정이 제대로 만들어졌는지 확인하려면 다음 명령어를 입력하여 접속해본다.

여기에는 세미콜론이 없어다 된다.

여기에서 테이블도 만들고 수정도 되겠지만, 이미 우리에겐 SQL Developer라는 좋은 툴이 있다.

위의 과정도 잘 기억은 안 나지만 Developer에서 더 쉽게 할 수 있을 것이다.

여튼 이제는 Developer를 사용하기 위해 계정을 연결하도록 한다.

oracle이 설치된 경로에 NETWORK\ADMIN\ 폴더에 보면 oracle에서 network와 관련한 여러 설정파일들이 있다.

그 중 'tnsnames.ora'라는 파일이 있는데... 이 파일은 간단히 설명하자면 클라이언트가 oracle 서버에 접속하기 위해 필요한 일종의 설정파일이라고 보면 된다. 

근데 문제는 'ora-12541 : TNS 리스너가 없습니다'라는 에러가 나올 경우인데... 

별도로 'tnsnames.ora' 파일을 셋팅하지 않은 경우라면 oracle 리스너가 동작하지 않고 있을 가능성이 높다. 'tnsnames.ora'파일을 셋팅을 한 일이 있다면 'tnsnames.ora'파일 설정이 올바르게 되어 있는지도 고려해 봐야 할 것이다.

1. oracle 리스너 컨트롤 툴에 접속

2. start

3. 오류발생!?!?

4. 알고 보니 예전에 'tnsnames.ora'파일을 바꿨던게 host 주소가 바뀌면서 문제가 생긴 것...  다시 수정하고 start

oracle을 쓰지 않는지라 가끔씩 심심할 때마다 만지면 기억이 없다. 리스너 컨트롤 생각은 하면서 명령어가 기억이 나지 않으니;; 매번 같은 검색을 또 하고 있다. 이번엔 좀 더 오래 남겠지;;

오라클 sysdba 접속 방법

* conn /as sysdba;

또는,

* connect sys/oracle as sysdba;

현재 포트 확인 명령

* select dbms_xdb.gethttpport() from dual;

포트번호를 9090으로 변경

* exec dbms_xdb.sethttpport(9090);

오라클 11g hr 계정 활성화

* conn /as sysdba;

* alter user hr identified by hr account unlock;

→ sys계정으로 로그인해야만 위의 명령어 사용가능. 두번째에 나오는 hr은 password. 원하는 password 입력

* conn hr/hr;

기타 오라클 계정 생성

* create user id identified by password;

→ id와 password에 원하는 id, 비밀번호 입력

비밀번호를 변경하고 싶다면,

* alter user id identified by password;

계정에 권한 부여하는 명령어

오라클 로그인 권한 부여

* grant create session to id;

테이블 생성 권한 부여

* grant create table to id;

테이블 스페이스 생성 권한 부여

* grant create tablespace to id;

오라클 12c버전 부터는 계정 생성및 권한 부여할 때 id값 앞에 c##을 붙여줘야한다.

CONCAT 함수는 입력되는 두 문자열을 연결하여 반환한다.

문자열의 데이터타입은 CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB이며, 두 문자열 데이터 타입이 같으면, 그와 같은 데이터타입을 반환한다. 하지만 입력되는 두 문자열의 데이터타입이 다를 경우 NCLOB 데이터타입으로 반환된다.

SELECT wm_concat(emp_name)
FROM dept_tb
GROUP BY dept_name;

※ 단, 여기서 select 결과가 다음과 같이 'oracle.sql.CLOB@e62121' ? clob의 주소값을 반환하고 있는 경우가 있는데... 

이때, TO_CHAR로 감싸서 반환해주면 해결된다! 혹시 그래도 해결이 안된다면... 전자정부프레엠워크 사용시 

Oracle의 경우는 iBatis를 위한 sqlMapClient bean 설정 시 다음과 같이 lobHandler를 등록해 주어야 한다. 

(ex: context-sqlMap.xml)  보통은 공통 잡는 분들이 프레임워크 잡을떄 당연 추가하겠지만 혹시나..!!! 

 <bean id="lobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler" lazy-init="true" />

콤마 제거하고 조회 하고 싶을땐 (replace나 translate 함수) : 문자열을 치환해 주는 기능을 하는 표현식

※ 단순 문자열 치환 : TRANSLATE('대상문자열', '비교문자', '바꿀문자')?

SELECT translate(wm_concat(emp_name),'a,','a ')
FROM dept_tb
GROUP BY dept_name;

[결과]?

==============================
dept_name || emp_name
==============================
인사과             영수철수민수길동
=============================

WM_CONCAT 함수에 대해 정리해 보았다. 알면 정말 유용한 오라클 함수들!! 모르면 정말 어떻게 해야할지 

많은 고민들로 시간을 보내는 경우가 허다한데... 한 번 잘 정리해 놓고 이용하면 좋을 듯 싶다!

오라클 JOB을 걸면 일정 주기마다 DML(INSERT,UPDATE,DELETE) 작업을 수행하게 하여 TABLE에 데이터 작업을 해줄 수 있다. 일정 주기나 시간이 아니라, TABLE에 어떤 이벤트가 발생했을 때 알아서 오라클에서 내가 정의 해놓은 작업들을 수행해 준다면 얼마나 좋을까? .... 바로 이때 사용 할 수있는 오라클 구문에는 트리거(TRIGGER)가 있다. 

1. 트리거(TRIGGER) 정의

- 테이블에 어떤 이벤트가 발생했을 때, 자동으로 사용자가 정의한 PL/SQL 명령을 실행 할수 있는 구문이다. 

2. 트리거(TRIGGER)  생성 구문

CREATE [OR REPLACE] TRIGGER 트리거명 [BEFORE | AFTER]
triggering-event[Insert,delete,update] ON 테이블명
[Referencing OLD AS {변경전 값을 참조하는 변수명} NEW AS {변경 후 값을 참조하는 변수명}]
[FOR EACH ROW]

[WHEN (condition)]?
DECLARE
--변수선언
BEGIN
--트리거 PL/SQL 명령 작성
EXCEPTION
END;

#구문에 대한 상세 설명#

우선 MERGE 란 뜻은 무엇인가? 충돌나지 않게 합친다는 개념이다.   SVN에서 MERGE는 소스들의 충돌을 방지하고 적절하게  누락되지 않게 통합하기 위해 사용한다.  오라클에서 MERGE 또한 같은 개념이다. TABLE에 존재하는 데이터는 그대로 변경만 하고 없는 데이터는 삽입을 하여 적절하게 통합하기 위한 예약어이다. 즉,

MERGE INTO 구문은 대상 테이블 해당 KEY에 맞는 데이터가 이미 존재하면 UPDATE!!,존재하지 않으면 INSERT를 하여 테이블 ROW가 충돌나지 않으며 데이터를 UPDATE,INSERT 등의 작업을 한번에 해줄 수 있다. 

#오라클 MERGE INTO 문법#

MERGE INTO [1. 테이블 명 혹은 VIEW명] - Update또는 Insert할 테이블 명 혹은 뷰

USING [2. 조회서브쿼리]      --(만약 INTO절의 동일 테이블이라면  dual 사용)

ON [1과2의 조인 조건]  - 조인 조건의 KEY와 일치여부[UPDATE/INSERT 조건은 바로 ON절에 의해 결정]

WHEN MATCHED THEN   -일치되는 경우 UPDATE

UPDATE SET -- ※조인조건(on)절에 사용한 컬럼은 UPDATE가 불가하다!!

[컬럼1] = [값1],

[컬럼2] = [값2]

...

WHEN NOT MATCHED THEN -일치 안 되는 경우 INSERT

INSERT(컬럼1, 컬럼2...)

VALUES(값1, 값2...)

※오라클 9i 버전 이상부터 사용이 가능하다.

※조건절에 사용한 컬럼은 UPDATE가 불가하다!!

그리고 놀라운 사실!! UPDATE 구문만 가능한게 아니라 상황에 따라서는 DELETE 구문도 가능하다! 잘 쓸 경우는 없지만. 존재하는 ROW는 삭제하고 존재하지 않는 ROW는 삽입해야하는 경우라든지 다양하게 응용 하여 한번에 삽입 삭제 변경 처리가 가능하다.

오라클에서 MERGE  INTO 구문은 자주 사용하는 쿼리 구문이니 익혀두고  이용하면 좋을 듯 하다. 단, 9i 이상 오라클에서 사용 가능하다고 했지만 9i에서의 Merge 구문은 불완전 (DELETE구문도 10g 부터 사용가능 )하다. 또한 구문상 제약사항이 많기때문에 ON 구문에서 조건식을 잘못 넣으면 작동이 안된다; 10g 이상부터 구문을 자유롭게 이용할 수있다. 또한 Trigger 발생이 안된다는 것도 주의해야 하겠다. 그리고 당연한 애기겠지만 단순 INSERT 구문이나 UPDATE구문을 써도 문제가 안되는데 궂이 MERGE INTO를 남발? 하는것도 성능이 떨어지기 때문에(100~200만건 이상 TABLE에서는 느려지는듯 ㅜㅜ) 꼭 필요할 때 잘 쓰도록 하자^^

DBMS_JOB이란 오라클에서 주기적으로 수행되는 백업작업이나, 쿼리나 프로시져등의 JOB을 시간단위나 일 단위나 월 단위 등 주기적인 예약 작업으로 등록하여 동작할 수 있도록 하는 스케줄러이다.UNIX에서 CRONTAB을 돌리 듯이 DB에서 스케쥴러를 등록하여 일정 주기별로 원하는 작업을 할 수 있다는 점에서 유사한데 차이점은 CRON JOB은 OS가 직접 관리하고 실행하는 반면 DBMS_JOB에 등록된 JOB은 오라클이 관리한다.

[출처] [Oracle]오라클 잡 스케줄러 생성 - Job Scheduler (DBMS_JOB 패키지)|작성자 티시포스

위의 출처에 잘 정리되어져 있어서 블로그에 정리해 담아보았다.

* DBMS_JOB 패키지 내 프로시저 구성은 다음과 같다.

    1. SUBMIT : DB에 새로운 JOB을 추가하는 프로시저

    2. REMOVE : DB에 추가된 JOB을 삭제하는 프로시저 

    3. CHANGE : DB에 저장되어 있는 JOB의 field들을 변경하는 프로시저

    4. WHAT : JOB이 수행하는 작업을 변경하는 프로시저

    5. NEXT_DATE : JOB이 Schedule되어 Timer에 의해 자동으로 실행될 때를 변경하는 프로시저

    6. INTERVAL : JOB 실행 주기 파라미터를 변경하는프로시저

    7. BROKEN  : DB에 저장되어 있는 JOB의 상태를 정상 or Broken 상태로 설정하는 프로시저

    8.  RUN : JOB을 현재 시점에서 즉시 수행시키는 프로시저

1. SUBMIT

DB에 새로운 JOB을 추가하는 프로시저

오라클 기본 셋팅 확인

 여기에 'JOB_QUEUE_PROCESSES'의 값이 '0' 이면 스케줄러가 작동하지 않는다. (기본셋팅)

아래와 같이 변경

변경이 되었으면 JOB SCHEDULER를 생성한다.

DBMS_JOB 패키지 내 프로시저 이용

* job : 실행할 job number

* what - 실행할 PL/SQL 프로시저(procedure) 명 혹은 psm 문장의 sequence

* next_date - job을 언제 처음 시작할 것인지 지정한다. date type으로 evaluate되는 문자열 입력(SYSDATE)

* interval - job을 수행한 후, 다음 실행시간까지의 시간을 지정한다. 위 셋팅은 매일 오전 3시 마다 실행한다.

* no_parse - true 이면 submit시에 job을 parsing하지 않는다.

마지막으로 로그(Job Number)를 찍어줌으로써 스케줄러가 정상적으로 돌아가는지 확인할 수 있다.

* job Interval 설정

SYSDATE+7 : 7일에 한번 씩 job 수행

SYSDATE+1/24 : 1시간에 한번 씩 job 수행

SYSDATE+30/ : 30초에 한번 씩 job 수행(24: 시간 당, 1440(24x60):분 당, 86400(24x60x60):초 당 )

TRUNC(SYSDATE, 'MI')+8/24 : 최초 job 수행시간이 12:29분 일 경우 매시 12:29분에 job 수행

TRUNC(SYSDATE+1) : 매일 밤 12시에 job 수행

TRUNC(SYSDATE+1)+3/24 : 매일 오전 3시 job 수행

NEXT_DAY(TRUNC(SYSDATE),'MONDAY')+15/25 : 매주 월요일 오후 3시 정각에 job 수행

TRUNC(LAST_DAY(SYSDATE))+1 : 매월 1일 밤 12시에 job 수행

TRUNC(LAST_DAY(SYSDATE))+1+8/24+30/1440 : 매월 1일 오전 8시 30분

* job queue 정보 VIEWING

SELECT * FROM DBA_JOBS

SELECT * FROM USER_JOBS

SELECT * FROM ALL_JOBS

2. REMOVE

DB에 추가된 job을 삭제하는 프로시저

* job : 삭제할 job number

3. CHANGE

DB에 저장되어 있는 job의 field들을 변경하는 프로시저

* job : 실행할 job number

* what : 실행할 PL/SQL procedure 혹은 psm 문장의 sequence

* next_date : job을 다음 수행할 시간

* interval : job을 수행 후 nex_date를 update하기 위한 expression. date type으로 evaluate되는 문자열

4. WHAT

job이 수행하는 작업을 변경하는 프로시저

* job : 실행할 job number

* what : 실행할 PL/SQL procedure 혹은 psm 문장의 sequence

5. NEXT_DATE

job이 schedule되어 Tibero에 의해 자동으로 실행될 때를 변경하는 프로시저

* job : 실행할 job number

* next_date : job이 schedule되어 실행될 시간

6. INTERVAL

job 실행주기 파라미터를 변경하는 프로시저

* job : 실행할 job number

* interval : job을 수행 후 next_date를 update하기 위한 expression. date type으로 evaluate되는 문자열

7. BROKEN

DB에 저장되어 있는 job의 상태를 정상 or Broken 상태로 설정하는 프로시저

* job : 실행할 job number

* broken : job이 broken 된 경우 true, 정상 상태인 경우 false

8. RUN

job을 현재 session에서 즉시 수행시키는 프로시저.

job이 broken되어 있어도 실행하고, 실행에 성공한 경우 job을 정상 상태로 변경한다.

* job : 실행할 job number

Index의 정의를 보면

1) 조회속도를 향상시키기 위한 데이터베이스 검색 기술

2)색인이라는 뜻으로 해당 테이블의 조회결과를 빠르게 하기 위해 사용.

즉 인덱스가 필요한 이유는 인덱스를 생성해 줌으로써 조회 속도를 빠르게 할 수 있다.

INDEX를 테이블의 특정 컬럼에 한개이상 주게 되면 INDEX TABLE이 따로 만들어 지고,

인텍스 컬럼의 로우값과 rowid 값이 저장되며 로우값은 정렬된 트리 구조로 저장시켜 두었다가

검색시 좀더 빠르게 해당 데이타를 찾는데 도움을 준다.

*테이블을 생성하고 컬럼을 만든 후 데이타를 삽입하면 하나의 ROW가 생성되며 이 ROW는 절대적인 주소를 가지게 되는데 이를 ROWID라고 한다.

하지만 DML 명령을 사용 할때는 원본 TABLE은 물론 INDEX TABLE에도 데이타를 갱신시켜 주어야 하기 때문에 UPDATE, INSERT, DELETE (DML) 명령을 쓸때 속도가 느려진다는 단점이 있다. 

그렇기 때문에 무조건 인덱스를 생성한다고 좋은 것 많은 아니고 꼭 필요할 때만 분별하여 생성해 주어야 하는게 KEY POINT라 할 수 있겠다. 그럼 언제 인덱스(INDEX)를 생성해 주면 좋을까?

우선 데이터가 많이 쌓일거라고 예상되는 경우 혹은 많이 쌓여 있어 현재 화면에서 조회 속도가 너무 느릴때 인덱스 생성을 한다. 그리고 조회결과가 전체 데이터수의 3~5% 미만일 경우에는 인덱스 스캔이 효율적이고 적은 COST로 빠르게 데이터를 찾아낼 수 있다.(전체범위 3~5% 이상 되면 인덱스 스캔보다 풀스캔이 훨씬 유리)

쿼리 플랜을 떠보면 다음과 같은 단어로 확인 할 수 있다

인덱스 스캔 = Index Scan

풀 스캔 = Full Scan

반대로 INDEX생성이 불필요한 경우는?

1) 데이터가 적은(수천건 미만) 경우에는 인덱스를 설정하지 않는게 오히려 성능이 좋다.

2) 조회 보다 삽입, 수정, 삭제 처리가 많은 테이블

3) 조회결과가 전체행의 15% 이상 읽어들일 것으로 예상될때

INDEX 생성 문법은 간단하다

1) 단일 인덱스 지정

CREATE INDEX 인덱스 명 ON table_name (컬럼명)

2) 다중 인덱스(복합 인덱스) 지정

CREATE INDEX 인덱스 명 ON table_name (컬럼명1,컬럼명2,컬럼명3)

※ 복합 인덱스로 지정해준 테이블에서 복합 인덱스를 타게 하려면 복합 인덱스로 준 컬럼

을 조회쿼리에서 모두 조회조건에 사용해야 인덱스를 탈 확률이 높아진다.

예를 들면 다음과 같다

create index index_make1_dx on account(id);

create index index_make2_dx on account(name, date, dept_name);

※ 인덱스는 NOT NULL 값에 대해서만 생성되기 때문에 null 여부를 체크하는 비교문은 인덱스를 사용하지 못함.

INDEX 가능 컬럼

인덱스는 모든 컬럼에 적용가능하다.

그런데 오라클은 가공시킨 컬럼에도 적용가능하다. 아래 참고

CREATE INDEX IDX_NAME ON TABLE_NAME(ROUND(PRICE1-PRICE2));

ROUND(PRICE1-PRICE2) 는 컬럼은 아니지만 컬럼을 가공해서 만든 것이다.

이런 가공컬럼은 다음과 같은 SQL 쿼리로 인덱스를 탈수 있다.

SELECT * FROM TABLE_NAME WHERE ROUND(PRICE1-PRICE2) > 0

※ 인덱스 줄때의 가공컬럼과 같아야 한다.

SQL 쿼리의 INDEX SCAN 유무 체크 방법

1. 상용 DB 관리도구를 이용하는 방법

PL/SQL Developer, Toad 같은 도구에서 SQL문을 작성하고 실행하면 Explain plan 에서 확인 가능하다.

INDEX를 사용해야 할 컬럼?

1) where절이나 조인 조건에서 자주 사용되는 열에 생성

2) 열은 광범위한 값을 포함 할 때

3) 열은 많은수의 null값을 포함 할 때

4) 조회결과가 전체행의 2-4% 보다 적게 읽어들일 것으로 예상될 때

--테이블이 클때 적은 양의 로우를 검색할때 인덱스를 준다다. 적은 양을 검색하는데 테이블을 전체 풀스캔하면 시간이 오래 걸려서 꼭 index를 줘야 한다다.

INDEX를 사용하지 말아야할 컬럼?

1) 테이블에 데이타가 작은 경우

2) where절에 자주 사용되지 않는 열은 사용되지 않는다.

3) 조회결과가 전체행의 2-4% 이상을 읽어들일것으로 예상될때

4) 테이블이 자주 갱신될때

INDEX 생성시 고려사항해야 할 사항들은 무엇일까?

인덱스가 적용된 컬럼이 조건식에서 인덱스를 탈수있게 하려면 해당컬럼을 가공하지않거나 연산을 하지 않은 상태에서 비교해야 인덱스를 탄다.

예를들어 연락처컬럼의경우(010-1234-5678) 010 만 따로 문자열을 잘라(가공) 조건검색하면 인덱스를 타지 않는다.

왜냐하면 인덱스 컬럼에 변형이 일어나면 상대값과 비교되기 전에 먼저 가공이 된 후에 비교된다.하지만 인덱스는 가공되기 전의 값으로 생성되어 있기 때문에 당연히 인덱스를 사용할 수 없게 된다. 여기에서 외부적(External) 변형이란 사용자가 인덱스를 가진 컬럼을 어떤 SQL함수나 사용자 지정함수(User Defined Stored Function), 연산, 결합(||) 등으로 가공을 시킨 후에 발생되는 것이며 이러한 경우는 인덱스를 탈수 없어 변형이 일어나지 않도록 제대로 기술해야 한다.

그렇기 때문에 010과 1234과 5678를 각각의 컬럼으로 만들어 저장한 후 각각의 컬럼에 인덱스를 주면 아무런 가공없이 조건 검색이 가능하므로 인덱스를 탈수 있다.

테이블 컬럼에 인덱스가 있따면 테이블 컬럼을 변경하는것보다 비교값을 변경하여

비교해주는데 좋다. 왜냐면 그래야 인덱스를 타기 때문이다.

WHERE to_char(joindate, 'yyyymmdd') = '20150131'

WHERE joindate = TO_DATE('20150131','yyyymmdd')

인덱스를 타지 않는 CASE

SELECT * FROM ACCOUNT WHERE A_DAY+1>2;

SELECT * FROM ACCOUNT WHERE SUBSTR(A_STRDAY,1,1)='월';

SELECT * FROM EMP WHERE EMP_ID = NVL(EMP_ID,'10');

아래는 인덱스를 타는 CASE

SELECT * FROM ACCOUNT WHERE A_STRDAY='월요일';

SELECT * FROM ACCOUNT WHERE A_DAY>2;

SELECT * FROM EMP WHERE EMP_ID = NVL('10','20');

SELECT * FROM ACCOUNT WHERE A_STRDAY like '월요일%';

※ 첫번째 쿼리부터 인덱스효과가 크게 나타나는 순서

INDEX 타는 경우와 안타는 경우

안타는 경우

1. SELECT * FROM emp WHERE empno <> '1010035';

오라클에서 exists를 이용하여 타게 할수있다?

SELECT * FROM emp WHERE not exists

(select empno FROM emp WHERE empno = '1010035' and a.empno = b.empno);

INDEX 삭제 방법은 간단하다.

DROP INDEX INDEX_NAME;

※ TABLE이 삭제되면 INDEX도 삭제된다.

※ 인덱스의 소유자와 DROP ANY INDEX권한을 가진 사람만 인덱스 삭제가 가능합니다.

?이상으로 인덱스에 대한 정리를 마치고자 한다. 나는 위에 정리된 인덱스 개념처럼 조회되는 데이터 양은 3~5%밖에 안되며 엄청 느린.. 그리고  Explain plan 에서 확인 했을때 FULL SCAN을 타고 있는 상황에서 해당 컬럼에 INDEX를 생성해 주어 4분 이상 돌아가던 쿼리 속도를 4초 이내로 줄여 개선 할 수 있었다. 뿌듯한 성과였다. 이제 단순 개발 뿐만이 아니라 시스템의 성능과 품질을 고려할 수 있는 개발자가 되기위한  한걸음 도약이였다.

OCP라는 자격증을 취득 후 그다음 단계는 OCM이라는 자격증을 취득할 수 있다. 2010년도 그당시만 해도 OCM 자격증 취득자가 별로 없었다. 덤프를 외워서 취득할 수 있는 OCP와 달리 OCM은 실습도 있고 상당히 까다롭기 때문에 그만큼 자격증 취득 여부에 따라 회사에 들어갈때 그만큼 가산점을 얻을 수 있었다. OCM을  취득하기 위해 가장 첫번째 실습 시나리오가 바로 DB 수동 설치이다. 오늘은 DB 수동 설치 후 EM을 추가하는 것 까지 정리해 보고자 한다.

[CONTENTS]

1. 수동 DB 생성 (OCM 0 scenario)
2. DBMS_SPACE_ADMIN 을 이용한 Dictionary → Local migration
3. Enterprise Manager 추가

--수동 DB 설치 (SID-testdb)

1. Nomount 수행을 위한 구성

Parameter file (수동으로 생성해야 하므로 pfile 생성.)

$] cd $ORACLE_HOME/dbs

$] vi inittestdb.ora

-다음과 같이 파라메터 값을 입력한다.

#testdb.__db_cache_size=83886080
#testdb.__java_pool_size=4194304
#testdb.__large_pool_size=4194304
#testdb.__shared_pool_size=67108864
#testdb.__streams_pool_size=0
#*.background_dump_dest='/u01/app/oracle/admin/testdb/bdump'
#*.compatible='10.2.0.1.0'
#*.core_dump_dest='/u01/app/oracle/admin/testdb/cdump'
#*.db_domain=''
#*.db_recovery_file_dest='/u01/oradata/flash_recovery_area'
#*.db_recovery_file_dest_size=2147483648
#*.job_queue_processes=10
#*.nls_language='KOREAN'
#*.nls_territory='KOREA'
#*.open_cursors=300
#*.processes=150
#*.remote_login_passwordfile='EXCLUSIVE'
#*.user_dump_dest='/u01/app/oracle/admin/testdb/udump'

--필수 파라메터 값

*.db_name='testdb'

*.db_block_size=8192

*.db_file_multiblock_read_count=16

*.control_files='/u01/oradata/testdb/control01.ctl','/u01/oradata/testdb/control02.ctl'

*.pga_aggregate_target=16777216

*.sga_target=167772160

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

> password file 생성

$ orapwd file = orapwtestdb password=oracle entries=5

>SID 변경

$ export ORACLE_SID=testdb

>필수 경로 사전 생성

ocp@orcl : /home/oracle> cd /u01/oradata/
ocp@orcl : /u01/oradata> mkdir testdb
ocp@orcl : /home/oracle> cd /u01/app/oracle/admin/
ocp@orcl : /u01/app/oracle/admin/> mkdir testdb
ocp@orcl : /u01/app/oracle/admin/> cd testdb
ocp@orcl /u01/app/oracle/admin/testdb> mkdir bdump udump cdump pfile adump

> $ sqlplus sys/oracle as sysdba

SQL> startup nomount

2. create database 구문 작성

$] vi credb.sql

다음과 같이 작성을 한다.

CREATE DATABASE testdb

LOGFILE

GROUP 1 ('/u01/oradata/testdb/redo01.dbf') SIZE 10M reuse,

GROUP 2 ('/u01/oradata/testdb/redo02.dbf') SIZE 10M reuse,

GROUP 3 ('/u01/oradata/testdb/redo03.dbf') SIZE 10M reuse

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

MAXINSTANCES 1

DATAFILE '/u01/oradata/testdb/system01.dbf' SIZE 600M reuse

AUTOEXTEND on NEXT 10M MAXSIZE UNLIMITED

UNDO TABLESPACE undotbs1

DATAFILE '/u01/oradata/testdb/undotbs1.dbf' SIZE 100M reuse

AUTOEXTEND on NEXT 10M MAXSIZE 1000M

sysaux DATAFILE '/u01/oradata/testdb/sysaux01.dbf' SIZE 600M reuse

AUTOEXTEND on NEXT 10M MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE temp

TEMPFILE '/u01/oradata/testdb/temp01.dbf' SIZE 50M reuse

CHARACTER SET KO16MSWIN949

;

$ sqlplus sys/oracle as sysdba

SQL> @credb.sql -- 4분정도 소요

> database 상태 확인

SQL> select instance_name,status from v$instance;

> 유저 설정

SQL> alter user sys identified by oracle

SQL> alter user system identified by oracle

3. DATABASE INSTALL

$] vi dbinstall.sql

다음과 같이 입력한다

@@$ORACLE_HOME/rdbms/admin/catalog.sql

@@$ORACLE_HOME/rdbms/admin/catproc.sql

conn system/oracle

@@$ORACLE_HOME/sqlplus/admin/pupbld.sql

$] sqlplus sys/oracle as sysdba

SQL>@dbinstall.sql

--10분정도 소요됨

4. DBMS_SPACE_ADMIN을 이용한 Dictionary ->Local migration

SYS> select tablespace_name,extent_management form dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN
------------------------------ ----------
SYSTEM DICTIONARY
UNDOTBS1 LOCAL
SYSAUX LOCAL
TEMP LOCAL

4 rows selected.

-- 현재 system tablespace가 dictionary 방식으로 되어 있는 걸 확인할 수 있고,
이것을 local 방식으로 이전수행 할 수 있다.

SYS> shutdown immediate

SYS> startup mount exclusive

SYS>alter database open;

-- 나머지 tablespace 중에 dictionary 방식의 tablespace가 있다면
해당 tablespace를 read only로 변경한다. (ex ? alter tablespace xxxx read only; )

SYS>alter tablespace sysaux offline;

--제한모드 설정

SYS>alter system enable restricted session;

SYS>select logins from v$instance;

LOGINS
----------
RESTRICTED

1 row selected.

--이전 수행

SYS>exec DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

SYS> alter tablespace sysaux online;

SYS> alter system disable restricted session;

SYS> select tablespace_name,extent_management from dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN
------------------------------ ----------
SYSTEM LOCAL
UNDOTBS1 LOCAL
SYSAUX LOCAL
TEMP LOCAL

4 rows selected.

5. EM (Enterprise Manager Database Control) 수동 구성

ocp@testdb : /home/oracle> export ORACLE_SID=testdb
ocp@testdb : /home/oracle> echo $ORACLE_SID
testdb
ocp@testdb : /home/oracle> cd $ORACLE_HOME

--testdb의 EM설치 여부 확인
ocp@testdb : /u01/app/oracle/product/10.2.0/db_1> ls
assistants has log opmn root.sh
bin hs md oracore root.sh.old
cdata install mesg oraInst.loc slax
cfgtoollogs install.platform mgw ord sqlj
clone inventory network oui sqlnet.log
config javavm nls owm sqlplus
crs jdbc oc4j perl srvm
css jdk ocp.mycorpdomain.com_orcl plsql startup.log
ctx jlib ocp.mycorpdomain.com_prod precomp sysman
dbs jre odbc racg uix
demo ldap olap rdbms wwg
diagnostics lib OPatch relnotes xdk

--현재 testdb는 EM이 없음을 알 수 있다.

--사전 암호 변경

ocp@testdb : /u01/app/oracle/product/10.2.0/db_1> emca -config dbcontrol db -repos create

ocp@testdb : /u01/app/oracle/product/10.2.0/db_1> ls
assistants hs mesg oraInst.loc sqlj
bin install mgw ord sqlnet.log
cdata install.platform network oui sqlplus
cfgtoollogs inventory nls owm srvm
clone javavm oc4j perl startup.log
config jdbc ocp.mycorpdomain.com_orcl plsql sysman
crs jdk ocp.mycorpdomain.com_prod precomp uix
css jlib ocp.mycorpdomain.com_testdb racg wwg
ctx jre odbc rdbms xdk
dbs ldap olap relnotes
demo lib OPatch root.sh
diagnostics log opmn root.sh.old
has md oracore slax

ocp.mycorpdomain.com_testdb확인!!

ocp@testdb : /u01/app/oracle/product/10.2.0/db_1/install> cat portlist.ini
iSQL*Plus HTTP port number =5560
Enterprise Manager Console HTTP Port (orcl) = 1158
Enterprise Manager Agent Port (orcl) = 3938
Enterprise Manager Console HTTP Port (prod) = 5500
Enterprise Manager Agent Port (prod) = 1830
Enterprise Manager Console HTTP Port (testdb) = 5501
Enterprise Manager Agent Port (testdb) = 1831

==> http://192.168.100.100:5501/em