"오라클 테이블 정보"의 두 판 사이의 차이
DB CAFE
(새 문서: {| class="sortable" |- ! 테이블 !! 설명 |- |USER_OBJECTS(OBJ) || 모든 오브젝트에 대한 정보를 지원, 오브젝트 유형, 작성된시간,최종 DDL명령,ALTER, GR...) |
|||
1번째 줄: | 1번째 줄: | ||
− | + | # 테이블 명세 | |
− | + | #* (1) 테이블 정보 | |
− | + | <source lang=sql> | |
− | + | SELECT a.OBJECT_ID, a.OBJECT_TYPE, a.OBJECT_NAME | |
− | + | , TO_CHAR(CREATED, 'YYYY-MM-DD HH24:MI:SS') AS CREATED | |
− | + | , TO_CHAR(LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LAST_DDL_TIME | |
− | + | , b.COMMENTS | |
− | + | FROM SYS.USER_OBJECTS a | |
− | + | , SYS.USER_TAB_COMMENTS b | |
− | | | + | WHERE (a.OBJECT_TYPE = 'TABLE' OR a.OBJECT_TYPE = 'VIEW') |
− | | | + | AND a.OBJECT_NAME = b.TABLE_NAME |
− | + | ORDER BY a.OBJECT_TYPE ASC | |
− | + | , a.OBJECT_NAME ASC; | |
− | | | + | </source> |
− | | | + | |
− | | | + | # 테이블-컬럼 명세 |
− | | | + | #* (2) 테이블 컬럼 정보 보기 |
− | + | <source lang=sql> | |
− | + | SELECT a.OBJECT_ID, a.OBJECT_TYPE, a.OBJECT_NAME, b.COLUMN_ID, b.COLUMN_NAME | |
− | + | , b.DATA_TYPE | |
− | + | , (CASE b.DATA_TYPE WHEN 'NUMBER' THEN TO_CHAR(b.DATA_LENGTH) | |
− | + | WHEN 'DATE' THEN ' ' | |
− | + | ELSE TO_CHAR(b.DATA_LENGTH) END) AS DATA_LENGTH | |
− | + | , b.DATA_TYPE || (CASE b.DATA_TYPE WHEN 'NUMBER' THEN '(' || TO_CHAR(b.DATA_LENGTH) || ',' || TO_CHAR(b.DATA_PRECISION) || ')' | |
− | + | WHEN 'DATE' THEN ' ' | |
− | + | ELSE '(' || b.DATA_LENGTH || ')' | |
− | + | END ) DATA_TYPE_DESC | |
− | + | , DECODE(b.NULLABLE, 'N', 'Y', '') NOT_NULL | |
− | + | , b.DATA_DEFAULT, c.COMMENTS | |
− | + | FROM SYS.USER_OBJECTS a, SYS.USER_TAB_COLUMNS b, SYS.USER_COL_COMMENTS c | |
− | + | WHERE (a.OBJECT_TYPE = 'TABLE' OR a.OBJECT_TYPE = 'VIEW') | |
− | + | AND a.OBJECT_NAME = b.TABLE_NAME | |
− | + | AND b.TABLE_NAME= c.TABLE_NAME | |
− | + | AND b.COLUMN_NAME = c.COLUMN_NAME | |
− | + | ORDER BY a.OBJECT_TYPE ASC, a.OBJECT_NAME ASC, b.COLUMN_ID ASC; | |
− | + | </source> | |
− | + | ||
− | + | # 테이블-인덱스 명세 | |
− | + | #* (3) 테이블 인덱스 정보 보기 | |
− | + | <source lang=sql> | |
− | + | SELECT a.TABLE_NAME, a.INDEX_NAME, b.UNIQUENESS, a.COLUMN_POSITION, a.COLUMN_NAME, a.DESCEND | |
− | + | FROM SYS.USER_IND_COLUMNS a, SYS.USER_INDEXES b | |
− | + | WHERE a.INDEX_NAME = b.INDEX_NAME | |
− | + | ORDER BY a.TABLE_NAME ASC, a.INDEX_NAME ASC, a.COLUMN_POSITION ASC; | |
− | + | (4) 테이블 참조키 정보 보기 | |
− | + | SELECT a.CONSTRAINT_NAME "Foreign Key" | |
− | + | , a.CONSTRAINT_TYPE | |
− | + | , a.TABLE_NAME "Foreign Key TableName" | |
− | + | , (SELECT COLUMN_NAME FROM SYS.USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = a.CONSTRAINT_NAME) "Foreign | |
− | + | Key ColumnName" | |
− | + | , b.CONSTRAINT_NAME "Referenced Key" | |
− | + | , b.CONSTRAINT_TYPE | |
− | + | , b.TABLE_NAME "Referenced Key TableName" | |
− | + | , (SELECT COLUMN_NAME FROM SYS.USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = b.CONSTRAINT_NAME) | |
− | + | "Referenced Key ColumnName" | |
− | + | FROM SYS.USER_CONSTRAINTS a, SYS.USER_CONSTRAINTS b | |
− | + | WHERE a.CONSTRAINT_TYPE = 'R' AND a.R_CONSTRAINT_NAME = b.CONSTRAINT_NAME | |
− | + | ORDER BY a.CONSTRAINT_NAME ASC; | |
− | + | </source> | |
− | + | ||
− | + | (5) 테이블 제약조건 정보 보기 | |
+ | <source lang=sql> | ||
+ | SELECT CONSTRAINT_NAME | ||
+ | , CONSTRAINT_TYPE | ||
+ | , TABLE_NAME | ||
+ | FROM SYS.USER_CONSTRAINTS | ||
+ | ORDER BY CONSTRAINT_NAME ASC; | ||
+ | </source> | ||
+ | |||
+ | (6) 테이블 트리거 정보 보기 | ||
+ | <source lang=sql> | ||
+ | SELECT OBJECT_ID | ||
+ | , OBJECT_TYPE, OBJECT_NAME | ||
+ | , TO_CHAR(CREATED, 'YYYY-MM-DD HH24:MI:SS') AS CREATED | ||
+ | , TO_CHAR(LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LAST_DDL_TIME | ||
+ | FROM SYS.USER_OBJECTS | ||
+ | WHERE OBJECT_TYPE = 'TRIGGER' | ||
+ | ORDER BY OBJECT_NAME ASC; | ||
+ | </source> |
2019년 2월 21일 (목) 12:35 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
- 테이블 명세
- (1) 테이블 정보
SELECT a.OBJECT_ID, a.OBJECT_TYPE, a.OBJECT_NAME
, TO_CHAR(CREATED, 'YYYY-MM-DD HH24:MI:SS') AS CREATED
, TO_CHAR(LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LAST_DDL_TIME
, b.COMMENTS
FROM SYS.USER_OBJECTS a
, SYS.USER_TAB_COMMENTS b
WHERE (a.OBJECT_TYPE = 'TABLE' OR a.OBJECT_TYPE = 'VIEW')
AND a.OBJECT_NAME = b.TABLE_NAME
ORDER BY a.OBJECT_TYPE ASC
, a.OBJECT_NAME ASC;
- 테이블-컬럼 명세
- (2) 테이블 컬럼 정보 보기
SELECT a.OBJECT_ID, a.OBJECT_TYPE, a.OBJECT_NAME, b.COLUMN_ID, b.COLUMN_NAME
, b.DATA_TYPE
, (CASE b.DATA_TYPE WHEN 'NUMBER' THEN TO_CHAR(b.DATA_LENGTH)
WHEN 'DATE' THEN ' '
ELSE TO_CHAR(b.DATA_LENGTH) END) AS DATA_LENGTH
, b.DATA_TYPE || (CASE b.DATA_TYPE WHEN 'NUMBER' THEN '(' || TO_CHAR(b.DATA_LENGTH) || ',' || TO_CHAR(b.DATA_PRECISION) || ')'
WHEN 'DATE' THEN ' '
ELSE '(' || b.DATA_LENGTH || ')'
END ) DATA_TYPE_DESC
, DECODE(b.NULLABLE, 'N', 'Y', '') NOT_NULL
, b.DATA_DEFAULT, c.COMMENTS
FROM SYS.USER_OBJECTS a, SYS.USER_TAB_COLUMNS b, SYS.USER_COL_COMMENTS c
WHERE (a.OBJECT_TYPE = 'TABLE' OR a.OBJECT_TYPE = 'VIEW')
AND a.OBJECT_NAME = b.TABLE_NAME
AND b.TABLE_NAME= c.TABLE_NAME
AND b.COLUMN_NAME = c.COLUMN_NAME
ORDER BY a.OBJECT_TYPE ASC, a.OBJECT_NAME ASC, b.COLUMN_ID ASC;
- 테이블-인덱스 명세
- (3) 테이블 인덱스 정보 보기
SELECT a.TABLE_NAME, a.INDEX_NAME, b.UNIQUENESS, a.COLUMN_POSITION, a.COLUMN_NAME, a.DESCEND
FROM SYS.USER_IND_COLUMNS a, SYS.USER_INDEXES b
WHERE a.INDEX_NAME = b.INDEX_NAME
ORDER BY a.TABLE_NAME ASC, a.INDEX_NAME ASC, a.COLUMN_POSITION ASC;
(4) 테이블 참조키 정보 보기
SELECT a.CONSTRAINT_NAME "Foreign Key"
, a.CONSTRAINT_TYPE
, a.TABLE_NAME "Foreign Key TableName"
, (SELECT COLUMN_NAME FROM SYS.USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = a.CONSTRAINT_NAME) "Foreign
Key ColumnName"
, b.CONSTRAINT_NAME "Referenced Key"
, b.CONSTRAINT_TYPE
, b.TABLE_NAME "Referenced Key TableName"
, (SELECT COLUMN_NAME FROM SYS.USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = b.CONSTRAINT_NAME)
"Referenced Key ColumnName"
FROM SYS.USER_CONSTRAINTS a, SYS.USER_CONSTRAINTS b
WHERE a.CONSTRAINT_TYPE = 'R' AND a.R_CONSTRAINT_NAME = b.CONSTRAINT_NAME
ORDER BY a.CONSTRAINT_NAME ASC;
(5) 테이블 제약조건 정보 보기
SELECT CONSTRAINT_NAME
, CONSTRAINT_TYPE
, TABLE_NAME
FROM SYS.USER_CONSTRAINTS
ORDER BY CONSTRAINT_NAME ASC;
(6) 테이블 트리거 정보 보기
SELECT OBJECT_ID
, OBJECT_TYPE, OBJECT_NAME
, TO_CHAR(CREATED, 'YYYY-MM-DD HH24:MI:SS') AS CREATED
, TO_CHAR(LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LAST_DDL_TIME
FROM SYS.USER_OBJECTS
WHERE OBJECT_TYPE = 'TRIGGER'
ORDER BY OBJECT_NAME ASC;