행위

ORACLE 테이블 추출쿼리

DB CAFE

thumb_up 추천메뉴 바로가기

1 테이블 추출 쿼리[편집]

1.1 테이블 생성[편집]

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 
;

1.2 테이블 + 코멘트 추출[편집]

SELECT  TABLE_NAME Y 
        ,0 X 
        ,'CREATE TABLE ' ||RTRIM(TABLE_NAME) ||'(' 
  FROM  DBA_TABLES 
 WHERE  OWNER = UPPER('$ownerName') 
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('$ownerName') 
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('$ownerName') 
 ORDER  BY 1,2 
;

1.3 코멘트 주석 추출[편집]

SELECT  'COMMENT ON COLUMN ' || 'korvans'||'.'||C.TABLE_NAME||'.'||COLUMN_NAME || ' IS ' || ''''|| COMMENTS ||'''' || ';' 
  FROM  DBA_COL_COMMENTS C 
 WHERE  OWNER  = UPPER('$ownerName');

1.4 테이블 DDL 추출 스크립트[편집]

SELECT DBMS_METADATA.GET_DDL('TABLE','TB_BOARD','OWNER') FROM DUAL;
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=ORACLE_테이블_추출쿼리&oldid=3463"