select table_name,total_phys_io
from ( select owner||'.'||object_name as table_name,
sum(value) as total_phys_io
from v$segment_statistics
where owner!='SYS' and object_type='TABLE'
and statistic_name in ('physical reads','physical reads direct',
'physical writes','physical writes direct')
group by owner||'.'||object_name
order by total_phys_io desc)
where rownum <=10;
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;
날짜 계산 SQL 조회(336)
Database | 2004/12/22 (수) 13:42 공감하기(0) | 스크랩하기(1)
날짜 계산 SQL
select months_between(sysdate,to_date('2002-12-22','yyyy-mm-dd'))
-- months_between(A,B) = A-B/30
--select add_months(sysdate,4) -- 특정일의 달수 더한 날
--select next_day(sysdate,'friday') -- 특정일의 다음주 요일
--select last_day(sysdate) -- 특정일의 해당 월의 마지막 날
--select round(sysdate,'dd') -- 특정일의 반올림(오후면 다음날..)
--select trunc(sysdate,'ww') -- 특정일의 주 첫일 찾기
from dual
날짜계산
/* 어제 */ 날짜칼럼 BETWEEN TRUNC(SYSDATE-1) AND TRUNC(SYSDATE-1)+0.99999421
/* 오늘 */ 날짜칼럼 BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 0.99999421
/* 내일 */ 날짜칼럼 BETWEEN TRUNC(SYSDATE+1) AND TRUNC(SYSDATE+1)+0.99999421
/* 금주 */ 날짜칼럼 BETWEEN TRUNC(SYSDATE+1)-TO_CHAR(SYSDATE,'D')
AND TRUNC(SYSDATE+1)-TO_CHAR(SYSDATE,'D')+6.99999421
/* 차주 */ 날짜칼럼 BETWEEN TRUNC(SYSDATE+8)-TO_CHAR(SYSDATE, 'D')
AND TRUNC(TRUNC(SYSDATE)+14.99999421)-TO_CHAR(SYSDATE, 'D')
/* 금월 */ 날짜칼럼 BETWEEN TRUNC(SYSDATE+1)-TO_CHAR(SYSDATE,'DD')
AND TRUNC(LAST_DAY(SYSDATE))+0.99999421
/* 전월 */ 날짜칼럼 BETWEEN TRUNC(ADD_MONTHS(SYSDATE,-1)+1)-TO_CHAR(SYSDATE,'DD')
AND TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)))+0.99999421
/* 차월 */ 날짜칼럼 BETWEEN ADD_MONTHS(TRUNC(SYSDATE),1)-TO_CHAR(SYSDATE,'DD')+1
AND LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE),1)+0.99999421)
# 특정일 까지의 간격을 년, 개월, 일로 표현하기
SELECT
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('19970101', 'YYYYMMDD'))/12) "년",
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('19970101', 'YYYYMMDD')) -
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('19970101', 'YYYYMMDD'))/12) * 12) "개월",
TRUNC((MONTHS_BETWEEN(SYSDATE,TO_DATE('19970101', 'YYYYMMDD')) -
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('19970101', 'YYYYMMDD')))) * 30.5) "일"
FROM DUAL;
SELECT
'20040511' as "날짜"
, ceil((to_number(substrb('20040511', -2, 2)) + 7 - to_number(TO_CHAR(TO_DATE('20040511','YYYYMMDD'),'D')))/7) as "월별 주차"
from dual;
Prev
Rss Feed