'오라클'에 해당되는 글 4건

정보, 통신, 기술/BI와 DB

오라클 스키마 조회 쿼리

오라클 데이터베이스 리뷰에 유용한 스키마 조회 쿼리입니다.

 

오라클.스키마조회.쿼리.txt

-----------------------------------------------------------
-- TABLE 조회
-----------------------------------------------------------
SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
  FROM ALL_TABLES
 WHERE OWNER = 'MYNAME';

SELECT *
  FROM ALL_OBJECTS
 WHERE OWNER = 'MYNAME';

SELECT SUBSTR(TABLE_NAME, 1, 2) AS AREA1
     , SUBSTR(TABLE_NAME, 4, 1) AS AREA2
     , SUBSTR(TABLE_NAME, 4, 3) AS AREA3
     , TABLE_NAME
     , COMMENTS
  FROM ALL_TAB_COMMENTS
 WHERE OWNER = 'MYNAME'
   AND TABLE_NAME LIKE 'D%';

-----------------------------------------------------------
-- COLUMN 조회
-----------------------------------------------------------
SELECT OWNER, TABLE_NAME, COLUMN_ID, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE
  FROM ALL_TAB_COLUMNS
 WHERE OWNER = 'MYNAME'
   AND TABLE_NAME LIKE 'D%';

SELECT *
  FROM ALL_COL_COMMENTS
 WHERE OWNER = 'MYNAME'
   AND TABLE_NAME LIKE 'D%';

SELECT A.OWNER, A.TABLE_NAME, A.COLUMN_NAME, B.COMMENTS, A.DATA_TYPE, A.DATA_LENGTH
  FROM ALL_TAB_COLUMNS A,
       ALL_COL_COMMENTS B
 WHERE A.OWNER = 'MYNAME'
   AND A.TABLE_NAME LIKE 'D%'
   AND A.TABLE_NAME = B.TABLE_NAME
   AND A.COLUMN_NAME = B.COLUMN_NAME;

-----------------------------------------------------------
-- COLUMN NAME 기준 COMMENTS 중복 조회
-----------------------------------------------------------
SELECT *
  FROM ALL_COL_COMMENTS
 WHERE OWNER = 'MYNAME'
   AND TABLE_NAME LIKE 'D%'
   AND COLUMN_NAME IN (SELECT COLUMN_NAME
                         FROM (SELECT COLUMN_NAME, SUM(CNT) AS CNT
                                 FROM (SELECT DISTINCT A.COLUMN_NAME, B.COMMENTS, 1 AS CNT
                                         FROM ALL_TAB_COLUMNS A,
                                              ALL_COL_COMMENTS B
                                        WHERE A.OWNER = 'MYNAME'
                                          AND A.TABLE_NAME LIKE 'D%'
                                          AND A.TABLE_NAME = B.TABLE_NAME
                                          AND A.COLUMN_NAME = B.COLUMN_NAME
                                          AND B.COMMENTS IS NOT NULL
                                      )
                                GROUP BY COLUMN_NAME
                              ) WHERE CNT > 1
                      );

-----------------------------------------------------------
-- COLUMN COMMENTS 기준 NAME 중복 조회
-----------------------------------------------------------
SELECT DISTINCT COLUMN_NAME, COMMENTS
  FROM ALL_COL_COMMENTS
 WHERE OWNER = 'MYNAME'
   AND TABLE_NAME LIKE 'D%'
   AND COMMENTS IN (SELECT COMMENTS
                      FROM (SELECT COMMENTS, SUM(CNT) AS CNT
                              FROM (SELECT DISTINCT A.COLUMN_NAME, B.COMMENTS, 1 AS CNT
                                      FROM ALL_TAB_COLUMNS A,
                                           ALL_COL_COMMENTS B
                                     WHERE A.OWNER = 'BIS_MGR'
                                       AND A.TABLE_NAME LIKE 'D%'
                                       AND A.TABLE_NAME = B.TABLE_NAME
                                       AND A.COLUMN_NAME = B.COLUMN_NAME
                                       AND B.COMMENTS IS NOT NULL
                                   )
                             GROUP BY COMMENTS
                           ) WHERE CNT > 1
                      );

-----------------------------------------------------------
-- COLUMN 계획 데이터 필드 조회
-----------------------------------------------------------
SELECT A.OWNER, A.TABLE_NAME, B.COMMENTS, A.COMMENTS
  FROM ALL_COL_COMMENTS A, ALL_TAB_COMMENTS B
 WHERE A.OWNER = 'MYNAME'
   AND A.TABLE_NAME LIKE 'D%'
   AND A.TABLE_NAME = B.TABLE_NAME
   AND (A.COLUMN_NAME LIKE '%PLAN%' OR A.COLUMN_NAME LIKE '%PLN%');

정보, 통신, 기술/BI와 DB

오라클 스키마 조회 퀴리

-- TABLE
SELECT A.NAME, B.COMMENT$
  FROM OBJ$ A, COM$ B
 WHERE A.NAME LIKE 'BEMO%' AND TYPE# = 2
   AND B.COL#(+) IS NULL
   AND A.OBJ# = B.OBJ#(+);

-- VIEW
SELECT A.NAME, B.COMMENT$
  FROM OBJ$ A, COM$ B
 WHERE A.NAME LIKE 'BEMO%' AND TYPE# = 4
   AND B.COL#(+) IS NULL
   AND A.OBJ# = B.OBJ#(+);

-- COLUMN
SELECT O.NAME, C.NAME, CO.COMMENT$
  FROM SYS.OBJ$ O, SYS.COL$ C, SYS.COM$ CO
 WHERE O.NAME LIKE 'BEMO%'
   AND O.TYPE# IN (2, 4)
   AND O.OBJ# = C.OBJ#
   AND C.OBJ# = CO.OBJ#(+)
   AND C.INTCOL# = CO.COL#(+)
   AND BITAND(C.PROPERTY, 32) = 0;
 

'정보, 통신, 기술 > BI와 DB' 카테고리의 다른 글

[링크] SSAS Custom Rollup  (0) 2011.04.21
DMV를 이용해 SSAS 조회하기  (0) 2011.04.07
오라클 스키마 조회 퀴리  (0) 2011.03.28
OPEN BI  (0) 2011.02.25
[불펌] MSSQL 2005 AS Data Mining  (0) 2010.10.08
MSSQL 테이블, 뷰 스키마 정보 조회  (0) 2010.09.16

정보, 통신, 기술/BI와 DB

[오라클] CTE를 이용한 데이터 수정

전에 작성한 [오라클] CTE를 이용한 데이터 입력 2탄, CTE를 이용한 데이터 수정입니다.

CREATE TABLE MYT (ID INT, NAME VARCHAR(100), INDATE DATE);

INSERT INTO MYT VALUES (1, '김', SYSDATE);
INSERT INTO MYT VALUES (2, '이', SYSDATE);
INSERT INTO MYT VALUES (3, '박', SYSDATE);

SELECT * FROM MYT;

UPDATE MYT A
   SET NAME = NVL((WITH TAB AS (SELECT 1 ID, '최' NAME FROM DUAL
                                 UNION
                                SELECT 2 ID, '황' NAME FROM DUAL)
                   SELECT NAME
                     FROM TAB B
                    WHERE A.ID = B.ID)
                  , A.NAME)
    , INDATE = SYSDATE
;

DROP TABLE MYT;

약간의 문제가 있습니다.

1) MYT의 일부 행만을 수정하려면 아래쪽 WHERE에 새로운 CTE를 다시 작성해야 합니다.
위 쿼리문도 INDATE가 전부다 수정이 됩니다. "3-박"이 수정되지 않은 이유는 NVL을 적절하게 활용하고 있기 때문이죠.
SET 우변의 CTE는 WHERE 구문에 사용할 수가 없네요.

2) 컬럼들을 ()로 묶어서 한 서브쿼리로 여러 컬럼을 한번에 수정하는 쿼리는 안 됩니다.
아래와 같은 경우 말이죠. 사실 이건 되는 오라클이 대단한거죠!!

UPDATE TABLE SET (C1, C2) = (SELECT C1, C2 FROM TABLE2)

정보, 통신, 기술/BI와 DB

[오라클] CTE를 이용한 데이터 입력

사실 너무 간단한 거지만, 될지 안될지 좀 고민했던 거라... 뭘 고민했단 건지ㅡㅡ;;
그냥 코드 나갑니다~~ ㅎㅎ

CREATE TABLE MYT2 (NAM VARCHAR2(10));

SELECT * FROM MYT2;

INSERT INTO MYT2
WITH S1 AS (SELECT 'TEST2' FROM DUAL UNION SELECT 'TEST3' FROM DUAL)
SELECT * FROM S1;

SELECT * FROM MYT2; -- 성공!!

DROP TABLE MYT2; 

  [ 1 ]  

카운터

Today : 6
Yesterday : 45
Total : 347,810

Site

Copyright (c) 2016 최윤호. All Rights Reserved.
Powered by Tistory. Skin by wallel.
Subscribe Rss Feed