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

원본출처: http://www.enjoydev.com/blog/46

오라클에서는 테이블의 일부 레코드 또는 테이블에 대한 Lock을 걸수 있다.

Lock이 걸린 Record(Table)에 대해서는 Commit, Rollback문을 수행하기 전까지 다른 세션에서 Delete, Update를 할 수 없다.

하지만 Lock을 걸더라도 다른 세션에서 Select는 가능하다.


▣ Table Lock
  - 문법 : lock table TABLE_NAME in exclusive mode;
  - Table Lock이 걸린 테이블에 대해서는 다른 세션에서 Insert, Update, Delete를 할 수 없다.
  - Table Lock이 걸리더라도 다른 세션에서 Select는 가능하다.
  - Commit, Rollback 문장 실행으로 Lock을 풀 수 있다.

▣ Record Lock
  - 문법 : select * from TABLE_NAME where CONDITION for update;
  - "for update" 키워드를 이용한다.
  - "for update" 를 이용한 Select 문에서의 CONDITION에 해당하는 레코드들에 대해 Lock이 설정된다.
  - Lock이 걸린 Record들에 대해서는 다른 세션에서 Delete, Update를 할 수 없다.
  - Lock이 걸리지 않은 Record들에 대해서는 다른 세션에서 Delete, Update를 할 수 있다.
  - Record Lock을 걸더라도 다른 세션에서 테이블에 대한 Insert는 가능하다.
  - Record Lock이 걸리더라도 다른 세션에서 Select는 가능하다.
  - Commit, Rollback 문장 실행으로 Lock을 풀 수 있다.


[Example1] - Table Lock
-- EMP 테이블에 대해 Insert, Delete, Update를 금지한다.