[오라클-튜닝]. 유용한 스크립트 모음

데이터베이스 사랑넷에 있는 글이었던 걸 옛날 블로그에서 펌질했었죠.

옛날 블로그에서 다시 옮겨옵니다. (아직 유효한 정보인지 모르겠지만 옛날 블로그 폐쇄를 위해...)

테이블이 사용중인 블록 크기를 계산해주는 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;