요즘 너무 포스팅이 없어 간만에 간단한 포스팅을 해 보겠다.


/* PRIMARY KEY 재생성 방법 */

1. PRIMARY KEY DROP

alter table 테이블명 drop primary key;

2. PRIMARY KEY 생성

alter table 테이블명 add constraint 테이블_PK primary key(컬럼명)
using index storage(initial 1m next 1m pctincrease 0)
tablespace USERS;

간단하게 재 생성 하는 방법이다...


WRITTEN BY
한량이

,


오라클을 사용하다 보면 ROLLBACK SEGMENT의 사용상황이 궁금 할 때가 많다.
 
많은 파일을 삭제 했다던가.. 기타 등등..

SELECT SUBSTRB(A.SEGMENT_NAME, 1, 14) as "SEGMENT NAME",
       SUBSTRB(A.TABLESPACE_NAME, 1, 10) as "TABLESPACE NAME",
       TO_CHAR(A.SEGMENT_ID, '99999') AS "SEG ID",
       TO_CHAR(A.MAX_EXTENTS, '2010123199') AS "  MAX EXT",
       TO_CHAR(B.EXTENTS, '999999') as "EXTENTS",
       TO_CHAR(B.EXTENDS, '999999') as "EXTENDS",
       TO_CHAR((A.INITIAL_EXTENT + (B.EXTENTS-1)*A.NEXT_EXTENT)
            /1000000, '9,999.999') as "ALLOC(MB)",
       TO_CHAR(XACTS,'9,999') as "XACTS"
FROM DBA_ROLLBACK_SEGS A, V$ROLLSTAT B
WHERE A.SEGMENT_ID = B.USN(+)
ORDER BY 1; 
 
-- EXTENTS = 현재 할당된 EXTENT의 수  
-- EXTENDS = 마지막 트랜잭션에 의해 할당된 EXTENT의 수  

아래는 일반 롤백 세그먼트 보기 쿼리 이다.
 
SELECT * FROM DBA_ROLLBACK_SEGS ; 
 

 


WRITTEN BY
한량이

,

오래 간만에 오라클에 관련된 포스팅을 한다.
 
데이터베이스를 생성하고 테이블 스페이스를 생성하는데 도대체 얼마나 사용하고 있지는 궁금할 때가 많다.
 
툴을 설치해서 보기도 귀찮고 해서 유용한 쿼리를 제공한다.
 

SELECT SUBSTRB(A.TABLESPACE_NAME, 1,16) as TABLESPACE,
       TO_CHAR((A.TOTAL/1024), '999,999,999,990') as "총량(M바이트)",
       TO_CHAR((B.FREE/1024),  '999,999,999,990') as "남은량(M바이트)",
       TO_CHAR((A.TOTAL-B.FREE)/1024,  '999,999,999,990') as "사용량(M바이트)",
       TO_CHAR(A.BLOCKS, '9,999,990') as "총블럭",
       TO_CHAR(C.BLOCKS,  '9,999,990') as "사용블럭",
       TO_CHAR(100*NVL(C.BLOCKS,0)/A.BLOCKS, '999.99') as "사용율%"
FROM (SELECT TABLESPACE_NAME,
             SUM(BYTES)  as TOTAL,
             SUM(BLOCKS) as BLOCKS
      FROM DBA_DATA_FILES
      GROUP BY TABLESPACE_NAME
     ) A,
     (SELECT TABLESPACE_NAME,
             SUM(BYTES) as FREE
      FROM DBA_FREE_SPACE
      GROUP BY TABLESPACE_NAME
     ) B,
     (SELECT TABLESPACE_NAME,
             SUM(BLOCKS) as BLOCKS
      FROM DBA_EXTENTS
      GROUP BY TABLESPACE_NAME
     ) C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
      AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+)
ORDER BY A.TABLESPACE_NAME ;
 
이 쿼리를 실행해 보면 아주 깔끔한 결과를 볼 수 있다, 쿼리하는데 시간은 좀 걸릴 수 있다.
 
드래그가 안돼서 쿼리가 필요하신 분은 댓글 주세요.. 바로 보내드릴께요.^^




WRITTEN BY
한량이

,

  


오라클 에서 MSsql로 컨버젼 작업을 진행 혹은 반대로 진행 하는 경우가 많다.
 
또한 오라클에서는 이렇게 썼는데 MSsql에서는 어떻게 쓰지? 이렇게 가끔 생각하는 경우가 있다.
 
몇가지 기억나는 순서대로 정리 해 보았다.

아래의 정리내역은 순수하게 기억나는 대로 정리하였기 때문에 실수도 있음을 인정해 달라..ㅋ
 
그래도 나도 가끔 이 정리한 내역을 보면서 유용하게 사용 하곤 한다.
 

항목

처리방식

Outer Join 수정

- 표준 outer join을 사용할 것.

   주의) 표준 outer joun 사용 시 상수의 outer join 주의

   예) FROM v_acl_user acluser, v_acl_dept acldept

        WHERE  acluser.deptid=acldept.deptid(+)

        AND     acldept.useyn ='Y'(+)

    
       => FROM v_acl_user acluser LEFT OUTER JOIN  
                     v_acl_dept acldept ON  acluser.deptid=acldept.deptid 
           AND  acldept.useyn ='Y' 임

        FROM v_acl_user acluser LEFT OUTER JOIN  
                     v_acl_dept acldept ON  acluser.deptid=acldept.deptid

        WHERE  acldept.useyn ='Y' 가 절대 아님

NVL 수정

- NVL => ISNULL

   주의) NVL( 변수, '') 인 경우는 ISNULL 에서 NULL 과 동일하게 인식하므로
             건별로 수정

   예) NVL(a.amount,0)
           => ISNULL(a.amount,0)

Convert 함수

- TO_NUMBER, TO_DATE,TO_CHAR 등의 함수
      => CAST 또는 CONVERT 로 변환

   예) 1) TO_NUMBER(x0.eval_totpoint)
           => CAST(x0.eval_totpoint AS NUMERIC(20,5))

         2) to_char(sysdate,'YYYYMMDD')
            => CONVERT(varchar(8),GETDATE(),112)

String 연결 함수

- || => +

    예) B.id LIKE (A.id || '%')
           => B.id LIKE (A.id + '%')

SUBSTRING 함수

- SUBSTR => SUBSTRING

    예) SUBSTR(up_deptid ,1 ,4)
           => SUBSTRING(up_deptid ,1 ,4)

         SUBSTR(a.nm,4)
           => SUBSTR(a.nm,4,len(a.nm))

상위 1 개 Select

- /*+ first_rows */ => TOP 문 으로 치환

rownum 수정

- TOP 으로 수정

    예) WHERE rownum = 1
            => SELECT TOP 1

- Select 바로 옆의 rownum 은 Cursor 이용함
   - Inline View 형식의 rownum 은 dual 테이블 이용함

LENGTH/LENGTHB

- LENGTH => LEN

   예) LENGTH(up_deptid)
           => LEN(up_deptid)

 - LENGTHB => DATALENGTH

DECODE/Greatest/Least

 - CASE .. WHEN .. ELSE.. END 로 수정

   오라클은 8i(?) 부터 위의 문장이 되므로 오라클도 case사용

LPAD / RPAD

-REPLICATE 및 DATALENGTHB 이용하여 변경

  예) LPAD(to_char(w_seq_no), 4, '0')
         => REPLICATE('0', 4 - DATALENGTH(CONVERT(VARCHAR(4),@w_seq_no))) + CONVERT(VARCHAR(4),@w_seq_no)

        RPAD(' ',depth*3)
          => space(depth*3)

TRIM 함수

  예) TRIM(@v_apprepuserid)
          => RTRIM(LTRIM(@v_apprepuserid))

DATE 연산 함수

- ADDDATE 함수 사용

   예) sysdate + 1
          => ADDDATE(day,1,getdate()) 와 같은 형식으로 변경

INSTR 함수

- CHARINDEX  함수 사용

   예) INSTR(문자열a,문자열b)
          => CHARINDEX(문자열b,문자열a) 와 같이 변경됨

CHR(10)

 CHR(10) => CHAR(10), CHR => CHAR 로 변경

SF 수정 절차

1) varchar2 => varchar로

2) number => numeric

3) 변수 앞에 @ 붙이기 및 Declare 추가

   예제) aaa => @aaa

4) 변수 선언 시 Declare 추가

5) ";" 제외

6) Exception 부분의 no data found 부분 처리

7) IF 문 수정

8) 선언문 수정( :=  => SET 또는 SELECT)

9) INTO 문 없애기

  **기타) Query 문 수정

주의) 함수에서 GETDATE() 함수는 사용 불가

        #Temp table 사용 불가

        함수를 select 문에서 call 할 경우 반드시 owner 이름을 붙인다 => select dbo.sf_get('a'))

SP 수정 절차

* SF 와 거의 유사

Trigger 수정 절차

1) :old, :new 대신 select id from deleted OR select id from inserted 사용

2) SP Call 할 경우 sp_('a','a')=>exec sp_ 'a','a'

3) ON Table 명을 Trigger 명 바로 뒤에

    예제) CREATE TRIGGER tr_t_appline_insert ON t_appline

4) Before Trigger 가 안됨 => FOR 로 대치함

5) FOR EACH 인 경우 Cursor 를 이용하여 Loop 처리

6) old 와 new 를 같이 사용하는 UPDATE의 경우 PK 에 대한 수정은 없다고 가정

  * 나머지 문법적인 요소는 SP/SF 참조

SF 호출

- Store Function 호출 시 반드시 dbower.function 명으로 호출

   예) SELECT sf_appcontent_columnval('a','a')  
           FROM v_voter_d
          => SELECT dbo.sf_appcontent_columnval('a','a')  
              FROM  v_voter_d



다소 지저분하게 정리가 되었다. 위의 테이블은 ORACLE에서 MSSQL로의 CONVERSION 기준으로 정리 하였다.


WRITTEN BY
한량이

,

계층형의 데이터를 가져 오는데 많이 사용한다. 흔희들 업무에서는 부서의 정보를 가져오는 것에 대하여 사용한다.

사용하는 방법은

SELCT deptid,deptname  FROM 테이블 
START WITH deptid = 'xxx'                 -- 계층형 쿼리의 부모로 사용될 행을 지정, 서브 쿼리로도 사용이 가능
CONNECT BY PRIOR uptreeid = treeid  -- 부모와 자식의 관계를 지정, 서브쿼리 사용 불가
AND uptreeid <> '*'                            -- 기타 조건
     
 
위의 예제는 부서 ID가 xxx를 기준으로 부서의 상위 부서를 최상위 부서까지의 계층을 보여주는 쿼리이다

간단하게 작성하였지만 은근히 많이 사용되는 쿼리이다.

LPAD를 이용해서 deptname에 공백을 주어 표현하면 계층구도를 더욱 확실하게 나타낼 수 있다.

'IT reference > OS,DB' 카테고리의 다른 글

배드섹터 검사 및 치료  (2) 2008.11.25
리눅스 네트워크 연결 NFS  (0) 2008.11.19
오라클 리스타트 (SQLPLUS )  (0) 2008.11.06
오라클 유용한 쿼리 ROW_NUMBER()  (1) 2008.11.05
오라클 테이블 스페이스 관리  (0) 2008.11.04

WRITTEN BY
한량이

,

어떻게 작성하다 보니 오라클 관련으로 글이 참 많아 진다.

오늘은 ROW_NUMBER() 에 관하여 작성해 본다.

이게 참 유용하게 쓰인다.

물론 순서를 사용하기 위해서는 rownum을 많이 사용하기는 한다.

하지만 order by의 취양점이 있다.

예를 들어

SELECT rownum , aaa,bbb FROM table 이라고 해서 나온값과

SELECT rownum , aaa,bbb FROM table ORDER BY aaa 해서 나온값이 다르다.

또한 rownum을 순서로 이용하려면.
SELECT rownum , aaa,bbb
FROM (SELECT aaa,bbb
           FROM table ORDER BY aaa
)
이렇게 써야 원하는 결과를 얻을 수 있다.


이해 반해 ROW_NUMBER() 조건 절을 두어 그 안에서의 순서를 보여줄 수 있다
즉 각각의 데이터에 순서를 붙이기 위해서 유용하게 사용된다.

SELECT aaa, bbb, ROW_NUMBER() OVER (PARTITION BY aaa ORDER BY bbb) as ccc
FROM table

이렇게 작성 하면 aaa 그룹별로 bbb의 순서에 따라서 순서값을 얻을 수 있다.

결과값
aaa             bbb           ccc
--------      --------     --------
10               a                1
10               b                2
10               c                3
20               a                1
20               c                2
20               d                3
20               e                4
20               f                 5
30               a                1
30               d                2




WRITTEN BY
한량이

,



우리가 디비를 사용하고 있으면. 현재 세션에 걸려 있는 쿼리를 보고 싶을 경우가 있다.

어떤것들을 처리 하고 있는지.. 등등에 대한

물론 토드나, 네비게이터 등등의 툴을 사용해도 된다.

간단하게 아래의 정보로 현재 접속한 상태의 쿼리및 기타 정보를 볼 수 있다.

물론 Alter System Kill session 으로 강제로 죽여 줄 수도 있다.


SELECT 
      a.sid,             -- SID
      a.status,         -- 상태정보
      a.process,      -- 프로세스정보
      a.osuser,        -- 접속자의 OS 사용자 정보
      b.sql_text,       -- sql
      c.program       -- 접속 프로그램
FROM v$session a,
          v$sqlarea b,
          v$process c
WHERE a.sql_hash_value=b.hash_value
AND a.sql_address=b.address
AND a.paddr=c.addr
AND a.status='ACTIVE';  -- 현재 상태가 ACTIVE인것

이런 정보 및 기타 정보로는 추가 해서 보면된다. 
이거 속도가 무지 오래 걸린다... 죽었다고 생각하지 말고. 대략 10~20초 정도 기다리면 원하는 값이 나타난다.


WRITTEN BY
한량이

,


 
 
우리가 오라클을 사용하다 보면.. 인덱스를 리빌드해야 할 때가 온다..
 
그럼 그많은 테이블에 대해서 어떻게 인덱스를 일일히 다시 생성하고 만들어 줄까..
 
그래서 아주 쉬운 쿼리를 제공한다.
 
나서 다른 서버에 import를 할때 인덱스 테이블 스페이스를 따로 생성했는데도 불구 하고 잘 안들어 같다. 그럴때 유용하게 사용할 수 있다.

select 'ALTER INDEX '||index_name||' rebuild TABLESPACE 인텍스 테이블스페이스;' from user_indexes
  
확인
select index_name,table_name,tablespace_name from user_indexes;


  



WRITTEN BY
한량이

,

오라클 디비를 사용하다 보면 분명 프로그램 이상이겠지..

꼭 데이터베이스에 lock이 걸려 서버가 다운되거나... 미친듯이 오래 걸린다던가..

암튼 이만저만 짜증이 난다.. 중요한건.. 토드 같은 오라클 툴이 없으면.. 이거 LOCK걸렸을 때 쿼리 기억 안나면 미친다..

그래서 정리 해 봤다.. 힌트를 줘서 사용하니 조금 빠르군..

사용하는 계정으로 오라클을 로그인 하여 아래의 쿼리를 실행한다.


SELECT /*+ ordered */
        a.sid SID,
        to_char(b.serial#) SEQ,
        id1 ID1,
        to_char(b.audsid) AUD,
        b.username Uname,
        b.osuser OsUser,
        c.sql_text SQLSTMT
FROM v$lock a ,
        v$session b,
        v$sql c
WHERE   a.type = 'TM'
AND       a.sid = b.sid
AND       c.hash_value (+) = b.sql_hash_value;

제일 처음 나오는 a.sid는 Session ID이고  두번째의 b.serial# 는 Serial번호이다.
이 둘을 이용하여 해당작업을 강제 종료시킬 수 있다.

Alter System Kill session 'Sid,Serial#';
위에서 나온 첫째 필드값 -----^    ^----------두번째 필드값

물론 앞 두개의 컬럼 이외에 나머지는 그냥 정보로써 사용하면 된다...

kill하고 나면 사용하고 있는 데이타가 제대로 rollback이 되었는지 확인 해주는 센스....



WRITTEN BY
한량이

,