컬럼에 걸려있는 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');컬럼명만 가지고 테이블과 설정상태를 찾아주는 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')
/딕셔너리에서 해당 키워드에 관한 뷰, 테이블을 찾아주는 SQL
/*
** 딕셔너리로부터 입력한 키워드에 관한 테이블명을 조회한다. <<박제용>>
**
** 사용법 : SQL> @dic_find [키워드(대소문자가림)]
**
*/
col TABLE_NAME format a15
col COMMENTS format a100
select * from dictionary
where COMMENTS like ('%&1%')
/dead lock이 발생했을때 발생시킨 유저와 SQL문을 찾아주는 SQL
/*
**
** 사용법 :SQL> @find_deadlock
** Description : 데드락이 발생할 경우 locking 된 유저와 sql문을 보여준다.
**
** 데드락이 발생한 유저를 kill 하려면.
** Alter system kill session '{SID},{serial#}';
**
*/
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
/*
** 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')테이블을 복사해주는 스크립트 (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 */
이미 컴파일된 프로시져소스를 보고싶을 때 사용하는 스크립트.
/*
** PL/SQL 소스를 보기위한 스크립트.. <박제용>
**
** 사용법 : find_plsql [프로시져명칭]
**
**
*/
select text
from user_source
where name = upper('&1')
order by line;테이블이 사용중인 블록 크기를 계산해주는 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
/SQL Cursor를 보여주는 스크립트
/* SQL Cursor를 조사하는 스크립트. ** <<박제용 99.11>> ** SQL Cursor 를 조사하여 부하가 많이 걸리는 SQL문과 ** 메모리를 조사한다. ** loads : 캐쉬에서 나갔다 들어온 횟수(best=1). ** invalidations : LRU에서 무효화된 횟수. 이 값이 4이상이면 ** shared_pool_area를 확장해야한다. ** parse_calls : 이 커서의 호출 수. ** sorts : 수행된 소트횟수 ** command_type: 2 - insert, 3-select, 4-update, 7-delete */ select sql_text, loads, invalidations, parse_calls, sorts FROM v$sqlarea WHERE sql_text NOT LIKE '%$%' AND command_type IN(2,3,6,7);
explain을 보기 쉽게 출력해주는 SQL
/*
** expain plan 결과를 보기 쉽게 출력해주는 스크립트.
**
** 1) expain을 처음 사용할 경우엔 [ORACLE_HOME]/rdbms/admin/utlxplan.sql을 실행,
** plan_table을 생성한다.
** 2) 처음 사용이 아니면 delete from plan_table; 을 실행하여 이전 결과를 삭제.
**
** 실행결과 파싱번호(id)가 길면 SQL이 비효율적이거나, shared_pool_size가 작은것이다.
** 기타 SQL문이 인덱스를 사용하는지 등등을 알수 있다.
*/
col operation format a30
col options format a20
col id format 99
select id, lpad(' ',2*level) || operation ||
decode(id, 0, ' Cost= ' || position )"operation",
options, object_name "object"
from plan_table
connect by prior id=parent_id
start with id =0;
get_time() 을 이용한 쿼리 수행시간 측정
/* -- SQL*PLUS time estimate sample -- By Jeyong Park -- */ -- Example 1) variable t number variable elapsed varchar2(20) -------------------------------------------------------------------------------- -- Read the clock at the start call Dbms_Utility.get_Time() into :t; -------------------------------------------------------------------------------- --Spool my_history.log -- calll script for estimate time -- @@your_script.sql -------------------------------------------------------------------------------- -- Read the clock at the end DECLARE t1 number; BEGIN t1 := Dbms_Utility.get_Time(); :elapsed := TO_CHAR( ((t1-:t)/6000.0), '999.9')||' min'; END; / PRINT elapsed -------------------------------------------------------------------------------- Spool Off -- Exmample 2) set serveroutput on DECLARE time_0 BINARY_INTEGER; time_1 BINARY_INTEGER; x number; BEGIN time_0 := DBMS_UTILITY.GET_TIME; -- put your query in here -- time_1 := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE (time_1 - time_0); END;