ORACLE 테이블 추출쿼리
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
테이블 추출[편집]
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
/**
* 테이블 추출 Query **/
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
/**
* 코멘트 주석 추출 **/
SELECT 'COMMENT ON COLUMN ' || 'korvans'||'.'||C.TABLE_NAME||'.'||COLUMN_NAME || ' IS ' || '|| COMMENTS ||' || ';'
FROM DBA_COL_COMMENTS C
WHERE OWNER = UPPER('$ownerName');