나의 향기에 젖다...

1. 16진수 -> 10진수

SELECT  TO_CHAR(15,'0X'), TO_CHAR(16,'0X'), TO_CHAR(17,'0X') 
  FROM  DUAL;

 >> OF   10   11

SELECT  TO_CHAR(15,'000X'), TO_CHAR(16,'000X'), TO_CHAR(17,'000X') 
  FROM  DUAL;

2. 10진수 -> 16진수

SELECT  TO_NUMBER('F', 'XX'), TO_NUMBER('10', 'XX'), TO_NUMBER('11', 'XX') 
  FROM  DUAL;

 >> 15   16   17

SELECT  S.FIRST_LOAD_TIME, TO_CHAR(S.BUFFER_GETS,'999,999,999,990') BUFFER_GETS,

              S.DISK_READS, S.ROWS_PROCESSED, S.EXECUTIONS,
              SUBSTR(U.NAME,1,1) USERNAME, S.MODULE, S.SQL_TEXT
   FROM  V$SQLAREA S, SYS.USER$ U
 WHERE  S.PARSING_USER_ID = U.USER#
     AND  U.NAME <> 'SYS'
     AND  S.DISK_READS > 10000
     AND  S.FIRST_LOAD_TIME >= TO_CHAR(SYSDATE,'yyyy-mm-dd')||'/00:00:00'
 ORDER BY  S.FIRST_LOAD_TIME DESC;

// TABLESPACE_NAME 별로 합처리

SELECT  SUBSTR(A.TABLESPACE_NAME,1,30) TABLESPACE,
              ROUND(SUM(A.TOTAL1)/1024/1024,1) "TOTALMB",
              ROUND(SUM(A.TOTAL1)/1024/1024,1)-ROUND(SUM(A.SUM1)/1024/1024,1) "USEDMB",
              ROUND(SUM(A.SUM1)/1024/1024,1) "FREEMB",
              ROUND((ROUND(SUM(A.TOTAL1)/1024/1024,1)-ROUND(SUM(A.SUM1)/1024/1024,1))

              /ROUND(SUM(A.TOTAL1)/1024/1024,1)*100,2) "USED%"
  FROM
             (SELECT  TABLESPACE_NAME,0 TOTAL1,SUM(BYTES) SUM1,MAX(BYTES) MAXB,

                            COUNT(BYTES) CNT
                 FROM  DBA_FREE_SPACE
               GROUP  BY TABLESPACE_NAME
              UNION
              SELECT  TABLESPACE_NAME,SUM(BYTES) TOTAL1,0,0,0
                 FROM  DBA_DATA_FILES
               GROUP  BY TABLESPACE_NAME) A
GROUP BY A.TABLESPACE_NAME
ORDER BY TABLESPACE;

// 전체 합처리

SELECT  ROUND(SUM(A.TOTAL1)/1024/1024/1024,1) "TOTALGB",
              ROUND(SUM(A.TOTAL1)/1024/1024/1024,1)-ROUND(SUM(A.SUM1)/1024/1024/1024,1) "USEDGB",
              ROUND(SUM(A.SUM1)/1024/1024/1024,1) "FREEGB",
              ROUND((ROUND(SUM(A.TOTAL1)/1024/1024,1)-ROUND(SUM(A.SUM1)/1024/1024,1))

              /ROUND(SUM(A.TOTAL1)/1024/1024,1)*100,2) "USED%"
  FROM
             (SELECT  TABLESPACE_NAME,0 TOTAL1,SUM(BYTES) SUM1,MAX(BYTES) MAXB,

                            COUNT(BYTES) CNT
                 FROM  DBA_FREE_SPACE
               GROUP BY TABLESPACE_NAME
              UNION
              SELECT  TABLESPACE_NAME,SUM(BYTES) TOTAL1,0,0,0
                 FROM  DBA_DATA_FILES
               GROUP BY TABLESPACE_NAME) A

1. 컬럼명과 데이블명은 원도우,리눅스,유닉스에서 대소문자를 가리지 않습니다.
 

2. SQL문은 대소문자를 구분하지 않습니다.
 
SELECT NAME FROM LEE;
select NAME from LEE;
-- 2개다 같은 출력을 합니다.
 
※ SQL 쿼리는 대문자로 써주는것이 좋습니다.
 

3.문자열 데이타형은 기본으로 대소문자를 구분합니다.
 

SELECT * FROM TAB WHERE NAME = 'lee';

SELECT * FROM TAB WHERE NAME = 'LEE';

-- lee컬럼의 값이 lee라면 첫번째것은 되지만 두번째것은 되지 않는다.
 

4.대소문자 구분없이 검색하는법
 

WHERE UPPER("+search+") LIKE UPPER('%"+keyword+"%')

WHERE LOWER("+search+") LIKE LOWER('%"+keyword+"%')

※검색할 단어와 비교할 컬럼을 모두 대문자나 소문자로 만든후 비교합니다.

** ROLLUP

- 그룹조건에 따라 전체 행을 그룹화하고, 각 그룹에 대한 부분합을 구하는 연산자

- ROLLUP절에는 255개의 컬럼까지 가능

- ROLLUP절에 컬럼의 개수가 n개이면 그룹핑 조합은 n + 1개

** GROUPING SETS

- 동일한 퀴리에 대해서 다양한 그룹핑을 정의 가능

- 개별로 그룹핑하여 UNION ALL한 경우와 동일하나, 성능적인 면에서 뛰어남

SQL >

 SELECT JBDATE, BAYONG, SUM(CHARGE) CHARGE
   FROM INVCON
  WHERE JBDATE BETWEEN '2013-02-04' AND '2013-02-06'
    AND BAYONG IN ('BS01','BS03')
GROUP BY ROLLUP (JBDATE, BAYONG);

SQL >

 SELECT JBDATE, BAYONG, SUM(CHARGE) CHARGE
   FROM INVCON
  WHERE JBDATE BETWEEN '2013-02-04' AND '2013-02-06'
    AND BAYONG IN ('BS01','BS03')
  GROUP BY GROUPING SETS ((JBDATE, BAYONG), (JBDATE), ());

>> 동일한 결과 나옴

JBDATE     BAYO     CHARGE
---------- ---- ----------
2013-02-04 BS01      90508
2013-02-04 BS03     559711
2013-02-04            650219
2013-02-05 BS01     176733
2013-02-05 BS03     533863
2013-02-05            710596
2013-02-06 BS01      93938
2013-02-06 BS03     371616
2013-02-06            465554
                          1826369

SQL >

 SELECT JBDATE, BAYONG, SUM(CHARGE) CHARGE
   FROM INVCON
  WHERE JBDATE BETWEEN '2013-02-04' AND '2013-02-06'
    AND BAYONG IN ('BS01','BS03')
  GROUP BY GROUPING SETS ((JBDATE, BAYONG),());

>> JBDATE의 그룹핑은 제외

JBDATE     BAYO     CHARGE
---------- ---- ----------
2013-02-04 BS01      90508
2013-02-04 BS03     559711
2013-02-05 BS01     176733
2013-02-05 BS03     533863
2013-02-06 BS01      93938
2013-02-06 BS03     371616
                          1826369

* 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

 기존에는 테이블을 새로 작성(reorg)  작업을 통해 커진 테이블 스페이스 크기를 복구 가능하였으나 10g 부터는 Shrink 기능을 이용하여 손쉽게 테이블을 줄일수 있게 되었다.

Shrink 기능은 HWM(High Water Mark)를 줄일수 있는 기능이다.

- 사전 확인 사항

init.ora 파일내에 compatible = 10.2 이상인것을 확인

ASSM (Automatic Segment Space Management) 테이블 스페이스여만 가능

각 테이블 조회 방법은

SELECT OBJ.OWNER
           , OBJ.TABLE_NAME, OBJ.TABLESPACE_NAME
           , (CASE WHEN NVL(IDX.CNT, 0) < 1 THEN 'YES' ELSE 'NO' END) SHRINKABLE
   FROM DBA_TABLES OBJ,
             (SELECT TABLE_NAME, COUNT(ROWNUM) CNT
                 FROM DBA_INDEXES
               WHERE INDEX_TYPE LIKE 'FUN%'
               GROUP BY TABLE_NAME) IDX
 WHERE OBJ.TABLE_NAME = IDX.TABLE_NAME(+)
     AND OBJ.TABLESPACE_NAME = :TABLESPACE_NAME;

쿼리를 통해 (테이블 스페이스 명을 인자값으로) 확인 가능하다.

1. 테이블 검색 방법

SELECT * FROM TABLE(DBMS_SPACE.ASA_RECOMMENDATIONS());

아래와 같이 테이블의 DBA_SEGMENTS에서 볼수 있는 크기와 축소 가능 크기가 조회된다.

SEGMENT_OWNER | SEGMENT_NAME | SEGMENT_TYPE | PARTITION_NAME |

ALLOCATED_SPACE | USED_SPACE | RECLAIMABLE_SPACE
MAX_TI | SMR_IF_LOG       | TABLE  | | 6761793074 | 5910149080 | 851643994
MAX_TI | SMR_IF_LOG_I01 | INDEX  | |   478455081 |   420139142 | 58315939
MAX_TI | SMR_IF_LOG_I02 | INDEX  | |   411921243 |   398204079 | 13717164

2. 축소하기

가장 먼저 행들을 이동 가능하도록 변경한다.

위의 SMR_IF_LOG 테이블을 기준으로 설명하겠다.

ALTER TABLE SMR_IF_LOG ENABLE ROW MOVEMENT;

--> 반대로는 ALTER TABLE SMR_IF_LOG DISABLE ROW MOVEMENT; 이다..

이제 SHRINK 기능을 수행할수 있으며, 다음 3가지 형태로 가능하다

① ALTER TABLE SMR_IF_LOG SHRINK SPACE; 
    -- SMR_IF_LOG 테이블의 공간만 TABLESPACE로 환원

② ALTER TABLE SMR_IF_LOG SHRINK SPACE CASCADE;
    -- 모든 관련 OBJECT까지 TABLESPACE으로 공간 환원

③ ALTER TABLE SMR_IF_LOG SHRINK SPACE COMPACT;
   -- 행들만 이동시킴.

※ SHRINK 를 적용 시킬  없는 테이블
UNDO segments
temporary segments
clustered tables
tables with a column of datatype LONG
LOB indexes
IOT mapping tables and IOT overflow segmnets
tables with MVIEWS with ON COMMIT
tables with MVIEWS which are based on ROWIDs.

[출처] 웹 개발과 관련된 모든 것들 (http://web-dev.tistory.com/116)

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