행위

ORACLE 추출쿼리

DB CAFE

Dbcafe (토론 | 기여)님의 2020년 8월 10일 (월) 08:52 판 (테이블 추출2 - 코멘트 포함)
(차이) ← 이전 판 | 최신판 (차이) | 다음 판 → (차이)
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');