웹 개발과 관련된 모든 것들

1. RANK () OVER ( [query_partition_clause] order_by_clause)

2. DENSE_RANK () OVER ( [query_partition_clause] order_by_clause)

3. PERCENT_RANK () OVER ([query_partition_clause] order_by_clause )

  --그룹 수에 대한 값의 순위 퍼센트를 계산,  범위 : 0 < PERCENT_RANK < 1

4. CUME_DIST ( ) OVER ( [query_partition_clause] order_by_clause )

  -- 누적 분포 (전체 중 특정한 값의 위치를 계산), 범위 : 0 < CUME_DIST < 1

5. NTILE (expr) OVER ( [query_partition_clause] order_by_clause )

  -- 분위수 계산

6. ROW_NUMBER () OVER ( [query_partition_clause] order_by_clause )

  -- 행의 수 계산 (파티션 내에서 각 행에 대해 1로 시작하여 정렬되어 정의되는 유일한 수를 할당)

 

------------------------------------------

[누적합]  :   ROWS UNBOUNDED PRECEDING

[이동평균]  :   ROWS 2 PRECEDING  (시간에 근거 - 시계열)

[중심평균]  :  현재 행이 중심이 된다.   (예. 전날과 다음날과의 평균)

 

------------------------------------------

RATIO_TO_REPORT  : RATIO_TO_REPORT ( expr ) OVER ( [query_partition_clause] )

 

------------------------------------------

{LAG | LEAD} ( value_expr [, offset] [, default] )  OVER ( [query_partition_clause] order_by_clause )

 

------------------------------------------

[CUME DIST]  :   RANK () OVER ( [query_partition_clause] order_by_clause)  --> 누적분포

db특별sql-drake661.doc

[출처] 오라클 특별한 함수 모음|작성자 구름할까

EXPORT받은 DUMP 파일의 CHARACTER SET 확인 방법
====================================================

다른 server에서 export받아온 dump file을 import시킬 경우 자주 겪게 되는
문제가 CHARACTERset에 관련된 것이다.
양쪽의 CHARACTERset이 같고 .profile의 환경변수(NLS_LANG)도 맞다고
생각되는 데도 import 시 segment fault가 발생되거나
imp-16, 36, 37, 38, 42, 43, 45, 49 등의 error가 발생되면 dump받아온 file을
의심해볼 필요가 있다.

이 때 dump file을 8진수로 변환해 보면 현재 export받은 file의
CHARACTERset을 쉽게 알 수 있다.


오라클 export file은 특별한 CHARACTER set을 사용해서 쓰여진다.
version 5, 6에서는 export 시 단지 두개의 CHARACTER set-ASCII와 EBCIDC-만이
지원되었으나, version 7에서는 지원하는 모든 CHARACTER set이 export 시에
사용될 수 있다. export 시 사용된 CHARACTER set은 dump file 내에 code로써
기술되는데 file의 첫 byte가 이것을 나타낸다.

1st byte 의미
----------------------------------------------------
1 version 5, 6의 ASCII CHARACTER set
2 version 5, 6의 EBCDIC CHARACTER set
3 version 7의 CHARACTER set.

"3"일 경우 두, 세번째 byte를 보고서 CHARACTER set을 판단한다.
아래에는 우리가 흔히 접하는 character set에 대한 CHARACTER set ID이다.

2nd 3rd value
--------------------------------------------------
000 001 US7ASCII
000 002 WE8DEC
000 037 WE8ISO8859P1
003 110 KO16KSC5601
003 147 UTF8 (oracle8.0 이상부터 지원)
: :
: :

export file을 체크하기 위해서는 다음의 unix command를 사용한다.


$ od -b expdat.dmp|more

0000000 003 000 001 105 130 120 117 122 124 072 126 060 067 056 060 062
0000020 056 060 062 012 104 112 115 113 111 115 012 122 124 101 102 114
          :
          :
위의 경우 1st byte가 003이므로 version 7이며, 3rd byte가 001이므로 ID가
1번인 US7ASCII가 CHARACTER set이 된다.

(비고:
  HP-UX에서 TEST 결과 DB CHARACTERSET이 무엇으로 지정되었건 EXPORT 시는
  USER의 .PROFILE에 있는 환경 변수를 기준하여 DUMP FILE을 WRITE하므로
  EXPORT 실시 때의 OS USER의 환경변수인 NLS_LANG 값이 상당히 중요함.)

          
from www.oracle.co.kr 

SQL> set markup html on

출처:http://blog.naver.com/rojinsong?Redirect=Log&logNo=50004423503

$ORACLE_HOME 디렉토리에 있는 설치화일을 전부삭제
/etc밑에 orainst.loc, oratab 삭제
/usr/local/bin/oraenv 파일삭제
/tmp디렉토리에서 관련파일삭제
elete the /etc/oratab file. If using 9iAS delete the /etc/emtab file also.
# rm /etc/oratab /etc/emtab
끝 재설치 하면됩니다.

얼마 전 database.sarang.net의 오라클 게시판에 DBMS_JOB을 이용해 원하는 작업을 08시, 14시, 20시에 실행시키는 방법을 묻는 질문이 올라왔다. 작업 간격이 규칙적일 때는 문제가 간단하지만 원하는 시간 간격이 불규칙하므로 그냥 JOB을 세 개 등록하면 어떻겠냐고 답했더니 이번에는 이 작업을 평일에만 실행시키게 하고 싶다고 했다. 즉 평일 08시, 14시, 20시에 작업이 실행되도록 하고 싶다는 것이었다.

그럼 문제를 풀기 전에 DBMS_JOB.SUBMIT 프로시저를 살펴보자. DBMS_JOB을 이용해 JOB을 등록시키려면 SUBMIT 프로시저를 사용해야 한다. 파라미터 중 next_date와 interval를 통해 작업 실행 시각을 조절할 수 있다.

DBMS_JOB.SUBMIT (
job OUT BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE DEFAULT sysdate,
interval IN VARCHAR2 DEFAULT 'null',
no_parse IN BOOLEAN DEFAULT FALSE,
instance IN BINARY_INTEGER DEFAULT any_instance,
force IN BOOLEAN DEFAULT FALSE);

next_date의 디폴트 값은 sysdate이므로 값을 주지 않으면 등록 즉시 실행된다. 그 다음 실행 시각은 JOB이 실행되기 직전 interval에 지정된 수식을 이용해 계산한다. (interval이 NULL일 경우는 작업이 한 번만 실행된다.) 파라미터 이름이 interval이기는 하지만 실제 의미는 interval이 아니라 "다음 실행될 시점을 계산할 수식"인 것이다. 만약 어떤 작업을 1시간에 1번씩 실행시키고 싶다면 interval을 'sysdate+1/24'로 주면 된다. 작업을 시작하기 전에 sysdate+1/24를 통해 다음 실행할 시각을 구하면 작업 시작 시간으로부터 1시간 후인 시각이 된다. 다음 작업 시작 시각을 알고 싶으면 ALL_JOBS의 NEXT_DATE 컬럼을 조회해 확인할 수 있다.


그런데 위와 같이 하면 작업 주기만 지정한 것일 뿐이다. 특정 시각에 JOB을 실행시키려면 다음과 같이 하면 된다.


interval 파라미터는 문자열로 주어야 하므로 수식 내에 따옴표(single quotation)이 있으면 따옴표를 두 개 써줘야 하는 것에 유의해야 한다. interval 수식이 복잡할 때는 확인하기가 어려울 수 있는데, 그럴 때는 interval 수식으로 직접 쿼리를 작성해 확인할 수 있다.

select trunc(sysdate, 'D') + 7 from dual;

이제 다음과 같이 다양한 경우에 대한 interval을 구해보자.

1. 매주 토요일 새벽 1시에 실행
   
2. 매월 1일 새벽 0시에 실행
   
3. 매월 말일 밤 11시에 실행
   
4. 평일(월화수목금) 밤 10시에 실행
   
5. 불규칙한 시각, 8시, 14시, 20시에 한번씩

1번은 쉽다. 일단 next_date를 이번 주 토요일 새벽 1시로 지정하고, 그 다음 실행될 날은 거기서 7일 후가 된다. 즉,

next_date=>to_date('2007102701','YYYYMMDDHH24'),
interval=>'sysdate + 7'

월초나 월말의 경우는 add_months나 last_day를 이용해 구하면 된다.

매월1일 새벽 0시 작업 실행
next_date=>add_months(trunc(sysdate,'MM'),1),
interval=>'add_months(trunc(sysdate,''MM''),1)'

매월 말일 밤 11시에 작업 실행
next_date=>last_day(trunc(sysdate))+23/24,
interval=>'last_day(trunc(sysdate)+1)+23/24' -- 말일+1일은 다음달 1일

평일만 실행되도록 하기 위해서는 interval이 좀더 복잡해진다.

interval=>'trunc(sysdate) + decode(to_char(sysdate,''D''), ''6'', 3, ''7'', 2, 1) + 22/24'

요일을 구한 다음 토요일(to_char(sysdate,'D')='6')에는 작업 후 3일 후에, 일요일(to_char(sysdate,'D')='7')에는 작업 후 2일 후에, 평일에는 자업 후 1일 후에 작업이 다시 시작되도록 하면 된다. 이를 위해 DECODE 함수를 활용했다.

불규칙한 시간 간격일 경우에도 작업 시각을 기반으로 DECODE를 활용하면 가능할 것 같다. 그러나 하루 수행 횟수가 서너 번 정도라면 그냥 각 시각마다 실행되도록 서너 개의 JOB을 등록시켜주는 것도 생각해볼 수 있다.

원래 문제는 불규칙한 시각+평일 조건을 만족해야 하므로 하나의 interval 수식으로 해결하려면 수식이 무척 복잡해질 것 같다. interval 수식이 복잡해지면 이해가기도 어려워 진고, 나중에 수정하고 싶을 때 문제가 생길 수도 있다.

참고로 10g부터는 DBMS_JOB 대신 DBMS_SCHEDULER을 쓰도록 권고하고 있다.

출처 : http://ntalbs.blogspot.com/2007/10/database.html

Oracle SID를 알려면 다음 처럼 질의 합니다.

SQL> select instance from v$thread;

INSTANCE
----------------
NEWS

Oracle DB_NAME은 다음 처럼 질의 하구요...

SQL> select name from v$database;

NAME
---------
NEWS

ORACLE_SID나 DB_SID는 같은 값을 가리키며 v$thread에서 확인 가능!!
DB_NAME은 v$database에서 확인 가능!!!

FIRST_VALUE() OVER() -> 전체 값들 중 첫번째값을 반환합니다

 LAST_VALUE() OVER() -> 전체 값들 중 마지막값을 반환합니다

 COUNT() OVER() -> 조건을 만족하는 행의 수를 반환합니다

 SUM() OVER() -> 특정 값을 누적하여 결과를 반환합니다

 DENSE_RANK() OVER() -> RANK()와는 달리 같은 순위가 둘 이상 있어도 다음 순위는 1증가

AVG() -> 하나 이상의 값들로 부터 평균값을 반환합니다

 AVG() OVER() -> 분석 함수로 사용된 예

 RANK() WITHIN GROUP() -> 전체 값을 대상으로 특정 값의 순위를 반환합니다

 RANK() OVER() -> 특정 순위가 아닌 전체 순위를 반환합니다

 SUM() -> 하나 이상의 행으로 부터 합계를 반환합니다

 MAX() -> 하나 이상의 행으로 부터 최대값을 반환합니다

 MIN() -> 하나 이상의 행으로 부터 최소값을 반환합니다

 COUNT() -> 전체 행의 수를 반환합니다

ORACLE PL/SQL USER GUIDE AND REFERENCE

휴...

이것보다 좋은것은 찾아보기 힘들지...

PL/SQL을 하는 사람은 누구나 반드시 봐야할 교재 ㅎㅎ

오라클 역쉬  ~~