Oracle 10g SQL MODEL Clase.pdf

출처:http://www.gurubee.net/article/55486

-- 접속한 사용자의 테이블별 사용 조회 (data파일과 무관하게 테이블 통계정보 활용)
SELECT TABLE_NAME, TABLESPACE_NAME, NUM_ROWS, AVG_ROW_LEN, INITIAL_EXTENT AS BYTES FROM USER_TABLES;

-- 오라클에서 임의의 사용자가 가지고 있는 테이블 항목 조회하기
    SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = '[사용자아이디]';

-- 오라클 데이터파일안에 들어있는 테이블 목록
    SELECT
        A.SEGMENT_NAME, B.TABLESPACE_NAME, B.FILE_NAME
    FROM DBA_EXTENTS A,DBA_DATA_FILES B
    WHERE A.FILE_ID=B.FILE_ID
        AND A.SEGMENT_TYPE='TABLE'
        AND A.OWNER='[사용자아이디]' AND B.TABLESPACE_NAME = '[테이블스페이스명]';

-- 사용자의 테이블별 DB 사용량(사용 파일 포함)
    SELECT
        A.SEGMENT_NAME, B.TABLESPACE_NAME, B.FILE_NAME, ROUND((SUM(A.BYTES)/1024/1024),0) AS EXTENDS_BYTES
    FROM DBA_EXTENTS A,DBA_DATA_FILES B
    WHERE A.FILE_ID=B.FILE_ID AND A.SEGMENT_TYPE='TABLE' AND
        A.OWNER='[사용자아이디]' AND B.TABLESPACE_NAME = '[테이블스페이스명]'
    GROUP BY A.SEGMENT_NAME, B.TABLESPACE_NAME, B.FILE_NAME

-- 사용자의 테이블별 DB 사용량 (DB파일 상관없이)
SELECT
        A.SEGMENT_NAME, SUM(A.BYTES) AS BYTES
    FROM DBA_EXTENTS A
    WHERE A.SEGMENT_TYPE='TABLE' AND
        A.OWNER='[사용자아이디]'
    GROUP BY A.SEGMENT_NAME

-- 해당 사용자의 총 DB 사용량 비율
    SELECT
     TOTAL,  TOTAL-FREE AS USED
        , NVL(B.FREE,0) FREE, ROUND((TOTAL - NVL(FREE,0))*100/TOTAL,0) AS USEDRATE
        , B.LARGEST
    FROM
      (SELECT TABLESPACE_NAME, ROUND((SUM(BYTES)/1024/1024),0) AS TOTAL
       FROM DBA_DATA_FILES
       GROUP BY TABLESPACE_NAME) A,
      (SELECT TABLESPACE_NAME, ROUND((SUM(BYTES)/1024/1024),0) AS FREE, MAX(BYTES)/1024/1024 AS LARGEST
        FROM DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME) B
    WHERE A.TABLESPACE_NAME = '[테이블스페이스명]' AND B.TABLESPACE_NAME = '[테이블스페이스명]'

-----------------------------2. 다른 방법---------------------------------
예)  테이블명 = 'TABLE1'
분석: ANALYZE TABLE TABLE1 COMPUTE STATISTICS;
(건수가 매우 많을 때에는 COMPUTE 대신 ESTIMATE 사용)

SELECT TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, INITIAL_EXTENT AS BYTES
FROM USER_TABLES
WHERE TABLE_NAME = 'TABLE1';
-----------------------------------------------------------------------

SELECT A.TABLE_NAME
,(SELECT C.COMMENTS FROM DBA_TAB_COMMENTS C WHERE A.OWNER = C.OWNER AND A.TABLE_NAME = C.TABLE_NAME) AS TABLE_COMMENTS
,B.COLUMN_ID AS COLUMN_NO
,B.COLUMN_NAME
,(SELECT D.COMMENTS FROM DBA_COL_COMMENTS D WHERE A.OWNER = D.OWNER AND A.TABLE_NAME = D.TABLE_NAME AND B.COLUMN_NAME = D.COLUMN_NAME) AS COLUMN_COMMENTS
,B.DATA_TYPE
,CASE
WHEN B.DATA_TYPE IN ('VARCHAR', 'VARCHAR2', 'CHAR') THEN
TO_CHAR(B.DATA_LENGTH)
WHEN B.DATA_TYPE = 'NUMBER' THEN
B.DATA_PRECISION || ',' || DECODE(B.DATA_SCALE,0,'0',B.DATA_SCALE)
ELSE ''
END AS DATA_LENGTH
,B.NULLABLE
FROM DBA_TABLES A
JOIN DBA_TAB_COLUMNS B ON A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
WHERE A.OWNER = 'EDUIS'
AND (A.TABLE_NAME LIKE 'A______T' OR A.TABLE_NAME LIKE 'Y_BANK_%')
ORDER BY A.TABLE_NAME
,B.COLUMN_ID

/*
※ DB 스키마 조회
1. DBA권한을 가진 스키마로 DB 접속 후
2. OBJECT 관리 스키마 목록을 "(<스키마목록>)"에 열거한 후 SQL을 수행한다.
예) OWNER IN ('AAA', 'BBB', 'CCC')
3. 쿼리 결과를 CSV 파일 또는 Excel 파일로 저장 한다.
저장 파일명은 시스템명_쿼리명.csv
예) 학자금_01_컬럼목록.csv
*/

/*
쿼리명 : 01_컬럼목록
설 명 : Oracle Dictionary에서 컬럼의 Comment 와 Type, Null 여부를 조회.
*/
SELECT
A.OWNER
, A.TABLE_NAME
, A.COLUMN_NAME
, A.COLUMN_ID AS COL_ORDER
, CASE WHEN A.DATA_TYPE IN ('VARCHAR', 'VARCHAR2', 'CHAR') THEN
A.DATA_TYPE || '(' || A.DATA_LENGTH || ')'
WHEN A.DATA_TYPE = 'NUMBER' THEN
A.DATA_TYPE || '(' || A.DATA_PRECISION || ',' || DECODE(A.DATA_SCALE,0,'0',A.DATA_SCALE) || ')'
ELSE A.DATA_TYPE
END AS FULL_DATA_TYPE
, A.DATA_TYPE
, A.DATA_LENGTH
, A.DATA_PRECISION
, A.DATA_SCALE
, A.NULLABLE
, REPLACE(B.COMMENTS, CHR(10), '') AS COMMENTS
FROM DBA_TAB_COLUMNS A
, DBA_COL_COMMENTS B
, DBA_TABLES C
WHERE A.OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS','TSMSYS')
--A.OWNER IN (<스키마목록>)
AND A.OWNER = C.OWNER
AND A.TABLE_NAME = C.TABLE_NAME
AND A.OWNER = B.OWNER(+)
AND A.TABLE_NAME = B.TABLE_NAME(+)
AND A.COLUMN_NAME = B.COLUMN_NAME(+)
;

/*
쿼리명 : 02_제약사항
설 명 : Oracle Dictionary에서 PK를 포함한 Constraint 컬럼 조회
Constraint Type
P : Primary
U : Unique Index
C : Check
R : Relation Ship (Forgin Key)
*/
SELECT A.OWNER
, A.TABLE_NAME
, A.CONSTRAINT_TYPE
, A.CONSTRAINT_NAME
, DECODE(A.STATUS, 'ENABLED', 'Y', NULL) ENABLED
, B.COLUMN_NAME
, B.POSITION
, A.R_OWNER
, A.R_CONSTRAINT_NAME
FROM DBA_CONSTRAINTS A
, DBA_CONS_COLUMNS B
WHERE A.OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS','TSMSYS')
--A.OWNER IN (<스키마목록>)
AND A.CONSTRAINT_TYPE IN ('P','U','R')
AND A.OWNER = B.OWNER
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
;

/*
쿼리명 : 03_인덱스
설 명 : Oracle Dictionary에서 INDEX 컬럼을 조회
*/
SELECT A.INDEX_OWNER
, A.INDEX_NAME
, A.TABLE_OWNER
, A.TABLE_NAME
, A.COLUMN_NAME
, A.COLUMN_POSITION
, A.COLUMN_LENGTH
, A.CHAR_LENGTH
, A.DESCEND
, B.CONSTRAINT_TYPE
FROM DBA_IND_COLUMNS A
, DBA_CONSTRAINTS B
WHERE A.INDEX_OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS','TSMSYS')
--A.INDEX_OWNER IN (<스키마목록>)
AND A.INDEX_OWNER = B.OWNER (+)
AND A.INDEX_NAME = B.CONSTRAINT_NAME (+)
;

/*
쿼리명 : 04_테이블목록
설 명 : Oracle Dictionary에서 테이블 목록 및 테이블 코멘트를 조회
*/
SELECT A.*
,B.*
FROM DBA_TABLES A
,DBA_TAB_COMMENTS B
WHERE A.OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS','TSMSYS')
--A.OWNER IN (<스키마목록>)
AND A.OWNER = B.OWNER (+)
AND A.TABLE_NAME = B.TABLE_NAME (+)
--AND B.COMMENTS (+) IS NOT NULL
;

/*
쿼리명 : 04-1_Owner별 테이블 수
설 명 : Oracle Dictionary에서 Owner별 테이블 수를 조회
*/
SELECT OWNER, COUNT(*) CNT
FROM DBA_TABLES A
WHERE A.OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS','TSMSYS')
--A.OWNER IN (<스키마목록>)
GROUP BY OWNER

/*
쿼리명 : 05_FK키
설 명 : Oracle Dictionary에서 FK Constraint 참조 테이블 및 컬럼 조회
*/
SELECT A.CONSTRAINT_NAME
,A.OWNER
,A.TABLE_NAME
,A.PK_OWNER
,A.PK_TABLE
,A.CONSTRAINT_TYPE
,MAX(CASE WHEN B.POSITION IS NULL OR B.POSITION=1 THEN B.COLUMN_NAME END) COLUMN01
,MAX(CASE WHEN B.POSITION= 2 THEN B.COLUMN_NAME END) COLUMN02
,MAX(CASE WHEN B.POSITION= 3 THEN B.COLUMN_NAME END) COLUMN03
,MAX(CASE WHEN B.POSITION= 4 THEN B.COLUMN_NAME END) COLUMN04
,MAX(CASE WHEN B.POSITION= 5 THEN B.COLUMN_NAME END) COLUMN05
,MAX(CASE WHEN B.POSITION= 6 THEN B.COLUMN_NAME END) COLUMN06
,MAX(CASE WHEN B.POSITION= 7 THEN B.COLUMN_NAME END) COLUMN07
,MAX(CASE WHEN B.POSITION= 8 THEN B.COLUMN_NAME END) COLUMN08
,MAX(CASE WHEN B.POSITION= 9 THEN B.COLUMN_NAME END) COLUMN09
,MAX(CASE WHEN B.POSITION=10 THEN B.COLUMN_NAME END) COLUMN10
FROM (
SELECT A.CONSTRAINT_NAME
,A.OWNER AS OWNER
,A.TABLE_NAME AS TABLE_NAME
,A.R_OWNER AS PK_OWNER
,B.TABLE_NAME AS PK_TABLE
,A.CONSTRAINT_TYPE
,DECODE(A.STATUS, 'ENABLED', 'Y', NULL) ENABLED
FROM DBA_CONSTRAINTS A
,DBA_CONSTRAINTS B
WHERE A.OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS','TSMSYS')
-- A.OWNER IN (<스키마목록>)
AND A.CONSTRAINT_TYPE IN ('P','R')
AND A.R_OWNER = B.OWNER(+)
AND A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME(+)
) A
, ALL_CONS_COLUMNS B
WHERE A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
GROUP BY A.OWNER, A.TABLE_NAME, A.PK_OWNER, A.PK_TABLE, A.CONSTRAINT_NAME, A.CONSTRAINT_TYPE
;

/*
쿼리명 : 06_레코드건수
설 명 : 테이블별 레코드건수 및 사이즈를 조회
※ ANALYZE 를 수행한 테이블만 용량산정이 가능함
*/
WITH
TM_TB_SIZE AS (
SELECT SEGMENT_NAME AS TABLE_NAME
, BYTES
, (BYTES/1024)/1024 AS TABLE_SIZE
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE'
AND OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS','TSMSYS')
-- OWNER IN (<스키마목록>)
)
SELECT A.OWNER
, A.TABLE_NAME
, A.TABLESPACE_NAME
, A.NUM_ROWS
, A.BLOCKS
, A.LAST_ANALYZED
, A.PARTITIONED
, B.BYTES AS "TABLE_SIZE(BYTE)"
, B.TABLE_SIZE AS "TABLE_SIZE(MB)"
FROM DBA_TABLES A
, TM_TB_SIZE B
WHERE A.OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS','TSMSYS')
--A.OWNER IN (<스키마목록>)
AND A.TABLE_NAME = B.TABLE_NAME (+)
;

/*
쿼리명 : 07_테이블스페이스용량
설 명 : 대략적인 테이블스페이스별 할당량, 사용량을 조회 한다.
*/
SELECT A.TABLESPACE_NAME
, ROUND(A.BYTES / 1024 / 1024) AS "MB ALLOCATED"
, ROUND((A.BYTES-NVL(B.BYTES, 0)) / 1024 / 1024) AS "MB USED"
, NVL(ROUND(B.BYTES / 1024 / 1024), 0) AS "MB FREE"
, ROUND(((A.BYTES-NVL(B.BYTES, 0))/A.BYTES)*100,2) AS "PCT USED"
, ROUND((1-((A.BYTES-NVL(B.BYTES,0))/A.BYTES))*100,2) AS "PCT FREE"
FROM ( SELECT TABLESPACE_NAME
, SUM(BYTES) BYTES
FROM SYS.DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) A,
( SELECT TABLESPACE_NAME
, SUM(BYTES) BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+)
;

/*
쿼리명 : 08_오브젝트용량
설 명 : 대략적인 테이블스페이스내 오브젝트의 사용량을 조회 한다.
*/
SELECT S.OWNER
, SUBSTR(S.SEGMENT_NAME, 1, 30) AS "TABLE NAME"
, ROUND(SUM(S.BYTES) / 1024 / 1024, 2) AS "MB ALLOCATED"
, CASE WHEN T.BLOCKS > 0 THEN
ROUND(SUM(S.BYTES) / 1024 / 1024 * TO_NUMBER(DECODE(T.BLOCKS, NULL, NULL, (T.BLOCKS / (T.BLOCKS + T.EMPTY_BLOCKS)))), 2)
ELSE 0
END AS "MB USED"
, CASE WHEN T.BLOCKS > 0 THEN
TO_NUMBER(DECODE(T.BLOCKS,NULL, NULL, ROUND(100 *(T.BLOCKS / (T.BLOCKS + T.EMPTY_BLOCKS)), 2)))
ELSE 0
END AS "PCT FULL"
, S.TABLESPACE_NAME
FROM SYS.DBA_SEGMENTS S
, SYS.DBA_TABLES T
WHERE T.OWNER = S.OWNER
AND T.TABLE_NAME = S.SEGMENT_NAME
AND T.TABLESPACE_NAME = S.TABLESPACE_NAME
AND S.OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS','TSMSYS')
-- S.OWNER IN (<스키마목록>)
GROUP BY S.TABLESPACE_NAME, S.OWNER, S.SEGMENT_NAME, S.SEGMENT_TYPE, T.BLOCKS, T.EMPTY_BLOCKS

출처:http://opensrc.tistory.com/11

Glance - GlancePlus system performance monitor for HP-UX

/*

pthread_t는 시스템 마다 형식이 다름 HP의 경우 int

계속 증가하다 최대값에 도달 시 다시 n(남은스레드 + 1) 부터 시작

*/

pthread_t tid;

pthread_create(&tid, NULL, ThreadFunc, (void*)&param);

pthread_join(pthread_t tid, void ** thread_return);

pthread_exit(void * rval); //스레드에서 자신 종료 - rval은 join에 보낼 리턴값.

pthread_cancel(tid); //특정스레드를 죽임

pthread_kill(pthread_t tid, int sig); //특정 스레드에 시그널 보냄. (단, 해당 프로세스 전체에 영향이 감으로 스레드 죽일 때는 부적합) 스레드가 살아 있는지 확인용도로 사용

pthread_kill(tid, 0) -> 리턴값이 ESRCH이면 존재하지 않는 스레드.

pthread_detach(tid); //스레드 분리 - 해당 스레드가 죽으면 별도 join없이 자원 정리

pthread_cleanup_push(void (*func)(void*), void * arg); //스레드 종료 핸들러 등록 (스택으로 쌓임

pthread_cleanup_pop(int execute); //등록된 핸들러 삭제.

pthread_mutex_init(&g_mutex, NULL);  //뮤텍스 초기화
pthread_cond_init(&g_condition, NULL); //조건변수 초기화

pthread_mutex_lock(&g_mutex); //뮤텍스 락
pthread_cond_signal(&g_condition); //waiting 중인 스레드를 한 넘을 깨움
pthread_cond_broadcast(&g_condition); //waiting 중인 스레드 모두 깨움
pthread_mutex_unlock(&g_mutex); //뮤텍스 언락

pthread_cond_destroy(&g_condition); //조건변수 파괴
pthread_mutex_destroy(&g_mutex); //뮤텍스 파괴

void f(){

  pthread_mutex_lock(&g_mutex);

  pthread_cond_wait(&g_condition, &g_mutex);

  //wake up...do work...

  pthread_mutex_unlock(&g_mutex); //뮤텍스 언락

}

void sender(){

  pthread_mutex_lock(&g_mutex);
  pthread_cond_signal(&g_condition);
  pthread_mutex_unlock(&g_mutex); //뮤텍스 언락

}

Thread Pool Example

https://sourceforge.net/projects/cthreadpool/

출처:http://darkhorizon.tistory.com/287

Cron의 실행주기를 설정하는 방법

<property name="cronExpression">

<!--  -->

<value>* * * * * * *</value>

</property>

왼쪽부터 초 분 시간 일(Day of Month) 월 요일(Day of Week, 1-7 : SUN-SAT) 년도(생략가능) 의 순이다.

표현식

* : 모든 값
? : 특정 값 없음
- : 범위 지정
, : 여러 값 지정 구분에 사용
/ : 초기값과 증가치 설정에 사용
L : 지정할 수 있는 범위의 마지막 값
W : 월~금요일 또는 가장 가까운 월/금요일
# : 몇 번째 무슨 요일 2#1 => 첫 번째 월요일

예제)

"0 0 12 * * ?"   매일 정오 12시에 실행

"0 15 10 ? * *"   매일 오전 10시 15분에 실행

"0 15 10 * * ?"   매일 오전 10시 15분에 실행

"0 15 10 * * ? *"         매일 오전 10시 15분에 실행

"0 15 10 * * ? 2005" 2005년 동안 매일 오전 10시 15분에 실행

"0 * 14 * * ?"   매일 오후 2시부터 시작하여 오후 2시 59분까지 실행

"0 0/5 14 * * ?"         매일 오후 2시부터 2시 55분까지 5분 간격으로 실행

"0 0/5 14,18 * * ?" 매일 오후 2시부터 2시 55분까지, 오후 6시부터 6시 55분까지 5분 간격으로 실행

"0 0-5 14 * * ?"         매일 오후 2시부터 2시 05분까지 분 단위로 실행

"0 10,44 14 ? 3 WED" 3월의 매주 수요일 오후 2시 10분과 2시 44분에 실행

"0 15 10 ? * MON-FRI" 매주 월요일부터 금요일까지 오전 10시 15분에 실행

"0 15 10 15 * ?"         매달 15일 오전 10시 15분에 실행

"0 15 10 L * ?"   매달 마지막 날 오전 10시 15분에 실행

"0 15 10 ? * 6L"         매월 마지막 금요일 오전 10시 15분에 실행

"0 15 10 ? * 6#3"         매월 세번째 금요일 오전 10시 15분에 실행

!! 주의)

요일(Day of Week) 에 명시적인 특정값이 지정되었을 때, 일(Day of Month)의 표현식이 ?나 특정값이 아닌 모든 값(*)으로 설정되었을 때 아래의 Exception이 발생한다. 

Support for specifying both a day-of-week AND a day-of-month parameter is not implemented