[Oracle] Toad팁 & SQL 툴에서 데이터 직접 수정하는법 & 커밋후 데이터 복구방법

Toad 정렬 단축키
ctrl+shift+f

Toad 세로편집 단축키
Alt

Toad 주석처리
ctrl+b

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL 툴에서 데이터 직접 수정하는법

 

1. EDIT [tablename] WHERE --

ex)

EDIT sys_postno
WHERE  POSTNO='57066'

 

2. rowid

ex)

SELECT rowid, a.*

FROM emp a

 

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

커밋후 데이터 복구방법

 

INSERT INTO TEST
SELECT * FROM TEST AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '30' MINUTE);

 

 

실제로 날려먹고 부라부랴 적용한 예시....

 

DELETE
FROM 
      (
        SELECT * 
        FROM LOGIN B, PER_MAST A
        WHERE A.EMPCD = B.LOGIN_ID
        AND A.RETDAT IS NOT NULL
        AND A.RETDAT <='20220630'
      )

 

위 쿼리를 실행하면 재미있게도 FROM 서브 쿼리에 있는 첫번쨰 테이블 데이터만 삭제된다.....

LOGIN 테이블 데이터만 삭제되고 PER_MAST 테이블 데이터는 삭제 안되는셈(반대로 했다가 데이터 날릴뻔...)

 

             

 SELECT *
 FROM PER_MAST AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '30' MINUTE) A, LOGIN B
 WHERE A.EMPCD = B.LOGIN_ID
       AND A.RETDAT IS NOT NULL
       AND A.RETDAT <='20220630'

 

부랴부랴 날려먹은 데이터부터 확인한후

 

INSERT INTO PER_MAST
              SELECT
              A.EMPCD,        
              A.KORNAME,      
              A.CHINAME,      
              A.ENGNAME,      
              A.SAUPGB ,      
              A.PAYGUBUN  ,   
              A.DEPT_CD ,     
              A.JIKWICD    ,  
              A.GUBCODE    ,  
              A.HCODE      ,  
            A.JIKCHKCD     ,
              A.BASEAMT      ,
              A.GUNMUGB      ,
              A.FOREIGNGB    ,
              A.JUMINNO      ,
              A.MFGUBUN      ,
              A.EMPLGUBUN    ,
              A.EMPLROOT     ,
              A.IGENTDATE    ,
              A.REIGENTDATE  ,
              A.IGENTGUB     ,
              A.BETELNO      ,
              A.INCONNECT    ,
              A.IGENTDATE1   ,
              A.RETDAT       ,
              A.JOBDEPTCD    ,
              A.COMDEPTCD    ,
              A.JAJIKGUBUN   ,
              A.MARRGUBUN    ,
              A.RELIGUBUN    ,
              A.HOMEGUBUN    ,
              A.HOBBY        ,
              A.SKILL        ,
              A.NATION       ,
              A.MARRDATE     ,
              A.BAEBIRTH     ,
              A.BIRTHDAY     ,
              A.BIRTHGUBUN   ,
              A.LASTEDU      ,
              A.LASTSCH      ,
              A.HPHONE       ,
              A.CPHONE       ,
              A.HANDPHONE    ,
              A.CAR_CHK      ,
              A.CAR_NO       ,
              A.EMAIL        ,
              A.BONPOST      ,
              A.BONADDR1     ,
              A.BONADDR2     ,
              A.HUNPOST      ,
              A.HUNADDR1     ,
              A.HUNADDR2     ,
              A.HMASTER      ,
              A.BOGUBUN      ,
              A.BONO         ,
              A.JUNIPDATE    ,
              A.JUNCHDATE    ,
              A.JANMONTH     ,
              A.VACDATE      ,
              A.REVACDATE    ,
              A.MEDICHOHA    ,
              A.MEDICARD     ,
              A.MEDISTRDATE  ,
              A.MEDIENDDATE  ,
              A.NATAGRAD     ,
              A.NATASTRDATE  ,
              A.NATAENDDATE  ,
              A.NATCHUNG     ,
              A.PAYBANK1     ,
              A.PAYBANKNO1   ,
              A.PAYBANK2     ,
              A.PAYBANKNO2   ,
              A.PAYNAME2     ,
              A.SUBTRGB      ,
              A.NOZODATE     ,
              A.NOZOEDATE    ,
              A.RIGHT1       ,
              A.RIGHT2       ,
              A.RIGHT3       ,
              A.SUBETC1      ,
              A.SUBETC2      ,
              A.SUBETC3      ,
             A. DONGSAN      ,
              A.BUDONG       ,
             A. TOTPTY       ,
              A.SKYPASS      ,
              A.FIRSTYMD     ,
              A.FIRSTEMP     ,
              A.LASTYMD      ,
              A.LASTEMP      ,
              A.MGUBUN       ,
              A.MRETDAT      ,
              A.MEDIGRADE    ,
              A.SENGJIN      ,
              A.DELIV_DEPT   ,
              A.RETGUBUN     ,
              A.LUN_GUBUN    ,
              A.TAX_GUBUN    ,
              A.PERMISSION_YN,
              A.JIGUB_BANK    
              FROM PER_MAST AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '30' MINUTE) A, LOGIN B
              WHERE A.EMPCD = B.LOGIN_ID
              AND A.RETDAT IS NOT NULL
              AND A.RETDAT <='20220630'

 

PER_MAST 테이블 컬럼들에 실수로 삭제한 데이터들 쭉쭉 다시 넣어주었다........

골든타임내에 세이프.......  복구못했으면 백업한거 불러와서 날린 데이터만 찾아서 다시넣고.....

부장님한테 쿠사리먹고...  야근할뻔 ㅠㅠ