ORACLE 컬럼 - DB CAFE

테이블 컬럼 정보

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

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