오라클 10g에서 오라클12c로 업그레이드를 해서 ojbdc.jar를 변경해줘야하는 일이 있었습니다.

저는 oracle12c에 맞는 ojdbc6.jar에서 ojdbc7.jar로 변경해주면 되는 줄 알았습니다.

하지만 ojdbc7.jar로 변경하고 select해본 결과 아래와 같은 오류가 발생했습니다.

Caused by: java.lang.UnsupportedClassVersionError: oracle/jdbc/driver/OracleDriver : Unsupported major.minor version 51.0

구글링 결과

jdk버전에 맞지 않는 lib를 추가해서 발생한 오류였습니다.

즉 jdk버전은 1.6인데 ojdbc7.jar를 사용해서 에러가 발생한 것

oracle사이트에 들어가 oracle12c에 맞는 jdk버전을 확인해봤습니다.

 ojdbc7.jar : jdk7 또는 jdk8버전 

 ojdbc6.jar : jdk6버전

https://www.oracle.com/technetwork/database/features/jdbc/jdbc-drivers-12c-download-1958347.html

결론은 jdk버전에 따라서 오라클 ojdbc.jar 버전도 달랐던 것이었습니다.

jdk 버전에 맞는 jar파일 다운받아서 사용해야합니다.

오라클 데이터를 개행으로 입력하는 법에 대해 알아보겠습니다.

1) select '안녕하세요. ' || '홍길동입니다.' from dual;

 : 안녕하세요. 홍길동입니다.

2) select '안녕하세요. ' || CHR(13) || CHR(10) || '홍길동입니다.' from dual;

 : 안녕하세요.

 : 홍길동입니다.

3) select '안녕하세요. ' || CHR(10) || CHR(13) || '홍길동입니다.' from dual;

 : 안녕하세요.

 : 

 : 홍길동입니다.

ex) 업데이트 쿼리

update tbl_opinion set opinion = '안녕하세요.' || CHR(13) || CHR(10) || '홍길동입니다.' || CHR(13) || CHR(10) || '잘부탁드립니다.'

where opinionid = '' 

;

MSSQL을 사용하다 보면 select를 하고 싶은데 조회 결과가 나오지 않거나 타임아웃에 걸려 커넥션이 끊어지는 경우가 잇습니다.

테이블에 락이 걸려서 이런 현상이 발생하곤 하는데요.

MSSQL의 경우 여러 사용자 중 한명이 락이 걸린 경우 역시 조회가 되지 않는다고 하네요.

락 걸린 쿼리를 조회하여 강제로 kill하는 방법에 대해 알아보겠습니다.

1. sp_lock 실행

 Mode값이 X인 경우 락이 걸린 것인데 이때 spid를 확인합니다.

2. 락 걸린 쿼리 조회

 dbcc inputbuffer(87)

3. spid이용하여 kill하기

 kill 87

락으로 의심되는 spid 조회하기

SELECT p.status ,p.program_name ,p.hostname ,p.spid ,p.blocked ,p.kpid

    ,p.cpu ,p.physical_io ,p.waittype ,p.waittime ,p.lastwaittype

    ,p.waitresource ,p.dbid ,p.uid ,p.memusage ,p.login_time ,p.last_batch

    ,p.ecid ,p.open_tran ,p.sid ,p.hostprocess

    ,p.cmd ,p.nt_domain ,p.nt_username ,p.net_address

    ,p.net_library ,p.loginame ,p.context_info ,p.sql_handle

    ,p.stmt_start ,p.stmt_end

FROM master..sysprocesses p

WHERE (

        STATUS LIKE 'run%'

        OR waittime > 0

        OR blocked <> 0

        OR open_tran <> 0

        OR EXISTS (

            SELECT *

            FROM master..sysprocesses p1

            WHERE p.spid = p1.blocked

                AND p1.spid <> p1.blocked

            )

        )

    AND spid > 50

    AND spid <> @@spid

ORDER BY CASE

        WHEN STATUS LIKE 'run%'

            THEN 0

        ELSE 1

        END

    ,waittime DESC

    ,open_tran DESC

자바를 이용해 sha256 함수를 만들어 보겠습니다.

1. 오라클에 자바 클래스를 생성합니다.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SHA256PasswordEncoder{" AS 

import java.lang.*;

import java.io.*;

public class SHA256PasswordEncoder{

public static String encode(String passwd){

try {

if (passwd != null && !passwd.equals("")) {

java.security.MessageDigest digest = java.security.MessageDigest.getInstance("SHA-256");

byte[] bytData = passwd.getBytes();

digest.update(bytData);

byte[] buffer = digest.digest();

passwd = "";

for (int i = 0; i < buffer.length; i++) {

passwd = passwd + Integer.toHexString(buffer[i] & 0xFF).toUpperCase();

}

}

      return passwd;

} catch (Exception e) {

throw new RuntimeException(e);

}

}

}

2. 만든 자바클래스를 이용하여 함수를 만듭니다.

create or replace function gnuhash_sha256(passwd VARCHAR2) RETURN VARCHAR AS 

language java name 'SHA256PasswordEncoder.encode(java.lang.String) return java.lang.String';

3. 함수가 정상적으로 생성되었는지 테스트합니다.

select gnuhash_sha256('a') sha256_passwd

from dual

;--CA978112CA1BBDCAFAC231B39A23DC4DA786EFF8147C4E72B9807785AFEE48BB

함수가 정상적으로 생성되었음을 확인할 수 있습니다.

패스워드 SHA256으로 암호화하기

오라클 SQL Developer에서 날짜(Data타입)는 19/01/12 이런식으로 조회가 되어 

시간을 조회하고 싶은 경우 설정을 통해 변경을 할 수 있습니다.

도구 -> 환경설정 -> 데이터베이스 -> NLS 을 선택합니다.

변경하지 않았더라면 날짜 형식 RR/MM/DD으로 되어 있을 겁니다.

형식을 RR/MM/DD HH24:MI:SS로 변경해주면 시간(시/분/초)까지 조회가 가능합니다.

오라클에서 쿼리를 조회할 때 '' 홑따옴표를 이용해서 조회를 하게됩니다.

그렇다면 홑따옴표(') 가 들어간 데이터는 어떻게 조회하면 될까요?

홑따오표를 '' 연속으로 입력하면 됩니다.

예시를 통해 확인해보겠습니다.

SELECT * FROM 테이블명

WHERE title = '190101 ''보고서'' 테스트문서'

;

따옴표가 들어간 데이터를 조회할 수 있습니다.

오라클에서 실수로 삭제하거나 오데이터를 업데이트하고 commit하였을때 

복원을 시켜야하는 경우가 있었는데요.

이때 특정시간으로 되돌릴 수 있는 Oracle Flashback 기능이 있습니다.

이 플래시백 기능을 통해 삭제하거나 업데이트 전의 데이터를 조회하여 복구가 가능합니다.

SELECT  *  

FROM employee  AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' MINUTE)  

WHERE userid = 'aa'

;

다음과 같이 쿼리로 조회를 하면 10분 전 데이터가 조회됩니다.

단위는 SECOND, MINUTE, HOUR, DAY(초, 분, 시간, 일)로 바꿔서 조회가 가능합니다.

실수로 데이터를 업데이트하거나 삭제하신 경우 복구할 수 있도록 하세요~

테이블스페이스(tablespace)

 : 테이블 및 인덱스를 저장한 오라클의 논리적인 공간으로, 

  실제 물리적인 공간은 데이터파일(파일명.DBF)에 저장

테이블스페이스 생성 쿼리

create TABLESAPCE테이블스페이스명 DATAFILE

'저장될 디렉토리\파일명.dbf' size 100m // 100MB 크기의 파일이 생성됨

extent management local

segment space management auto;

테이블스페이스 사용하기

CREATE TABLE 테이블명(

 ID VARCHAR2(40 BYTE)       NOT NULL,

 NAME VARCHAR2(255 BYTE),

 ....

)tablespace 만든 테이블스페이스명;

 : 만든 테이블의 데이터는 만든 테이블스페이스에 저장됨

테이블 스페이스 관련 테이블

 - DBA_TABLESPACES : 테이블스페이스 목록

SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES;

- DBA_DATA_FILES : 테이블스페이스 파일 목록

SELECT FILE_NAME, BYTES, STATUS FROM DBA_DATA_FILES;

- DBA_FREE_SPACE : 테이블스페이스 잔여 공간

SELECT TABLESPACE_NAME, BYTES, BLOCKS FROM DBA_FREE_SPACE;

테이블스페이스 용량 조회 쿼리

select   substr(a.tablespace_name,1,30) tablespace,

         round(sum(a.total1)/1024/1024,1) "TotalMB",

         round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) "UsedMB",

         round(sum(a.sum1)/1024/1024,1) "FreeMB",

         round((round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/round(sum(a.total1)/1024/1024,1)*100,2) "Used%"

   from

         (select   tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt

          from     dba_free_space

          group by tablespace_name

          union

          select   tablespace_name,sum(bytes) total1,0,0,0

          from     dba_data_files

          group by tablespace_name) a

   group by a.tablespace_name

   order by tablespace;

테이블스페이스 리사이즈

alter DATABASE datafile '/data/oracle/oradata/파일명.dbf' resize 20000M 

 -> 해당 디렉토리에 있는 파일명.dbf의 파일 크기를 20GB로 늘림