행위

ORACLE 테이블 컬럼

DB CAFE

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)
);

4 테이블 변경

alter_table.gif alter_table_properties.gif

4.1 테이블명 변경

ALTER TABLE OWNER.TB_A RENAME TO TB_B;

바로가기 링크 >>> 오라클_테이블_최종_입력수정일시 확인하기


4.2 테이블 읽기/쓰기 모드 변경

ALTER TABLE TB_TEST READ ONLY;
ALTER TABLE TB_TEST READ WRITE;

5 테이블(제약사항 포함) 삭제

DROP TABLE OWNER.TB_A CASCADE CONSTRAINT;

6 컬럼 CRUD

6.1 컬럼 추가

add_column_clause.gif

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 파티션 테이블의 파티션 범위 보기

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('&테이블명');