티베로 파이프라인드 테이블 함수 생성 및 사용

화면에서 파라미터 1,2,3으로 넘어오는 변수를

mybatis에서 for each 문을 이용하여 in 절을 이용하여 쿼리를 보통 하는데

이번에는 자바단에서 list를 만들수 없는 상황.

그래서 오라클에서 사용하는 pipelined 를 이용한 table 형태를 반환하는 함수 생성.

티베로도 잘 되는 구만.

WITH tt AS ( SELECT '1,2,3' AS txt FROM dual )
SELECT TRIM ( REGEXP_SUBSTR(txt, '[^,]+',1, LEVEL )) AS txt
FROM tt
CONNECT BY INSTR(txt, ',', 1, LEVEL-1) > 0
;




CREATE PACKAGE PKG_TEST IS
  TYPE rec IS RECORD(cd VARCHAR2(100), cd2 VARCHAR2(100));
  TYPE tbl_rec IS TABLE OF rec;
END;
/

CREATE OR REPLACE FUNCTION pipelined_table_func
(
    p_value IN varchar
)
RETURN PKG_TEST.TBL_REC PIPELINED
AS
x PKG_TEST.REC;

CURSOR c_sc111 IS
  WITH tt AS ( SELECT p_value AS txt FROM dual )
SELECT TRIM ( REGEXP_SUBSTR(txt, '[^,]+',1, LEVEL )) AS txt
FROM tt
CONNECT BY INSTR(txt, ',', 1, LEVEL-1) > 0
 ;

c_sc111_r c_sc111%ROWTYPE;


BEGIN

  OPEN c_sc111;
      LOOP
        FETCH c_sc111 INTO c_sc111_r;
        EXIT WHEN c_sc111%NOTFOUND;

          x.cd := c_sc111_r.txt;
    PIPE ROW(x);

      END LOOP;

    CLOSE c_sc111;


END;
/






WITH data1 AS (
SELECT '1' AS code, '대한민국1' AS codename FROM dual
UNION ALL
SELECT '2', '대한민국2' FROM dual
UNION ALL
SELECT '3', '대한민국3' FROM dual
UNION ALL
SELECT '4', '대한민국4' FROM dual
UNION ALL
SELECT '5', '대한민국5' FROM dual
UNION ALL
SELECT '6', '대한민국6' FROM dual
UNION ALL
SELECT '7', '대한민국7' FROM dual
UNION ALL
SELECT '8', '대한민국8' FROM dual
UNION ALL
SELECT '9', '대한민국9' FROM dual
)
SELECT * FROM
 data1 a,
 TABLE(pipelined_table_func(',1,2,3,')) b
 WHERE a.code = b.cd
;

오라클 플래시백 사용법

간혹 실수로 데이타를 날리는 경우 플래시백 기능을 활용하여

데이타를 복구하는 경우가 발생함.

ㅠㅠ 어제 내가 날렸음..

SELECT * FROM '테이블명' AS OF TIMESTAMP TO_TIMESTAMP('2011-12-06 17:36:58', 'YYYY-MM-DD HH24:MI:SS')
;


insert into '테이블명'
SELECT * FROM '테이블명' AS OF TIMESTAMP TO_TIMESTAMP('2011-12-06 17:36:58', 'YYYY-MM-DD HH24:MI:SS')
;

아..이렇게 해서 복구했음.

가슴을 쓸어내리는 순간이었음....쩝

유닉스환경에서 trace + tkprof 실행

-- 2010.1.5 수정

1. sql*plus 실행
-- sql 실행할 파일위치가 있는 곳( /usr_app/sql ) 에서 sqlplus 실행. ( 개인적으로 여기가 편함 )
-- sqlplus에서 바로 sql파일을 읽어서 실행하기 편하므로
/usr_app/sql/sqlplus xxx/xxx@xxx

2. sql 파일 생성
-- 현재 접속된 SESSION 의 spid 확인
select p.spid
  from v$process a, v$session b
 where a.addr = b.paddr
    and b.audsid = userenv('SESSIONID');

결과 SPID : 1080

-- 2010.1.5 추가분
- CPU시간, 실행시간등 시간에 관련된 정보를 표시하기 위해 사용된다.
- 지속적인 설정을 위해 패러미터파일(init.ora)에 설정하거나
- 세션에서만 임시로 설정하기 위해
  alter session set timed_statistics=true 로 사용할 수 있다.

ALTER SESSION SET TIMED_STATISTICS  = TRUE;

-- TRACE 시작
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

-- 2011.1.5 추가
-- 트레이스 파일에 식별자 붙이기 ( 'jjang_' 로 붙는다 )
alter session set tracefile_identifier='jjang';
alter session set sql_trace=true;
set autot traceonly;



-- 실행할 sql 입력
SELECT *
   FROM EMP
 WHERE EMPNO = 000000
     AND DEPTNO = 999;

--TRACE 끄기
ALTER SESSION SET SQL_TRACE = FALSE;

-- SQL TRACE 파일 위치의 파악
-- 기억해 두었다면 실행할 필요가 없겠죠?
SHOW PARAMETER USER_DUMP_DEST;
==> test.sql로 생성
==> 보통 다른데서 파일 만들어놓고 FTP로 업로드 해서 사용하면 됨.

3. TKPROF 실행
tkprof  db명_ora_1080.trc /usr_app/sql/db명_ora_1080.txt
/usr_app/sql/ 폴더아래에 db명_ora_1080.txt 으로 텍스트 파일이 생성됨.

다시 ftp로 받아서 읽거나 vi로 읽으면 끝.

로컬 oracle trace + tkprof 사용하기

1. sql*plus 실행

system으로 접속

 

2. scott - dba권한 부여

grant dba to scott with admin option;

 

3. 현재 접속된 SESSION 의 spid 확인

select p.spid

  from v$process a, v$session b

 where a.addr = b.paddr

    and b.audsid = userenv('SESSIONID');

 

결과 SPID : 1080

 

4. TRACE 시작

ALTER SESSION

    SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

 

5. SQL 실행

SELECT *

   FROM EMP

 WHERE EMPNO = 000000

     AND DEPTNO = 999;

 

6. TRACE 끄기

ALTER SESSION SET SQL_TRACE = FALSE;

 

7. SQL TRACE 파일 위치의 파악
SHOW PARAMETER USER_DUMP_DEST;

 

8. TKPROF 실행

로컬에 설치된 오라클 ( C:ORACLE )

- 디폴트 세팅된 TRC 파일 위치

  C:\ADMIN\DATABASE명\UDUMP\  XXX.TRC

 

9. CMD 실행

tkprof scott/tiger trace=C:\admin\db명\udump\db명_ora_1080.trc sys=no output=db명_ora_1080.txt

db명_ora_1080.txt 으로 텍스트 파일이 생성됨.

 

내용확인.

Cost 구하는 공식 정의

1.

Cost 구하는 공식 정의

cost =  ( Estimated time  /  Single Block I/O time )

       = (Single Block I/O Count ) + ( Multi Block I/O Time / Single Block I/O Time ) + (CPU Time / Single Block I/O Time )

       =  Single Block I/O Count + Ajdusted Multi Block I/O Count + Adjusted CPU Count

 

 

2.

Oracle9i는 System Statistics를  수동으로 수집

                System Statistics를 별도로 수집하지 않는 한 I/O 기반의 Cost 계산 방식을 사용한다.

                따라서 정기적으로 Syste Statistics를 생성해야 한다.

 

Oracle10g는 Noworkload System Statistics를 기본으로 이용하기 때문에 CPU Model 이 사용되며

                  항상 Time 기반의 Cost 계산방식을 사용하게 된다.

 

따라서 DBA가 수동으로 수집해야 할 System Statistics는 10g와 다르게 Workload System Statistics라 불린다.

 

3.

Oracle 9i 이전까지는 I/O 기반의 Cost Model에서는 Time을 고려하지 않으며 CPU의 성능도 고려하지 않는다.

오로지 I/O의 수행횟수 ( Count )만을 고려한다.

 

I/O 기반의 Cost 계산

Cost = Single Block I/O Count + Multi Block I/O Count

 

동시접속 pk 중복 오류 방지방안

하나의 테이블에 여러 트랜잭션에서 일련번호 성 pk 데이타를 입력할 때 중복 오류가 발생하는 현상을 방지할 수 있는 방안입니다.

1. SEQUENCE 오브젝트를 이용한다.

이를 사용하면 일련번호 관련해서 중복오류를 없앨 수 있는 완벽한 방안입니다.

그러나 모델링이나 다른 이슈로 이렇게 처리하지 못하는 경우가 있습니다.

2. 1번에서 문제가 되는 경우가 일련번호가 연도별, 또는 다른 UID별 일련번호 생성과 관련해서일 것이다.

SELECT /*+ INDEX_DESC(A A30_PK) */ nvl(max(일련번호), 0) + 1 as max번호
INTO 일련번호
FROM TABLE_A30 A
WHERE 년도 = 2010

AND ROWNUM = 1;

 

로 MAX값을 구해와야 하는데 ROCK를 걸지 않고 비지니스 로직을 여러곳에서 동시에 실행시에

 

PK중복이 날 수 있다. ( 실제로도 나고 있고 )

 

오라클에서는 이와 같은 문제를 처리하기위해 AUTONOMOUS_TRANSACTION 을 제공한다.

 

CREATE OR REPLACE FUNCTION NUM_VAL (YEAR IN VARCHAR2)
RETURN VARCHAR2
IS
/*********** AUTONOMOUS_TRANSACTION 선언 시작**********/
PRAGMA AUTONOMOUS_TRANSACTION;
/*********** AUTONOMOUS_TRANSACTION 선언 끝**********/

vs_return_val VARCHAR(10);

BEGIN

-- 1. UPDATE --------------
BEGIN
UPDATE TEMP_A
SET SEQ_NO = SEQ_NO + 1
WHERE YEAR = V_YEAR
;
EXCEPTION
WHEN OTHERS THEN
에러처리

END;

-- 2. SELECT --------------
BEGIN
SELECT SEQ_NO
INTO vs_return_val
FROM TEMP_A
WHERE FRM_YEAR = V_YEAR

;
EXCEPTION
WHEN OTHERS THEN
에러처리

END;

COMMIT;
RETURN vs_return_val;

EXCEPTION
WHEN OTHERS THEN
에러처리;
END;

 

 

--SELECT /*+ INDEX_DESC(A A30_PK) */ nvl(max(일련번호), 0) + 1 as max번호
--INTO 일련번호
--FROM TABLE_A30 A
--WHERE 년도 = 2010

--AND ROWNUM = 1;

일련번호 := NUM_VAL('2010');

 

 

잘된다.

 

 

 

 

 

 

window7 + erwin 실행시 에러 해결

ERWin에서 파일 오픈 시 다음과 같은 오류가 발생하는 경우 해결 방법 입니다.

[오류메시지]

오류 Input file: <파일명>.ER1 with file version: -1 is not readable by Computer Associates ERwin

[해결방법]

사용 중인 OS 가 Vista 혹은 Window 7인 경우, ERwin을읽지 못하여 생기는 문제.
ERwin이 설치되어 있는 폴더에 가서 (기본적으로 C:Program FilesCAAllFusion ERwin Data Modeler에 설치 됩니다.) MMOPN32.exe 파일의 속성으로 갑니다.
호환성 탭으로 이동한 후, 호환성 모드의 '다음에 대해 호환성 모드로 이 프로그램 실행'을 체크하시고 window XP를 선택하면 됩니다.

 

원본 :

http://3030.tistory.com/59?srchid=BR1http%3A%2F%2F3030.tistory.com%2F59

[불펌]Oracle Client 설치 안하고 Toad 사용하기

Oracle Client 설치 안하고 Toad 사용하기

2008/09/03 15:34 in 일거리

1. 오라클 클라이언트 Instant Client Basic 버젼을 받는다.
   http://download.oracle.com/otn/nt/instantclient/instantclient-basic-win32-10.2.0.3-20061115.zip
2. C:\instantclient_10_2압축 풀기 ( 대략 100M가 정도 )
3. 시스템변수명에 TNS_ADMIN을 넣고 셋팅
   
4. C:\instantclient_10_2\tnsnames.ora 파일 생성
   TNS이름 =
       (DESCRIPTION =
           (ADDRESS_LIST =
               (ADDRESS = (PROTOCOL = TCP)(HOST = IP주소)(PORT = 1521))
           )                                                               
           (CONNECT_DATA =
           (SID = ifoundrydev)
       )
   )
5. Toad 접속 확인