'Oracle'에 해당되는 글 5건

ora-12541 : TNS:no listener.

ora-12541 : TNS 리스너가 없습니다.

오라클의 해당 에러는 오라클 클라이언트가 서버에 접속하고자 할 때 지정된 리스너에 연결을 할 수 없는 경우에

발생하는 에러로서.  몇가지의 상황이 있을 수가 있습니다.

1. client 에서의 tnsnames.ora 설정 확인

   tnsnames.ora 파일에서 접속하고자 하는 오라클 서버의 설정이 제대로 되어있는지 확인

2. 오라클 서버에 리스너 구동 여부 확인

   lsnrctl status 명령어를 이용하여 상태를 확인 하고 만약 정지되어 있다면

   lsnrctl start 명령을 통해 리스너를 구동

3. 서비스 연결 확인

   tnsping 명령으로 서버 연결 상태를 확인해 보는경우.

   또는 telnet 명령으로 해당 서버에 대한 오라클 포트가 열려있는지 확인

마지막으로 희박한 경우이기는 하나 listener 의 로그파일이 너무 커져서 발생할 수도 있습니다.

이때는 , listener/trace 디렉토리의 listener.log 파일을 체크하여 복사해놓고 새로 생성해 주시거나 하시면 됩니다.

-- ###################
-- 프로시져 생성
-- ###################

create or replace procedure pr_personal_annual
(
in_year in salary.year%TYPE,
in_no in emp.no%TYPE
)

is

v_emp_no emp.no%TYPE;
v_emp_name emp.name%TYPE;
v_dept_deptname dept.deptname%TYPE;
v_emp_position emp.position%TYPE;
v_sal_year salary.year%TYPE;
sal_n salay.annual%TYPE;
sal_l salay.annual%TYPE;
sal_d salay.annual%TYPE;

begin

select n.no,n.name,n.deptname,n.position,n.year,
n.annual,l.annual,n.annual-l.annual
into v_emp_no,v_emp_name,v_dept_deptname,v_emp_position,v_sal_year,
sal_n,sal_l,sal_d
from
(select emp.no,emp.name,annual,deptname,position,year
from emp,dept,salary sn
where emp.no=in_no and sn.no=in_no and emp.dept=dept.dept and year=in_year) n,
(select emp.no,emp.name,annual,deptname,position,year
from emp,dept,salary sn
where emp.no=in_no and sn.no=in_no and emp.dept=dept.dept and year=in_year-1) l;

DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('emp no : ' || v_emp_no);
DBMS_OUTPUT.PUT_LINE('emp name : ' || v_emp_name);
DBMS_OUTPUT.PUT_LINE('dept : ' || v_dept_deptname);
DBMS_OUTPUT.PUT_LINE('position : ' || v_emp_position);
DBMS_OUTPUT.PUT_LINE('year : ' || v_sal_year);
DBMS_OUTPUT.PUT_LINE('cur annual : ' || sal_n);
DBMS_OUTPUT.PUT_LINE('last annual : ' || sal_l);
DBMS_OUTPUT.PUT_LINE('annual gap : ' || sal_d);
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('no name dept posi year c_yr l_yr gap');
DBMS_OUTPUT.PUT_LINE('v_emp_no || ',' || v_emp_name || ',' || v_dept_deptname || ',' ||
v_emp_position || ',' || v_sal_year || ',' || sal_n || ',' ||
sal_l || ',' || sal_d);

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('해당 데이터가 없습니다!');


END;
/


-- 프로시져 실행
SQL> set serveroutput on
SQL> execute pr_personal_annual('2006','1')
SQL> set serveroutput off

* 출처 : http://www.redjava.co.kr

Oracle View(뷰) 의 생성 및 사용

VIEW 란 무엇인가?

하나또는 하나이상의 테이블로부터 데이터의 부분집합을 논리적으로 표현하는것으로 실제
데이타를 가지고 있는것이 아니라 해당 데이타의 결과를 하나의 SQL 쿼리 정보로 가지고
있습니다.

※ VIEW는 생성시 SQL쿼리문(SELECT * FROM TABLE) 형태로 저장이 됩니다.

※ 뷰테이블이란 표현은 잘못된 표현입니다. 뷰쿼리가 맞는 표현입니다.

VIEW 의 장점과 단점

뷰를 사용하면 코딩 라인이 짧아지고 결과에 대한 오류 발생시 빠르게 대응 할수 있다는 장점을
갖고 있습니다. 뷰는 상수값을 받지 못합니다. 즉, parameter 값을 받을수 없어 Scan 범위를 줄일수 없습니다.

VIEW 사용의 목적

데이터베이스 액세스를 제한하기위해
view는 선택적인 내용을 보여주기 때문에 액세스를 제한합니다.

복잡한 질의를 쉽게 만들어 준다.

group나 order by와 같은 복잡한 질의의 정보를 view로 저장시켜놓으면 다음부터는 view의 정보만 가져오면 되므로 쉽게 사용가능하다.

데이터의 독립성을 허용하기 위해 다양하고 빠른 조회를 위해 사용

테이블의 수를 줄이고 성능향상을 가져온다.

VIEW 사용시점

해당 프로젝트에서 관리차원이나 유지보수차원을 중요시 여긴다면 VIEW를 사용하는게 좋다.

VIEW의 종류

1. 단순뷰

 하나의 TABLE에서 뽑아오며 함수를 사용안한다.
 DML(INSERT,UPDATE,DELETE)사용 가능

2. 복합뷰

 하나이상의 TABLE에서 뽑아오며 함수를 사용합니다.
 DML(INSERT,UPDATE,DELETE)사용 불가능

[예제]

CREATE OR REPLACE VIEW AAA AS
 SELECT
 D.DNAME,
 MIN(E.SAL),
 MAX(E.SAL),
 AVG(E.SAL)
 FROM EMP E , DEPT D
 WHERE E.DEPTNO=D.DEPTNO
 GROUP BY D.DNAME;

VIEW 생성하기

사용방식

CREATE [OR REPLACE] VIEW VIEW_NAME AS QUERY_STRING [WITH READ ONLY];

주의할점

1. QUERY_STRING 에는 ORDER BY절 빼고 모두 사용가능하다.
2. "OR REPLACE" 옵션을 사용하게되면 생성할 VIEW와 같은 VIEW_NAME이 있어도 무시하고 새로운것으로 갱신하게 됩므로 주의하여 사용해야 합니다.
3. "WITH READ ONLY" 옵션을 사용하게 되면 해당 VIEW 에 DML 언어를 사용할수 없으며 DML 언어를 사용하게되면 에러가 발생합니다.

사용예제 :
CREATE VIEW VIEW1 AS
 SELECT NAME, AGE, ADDRESS
 FROM SCHOOL
 WHERE NAME = '이준식';
CREATE OR REPLACE VIEW LEE AS
 SELECT * FROM DEPT;

CREATE OR REPLACE VIEW LEE AS
 SELECT * FROM DEPT
WITH READ ONLY;

 VIEW에 DML한후의 반응

VIEW을 생성후 INSERT을 하게되면 VIEW에는 안들어가지고 원본 TABLE에 입력된다.
VIEW을 생성후 UPDATE을 하게되면 VIEW는 데이타가 없어지면서 원본 TABLE내용이 갱신된다.

뷰는 실테이블을 적당히 가공하여 보여주는 역할을 합니다.
실제 테이블의 자료를 보기 위한 하나의 틀 정도로 생각하시면 됩니다.
뷰중에는 자료 입력,수정,삭제가 가능한 뷰도 있습니다.
뷰의 자료를 수정하게 되면 실제 테이블의 자료가 수정되는 것입니다.
테이블이 수정되면 뷰 또한 수정된 값을 보여줍니다.

VIEW 지우기

사용형식 : DROP VIEW VIEW_NAME;

사용예제 : DROP VIEW EXAMPLE1;

생성된 VIEW보기

SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='VIEW';

인라인 뷰란?

SQL문에서 사용가능한 별칭을 사용하는 서브쿼리입니다.
인라인뷰는 기본 질의의 FROM절에 명명된 서브쿼리를 사용하는것과 유사합니다.
Oracle 7.1 이상에서 사용가능하다.

※ 단점 : 인라인뷰를 사용한다는것은 가상메모리를 더 사용한다는 것이다.

※ 대용량에서 인라인뷰를 출력항목에 사용하게되면 성능이 많이 떨어진다. 왜냐하면
   조회되는 건수만큼 인라인뷰가 실행되기 때문이다.

ex)

 SELECT (SELECT NAME FROM MEMBER) AS NAME FROM TRADE;

인라뷰를 응용한 Top-N 분석

 회사에서 월급많이 받는 3명뽑아라
 회사에서 가장 최근에 입사한 직원 3명뽑아라

 사용형식:

SELECT COLUMN_LIST ,ROWNUM FROM
 (SELECT COLUMN_LIST FROM TABLE ORDER BY TOP_N_COLUMN)
 WHERE ROWNUM<=N;

사용예제:

SELECT * FROM
 (
 SELECT * FROM DEPT ORDER BY DNAME
 )
 WHERE ROWNUM <= 3;

 
VIEW의 내부 동작

1. VIEW 생성

CREATE VIEW TEST_VIEW AS SELECT NAME, AGE FROM MEMBER WHERE AGE > 5;

2. VIEW 갱신명령

UPDATE TEST_VIEW SET AGE = AGE + 10 WHERE NAME = 'JUNSIK';

3. VIEW 내부적 변환

UPDATE MEMBER SET AGE = AGE + 10 WHERE NAME = 'JUNSIK' AND AGE > 5;

※ VIEW에 갱신명령을 내리면 겉으로는 2번과 같이 처리되는것 처럼 보이지만 DBMS가 명령을
   받은후에 3번과 같이 변환후 실행한다.

서로 다른계정에서 View 접근권한

create or replace view test
 as
 select * from uer.temp
 where deptid =11;

위의 명령을 A계정에서 할경우 당연히 권한이 없다고 나온다. 이때는 SYSTEM 계정에서
A계정에 uer.temp 테이블에 대한 SELECT 권한을 주면 된다.
그리고 SYSTEM계정으로 접속을 해도 권한이 없으면 다른 유저의 자료는 액세스 할수 없다.

Oracle 인덱스(index) 에 대한 정의

1) 조회속도를 향상시키기 위한 데이터베이스 검색 기술

2) 색인이라는 뜻으로 해당 테이블의 조회결과를 빠르게 하기 위해 사용합니다.

보통 INDEX를 테이블의 특정 컬럼에 한개이상을 주게 되면 Index table 이 따로 만들지는데
이 INDEX table 에는 인덱스컬럼의 로우의 값과 rowid 값이 저장되게 되며 로우의값은 정렬된 B-TREE 구조로 저장시켜두어 검색시 좀더 빠르게 해당 데이타를 찾는데 도움을 준다.

하지만 UPDATE,INSERT,DELETE 시에 속도가 느려진다는 단점이 있다.

왜냐하면 INSERT, UPDATE, DELETE 시에는 원본TABLE은 물론 INDEX table 에도 데이타를 갱신시켜 주어야 하기 때문입니다.

하지만 너무 작은 로우(레코드)가 있는 TABLE에 INDEX를 사용하게 되면 index의 효력을 제대로 발휘못하며 반드시 INDEX키를 조건으로 검색시에는 연산이나 가공을 하면 INDEX를 탈수없다.

※ 테이블을 생성하고 컬럼을 만든후 데이타를 삽입하면 하나의 로우가 생성되며 이 로우는 절대적인 주소를 가지게 됩니다. 이 절대적인 주소를 ROWID 라고 합니다.

INDEX가 필요한 이유?

조회 속도를 최대한 줄일수 있다.

[참고]
인덱스를 만들 때 현재 컬럼수가 너무 많으면 DML의 성능이 떨어지고 너무 부족하면
쿼리의 성능이 떨어집니다. 데이터가 많고 B*Tree Index인 경우 컬럼level이 하나
늘어날 때 그에 따른 node의 추가가 엄청나게 일어날 수 있습니다
이런 node들이 너무 많으면 쿼리성능도 좋을 수 없습니다.
인덱스는 일반적으로 최대 5개 컬럼 내외정도로 상황에 따라 합리적으로 구성합니다.

인덱스(Index)가 필요한 경우

1. 데이타가 많이 쌓일거라고 예상되는 경우와 많이 쌓인 경우와 현재 화면에서 조회속도가 너무 느릴때
2. 조회결과가 전체 데이타수의 3~5% 미만일 경우에 인덱스스캔이 효율적이고
    적은비용으로 빠르게 데이터를 찾아낼수있습니다.

하지만 Acess 대상범위가 전체범위의 3~5%이상쯤 되면 인덱스스캔 보다 풀스캔이 휠씬
유리합니다.

인덱스 스캔 = Index Scan

풀 스캔 = Full Scan

INDEX가 불필요한 경우?

1) 데이터가 적은(수천건 미만) 경우에는 인덱스를 설정하지 않는게 오히려 성능이 좋습니다.
2) 조회 보다 삽입, 수정, 삭제 처리가 많은 테이블
3) 조회결과가 전체행의 15% 이상 읽어들일것으로 예상될때

Index 튜닝의 시기단계

하드웨어

DBMS 환경체크 - INDEX설정 및 파티셔닝체크 - SQL최적화 등의 레벨 순서로 튜닝합니다.

소프트웨어

쿼리문의 조회조건이 인덱스를 타는지 체크한다.(조금 중요)


쿼리문의 JOIN키가 모두 INDEX 설정이 되어 있는지 체크한다. INDEX설정이 되어 있지 않는 컬럼을 JOIN키로 지정했다면 조회속도가 많이 느려질수 있다.(많이 중요)

INDEX 생성하는 방법?


자동 생성

유일 인덱스는 테이블 정의시 PRIMARY KEY와 UNIQUE KEY 제약조건을 정의할때 자동으로 생성한다.


수동 생성

사용자는 행에 대한 액세스 시간을 향상 시키기 위해 열에서 유일하지 않은 인덱스를 생성할수 있다.


INDEX 생성 문법?

사용형식

CREATE INDEX index_name ON table_name (column_name)

--단일 인덱스 지정

CREATE INDEX index_name ON table_name (column_name1,column_name2,column_name3)

--다중 인덱스(복합 인덱스) 지정


※ 복합 인덱스로 지정해준 테이블에서 복합 인덱스를 타게 하려면 복합 인덱스로 준 컬럼을 조회쿼리에서

    모두 조회조건에 사용해야 인덱스를 탈 확률이 높아진다.

사용예제

create index index_a_date on account(a_date);
create index index_a_date on account(a_date, b_date, c_date);

※ null 허용 컬럼은 인덱스를 만들수 없습니다.

INDEX 가능 컬럼


인덱스는 모든 컬럼에 적용가능하다.
그런데 오라클은 가공시킨 컬럼에도 적용가능하다. 아래 참고

CREATE INDEX IDX_NAME ON TABLE_NAME(ROUND(PRICE1-PRICE2));

ROUND(PRICE1-PRICE2) 는 컬럼은 아니지만 컬럼을 가공해서 만든것이다.

이런 가공컬럼은 다음과 같은 SQL 쿼리로 인덱스를 탈수 있다.

SELECT * FROM TABLE_NAME WHERE ROUND(PRICE1-PRICE2) > 0


※ 인덱스 줄때의 가공컬럼과 같아야 합니다.



SQL 쿼리의 INDEX SCAN 유무 체크 방법

1. 상용 DB 관리도구를 이용하는 방법

PL/SQL Developer, Toad 같은 도구에서 SQL문을 작성하고 실행하면 Explain plan 에서 확인 가능합니다.


INDEX를 사용해야할 컬럼은?

where절이나 조인 조건에서 자주 사용되는 열에 생성

열은 광범위한 값을 포함할때
열은 많은수의 null값을 포함할때
조회결과가 전체행의 2-4% 보다 적게 읽어들일것으로 예상될때

--테이블이 클때 적은 양의 로우를 검색할때 인덱스를 줍니다. 적은 양을 검색하는데 테이블을 전체 풀스캔하면 시간이 오래 걸려서 꼭 index를 줘야 합니다.

INDEX를 사용하지 말아야할 컬럼은?


테이블에 데이타가 작은 경우

where절에 자주 사용되지 않는 열은 사용되지 않는다.

조회결과가 전체행의 2-4% 이상을 읽어들일것으로 예상될때 테이블이 자주 갱신된다.


INDEX 생성시 고려사항?

고려사항

인덱스가 적용된 컬럼이 조건식에서 인덱스를 탈수있게 하려면 해당컬럼을 가공하지않거나 연산을 하지 않은 상태에서 비교해야 인덱스를 탑니다.

예를들어 연락처컬럼의경우(016-293-1965) 016 만 따로 문자열을 잘라(가공) 조건검색하면 인덱스를 타지 않습니다.

왜냐하면 인덱스 컬럼에 변형이 일어나면 상대값과 비교되기 전에 먼저 가공이 된 후에 비교된다.하지만 인덱스는 가공되기 전의 값으로 생성되어 있기 때문에 당연히 인덱스를 사용할 수 없게 된다. 여기에서 외부적(External) 변형이란 사용자가 인덱스를 가진 컬럼을 어떤 SQL함수나 사용자 지정함수(User Defined Stored Function), 연산, 결합(||) 등으로 가공을 시킨 후에 발생되는 것이며 이러한 경우는 인덱스를 탈수 없어 변형이 일어나지 않도록 제대로 기술해야 합니다.

그렇기때문에 016과 293과 1965를 각각의 컬럼으로 만들어 저장한후 각각의 컬럼에 인덱스를 주면

아무런 가공없이 조건 검색이 가능하므로 인덱스를 탈수 있습니다.

테이블 컬럼에 인덱스가 있따면 테이블 컬럼을 변경하는것보다 비교값을 변경하여 비교해주는게 좋다. 왜냐면 그래야 인덱스를 타기 때문이다.

WHERE to_char(joindate, 'yyyymmdd') = '20070612'

WHERE joindate = TO_DATE('20070612','yyyymmdd')

아래는 인덱스를 타지 않습니다.


SELECT * FROM ACCOUNT WHERE A_DAY+1>2;

SELECT * FROM ACCOUNT WHERE SUBSTR(A_STRDAY,1,1)='월';

SELECT * FROM EMP WHERE EMP_ID = NVL(EMP_ID,'10');



아래는 인덱스를 탑니다.



SELECT * FROM ACCOUNT WHERE A_STRDAY='월요일';

SELECT * FROM ACCOUNT WHERE A_DAY>2;

SELECT * FROM EMP WHERE EMP_ID = NVL('10','20');

SELECT * FROM ACCOUNT WHERE A_STRDAY like '월요일%';



※ 첫번째 쿼리부터 인덱스효과가 크게 나타나는 순입니다.



INDEX 타는 경우와 안타는 경우



안타는 경우

1. SELECT * FROM emp WHERE empno <> '7369';



※ 오라클에서는 인덱스 타게 가능(exists 이용)

SELECT * FROM emp WHERE not exists

(select empno FROM emp WHERE empno = '7369' and a.empno = b.empno);



INDEX 보기?



SELECT * FROM USER_INDEXES

--데이터 사전 뷰는 인덱스의 이름과 그것의 유일성을 디스플레이 합니다.



SELECT * FROM USER_IND_COLUMNS

--뷰는 인덱스명,테이블명,열명을 디스플레이 합니다.



SELECT * FROM ALL_OBJECTS where object_type='INDEX';

--현재 계정에 생성된 모든 인덱스 보기(속도느림)



SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='INDEX';
--현재 계정에 생성된 모든 인덱스 보기(속도빠름)



select ic.index_name,
ic.column_name,
ix.uniqueness
from user_indexes ix, user_ind_columns ic
where ic.index_name = ix.index_name
and ic.table_name = 'ACCOUNT';
--ACCOUNT TABLE의 인덱스 정보를 검색합니다.



INDEX 삭제?

사용형식

DROP INDEX INDEX_NAME;


사용예제

DROP INDEX BYC_LOVE_IDX;



※ TABLE이 삭제되면 INDEX도 삭제된다.

※ 인덱스의 소유자와 DROP ANY INDEX권한을 가진 사람만 인덱스 삭제가 가능합니다.



datecolumn => '20060517' and datecolumn <= '20060517' 좌측의 쿼리가 인덱스 안

타는 경우

1. 인덱스키가 아님

2. 복합키 인덱스인데 첫번째 컬럼에 조건을 안준 경우

3. 인덱스 스캔을 했을때 전체 데이터의 10~15% 이상이 되어 옵티마이져가 판단했을때

4. 인덱스 스캔이 불리하다고 판단되어 강제로 인덱스안타는 풀스캔 타는 경우

5. 좌변이 가공되어 인덱스 안탐



자주 쓰이지 않는 통계용 쿼리에는 인덱스를 주지 않는다.

여기서 인덱스를 준다는 얘기는 조건절에 인덱스를 안타는 컬럼에 인덱스를 생성해준다는

얘기이다. 이때는 create temp table as 해서 임시 테이블 만들어 통계 내용을 모두 담은후에

해당 조건컬럼에 인덱스를 만들어 쿼리하는게 좋다.

출처 : http://www.redjava.co.kr    자바의 모든것...

간혹 로컬 PC 환경에서 Query 를 작성하여 정상 확인 한 후  서버에 올렸는데 에러가 발생하는 경우가 있습니다.

형식이 일치하지 않는 ORA-01861 이 대표적으로 발생 할 수 있는데요.

그중에서도 Date 형태의 데이터를 처리할 때 가장 많은 오류가 발생합니다.

SELECT TO_DATE(sysdate)  from dual;

이런식으로 날짜 형식에 대한 포멧을 지정하지 않는 경우입니다.

이럴때는 반드시

SELECT TO_DATE(sysdate, 'YYYY-MM-DD') from dual;

과 같이 포멧을 지정해 주어야 ORA-01861 에러가 발생하지 않습니다.

이런 원인은 Date 형태의 포멧이 클라이언트(Session) 마다 NLS_DATE_FORMAT 에 디폴트로 설정이

되어있지만, 이게 환경마다 틀리기 때문입니다.

다른 해결 방법으로는

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' ;

이런식으로 세션에 대한 디폴트 포멧을 지정해 줄 수도 있지만, 쿼리에서 항상 포멧을 지정하는 습관을

가지시는게 좋겠지요.