오라클 1원 팁

1. 특정 오라클 DB 프로세스 강제로 죽이기

예) 오라클에서 30초 이상된 프로세스중에서 외부에서 연결된 프로세스 죽이는 스크립트
ps -ef|grep "LOCAL=NO"|grep -v extprocPLSExtProc|cut -c 10-15 >
exam.kill sleep 30 cat exam.kill cat exam.kill|xargs kill -9
---------------------------------------------------------------
1. 조인시 속도 향상법(뷰를 이용)

여러테이블의 합계를 조인 할때는 인라인 뷰또는 뷰를 생성해서 사용하라
테이블 사이즈가 크고 합계를 냈을때 레코드 수가 현저히 작다면
테이블 레코드를 모두 조인 하는것보다 합계를 내는 뷰를 작성하고
이를 조인 하는것이 훨씬 유용하다.
예를 들어 1999년의 급여합계와 정기상여 합계 를 한 레코드에 출력 할 경우
select a.pay_date,a.pay , b.bonus
from (select substr(pay_date,1,4) pay_date,sum(pay) pay
from pay
where pay_date like '1999%'
group by substr(pay_date,1,4) ) a,
(select substr(pay_date,1,4) pay_date,sum(bonus) bonus
from bonus
where pay_date like '1999%'
group by substr(pay_date,1,4)) b
where a.pay_date = b.pay_date;
이것은 급여 합계 한레코드와 상여 합계 한 레코드를 조인하므로
각각의 급여월과 사원별로 조인하는것보다 현저히 빠르다.
---------------------------------------------------------------
1. 사용자 생성 및 권한부여

(1) 사용자 생성
create user guest identified by guest
default tablespace guest
temporary tablespace guest
(2) 권한 부여
grant resource, connect to guest
---------------------------------------------------------------
1. 리눅스에서 데이타베이스 시작과 종료

(1) database start script
----------------------
#!/bin/sh

svrmgrl << EOF
connect internal
startup
quit
EOF

lsnrctl start
----------------------

(2) database down script
----------------------
#!/bin/sh

lsnrctl stop

svrmgrl << EOF
connect internal
shutdown abort
EOF
---------------------------------------------------------------
1. primary key 생성

alter table tablename
add constraint constraint_name
primary key (fieldname)
---------------------------------------------------------------
1. 데이타파일 크기조정(datafile resize)

alter database datafile '/oracle/db/temp./dbf' resize 50m
---------------------------------------------------------------
1. 공간, 테이블, 인덱스 생성(create table, tablespace, index)

(1) 테이블 스페이스 생성
create tablespace guest
datafile '/oracle/tablespace/guest.dbf' size 500m
(2) 테이블 생성
create table tablename (
field1 number not null,
field2 name varchar2(10),
field3 long );
(3) 인덱스 생성
create index indexname
on tablename ( field1, field2)
---------------------------------------------------------------
1. 시퀀스 사용하기

시퀀스 생성
create sequence seqname
increment by 1
maxvalue 1000000
start with 1
nocycle

시퀀스 사용
select seqname.currval
from dual;

insert into tablename (seq)
values ( seqname.nextval );
---------------------------------------------------------------
1. 빠르게 역순으로 정렬하는법

오라클 힌트(index_desc)를 사용한다.
오라클 7.2 이상에서만 지원됨

select /*+ index_desc(table_name index_name) field1
from table1
where field1 < 'xx'
and rownum < 10;

위의 내용은
테이블 table1에서 field1의 값이 'xx'보다 작은 값중에서
역순으로 9개 값을 페치한다.
order by를 사용하면 테이블 전체를 다 읽어야 하지만
위의 방식은 단지 9개만 읽어오므로 매우 빠르고 시스템 부하가 적다.
위의 방식을 이용하여 현재의 게시판을 만들었다.

인덱스를 만들어야 한다.
---------------------------------------------------------------
1. 마지막 레코드 값 얻기

인덱스가 있는 경우
마지막 값을 얻기위해
select max(field_name) 을 사용하는경우가 많다
레코드수가 적을 경우는 문제가 없지만
많을경우 속도가 오래걸린다

이경우
select /*+ index_desc(table_name index_name) */ field_name
from table_name
where rownum = 1;
을 사용하라
이렇게 하면 마지막레코드 하나만 찾는다
---------------------------------------------------------------
1. Long형의 테이블 복사하기

long형 테이블은 insert문으로 복사가 안되므로 pl-sql을 이용한다.

예제)
DECLARE
cursor cur is
SELECT seqno,to_number(substr(yyyymm,1,4)) year,
to_number(substr(yyyymm,6,2)) month,
class,title,seq,wwwflag,detail
FROM w_travelnews;
begin
for t in cur loop
INSERT INTO e_travelnews(seqno,year,month,class,title,seq,wwwflag,detail)
VALUES(t.seqno,t.year,t.month,t.class,t.title,t.seq,t.wwwflag,t.detail);
end loop;
END;
---------------------------------------------------------------
1. 텍스트화일의 경우 Oracle DB에 import시키는 방법

SQL * Loader 이용
-.이용방법
(1) 오라클 계정으로 로그인
(2) /home/ora816/bin 디렉토리에 있는 sqlldr 이용
sqlldr scott/tiger control=ctl파일의 경로 data=data파일의 경로 error=error의
개수입력(default 50) log=log파일의 지정
control파일 이하는 생략이 가능하나 error발생시 문제해결을 위해 log파일을 만드는게 좋을 것 같다.
예)sqlldr scott/tiger control=test.ctl data=test.txt error=100 log=test.log
(3) control파일을 만드는 방법
편집기를 이용하여 아래와 같은 형식으로 코딩을 한다.
Load data
Infile ‘test1.csv’
Append
Into table test
Fields terminated by ‘,’
Trailing nullcols
(quiz_idx integer external,
problem char,
select_0 char,
select_1 char,
select_2 char,
select_3 char,
select_4 char,
select_5 char)

infile에 들어가는 파일이 import하고 하는 파일입니다.
여기서 csv의 확장자는 엑셀화일에서 콤마로 분리시킨 파일입니다.
Append는 기존 테이블에 데이터가 있을 때 쓰는 옵션입니다.
만일 테이블에 데이터가 없으면 insert로 대체해야 한다.
Into table test 에서 test는 import하고자 하는 테이블입니다.
Fields terminated by ‘,’ 는 infile에서 필드를 분리시킨 구분자입니다.
여기선 , 로 분리했기 때문에 이문장이 들어갔습니다.
자신이 분리한 구분자를 써주면 됩니다. 예)’;’
제가 해보기로는 텝으로 분리된 파일에 대해선 이문장을 생략하면 되는 것 같습니다.
Trailing nullcols 은 null값에 대한 체크문장입니다.
그 다음 내용은 테이블의 필드 입니다. 순서를 맞추어 넣어주시면 되고 주의하실 사항은
number값으로 대치되는 경우는 integer external을 해주시면 되고 varchar2로
대치되는 경우는 char로 해주시면 됩니다.

SQL * Loader에 대한 설명이 책이나 웹사이트에서 내용이 부족해 여러 번의 테스트를
거쳐 위의 내용이 가장 적합하다는 결론을 내렸습니다.
SQL * Loader를 실행하고 나면 bin폴더에 로그화일이 생기며 실패했을때는 bad file도 생깁니다.
Bad File은 데이터 파일로부터 데이터를 읽어들일 때 오류가 발생한 데이터들이 기록되는 파일입니다.
자신이 입력하고자 하는 데이터중에 입력이 되지 않은 데이터를 체크하는데 유용하게 쓸수 있을 것 같습니다.
---------------------------------------------------------------
1. Oracle 계층형 쿼리..(질문답변게시판사용시)

Select rrow,indent,seq,subject,name,reg_date,read_count
from ( SELECT　rownum rrow, LEVEL - 1 indent,
SEQ, subject,name,reg_date,read_count
FROM　 (SELECT /*+ INDEX_DESC (z_board_list z_board_list_pk) */
SEQ, PARENT, SUBJECT,name,
to_char(reg_date,'yyyy-mm-dd hh:mi:ss') reg_date,
read_count
FROM　z_board_list) where rownum <= 1 * 10
START WITH　　PARENT = 0
CONNECT BY　　PRIOR SEQ　= PARENT
where rrow between 1 * 10 - 10 + 1 and 1 * 10

위의 자료중 1 이 있는 부분은 페이지를 나타냅니다. 2로 바꾸면 2페이지가
쿼리됩니다.
계층형으로 쿼리가 생성됩니다.

BOM 형태의 테이블로 구성하면되겠죠..

테이블 형태는 아래와 같으며 이런식으로 데이타가 있어야합니다.

테이타는 이렇게 있으면
SEQ Parnet subject
1 1 질문
2 1 1번의 답변
3 2 2번의 답변
4 1 1번의 답변
5 3 3번의 답변
그리고 index를 사용하시는것 잊지마시구..
---------------------------------------------------------------
메모...까묵을까봐...헤~ ^^

load from 'hard.txt'
insert into member (member_no,name,phone,address);

unload to hard.txt select name,phone,address from member order by name desc;

select title, address, name, email,
trim(tel1) || '-' || trim(tel2) || '-' || trim(tel3) , phone from store_info;
---------------------------------------------------------------