"ORACLE 추출쿼리"의 두 판 사이의 차이
DB CAFE
(→테이블 추출2 - 코멘트 포함) |
|||
(사용자 3명의 중간 판 6개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
− | = 테이블 | + | == 테이블 생성 SQL 생성 == |
+ | <source lang=sql> | ||
SELECT TABLE_NAME Y | SELECT TABLE_NAME Y | ||
,0 X | ,0 X | ||
46번째 줄: | 47번째 줄: | ||
ORDER BY 1,2 | ORDER BY 1,2 | ||
; | ; | ||
+ | </source> | ||
− | + | = 테이블 추출2 - 코멘트 포함 = | |
− | + | <source lang=sql> | |
− | + | SELECT TABLE_NAME Y | |
− | + | , 0 X | |
− | SELECT | + | , 'CREATE TABLE ' ||RTRIM(TABLE_NAME) ||'(' |
− | + | FROM DBA_TABLES | |
− | + | WHERE OWNER = UPPER('GSSMADM') | |
− | FROM | + | UNION |
− | WHERE | + | SELECT TC.TABLE_NAME Y |
− | UNION | + | , COLUMN_ID X |
− | SELECT | + | , RTRIM(DECODE(COLUMN_ID,1,NULL,',')) |
− | + | || RTRIM(COLUMN_NAME)|| ' ' | |
− | |||
− | || 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 | + | FROM DBA_TAB_COLUMNS TC |
− | + | , DBA_OBJECTS O | |
− | WHERE | + | WHERE O.OWNER = TC.OWNER |
− | + | AND O.OBJECT_NAME = TC.TABLE_NAME | |
− | + | AND O.OBJECT_TYPE = 'TABLE' | |
− | + | AND O.OWNER = UPPER('GSSMADM') | |
− | UNION | + | UNION |
+ | |||
SELECT A.TABLE_NAME Y | 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 ' || ' | + | 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> | ||
+ | |||
+ | [[Category:oracle]] |
2020년 8월 10일 (월) 08:52 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
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');