깡통

lock 걸린 테이블 및 유저찾기
 
column username format a10
column lockwait format a10
column sql_text format a80
column object_owner format a14
column object format a15
select b.username username, c.sid sid, c.owner object_owner,
c.object object, b.lockwait, a.sql_text 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.lockwait = 'WAIT'
and b.sid = c.sid
and c.owner != 'SYS';
 
리소스를 많이 차지 하는 sql찾기
 
1) 총 메모리 사용량이 많은 SQL (상위 N개)
SELECT BUFFER_GETS,DISK_READS,EXECUTIONS,SQL_TEXT
FROM (SELECT BUFFER_GETS,DISK_READS,EXECUTIONS,SQL_TEXT
FROM V$SQLAREA
ORDER BY BUFFER_GETS DESC )
WHERE ROWNUM <= :p_rank

2) 평균 메모리 사용량이 많은 SQL (상위 N개)
SELECT BUFFER_GETS,DISK_READS,
EXECUTIONS,BUFFER_PER_EXEC,SQL_TEXT
FROM ( SELECT BUFFER_GETS,DISK_READS, EXECUTIONS,
BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) BUFFER_PER_EXEC,
SQL_TEXT
FROM V$SQLAREA
ORDER BY BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) DESC )
WHERE ROWNUM <= :p_rank

3) 메모리, 디스크 사용량이 일정 기준치를 넘은 SQL
SELECT BUFFER_GETS,DISK_READS,
EXECUTIONS,BUFFER_GETS/EXECUTIONS,SQL_TEXT
FROM V$SQLAREA
WHERE BUFFER_GETS > :p_val1
OR DISK_READS > :p_val2
OR EXECUTIONS > :p_val3
 
실행중인 쿼리 보기
 
select s.sid, s.username username, s.osuser, s.program, a.sql_text SQL
from v$sqltext a, v$session s
where a.address = s.sql_address
and a.hash_value = s.sql_hash_value
 
출처 : http://blog.empas.com/goldenant
 
 
테이블이 사용중인 블록 크기를 계산해주는 SQL

/*
** Table이 사용하는 블럭 크기를 구하는 스크립트... <<박제용>>
**
** 사용법 : 1) DBA 권한으로 로그인한다.
** 2) SQL> @tab_block [table명]
**
** Notice : sum(blocks)는 사용하는 블럭의 갯수이며 사이즈는 db_block_size를
** 곱하여 얻을 수 있다.
*/
SELECT OWNER, TABLESPACE_NAME, SEGMENT_NAME, SUM(BLOCKS)
FROM DBA_EXTENTS
WHERE SEGMENT_NAME = UPPER('&1')
GROUP BY OWNER, TABLESPACE_NAME, SEGMENT_NAME
/

이미 컴파일된 프로시져소스를 보고싶을 때 사용하는 스크립트

/*
** PL/SQL 소스를 보기위한 스크립트.. <박제용>
**
** 사용법 : find_plsql [프로시져명칭]
**
**
*/
select text
from user_source
where name = upper('&1')
order by line;

테이블을 복사해주는 스크립트 (v8.0 only)

/*
** table을 다른 스키마 혹은 table로 복사 <<박제용>>
**
** Notice) 1. Oracle 8.0 이상에서만 지원.
** 2. sql*net 이 설정되어 있어야만 한다.
** 3. 테이블과 PK만 복사하고 인덱스는 모두 다시 생성해주어야 한다.
** 따라서 테이블을 생성해 주고 입력하는것이 좋다.
** 4. sql*plus 에서만 실행된다.
** 사용법) @tab_copy scott/tiger@link source_table_name target_table_name
**
*/
copy from &1 create &3 using select * from &2

/* 다른 DB로 복사할때는
copy from &1 to &2 create &4 using select * from &3
*/

/* 미리 만들어진 table에 입력할때는
copy from &1 insert &3 using select * from &2
*/

Table Data Size를 정확히 계산해주는 스크립트

/*
** Table Data Size를 정확히 계산해주는 스크립트. <<박제용>>
**
** 사용법 : @tab_size [table_name]
**
*/
analyze table &1 delete statistics;
analyze table &1 compute statistics;

SELECT GREATEST(4, ceil(NUM_ROWS/
( (round(((1958-(INI_TRANS*23))*((100-PCT_FREE)/100))/AVG_ROW_LEN)))) * 2048) TableSize_Kbytes
FROM user_tables
WHERE table_name = upper('&1');

dead lock이 발생했을때 발생시킨 유저와 SQL문을 찾아주는 SQL

/*　　
**　　
**　 사용법　　 :SQL> @find_deadlock
**　 Description : 데드락이 발생할 경우 locking 된 유저와 sql문을 보여준다.
**　　
**　 데드락이 발생한 유저를 kill 하려면.
** Alter system kill session '{serial#},{SID}';
**
*/
Select a.serial#, a.sid, a.username, b.id1, c.sql_text
from v$session a, v$lock b, v$sqltext c
where b.id1 in( select distinct e.id1 from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and c.hash_value = a.sql_hash_value
and b.request = 0;

딕셔너리에서 해당 키워드에 관한 뷰, 테이블을 찾아주는 SQL

/*
** 딕셔너리로부터 입력한 키워드에 관한 테이블명을 조회한다. <<박제용>>
**
** 사용법 : SQL> @dic_find [키워드(대소문자가림)]
**
*/
col TABLE_NAME format a15
col COMMENTS format a100

select * from dictionary
where COMMENTS like ('%&1%')
/

컬럼명만 가지고 테이블과 설정상태를 찾아주는 SQL

/*
** 컬럼의 스펙과, 소속 테이블을 찾는다. <<박제용>>
**
** 사용법 : SQL> @col_find [컬럼명]
**
*/
col CNAME format a20
col COLTYPE format a10
col NULLS format a5
col DEFAULTVAL format a10

select TNAME, COLNO, CNAME, COLTYPE, WIDTH, NULLS, DEFAULTVAL
from col
where CNAME = UPPER('&1')
/

Constraint 이름으로 해당 테이블과 컬럼찾는 SQL

/*
**=============================================
** CONSTRAINT 이름으로 사용 테이블 찾기
**=============================================
**
** Usage : @Show_Columns Constraint_Name
** Description : Shows The Columns Bound By A Constraint
** 사용예 : SQL> @show_Columns PK_EMPNO
*/
SET VERIFY OFF
CLEAR BREAK
BREAK ON CONSTRAINT_NAME ON TABLES

SELECT SUBSTR(CONSTRAINT_NAME,1,25) CONSTRAINT_NAME,
SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,15) COL_NAME
FROM ALL_CONS_COLUMNS
WHERE CONSTRAINT_NAME = UPPER('&1');

컬럼에 걸려있는 constraint 를 보여주는 SQL

/*
**=======================================
** 해당 COLUMN에 걸려 있는 CONSTRAINT확인
**=======================================
**
** Usage : @Show_Constraints Table_Name Column_Name
**
** Description : 해당 Table의 Column에 걸려 있는 Constraint를 보여준다.
**
** < 실행 예 >
** SQL> @SHOW_CONSTRAINTS WIDGETS LENGTH
**
*/

SET VERIFY OFF
CLEAR BREAK
BREAK ON TABLES ON COL_NAME
SELECT SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,15) COL_NAME,
SUBSTR(CONSTRAINT_NAME,1,25) CONSTRAINT_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = UPPER('&1')
AND COLUMN_NAME = UPPER('&2');

PK와 FK간의 연관관계를 찾아 보여주는 SQL

/*　　
**　　
**　 사용법　　 　 :> @Show_Positions　 Parent_Table　 Child_Table　　
**　 Description　 :　 Shows Primary And Foreign Key Positions　　
**　　
**　 WARNING　　 :　 이 문장은 해당 Table의 Constraint생성시 Naming　　　
**　　　　　　　　　 Convention을 따른 경우에 적용되도록 되어 있다.　　
**
*/
SET VERIFY OFF　　
CLEAR BREAK　　
BREAK ON CONSTRAINT_NAME ON TABLES
SELECT SUBSTR(CONSTRAINT_NAME,1,27) CONSTRAINT_NAME,
SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,15) COL_NAME,
SUBSTR(POSITION,1,3) POSITION,
SUBSTR(OWNER,1,7) OWNER
FROM USER_CONS_COLUMNS WHERE TABLE_NAME = UPPER('&1') AND CONSTRAINT_NAME LIKE 'PK%'
UNION
SELECT SUBSTR(CONSTRAINT_NAME,1,27) CONSTRAINT_NAME,
SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,25) COL_NAME,
SUBSTR(POSITION,1,3) POSITION,
SUBSTR(OWNER,1,7) OWNER
FROM USER_CONS_COLUMNS WHERE TABLE_NAME = UPPER('&2') AND CONSTRAINT_NAME LIKE 'FK%'
ORDER BY 1 DESC,4 ASC;

테이블의 특정 컬럼에 중복된 값을 찾는 SQL

/*
**=============================================
** 중복된 값 있는지 찾기
**=============================================
** Usage : @중복찾기.sql [테이블명] [중복을조사할컬럼명]
**
** Warning : 똑같은값이 2개 이상있을때 처음값은 출력 않되고 2번째 값부터 출력됨. <>
*/

select * from &1 A
where rowid >
(SELECT min(rowid) FROM &1 B
WHERE B.&2 = A.&2)
order by &2;