나의 향기에 젖다...

http://ihayatesw.tistory.com/245

SQL> SELECT PROFILE, LIMIT FROM DBA_PROFILES WHERE RESOURCE_NAME = 'IDLE_TIME';

PROFILE                        LIMIT
------------------------------ ----------------------------------------
DEFAULT                        UNLIMITED

SQL> SELECT PROFILE FROM DBA_USERS WHERE USERNAME = USER;

PROFILE
------------------------------
DEFAULT

sqlnet.ora에도 timeout 설정하는 부분 있습니다. 그건 network단으로 idle time 조정하는 것입니다.

sqlnet.expire_time = 3   (단위 : 분)

imp userid/userpass file = data_bak.dmp fromuser = kice tables = (PRGVER)

이런 환경에서는 SID와 Service Name을 구분할 필요가 없었던것이다.

단순히 구분짓자면 이렇게 말할수 있다.

SID = DB 하나의 인스턴스

Service Name = 여러개의 인스턴스를 모아 하나의 서버 혹은 시스템을 구성한것

쉽게 예를 들어보자.

서버 한대에 인스턴스를 여러개 생성하여 orcl1, orcl2 로 각각 생성했다고 하자.

각각의 인스턴스는 orcl1, orcl2 라는 SID를 갖게 된다.

해당 서버에서 두개의 인스턴스를 묶어 사용할경우, orcl 이라는 Service Name을 갖을수 있다.

이외에도 서버 두대에 설치하여 각각 미러링 처리하여 동일한 서버인것 처럼 활용할경우

각각의 서버는 서로다른 SID를 갖게 되지만 Service Name을 동일하게 하여 같은 서버 처럼 활용할수 있다.

1. 오라클 데이타베이스명 확인하는 방법

SELECT NAME, DB_UNIQUE_NAME FROM v$database;

2. 오라클 SID 확인하는 방법

SELECT INSTANCE FROM v$thread;

* DB에 존재하는 모든 ROW가 갖는 유일한 식별자

* ROWID는 SELECT문을 이용하여 조회 할수 있지만 UPDATE문을 이용한 UPDATE는 불가능

* INSERT문을 이용해서 강제로 부여할수 없음

ROWID 구성 정보

000000 FFF BBBBBB RRR ( 6 + 3 + 6 + 3)

Display : 18자리

저장 : 10자리

000000 : Data Object Number :DB Segment 식별 정보

(해당 로우가 속해 있는 오브젝트 번호)

FFF : Relative File : Tablespace에 상대적 Datafile 번호

(해당 로우가 속해 있는 데이터 파일 번호)

BBBBBB : Block Number : Row를 포람하는 Data Block 번호

(해당 로우가 속해있는 데이터 파일의 데이터 블록 주소값)

RRR : Row Number : Block에서의 ROW의 SLOT

(데이터 블록 내에서 해당 로우의 주소 값)


출처 : http://blog.naver.com/speedsky98/120045820683

SQL> L

1 DECLARE

2 t_personnel PERSONNEL%ROWTYPE;

3 BEGIN

4 SELECT EMP_NAME, ADDRESS

5 INTO t_personnel.emp_name, t_personnel.address

6 FROM PERSONNEL

7 WHERE EMPNO='98002';

8

9 dbms_output.put_line('<<<조회결과>>>');

10 dbms_output.put_line(' 이름 : ' || t_personnel.emp_name);

11 dbms_output.put_line(' 주소 : ' || t_personnel.address);

12 EXCEPTION

13 WHEN NO_DATA_FOUND THEN

14 dbms_output.put_line('<<< ERROR >>>');

15 dbms_output.put_line(' 에러내용 : 조회된 데이타가 없습니다.!!');

16 WHEN TOO_MANY_ROWS THEN

17 dbms_output.put_line('<<< ERROR >>>');

18 dbms_output.put_line('에러내용 : 조회 데이타가 너무 많습니다.!!');

19 WHEN OTHERS THEN

20 dbms_output.put_line('<<< ERROR >>>');

21 dbms_output.put_line('에러코드 : ' || SQLCODE );

22 dbms_output.put_line('에러내용 : ' || SUBSTR(SQLERRM, 1, 100));

23* END;

SQL> /

<<<조회결과>>>

이름 : 이대명

주소 : 경기도 군포시 궁내동

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> L

1 declare

2 t_personnel PERSONNEL%ROWTYPE;

3 BEGIN

4 SELECT EMP_NAME, ADDRESS

5 INTO t_personnel.emp_name, t_personnel.address

6 FROM PERSONNEL;

7

8 dbms_output.put_line('<<<조회결과>>');

9 dbms_output.put_line('이름 : ' || t_personnel.emp_name );

10 dbms_output.put_line('주소 : ' || t_personnel.address);

11 EXCEPTION

12 WHEN NO_DATA_FOUND THEN

13 dbms_output.put_line('<<<ERROR>>>');

14 dbms_output.put_line('에러내용 : 조회된 데이타가 없습니다.');

15 WHEN TOO_MANY_ROWS THEN

16 dbms_output.put_line('<<<ERROR>>>');

17 dbms_output.put_line('에러내용 : 조회 데이타가 너무 많습니다.');

18 WHEN OTHERS THEN

19 dbms_output.put_line('<<<ERROR>>>');

20 dbms_output.put_line('에러코드 : ' || SQLCODE );

21 dbms_output.put_line('에러내용 : ' || SUBSTR(SQLERRM, 1, 100));

22* END;

SQL> /

<<<ERROR>>>

에러내용 : 조회 데이타가 너무 많습니다.

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> L

1 DECLARE

2 t_personnel PERSONNEL%ROWTYPE;

3 BEGIN

4 SELECT EMP_NAME, ADDRESS

5 INTO t_personnel.emp_name, t_personnel.address

6 FROM PERSONNEL

7 WHERE EMPNO='98002';

8

9 IF SQL%ROWCOUNT = 1 THEN

10 dbms_output.put_line('<<<조회결과>>>');

11 dbms_output.put_line('이름 : ' || t_personnel.emp_name);

12 dbms_output.put_line('주소 : ' || t_personnel.address);

13 ELSIF SQL%ROWCOUNT = 0 THEN

14 dbms_output.put_line('<<<ERROR>>>');

15 dbms_output.put_line('에러내용 : 조회된 데이타가 없습니다.');

16 ELSIF SQL%ROWCOUNT > 1 THEN

17 dbms_output.put_line('<<<ERROR>>>');

18 dbms_output.put_line('에러내용 : 조회 데이타가 너무 많습니다.');

19 END IF;

20

21 EXCEPTION

22 WHEN OTHERS THEN

23 dbms_output.put_line('<<<ERROR>>>');

24 dbms_output.put_line('에러코드 : ' || SQLCODE );

25 dbms_output.put_line('에러내용 : ' || SUBSTR(SQLERRM, 1, 100));

26 END;

27 /

<<<조회결과>>>

이름 : 이대명

주소 : 경기도 군포시 궁내동

PL/SQL 처리가 정상적으로 완료되었습니다.

CREATE OR REPLACE PROCEDURE SAMPLE_PROCE AS

-- SELETE 문의 결과를 TEMP_CUR 라는 CURSOR 에 넣는다.
CURSOR TEMP_CUR IS
SELECT BWCODE, BWNAME, BWADDS
  FROM TEMP_TAB;

-- TEMP_CUR의 타입을 그대로 사용할 수 있는 NOW_CUR 변수선언
NOW_CUR TEMP_CUR%ROWTYPE;

BEGIN

-- 커서 사용을 위해서 연다.
OPEN TEMP_CUR;

-- 반복문 시작
LOOP

-- 커서의 ROW를 NOW_CUR로 보낸다.
FETCH TEMP_CUR INTO NOW_CUR;

-- DBMS_OUTPUT.put_line('LOOP 시작');

-- 커서가 끝나면 반복문을 종료한다.
EXIT WHEN TEMP_CUR%NOTFOUND;

--조건문 사용
IF (NOW_CUR.BWCODE  <> 'A') THEN

ELSIF -- 또는 ELSE IF

ELSE

  
END IF;

INSERT INTO NEW_TAB ( BWCODE, BWNAME, BWADDS )
VALUES ( NOW_CUR.BWCODE, NOW_CUR.BWNAME, NOW_CUR.BWADDS );

COMMIT;

END LOOP;

-- 커서를 닫는다.
CLOSE TEMP_CUR;

EXCEPTION 

WHEN OTHERS THEN

-- 에러 발생시 에러 메시지 출력

-- 토드의 DBMS_OUTPUT 에서 확인 할 수 있다.

DBMS_OUTPUT.put_line('ERROR');

ROLLBACK; 

END;

실행은 ~~

EXEC SAMPLE_PROCE;

디버깅은 ~~

CREATE OR REPLACE PROCEDURE SAMPLE_PROCE 부분을 DECLARE 로 바꾸고
중간중간마다 DBMS_OUTPUT.put_line(...) 를 이용해서 처리한다.

* 참고로 SET SERVEROUTPUT ON; 작성 후 실행해야 DBMS_OUTPUT 나옴.

여러개의 쿼리가 있을경우엔 블록지정을 해줘야만 실행계획이 나온다.
Explain plan 을 실행하려면 ctrl + E 를 눌러주면 아래의 상태창에 실행 계획이 나온다.

1. 트리를 보면 왼쪽에 숫자가 나온다. 1부터가 실행되는 순서!!

 - 플랜은 트리 형태로 되어 있으며, 자신보다 하위 레벨이 있으면 하위 레벨부터, 같은 레벨이라면 상위 로우 소스부터 실행된다.

2.  TABLE ACCESS FULL 는 쿼리상 나오면 안됨(겁나 느림)!!

 - 옵티마이저가 통계 정보를 이용해 추정한 값이라고 보면 된다.

Cost - 옵티마이저가 해당 오퍼레이션을 수행하는데 필요하다고 판단한 비용. 측정을 위한 단위가 있는 것은 아니고, 그냥 weight 값 정도로 생각하면 됨. CBO는 여러 가지 실행 계획을 세우고 이에 대한 비용(cost)를 비교한 다음 비용이 가장 적은 실행 계획으로 SQL을 실행함.

Cardinality - 오퍼레이션을 수행할 때 접근하게 될 row 수. 역시 옵티마이저가 추정한 값
 
Bytes - 해당 오퍼레이션을 수행할 때 접근하게 될 byte 수. 옵티마이저가 추정한 값

3. 실행계획에서는 옵티마이져가 판단한 값!!

 - 실제 oracle의 실행과 다를수도 있으며 정확한것은 Trace를 통해서 확인해야 한다. 단지 이것을 어떻게 쿼리를 풀어나가는지를 확인하는 단계다.

1. Toad for Oracle 기능 및 사용법 (첨부)

▶ 목차
   * Toad for Oracle 제품 개요
   * 설치 방법
   * Toad 제공 기능 및 사용법   
      - Server Login
      - Schema Browser ( Data Grid )
      - SQL Builder
      - SQL Editor
      - Procedure Editor
      - SQL Optimizer
      - DBA 기능

Toad_for_Oracle_기능_및_사용법.ppt

2. Toad Advanced 교육 자료 (첨부)

TOAD_Advanced_교육_자료.pdf

GRANT SELECT ON 권한 줄 테이블명 TO 권한 받을 사용자계정;

2. 테이블 입력이나 수정 삭제의 권한을 부여 [INSERT , UPDATE , DELETE ]

GRANT  INSERT , UPDATE , DELETE  ON 권한 줄 테이블명 TO 권한 받을 사용자계정;

GRANT EXECUTE ON 권한 줄 함수명 TO 권한 받을 사용자계정;

* SYNONYM 생성

내 계정에서 다른 계정의 특정 테이블을 내 계정에서 볼때 Alias(별칭)를 정해놓고,
그 테이블을 참조할때 Alias 로 참조한다.

CREATE SYNONYM 시노님명  FOR  계정명.테이블명;

[예제]

1. SELECT * FROM USER_NAME.YNGMST;

2. CREATE SYNONYM YNG FOR USER_NAME.YNGMST;

3. SELECT * FROM YNG;

=> 1 과 3 은 똑같은 내용임.