ORACLE 컬럼

오라클 컬럼

테이블 컬럼 정보

 
SELECT A.OWNER           -- 유저명
     , A.TABLE_NAME      -- 테이블명
     , A.COLUMN_ID       -- 컬럼순서
     , B.COMMENTS        -- 컬럼코멘트
     , A.COLUMN_NAME     -- 컬럼명
     , A.DATA_TYPE || '(' ||DECODE(A.DATA_TYPE,'NUMBER',A.DATA_PRECISION
                          ||DECODE(A.DATA_SCALE, 0, '', ',' || A.DATA_SCALE)
                        , A.DATA_LENGTH) || ')'  -- 타입
     , A.DATA_SCALE
     , A.NULLABLE        -- NULLABLE
     , A.DATA_DEFAULT     -- 디폴트값
  FROM DBA_TAB_COLUMNS  A
     , DBA_COL_COMMENTS B
 WHERE A.TABLE_NAME = B.TABLE_NAME
   AND A.COLUMN_NAME = B.COLUMN_NAME
   AND EXISTS (SELECT 1
                 FROM MIG_TABLES X
                WHERE X.OWNER = A.OWNER
                  AND X.TABLE_NAME = A.TABLE_NAME
               )
 ORDER BY A.OWNER,A.TABLE_NAME,A.COLUMN_ID

컬럼 사용 통계

set lines 150
set pages 500
col table_name for a20
col column_name for a20

  SELECT a.object_name     table_name
       , c.column_name
       , equality_preds
       , equijoin_preds
       , range_preds
       , like_preds
    FROM dba_objects a, col_usage$ b, dba_tab_columns c
   WHERE a.object_id = b.OBJ#
     AND c.COLUMN_ID = b.INTCOL#
     AND a.object_name = c.table_name
     AND b.obj# = a.object_id
     AND a.object_name = '&table_name'
     AND a.object_type = 'TABLE'
     AND a.owner = '&owner'
ORDER BY 3 DESC, 4 DESC, 5 DESC;

CTAS로 컬럼정보 테이블 생성

 
CREATE TABLE MIG_SRC_COLS
   AS 
SELECT 'LC1' AS SYSTEM_CODE
     , A.OWNER           -- 유저명
     , A.TABLE_NAME      -- 테이블명
     , A.COLUMN_ID       -- 컬럼순서
     , B.COMMENTS        -- 컬럼코멘트
     , A.COLUMN_NAME     -- 컬럼명
     , A.DATA_TYPE || '(' ||DECODE(A.DATA_TYPE,'NUMBER',A.DATA_PRECISION
                          ||DECODE(A.DATA_SCALE, 0, '', ',' || A.DATA_SCALE)
                        , A.DATA_LENGTH) || ')'  AS TYPE -- 타입
--     , A.DATA_SCALE
     , (SELECT POSITION 
          FROM DBA_CONS_COLUMNS AA
         INNER JOIN DBA_CONSTRAINTS BB
            ON AA.CONSTRAINT_NAME = BB.CONSTRAINT_NAME
           AND AA.OWNER = BB.OWNER
         WHERE BB.CONSTRAINT_TYPE = 'P'
           AND AA.OWNER = A.OWNER           
           AND AA.TABLE_NAME = A.TABLE_NAME  
           AND AA.COLUMN_NAME = A.COLUMN_NAME       
       ) AS PK
     , A.NULLABLE        -- NULLABLE
--     , A.DATA_DEFAULT     -- 디폴트값
  FROM DBA_TAB_COLUMNS  A
     , DBA_COL_COMMENTS B            
 WHERE A.OWNER = B.OWNER
   AND A.TABLE_NAME = B.TABLE_NAME
   AND A.COLUMN_NAME = B.COLUMN_NAME   
   AND EXISTS (SELECT 1
                 FROM MIG_TABLES X
                WHERE X.OWNER = A.OWNER                
                  AND X.TABLE_NAME = A.TABLE_NAME
                  AND X.SYSTEM_CODE = 'LC1'
               )               
 ORDER BY A.OWNER,A.TABLE_NAME,A.COLUMN_ID 
 ;

한 컬럼에 있는 동일한 자료 삭제 방법

 
DELETE FROM EMP E　
 WHERE E.ROWID > ( SELECT MIN(X.ROWID)　　　 　　　　　　　　　 
                     FROM EMP X　　　 
                    WHERE X.EMPNO = E.EMPNO );

테이블에서 로우 체이닝이 발생된 컬럼건수 조회

 
-- 1. First, analyze the table as below:

ANALYZE TABLE SCOTT.EMPTABLE LIST CHAINED ROWS;

-- Then check the row_count in chained_row table

select count(*) from chained_rows where table_name='EMPTABLE';