SQL 모음
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
- 01 테이블스페이스별 파일 목록을 보기
<syntaxhighlight lang='sql'> SELECT SUBSTRB(TABLESPACE_NAME, 1, 10) AS "테이블스페이스"
,SUBSTRB(FILE_NAME, 1, 50) AS "파일명" ,TO_CHAR(BLOCKS, '999,999,990') AS "블럭수" ,TO_CHAR(BYTES, '99,999,999') AS "크기"
FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME, FILE_NAME; </syntaxhighlight>
- 02 테이블스페이스별 정보 보기
SELECT A.TABLESPACE_NAME AS "TABLESPACE"
,A.INITIAL_EXTENT / 1024 AS "INIT(K)" ,A.NEXT_EXTENT / 1024 AS "NEXT(K)" ,A.MIN_EXTENTS AS "MIN" ,A.MAX_EXTENTS AS "MAX" ,A.PCT_INCREASE AS "PCT_INC(%)" ,B.FILE_NAME AS "FILE_NAME" ,B.BLOCKS * C.VALUE / 1024 / 1024 AS "SIZE(M)" ,B.STATUS AS "STATUS"
FROM DBA_TABLESPACES A
,DBA_DATA_FILES B ,V$PARAMETER C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND C.NAME = 'db_block_size' ORDER BY 1, 2;
- 03 테이블스페이스별 사용하는 파일의 크기 합 보기
SELECT SUBSTRB(TABLESPACE_NAME, 1, 10) AS TABLESPACE
,TO_CHAR(SUM(BYTES), '9,999,999,999,990') AS BYTES ,TO_CHAR(SUM(BLOCKS), '9,999,999,990') AS BLOCKS
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME UNION ALL SELECT '총계', TO_CHAR(SUM(BYTES), '9,999,999,999,990') AS BYTES, TO_CHAR(SUM(BLOCKS), '9,999,999,990') AS BLOCKS FROM DBA_DATA_FILES;
- 04 테이블스페이스별 디스크 사용량 보기
SELECT A.TABLESPACE_NAME AS "TABLESPACE"
,A.INIT AS "INIT(K)" ,A.NEXT AS "NEXT(K)" ,A.MIN AS "MIN" ,A.MAX AS "MAX" ,A.PCT_INC AS "PCT_INC(%)" ,TO_CHAR(B.TOTAL, '999,999,999,990') AS "총량(바이트)" ,TO_CHAR(C.FREE, '999,999,999,990') AS "남은량(바이트)" ,TO_CHAR(B.BLOCKS, '9,999,990') AS "총블럭" ,TO_CHAR(D.BLOCKS, '9,999,990') AS "사용블럭" ,TO_CHAR(100 * NVL(D.BLOCKS, 0) / B.BLOCKS, '999.99') AS "사용율%"
FROM (SELECT TABLESPACE_NAME
,INITIAL_EXTENT / 1024 AS INIT ,NEXT_EXTENT / 1024 AS NEXT ,MIN_EXTENTS AS MIN ,MAX_EXTENTS AS MAX ,PCT_INCREASE AS PCT_INC FROM DBA_TABLESPACES) A ,(SELECT TABLESPACE_NAME, SUM(BYTES) AS TOTAL, SUM(BLOCKS) AS BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B ,(SELECT TABLESPACE_NAME, SUM(BYTES) AS FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) C ,(SELECT TABLESPACE_NAME, SUM(BLOCKS) AS BLOCKS FROM DBA_EXTENTS GROUP BY TABLESPACE_NAME) D
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+) AND A.TABLESPACE_NAME = D.TABLESPACE_NAME(+) ORDER BY A.TABLESPACE_NAME;
- 05 테이블스페이스의 테이블 명 보기
SELECT TABLESPACE_NAME, TABLE_NAME
FROM USER_TABLES
WHERE TABLESPACE_NAME = UPPER('&테이블스페이스명')
ORDER BY TABLESPACE_NAME, TABLE_NAME;
- 06 ROLLBACK SEGMENT의 사용상황 보기
--: EXTENTS = 현재 할당된 EXTENT의 수 --: EXTENDS = 마지막 트랜잭션에 의해 할당된 EXTENT의 수
SELECT SUBSTRB(A.SEGMENT_NAME, 1, 10) AS SEGMENT_NAME
,SUBSTRB(A.TABLESPACE_NAME, 1, 10) AS TABLESPACE_NAME ,TO_CHAR(A.SEGMENT_ID, '99,999') AS SEG_ID ,TO_CHAR(A.MAX_EXTENTS, '999,999') AS MAX_EXT ,TO_CHAR(B.EXTENTS, '999,999') AS EXTENTS ,TO_CHAR(B.EXTENDS, '999,999') AS EXTENDS ,TO_CHAR((A.INITIAL_EXTENT + (B.EXTENTS - 1) * A.NEXT_EXTENT) / 1000000, '9,999.999') AS "ALLOC(MB)" ,TO_CHAR(XACTS, '9,999') AS XACTS
FROM DBA_ROLLBACK_SEGS A
,V$ROLLSTAT B
WHERE A.SEGMENT_ID = B.USN(+) ORDER BY 1;
- 07 CONSTRAINT 보기
SELECT DECODE(A.CONSTRAINT_TYPE, 'P', 'Primary Key', 'R', 'Foreign Key', 'C', 'Table Check', 'V', 'View Check', 'U', 'Unique', '?')
AS "유형" ,SUBSTRB(A.CONSTRAINT_NAME, 1, 25) AS CONSTRAINT_NAME ,B.POSITION ,SUBSTRB(B.COLUMN_NAME, 1, 25) AS COLUMN_NAME
FROM DBA_CONSTRAINTS A
,DBA_CONS_COLUMNS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.OWNER = 'E_LUCIS' AND A.TABLE_NAME = UPPER('&테이블명') ORDER BY 1, 2, 3;
- 08 INDEX 보기
SELECT A.INDEX_NAME
,A.UNIQUENESS ,TO_CHAR(COLUMN_POSITION, '999') AS POS ,SUBSTRB(COLUMN_NAME, 1, 33) AS COLUMN_NAME
FROM USER_INDEXES A
,USER_IND_COLUMNS B
WHERE A.INDEX_NAME = B.INDEX_NAME AND A.TABLE_OWNER = UPPER('E_LUCIS') AND A.TABLE_NAME = UPPER('&테이블명') ORDER BY 1, 3;
- 09 전체 INDEX 보기
SELECT SUBSTRB(A.TABLE_NAME, 1, 22) AS TABLE_NAME
,SUBSTRB(A.INDEX_NAME, 1, 23) AS INDEX_NAME ,SUBSTRB(A.UNIQUENESS, 1, 7) AS UNIQUE ,TO_CHAR(COLUMN_POSITION, '999') AS POS ,SUBSTRB(COLUMN_NAME, 1, 20) AS COLUMN_NAME
FROM DBA_INDEXES A
,DBA_IND_COLUMNS B
WHERE A.INDEX_NAME = B.INDEX_NAME AND A.TABLE_OWNER = B.TABLE_OWNER AND A.TABLE_OWNER = 'E_LUCIS' ORDER BY 1, 2, 3;
- 10 인덱스에 대한 컬럼 조회
SELECT TABLE_NAME
,INDEX_NAME ,COLUMN_POSITION ,COLUMN_NAME
FROM USER_IND_COLUMNS ORDER BY TABLE_NAME, INDEX_NAME, COLUMN_POSITION;
- 11 테이블에 LOCK이 걸렸는지를 보기
SELECT A.SID
,A.SERIAL# ,SUBSTRB(A.USERNAME, 1, 16) AS USERNAME ,SUBSTRB(A.MACHINE, 1, 30) AS MACHINE ,A.TERMINAL ,A.OSUSER ,A.PROGRAM ,SUBSTRB(TO_CHAR(A.LOGON_TIME, 'MM/DD HH24:MI:SS'), 1, 14) AS LOGON_TIME ,SUBSTRB(C.OBJECT_NAME, 1, 58) AS OBJECT_NAME
FROM V$SESSION A
,V$LOCK B ,DBA_OBJECTS C
WHERE A.SID = B.SID AND B.ID1 = C.OBJECT_ID AND B.TYPE = 'TM' AND C.OBJECT_NAME LIKE UPPER('&테이블명');
- 12 Lock을 잡고있는 세션과 기다리는 세션 조회
SELECT DECODE(B.LOCKWAIT, NULL, ' ', 'w') AS WW
,B.SID ,B.SERIAL# AS SER# ,SUBSTR(B.MACHINE, 1, 10) AS MACHINE ,SUBSTR(B.PROGRAM, 1, 15) AS PROGRAM ,SUBSTR(A.OBJECT_NAME, 1, 17) AS OBJ_NAME ,SUBSTR(B.STATUS, 1, 1) AS S ,DECODE(B.COMMAND, 0, NULL, 2, 'INSERT', 6, 'UPDATE', 7, 'DELETE', B.COMMAND) AS SQLCMD ,B.PROCESS AS PGM_PSS
FROM V$SESSION B
,(SELECT A.SID, DECODE(B.OWNER, NULL, A.TYPE || '..ing', B.OWNER || '.' || B.OBJECT_NAME) AS OBJECT_NAME FROM V$LOCK A ,DBA_OBJECTS B WHERE A.ID1 = B.OBJECT_ID(+) GROUP BY A.SID, DECODE(B.OWNER, NULL, A.TYPE || '..ing', B.OWNER || '.' || B.OBJECT_NAME)) A
WHERE B.SID = A.SID AND B.TADDR IS NOT NULL;
- 13 테이블에 걸린 비정상적 LOCK 풀기
ALTER SYSTEM KILL SESSION '&SID,&SERIAL';
- 14 연결되어 있는 OS 사용자 및 프로그램 조회
SELECT SID
,SERIAL# ,OSUSER ,SUBSTRB(USERNAME, 1, 10) AS USER_NAME ,SUBSTRB(PROGRAM, 1, 30) AS PROGRAM_NAME ,STATUS ,TO_CHAR(LOGON_TIME, 'YYYY/MM/DD HH:MI') AS LOGON_TIME
FROM V$SESSION WHERE TYPE != ‘BACKGROUND’ AND STATUS = ‘ACTIVE’;
- 15 위치별 space를 아는 방법
SELECT SUBSTRB(A.FILE_NAME, 1, 40) AS FILE_NAME
,A.FILE_ID ,B.FREE_BYTES / 1024 AS FREE_BYTES ,B.MAX_BYTES / 1024 AS MAX_BYTES
FROM DBA_DATA_FILES A
,(SELECT FILE_ID, SUM(BYTES) AS FREE_BYTES, MAX(BYTES) AS MAX_BYTES FROM DBA_FREE_SPACE GROUP BY FILE_ID) B
WHERE A.FILE_ID = B.FILE_ID AND A.TABLESPACE_NAME = UPPER('&테이블스페이스명') ORDER BY A.FILE_NAME;
- 16 DB Link 보기
SELECT SUBSTRB(U.NAME, 1, 10) AS OWNER
,SUBSTRB(L.NAME, 1, 20) AS DB_LINK ,SUBSTRB(L.HOST, 1, 10) AS HOST ,SUBSTRB(L.USERID || '/' || L.PASSWORD, 1, 15) AS USERPASS
FROM SYS.LINK$ L
,SYS.USER$ U
WHERE L.OWNER# = U.USER#;
- 17 테이블 생성일자 보기
SELECT SUBSTRB(OBJECT_NAME, 1, 15) AS OBJECT_NAME
,CREATED ,LAST_DDL_TIME ,TIMESTAMP ,STATUS
FROM USER_OBJECTS WHERE OBJECT_NAME = UPPER('&테이블명') AND OBJECT_TYPE = 'TABLE';
- 18 테이블의 크기 및 블록 보기
SELECT SUBSTR(SEGMENT_NAME, 1, 20), BYTES, BLOCKS
FROM USER_SEGMENTS
WHERE SEGMENT_NAME = UPPER('&테이블명');
- 19 View의 정의 내역 보기
SET LONG 100000
SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME LIKE UPPER('&뷰_이름');
- 20 파티션 테이블의 파티션 범위 보기
SELECT SUBSTRB(PARTITION_NAME, 1, 30) AS PARTITION_NAME, SUBSTRB(TABLESPACE_NAME, 1, 30) AS TABLESPACE_NAME, HIGH_VALUE
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = UPPER('&테이블명');
- 21 PRIMARY KEY 재생성 방법
--- PRIMARY KEY DROP
ALTER TABLE EMP DROP PRIMARY KEY;
-- PRIMARY KEY 생성 ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY(EMPNO) USING INDEX STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0) TABLESPACE USERS;
- 22 PRIMARY KEY를 REFERENCE 하는 FOREIGN KEY 찾기
SELECT C.NAME CONSTRAINT_NAME
FROM DBA_OBJECTS A
,CDEF$ B ,CON$ C
WHERE A.OBJECT_NAME = UPPER('&테이블명') AND A.OBJECT_ID = B.ROBJ# AND B.CON# = C.CON#;
- 23 동일한 자료 삭제 방법
DELETE FROM EMP E WHERE E.ROWID > ( SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMPNO = E.EMPNO );
- 24 1시간 이상 유휴 상태인 세션
SELECT SID
,SERIAL# ,USERNAME ,TRUNC(LAST_CALL_ET / 3600, 2) || ' HR' LAST_CALL_ET
FROM V$SESSION WHERE LAST_CALL_ET > 3600 AND USERNAME IS NOT NULL;
- 25 Oracle Process의 정보
SELECT S.STATUS "STATUS"
,S.SERIAL# "SERIAL#" ,S.TYPE "TYPE" ,S.USERNAME "DB USER" ,S.OSUSER "CLIENT USER" ,S.SERVER "SERVER" ,S.MACHINE "MACHINE" ,S.MODULE "MODULE" ,S.TERMINAL "TERMINAL" ,S.PROGRAM "PROGRAM" ,P.PROGRAM "O.S. PROGRAM" ,S.LOGON_TIME "CONNECT TIME" ,LOCKWAIT "LOCK WAIT" ,SI.PHYSICAL_READS "PHYSICAL READS" ,SI.BLOCK_GETS "BLOCK GETS" ,SI.CONSISTENT_GETS "CONSISTENT GETS" ,SI.BLOCK_CHANGES "BLOCK CHANGES" ,SI.CONSISTENT_CHANGES "CONSISTENT CHANGES" ,S.PROCESS "PROCESS" ,P.SPID ,P.PID ,S.SERIAL# ,SI.SID ,S.SQL_ADDRESS "ADDRESS" ,S.SQL_HASH_VALUE "SQL HASH" ,S.ACTION
FROM V$SESSION S
,V$PROCESS P ,SYS.V_$SESS_IO SI
WHERE S.PADDR = P.ADDR(+) AND SI.SID(+) = S.SID AND S.USERNAME IS NOT NULL AND NVL(S.OSUSER, 'X') <> 'SYSTEM' AND S.TYPE <> 'BACKGROUND' ORDER BY 3;
- 26 중복인덱스 체크
SELECT O1.NAME || '.' || N1.NAME REDUNDANT_INDEX, O2.NAME || '.' || N2.NAME SUFFICIENT_INDEX
FROM SYS.ICOL$ IC1
,SYS.ICOL$ IC2 ,SYS.IND$ I1 ,SYS.OBJ$ N1 ,SYS.OBJ$ N2 ,SYS.USER$ O1 ,SYS.USER$ O2
WHERE IC1.POS# = 1 AND IC2.BO# = IC1.BO# AND IC2.OBJ# != IC1.OBJ# AND IC2.POS# = 1 AND IC2.INTCOL# = IC1.INTCOL# AND I1.OBJ# = IC1.OBJ# AND BITAND(I1.PROPERTY, 1) = 0 AND (SELECT MAX(POS#) * (MAX(POS#) + 1) / 2
FROM SYS.ICOL$ WHERE OBJ# = IC1.OBJ#) = (SELECT SUM(XC1.POS#) FROM SYS.ICOL$ XC1 ,SYS.ICOL$ XC2 WHERE XC1.OBJ# = IC1.OBJ# AND XC2.OBJ# = IC2.OBJ# AND XC1.POS# = XC2.POS# AND XC1.INTCOL# = XC2.INTCOL#)
AND N1.OBJ# = IC1.OBJ# AND N2.OBJ# = IC2.OBJ# AND O1.USER# = N1.OWNER# AND O2.USER# = N2.OWNER#;
- 27 공간의 90% 이상을 사용하고 있는 Tablespace
SELECT X.TABLESPACE_NAME
,TOTAL_SIZE / 1024 / 1024 TOTAL_SIZE ,USED_SIZE / 1024 / 1024 USED_SIZE ,(ROUND(USED_SIZE / TOTAL_SIZE, 2)) * 100 USED_RATIO
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL_SIZE
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) X ,(SELECT TABLESPACE_NAME, SUM(BYTES) USED_SIZE FROM DBA_EXTENTS GROUP BY TABLESPACE_NAME) Y
WHERE X.TABLESPACE_NAME = Y.TABLESPACE_NAME(+) AND Y.USED_SIZE > .9 * X.TOTAL_SIZE;
- 28 현재 Extension 횟수가 MaxExtents의 80% 이상인 경우
SELECT TABLESPACE_NAME
,OWNER ,SEGMENT_NAME ,SEGMENT_TYPE ,EXTENTS ,MAX_EXTENTS
FROM SYS.DBA_SEGMENTS S WHERE EXTENTS / MAX_EXTENTS > .8 AND MAX_EXTENTS > 0 ORDER BY TABLESPACE_NAME, OWNER, SEGMENT_NAME;
- 29 Active Session 중 Idle Time이 긴 작업
SELECT VS.SID || ',' || VS.SERIAL# " SID"
,VP.SPID ,VS.MACHINE ,VS.PROGRAM ,VS.MODULE ,VS.STATUS ,TO_CHAR(VS.LOGON_TIME, 'MM/DD HH24:MI') LOGIN_TIME ,ROUND(VS.LAST_CALL_ET / 60) "IDLE"
FROM V$SESSION VS
,V$PROCESS VP
WHERE VS.STATUS = 'ACTIVE' AND VS.SID NOT IN (1, 2, 3, 4, 5, 6, 7) AND VS.PADDR = VP.ADDR ORDER BY 8;
- 30 DBUser 별로 Session 정보를 조회
SELECT S.USERNAME
,S.SID ,S.SERIAL# ,P.SPID ,S.OSUSER ,S.MACHINE ,S.PROGRAM ,TO_CHAR(S.LOGON_TIME, 'MM/DD HH24:MI') "LOGON_TIME" ,ROUND(S.LAST_CALL_ET / 60) "IDLE"
FROM V$SESSION S
,V$PROCESS P
WHERE S.PADDR = P.ADDR AND S.USERNAME LIKE UPPER('&DBUSER%') ORDER BY 9;
- 31 사용자 session 중에서 2시간 이상 idle 상태가 지속되는 session을 kill
SET PAGESIZE 0 SPOOL KILLIDLE3.SQL
SELECT DISTINCT '!KILL -9 ' || B.SPID, 'ALTER SYSTEM KILL SESSION || A.SID || ',' || A.SERIAL# || ;' FROM V$SESSION A
,V$PROCESS B
WHERE A.PADDR IN (SELECT S.PADME, A.COLUMN_ID ORDER BY A.COLUMN_ID, B.POSITION) RN
FROM (SELECT COL.OWNER ,COL.TABLE_NAME ,COL.COLUMN_ID ,COL.COLUMN_NAME ,COL.DATA_TYPE ,COL.DATA_LENGTH ,COL.DATA_PRECISION ,COL.DATA_SCALE ,COL.NULLABLE ,COM.COMMENTS FROM DBA_TAB_COLUMNS COL ,DBA_COL_COMMENTS COM WHERE COL.COLUMN_NAME = COM.COLUMN_NAME AND COL.OWNER = COM.OWNER AND COL.TABLE_NAME = COM.TABLE_NAME AND COM.OWNER = :IN_OWNER AND COM.TABLE_NAME LIKE :IN_TABLE_NAME || '%') A ,DBA_CONS_COLUMNS B WHERE B.TABLE_NAME(+) = A.TABLE_NAME AND B.COLUMN_NAME(+) = A.COLUMN_NAME) X
WHERE X.RN = 1 ORDER BY X.TABLE_NAME, X.COLUMN_ID;
--: 일반 사용자 용
SELECT TABLE_NAME
,COLUMN_NAME ,PK ,COLUMN_NAME ,DATA_TYPE || '( ' || NVL(DATA_TYPE_2, DATA_LENGTH) || ' )' DATA_TYPE ,NULLABLE ,COMMENTS
FROM (SELECT A.TABLE_NAME
,A.COLUMN_ID ,B.POSITION PK ,A.COLUMN_NAME ,A.DATA_TYPE ,A.DATA_PRECISION || DECODE(A.DATA_SCALE, NULL, NULL, ',' || A.DATA_SCALE) DATA_TYPE_2 ,A.DATA_LENGTH ,A.DATA_PRECISION ,A.DATA_SCALE ,A.NULLABLE ,A.COMMENTS ,ROW_NUMBER() OVER (PARTITION BY A.TABLE_NAME, A.COLUMN_ID ORDER BY A.COLUMN_ID, B.POSITION) RN FROM (SELECT COL.TABLE_NAME ,COL.COLUMN_ID ,COL.COLUMN_NAME ,COL.DATA_TYPE ,COL.DATA_LENGTH ,COL.DATA_PRECISION ,COL.DATA_SCALE ,COL.NULLABLE ,COM.COMMENTS FROM USER_TAB_COLUMNS COL ,USER_COL_COMMENTS COM WHERE COL.COLUMN_NAME = COM.COLUMN_NAME AND COL.TABLE_NAME = COM.TABLE_NAME AND COM.TABLE_NAME LIKE :IN_TABLE_NAME || '%') A ,USER_CONS_COLUMNS B WHERE B.TABLE_NAME(+) = A.TABLE_NAME AND B.COLUMN_NAME(+) = A.COLUMN_NAME) X
WHERE X.RN = 1 ORDER BY X.TABLE_NAME, X.COLUMN_ID;
- 56 특정 테이블의 인덱스 확인
/*
인덱스를 확인 하고자 할때 사용하는 쿼리
- /
SELECT C.TABLE_NAME
,C.INDEX_NAME ,C.COLUMN_NAME ,C.COLUMN_POSITION ,T.NUM_ROWS
FROM ALL_IND_COLUMNS C
,(SELECT TABLE_NAME, NUM_ROWS FROM ALL_TABLES WHERE OWNER = 'ESTDBA' AND TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE :IN_TABLE_NAME || '%') AND NUM_ROWS > 0) T
WHERE C.TABLE_NAME = T.TABLE_NAME ORDER BY T.NUM_ROWS DESC, C.TABLE_NAME, C.INDEX_NAME, C.COLUMN_POSITION;
- 57 다중 UPDATE 쿼리
/*
BYPASS_UJVC 힌트를 이용하면 특정 뷰를 만들어서 컬럼 대 컬럼을 대입 할 수 있습니다. 아래 쿼리는 메타정보관리용 유저로서 코멘트의 내용 중에서 정규표현식을 이용하여 관련 데이터를 조작 하는 방법 입니다.
- /
UPDATE /*+ BYPASS_UJVC */
( SELECT X.* ,CASE WHEN UNIT_INSTR > 0 THEN SUBSTR(X.COLUMN_DESC, X.UNIT_INSTR + 3, 1) END B_UNIT FROM (SELECT A.SERVER ,A.OWNER ,A.TABLE_NAME ,A.COLUMN_NAME ,A.COLUMN_DESC ,A.GRP_CD A_GRP_CD ,A.UNIT A_UNIT ,REGEXP_INSTR(COLUMN_DESC, '단위\:') UNIT_INSTR FROM DBA_MYCOL A WHERE A.SERVER = :IN_SERVER AND A.OWNER = :IN_OWNER AND A.TABLE_NAME = :IN_TABLE_NAME) X)
SET A_UNIT = B_UNIT
UPDATE_DT = SYSDATE;
- 58 상호 DB간에 컬럼 이름 비교
/*
양쪽 DB에서 사용하는 테이블 중에서 컬럼 이름 다른 항목을 찾는다.
- /
SELECT A.TABLE_NAME
,A.COLUMN_NAME ,A.COLUMN_ID ,A.DATA_TYPE || '(' || A.DATA_LENGTH || ')' DATA_TYPE
FROM USER_TAB_COLUMNS@LINK_ESTDB A WHERE A.TABLE_NAME = :IN_TABLE_NAME AND NOT EXISTS
(SELECT 'X' FROM USER_TAB_COLUMNS B WHERE B.TABLE_NAME = A.TABLE_NAME AND B.COLUMN_NAME = A.COLUMN_NAME);
SELECT A.TABLE_NAME, A.COLUMN_NAME, A.COLUMN_ID FROM USER_TAB_COLUMNS@LINK_ESTDB A WHERE A.TABLE_NAME = :IN_TABLE_NAME MINUS SELECT A.TABLE_NAME, A.COLUMN_NAME, A.COLUMN_ID FROM USER_TAB_COLUMNS A WHERE A.TABLE_NAME = :IN_TABLE_NAME
- 59 해당 테이블의 세션을 제거하는 쿼리
/*
특정 테이블이 락을 발생하고 있으면 세션을 찾아서 중단시킨다.
- /
SELECT 'ALTER SYSTEM KILL SESSION || S.SID||','||S.SERIAL# ||;' FROM V$LOCK L, DBA_OBJECTS O, V$SESSION S WHERE L.ID1 = O.OBJECT_ID AND S.SID = L.SID AND O.OWNER = 'ESTDBA' AND O.OBJECT_NAME = 'TMP_GSYM2'
- 60 CPU를 많이 사용하는 세션의 식별(SQL TEXT 조회)
SELECT A.*
,(SELECT SS.SQL_TEXT FROM V$SQLAREA SS WHERE SS.ADDRESS = A.SQL_ADDRESS AND ROWNUM <= 1 ) AS SQL_TEST
FROM (
SELECT A.SID ,C.SERIAL# ,A.VALUE ,C.USERNAME ,C.STATUS ,C.PROGRAM ,C.SQL_ADDRESS ,ROW_NUMBER() OVER (ORDER BY A.VALUE DESC) RN FROM V$SESSTAT A ,V$STATNAME B ,V$SESSION C WHERE A.STATISTIC# = B.STATISTIC# AND A.SID = C.SID AND B.NAME = 'CPU used by this session' AND A.VALUE > 0 AND C.STATUS = 'ACTIVE' AND C.USERNAME IS NOT NULL ) A
WHERE A.RN <= 10;
- 61 Blocking Lock Session 확인
SELECT B.BLOCKING_SESSION AS BLOCKING_SESSION_SID
,C.SID AS LOCK_SESSION_SID ,C.OWNER AS OBJECT_OWNER ,C.OBJECT AS OBJECT ,B.LOCKWAIT ,A.PIECE ,A.SQL_TEXT AS SQL
FROM V$SQLTEXT A
,V$SESSION B ,V$ACCESS C
WHERE A.ADDRESS = B.SQL_ADDRESS AND A.HASH_VALUE = B.SQL_HASH_VALUE AND B.SID = C.SID AND B.BLOCKING_SESSION IS NOT NULL AND C.OWNER NOT IN ('SYS', 'PUBLIC') AND C.OBJECT NOT IN ('TOAD_PLAN_TABLE') ORDER BY A.PIECE;
- 62 현재 세션에서 10초이상 걸리는 쿼리 조회 (SELECT절)
SELECT ABS(SYSDATE - A.LAST_LOAD_TIME) * 24 * 60 * 60 AS SEC_TIEM, A.*
FROM V$SQLAREA A
,V$SESSION B
WHERE A.SQL_TEXT LIKE '%SELECT%' AND A.ADDRESS = B.SQL_ADDRESS AND B.STATUS = 'ACTIVE' AND A.ELAPSED_TIME >= 10 * 1000000 -- 실행계획에서 10초 이상 걸리는 쿼리를 조회(실제 걸리는 시간은 아님.) AND A.PARSING_SCHEMA_NAME NOT IN ('SYS', 'SYSTEM', 'SYSMAN') AND B.USERNAME IS NOT NULL;
- 63 현재 세션에서 PGA, UGA, CPU 사용량 세션별로 조회하는 쿼리
SELECT B.USERNAME
,A.SID ,A.PGA_USAGE ,A.UGA_USAGE ,A.CPU_USAGE_SECONDS ,B.MACHINE ,B.PROGRAM ,B.MODULE
FROM (SELECT B.SID
,MAX(DECODE(C.NAME, 'session pga memory', TRUNC(B.VALUE / 1024 / 1024) || 'MB', 0)) AS PGA_USAGE ,MAX(DECODE(C.NAME, 'session uga memory', TRUNC(B.VALUE / 1024 / 1024) || 'MB', 0)) AS UGA_USAGE ,MAX(DECODE(C.NAME, 'CPU used by this session', (B.VALUE / 100) || ' Sec', 0)) AS CPU_USAGE_SECONDS FROM V$SESSTAT B ,V$STATNAME C WHERE B.STATISTIC# = C.STATISTIC# GROUP BY B.SID) A ,V$SESSION B
WHERE B.SID = A.SID AND B.STATUS = 'ACTIVE' AND B.USERNAME IS NOT NULL;