'Dev/DataBase'에 해당되는 글 23건
- Ms sql 링크 서버 DB 연결하기 2012/03/06
- MSSQL 스키마 변경 2012/01/17
- [ 파이어버드 ] Outer Join 2011/05/03
- [ 파이버버드 ] SubString 사용법 2011/05/02
- [오라클] 권한 추가 / 제거 2011/03/22
- [오라클] trunc 날짜 계산 2011/02/11
- Oracle DBMS_JOB 사용법(생성, 정지, 수정, 삭제) 2011/02/08
- 롤백 세그먼트란 ? 2011/02/01
- 오라클 동적 쿼리를 이용한 CURSOR 사용법 2011/01/13
- 오라클 split 함수 만들기 2011/01/13
- 오라클 프로시저 다른 계정의 TABLE 참조 2011/01/12
- 오라클 주민번호로 나이대별 인원수 구하기 2010/11/01
- 오라클 주민번호로 성별 구하기 2010/11/01
- 오라클 중복DATA 삭제 2009/10/16
- [oracle] ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다 2009/10/07
- 데이터베이스 및 ASP 세션을 사용한 ASP 보안 구현 2009/09/16
- 오라클(ORACLE) 데이터 형 변환 함수 2009/09/15
- [ORACLE] 잡매니저 2009/08/21
- 오라클 페이징 2009/08/07
- 오라클 인덱스 1 2009/07/09
- 오라클 인덱스 생성 2009/07/09
- 오라클 인덱스 2009/07/09
- SQL 날짜 관련 2009/01/30
Ms sql 링크 서버 DB 연결하기Ms sql 링크 서버 DB 연결하기
Posted at 2012/03/06 17:27 | Posted in Dev/DataBase
링크 서버 연결
EXEC sp_addlinkedserver
@server='링크 서버 명',
@srvproduct='', -- 먼지 잘.... (안적어도 됨)
@provider = 'SQLOLEDB', -- 해당 디비에 맞는 공급자를 선택
@datasrc = '127.0.0.1', -- 아이피 (정확히는 네트워크 이름을 적어줍니다.)
@provstr='', -- 먼지 잘.... (안적어도 됨)
@catalog='' -- 카다로그 이름(안적어도 됨)
연결 되었는지 확인
select * from master.dbo.sysservers where srvname = '링크 서버 명'
링크 서버 DB 로그인
EXEC sp_addlinkedsrvlogin '링크 서버 명', 'false', NULL, 'id', 'pw'
링크 서버 DB 로그아웃
exec sp_droplinkedsrvlogin @rmtsrvname = '링크 서버 명', @locallogin = NULL
링크 서버 연결 종료
exec sp_dropserver @server='링크 서버 명'
'Dev > DataBase' 카테고리의 다른 글
| Ms sql 링크 서버 DB 연결하기 (0) | 2012/03/06 |
|---|---|
| MSSQL 스키마 변경 (0) | 2012/01/17 |
| [ 파이어버드 ] Outer Join (0) | 2011/05/03 |
| [ 파이버버드 ] SubString 사용법 (0) | 2011/05/02 |
| [오라클] 권한 추가 / 제거 (0) | 2011/03/22 |
| [오라클] trunc 날짜 계산 (0) | 2011/02/11 |
MSSQL 스키마 변경MSSQL 스키마 변경
Posted at 2012/01/17 15:37 | Posted in Dev/DataBase
'Dev > DataBase' 카테고리의 다른 글
| Ms sql 링크 서버 DB 연결하기 (0) | 2012/03/06 |
|---|---|
| MSSQL 스키마 변경 (0) | 2012/01/17 |
| [ 파이어버드 ] Outer Join (0) | 2011/05/03 |
| [ 파이버버드 ] SubString 사용법 (0) | 2011/05/02 |
| [오라클] 권한 추가 / 제거 (0) | 2011/03/22 |
| [오라클] trunc 날짜 계산 (0) | 2011/02/11 |
[ 파이어버드 ] Outer Join[ 파이어버드 ] Outer Join
Posted at 2011/05/03 19:04 | Posted in Dev/DataBaseFROM TABLE A LEFT OUTER JOIN TABLE B ON A.Column = B.Column
'Dev > DataBase' 카테고리의 다른 글
| Ms sql 링크 서버 DB 연결하기 (0) | 2012/03/06 |
|---|---|
| MSSQL 스키마 변경 (0) | 2012/01/17 |
| [ 파이어버드 ] Outer Join (0) | 2011/05/03 |
| [ 파이버버드 ] SubString 사용법 (0) | 2011/05/02 |
| [오라클] 권한 추가 / 제거 (0) | 2011/03/22 |
| [오라클] trunc 날짜 계산 (0) | 2011/02/11 |
[ 파이버버드 ] SubString 사용법[ 파이버버드 ] SubString 사용법
Posted at 2011/05/02 09:56 | Posted in Dev/DataBaseSUBSTRING(ACCEPT_Date FROM 1 FOR 6)
'Dev > DataBase' 카테고리의 다른 글
| MSSQL 스키마 변경 (0) | 2012/01/17 |
|---|---|
| [ 파이어버드 ] Outer Join (0) | 2011/05/03 |
| [ 파이버버드 ] SubString 사용법 (0) | 2011/05/02 |
| [오라클] 권한 추가 / 제거 (0) | 2011/03/22 |
| [오라클] trunc 날짜 계산 (0) | 2011/02/11 |
| Oracle DBMS_JOB 사용법(생성, 정지, 수정, 삭제) (0) | 2011/02/08 |
[오라클] 권한 추가 / 제거[오라클] 권한 추가 / 제거
Posted at 2011/03/22 16:29 | Posted in Dev/DataBase
1. 권한 추가:
GRANT ALL PRIVILEGES ON 테이블명 TO 사용자명
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON 테이블명 TO 사용자
GRANT SELECT, INSERT, UPDATE, DELETE ON 테이블명 TO 사용자
2. 권한 제거
revoke SELECT on 테이블명 from 사용자
revoke CREATE, DROP, INDEX, ALTER on 테이블명 from 사용자
'Dev > DataBase' 카테고리의 다른 글
| [ 파이어버드 ] Outer Join (0) | 2011/05/03 |
|---|---|
| [ 파이버버드 ] SubString 사용법 (0) | 2011/05/02 |
| [오라클] 권한 추가 / 제거 (0) | 2011/03/22 |
| [오라클] trunc 날짜 계산 (0) | 2011/02/11 |
| Oracle DBMS_JOB 사용법(생성, 정지, 수정, 삭제) (0) | 2011/02/08 |
| 롤백 세그먼트란 ? (0) | 2011/02/01 |
[오라클] trunc 날짜 계산[오라클] trunc 날짜 계산
Posted at 2011/02/11 11:17 | Posted in Dev/DataBase
1) 년
trunc(sysdate, 'YYYY') 또는 trunc(sysdate, 'YEAR')
2) 월
trunc(sysdate, 'MON')
3) 일
trunc(sysdate, 'DDD') 또는 trunc(sysdate, 'DD')
4) 주
trunc(sysdate, 'W') => 매월 1일의 요일을 기준한 주초일자 (해당월 1일이 금요일이면 금요일) trunc(sysdate, 'WW') => 매년 1월 1일의 요일을 기준한 주초일자 (1월 1일이 화요일이면 화요일)
trunc(sysdate, 'IW') => 주 초 월요일 일자
trunc(sysdate, 'DY') => 주 초 일요일 일자
'Dev > DataBase' 카테고리의 다른 글
| [ 파이버버드 ] SubString 사용법 (0) | 2011/05/02 |
|---|---|
| [오라클] 권한 추가 / 제거 (0) | 2011/03/22 |
| [오라클] trunc 날짜 계산 (0) | 2011/02/11 |
| Oracle DBMS_JOB 사용법(생성, 정지, 수정, 삭제) (0) | 2011/02/08 |
| 롤백 세그먼트란 ? (0) | 2011/02/01 |
| 오라클 동적 쿼리를 이용한 CURSOR 사용법 (0) | 2011/01/13 |
Oracle DBMS_JOB 사용법(생성, 정지, 수정, 삭제)Oracle DBMS_JOB 사용법(생성, 정지, 수정, 삭제)
Posted at 2011/02/08 11:21 | Posted in Dev/DataBase1. Job의 생성.
DECLARE jobno NUMBER(5);
begin
dbms_job.submit(jobno, 'USP_TEST;', sysdate, 'sysdate + 1/24/60', FALSE );
commit;
end;
-- 위에꺼 재설명 하자면...
DECLARE jobno NUMBER(5);
begin
dbms_job.submit(
jobno, -- job의 번호. output 변수임.
'USP_TEST;', -- job이 실행할 쿼리 및 SP. single quatation('~') 으로 감싸야함.
sysdate, -- job이 실행될 시간.
'sysdate + 1/24/60' -- job의 실행간격. 여기서는 1분간격. '~' 으로 감싸야함.
);
commit;
end;
SELECT * FROM user_jobs; 실행하여 생성여부 확인.
2. Job의 정지, 수정, 삭제
실행 : exec dbms_job.run(jobno);
정지 : exec dbms_job.broken(jobno, TRUE);
수정 : exec dbms_job.change(:jobno, USP_TEST;', sysdate, 'sysdate + 1/24/6');
-- 10분 간격
삭제 : exec dbms_job.remove(jobno);
'Dev > DataBase' 카테고리의 다른 글
| [오라클] 권한 추가 / 제거 (0) | 2011/03/22 |
|---|---|
| [오라클] trunc 날짜 계산 (0) | 2011/02/11 |
| Oracle DBMS_JOB 사용법(생성, 정지, 수정, 삭제) (0) | 2011/02/08 |
| 롤백 세그먼트란 ? (0) | 2011/02/01 |
| 오라클 동적 쿼리를 이용한 CURSOR 사용법 (0) | 2011/01/13 |
| 오라클 split 함수 만들기 (0) | 2011/01/13 |
롤백 세그먼트란 ?롤백 세그먼트란 ?
Posted at 2011/02/01 12:47 | Posted in Dev/DataBase롤백 세그먼트란 ?
롤백 세그먼트란?
- 롤백 세그먼트는 트랜잭션 진행시 그 이전 이미지를 기록하기 위해 사용되는 CIRCULAR구조의 세그먼트입니다.
- 롤백 세그먼트는 프로세스가 데이터베이스의 데이터에 변경 할 때 이전 값을 저장하는데 사용됩니다.
- 롤백 세그먼트는 수정되기 전의 파일, 블록 ID같은 블럭 정보 및 데이터를 저장합니다.
- 롤백 세그먼트의 헤더는 현재 세그먼트를 사용하고 있는 트랜잭션에 대한 정보를 저장하는 있는 트랜잭션 테이블을 포함하고 있습니다.
- 트랜잭션은 단 하나의 롤백 세그먼트에 롤백(실행 취소) 기록 전부를 기록합니다.
- 많은 트랜잭션이 동시에 하나의 롤백 세그먼트에 쓰기를 할 수 있습니다.
롤백 세그먼트의 목적
롤백 세그먼트는 트랜잭션 롤백, 트랜잭션 복구, 읽기 일관성을 제공하기 위해서 사용 됩니다.
트랜잭션 롤백
트랜잭션이 테이블내의 행을 변경할 때 Before image를 롤백세그먼트에 저장함으로써 트랜잭션이 롤백되는 경우에 다시 데이터값을 행으로 옮겨서 원래의 값으로 복원하는데 사용합니다.
트랜잭션 복구
트랜잭션이 수행되고 있을 때 인스턴스가 비정상적으로 종료하면 오라클 서버는 데이터베이스를 다시 오픈할 때 커밋되지 않은 변경 사항을 롤백해야 합니다.
트랜잭션 복구라 불리는 이와 같은 롤백은 롤백 세그먼트에 가해진 변경 사항 역시 리두로그 파일에 의해서 보호되어야만 가능합니다.
읽기 일관성
트랜잭션이 수행되고 있을 때 데이터베이스의 다른 사용자는 이 트랜잭션이 커밋하지 않은 변경된 데이터를 볼 수가 없습니다.
또한 SELECT문이 실행된 시점에서는 그 이전에 커밋된 데이터 까지의 정보만 볼 수 있습니다. SELECT문 수행도중 다른 사용자에 의해 변경된 데이터는 볼 수 없습니다.
트랜잭션과 롤백세그먼트
1) 트랜잭션이 시작하려면 반드시 롤백 세그먼트가 있어야 합니다.
2) 오라클은 트랜잭션을 여러 롤백세그먼트 중에서 가장 적은 수의 트랜잭션이 지정된 롤백세그먼트를 설정하여 할당해 줍니다
3) 만약 사용자가 특정 트랜잭션에 대해서 롤백 세그먼트를 지정하고 싶으면 아래의 명령어를 사용하면 됩니다. (큰 트랜잭션을 따로 수행할 경우 많이 사용 합니다. )
SET TRANSACTION USE ROLLBACK SEGMENT 롤백세그먼트이름
4) 트랜잭션은 현재의 롤백세그먼트 내의 Extent가 꽉차면 다른 익스텐트로 옮겨 가는데(이 단계를 WRAP라고 부름) 만약 다음 익스텐트가 활성화 중이면 새로운 익스텐트를 생성 합니다. 롤백세그먼트는 원형구조로 되어 있기 때문에 다음이나 그 다음 익스텐트로 건너 뛸수 없습니다.
5) 롤백 세그먼트의 한 익스텐트에 하나 이상의 트랜잭션이 쓰기를 할 수 있습니다. 하지만, 롤백 세그먼트의 각 블록은 오직 하나의 트랜잭션에서 나온 정보만을 기록할 수 있습니다.
작성자 : 김정식 (oramaster _at_ naver.com)
작성일 : 2003-03-17
강좌 URL : http://www.oracleclub.com/lecture/1726
이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
오라클클럽의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
'Dev > DataBase' 카테고리의 다른 글
| [오라클] trunc 날짜 계산 (0) | 2011/02/11 |
|---|---|
| Oracle DBMS_JOB 사용법(생성, 정지, 수정, 삭제) (0) | 2011/02/08 |
| 롤백 세그먼트란 ? (0) | 2011/02/01 |
| 오라클 동적 쿼리를 이용한 CURSOR 사용법 (0) | 2011/01/13 |
| 오라클 split 함수 만들기 (0) | 2011/01/13 |
| 오라클 프로시저 다른 계정의 TABLE 참조 (0) | 2011/01/12 |
오라클 동적 쿼리를 이용한 CURSOR 사용법오라클 동적 쿼리를 이용한 CURSOR 사용법
Posted at 2011/01/13 10:26 | Posted in Dev/DataBase쿼리를 동적으로 생성하여 SELECT 한 DATA 를 가지고
작업을 해야할 경우 사용하면 좋은 방법
몇시간의 구글링과 몇시간의 삽질로 완성 ㅠㅠ
DECLARE
v_tabname NUMBER; -- 대상 테이블
v_fldname VARCHAR2(100); -- 대상에서 읽을 필드명
v_val1 VARCHAR2(100); -- 읽은 값 저장할 변수
v_val2 VARCHAR2(100); -- 읽은 값 저장할 변수
v_val3 VARCHAR2(100); -- 읽은 값 저장할 변수
TYPE type_cur is REF CURSOR; -- 리퍼런스 커서 타입 선언
cur_test type_cur; -- 커서타입으로 커서 선언
vCurSql VARCHAR2(32767);
OPEN
vCur_Sql := 'SELECT name, emp, money FROM ' || v_tabname;
OPEN cur_test FOR vCurSql;
LOOP
FETCH cur_test INTO v_val1, v_val2, v_val3;
작업 코딩
EXIT WHEN cur_test%NOTFOUND;
END LOOP;
CLOSE cur_test;
END;
'Dev > DataBase' 카테고리의 다른 글
| Oracle DBMS_JOB 사용법(생성, 정지, 수정, 삭제) (0) | 2011/02/08 |
|---|---|
| 롤백 세그먼트란 ? (0) | 2011/02/01 |
| 오라클 동적 쿼리를 이용한 CURSOR 사용법 (0) | 2011/01/13 |
| 오라클 split 함수 만들기 (0) | 2011/01/13 |
| 오라클 프로시저 다른 계정의 TABLE 참조 (0) | 2011/01/12 |
| 오라클 주민번호로 나이대별 인원수 구하기 (0) | 2010/11/01 |
오라클 split 함수 만들기오라클 split 함수 만들기
Posted at 2011/01/13 10:15 | Posted in Dev/DataBase구글링을 통해 얻은 함수..
이대로 만들어서 사용하면 됨
CREATE OR REPLACE function split
(
p_list varchar2,
p_del varchar2 := '^'
) return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(32767) := p_list;
l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
pipe row(substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));
else
pipe row(l_list);
exit;
end if;
end loop;
return;
end split;
그러나 이상태로 컴파일을 하면
split_tbl 이 부분에서 오류가 남
그럴땐
create or replace type split_tbl as table of varchar2(32767);
type을 생성해 주면 됨
'Dev > DataBase' 카테고리의 다른 글
| 롤백 세그먼트란 ? (0) | 2011/02/01 |
|---|---|
| 오라클 동적 쿼리를 이용한 CURSOR 사용법 (0) | 2011/01/13 |
| 오라클 split 함수 만들기 (0) | 2011/01/13 |
| 오라클 프로시저 다른 계정의 TABLE 참조 (0) | 2011/01/12 |
| 오라클 주민번호로 나이대별 인원수 구하기 (0) | 2010/11/01 |
| 오라클 주민번호로 성별 구하기 (0) | 2010/11/01 |
오라클 프로시저 다른 계정의 TABLE 참조오라클 프로시저 다른 계정의 TABLE 참조
Posted at 2011/01/12 09:27 | Posted in Dev/DataBase
프로시저를 만들다 보면
table 없습니다. 라는 오류가 뜨는경우가 있다
select * from aaa.table
위 구문을 실행하면 SELECT 가 잘 되지만
프로시저에서 실행을 시키려고 하면 table이 없단다 -_-;;;
그럴땐 아래와 같은 방법으로 권한을 줄 수 있다.
GRANT SELECT ON SCOTT.EMP TO TESTER;
GRANT INSERT ON SCOTT.EMP TO TESTER;
GRANT UPDATE ON SCOTT.EMP TO TESTER;
GRANT DELETE ON SCOTT.EMP TO TESTER;
SCOTT 계정으로 접속을 하여 EMP 테이블의 권한을 TESTER 에게 주는거
'Dev > DataBase' 카테고리의 다른 글
| 오라클 동적 쿼리를 이용한 CURSOR 사용법 (0) | 2011/01/13 |
|---|---|
| 오라클 split 함수 만들기 (0) | 2011/01/13 |
| 오라클 프로시저 다른 계정의 TABLE 참조 (0) | 2011/01/12 |
| 오라클 주민번호로 나이대별 인원수 구하기 (0) | 2010/11/01 |
| 오라클 주민번호로 성별 구하기 (0) | 2010/11/01 |
| 오라클 중복DATA 삭제 (0) | 2009/10/16 |
오라클 주민번호로 나이대별 인원수 구하기오라클 주민번호로 나이대별 인원수 구하기
Posted at 2010/11/01 16:57 | Posted in Dev/DataBase이방법밖에 없는건가....
일단 구하긴 했는데, 흠....
select decode(ROUND(age / 5), 0, '0~4', 1, '5~9', 2, '10~14', 3, '15~19', 4, '20~24', 5, '25~29', 6, '30~34', 7, '35~39', 8, '40~44', 9, '45~49', 10, '50~54', 11, '55~59', 12, '60~64', 13, '65~69', 14, '70~74', 15, '75~79', 16, '80~84', 17, '85~89', 18, '90~94', 19, '95~99') as gp, COUNT(*)
from (
select to_number(to_char(sysdate, 'yyyy')) - to_number(decode(substr(register_no, 7, 1), '1', '19', '2', '19', '20')||substr(register_no, 0, 2)) + 1 as age
from (
select distinct a.bc_id, b.register_no
from bc_recharge_log a, bc_account_info b
where createdate >= '2010-07-01'
and createdate <= '2010-11-01'
and status in ('3', '4')
and b.bc_id = a.bc_id(+)
)
)
group by decode(ROUND(age / 5), 0, '0~4', 1, '5~9', 2, '10~14', 3, '15~19', 4, '20~24', 5, '25~29', 6, '30~34', 7, '35~39', 8, '40~44', 9, '45~49', 10, '50~54', 11, '55~59', 12, '60~64', 13, '65~69', 14, '70~74', 15, '75~79', 16, '80~84', 17, '85~89', 18, '90~94', 19, '95~99')
order by 1 asc
'Dev > DataBase' 카테고리의 다른 글
| 오라클 split 함수 만들기 (0) | 2011/01/13 |
|---|---|
| 오라클 프로시저 다른 계정의 TABLE 참조 (0) | 2011/01/12 |
| 오라클 주민번호로 나이대별 인원수 구하기 (0) | 2010/11/01 |
| 오라클 주민번호로 성별 구하기 (0) | 2010/11/01 |
| 오라클 중복DATA 삭제 (0) | 2009/10/16 |
| [oracle] ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다 (0) | 2009/10/07 |
오라클 주민번호로 성별 구하기오라클 주민번호로 성별 구하기
Posted at 2010/11/01 16:54 | Posted in Dev/DataBaseselect DECODE(SUBSTR(주민번호, 7, 1), '1', '남자', '2', '여자', '3', '남자', '4', '여자') as 성별 from dual
역시 Decode 밖에 없는건가...
'Dev > DataBase' 카테고리의 다른 글
| 오라클 프로시저 다른 계정의 TABLE 참조 (0) | 2011/01/12 |
|---|---|
| 오라클 주민번호로 나이대별 인원수 구하기 (0) | 2010/11/01 |
| 오라클 주민번호로 성별 구하기 (0) | 2010/11/01 |
| 오라클 중복DATA 삭제 (0) | 2009/10/16 |
| [oracle] ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다 (0) | 2009/10/07 |
| 데이터베이스 및 ASP 세션을 사용한 ASP 보안 구현 (0) | 2009/09/16 |
오라클 중복DATA 삭제오라클 중복DATA 삭제
Posted at 2009/10/16 09:47 | Posted in Dev/DataBase1. 테이블 생성을 통한 중복데이터 삭제
처리 방법: 중복을 제외한 테이블을 생성 -> 기존 테이블 삭제 -> 신규테이블을 명칭 변경
Ex)
CREATE TABLE emp2 AS SELECT distinct * FROM emp;
DROP TABLE emp;
RENAME emp2 TO emp;
2. RowID를 이용한 중복데이터 삭제
처리 방법 : 중복된 Row를 찾아서, ROWID가 더큰것을 삭제
Ex)
DELETE FROM 테이블 A
WHERE ROWID > (SELECT MIN(ROWID)
FROM 테이블 B
WHERE A.PROFNO=B.PROFNO)
'Dev > DataBase' 카테고리의 다른 글
| 오라클 주민번호로 나이대별 인원수 구하기 (0) | 2010/11/01 |
|---|---|
| 오라클 주민번호로 성별 구하기 (0) | 2010/11/01 |
| 오라클 중복DATA 삭제 (0) | 2009/10/16 |
| [oracle] ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다 (0) | 2009/10/07 |
| 데이터베이스 및 ASP 세션을 사용한 ASP 보안 구현 (0) | 2009/09/16 |
| 오라클(ORACLE) 데이터 형 변환 함수 (0) | 2009/09/15 |
[oracle] ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다[oracle] ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다
Posted at 2009/10/07 14:32 | Posted in Dev/DataBase[oracle] ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다
의 오류 메세지가 있는 경우..
1. 예를 들어 lock이 걸려 있는 테이블이 TEST라고 가정하면 다음 SELECT 문으로 TEST 테이블에 lock을 걸고 있는 session을 찾는다.
SELECT A.SID, A.SERIAL# 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='TEST'
SID SERIAL#
--- -------
25 12345
36 16789
--- -------
25 12345
36 16789
2. SESSION들을 KILL한다.
alter system kill session '25, 12345';
'Dev > DataBase' 카테고리의 다른 글
| 오라클 주민번호로 성별 구하기 (0) | 2010/11/01 |
|---|---|
| 오라클 중복DATA 삭제 (0) | 2009/10/16 |
| [oracle] ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다 (0) | 2009/10/07 |
| 데이터베이스 및 ASP 세션을 사용한 ASP 보안 구현 (0) | 2009/09/16 |
| 오라클(ORACLE) 데이터 형 변환 함수 (0) | 2009/09/15 |
| [ORACLE] 잡매니저 (0) | 2009/08/21 |
데이터베이스 및 ASP 세션을 사용한 ASP 보안 구현데이터베이스 및 ASP 세션을 사용한 ASP 보안 구현
Posted at 2009/09/16 09:20 | Posted in Dev/DataBase
요약
이 문서에서는 ASP(Active Server Pages) 응용 프로그램에 대해 폼 기반 보안을 구현하는 방법을 단계별로 설명합니다. 응용 프로그램의 보안을 유지해야 하는 경우, 인증된 사용자만 허용하려는 경우 및 사용자가 내부 도메인에 속하지 않는 경우(예: 인터넷 사용자)에 이 메커니즘을 사용할 수 있습니다. 이 예제에서는 데이터베이스를 사용하여 사용자 정보를 저장한 다음 데이터베이스를 기준으로 사용자가 유효한지 확인합니다.전제 조건
- Microsoft Windows NT 4.0 Workstation, Windows NT 4.0 Server, Windows 2000 Professional, Windows 2000 Server 또는 Windows 2000 Advanced Server
- Windows NT 4.0이 실행되는 컴퓨터의 경우 Microsoft Internet Information Server(IIS) 4.0, Windows 2000이 실행되는 컴퓨터의 경우 IIS 5.0
- Microsoft SQL Server 6.5 이상
응용 프로그램 디자인
이 절에서는 ASP 웹 응용 프로그램에서 폼 기반의 보안 또는 사용자 지정 보안을 구현하는 데 필요한 단계를 간략히 설명합니다.- 사용자에게 로그온 폼을 제공합니다.
- 사용자 데이터베이스에 저장되어 있는 사용자 정보를 기준으로 사용자 자격 증명이 유효한지 검사합니다.
- 세션 변수를 만들고 해당 값을 userID로 설정합니다.
- 이후에 사용자가 수행하는 모든 요청에 대해 이 세션 변수의 값이 빈 문자열("")이 아닌지 점검하여 사용자가 로그온되어 있는지 여부를 확인합니다.
- 해당 변수가 비어 있으면 사용자가 유효한 사용자가 아니거나 세션에서 로그오프된 것입니다. 해당 변수가 비어 있으면 사용자를 로그온 페이지로 리디렉션합니다.
- 사용자 정보가 데이터베이스에 없기 때문에 로그온에 실패하면 해당 사용자가 사이트에 아직 등록되지 않은 것일 수 있습니다. 이 경우에는 사용자를 Register.asp 페이지로 리디렉션하여 웹 사이트에 등록할 수 있게 합니다. 사용자가 등록을 마치면 해당 사용자의 정보가 사용자 데이터베이스에 추가됩니다.
- 로그온 페이지를 제외한 모든 페이지에 로그오프 페이지에 대한 링크를 제공하여 사용자가 어느 페이지에서나 세션에서 로그오프할 수 있게 합니다. 이 페이지는 userID에 빈 문자열("")을 할당하여 userID를 보유하고 있는 세션 변수를 지웁니다.
사용자 데이터베이스 테이블 만들기
- Windows 시작 메뉴에서 실행을 누른 다음 notepad를 입력하여 메모장을 엽니다.
- 다음 SQL 스크립트를 강조 표시하고 해당 스크립트를 마우스 오른쪽 단추로 클릭한 다음 복사를 누릅니다. 메모장의 편집 메뉴에서 붙여넣기를 누릅니다.
CREATE TABLE [Users] ( [uid] [varchar] (25) NOT NULL , [password] [varchar] (25) NOT NULL , CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ( [uid] ) ON [PRIMARY] ) ON [PRIMARY] GO
- 파일 메뉴에서 저장을 누릅니다. 파일 이름 입력란에 User.txt를 입력합니다.
- Windows 시작 메뉴에서 프로그램과 Microsoft SQL Server를 차례로 가리킨 다음 쿼리 분석기를 누릅니다. SQL Server에 연결 대화 상자에서 SQL 서버 이름, 사용자 ID 및 암호를 지정하여 SQL Server에 연결합니다.
- 파일 메뉴에서 열기를 누릅니다. 열기 대화 상자의 파일 형식 드롭다운 목록 상자에서 모든 파일(*.*)을 누릅니다. 목록에서 User.txt를 누른 다음 열기를 누릅니다.
- 도구 모음의 데이터베이스 드롭다운 목록 상자에서 이 테이블을 만들려는 데이터베이스를 선택합니다. 이 목적에 맞는 데이터베이스가 없으면 Pubs를 눌러 예제 Pubs 데이터베이스에 이 테이블을 만듭니다.
- 데이터베이스를 선택한 후에 쿼리 메뉴에서 실행을 눌러 쿼리를 실행합니다. 이렇게 하면 선택된 데이터베이스에 Users 테이블이 만들어집니다.
가상 디렉터리 만들기 및 구성
- Windows 탐색기에서 웹 루트(기본적으로 <SystemDrive>:\Inetput\Wwwroot) 아래에 ASPSecureAPP라는 폴더를 만듭니다.
- 인터넷 서비스 관리자(Microsoft Management Console 또는 MMC라고도 함)를 엽니다. Microsoft Windows 2000을 실행 중인 컴퓨터에서 인터넷 서비스 관리자를 열려면 다음 단계를 수행합니다.
- Windows 시작 메뉴에서 설정을 가리킨 다음 제어판, 관리 도구를 차례로 가리킵니다.
- 인터넷 서비스 관리자를 눌러 ISM(인터넷 서비스 관리자)을 엽니다.
- Windows 시작 메뉴에서 프로그램, Windows NT 4.0 Option Pack을 차례로 가리킨 다음 Microsoft Internet Information Server를 누릅니다.
- 인터넷 서비스 관리자를 누릅니다.
- 컴퓨터 및 기본 웹 사이트 노드를 눌러 확장합니다. 1단계에서 만든 ASPSecureAPP 폴더를 마우스 오른쪽 단추로 클릭한 다음 등록 정보를 누릅니다.
- 등록 정보 대화 상자의 디렉터리 탭에 있는 응용 프로그램 설정 구역에서 만들기를 눌러 해당 디렉터리를 응용 프로그램으로 표시합니다.
예제 페이지 만들기
참고: 메모장을 사용하여 이러한 페이지를 만드는 경우 파일을 저장할 때 다른 이름으로 저장 대화 상자의 파일 형식 텍스트 상자에서 모든 파일을 선택해야 합니다.Logon.asp
이 페이지에는 사용자 사이트에 액세스하기 위한 사용자 이름과 암호를 입력할 수 있습니다.
다음 코드를 새 ASP 페이지에 복사하고 해당 파일을 Inetpub\Wwwroot 디렉터리의 ASPSecureAPP 폴더에 Logon.asp로 저장합니다.
<% 'The following three lines of code are used to ensure that this page is not cached on the client. Response.CacheControl = "no-cache" Response.AddHeader "Pragma", "no-cache" Response.Expires = -1 %> <html><body> <form action="Validate.asp" method="post"> <P> Login ID:   <INPUT type=text id=UID name=UID> <br> Password:  <input type="password" id="passwd" name="passwd"> </P> <input type="submit" value="Logon" id="submit1" name="submit1"> </form> </body></html>
Validate.asp
사용자가 응용 프로그램에 로그온하기 위해 로그온 정보를 제공하면 이 페이지에서는 사용자 정보가 유효한지 확인하여 사용자를 적절한 페이지로 리디렉션합니다.
다음 코드를 새 ASP 페이지로 복사하고, 유효한 값을 포함하도록 연결 문자열 매개 변수(User ID , Password , Initial Catalog 및 Data Source)를 변경하고, 해당 파일을 Inetpub\Wwwroot 디렉터리의 ASPSecureAPP 폴더에 Validate.asp로 저장합니다.
<%
Response.Buffer=true
'The following three lines of code are used to ensure that this page is not cached on the client.
Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1
Dim userid
Dim Pwd
'Assign the UserID, which the user supplies, to this variable.
userid= Request.Form("UID")
'Check whether userid is an empty string. If it is empty, redirect to Logon.asp.
'If it is not empty, connect to the database, and validate the user.
if userid <> "" then
pwd = Request.Form("passwd")
Dim Cn
Dim Rs
Dim StrConnect
'Specify the connection string to access the database.
'Remember to change the following connection string parameters to reflect the correct values
'for your SQL server.
StrConnect = "Provider=SQLOLEDB.1;User ID=sa;Password=password;Initial Catalog=pubs;" & _
"Network Library=dbmssocn;Data Source=servername"
Set Cn = Server.CreateObject("ADODB.Connection")
Cn.Open StrConnect
Set Rs = Server.CreateObject("ADODB.Recordset")
Rs.Open "Select * from Users where uid='" & userid & "'",Cn
'Check to see if this userID exists in your database.
If Not Rs.EOF then
If strcomp( pwd, Rs.Fields("password").value , 1) = 0 then
'Password is correct. Set a session variable, and redirect the user to a Default.asp page
'or the main page in your application.
Session("UID") = userid
Response.Redirect "Default.asp"
Response.End
Else
'Password is incorrect. Redirect the user to the logon page.
Response.Redirect "Logon.asp"
Response.End
End if
Else
'If the user is not in your database, point him or her to the Register.asp page
'so that he or she can register at your Web site.
Response.Redirect "Register.asp"
Response.End
End if
Else
Response.Redirect "Logon.asp"
Response.End
End if
%>Register.asp
이 페이지에서는 해당 사이트에 액세스하기 위한 사용자 ID와 암호를 등록할 수 있습니다.
다음 코드를 새 ASP 페이지로 복사하고, 유효한 값을 포함하도록 연결 문자열 매개 변수(User ID , Password , Initial Catalog 및 Data Source)를 변경하고, 해당 파일을 Inetpub\Wwwroot 디렉터리의 ASPSecureAPP 폴더에 Register.asp로 저장합니다.
<%
Response.Buffer=true
'The following three lines of code are used to ensure that this page is not cached on the client.
Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1
'Check whether user has submitted user name and password so that you can
'add that user to the users database and register him or her as a valid
'user to use this application.
'This is just the minimal code that you need. You can customize this the way you want.
Dim pwd
Dim userid
userid = Request.Form("uname")
pwd = Request.Form("pwd")
If userid <> "" then
If pwd <> "" then
Dim Cn
Dim Rs
Dim StrConnect
'Specify the connection string to access the database.
'Remember to change the following connection string parameters to reflect the correct values
'for your SQL server.
StrConnect = "Provider=SQLOLEDB.1;User ID=sa;Password=password;" & _
"Initial Catalog=pubs;Network Library=dbmssocn;Data Source=servername"
Set Cn = Server.CreateObject("ADODB.Connection")
Cn.Open StrConnect
Set Rs = Server.CreateObject("ADODB.Recordset")
Rs.Open "Select * from Users where uid='" & userid & "'",Cn,3
If Rs.RecordCount>0 then
Response.Write "The Username that you entered has already been taken by someone else." & _
Response.Write "Use a different Username."
Set Rs = Nothing
Set Cn = Nothing
Else
Dim records
Cn.Execute "INSERT INTO USERS1 (uid,password) VALUES" & _
"('" & userid & "','" & pwd & "')" , records
If records=1 then
Response.Write "You have been registered successfully."
Set Rs = Nothing
Set Cn = Nothing
Session("UID")= userid
Response.Redirect "Default.asp"
Response.End
Else
Response.Write Err.Description
Set Rs = Nothing
Set Cn = Nothing
Response.End
End if
End if
Else
Response.Write "Password is empty. Could not register. Try again."
End if
End if
%>
<html>
<head>
<script language="javascript">
function callsubmit()
{
if (frm1.pwd.value==frm1.pwdc.value) {
frm1.submit();
}
else
{
alert("Password does not match. Re-enter the password");
}
}
</script>
</head>
<body>
<form action="" method="post" id=frm1 name=frm1>
<P>
Login ID:   <INPUT type=text id=uname name=uname> <br>
Password:  <input type="password" id="pwd" name="pwd"> <br>
Confirm Password:  <input type="password" id="pwdc" name="pwdc">
</P>
<input type="button" value="Register" id="submit1" name="submit1" onclick=javascript:callsubmit();>
</form>
</body>
</html>Logoff.asp
이 페이지에서는 로그오프할 수 있습니다.
다음 코드를 새 ASP 페이지에 복사하고 해당 파일을 Inetpub\Wwwroot 디렉터리의 ASPSecureAPP 폴더에 Logoff.asp로 저장합니다.
<%
Response.Buffer=True
'The following three lines of code are used to ensure that this page is not cached on the client.
Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1
'Set the session variable to an empty string and also destroy the session to make
'to complete the user session.
Session("UID")=""
Session.Abandon
Response.Redirect "Logon.asp"
Response.End
%>Default.asp
이 페이지를 사용하여 앞서 설명한 여러 페이지를 테스트할 수 있습니다.
다음 코드를 새 ASP 페이지에 복사하고 해당 파일을 Inetpub\Wwwroot 디렉터리의 ASPSecureAPP 폴더에 Default.asp로 저장합니다.
<%
'The following three lines of code are used to ensure that this page is not cached on the client.
Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1
if session("UID")="" then
Response.Redirect "Logon.asp"
Response.End
else
Response.Write "You are logged on as " & session("UID") & "<br>"
end if
%>
<HTML>
<BODY>
<A HREF="Logoff.asp">Click here to log off</A>
<BODY>
</HTML>페이지에 유효성 검사 코드 추가
다음 코드는 사용자가 응용 프로그램에 이미 로그온한 후 아직 로그오프하지 않았는지 여부를 확인합니다.이 코드 부분을 Logon.asp를 제외한 각 보안 ASP 페이지에 복사합니다. Logon.asp에는 복사하면 안 됩니다. 코드가 각 페이지의 맨 처음에 나타나도록 코드를 맨 위에 붙여 넣어야 합니다.
<%
'The following three lines of code are used to ensure that this page is not cached on the client.
Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1
if session("UID")="" then
Response.Redirect "Logon.asp"
Response.End
end if
%>응용 프로그램의 작동 방식
기본적으로 이 응용 프로그램은 자격 증명을 제공하지 않고도 누구나 볼 수 있는 두 개의 페이지(Logon.asp 및 Register.asp)를 포함합니다. 나머지 페이지를 보기 위해서는 사용자 ID와 암호로 로그온해야 합니다. 따라서 사용자가 로그온 정보가 필요한 페이지를 직접 찾아 보는 경우 사용자 ID와 암호를 제공할 수 있는 Logon.asp 페이지로 리디렉션됩니다. 사용자 암호가 올바르지 않으면 다시 로그온할 수 있습니다.해당 사용자가 데이터베이스에 없으면 사용자는 웹 사이트에 등록할 수 있는 Register.asp 페이지로 리디렉션됩니다. 사용자가 Register.asp 페이지를 통해 웹 사이트에 등록하면 사용자의 정보는 해당 사용자가 유효한지 확인하는 데 사용되는 데이터베이스에 입력됩니다.
문제 해결
- 요구 사항 및 이 응용 프로그램에 적용되는 보안 수준에 따라 Logon.asp에서 SSL(Secure Sockets Layer) 암호화를 사용하여 사용자 자격 증명이 일반 텍스트로 전달되지 않게 할 수 있습니다.
- 이러한 사용자 계정은 Windows 계정에 매핑되지 않습니다. 따라서 Windows 계정으로는 이 응용 프로그램에 직접 로그온할 수 없습니다.
- 이 보안 메커니즘은 ASP 세션 기반 정보를 활용하므로 쿠키가 활성화되지 않은 사용자에게는 적용되지 않습니다.
'Dev > DataBase' 카테고리의 다른 글
| 오라클 중복DATA 삭제 (0) | 2009/10/16 |
|---|---|
| [oracle] ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다 (0) | 2009/10/07 |
| 데이터베이스 및 ASP 세션을 사용한 ASP 보안 구현 (0) | 2009/09/16 |
| 오라클(ORACLE) 데이터 형 변환 함수 (0) | 2009/09/15 |
| [ORACLE] 잡매니저 (0) | 2009/08/21 |
| 오라클 페이징 (0) | 2009/08/07 |
오라클(ORACLE) 데이터 형 변환 함수오라클(ORACLE) 데이터 형 변환 함수
Posted at 2009/09/15 10:50 | Posted in Dev/DataBase3.데이터 형 변환 함수(conversion function)
3-1) ASCIISTR 함수
3-2) BIN_TO_NUM 함수
3-3) CAST 함수
3-4) CHARTOROWID 함수
3-5) COMPOSE 함수
3-6) CONVERT 함수
3-7) HEXTORAW 함수
3-8) NUMTODSINTERVAL 함수
3-9) NUMTOYMINTERVAL 함수
3-10) RAWTOHEX 함수
3-11) RAWTONHEX 함수
3-12) ROWIDTOCHAR 함수
3-13) ROWIDTONCHAR 함수
3-14) TO_CHAR(character) 함수
3-15) TO_CLOB 함수
3-16) TO_DSINTERVAL 함수
3-17) TO_LOB 함수
3-18) TO_MULTI_BYTE 함수
3-19) TO_NCHAR(character) 함수
3-20) TO_NCHAR(datetime) 함수
3-21) TO_NCHAR(number) 함수
3-22) TO_NCLOB 함수
3-23) TO_NUMBER 함수
3-24) TO_SINGLE_BYTE 함수
3-25) TO_YMINTERVAL 함수
3-26) TRANSLATE ... USING 함수
3-27) UNISTR 함수
3-1) ASCIISTR 함수
--------------------------------------------------------------------------------
asciistr('string')의 string의 아스키 문자로 반환한다.
A
【예제】
SQL> select ascii('ABACDE') from dual;
☜ ABACDE의 두번째 A는 A에 움라우트(Umlaut)가 붙은 글씨이다.
ASCIIS
------
ABDCDE
SQL>
3-2) BIN_TO_NUM 함수
--------------------------------------------------------------------------------
이 함수는 2진수 벡터를 10진수로 변환한다.
SQL> select bin_to_num(1,0,1,0) from dual;
BIN_TO_NUM(1,0,1,0)
-------------------
10
SQL>
3-3) CAST 함수
--------------------------------------------------------------------------------
데이터형식이나 collection 형식을 다른 데이터형식이나 다른 collection 형식으로 변환한다.
【예제】데이터형식인 경우
SQL> select current_date from dual;
CURRENT_D
---------
30-JUL-04
SQL> select cast(current_date as timestamp) from dual;
CAST(CURRENT_DATEASTIMESTAMP)
---------------------------------------------------------------------------
30-JUL-04 12.29.15.000000 PM
SQL>
3-4) CHARTOROWID 함수
--------------------------------------------------------------------------------
이 함수는 char, varchar2, nchar, ncharvar2형 데이터 타입을 rowid 형 데이터 타입으로 변경한다.
【예제】
SQL> select name from emp
2 where rowid = chartorowid('AAAHZ+AABAAAMWiAAF');
NAME
----------
jijoe
SQL> select rowid,name from emp;
ROWID NAME
------------------ ----------
AAAHZ+AABAAAMWiAAA Cho
AAAHZ+AABAAAMWiAAB Joe
AAAHZ+AABAAAMWiAAC kim
AAAHZ+AABAAAMWiAAF jijoe
SQL>
여기서 rowid의 의미는 다음과 같다.
AAAHZ+ AAB AAAMWi AAA 객체번호 테이블스페이스번호 블록번호 행번호
3-5) COMPOSE 함수
--------------------------------------------------------------------------------
입력된 스트링을 unicode로 나타낸다.
【예제】
SQL> select compose('aa' || unistr('308') ) from dual;
CO
--
aa
SQL>
3-6) CONVERT 함수
--------------------------------------------------------------------------------
입력된 문자열을 지정한 코드로 변환한다.
공용 문자셋은 살펴보자.
US7ASCII US 7-bit ASCII 문자 WE8DEC 서유럽 8비트 문자 WE8HP HP 서유럽 레이져젯 8비트 문자 F7DEC DEC 프랑스 7비트 문자 WE8EBCDIC500 IBM 서유럽 EBCDIC 코드 페이지 500 WE8PC850 IBM PC 코드 페이지 850 WE8ISO8859P1 ISO 8859 서유럽 8비트 문자
【예제】
SQL> select convert('arirang','we8pc850') from dual;
CONVERT
-------
arirang
SQL>
3-7) HEXTORAW 함수
--------------------------------------------------------------------------------
HEXTORAW(char) 함수는 char, varchar2, nchar, nvarchar2 따위의 문자로 주어지는
hexadecimal digit을 raw 값으로 변환한다.
【예제】
SQL> create table test(raw_col RAW(10));
Table created.
SQL> insert into test VALUES (HEXTORAW('7D'));
1 row created.
SQL> select * from test;
RAW_COL
--------------------
7D
SQL>
3-8) NUMTODSINTERVAL 함수
--------------------------------------------------------------------------------
NUMTODSINTERVAL(n,'char_expr') 함수는 n을 interval day to second로 변환하여 출력한다.
char_expr은 다음 중의 하나이다.
‘DAY’
‘HOUR’
‘MINUTE’
‘SECOND’
【예제】
SQL> select numtodsinterval(100,'MINUTE') from dual;
NUMTODSINTERVAL(100,'MINUTE')
--------------------------------------------------------------------------
+000000000 01:40:00.000000000
SQL>
【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename, hiredate,
2 numtodsinterval(100,'day')+hiredate from emp;
ENAME HIREDATE NUMTODSIN
---------- --------- ---------
SMITH 17-DEC-80 27-MAR-81
ALLEN 20-FEB-81 31-MAY-81
WARD 22-FEB-81 02-JUN-81
JONES 02-APR-81 11-JUL-81
MARTIN 28-SEP-81 06-JAN-82
BLAKE 01-MAY-81 09-AUG-81
CLARK 09-JUN-81 17-SEP-81
SCOTT 19-APR-87 28-JUL-87
KING 17-NOV-81 25-FEB-82
TURNER 08-SEP-81 17-DEC-81
ADAMS 23-MAY-87 31-AUG-87
JAMES 03-DEC-81 13-MAR-82
FORD 03-DEC-81 13-MAR-82
MILLER 23-JAN-82 03-MAY-82
14 rows selected.
SQL>
3-9) NUMTOYMINTERVAL 함수
--------------------------------------------------------------------------------
NUMTOYMINTERVAL(n,'char_expr') 함수는 n을 interval year to month로 변환하여 출력한다.
char_expr은 다음 중의 하나이다.
‘YEAR’
‘MONTH’
【예제】
SQL> select numtoyminterval(30,'month') from dual;
NUMTOYMINTERVAL(30,'MONTH')
---------------------------------------------------------------------------
+000000002-06
SQL>
【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename,hiredate,
2 numtoyminterval(30,'month')+hiredate from emp;
ENAME HIREDATE NUMTOYMIN
---------- --------- ---------
SMITH 17-DEC-80 17-JUN-83
ALLEN 20-FEB-81 20-AUG-83
WARD 22-FEB-81 22-AUG-83
JONES 02-APR-81 02-OCT-83
MARTIN 28-SEP-81 28-MAR-84
BLAKE 01-MAY-81 01-NOV-83
CLARK 09-JUN-81 09-DEC-83
SCOTT 19-APR-87 19-OCT-89
KING 17-NOV-81 17-MAY-84
TURNER 08-SEP-81 08-MAR-84
ADAMS 23-MAY-87 23-NOV-89
JAMES 03-DEC-81 03-JUN-84
FORD 03-DEC-81 03-JUN-84
MILLER 23-JAN-82 23-JUL-84
14 rows selected.
SQL>
3-10) RAWTOHEX 함수
--------------------------------------------------------------------------------
RAWTOHEX(raw) 함수는 raw 값을 hexadecimal 값으로 변환한다.
【예제】
SQL> create table test(raw_col RAW(10));
Table created.
SQL> insert into test VALUES (HEXTORAW('7D'));
1 row created.
SQL> select * from test;
RAW_COL
--------------------
7D
SQL> select rawtohex(raw_col) from test;
RAWTOHEX(RAW_COL)
--------------------
7D
SQL>
3-11) RAWTONHEX 함수
--------------------------------------------------------------------------------
RAWTONHEX(raw) 함수는 raw 값을 nvarchar2형 hexadecimal 값으로 변환한다.
【예제】
SQL> create table test(raw_col RAW(10));
Table created.
SQL> insert into test VALUES (HEXTORAW('7D'));
1 row created.
SQL> select * from test;
RAW_COL
--------------------
7D
SQL> select rawtonhex(raw_col) from test;
RAWTONHEX(RAW_COL)
--------------------
7D
SQL>
3-12) ROWIDTOCHAR 함수
--------------------------------------------------------------------------------
RAWIDTOCHAR(rowid) 함수는 rowid 값을 varchar2 형식의 데이터로 변환한다.
【예제】
SQL> select rowid from test;
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL> select rowid from test
2 where rowidtochar(rowid) like '%AABAA%';
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL>
여기서 rowid의 의미는 다음과 같다.
AAAHbH AAB AAAMXC AAA 객체번호 테이블스페이스번호 블록번호 행번호
3-13) ROWIDTONCHAR 함수
--------------------------------------------------------------------------------
RAWIDTONCHAR(rowid) 함수는 rowid 값을 nvarchar2 형식의 데이터로 변환한다.
【예제】
SQL> select rowid from test;
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL> select rowid from test
2 where rowidtochar(rowid) like '%AABAA%';
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL> select lengthb(rowidtonchar(rowid)), rowidtonchar(rowid)
2 from test;
LENGTHB(ROWIDTONCHAR(ROWID)) ROWIDTONCHAR(ROWID
---------------------------- ------------------
36 AAAHbHAABAAAMXCAAA
SQL>
여기서 rowid의 의미는 다음과 같다.
AAAHbH AAB AAAMXC AAA 객체번호 테이블스페이스번호 블록번호 행번호
3-14) TO_CHAR(character) 함수
--------------------------------------------------------------------------------
이 함수는 nchar, nvarchar2, clob, nclob 형식의 데이터를
데이터베이스 character set으로 변환한다. 즉, 문자로 변환한다.
【형식】
TO_CHAR( nchar| clob | nclob)
【예제】
SQL> select to_char('01110') from dual;
TO_CH
-----
01110
SQL>
3-15) TO_CLOB 함수
--------------------------------------------------------------------------------
이 함수는 LOB 컬럼에 있는 NCLOB나 또는 다른 문자 스트링을 CLOB로 변환한다.
【형식】
TO_CLOBR({ lob_column | char})
【예제】
SQL> select to_clob('corea') from dual;
TO_CLOB('COREA')
--------------------------------------------------------------------------
corea
SQL>
3-16) TO_DSINTERVAL 함수
--------------------------------------------------------------------------------
이 함수는 문자열을 interval day to second 형식으로 변환하는 함수이다.
【형식】
to_dsinterval ( char [ ‘nlsparam’] )
【예제】
SQL> select sysdate,
2 sysdate+to_dsinterval('003 17:00:00') as "3days 17hours later"
3 from dual;
SYSDATE 3days 17h
--------- ---------
04-AUG-04 08-AUG-04
SQL>
3-17) TO_LOB 함수
--------------------------------------------------------------------------------
TO_LOB(long_column) 함수는 LONG, LONG RAW 컬럼의 데이터를 LOB 값으로 변환한다.
【예제】
SQL> create table test2(zz clob);
Table created.
SQL> insert into test2
2 (select to_lob(p.raw_col) from test p);
SQL>
3-18) TO_MULTI_BYTE 함수
--------------------------------------------------------------------------------
TO_MULTI_BYTE(char) 함수는 싱글 바이트 문자를 포함한 모든 문자열을 다중바이트 문자열로 변환한다.
【예제】
SQL> select dump(to_multi_byte('Corea')) from dual;
DUMP(TO_MULTI_BYTE('COREA'))
-----------------------------------------------------
Typ=1 Len=10: 163,195,163,239,163,242,163,229,163,225
SQL>
3-19) TO_NCHAR(character) 함수
--------------------------------------------------------------------------------
이 함수는 문자스트링, clob, nclob 형식의 데이터를 national character set,
즉 nchar으로 변환한다. 이는 translate ... using 문의 경우와 같다.
【형식】
TO_NCHAR({char|clob|nclob} [,fmt [,'nlsparam']])
【예제】
SQL> select to_nchar('Corea') from dual;
TO_NC
-----
Corea
SQL>
3-20) TO_NCHAR(datetime) 함수
--------------------------------------------------------------------------------
이 함수는 date, timestamp, timestamp with time zone, timestamp with local time zone,
interval month to year, interval day to second 형식의 데이터를
nchar 형식의 데이터로 변환한다.
【형식】
TO_NCHAR({datetime|interval} [,fmt [,'nlsparam']])
【예제】
SQL> select to_nchar(sysdate) from dual;
TO_NCHAR(SYSDATE)
------------------------------
05-AUG-04
SQL>
3-21) TO_NCHAR(number) 함수
--------------------------------------------------------------------------------
이 함수는 숫자를 nvarchar2 형식의 데이터로 변환한다.
【형식】
TO_NCHAR(n [,fmt [,'nlsparam']])
【예제】
SQL> select to_nchar(1234) from dual;
TO_N
----
1234
SQL> select to_nchar(rownum) from test;
TO_NCHAR(ROWNUM)
----------------------------------------
1
SQL>
3-22) TO_NCLOB 함수
--------------------------------------------------------------------------------
이 함수는 clob, 문자열 형식의 데이터를 nclob 형식의 데이터로 변환한다.
【형식】
TO_NCLOB({char|lob_column})
【예제】
SQL> select to_nclob('Corea') from dual;
TO_NCLOB('COREA')
--------------------------------------------------------------------------
Corea
SQL>
3-23) TO_NUMBER 함수
--------------------------------------------------------------------------------
이 함수는 숫자를 포함하는 char, varchar2, nchar, nvarchar2 형식의
문자 데이터를 number 형식의 숫자 데이터로 변환한다.
【형식】
TO_NUMBER(char [,fmt [,'nlsparam']])
【예제】
SQL> select to_number('1234') from dual;
TO_NUMBER('1234')
-----------------
1234
SQL>
3-24) TO_SINGLE_BYTE 함수
--------------------------------------------------------------------------------
TO_SINGLE_BYTE(char) 함수는 다중 바이트 문자열을 single byte 문자로 변환한다.
【예제】
SQL> select dump(to_multi_byte('Corea')) from dual;
DUMP(TO_MULTI_BYTE('COREA'))
-----------------------------------------------------
Typ=1 Len=10: 163,195,163,239,163,242,163,229,163,225
SQL> select dump(to_single_byte('Corea')) from dual;
DUMP(TO_SINGLE_BYTE('COREA'))
------------------------------
Typ=1 Len=5: 67,111,114,101,97
SQL> select to_single_byte(chr(65)) from dual;
T
-
A
SQL>
3-25) TO_YMINTERVAL 함수
--------------------------------------------------------------------------------
TO_YMINTERVAL ( char ) 함수는 문자열을 interval year to month 형식으로 변환하는 함수이다.
【예제】
SQL> select sysdate,
2 sysdate+to_yminterval('01-03') "15Months later"
3 from dual;
SYSDATE 15Months
--------- ---------
04-AUG-04 04-NOV-05
SQL>
3-26) TRANSLATE ... USING 함수
--------------------------------------------------------------------------------
이 함수는 텍스트 형식의 데이터를 지정한 문자 형식의 데이터로 변환한다.
【형식】
TRANSLATE ( text USING {CHAR_CS|NCHAR_CS} )
【예제】
SQL> select translate('Corea' USING char_cs) from dual;
TRANS
-----
Corea
SQL> select to_nchar('Corea') from dual;
TO_NC
-----
Corea
SQL>
3-27) UNISTR 함수
--------------------------------------------------------------------------------
UNISTR('string') 함수는 스트링 형식의 데이터를 nchar 형식의 데이터로 변환한다.
【예제】
SQL> select unistr('abc0e50f10f6') from dual;
UNISTR
------
abc??o
SQL> select unistr('Corea') from dual;
UNIST
-----
Corea
SQL>
3-1) ASCIISTR 함수
3-2) BIN_TO_NUM 함수
3-3) CAST 함수
3-4) CHARTOROWID 함수
3-5) COMPOSE 함수
3-6) CONVERT 함수
3-7) HEXTORAW 함수
3-8) NUMTODSINTERVAL 함수
3-9) NUMTOYMINTERVAL 함수
3-10) RAWTOHEX 함수
3-11) RAWTONHEX 함수
3-12) ROWIDTOCHAR 함수
3-13) ROWIDTONCHAR 함수
3-14) TO_CHAR(character) 함수
3-15) TO_CLOB 함수
3-16) TO_DSINTERVAL 함수
3-17) TO_LOB 함수
3-18) TO_MULTI_BYTE 함수
3-19) TO_NCHAR(character) 함수
3-20) TO_NCHAR(datetime) 함수
3-21) TO_NCHAR(number) 함수
3-22) TO_NCLOB 함수
3-23) TO_NUMBER 함수
3-24) TO_SINGLE_BYTE 함수
3-25) TO_YMINTERVAL 함수
3-26) TRANSLATE ... USING 함수
3-27) UNISTR 함수
3-1) ASCIISTR 함수
--------------------------------------------------------------------------------
asciistr('string')의 string의 아스키 문자로 반환한다.
A
【예제】
SQL> select ascii('ABACDE') from dual;
☜ ABACDE의 두번째 A는 A에 움라우트(Umlaut)가 붙은 글씨이다.
ASCIIS
------
ABDCDE
SQL>
3-2) BIN_TO_NUM 함수
--------------------------------------------------------------------------------
이 함수는 2진수 벡터를 10진수로 변환한다.
SQL> select bin_to_num(1,0,1,0) from dual;
BIN_TO_NUM(1,0,1,0)
-------------------
10
SQL>
3-3) CAST 함수
--------------------------------------------------------------------------------
데이터형식이나 collection 형식을 다른 데이터형식이나 다른 collection 형식으로 변환한다.
【예제】데이터형식인 경우
SQL> select current_date from dual;
CURRENT_D
---------
30-JUL-04
SQL> select cast(current_date as timestamp) from dual;
CAST(CURRENT_DATEASTIMESTAMP)
---------------------------------------------------------------------------
30-JUL-04 12.29.15.000000 PM
SQL>
3-4) CHARTOROWID 함수
--------------------------------------------------------------------------------
이 함수는 char, varchar2, nchar, ncharvar2형 데이터 타입을 rowid 형 데이터 타입으로 변경한다.
【예제】
SQL> select name from emp
2 where rowid = chartorowid('AAAHZ+AABAAAMWiAAF');
NAME
----------
jijoe
SQL> select rowid,name from emp;
ROWID NAME
------------------ ----------
AAAHZ+AABAAAMWiAAA Cho
AAAHZ+AABAAAMWiAAB Joe
AAAHZ+AABAAAMWiAAC kim
AAAHZ+AABAAAMWiAAF jijoe
SQL>
여기서 rowid의 의미는 다음과 같다.
AAAHZ+ AAB AAAMWi AAA 객체번호 테이블스페이스번호 블록번호 행번호
3-5) COMPOSE 함수
--------------------------------------------------------------------------------
입력된 스트링을 unicode로 나타낸다.
【예제】
SQL> select compose('aa' || unistr('308') ) from dual;
CO
--
aa
SQL>
3-6) CONVERT 함수
--------------------------------------------------------------------------------
입력된 문자열을 지정한 코드로 변환한다.
공용 문자셋은 살펴보자.
US7ASCII US 7-bit ASCII 문자 WE8DEC 서유럽 8비트 문자 WE8HP HP 서유럽 레이져젯 8비트 문자 F7DEC DEC 프랑스 7비트 문자 WE8EBCDIC500 IBM 서유럽 EBCDIC 코드 페이지 500 WE8PC850 IBM PC 코드 페이지 850 WE8ISO8859P1 ISO 8859 서유럽 8비트 문자
【예제】
SQL> select convert('arirang','we8pc850') from dual;
CONVERT
-------
arirang
SQL>
3-7) HEXTORAW 함수
--------------------------------------------------------------------------------
HEXTORAW(char) 함수는 char, varchar2, nchar, nvarchar2 따위의 문자로 주어지는
hexadecimal digit을 raw 값으로 변환한다.
【예제】
SQL> create table test(raw_col RAW(10));
Table created.
SQL> insert into test VALUES (HEXTORAW('7D'));
1 row created.
SQL> select * from test;
RAW_COL
--------------------
7D
SQL>
3-8) NUMTODSINTERVAL 함수
--------------------------------------------------------------------------------
NUMTODSINTERVAL(n,'char_expr') 함수는 n을 interval day to second로 변환하여 출력한다.
char_expr은 다음 중의 하나이다.
‘DAY’
‘HOUR’
‘MINUTE’
‘SECOND’
【예제】
SQL> select numtodsinterval(100,'MINUTE') from dual;
NUMTODSINTERVAL(100,'MINUTE')
--------------------------------------------------------------------------
+000000000 01:40:00.000000000
SQL>
【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename, hiredate,
2 numtodsinterval(100,'day')+hiredate from emp;
ENAME HIREDATE NUMTODSIN
---------- --------- ---------
SMITH 17-DEC-80 27-MAR-81
ALLEN 20-FEB-81 31-MAY-81
WARD 22-FEB-81 02-JUN-81
JONES 02-APR-81 11-JUL-81
MARTIN 28-SEP-81 06-JAN-82
BLAKE 01-MAY-81 09-AUG-81
CLARK 09-JUN-81 17-SEP-81
SCOTT 19-APR-87 28-JUL-87
KING 17-NOV-81 25-FEB-82
TURNER 08-SEP-81 17-DEC-81
ADAMS 23-MAY-87 31-AUG-87
JAMES 03-DEC-81 13-MAR-82
FORD 03-DEC-81 13-MAR-82
MILLER 23-JAN-82 03-MAY-82
14 rows selected.
SQL>
3-9) NUMTOYMINTERVAL 함수
--------------------------------------------------------------------------------
NUMTOYMINTERVAL(n,'char_expr') 함수는 n을 interval year to month로 변환하여 출력한다.
char_expr은 다음 중의 하나이다.
‘YEAR’
‘MONTH’
【예제】
SQL> select numtoyminterval(30,'month') from dual;
NUMTOYMINTERVAL(30,'MONTH')
---------------------------------------------------------------------------
+000000002-06
SQL>
【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename,hiredate,
2 numtoyminterval(30,'month')+hiredate from emp;
ENAME HIREDATE NUMTOYMIN
---------- --------- ---------
SMITH 17-DEC-80 17-JUN-83
ALLEN 20-FEB-81 20-AUG-83
WARD 22-FEB-81 22-AUG-83
JONES 02-APR-81 02-OCT-83
MARTIN 28-SEP-81 28-MAR-84
BLAKE 01-MAY-81 01-NOV-83
CLARK 09-JUN-81 09-DEC-83
SCOTT 19-APR-87 19-OCT-89
KING 17-NOV-81 17-MAY-84
TURNER 08-SEP-81 08-MAR-84
ADAMS 23-MAY-87 23-NOV-89
JAMES 03-DEC-81 03-JUN-84
FORD 03-DEC-81 03-JUN-84
MILLER 23-JAN-82 23-JUL-84
14 rows selected.
SQL>
3-10) RAWTOHEX 함수
--------------------------------------------------------------------------------
RAWTOHEX(raw) 함수는 raw 값을 hexadecimal 값으로 변환한다.
【예제】
SQL> create table test(raw_col RAW(10));
Table created.
SQL> insert into test VALUES (HEXTORAW('7D'));
1 row created.
SQL> select * from test;
RAW_COL
--------------------
7D
SQL> select rawtohex(raw_col) from test;
RAWTOHEX(RAW_COL)
--------------------
7D
SQL>
3-11) RAWTONHEX 함수
--------------------------------------------------------------------------------
RAWTONHEX(raw) 함수는 raw 값을 nvarchar2형 hexadecimal 값으로 변환한다.
【예제】
SQL> create table test(raw_col RAW(10));
Table created.
SQL> insert into test VALUES (HEXTORAW('7D'));
1 row created.
SQL> select * from test;
RAW_COL
--------------------
7D
SQL> select rawtonhex(raw_col) from test;
RAWTONHEX(RAW_COL)
--------------------
7D
SQL>
3-12) ROWIDTOCHAR 함수
--------------------------------------------------------------------------------
RAWIDTOCHAR(rowid) 함수는 rowid 값을 varchar2 형식의 데이터로 변환한다.
【예제】
SQL> select rowid from test;
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL> select rowid from test
2 where rowidtochar(rowid) like '%AABAA%';
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL>
여기서 rowid의 의미는 다음과 같다.
AAAHbH AAB AAAMXC AAA 객체번호 테이블스페이스번호 블록번호 행번호
3-13) ROWIDTONCHAR 함수
--------------------------------------------------------------------------------
RAWIDTONCHAR(rowid) 함수는 rowid 값을 nvarchar2 형식의 데이터로 변환한다.
【예제】
SQL> select rowid from test;
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL> select rowid from test
2 where rowidtochar(rowid) like '%AABAA%';
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL> select lengthb(rowidtonchar(rowid)), rowidtonchar(rowid)
2 from test;
LENGTHB(ROWIDTONCHAR(ROWID)) ROWIDTONCHAR(ROWID
---------------------------- ------------------
36 AAAHbHAABAAAMXCAAA
SQL>
여기서 rowid의 의미는 다음과 같다.
AAAHbH AAB AAAMXC AAA 객체번호 테이블스페이스번호 블록번호 행번호
3-14) TO_CHAR(character) 함수
--------------------------------------------------------------------------------
이 함수는 nchar, nvarchar2, clob, nclob 형식의 데이터를
데이터베이스 character set으로 변환한다. 즉, 문자로 변환한다.
【형식】
TO_CHAR( nchar| clob | nclob)
【예제】
SQL> select to_char('01110') from dual;
TO_CH
-----
01110
SQL>
3-15) TO_CLOB 함수
--------------------------------------------------------------------------------
이 함수는 LOB 컬럼에 있는 NCLOB나 또는 다른 문자 스트링을 CLOB로 변환한다.
【형식】
TO_CLOBR({ lob_column | char})
【예제】
SQL> select to_clob('corea') from dual;
TO_CLOB('COREA')
--------------------------------------------------------------------------
corea
SQL>
3-16) TO_DSINTERVAL 함수
--------------------------------------------------------------------------------
이 함수는 문자열을 interval day to second 형식으로 변환하는 함수이다.
【형식】
to_dsinterval ( char [ ‘nlsparam’] )
【예제】
SQL> select sysdate,
2 sysdate+to_dsinterval('003 17:00:00') as "3days 17hours later"
3 from dual;
SYSDATE 3days 17h
--------- ---------
04-AUG-04 08-AUG-04
SQL>
3-17) TO_LOB 함수
--------------------------------------------------------------------------------
TO_LOB(long_column) 함수는 LONG, LONG RAW 컬럼의 데이터를 LOB 값으로 변환한다.
【예제】
SQL> create table test2(zz clob);
Table created.
SQL> insert into test2
2 (select to_lob(p.raw_col) from test p);
SQL>
3-18) TO_MULTI_BYTE 함수
--------------------------------------------------------------------------------
TO_MULTI_BYTE(char) 함수는 싱글 바이트 문자를 포함한 모든 문자열을 다중바이트 문자열로 변환한다.
【예제】
SQL> select dump(to_multi_byte('Corea')) from dual;
DUMP(TO_MULTI_BYTE('COREA'))
-----------------------------------------------------
Typ=1 Len=10: 163,195,163,239,163,242,163,229,163,225
SQL>
3-19) TO_NCHAR(character) 함수
--------------------------------------------------------------------------------
이 함수는 문자스트링, clob, nclob 형식의 데이터를 national character set,
즉 nchar으로 변환한다. 이는 translate ... using 문의 경우와 같다.
【형식】
TO_NCHAR({char|clob|nclob} [,fmt [,'nlsparam']])
【예제】
SQL> select to_nchar('Corea') from dual;
TO_NC
-----
Corea
SQL>
3-20) TO_NCHAR(datetime) 함수
--------------------------------------------------------------------------------
이 함수는 date, timestamp, timestamp with time zone, timestamp with local time zone,
interval month to year, interval day to second 형식의 데이터를
nchar 형식의 데이터로 변환한다.
【형식】
TO_NCHAR({datetime|interval} [,fmt [,'nlsparam']])
【예제】
SQL> select to_nchar(sysdate) from dual;
TO_NCHAR(SYSDATE)
------------------------------
05-AUG-04
SQL>
3-21) TO_NCHAR(number) 함수
--------------------------------------------------------------------------------
이 함수는 숫자를 nvarchar2 형식의 데이터로 변환한다.
【형식】
TO_NCHAR(n [,fmt [,'nlsparam']])
【예제】
SQL> select to_nchar(1234) from dual;
TO_N
----
1234
SQL> select to_nchar(rownum) from test;
TO_NCHAR(ROWNUM)
----------------------------------------
1
SQL>
3-22) TO_NCLOB 함수
--------------------------------------------------------------------------------
이 함수는 clob, 문자열 형식의 데이터를 nclob 형식의 데이터로 변환한다.
【형식】
TO_NCLOB({char|lob_column})
【예제】
SQL> select to_nclob('Corea') from dual;
TO_NCLOB('COREA')
--------------------------------------------------------------------------
Corea
SQL>
3-23) TO_NUMBER 함수
--------------------------------------------------------------------------------
이 함수는 숫자를 포함하는 char, varchar2, nchar, nvarchar2 형식의
문자 데이터를 number 형식의 숫자 데이터로 변환한다.
【형식】
TO_NUMBER(char [,fmt [,'nlsparam']])
【예제】
SQL> select to_number('1234') from dual;
TO_NUMBER('1234')
-----------------
1234
SQL>
3-24) TO_SINGLE_BYTE 함수
--------------------------------------------------------------------------------
TO_SINGLE_BYTE(char) 함수는 다중 바이트 문자열을 single byte 문자로 변환한다.
【예제】
SQL> select dump(to_multi_byte('Corea')) from dual;
DUMP(TO_MULTI_BYTE('COREA'))
-----------------------------------------------------
Typ=1 Len=10: 163,195,163,239,163,242,163,229,163,225
SQL> select dump(to_single_byte('Corea')) from dual;
DUMP(TO_SINGLE_BYTE('COREA'))
------------------------------
Typ=1 Len=5: 67,111,114,101,97
SQL> select to_single_byte(chr(65)) from dual;
T
-
A
SQL>
3-25) TO_YMINTERVAL 함수
--------------------------------------------------------------------------------
TO_YMINTERVAL ( char ) 함수는 문자열을 interval year to month 형식으로 변환하는 함수이다.
【예제】
SQL> select sysdate,
2 sysdate+to_yminterval('01-03') "15Months later"
3 from dual;
SYSDATE 15Months
--------- ---------
04-AUG-04 04-NOV-05
SQL>
3-26) TRANSLATE ... USING 함수
--------------------------------------------------------------------------------
이 함수는 텍스트 형식의 데이터를 지정한 문자 형식의 데이터로 변환한다.
【형식】
TRANSLATE ( text USING {CHAR_CS|NCHAR_CS} )
【예제】
SQL> select translate('Corea' USING char_cs) from dual;
TRANS
-----
Corea
SQL> select to_nchar('Corea') from dual;
TO_NC
-----
Corea
SQL>
3-27) UNISTR 함수
--------------------------------------------------------------------------------
UNISTR('string') 함수는 스트링 형식의 데이터를 nchar 형식의 데이터로 변환한다.
【예제】
SQL> select unistr('abc0e50f10f6') from dual;
UNISTR
------
abc??o
SQL> select unistr('Corea') from dual;
UNIST
-----
Corea
SQL>
'Dev > DataBase' 카테고리의 다른 글
| [oracle] ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다 (0) | 2009/10/07 |
|---|---|
| 데이터베이스 및 ASP 세션을 사용한 ASP 보안 구현 (0) | 2009/09/16 |
| 오라클(ORACLE) 데이터 형 변환 함수 (0) | 2009/09/15 |
| [ORACLE] 잡매니저 (0) | 2009/08/21 |
| 오라클 페이징 (0) | 2009/08/07 |
| 오라클 인덱스 1 (0) | 2009/07/09 |
[ORACLE] 잡매니저[ORACLE] 잡매니저
Posted at 2009/08/21 13:45 | Posted in Dev/DataBase주기적으로 수행되어지는 JOB을 JOB QUEUE를 사용하여 스케줄링 할 수 있습니다..
JOB을 스케줄링 하기 위해서 ORACLE의 DBMS_JOB 패키지를 이용합니다..
JOB QUEUE PROCESS가 JOB QUEUE 안의 잡을 수행합니다..
JOB으로 등록될 수 있는 것은 PL/SQL 프로시저 또는 패키지가 되며
예를 들어소스 디비의 테이블들에서 타겟 테이블로 데이터를 적재하는 프로시저를 생성했는데
1분단위로 데이터를 타겟 테이블로 적재를 해야 할 때 DBMS_JOBS에 등록하여
스케줄링 할 수 있습니다.
.JOB_QUEUE_PROCESSES 파라미터가 이와 관련된 초기화 파라미터로 0으로 설정되면
JOB QUEUE PROCESS는 시작되지 않으며 JOB QUEUE의 어느 잡도 수행되지 않습니다..
JOB_QUEUE_PROCESSES 파라미터의 MAX값이 설정되어야 오라클 인스턴스 위에서
동시에 잡을 수행할 수 있다. 설정할 수 있는 최고 값은 1000입니다..
JOB_QUEUE_PROCESSES=60 과 같이 설정할 수 있습니다..
.등록되거나 수행되는 잡에 대해서는 DBA_JOBS 또는 USER_JOBS 딕셔너리 뷰를 통해
확인 할 수 있다.
.JOB_QUEUE_PROCESSES 는 다이나믹 하게 DB를 SHUTDOWN하지 않고 ALTER SYSTEM
명령을 이용해서 설정할 수 있습니다.
ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20;
.JOB QUEUE안의 JOB을 스케줄링 하기 위해서는 DBMS_JOBS패키지를 사용할 수 있으며
JOB_QUEUE를 사용하기 위해 관련된 DB 권한은 없다.
.다음은 DBMS_JOBS의 패키지를 사용하기 위한 패키지의 프로시져들입니다.
SUBMIT - 잡큐의 잡을 등록합니다.
REMOVE - 잡큐의 잡을 제거합니다.
CHANGE - 잡큐의 잡을 변경합니다.
NEXT_DATE - 잡의 다음 수행시간을 변경합니다.
INTERVAL - 잡 수행 주기를 변경합니다.
WHAT - 잡으로 등록된 프로시저 또는 패키지를 변경합니다.
RUN - 잡을 수동으로 강제로 수행합니다.
.새 JOB을 JOB QUEUE에 등록하기 위해 사용되는 파라미터로 DBMS_JOB.SUBMIT()에 들어가느
파라미터 입니다.
JOB - OUTPUT 파라미터로 생성한 잡에 의해 할당되는 식별자 입니다.
WHAT - JOB QUEUE에 등록되는 PL/SQL 프로시저 또는 패키지 입니다.
NEXT_DATE - 잡이 수행되는 다음 시간입니다.
INTERVAL - 잡이 수행되는 주기로 초 단위까지 지정 가능합니다.
JOB_QUEUE에 등록하는 예제입니다.
VARIABLE jobno NUMBER
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'SP_IN_EMP_SAL;',
SYSDATE,
'SYSDATE + 1');
COMMIT;
END;
/
위의 PL/SQL문을 SQL PLUS에서 수행합니다.
첫번째 파라미터가 JOB NUMBER가 부여되는 부분이고
두번째 파라미터가 WHAT으로 SP_IN_EMP_SAL이라는 프로시저를 등록했습니다.
세번째 파라미터가 NEXT_DATE이며 4번째 파라미터가 잡 수행 주기로 하루에 한번씩
수행하라는 의미입니다.
DBMS_JOB을 이용하면 특정시간, 특정요일, 특정일, 30초단위, 매분 정각, 매시정각
등 다양하게 잡을 스케줄링 하는 것이 가능합니다.
ETL 수행 할 때도 유용하게 사용할 수 있습니다.
잡 수행 간격 조정의 예
.SYSDATE+ 7 : 7일에 한번씩 잡이 수행됩니다.
.SYSDATE+1/24 : 1시간에 한번씩 잡이 수행됩니다.
.SYSDATE+30/86400 : 30초에 한번씩 잡이 수행됩니다.
.최초 잡 수행시간이 14:02분일 경우 매시 14:02분에 잡을 수행해야 될 경우
=>trunc(SYSDATE,'MI')+1/24
.최조 잡 수행시간이 06시 이고 매 8시간마다 정각에 잡이 수행되어야 될 경우
=>trunc(SYSDATE,'MI')+8/24
.매주 월요일 정각 3시에 잡이 수행되어야 할 경우
=>NEXT_DAY(TRUNC(SYSDATE),'MONDAY')+15/25
.각 분기마다 첫번째 월요일에 잡이 수행되어야 될 경우
=>NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,'Q'),3),'MONDAY')
주의> dbms_job으로 잡을 스케줄링하게 될 경우 예를 들어 잡 최초 수행시간이 22일 14:00시이고
매시 정각에 잡이 수행되어야 할 경우 SYSDATE+1/24로 간격을 주게 되면 매 정시에 도는 것이
아니라 잡 수행 시간에 따라 약간씩 늦어지게 되어 14:00:04 => 15:00:07 => 16:00:10 이런식으로
잡 수행시간이 잡히게 됩니다. 따라서 정각에 수행되게 하려면 trunc함수를 이용해서 무조건 분에서
잘라내여 00으로 만들어 준다. trunc(SYSDATE,'MI')
잡의 삭제
BEGIN
DBMS_JOB.REMOVE(14443);
END;
/
14443은 잡 번호 이다. USER_JOBS 데이터 딕셔너리 뷰를 보면 잡 번호를 알 수 있습니다.
잡의 변경
BEGIN
DBMS_JOB.CHANGE(14144, NULL, NULL, 'SYSDATE + 3');
END;
/
잡으로 등록된 프로시저/패키지 변경
BEGIN
DBMS_JOB.WHAT(14144,
'SP_IN_EMP_SAL;');
END;
/
잡 다음 수행시간 변경
BEGIN
DBMS_JOB.NEXT_DATE(14144, TRUNC(SYSDATE,'MI') + 4);
END;
/
잡 수행 간격 변경
BEGIN
DBMS_JOB.INTERVAL(14144, TRUNC(SYSDATE,'MI')+30/1440);
END;
/
잡 수행 정지
잡이 BROKEN되면 잡은 수행되지 않으며 강제로 수행 할 시에는 DBMS_JOB.RUN()을 통해 수행합니다..
BEGIN
DBMS_JOB.BROKEN(14144, TRUE);
END;
/
잡큐 정보 VIEWING
DBA_JOBS, USER_JOBS, ALL_JOBS를 이용합니다.
SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN
FROM DBA_JOBS;
JOB NEXT_DATE NEXT_SEC FAILURES B
------- --------- -------- -------- -
9125 01-JUN-01 00:00:00 4 N
14144 24-OCT-01 16:35:35 0 N
9127 01-JUN-01 00:00:00 16 Y
3 rows selected.
DBMS_JOB의 활용예제를 주기적으로 소스테이블에서 타켓 테이블로
적재할 시 어떻게 사용할 수 있는지 간단하게 예를 만들었습니다
--수행될 잡의 목록이 들어갈 테이블
CREATE TABLE JOB_LIST
(JOB_ID VARCHAR2(2),
JOB_TYPE VARCHAR2(1),
JOB_NAME VARCHAR2(30),
JOB_EXEC_HOUR VARCHAR(2),
JOB_PARENTID VARCHAR2(2),
CONSTRAINTS JOB_LIST_PK PRIMARY KEY(JOB_ID)
USING INDEX
TABLESPACE CYS_INDEX
PCTFREE 0
STORAGE(INITIAL 32K NEXT 32K PCTINCREASE 0)
NOLOGGING)
TABLESPACE CYS_DATA
PCTFREE 0
STORAGE(INITIAL 128K NEXT 128K PCTINCREASE 0);
/
CREATE UNIQUE INDEX JOB_LIST_IDX01
ON JOB_LIST(JOB_NAME,JOB_EXEC_HOUR)
TABLESPACE CYS_INDEX
PCTFREE 0
STORAGE(INITIAL 64K NEXT 64K PCTINCREASE 0);
/
-잡 수행 히스토리 테이블로 하루에 한번씩 JOB_LIST 테이블에서 LOG테이블로 JOB_LIST가 복사된다.
CREATE TABLE JOB_LOG
(JOB_ID VARCHAR2(2),
JOB_EXEC_DATE VARCHAR2(8),
JOB_START_TIME DATE,
JOB_END_TIME DATE,
JOB_DATASTART_TIME DATE,
JOB_DATAEND_TIME DATE,
JOB_STATUS VARCHAR2(1),
JOB_ERR_MSG VARCHAR2(100),
CONSTRAINTS JOB_LOG_PK PRIMARY KEY(JOB_ID,JOB_EXEC_DATE)
USING INDEX
TABLESPACE CYS_INDEX
PCTFREE 0
STORAGE(INITIAL 128K NEXT 128K PCTINCREASE 0)
NOLOGGING,
CONSTRAINTS JOB_LIST_FK FOREIGN KEY(JOB_ID)
REFERENCES JOB_LIST(JOB_ID))
TABLESPACE CYS_DATA
STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0)
--JOB_ID를 부여하기 위한 시퀀스
CREATE SEQUENCE JOB_NUM
START WITH 1
INCREMENT BY 1
--하루에 한번 DBMS_JOB을 이용하여 JOB_LIST의 JOB들을 JOB_LOG에 INSERT하기 위한 프로시져
--다음날 수행할 JOB을 LOG로 넣는다.
CREATE OR REPLACE PROCEDURE
SP_IN_JOB_LOG(V_INDATE IN VARCHAR2 DEFAULT NULL)
AS
BEGIN
INSERT INTO JOB_LOG
SELECT JOB_ID,
NVL(V_INDATE,TO_CHAR(SYSDATE+1,'YYYYMMDD')),
NULL,
NULL,
NULL,
NULL,
'N',
NULL
FROM JOB_LIST;
COMMIT;
END SP_IN_JOB_LOG;
--SP_IN_JOB_LOG 프로시저를 DBMS_JOB에 등록한다.
DECLARE
JOB_NUMBER NUMBER;
BEGIN
DBMS_JOB.SUBMIT(JOB_NUMBER, --JOB번호
'SP_IN_JOB_LOG;', --프로시저명
TO_DATE('20050208180000','YYYYMMDDHH24MISS'), --NEXT_DATE
'TRUNC(SYSDATE,''MI'')+1'); --잡 수행 간격(매일 정각 6시)
END;
--JOB_LIST에 수행할 프로시져(JOB)을 등록
--DBA_USERS테이블을 이용해서 24개의 로우를 만들어 낸 후 카테시안 프러덕을 이용
INSERT INTO JOB_LIST
SELECT JOB_NUM.NEXTVAL JOB_ID,
JOB_TYPE,
JOB_NAME,
B.CNT JOB_EXEC_HOUR,
NULL
FROM(
SELECT NULL JOB_ID,
'F' JOB_TYPE,
'SP_IN_F_SALE_SUM' JOB_NAME,
NULL JOB_EXEC_HOUR,
NULL
FROM DUAL) A,
( SELECT LPAD(ROWNUM-1,2,'0') CNT FROM DBA_TABLES
WHERE ROWNUM<25) B
COMMIT;
--JOB_LIST를 JOB_LOG로 INSERT(현재 SP_IN_JOB_LOG 가 다음일을 INSERT하도록 되어 있으므로 해당일을 넣어줌)
EXEC SP_IN_JOB_LOG('20050208');
--제대로 들어갔는지 확인
SELECT * FROM JOB_LOG;
--SOURCE 테이블을 시간단위로 섬머리 해서 TARGET 테이블로 적재하기 위한 프로시져
--ERROR없이 매 시간 돌 때는 파라미터 없이 SP_IN_F_SALE_SUM으로 수행 되고 수동으로 어떤 데이터의
--범위를 적재해야 할 경우 시간의 범위를 파라미터로 넘겨줌
CREATE OR REPLACE PROCEDURE
SP_IN_F_SALE_SUM(V_STARTTIME IN VARCHAR2 DEFAULT NULL,
V_ENDTIME IN VARCHAR2 DEFAULT NULL)
AS
D_STARTTIME DATE;
D_ENDTIME DATE;
V_ERR_MSG VARCHAR2(100);
BEGIN
--프로시저가 파라미터 값이 없이 수행될 경우
IF V_STARTTIME IS NULL AND V_ENDTIME IS NULL THEN
SELECT NVL(JOB_DATAEND_TIME,TRUNC(SYSDATE-1/24,'HH24'))
INTO D_STARTTIME
FROM JOB_LOG
WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST
WHERE JOB_NAME='SP_IN_F_SALE_SUM'
AND JOB_EXEC_HOUR=TO_CHAR(SYSDATE-1/24,'HH24'))
AND JOB_EXEC_DATE=TO_CHAR(SYSDATE,'YYYYMMDD')
AND JOB_STATUS='Y';
D_ENDTIME:=TRUNC(SYSDATE,'HH24');
ELSE
SELECT NVL(JOB_DATAEND_TIME,TO_DATE(V_STARTTIME,'YYYYMMDDHH24MISS'))
INTO D_STARTTIME
FROM JOB_LOG
WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST
WHERE JOB_NAME='SP_IN_F_SALE_SUM'
AND JOB_EXEC_HOUR=SUBSTR(V_STARTTIME,9,2))
AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8)
AND JOB_STATUS='Y';
D_ENDTIME:=TO_DATE(V_ENDTIME,'YYYYMMDDHH24MISS');
END IF;
--수행되는 프로시저의 START시간을 찍어주고 RUNNING으로 표시
UPDATE JOB_LOG
SET JOB_START_TIME=SYSDATE,
JOB_STATUS='R'
WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST
WHERE JOB_NAME='SP_IN_F_SALE_SUM'
AND JOB_EXEC_HOUR=SUBSTR(V_STARTTIME,9,2))
AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8);
-- DML------
--수행되어질 INSERT문
-- DML-----
COMMIT;
--프로시저가 ERROR없이 수행이 끝나면 END 시간과 가져온 데이터의 범위를 찍어줌
UPDATE JOB_LOG
SET JOB_END_TIME=SYSDATE,
JOB_DATASTART_TIME=D_STARTTIME,
JOB_DATAEND_TIME=D_ENDTIME,
JOB_STATUS='Y'
WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST
WHERE JOB_NAME='SP_IN_F_SALE_SUM'
AND JOB_EXEC_HOUR=TO_CHAR(D_ENDTIME,'HH24'))
AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
V_ERR_MSG:= SUBSTRB(SQLERRM, 1, 80);
UPDATE JOB_LOG
SET JOB_END_TIME=SYSDATE,
JOB_STATUS='E',
JOB_ERR_MSG=V_ERR_MSG
WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST
WHERE JOB_NAME='SP_IN_F_SALE_SUM'
AND JOB_EXEC_HOUR=TO_CHAR(D_ENDTIME,'HH24'))
AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8);
COMMIT;
END SP_IN_F_SALE_SUM;
/
--SP_IN_F_SALE_SUM 프로시저를 DBMS_JOB에 등록합니다.
DECLARE
JOB_NUMBER NUMBER;
BEGIN
DBMS_JOB.SUBMIT(JOB_NUMBER, --JOB번호
'SP_IN_F_SALE_SUM;', --프로시저명
TO_DATE('20050209000000','YYYYMMDDHH24MISS'), --NEXT_DATE
'TRUNC(SYSDATE,''MI'')+1/24'); --잡 수행 간격(매시간 정각)
END;
JOB의 시간이나 간격 등록된 프로시저등을 변경하고자 할 때 DBMS_JOB의 다른 프로시져를 이용해서 변경합니다.
2번째 정리 부분에 설명되어 있습니다.
'Dev > DataBase' 카테고리의 다른 글
| 데이터베이스 및 ASP 세션을 사용한 ASP 보안 구현 (0) | 2009/09/16 |
|---|---|
| 오라클(ORACLE) 데이터 형 변환 함수 (0) | 2009/09/15 |
| [ORACLE] 잡매니저 (0) | 2009/08/21 |
| 오라클 페이징 (0) | 2009/08/07 |
| 오라클 인덱스 1 (0) | 2009/07/09 |
| 오라클 인덱스 생성 (0) | 2009/07/09 |
오라클 페이징오라클 페이징
Posted at 2009/08/07 09:23 | Posted in Dev/DataBase오라클의 경우 MYSQL과 같이 LIMIT 기능을 제공하지 않아 페이징 처리하는데 어려움이 따른다.
일반적으로 사람들이 가장 많이 사용하는 방법을 먼저 소개하도록 하겠다.
이 방법은 일방적인 데이터의 양에서는상당히 괜찮은 속도를 보장해준다.
그러나 대용량의 경우 부족한 성능을 보다.
1. 일반적인 오라클 페이징 처리
01.SELECT * FROM ( 02. SELECT A.*, 03. ROWNUM AS RNUM, 04. FLOOR((ROWNUM-1)/{디스플레이수}+1) AS PAGE, 05. COUNT(*) OVER() AS TOTCNT FROM ( 06. {검색쿼리 - 정렬이 필요할 경우 정렬조건 포함} 07. ) A 08.) WHERE PAGE = {페이지번호}; 09. 10.OR11. 12.SELECT * FROM ( 13. SELECT A.*, 14. ROWNUM AS RNUM, 15. COUNT(*) OVER() AS TOTCNT FROM ( 16. {검색쿼리 - 정렬이 필요할 경우 정렬조건 포함} 17. ) A 18.) WHERE RNUM > {범위부터} AND RNUM <= {범위까지};
2. 오라클 대용량 페이징 처리
01.SELECT * FROM ( 02. SELECT ROWNUM AS RNUM, A.* FROM ( 03. {검색쿼리 - 정렬이 필요할 경우 정렬조건 포함} 04. ) A WHERE ROWNUM <= {범위까지} 05.) WHERE RNUM > {범위부터}; 06. 07.OR08. 09.SELECT * FROM ( 10. SELECT /*+ INDEX_ASC or INDEX_DESC(A {정렬조건 인덱스명}) */ 11. ROWNUM AS RNUM, A.* FROM ( 12. {검색쿼리 - 정렬이 필요한 경우 정렬조건을 포함하지 않고 ORACLE 힌트사용} 13. ) A WHERE ROWNUM <= {범위까지} 14.) WHERE RNUM > {범위부터};
'Dev > DataBase' 카테고리의 다른 글
| 오라클(ORACLE) 데이터 형 변환 함수 (0) | 2009/09/15 |
|---|---|
| [ORACLE] 잡매니저 (0) | 2009/08/21 |
| 오라클 페이징 (0) | 2009/08/07 |
| 오라클 인덱스 1 (0) | 2009/07/09 |
| 오라클 인덱스 생성 (0) | 2009/07/09 |
| 오라클 인덱스 (0) | 2009/07/09 |
오라클 인덱스 1오라클 인덱스 1
Posted at 2009/07/09 11:43 | Posted in Dev/DataBase인덱스는 테이블이나 클러스트에서 쓰여지는 선택적인 객체로서, 오라클 데이터베이스 테이블내의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조 입니다.
자동 인덱스 : 프라이머리 키 또는 unique 제한 규칙에 의해 자동적으로 생성되는 인덱스 입니다.
수동 인덱스 : CREATE INDEX 명령을 실행해서 만드는 인덱스들 입니다.
※ Index를 생성하는 것이 좋은 Column
- WHERE절이나 join조건 안에서 자주 사용되는 컬럼
- null값이 많이 포함되어 있는 컬럼
- WHERE절이나 join조건에서 자주 사용되는 두 개이상의 컬럼들
※ 다음과 같은 경우에는 index 생성이 불필요 합니다.
- table이 작을 때
- 테이블이 자주 갱신될 때
※ 오라클 인덱스는 B-tree(binary search tree)에 대한 원리를 기반으로 하고 있습니다.
B-tree인덱스는 컬럼안에 독특한 데이터가 많을 때 가장 좋은 효과를 냅니다.
이 알고리즘 원리는
- 주어진 값을 리스트이 중간점에 있는 값과 비교합니다.
만약 그 값이 더 크면 리스트의 아래쪽 반을 버립니다.
만약 그 값이 더 작다면 위쪽 반을 버립ㄴ디ㅏ.
- 하나의 값이 발견될 때 까지 또는 리스트가 끝날 때까지 그와 같은 작업을 다른 반쪽에도 반복합니다.
※ 인덱스는 B-tree 구조를 가지며 크게 다음 네 가지로 분류될 수 있습니다.
1) Bitmap 인덱스
- 비트맵 인덱스는 각 컬럼에 대해 적은 개수의 독특한 값이 있을 경우에 가장 잘 작동합니다. 그러므로 비트맵 인덱스는 B-tree 인덱스가 사용되지 않을 경우에서 성능을 향상 시킵니다. 테이블이 매우 크거나 수정/변경이 잘 일어나지 않는 경우에 사용할수 있습니다.
SQL> CREATE BITMAP INDEX emp_deptno_indx
ON emp(deptno);
2) Unique 인덱스
- Unique 인덱스는 인덱스를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있는 장점이 있습니다. 프라이머리키와 Unique 제약 조건시 생성되는 인덱스는 Unique 인덱스 입니다.
SQL> CREATE UNIQUE INDEX emp_ename_indx
ON emp(ename);
3) Non-Unique 인덱스
- Non-Unique 인덱스는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질수 있습니다.
SQL> CREATE INDEX dept_dname_indx
ON dept(dname);
4) 결합(Concatenated(=Composite)) 인덱스
- 복수개의 컬럼에 생성할 수 있으며 복수키 인덱스가 가질수 있는 최대 컬럼값은 16개 입니다.
SQL> CREATE UNIQUE INDEX emp_empno_ename_indx
ON emp(empno, ename);
** 인덱스의 삭제 **
- 인덱스의 구조는 테이블과 독립적이므로 인덱스의 삭제는 테이블의 데이터에는 아무런 영향도 미치지 않습니다.
- 인덱스를 삭제하려면 INDEX의 소유자이거나 DROP ANY INDEX권한을 가지고 있어야 합니다.
- INDEX는 ALTER를 할 수 없습니다.
SQL> DROP INDEX emp_empno_ename_indx;
[출처] 9. 오라클 객체(1) [인덱스(Index)]|작성자 요요
'Dev > DataBase' 카테고리의 다른 글
| [ORACLE] 잡매니저 (0) | 2009/08/21 |
|---|---|
| 오라클 페이징 (0) | 2009/08/07 |
| 오라클 인덱스 1 (0) | 2009/07/09 |
| 오라클 인덱스 생성 (0) | 2009/07/09 |
| 오라클 인덱스 (0) | 2009/07/09 |
| SQL 날짜 관련 (0) | 2009/01/30 |
오라클 인덱스 생성오라클 인덱스 생성
Posted at 2009/07/09 10:28 | Posted in Dev/DataBaseCREATE INDEX 인덱스명
ON 테이블명 ("필드" 정렬방법, "필드" 정렬방법)
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64 K
NEXT 0 K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
)
TABLESPACE 테이블스페이스명
LOGGING
NOPARALLEL;
정렬방법은 두 가지가 있습니다. 내림차순은 DESC, 오름차순은 ASC 입니다. 위의 방법에서 "필드" 정렬방법은 여러개 지정이 가능합니다. 컴마(,) 구분자로 말이죠.
'Dev > DataBase' 카테고리의 다른 글
| [ORACLE] 잡매니저 (0) | 2009/08/21 |
|---|---|
| 오라클 페이징 (0) | 2009/08/07 |
| 오라클 인덱스 1 (0) | 2009/07/09 |
| 오라클 인덱스 생성 (0) | 2009/07/09 |
| 오라클 인덱스 (0) | 2009/07/09 |
| SQL 날짜 관련 (0) | 2009/01/30 |
오라클 인덱스오라클 인덱스
Posted at 2009/07/09 10:05 | Posted in Dev/DataBase* 인덱스를 작성해야 하는 경우
대형테이블이나 where 절에서 단순 일치성 검사를 하는 경우 작성해야 하며 where name='설렘이' and blog='설레는 마음으로'와 같은 경우에도 효과적이다. 또한 join시에도 적용이 되며 인덱스가 지정된 열에 대해 is not null과 is null을 사용하는 경우를 제외하고는 where 절에서 사용한 경우 검색 속도가 더욱 빠르다. 그러나 크기가 작은 테이블은 primary key 제약이 아니라면 인덱스가 없는 것이 유리하다. 크기가 작은 테이블은 행이 30개 이하이며 경우에 따라서는 100개 이하도 소규모로 취급하기도 한다.
* 인덱스 자동 생성 조건
primary key 제약이나 unique 제약이 있는 column을 생성하는 경우 자동으로 유일인덱스를 작성한다.
* 인덱스 작성문
create index indexSample on tableSample( columnSample1 [, columnSample2........] );
- indexSample: 인덱스 명칭, 유일한 명칭이며 오라클의 열에 대한 명명 규칙을 지켜야 한다. 인덱스 명칭은 data dictionary view, user_indexes 테이블에도 들어있으며 여기에서 조회가 가능하다.
- 인덱스 명칭에 대한 하나의 예시는 테이블 명과 컬럼명의 조합이다. concert 테이블에 대해 singer와 title의 열에 인덱스를 작성한다면 CONCERT_SINGER_TITLE 이라고 작성할 수 있다.
- tableSample: 테이블 명칭
- columnSample1, columnSample2: 인덱스가 작성되는 해당 열 명
- 인덱스 작성문 예시
create index CONCERT_SINGER_TITLE on concert(singer, title);
* 인덱스가 사용되는 경우
where 절에서 비교 대상이 되는 열에 인덱스가 지정되어 있는 경우 해당 열을 검색 시에 인덱스를 사용한다. 그러므로 중요한 열에 어떻게 인덱스를 지정하는가에 따라 조회 시 응답속도를 단축시킬 수 있다. 또한 관련 열에 인덱스가 지정되어 있으면 join된 조회의 경우에도 조회속도를 높여 줄 수 있다. order by도 인덱스를 사용하지만 distinct와 group by는 모두 인덱스를 사용하지 않는다.
* 인덱스가 무시되는 경우
인덱스의 유무를 탐지할 수 있는 단순 열에 대해 적용되어야만 인덱스의 장점이 활용된다. 즉 substr이나 || 와 같은 함수에 의해 변형되어 있다면 오라클은 인덱스를 사용하지 않는다. 그러나 min과 max도 열 자체와 함께 사용된다면 인덱스의 장점을 활용할 수 있다. 또한 클러스터 인덱스를 제외하고는 null인 열은 인덱스 상에 나타나지 않는다. 하나 이상의 열에 대해 지정된 인덱스는 전체 열 중에 어느 하나라도 null이 아니면 엔트리를 가진다. 모든 열이 null이면 인덱스에 이와 관련된 엔트리는 생기지 않는다.
* 인덱스가 지정된 열의 다양함
인덱스는 많은 양의 data가 다양하게 작성된 열에 대해 가장 유용하다. 즉 'Y'나 'N' 두 개의 값을 갖는 열에는 좋지 못한 선택이다. 실제로 조회를 느려지게 만들 수 있다. 전화번호는 훌륭한 후보이며 지역번호 열은 특히 효과적이다. 기본키가 하나 이상의 열을 포함하고 있는 경우에는 변화가 많은 것을 기본키 제약 조건에 먼저 위치시키는 것이 좋을 것이다. 다양성이 비슷하다면 가장 자주 접근되는 쪽을 먼저 위치시키는 것이 좋다.
설렘이 생각)
실제 인덱스는 다음과 같이 생겼다.
singer title 위치
김장훈 김장훈 스런 콘서트 0000204C.0001.0001
이소은 은소리 콘서트 0000204C.0002.0001
이승환 난리 0000204C.0004.0001
이승환 이승환이 꿈꾸는 음악회 0000204C.0003.0001
그러나 'Y' 나 'N'을 가지는 열에 대한 인덱스는 다음과 같다.
enableYN 위치
N 0000204C.0004.0001
N 0000204C.0001.0001
Y 0000204C.0003.0001
Y 0000204C.0002.0001
위의 인덱스에 비해 무슨 의미가 있겠는가!! 가나다 순으로 정렬이 된다해도 단 N과 Y의 차이일 뿐이다. 또한 전체 엔트리 중에서 하나의 엔트리의 위치를 찾아 접근하는 것과 전체 엔트리의 반 정도의 위치를 찾아 접근하는 것도 다를 것이다. 순서대로 전부 읽는 것과 반의 위치를 찾아 읽는 것이 그리 다를 것 같지 않다.
* 테이블에 사용할 수 있는 인덱스의 수
1개의 테이블에 최고 16개까지의 열에 대해 단일 인덱스를 작성하는 것이 가능하다. O.S에 따라서는 인덱스 당 최고 1000~2000 byte의 용량 점유가 가능하다. 그러나 인덱스를 너무 많은 열에 대해 지정하는 경우 새로운 행 삽입 시 속도가 느려진다. 이것은 행이 insert될 때 인덱스에도 하나의 entry가 삽입되어야 하기 때문이다.
* 인덱스 저장 위치
인덱스는 물리적으로 data tablespace와 분리된 별도의 tablespace에 저장하는 것이 좋다. tablespace 파일 간의 경합을 줄일 수 있기 때문이다. 인덱스에 tablespace를 지정하는 명령문은 다음과 같다.
create index CONCERT_SINGER_TITLE on concert(singer, title)
tablespace GBTALBOT;
이것은 주로 최적화나 공간사용량이 중요한 대형 데이터베이스의 경우에만 필요하다. 기본키나 유일성 제약조건을 만들 때 오라클은 자동으로 유일성을 보장하기 위해 인덱스를 작성한다. 별도로 지정하지 않는다면 이 인덱스는 테이블과 동일한 tablespace에 작성된다. 기본키와 유일성 제약조건을 작성할 때에는 using index 절을 활용하여야 한다.
create table coffeeMix(
coffee_name varchar2(20),
coffee number(6),
sugar number(6),
milk number(6),
cream number(6),
syrup number(6),
cocoa_powder number(6),
cinnamon_powder number(6),
water number(6) ),
constraint PK_COFFEE primary key(coffee_name)
using index tablespace INDEXES
storage (initial 20K next 20K) )
위의 예제에서 기본키 제약조건은 PK_COFFEE라는 명칭을 갖으며 이 기본키와 관련된 인덱스는 특정 영역 파라메터와 함께 INDEXES라는 테이블스페이스를 가리킨다.
덧) 위의 내용은 "Oracle 완벽해설"을 기반으로 한 자료입니다. 이 책에는 불행하게도 covers version 7.X 라는 문구가 있더군요. 명령문까지 변하지는 않았더라도 테이블에 사용할 수 있는 인덱스의 수 같은 것은 현재 버전과 약간 차이가 있을지도 모르겠습니다. ^^
'Dev > DataBase' 카테고리의 다른 글
| [ORACLE] 잡매니저 (0) | 2009/08/21 |
|---|---|
| 오라클 페이징 (0) | 2009/08/07 |
| 오라클 인덱스 1 (0) | 2009/07/09 |
| 오라클 인덱스 생성 (0) | 2009/07/09 |
| 오라클 인덱스 (0) | 2009/07/09 |
| SQL 날짜 관련 (0) | 2009/01/30 |
SQL 날짜 관련SQL 날짜 관련
Posted at 2009/01/30 18:20 | Posted in Dev/DataBaseDATEADD
RegDate BETWEEN DATEADD(MM,-3,getdate()) AND DATEADD(MM,0,getdate())
- 오늘부터 3개월전 데이타 가져오기
'Dev > DataBase' 카테고리의 다른 글
| [ORACLE] 잡매니저 (0) | 2009/08/21 |
|---|---|
| 오라클 페이징 (0) | 2009/08/07 |
| 오라클 인덱스 1 (0) | 2009/07/09 |
| 오라클 인덱스 생성 (0) | 2009/07/09 |
| 오라클 인덱스 (0) | 2009/07/09 |
| SQL 날짜 관련 (0) | 2009/01/30 |
행복한 프로그래머
행복한 지우 아빠
