행위

"ORACLE 추출쿼리"의 두 판 사이의 차이

DB CAFE

(새 문서: 이럴때 꼭 알아야 하는것이 DB사전입니다. DBA 가 되실라면 이중에 많이 쓰는것은 외우고 있죠 아마...^^ //----------------------------------------...)
 
(→‎테이블 추출2 - 코멘트 포함)
 
(사용자 3명의 중간 판 7개는 보이지 않습니다)
1번째 줄: 1번째 줄:
−
이럴때 꼭 알아야 하는것이 DB사전입니다. DBA 가 되실라면 이중에 많이 쓰는것은 외우고 있죠 아마...^^
+
== 테이블 생성 SQL 생성 ==
−
 
 
−
//----------------------------------------
 
  
 +
<source lang=sql>
 
SELECT  TABLE_NAME Y  
 
SELECT  TABLE_NAME Y  
 
         ,0 X  
 
         ,0 X  
48번째 줄: 47번째 줄:
 
  ORDER  BY 1,2  
 
  ORDER  BY 1,2  
 
;  
 
;  
 +
</source>
  
−
/******************************************************************************* 사용예
+
= 테이블 추출2 - 코멘트 포함 =  
−
 
+
<source lang=sql>
−
/**
+
SELECT TABLE_NAME Y
−
* 테이블 추출 Query  
+
    , 0 X  
−
**/
+
    , 'CREATE TABLE ' ||RTRIM(TABLE_NAME) ||'('
−
SELECT TABLE_NAME Y  
+
   FROM DBA_TABLES
−
        ,0 X  
+
  WHERE OWNER = UPPER('GSSMADM')  
−
        ,'CREATE TABLE ' ||RTRIM(TABLE_NAME) ||'('  
+
UNION
−
   FROM DBA_TABLES  
+
SELECT TC.TABLE_NAME Y  
−
  WHERE OWNER = UPPER('$ownerName')  
+
    , COLUMN_ID X  
−
UNION  
+
    , RTRIM(DECODE(COLUMN_ID,1,NULL,','))  
−
SELECT TC.TABLE_NAME Y  
+
         || RTRIM(COLUMN_NAME)|| ' '
−
        ,COLUMN_ID X  
 
−
        ,RTRIM(DECODE(COLUMN_ID,1,NULL,','))  
 
−
         || RTRIM(COLUMN_NAME)|| ' '  
 
 
         || RTRIM(DATA_TYPE)  
 
         || RTRIM(DATA_TYPE)  
 
         || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'LONG',NULL,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,'('),'('))  
 
         || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'LONG',NULL,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,'('),'('))  
 
         || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'CHAR',DATA_LENGTH,'VARCHAR2',DATA_LENGTH,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,TO_CHAR(DATA_PRECISION)
 
         || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'CHAR',DATA_LENGTH,'VARCHAR2',DATA_LENGTH,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,TO_CHAR(DATA_PRECISION)
−
         || ','  
+
         || ','
 
         || TO_CHAR(DATA_SCALE)),'LONG',NULL,'******ERROR'))  
 
         || TO_CHAR(DATA_SCALE)),'LONG',NULL,'******ERROR'))  
 
         || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'LONG',NULL,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,')'),')'))  
 
         || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'LONG',NULL,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,')'),')'))  
−
         || ' '  
+
         || ' '
 
         || RTRIM(DECODE(NULLABLE,'N','NOT NULL',NULL))  
 
         || RTRIM(DECODE(NULLABLE,'N','NOT NULL',NULL))  
−
   FROM DBA_TAB_COLUMNS TC  
+
   FROM DBA_TAB_COLUMNS TC  
−
        ,DBA_OBJECTS O  
+
    , DBA_OBJECTS O  
−
  WHERE O.OWNER = TC.OWNER  
+
  WHERE O.OWNER = TC.OWNER  
−
  AND O.OBJECT_NAME = TC.TABLE_NAME  
+
  AND O.OBJECT_NAME = TC.TABLE_NAME  
−
  AND O.OBJECT_TYPE = 'TABLE'  
+
  AND O.OBJECT_TYPE = 'TABLE'
−
  AND O.OWNER = UPPER('$ownerName')  
+
  AND O.OWNER = UPPER('GSSMADM')  
−
UNION  
+
UNION
 +
 
 
SELECT A.TABLE_NAME Y  
 
SELECT A.TABLE_NAME Y  
−
      ,999999 X  
+
    , 999999 X  
−
      ,')' || CHR(10)  
+
    , ')' || CHR(10)
−
            -- ||' STORAGE(' || CHR(10)  
+
      -- ||' STORAGE(' || CHR(10)  
−
            -- ||' INITIAL ' || INITIAL_EXTENT || CHR(10)  
+
        -- ||' INITIAL ' || INITIAL_EXTENT || CHR(10)  
−
            -- ||' NEXT ' || NEXT_EXTENT || CHR(10)  
+
        -- ||' NEXT ' || NEXT_EXTENT || CHR(10)  
−
            -- ||' MINEXTENTS ' || MIN_EXTENTS || CHR(10)  
+
        -- ||' MINEXTENTS ' || MIN_EXTENTS || CHR(10)  
−
            -- ||' MAXEXTENTS ' || MAX_EXTENTS || CHR(10)  
+
        -- ||' MAXEXTENTS ' || MAX_EXTENTS || CHR(10)  
−
            -- ||' PCTINCREASE '|| PCT_INCREASE || ')' ||CHR(10)  
+
        -- ||' PCTINCREASE '|| PCT_INCREASE || ')' ||CHR(10)  
−
            -- ||' INITRANS ' || INI_TRANS || CHR(10)  
+
        -- ||' INITRANS ' || INI_TRANS || CHR(10)  
−
            -- ||' MAXTRANS ' || MAX_TRANS || CHR(10)  
+
        -- ||' MAXTRANS ' || MAX_TRANS || CHR(10)  
−
            -- ||' PCTFREE ' || PCT_FREE || CHR(10)  
+
        -- ||' PCTFREE ' || PCT_FREE || CHR(10)  
−
            -- ||' PCTUSED ' || PCT_USED || CHR(10)  
+
        -- ||' PCTUSED ' || PCT_USED || CHR(10)  
−
            -- ||' PARALLEL (DEGREE ' || DEGREE || ') ' || CHR(10)  
+
        -- ||' PARALLEL (DEGREE ' || DEGREE || ') ' || CHR(10)  
−
            -- ||' TABLESPACE ' || RTRIM(TABLESPACE_NAME) ||CHR(10)  
+
        -- ||' TABLESPACE ' || RTRIM(TABLESPACE_NAME) ||CHR(10)  
−
            ||';'||CHR(10)||CHR(10)  
+
        ||';'||CHR(10)||CHR(10)  
−
            || 'COMMENT ON TABLE ' || A.TABLE_NAME || ' IS '||' '''|| B.COMMENTS ||''''  
+
        || 'COMMENT ON TABLE ' || A.TABLE_NAME || ' IS '||' '''|| B.COMMENTS ||''''
−
            ||';'||CHR(10)||CHR(10)  
+
        ||';'||CHR(10)||CHR(10)
−
  FROM DBA_TABLES A  
+
       
−
    , DBA_TAB_COMMENTS B  
+
        FROM DBA_TABLES A
−
WHERE A.TABLE_NAME   = B.TABLE_NAME  
+
      , DBA_TAB_COMMENTS B
−
  AND A.OWNER       = B.OWNER  
+
  WHERE A.TABLE_NAME=B.TABLE_NAME
−
  AND A.OWNER       = UPPER('$ownerName')  
+
    AND A.OWNER=B.OWNER  
−
ORDER BY 1,2  
+
    AND A.OWNER=UPPER('GSSMADM')  
 +
  ORDER BY 1,2
 
;  
 
;  
−
 
  
 
/**  
 
/**  
−
  *  필드 주석 추출   
+
  *  코멘트 주석 추출   
 
  **/  
 
  **/  
−
SELECT  'COMMENT ON COLUMN ' || 'korvans'||'.'||C.TABLE_NAME||'.'||COLUMN_NAME || ' IS ' || ''''|| COMMENTS ||'''' || ';'  
+
SELECT  'COMMENT ON COLUMN ' || '유저'||'.'||C.TABLE_NAME||'.'||COLUMN_NAME || ' IS ' || ''''|| COMMENTS ||'''' || ';'  
 
   FROM  DBA_COL_COMMENTS C  
 
   FROM  DBA_COL_COMMENTS C  
−
  WHERE  OWNER  = UPPER('$ownerName');  
+
  WHERE  OWNER  = UPPER('$ownerName');
−
 
+
</source>
−
/**
 
−
* 프로시져 추출
 
−
*/
 
−
select  OWNER
 
−
    ,  NAME
 
−
    ,  TYPE
 
−
    ,  LINE
 
−
    ,  decode(line,1,'CREATE OR REPLACE '
 
−
        ||replace(text,'PACKAGE BODY ','PACKAGE BODY '
 
−
        ||owner
 
−
        ||'.'),text) AS PKG_SOURCE
 
−
from dba_source where type='PROCEDURE' and owner='$ownerName';
 
  
−
/**
+
[[Category:oracle]]
−
* 펑션 추출 
 
−
**/
 
−
select  OWNER
 
−
    ,  NAME
 
−
    ,  TYPE
 
−
    ,  LINE
 
−
    ,  decode(line,1,'CREATE OR REPLACE '
 
−
        ||replace(text,'PACKAGE BODY ','PACKAGE BODY '
 
−
        ||owner
 
−
        ||'.'),text) AS PKG_SOURCE
 
−
from dba_source where type='FUNCTION' and owner='$ownerName';
 
−
 
 
−
/**
 
−
* 트리거 추출 
 
−
**/
 
−
select  OWNER
 
−
    ,  NAME
 
−
    ,  TYPE
 
−
    ,  LINE
 
−
    ,  decode(line,1,'CREATE OR REPLACE '
 
−
        ||replace(text,'PACKAGE BODY ','PACKAGE BODY '
 
−
        ||owner
 
−
        ||'.'),text) AS PKG_SOURCE
 
−
from dba_source where type='TRIGGER' and owner='$ownerName';
 

2020년 8월 10일 (월) 08:52 기준 최신판

thumb_up 추천메뉴 바로가기

1 테이블 생성 SQL 생성[편집]

SELECT  TABLE_NAME Y 
        ,0 X 
        ,'CREATE TABLE ' ||RTRIM(TABLE_NAME) ||'(' 
  FROM  DBA_TABLES 
 WHERE  OWNER = UPPER('사용자명') 
UNION 
SELECT  TC.TABLE_NAME Y 
        ,COLUMN_ID X 
        ,RTRIM(DECODE(COLUMN_ID,1,NULL,',')) 
        || RTRIM(COLUMN_NAME)|| ' ' 
        || RTRIM(DATA_TYPE) 
        || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'LONG',NULL,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,'('),'(')) 
        || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'CHAR',DATA_LENGTH,'VARCHAR2',DATA_LENGTH,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,TO_CHAR(DATA_PRECISION)
        || ',' 
        || TO_CHAR(DATA_SCALE)),'LONG',NULL,'******ERROR')) 
        || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'LONG',NULL,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,')'),')')) 
        || ' ' 
        || RTRIM(DECODE(NULLABLE,'N','NOT NULL',NULL)) 
  FROM  DBA_TAB_COLUMNS TC 
        ,DBA_OBJECTS O 
 WHERE  O.OWNER = TC.OWNER 
  AND  O.OBJECT_NAME = TC.TABLE_NAME 
  AND O.OBJECT_TYPE = 'TABLE' 
  AND O.OWNER = UPPER('사용자명') 
UNION 
SELECT TABLE_NAME Y 
      ,999999 X 
      ,')' || CHR(10) 
            -- ||' STORAGE(' || CHR(10) 
            -- ||' INITIAL ' || INITIAL_EXTENT || CHR(10) 
            -- ||' NEXT ' || NEXT_EXTENT || CHR(10) 
            -- ||' MINEXTENTS ' || MIN_EXTENTS || CHR(10) 
            -- ||' MAXEXTENTS ' || MAX_EXTENTS || CHR(10) 
            -- ||' PCTINCREASE '|| PCT_INCREASE || ')' ||CHR(10) 
            -- ||' INITRANS ' || INI_TRANS || CHR(10) 
            -- ||' MAXTRANS ' || MAX_TRANS || CHR(10) 
            -- ||' PCTFREE ' || PCT_FREE || CHR(10) 
            -- ||' PCTUSED ' || PCT_USED || CHR(10) 
            -- ||' PARALLEL (DEGREE ' || DEGREE || ') ' || CHR(10) 
            -- ||' TABLESPACE ' || RTRIM(TABLESPACE_NAME) ||CHR(10) 
            ||';'||CHR(10)||CHR(10) 
  FROM  DBA_TABLES 
 WHERE  OWNER = UPPER('사용자명') 
 ORDER  BY 1,2 
;

2 테이블 추출2 - 코멘트 포함[편집]

SELECT TABLE_NAME Y
     , 0 X 
     , 'CREATE TABLE ' ||RTRIM(TABLE_NAME) ||'('
  FROM DBA_TABLES
 WHERE OWNER = UPPER('GSSMADM') 
UNION
SELECT TC.TABLE_NAME Y 
     , COLUMN_ID X 
     , RTRIM(DECODE(COLUMN_ID,1,NULL,',')) 
        || RTRIM(COLUMN_NAME)|| ' '
        || RTRIM(DATA_TYPE) 
        || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'LONG',NULL,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,'('),'(')) 
        || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'CHAR',DATA_LENGTH,'VARCHAR2',DATA_LENGTH,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,TO_CHAR(DATA_PRECISION)
        || ','
        || TO_CHAR(DATA_SCALE)),'LONG',NULL,'******ERROR')) 
        || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'LONG',NULL,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,')'),')')) 
        || ' '
        || RTRIM(DECODE(NULLABLE,'N','NOT NULL',NULL)) 
  FROM DBA_TAB_COLUMNS TC 
     , DBA_OBJECTS O 
 WHERE O.OWNER = TC.OWNER 
   AND O.OBJECT_NAME = TC.TABLE_NAME 
   AND O.OBJECT_TYPE = 'TABLE'
   AND O.OWNER = UPPER('GSSMADM') 
UNION

SELECT A.TABLE_NAME Y 
     , 999999 X 
     , ')' || CHR(10)
       -- ||' STORAGE(' || CHR(10) 
        -- ||' INITIAL ' || INITIAL_EXTENT || CHR(10) 
        -- ||' NEXT ' || NEXT_EXTENT || CHR(10) 
        -- ||' MINEXTENTS ' || MIN_EXTENTS || CHR(10) 
        -- ||' MAXEXTENTS ' || MAX_EXTENTS || CHR(10) 
        -- ||' PCTINCREASE '|| PCT_INCREASE || ')' ||CHR(10) 
        -- ||' INITRANS ' || INI_TRANS || CHR(10) 
        -- ||' MAXTRANS ' || MAX_TRANS || CHR(10) 
        -- ||' PCTFREE ' || PCT_FREE || CHR(10) 
        -- ||' PCTUSED ' || PCT_USED || CHR(10) 
        -- ||' PARALLEL (DEGREE ' || DEGREE || ') ' || CHR(10) 
        -- ||' TABLESPACE ' || RTRIM(TABLESPACE_NAME) ||CHR(10) 
        ||';'||CHR(10)||CHR(10) 
        || 'COMMENT ON TABLE ' || A.TABLE_NAME || ' IS '||' '''|| B.COMMENTS ||''''
        ||';'||CHR(10)||CHR(10)
         
        FROM DBA_TABLES A
      , DBA_TAB_COMMENTS B
  WHERE A.TABLE_NAME=B.TABLE_NAME
    AND A.OWNER=B.OWNER 
    AND A.OWNER=UPPER('GSSMADM') 
  ORDER BY 1,2
; 

/** 
 *  코멘트 주석 추출  
 **/ 
SELECT  'COMMENT ON COLUMN ' || '유저'||'.'||C.TABLE_NAME||'.'||COLUMN_NAME || ' IS ' || ''''|| COMMENTS ||'''' || ';' 
  FROM  DBA_COL_COMMENTS C 
 WHERE  OWNER  = UPPER('$ownerName');
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=ORACLE_추출쿼리&oldid=4485"