SELECT A.TABLE_NAME, B.COMMENTS
    FROM ALL_TABLES A, ALL_TAB_COMMENTS B
   WHERE A.TABLE_NAME = B.TABLE_NAME AND A.TABLE_NAME LIKE :table_name || '%'
ORDER BY A.TABLE_NAME
종종 사용하는데 쓸때마다 잊어서 메모해놓자.
Posted by 두억시니 트랙백 0 : 댓글 0
 SELECT COLUMN_ID AS SEQ,
         (SELECT NVL (D.POSITION, '')
            FROM ALL_CONS_COLUMNS D, all_constraints E
           WHERE     D.OWNER = UPPER ( :user_name)
                 AND D.OWNER = A.OWNER
                 AND D.TABLE_NAME = A.TABLE_NAME
                 AND D.COLUMN_NAME = A.COLUMN_NAME
                 AND D.OWNER = E.OWNER
                 AND D.TABLE_NAME = E.TABLE_NAME
                 AND D.CONSTRAINT_NAME = E.CONSTRAINT_NAME
                 AND E.CONSTRAINT_TYPE = 'P')
            AS PK,
         (SELECT CASE WHEN D.POSITION IS NULL THEN '' ELSE 'V' END
            FROM ALL_CONS_COLUMNS D, all_constraints E
           WHERE     D.OWNER = UPPER ( :user_name)
                 AND D.OWNER = A.OWNER
                 AND D.TABLE_NAME = A.TABLE_NAME
                 AND D.COLUMN_NAME = A.COLUMN_NAME
                 AND D.OWNER = E.OWNER
                 AND D.TABLE_NAME = E.TABLE_NAME
                 AND D.CONSTRAINT_NAME = E.CONSTRAINT_NAME
                 AND E.CONSTRAINT_TYPE = 'R')
            AS FK,
         A.COLUMN_NAME AS COLUMN_ID,
         C.COMMENTS,
         A.DATA_TYPE,
         A.DATA_LENGTH,
         A.NULLABLE,
         A.DATA_DEFAULT
    FROM ALL_TAB_COLUMNS A, ALL_TAB_COMMENTS B, ALL_COL_COMMENTS C
   WHERE     A.OWNER = UPPER ( :user_name)
         AND B.OWNER = UPPER ( :user_name)
         AND C.OWNER = UPPER ( :user_name)
         AND A.OWNER = B.OWNER
         AND A.OWNER = C.OWNER
         AND A.TABLE_NAME = B.TABLE_NAME
         AND A.TABLE_NAME = C.TABLE_NAME
         AND A.COLUMN_NAME = C.COLUMN_NAME
         AND A.TABLE_NAME = UPPER ( :table_name)
ORDER BY 1
MSSQL은 어떻게 할까나...


Posted by 두억시니 트랙백 0 : 댓글 0
보통 2개 의 테이블에서 조인을 하여 특정 테이블의 컬럼을 Update 하고 싶을 때, 대부분 SET 절에 SubQuery 형태로 문장을 작성하여, 특정값을 가져오게 하여 Update 하도록 많이 작성을 할 것입니다. 예를 들면 아래와 같은 문장이 되겠죠?
UPDATE table_a a
   SET column_1 = (SELECT column_1
                     FROM table_b b
                    WHERE b.KEY = a.KEY)
그럼 위의 문장과 같은 형식으로 table_a 에 두개 이상의 컬럼을 Update 한다고 생각하면,
UPDATE table_a a
   SET column_1 = (SELECT column_1
                     FROM table_b b
                    WHERE b.key = a.key),
       column_2 = (SELECT column_2
                     FROM table_b b
                    WHERE b.key = a.key),
       column_3 = (SELECT column_3
                     FROM table_b b
                    WHERE b.key = a.key)
와 같은 식의 문장을 생각할 수 있습니다. 하지만, 아래와 같은 문장은 어떻게 될까요?
UPDATE
       (
        SELECT a.column_1 AS a_column_1,
               b.column_1 AS b_column_1
          FROM table_a a,
               table_b b
         WHERE a.key= b.key
       )
   SET a_column_1 = b_column_1
SQL문을 많이 접해보신 분이라면, 위 문장이 대략 어떤 의미인지 아실 수 있을 것 입니다. 즉, 위 문장과 같이 Updatable Join View 를 생각할 수 있습니다. 물론 이경우 조인되는 2개의 테이블은 반드시 1:1 또는 1:N의 관계여야 하며, Update되는 컬럼의 테이블은 N쪽 집합이어야 합니다. 이것은 1에 해당하는 집합인 부모 table의 조인 컬럼이 UK 또는 PK로 설정되어 있어야 된다는 것입니다. 위의 문장에서 보이는 table_a 와 table_b가 UK 또는 PK 로 설정이 되어 있지 않으면 ORA-01779 cannot modify a column which maps to a non key-preserved table 에러를 방생하며 실행되지 않습니다. 이쯤에서 한번 고민해봐야 할 문제가, 처음에 문제로 던졌던 두개의 Table을 조인하여, 특정 table의 컬럼 하나만 업데이트 할경우엔 Set 절에 Sub-Query 형태로 문장을 작성해도 큰 문제가 되지 않을 듯 합니다. 하지만, 컬럼이 여러개일 경우와 행이 무수히 많은 Table을 Update 할 경우 SET절에서 동일 문장으로 특정 테이블을 반복적으로 실행해야하는 로드가 발생하므로 Update 해야 하는 행이 많을 경우엔 Updatable Join View를 활용 해야만 하는 필요가 있다. 그러나, Updateable Join View 를 활용하기 위해 매번 테이블의 UK 혹은 PK를 생성해야만 해야 하나 라는 필요성에 대해 생각해 보게 되는데, 일반적으로는 UK나 PK Constraint를 설정하기 어려운 것이 현실입니다. 따라서, 이러한 Constraint를 피해서 Updatable Join View를 사용할 수 있도록 하기 위해 준비된 오라클 힌트가 있는데 그것이 바로, BYPASS_UJVC 입니다. 즉, 위에서 소개한 문장에서 UK 혹은 PK가 설정되지 않아 에러가 리턴 될경우 아래와 같이 힌트를 준다면 정상적으로 실행이 될 것입니다.
UPDATE 
       (
        SELECT a.column_1 AS a_column_1,
               b.column_1 AS b_column_1
          FROM table_a a,
               table_b b
         WHERE a.key= b.key
       )
   SET a_column_1 = b_column_1
컬럼이 여러개 일 경우에도,
UPDATE 
       (
        SELECT a.column_1 AS a_column_1,
               b.column_1 AS b_column_1,
               a.column_2 AS a_column_2,
               b.column_2 AS b_column_2,
               a.column_3 AS a_column_3,
               b.column_3 AS b_column_3
          FROM table_a a,
               table_b b
         WHERE a.key= b.key
       )
   SET a_column_1 = b_column_1,
       a_column_2 = b_column_2,
       a_column_3 = b_column_3

위와 같이 표현될 수 있을 것 입니다. 이렇게 두개이상의 테이블을 조인하여 Update 할 경우 Updatable Join View 를 활용하여 Update 문을 실행하면, 실행시간도 단축(튜닝)되는 효과를 보실 수 있을 듯 합니다. 마지막으로 위에서 소개한 힌트의 의미를 풀어보자면 bypass_ujvc : Bypass Updateable Join view Constraints 이라는 의미 입니다. 풀어서 적어보니, UJVC 라는 의미가 쏙 들어오지 않나요?


출처 : http://oracleerp.co.kr/bbs/board.php?bo_table=database&wr_id=3

Posted by 두억시니 트랙백 0 : 댓글 0

여백 만들기

2014.11.26 19:30 from database/oracle
/* Formatted on 2014/11/26 오후 7:31:59 (QP5 v5.256.13226.35510) */
WITH M
     AS (SELECT 1 AS seq, 'A' AS val FROM DUAL
         UNION ALL
         SELECT 2 AS seq, 'B' AS val FROM DUAL
         UNION ALL
         SELECT 3 AS seq, 'C' AS val FROM DUAL
         UNION ALL
         SELECT 4 AS seq, 'E' AS val FROM DUAL
         UNION ALL
         SELECT 5 AS seq, 'E' AS val FROM DUAL)
SELECT seq, val FROM M
UNION ALL
    SELECT ROWNUM + (SELECT COUNT (*) FROM M) AS seq,
           DECODE (ROWNUM, 1, '-이하여백-', NULL)
      FROM (SELECT '' val
              FROM DUAL
             WHERE 0 <
                      MOD (
                         (CASE
                             WHEN (SELECT COUNT (*) FROM M) = 0 THEN 29
                             ELSE (SELECT COUNT (*) FROM M)
                          END),
                         30)                           -- 30으로 딱 떨어질때를 위한 제동장치
                            ) S
CONNECT BY LEVEL <=
                30
              - MOD (
                   (CASE
                       WHEN (SELECT COUNT (*) FROM M) = 0 THEN 29
                       ELSE (SELECT COUNT (*) FROM M)
                    END),
                   30)
Posted by 두억시니 트랙백 0 : 댓글 0
SELECT WMSYS.WM_CONCAT (MESSAGE) MESSAGE
  FROM (SELECT LO.MESSAGE MESSAGE
          FROM SMEIS.S_LECTUREOPINION LO
         WHERE     LO.YEAR = '2014'
               AND LO.SEASONCODE = '11'
               AND LO.SUBJECTCODE = '380031'
               AND LO.DIVISION = '12'
               AND MESSAGE IS NOT NULL)
Posted by 두억시니 트랙백 0 : 댓글 0
INSERT INTO S_UPDATELOG
   SELECT *
     FROM (SELECT *
             FROM S_UPDATELOG AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE) A
    WHERE     A.STNO = '2009432007'
          AND A.CHANGECODE = '21'
          AND A.CHANGEYEAR = '2012';
Posted by 두억시니 트랙백 0 : 댓글 0



SELECT * FROM nls_database_parameters WHERE parameter LIKE '%CHARACTERSET%';


Posted by 두억시니 트랙백 0 : 댓글 0

집의 인터넷 공유기 안에 Oracle 11g를 기본값으로 설치하고 외부 접속 때문에 고생하고 몇자 끄적여 본다.
(이리도 쉬운걸 뱅뱅 돌고 돌아 해결하고 나니 속이 후련하기도 하고, 화가 나기도 하고 그러네.ㅡ,.ㅡ;;;)
아마도 Oracle 9i 또는 10g 도 공통적인 문제점(?)이 발생될것이 예상되니 이 문서를 참고해도 될듯 싶다.

오라클 버전별 호환성

다행히도 회사에서 사용하는 10.2와 9.2 버전은 11.2 버전과 호환이 된다. (출처 : http://branden.tistory.com/entry/oracle)


< 적용 환경 >
OS : Microsoft Windows 7 Ultimate K (x64)
DB : Oracle Database 11g Release 2 (11.2.0.1.0) for Microsoft Windows (32-bit)
인터넷 공유기 : ipTIME N8004(WOL, DDNS, 포트포워드를 사용)



< 방화벽 설정에 문제가 있을때 자주 볼수 있는 에러 메세지>
TNS-12541: TNS:리스너가 없습니다.
ORA-12170: TNS: 접속 시간 초과가 발생함


자, 이제 본격적으로 설정 방법을 알아보자.

① 오라클이 설치될 컴퓨터(서버)의 아이피는 당연히 고정으로 설정해준다.

Internet Prorocol Version 4 (TCP/IPv4) 속성

ipTIME 인터넷 공유기의 DHCP로 아이피를 부여받은 PC는 잘 변경이 되지 않는데, 혹시 모르니 고정 아이피로 설정해준다. 아니 여러가지 이유로 IP는 고정으로 설정해주는게 좋다. ㅎㅎ



② 오라클 설치, 별다른 주의사항은 없다. 자신이 특수한 네트워크 환경을 추구한다면 기본적인 Setting값 정도는 알고있지 아니한가? 



③ 리스너 구성
[시작]-[모든 프로그램]-[Oracle - OraDb11g_home1]-[구성 및 이전 툴]-[Net Manager] 를 실행하고,

Oracle Net Manager

화면 하단의 [주소 추가] 버튼을 누르고 위의 1번에서 설정한 IP를 입력해준다. 리스너 구성이 변경되었다면 명령 프롬프트(cmd) 를 열고 lsnrctl stop 하고 lsnrctl start 해서 리스너를 재시작해주는것이 좋다.




④ ipTIME 공유기 설정

iptime

인터넷 공유기 설정의 포트포워드 설정을 한다. DMZ로 설정했다면 상관 없겠지만, 그렇지 않은경우 포트포워드로 Oracle 1521 포트에 대한 설정을 해준다.




⑤ 윈도우7의 방화벽 설정
[시작]-[제어판]-[시스템 및 보안]-[Windows 방화벽에서 프로그램 허용]

허용되는 프로그램

oracle.exe 와 TNSLSNR.EXE 가 각각 등록되어 있는지 확인한다. 되어있지 않다면 오라클 설치폴더의 BIN 폴더에서 추가해준다. Oracle 11 버전의 경우 위의 프로그램들이 자동으로 등록되는것 같다. 하위 버전에서 되지 않는다면 수동으로 추가해준다.



특별히 윈도우즈 방화벽 설정에서 인바운드 TCP 1521 포트를 열어주진 않아도 된다. 추후 Oracle의 기본포트 1521를 바꿀수도 있으니 포트를 열어주기 보단 프로그램을 추가하는게 보안상에 더 안전하다. 위의 ⑤번 작업을 하고도 외부에서 접속이 되지 않는다면 잠깐 방화벽을 disable 해보고 접속해보고 윈도우즈 방화벽이 문제인건지 확인해 볼수 있다. ①번부터 ⑤번까지 번호(순서)를 채번하긴 했지만, 설정 순서라기 보단 필요한 부분만 참고하면 되겠다. 필자 본인은 이렇게 해서 외부에서 DDNS 주소로 접속하니 접속이 잘 된다. 필자처럼 ③번 항목 리스너 구성에서 주소를 DDNS(xxxxx.iptime.org)로 입력하는 우를 범하지 말길 바란다.
Posted by 두억시니 트랙백 1 : 댓글 1


ORA-12705

NLS 데이터 파일에 액세스할 수 없거나 부적합한 환경이 지정되었습니다.



기존 라이브러리를 추출하던중 나타난 접속 오류.
해당 노트북에는 Oracle 9i, Toad 10.6, instantclient 10 이 설치되어 있었다.

해경 방법 : [내 컴퓨터] - [시스템 등록 정보] - [고급] - [환경변수] 에 들어가서 아래의 [시스템 변수] 에 추가

( 변수 : NLS_LANG | 값 :  KOREAN_KOREA.KO16MSWIN949 )

시스템 등록 정보 - 환경 변수

시스템 변수 에 NLS_LANG 을 추가한다.(새로 만들기)

 

Toad 는 정상 접속 되는걸 보니, 이녀석은 Instantclient 로 붙었나 보다.ㅡ,.,ㅡ; 시작부터 삐걱거리네. ㅎㅎ
Posted by 두억시니 트랙백 0 : 댓글 1
얼마전 이직한 회사에서는 오라클9i 버전을 사용한다고 해서 연습삼하 설치해 보았다. 별다른 주의사항은 없었지만 중국어(간체) 추가와 문자집합을 유니코드사용 정도만 기억해 두면 되겠다. 오라클 9i의 설치파일은 10g와 달리 오라클 기본 홈페이지에서 찾을수 없었고, Oracle Software Delivery Cloud 라는 별도의 다운로드 사이트에서 받을수 있으니 참고하도록 하자.(무료 Oracle 계정/인증 필요) 다운로드 받은 파일은 Oracle9i Database Release 2 (9.2.0.1.0) for Microsoft Windows NT/2000/XP 라는 버전이고 모두 3장의 CD로 구성되어 있다. 다운로드 받은 파일은 A99346-01,  A99347-01, A99348-01 이라는 zip 파일로 구성되어 있으며 설치하기전 각각 폴더에 압축을 풀고 1번 CD(A99346-01)의 setup.exe 파일을 더블 클릭해서 설치를 시작한다.


01

1. 오라클9i 설치의 첫 화면이다. 별다른 버튼이 보이지 않으므로 [다음]을 클릭 하자.

02

2. 소스에는 다운로드 받은 오라클9i 의 위치가 자동 표기되고, 기본 대상폴더도 자동 표기된다. 이번 설치는 연습이라 C드라이브에 설치했지만 추후 정식으로 설치할 때에는 C드라이브가 아닌 다른 드라이브에 설치해야 한다. 내용을 확인하고 [다음] 버튼을 누른다.

03

3. 사용 가능한 제품 목록이 나온다. Database를 설치할것이므로 첫번째 라디오 버튼을 선택하고, 우측하단의 [제품 언어] 버튼을 누른다.

04

4. 선택된 언어에는 영어, 한국어만 보인다. 좌측에서 추가할언어 중국어(간체)를 선택하고 > 버튼을 눌러 선택하고. [확인] 버튼을 누른다.

05

5. 설치 유형은 Standard Edition 을 선택한다. 회사에 보유한 라이센스가 Standard 라고 한다. [다음] 버튼을 누른다.

06

6. Windows 방화벽이 활성화 되어있다면, 다음과 같은 차단에 관련된 팝업 윈도우를 볼 수 있다. 앞으로 주력으로 사용할 프로그램이므로 당연히 [차단해제(U)] 버튼을 누른다.

07

7. Oracle MTS Recovry Service 구성에 관련된 포트번호를 입력하라고 한다.(기본값:2030) 이 구성요소는 Oracle 9i와 Microsoft Transaction Server 완벽한 통합을 지원한다. Microsoft Transaction Server를 이용하는 COM 기반은 애플리케이션의 개발과 전개를 가능하게 한다.(뭔소린지 하나도 모르겠다. 썅) 나처럼 모르겠으면 기본값을 선택한뒤 [다음]을 누른다.

8. 다른 데이터베이스와의 구분을 위한 고유 명칭을 지정한다. Global Database Name은 데이터베이스 명을 의미하며, SID는 데이터베이스 인스턴스 명을 을 말한다. 우리가 주로 사용하는 단일 인스턴스환경 하에서는 두개의 이름을 같도록 오라클은 권장하고 있다. 8자 이내의 영문과 숫자를 사용할 수 있다. 전역 데이터베이스 이름란에 oracle(적당한 이름) 이라고 입력하고 다음을 누른다.(한 곳에만 입력하면 SID 부분까지 자동 입력된다.) [다음] 을 눌러 계속 진행한다.
 

09

8. 테이터베이스 파일 위치를 선택한다. 위의 설명대로 다른 디스크에 설치를 권장하지만, 이번은 연습이므로 C드라이브에 설치하도록 하겠다. 적당한 위치를 선택하더래도 파일디렉토리의 유형은 따라주는게 좋을것 같다.(예 D:\oracle\oradata) [다음]을 선택한다.

10

9. 데이베이스에 저장될 문자 집합을 선택하는 부분이다. 일반적으로는 기본 문자집합으로 선택된 KO16MSWIN949는 한글과 영문을 저장하여 사용하지만 중국어(간체)를 사용하기 위하여 유니코드(AL32UTF8)를 문자 집합으로 사용를 선택하고 [다음]을 누른다.(일반적인 경우에는 '기본 문자 집합 사용'을 선택한다)

11

10. 마지막으로 설치할 제품과 각종 관련 정보가 일목요연하게 표시된다. 잘못된 것이 있으면 [이전]을 눌러 수정하고, 그렇지 않다면 [설치]를 눌러 진행한다.

12

11. 여기부터 실제로 설치를 시작한다. 에러가 발생할 경우에는 위의 화면에서 표시되는 로그의 위치에서 파일을 연 다음 원인을 파악한다. 참고로 설치가 진행되는 동안에는 빠른 설치와 설치 중 에러를 방지하기 위해 컴퓨터의 다른 작업(웹 검색 및 다른 프로그램 실행 등)을 하지 않도록 해야 한다.

13

11-1. 설치도중 2번 CD를 넣어달라는 메시지가 나온다. [찾아보기] 버튼을 눌러 A99347-01 폴더를 선택하고 [확인] 버튼을 누른다.

14

11-2. 설치도중 3번 CD를 넣어달라는 메시지가 나온다. [찾아보기] 버튼을 눌러 A99348-01 폴더를 선택하고 [확인] 버튼을 누른다.

16

11-3. Oracle Database 기본 설치가 끝나면 구성 툴 화면이 나오는데, Oracle Net, DB 생성, Agent 등을 구성하게 된다. 모든 구성요소가 성공되어야 하겠지만, DBCA(Oracle Database Configuration Assistant) 즉 데이터베이스를 생성해 주는 구성 부분은 반드시 성공되어야 한다.

15

12. DB 생성을 모두 마치면 sys와 system 패스워드를 입력하는 창이 나온다. 적당한 암호를 입력하고 [확인]을 누른다.

17

13. 위의 6번과 마찬가지로 Windows 방화벽이 활성화 되어있다면, 다음과 같은 차단에 관련된 팝업 윈도우를 볼 수 있다. 앞으로 주력으로 사용할 프로그램이므로 당연히 [차단해제(U)] 버튼을 누른다.

18

14. 설치가 성공적으로 끝났다. [종료]버튼을 눌러서 설치프로그램을 종료한다.

19

14-1. 설치가 마무리 되고 다음과 같은 화면이 남아 있다면, 가만히 두면 사라진다.^^



Posted by 두억시니 트랙백 0 : 댓글 0