사랑하기 위해 필요한 것들

<오라클 테이블 정보 보기>

-- 전체 테이블 정보 보기

select * from all_tables ;

-- 특정 유정의 테이블 정보

select * from all_tables where owner = '유저아이디'

-- 테이블 comments 정보

select * from user_tab_comments

-- 테이블 컬럼 comments 정보

select * from user_col_comments 

-- 컬럼 정보(컬럼 타입, 길이, null 허용 여부 등)

select * from user_tab_columns

-- 해당 유저의 모든 컬럼 제약 사항 보기

select * from user_constraints

-- 해당 유저가 조회 가능한 데이터베이스의 모든 제약 조건 조회

select * from dba_constraint

-- 해당 유저가 조회 가능한 모든 제약 조건 조회

select * from all_constraint

select * from all_tables a1 left join user_tab_columns a2 on a1.table_name=a2.table_name where owner='유저아이디';

SQLPLUS 출력 컬럼 수 조정하기

--조회컬럼의 크기 변경하기
--column 컬럼명 format
--문자열인경우:  a글자수
--숫자인경우 9나 0으로 자릿수표현 : 9999 데이터가 없으면 표현하지 않는다. / 00000 데이터가 없어도 표현
--일시적으로 사용된다. 컬럼명이 같다면 테이블이 달라도 설정한 자릿수로 출력된다.
--column은 줄여서 col만 써도 된다.
--column은 sqlplus에서만 사용할수 있는 sqlplus문장이다.

column name format a6;
col num format 00;
col address format a13;
--포멧으로 설정한 글자가 컬럼의 있는 글자보다 작다면 해당 레코드는 두줄로 표현된다.

프로시저나 펑션을 만들다 보면 여러 소스코드 파일을 검색하듯이 여러 프로시저나 펑션의 내용을 검색하고자 할 경우가 있습니다.

결론부터 말하자면 USER_SOURCE 뷰를 이용하면 됩니다.

아래는 USER_SOURCE뷰의 스크립트입니다. (Oracle8i 기준입니다.)

SQL 오라클 내장함수(변환형 함수, 날짜 함수, 숫자형 함수)

SQL*Loader 사용하기

오라클/SQL/SQL공부
 

1. SQL*Loader란 ?
외부 화일에 있는 데이터를 Oracle에 Upload 할 수 있는 유틸리티 입니다.
기본적으로 외부 데이타 화일, 콘트롤화일(로드하는 데이타의 정보를 저장)이 필요합니다.

2. SQL*Loader 특징
  - 하나 이상의 입력 파일을 사용 가능
  - 로드를 위해 여러 개의 입력 레코드가 하나의 논리적 레코드로 결합될 수 있습니다..
  - 입력 필드는 고정 길이 또는 다양한 길이를 가질 수 있습니다.
  - 입력 데이터는  등   어떤 유형도 가능 합니다.
  - 데이터는 디스크/테이프/명명된 파이프 등 여러 유형의 매체로부터 로드 가능  
  - 데이터가 한 번에 여러 테이블로 로드 가능
  - 텍스트 데이터는 DB 버퍼 캐쉬를 거치지 않고 테이블로 직접 로드될 수 있다.
  - 테이블에 있는 기존 데이터를 대체 하거나(Replace) 추가(Append) 할 수 있는 기능이 있습니다.

3. SQL*Loader의 사용

기본문법 :    SQLLDR [keyword=] value  [ [keyword=] value ]...

     - KEYWORD : 아래에 설명되는 키워드중의 하나이며 VALUE는  키워드에 지정되는  값
  

실행예)

SQLLDR userid=scott/tiger control='test.ctl' log='test.log' 

키워드

•USERID :  사용자 ID와 암호
•CONTROL : 콘트롤 파일명
•LOG : 로그 파일명(기본 이름은 컨트롤파일명.log)
•BAD : 업로드에 실패한 레코드들이 저장 된다.(기본 이름은 컨트롤파일명.bad)
•DATA :  업로드를 위한 데이터 파일 이름 
•DISCARD : 선택되지 않은 레코드가 저장되는 디스카드 파일(선택 사항)
•DISCARDMAX :  버림(discard)의 최대 허용 갯수(기본값은 모든 버림을 허용하는 것입니다. 잘못된 입력 파일이 지정될 경우 실행을 중단시킬 안전 수단으로 사용) 
•SKIP : 건너 뛸 레코드 수, 주로 이전에 실패한 로드를 계속할 때 사용됨(한 테이블로 로드할 때나 로드되고 있는 모든 테이블에 대해 동일한 수의 레코드를 건너 뛸 때만 이 옵션을 사용)
•LOAD :  SKIP에 의해 지정된 레코드를 건너 뛴 다음 로드할 레코드 수를 지정 
•ERRORS : 배드 레코드의 최대 수
•ROWS : conventional 로드의 경우 각 삽입 전에 배열에 구축될 행의 수 지정(direct path로드의 경우 이 키워드는 각 data save마다 입력으로부터 읽어들일 행의 근사 수를 정의, Direct로드는 data save 전에 완전한 블록을 구축하고 버릴 행과 부적합한 행을거부한다.)
•BINDSIZE : conventional 로드의 경우 각 데이터베이스 호출시 삽입될 행의 배열을 구축하는데 사용될 최대 바이트 수를 지정(ROWS 파라미터도 지정되면 SQL*Loader는 BINDSIZE의 제한에 따라 ROWS에 의해 정의된 만큼의 행을 구축합니다.)
•DIRECT :  TRUE로 설정되면 SQL*Loader는 direct path를 사용. 반대의 경우는 기본 값인 conventional path 사용.
•PARFILE : 모든 로드 파라미터를 포함하고 있는 파일의 이름을 지정(명령 라인에 정의된 파라미터는  파라미터 파일에 정의된 값들을 무효화합니다.)
•PARALLEL : direct 로드에서만 적합한 이 파라미터는 다중 병렬 direct 로드가 수행되도록 지원
•FILE : 병렬 direct 로드의 경우 임시 세그먼트가 생성될 파일을 지정

--------------------------------------------------------------------------------

  
   Control File 예(Control File에서 데이터를 포함 할 경우)  
    LOAD DATA
    INFILE *
    REPLACE
    INTO TABLE  mydept　　<-- 오라클의 테이블 이름
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    (DEPTNO, DNAME)
    BEGINDATA
    40,SALES
    50,"RESEARCH"

    60,"ART"

참고 : 
   - Control File에서 데이터를 포함하지 않을 경우에는 INFILE 다음에 DATA파일을 지
        정하고 BEGINDATA 부터 생략 하면 된다.
   - NOLOGGING 키워드를 사용하여 테이블에 NOLOG 속성을 설정하는 것과 콘트롤 
     파일에 UNRECOVERABLE 옵션을 사용하는 것은 서로 같다.

 
6. Data File
  ① 콘트롤 파일에서 정의된 형식의 입력을 위한 데이터가 있다.
  ② UpLoad될 데이터를 Control File에 정의 할 수도 있고 Data File에 따로 분리 할 수도 
      있다.
   
7. Log File
  - 로드 시작/종료 시간, 총로드시간등 정보를 제공 
  - Load상태 정보를 저장
  -로드된 행의 수, 에러로 인해 로드가 거부된 행의 수, 버려진(Discard) 행의 수등의
   정보를 제공 한다.
  

출처 : ONJ프로그래밍 (http://www.onjprogramming.co.kr)

--------------------------------------------------------------------------------

(예제)

1. 연습용 테이블 생성

SQL> create table bbangmi_list(

  2  bbang_no           number not null primary key,

  3  food                       varchar2(30) not null,

  4  when                       varchar2(4)

  5  );

테이블이 생성되었습니다.

--------------------------------------------------------------------------------

2. 제어파일 데이터가 들어있는 경우

(C:\Documents and Settings\onj에 저장했다)

myfood.ctl파일을 만들었다.

LOAD DATA

INFILE *

INTO TABLE bbangmi_list

FIELDS TERMINATED BY','

(BBANG_NO, FOOD, WHEN)

BEGINDATA

10, 떡볶이, 간식

20, 피자, 저녁

30, 치킨, 점심

40, 맥주, 아침

50, 커피, 아침

3. 실행

C:\Documents and Settings\onj>sqlldr userid=scott/tiger control = 'myfood.ctl'

SQL*Loader: Release 11.2.0.1.0 - Production on 금 8월 2 15:47:22 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

커밋 시점에 도달 - 논리 레코드 개수 5

4. 데이터 insert 실행여부 확인

(저장폴더에는 이제 myfood.log가 생성되어있다.)

SQL> connect scott/tiger

SQL> select*from bbangmi_list;

  BBANG_NO FOOD                           WHEN

---------- ------------------------------ ----

        10 떡볶이                         간식

        20 피자                           저녁

        30 치킨                           점심

        40 맥주                           아침

        50 커피                           아침

--------------------------------------------------------------------------------

2. 제어파일과 데이터 파일이 분리된 경우
#. control file 부분 : food.ctl저장

#. DATA : food.dat저장

@ food.ctl 내용

LOAD DATA

INFILE 'food.dat'       

APPEND                

INTO TABLE bbangmi_list

FIELDS TERMINATED BY ','

(bbangmi_no,food,when)

--------------------------------------------------------------------------------

@ food.data 내용

60,금식,안돼

20,초콜렛,간식

10,콜라,저녁

(**주의!! data 파일에 빈칸이 있으면 자료가 안들어가 ㅠㅜㅠㅜㅠㅜㅠㅜ

예: 60, 금식, 안돼 nononono!!!

바른 답 : 60,금식,안돼 goooooooooood!!!

3. 실행

C:\Documents and Settings\onj>sqlldr userid=scott/tiger control = 'food.ctl'

SQL*Loader: Release 11.2.0.1.0 - Production on 금 8월 2 15:57:29 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

커밋 시점에 도달 - 논리 레코드 개수 2

커밋 시점에 도달 - 논리 레코드 개수 3

(dir확인)

2013-08-02  오후 03:57                56 food.bad

2013-08-02  오후 03:55               110 food.ctl

2013-08-02  오후 03:56                54 food.DAT

2013-08-02  오후 03:57             1,972 food.log

2013-08-02  오후 12:08     1,455,095,808 full.dmp

2013-08-01  오전 11:20    <DIR>          My Documents

2013-08-02  오후 02:26               157 mydept.ctl

2013-08-02  오후 02:26             1,537 mydept.log

2013-06-22  오후 03:31       923,430,912 myemp.dmp

2013-08-02  오후 03:46               179 myfood.ctl

2013-08-02  오후 03:47             1,549 myfood.log

2013-06-15  오전 11:22    <DIR>          Oracle

2013-08-02  오전 11:34     1,455,095,808 scott.dmp

2013-06-22  오전 10:32             4,096 scott.emp

2013-07-18  오후 03:01                 0 select

2013-07-18  오후 03:02                 0 spool

2013-08-02  오전 11:44             8,192 structure.dmp

2013-07-18  오후 05:30                 0 test.sql

2013-08-02  오전 10:16    <DIR>          바탕 화면

2011-07-25  오후 03:41    <DIR>          시작 메뉴

              34개 파일       5,289,240,626 바이트

               7개 디렉터리  77,600,690,176 바이트 남음

food.bad 파일이 생성됨 :

----내용----

20,초콜렛,간식

10,콜라,저녁

------------

(해석) BAD파일이 생긴 이유는 이미 기존에 위의 실습을 통해 10, 20, 30번,40번,50번은 저장 되어 있는 상태.

그러므로 APPEND 되는 10, 20, 60 번중에서 10, 20번을 가진 데이터는 DISCARD 되는 것

(bbang_no 가 Primary Key)

4. 데이터 insert 여부 확인

SQL> select * from bbangmi_list;

  BBANG_NO FOOD                           WHEN

---------- ------------------------------ ----

        10 떡볶이                         간식

        20 피자                           저녁

        30 치킨                           점심

        40 맥주                           아침

        50 커피                           아침

        60 금식                           안돼

6 개의 행이 선택되었습니다.

분모가 0이되어서 발생하는 문제.
 
당연히 분모가 0이 안되게 하면 되지만, 데이터의 정확성을 위해서는 0도 들어올 수 있어야 하는데
 
그럴때는 아래와 같이 NVL과 DECODE로 해결할 수 있습니다.

SELECT NVL(분자 / DECODE(분모, 0, NULL, 분모), 0) FROM 테이블;

SELECT A.SID, A.SERIAL# FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C WHERE A.SID=B.SID AND B.ID1=C.OBJECT_ID AND B.TYPE='TM' AND C.OBJECT_NAME='TEST'

alter system kill session '36, 16789';

한달 날짜 출력 해 보기

ORACLE PL/SQL 연습

DECLARE
    iCOUNT INTEGER;
    pDATE VARCHAR2(10) ;
    pYMD VARCHAR2(30);
BEGIN
  iCOUNT := 1;
  WHILE iCOUNT <= 31 LOOP
    IF(iCOUNT < 10) THEN
      pDATE := '0' || iCOUNT ;
    ELSE
      pDATE := iCOUNT;
    END IF ;

    pYMD := '2012-01-' || pDATE || ' 00:00:00' ;

      DBMS_OUTPUT.PUT_LINE(pYMD);

    iCOUNT := iCOUNT + 1;
  END LOOP;
END ;