ORACLE 테이블 컬럼
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 테이블 기본정보[편집]
1.1 CONSTRAINT 보기[편집]
SELECT DECODE(A.CONSTRAINT_TYPE, 'P', 'Primary Key', 'R', 'Foreign Key', 'C', 'Table Check', 'V', 'View Check', 'U', 'Unique', '?') AS "유형"
, SUBSTRB(A.CONSTRAINT_NAME, 1, 25) AS CONSTRAINT_NAME
, B.POSITION
, SUBSTRB(B.COLUMN_NAME, 1, 25) AS COLUMN_NAME
FROM DBA_CONSTRAINTS A
, DBA_CONS_COLUMNS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.OWNER = 'E_LUCIS'
AND A.TABLE_NAME = UPPER('&테이블명')
ORDER BY 1, 2, 3;
1.2 특정 테이블의 스키마 구조 확인[편집]
/*
보통 토드나 기타 오라클 클라이언트 툴을 이용해서 테이블 구조를 확인 해도 됩니다.
하지만 수많은 테이블을 전체 보고 싶을 경우 아래 쿼리를 이용하면 한번에 확인이 가능합니다.
*/
--: 관리자용
SELECT OWNER
, TABLE_NAME
, COLUMN_NAME
, PK
, COLUMN_NAME
, DATA_TYPE || '( ' || NVL(DATA_TYPE_2, DATA_LENGTH) || ' )' DATA_TYPE
, NULLABLE
, COMMENTS
FROM (SELECT A.OWNER
, A.TABLE_NAME
, A.COLUMN_ID
, B.POSITION PK
, A.COLUMN_NAME
, A.DATA_TYPE
, A.DATA_PRECISION || DECODE(A.DATA_SCALE, NULL, NULL, ',' || A.DATA_SCALE) DATA_TYPE_2
, A.DATA_LENGTH
, A.DATA_PRECISION
, A.DATA_SCALE
, A.NULLABLE
, A.COMMENTS
, ROW_NUMBER() OVER (PARTITION BY A.OWNER, A.TABLE_NAME, A.COLUMN_ID ORDER BY A.COLUMN_ID, B.POSITION) RN
FROM (SELECT COL.OWNER
, COL.TABLE_NAME
, COL.COLUMN_ID
, COL.COLUMN_NAME
, COL.DATA_TYPE
, COL.DATA_LENGTH
, COL.DATA_PRECISION
, COL.DATA_SCALE
, COL.NULLABLE
, COM.COMMENTS
FROM DBA_TAB_COLUMNS COL
, DBA_COL_COMMENTS COM
WHERE COL.COLUMN_NAME = COM.COLUMN_NAME
AND COL.OWNER = COM.OWNER
AND COL.TABLE_NAME = COM.TABLE_NAME
AND COM.OWNER =:IN_OWNER
AND COM.TABLE_NAME LIKE:IN_TABLE_NAME || '%') A
, DBA_CONS_COLUMNS B
WHERE B.TABLE_NAME(+) = A.TABLE_NAME
AND B.COLUMN_NAME(+) = A.COLUMN_NAME) X
WHERE X.RN = 1 ORDER BY X.TABLE_NAME, X.COLUMN_ID;
--: 일반 사용자 용
SELECT TABLE_NAME , COLUMN_NAME , PK , COLUMN_NAME , DATA_TYPE || '( ' || NVL(DATA_TYPE_2, DATA_LENGTH) || ' )' DATA_TYPE , NULLABLE , COMMENTS
FROM (SELECT A.TABLE_NAME , A.COLUMN_ID , B.POSITION PK , A.COLUMN_NAME , A.DATA_TYPE
, A.DATA_PRECISION || DECODE(A.DATA_SCALE, NULL, NULL, ',' || A.DATA_SCALE) DATA_TYPE_2
, A.DATA_LENGTH , A.DATA_PRECISION , A.DATA_SCALE , A.NULLABLE , A.COMMENTS
, ROW_NUMBER() OVER (PARTITION BY A.TABLE_NAME, A.COLUMN_ID
ORDER BY A.COLUMN_ID, B.POSITION) RN
FROM (SELECT COL.TABLE_NAME , COL.COLUMN_ID , COL.COLUMN_NAME , COL.DATA_TYPE , COL.DATA_LENGTH
, COL.DATA_PRECISION , COL.DATA_SCALE , COL.NULLABLE , COM.COMMENTS
FROM USER_TAB_COLUMNS COL
, USER_COL_COMMENTS COM
WHERE COL.COLUMN_NAME = COM.COLUMN_NAME
AND COL.TABLE_NAME = COM.TABLE_NAME
AND COM.TABLE_NAME LIKE:IN_TABLE_NAME || '%') A
, USER_CONS_COLUMNS B
WHERE B.TABLE_NAME(+) = A.TABLE_NAME AND B.COLUMN_NAME(+) = A.COLUMN_NAME
) X
WHERE X.RN = 1 ORDER BY X.TABLE_NAME, X.COLUMN_ID;
1.3 1.테이블 생성일자 보기[편집]
SELECT SUBSTRB(OBJECT_NAME, 1, 15) AS OBJECT_NAME
, CREATED
, LAST_DDL_TIME
, TIMESTAMP
, STATUS
FROM USER_OBJECTS
WHERE OBJECT_NAME = UPPER('&테이블명')
AND OBJECT_TYPE = 'TABLE';
2 테이블 사이즈 정보[편집]
2.1 테이블 용량 산정[편집]
데이터 용량 산정 방법: (컬럼 평균사이즈*로우스*(1 + 0.2(헤더값감안))) + 인덱스 사이즈
- 컬럼평균사이즈 = ROW 행에 대한 사이즈 평균 (LENGTHB 를 통해 구해도 됨)
- 인덱스별로 + 18바이트 (rowid) + a
2.2 테이블의 크기 및 블록 보기[편집]
SELECT SUBSTR(SEGMENT_NAME, 1, 20), BYTES, BLOCKS
FROM USER_SEGMENTS
WHERE SEGMENT_NAME = UPPER('&테이블명');
2.3 테이블 사이즈 조사[편집]
SELECT A.OWNER
, A.SEGMENT_NAME
, A.SEGMENT_TYPE
, ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB"
FROM DBA_SEGMENTS A
, DBA_TABLES B
WHERE A.SEGMENT_NAME = B.TABLE_NAME
AND A.SEGMENT_TYPE IN ('TABLE','TABLE PARTITION')
AND A.OWNER = B.OWNER
-- AND A.OWNER = '유저아이디'
AND EXISTS (SELECT 1
FROM MIG_TABLES C
WHERE C.TABLE_NAME = B.TABLE_NAME
AND C.OWNER = B.OWNER
AND C.SYSTEM_CODE = 'LC1'
AND C.USE_YN = 'Y'
)
GROUP BY A.OWNER
, A.SEGMENT_NAME
, A.SEGMENT_TYPE
;
2.4 테이블별(인덱스+LOB+테이블) 사이즈[편집]
SELECT owner, table_name, TRUNC (SUM (bytes) / 1024 / 1024 / 1024) GB
FROM (SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION')
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type IN ('INDEX', 'INDEX PARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX')
---WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM (bytes) / 1024 / 1024 > 10
/* Ignore really small tables */
ORDER BY SUM (bytes) DESC
2.4.1 테이블 사이즈 1기가 이상[편집]
SELECT segment_name, segment_type, SUM(bytes)/1024/1024/1024 GBytes
FROM dba_segments
WHERE OWNER IN ('OYSTDBA','OYHQDBA') -- schema owners
AND segment_type IN ('TABLE')
GROUP BY segment_name, segment_type
HAVING SUM(bytes)/1024/1024/1024 > 1;
2.5 사용자별 오브젝트 수[편집]
SELECT OWNER AS "OWNER"
, SUM(DECODE(OBJECT_TYPE, 'TABLE', 1, 0)) AS "TABLE"
, SUM(DECODE(OBJECT_TYPE, 'INDEX', 1, 0)) AS "INDEX"
, SUM(DECODE(OBJECT_TYPE, 'SYNONYM', 1, 0)) AS "SYNONYMS"
, SUM(DECODE(OBJECT_TYPE, 'SEQUENCE', 1, 0)) AS "SEQUENCES"
, SUM(DECODE(OBJECT_TYPE, 'VIEW', 1, 0)) AS "VIEWS"
, SUM(DECODE(OBJECT_TYPE, 'CLUSTER', 1, 0)) AS "CLUSTERS"
, SUM(DECODE(OBJECT_TYPE, 'DATABASE LINK', 1, 0)) AS "DBLINKS"
, SUM(DECODE(OBJECT_TYPE, 'PACKAGE', 1, 0)) AS "PACKAGES"
, SUM(DECODE(OBJECT_TYPE, 'PACKAGE BODY', 1, 0)) AS "PACKAGE_BODY"
, SUM(DECODE(OBJECT_TYPE, 'PROCEDURE', 1, 0)) AS "PROCEDURES"
, SUM(DECODE(OBJECT_TYPE, 'FUNCTION', 1, 0)) AS "FUNCTION"
FROM DBA_OBJECTS GROUP BY OWNER;
2.6 상호 DB간에 컬럼 이름 비교[편집]
/*
양쪽 DB에서 사용하는 테이블 중에서 컬럼 이름 다른 항목을 찾는다.
*/
SELECT A.TABLE_NAME
, A.COLUMN_NAME
, A.COLUMN_ID
, A.DATA_TYPE || '(' || A.DATA_LENGTH || ')' DATA_TYPE
FROM USER_TAB_COLUMNS@LINK_ESTDB A WHERE A.TABLE_NAME =:IN_TABLE_NAME AND NOT EXISTS
(SELECT 'X'
FROM USER_TAB_COLUMNS B
WHERE B.TABLE_NAME = A.TABLE_NAME
AND B.COLUMN_NAME = A.COLUMN_NAME);
2.7 테이블 간의 비교[편집]
SELECT A.TABLE_NAME, A.COLUMN_NAME, A.COLUMN_ID
FROM USER_TAB_COLUMNS@LINK_ESTDB A
WHERE A.TABLE_NAME =:IN_TABLE_NAME
MINUS
SELECT A.TABLE_NAME, A.COLUMN_NAME, A.COLUMN_ID
FROM USER_TAB_COLUMNS A
WHERE A.TABLE_NAME =:IN_TABLE_NAME
2.8 파티션 테이블 쿼리 추출 자동생성 스크립트[편집]
-- MIG_ADM.MIG_TABLES 은 사용자가 만든 임시테이블임
SELECT 'SELECT dbms_metadata.get_ddl(''TABLE'','''||A.TABLE_NAME||''','''||A.OWNER||''') DDL_QUERY from dual;'
FROM MIG_ADM.MIG_TABLES A
WHERE A.SYSTEM_CODE = 'LC1'
AND A.PARTITION_YN = 'Y';
3 테이블 생성[편집]
CREATE TABLE TB_TEST
(
COL1 numeric(10) not null,
COL2 varchar2(50) not null,
COL3 varchar2(50),
CONSTRAINT PK_TEST PRIMARY KEY (COL1)
);
5 테이블(제약사항 포함) 삭제[편집]
DROP TABLE OWNER.TB_A CASCADE CONSTRAINT;
6 컬럼 CRUD[편집]
6.1 컬럼 추가[편집]
6.2 컬럼 변경[편집]
ALTER TABLE TB_EMP
RENAME 컬럼 TO 컬럼2;
6.3 컬럼 삭제[편집]
ALTER TABLE TB_EMP
DROP COLUMN 컬럼명;
6.4 PK 컬럼 추가/삭제[편집]
6.4.1 컬럼 PK추가[편집]
ALTER TABLE TB_EMP
ADD CONSTRAINT PK명 PRIMARY KEY (컬럼1,컬럼2,...);
6.4.2 PK 삭제[편집]
ALTER TABLE TB_EMP
DROP CONSTRAINT PK명;
6.4.3 PK 사용 DISABLE[편집]
-- 방법 1
ALTER TABLE TB_EMP
DISABLE PRIMMARY KEY;
-- 방법 2
ALTER TABLE TB_EMP
DISABLE CONSTRAINT PK명;
6.4.4 PK 사용 ENABLE[편집]
-- 방법 1
ALTER TABLE TB_EMP
ENABLE PRIMMARY KEY;
-- 방법 2
ALTER TABLE TB_EMP
ENABLE CONSTRAINT PK명;
7 파티셔닝 테이블 정보[편집]
7.1 파티션 테이블 생성[편집]
CREATE TABLE TB_SALE (
SALE_DATE VARCHAR2(8 BYTE) NOT NULL ,
SALE_TIME VARCHAR2(6 BYTE) NOT NULL ,
CUST_NO VARCHAR2(10 BYTE) NOT NULL ,
SALE_AMT NUMBER ,
INPUT_DATE DATE
)
TABLESPACE TS_DATA
PARTITION BY RANGE (SALE_DATE)
(
PARTITION PR_TB_SALE_201801 VALUES LESS THAN ('20180201') ,
PARTITION PR_TB_SALE_201802 VALUES LESS THAN ('20180301') ,
PARTITION PR_TB_SALE_201803 VALUES LESS THAN ('20180401') ,
PARTITION PR_TB_SALE_201804 VALUES LESS THAN ('20180501') ,
PARTITION PR_TB_SALE_201805 VALUES LESS THAN ('20180601') ,
PARTITION PR_TB_SALE_201806 VALUES LESS THAN ('20180701') ,
PARTITION PR_TB_SALE_201807 VALUES LESS THAN ('20180801') ,
PARTITION PR_TB_SALE_201808 VALUES LESS THAN ('20180901') ,
PARTITION PR_TB_SALE_201809 VALUES LESS THAN ('20181001') ,
PARTITION PR_TB_SALE_201810 VALUES LESS THAN ('20181101') ,
PARTITION PR_TB_SALE_201811 VALUES LESS THAN ('20181201') ,
PARTITION PR_TB_SALE_201812 VALUES LESS THAN ('20190101') ,
PARTITION PR_TB_SALE_201901 VALUES LESS THAN ('20190201') ,
PARTITION PR_TB_SALE_201902 VALUES LESS THAN ('20190301') ,
PARTITION PR_TB_SALE_201903 VALUES LESS THAN ('20190401') ,
PARTITION PR_TB_SALE_201904 VALUES LESS THAN ('20190501') ,
PARTITION PR_TB_SALE_201905 VALUES LESS THAN ('20190601') ,
PARTITION PR_TB_SALE_201906 VALUES LESS THAN ('20190701') ,
PARTITION PR_TB_SALE_201907 VALUES LESS THAN ('20190801') ,
PARTITION PR_TB_SALE_201908 VALUES LESS THAN ('20190901') ,
PARTITION PR_TB_SALE_201909 VALUES LESS THAN ('20191001') ,
PARTITION PR_TB_SALE_201910 VALUES LESS THAN ('20191101') ,
PARTITION PR_TB_SALE_201911 VALUES LESS THAN ('20191201') ,
PARTITION PR_TB_SALE_201912 VALUES LESS THAN ('20200101')
-- PARTITION PR_TB_SALE_MAX VALUES LESS THAN (MAXVALUE)
)
;
-- PK 생성
CREATE UNIQUE INDEX PK_TB_SALE ON TB_SALE (SALE_DATE, SALE_TIME, CUST_NO)
TABLESPACE TS_INDEX LOCAL ;
ALTER TABLE TB_SALE
ADD CONSTRAINT PK_TB_SALE PRIMARY KEY (SALE_DATE, SALE_TIME, CUST_NO);
-- 코멘트
COMMENT ON COLUMN TB_SALE.SALE_DATE IS '매출일자';
COMMENT ON COLUMN TB_SALE.SALE_TIME IS '매출시간';
COMMENT ON COLUMN TB_SALE.CUST_NO IS '고객번호';
COMMENT ON COLUMN TB_SALE.SALE_AMT IS '매출 금액';
COMMENT ON TABLE TB_SALE IS '고객 매출내역';
파티션 구성 시 마지막에 MAXVALUE 를 주석으로 막은 이유는
분할해 둔 파티션의 마지막이 임박해오는 경우 SPLIT 보다 파티션 추가가 간편하기 때문
물론 예기치 않은 데이터가 들어오는 경우 MAXVALUE 를 지정해두는것이 안정적이긴 합니다.
7.2 파티션 추가[편집]
ALTER TABLE TB_SALE
ADD PARTITION PR_TB_SALE_202001 VALUES LESS THAN ('20200201' )
TABLESPACE TS_INDEX ;
7.3 파티션 삭제[편집]
ALTER TABLE TB_SALE
DROP PARTITION PR_TB_SALE_202001 ;
7.4 파티션 분할 (MAXVALUE 파티션 분할)[편집]
ALTER TABLE TB_SALE
SPLIT PARTITION PR_TB_SALE_MAX AT ( '20200201' )
INTO ( PARTITION PR_TB_SALE_202001
, PARTITION PR_TB_SALE_MAX
) ;
MAXVALUE 로 잡혀있던 PR_TB_SALE_MAX 파티션을 2020년 02월 01일 이전 데이터가 입력될 파티션으로 분리
7.5 파티션 TRUNCATE[편집]
ALTER TABLE TB_SALE
TRUNCATE PARTITION PR_TB_SALE_201801 ;
7.6 파티션 테이블 인덱스 생성[편집]
CREATE UNIQUE INDEX PK_TB_SALE ON TB_SALE (SALE_DATE, SALE_TIME, CUST_NO)
TABLESPACE TS_INDEX LOCAL ;
7.7 파티션 테이블을 생성할 때 INDEX 설정 시 주의 사항[편집]
7.7.1 RANGE 파티션을 가진 테이블을 월별로 파티션을 분리한다고 했을 때[편집]
- 테이블 전체에 INDEX 를 구성하는 것 보다 각각의 파티션 별로 INDEX 를 가지고 있어야 대용량의 테이블을 조회할 때 더 효과적
7.7.2 INDEX 지정 시 LOCAL INDEX 가 아닌 default 로 설정하거나 GLOBAL INDEX 로 설정할 경우[편집]
- 파티션기준이 아닌 전체 테이블을 기준으로 INDEX 가 만들어지니 주의
- 파티션 테이블이 GLOBAL INDEX 로 생성될 경우 테이블의 중간중간 데이터가 삭제되거나 변경된다면
전체 테이블 기준으로 최적화된 INDEX 가 뒤죽박죽 될 수 있으나 LOCAL 로 생성되는 경우에는 다른 파티션의 INDEX 에는 영향이 가지 않으니 INDEX 를 사용하는 SQL 인 경우 더 빠른 실행계획을 세울 수 있게 됩니다.
7.8 파티션 테이블의 파티션 범위 보기[편집]
SELECT TABLE_NAME
, SUBSTRB(PARTITION_NAME, 1, 30) AS PARTITION_NAME -- 파티셔닝 명
, SUBSTRB(TABLESPACE_NAME, 1, 30) AS TABLESPACE_NAME -- 테이블스페이스명
, HIGH_VALUE
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = UPPER('&테이블명');