Oracle 전체 테이블 삭제 및 DB 링크 복제 자동화: PL/SQL 실전 스크립트 정리

Oracle DB 전체 테이블 삭제 및 DB 링크로 테이블·데이터 복사 자동화하기

개발·운영 환경을 초기화하거나, 다른 서버의 스키마를 그대로 복제해야 할 때 “전체 테이블 삭제 → DB 링크를 이용한 테이블/데이터 복사” 작업이 자주 필요합니다.

이 글에서는 Oracle 데이터베이스 기준으로 PL/SQL 루프 + EXECUTE IMMEDIATE를 활용해 전체 테이블 삭제와 DB 링크를 통한 테이블/데이터 복사를 자동화하는 방법을 정리합니다. 실무에서 바로 쓸 수 있는 예제 코드와, 꼭 확인해야 할 주의사항까지 함께 담았습니다.

📚 목차

1. Oracle DB 전체 테이블 삭제 (user_tables 기반)
2. DB 링크로 테이블 구조만 복사하기
3. DB 링크로 테이블 데이터까지 복사하기
4. 실무에서 자주 하는 튜닝·주의사항

---

1. Oracle DB 전체 테이블 삭제 (user_tables 기반)

개발 환경을 초기화하거나, 테스트용 스키마를 리셋할 때 user_tables 기준으로 현재 스키마의 모든 테이블을 DROP하는 패턴입니다. 아래 예제는 PL/SQL 블록 + 커서 루프로 구현한 기본 형태입니다.

BEGIN
   FOR c IN (SELECT table_name FROM user_tables) LOOP
      EXECUTE IMMEDIATE 'DROP TABLE ' || c.table_name || ' CASCADE CONSTRAINTS';
   END LOOP;
END;
/
    

1-1. 코드 설명

• user_tables : 현재 접속한 유저가 소유한 테이블 목록을 반환
• FOR c IN (...) : 각 테이블 이름을 하나씩 순회
• EXECUTE IMMEDIATE : 동적 SQL 실행 (DROP TABLE 문을 문자열로 구성)
• CASCADE CONSTRAINTS : 외래키 등 제약조건까지 함께 삭제

⚠️ 주의
이 코드는 현재 스키마의 모든 테이블을 삭제합니다.
운영 환경(Production)에서는 절대 사용 금지이며, 반드시 개발/로컬/테스트 환경에서만 사용하세요.

테이블 이름에 대소문자/특수문자가 섞여 있는 경우에는 "'||c.table_name||'"처럼 더블 쿼트로 감싸는 패턴도 많이 사용합니다.

---

2. DB 링크로 테이블 구조만 복사하기

다른 DB 서버나 스키마에 있는 테이블 구조만 먼저 가져오고 싶을 때, DB LINK를 통해 CREATE TABLE AS SELECT (CTAS) 문을 자동으로 생성하는 패턴입니다.

아래 예제는 DB 링크 이름이 TEST라고 가정합니다.

BEGIN
   FOR c IN (SELECT table_name FROM user_tables@TEST) LOOP
      EXECUTE IMMEDIATE
         'CREATE TABLE ' || c.table_name ||
         ' AS SELECT * FROM ' || c.table_name || '@TEST WHERE 1 = 2';
   END LOOP;
END;
/
    

2-1. 코드 설명

• user_tables@TEST : DB 링크 TEST를 통해 원본 DB의 테이블 목록 조회
• CREATE TABLE ... AS SELECT * ... WHERE 1 = 2 : 구조만 복사하고 데이터는 가져오지 않는 CTAS 패턴
• 루프를 돌며 모든 테이블을 현재 스키마에 생성

💡 알아두면 좋은 점
이 방식은 기본적으로 컬럼 구조와 데이터 타입만 복사합니다.
인덱스, 제약조건, 시퀀스, 트리거 등은 별도로 생성해줘야 합니다.

---

3. DB 링크로 테이블 데이터까지 복사하기

2번에서 테이블 구조를 다 만들어두었다면, 이번에는 DB 링크를 통해 원본 DB의 데이터를 INSERT하는 작업을 자동화할 수 있습니다.

마찬가지로 DB 링크 이름은 TEST로 가정합니다.

BEGIN
   FOR c IN (SELECT table_name FROM user_tables@TEST) LOOP
      EXECUTE IMMEDIATE
         'INSERT INTO ' || c.table_name ||
         ' SELECT * FROM ' || c.table_name || '@TEST';
   END LOOP;

   COMMIT;
END;
/
    

3-1. 자주 하는 실수 바로잡기

질문 코드에 종종 INSERT INTO 테이블 AS SELECT ... 형태로 쓰는 경우가 있는데, INSERT 문에는 AS를 사용하지 않습니다.
올바른 문법은 다음과 같습니다.

-- 잘못된 예
INSERT INTO 테이블 AS SELECT * FROM 테이블@TEST;

-- 올바른 예
INSERT INTO 테이블 SELECT * FROM 테이블@TEST;
    

3-2. 코드 설명 및 주의사항

• INSERT INTO <local_table> SELECT * FROM <remote_table>@TEST
  ⇒ DB 링크를 통해 원본 데이터를 한 번에 복사
• 컬럼 순서가 동일해야 에러 없이 데이터가 들어갑니다.
• 대량 데이터인 경우 COMMIT 주기를 나눠주는 것이 좋습니다.

DECLARE
   v_count NUMBER := 0;
BEGIN
   FOR c IN (SELECT table_name FROM user_tables@TEST) LOOP
      EXECUTE IMMEDIATE
         'INSERT INTO ' || c.table_name ||
         ' SELECT * FROM ' || c.table_name || '@TEST';

      v_count := v_count + 1;

      -- 예: 10개 테이블마다 한 번씩 커밋
      IF v_count MOD 10 = 0 THEN
         COMMIT;
      END IF;
   END LOOP;

   COMMIT;
END;
/
    

---

4. 실무에서 자주 쓰는 팁·주의사항

4-1. 특정 테이블 제외하기

로그 테이블, 대용량 히스토리 테이블 등은 복사 대상에서 제외하고 싶은 경우가 많습니다. 이때는 WHERE 절에 조건을 추가하면 됩니다.

FOR c IN (
   SELECT table_name
     FROM user_tables@TEST
    WHERE table_name NOT IN ('BIG_LOG_TABLE', 'BATCH_HISTORY')
) LOOP
   ...
END LOOP;
    

4-2. 인덱스·제약조건·시퀀스는 별도 스크립트로

• CTAS로 생성한 테이블은 인덱스, PK/FK, 체크 제약조건, 트리거 등이 복사되지 않습니다.
• user_constraints, user_indexes, user_sequences 등을 이용해 별도 스크립트를 생성하거나, DDL 추출 툴을 사용하는 것이 일반적입니다.

4-3. 권한 및 DB 링크 점검

• 원본 DB에 대해 DB LINK가 정상적으로 연결되는지 사전 확인
• 해당 스키마에 대한 SELECT 권한이 있는지 체크
• 대량 데이터 복사 시 UNDO, TEMP, REDO 사용량 모니터링

4-4. 운영 환경에서는 신중하게

전체 테이블 삭제 및 대량 복사 작업은 운영환경에서는 치명적인 작업이 될 수 있습니다. 다음과 같은 원칙을 꼭 지키세요.

• 반드시 테스트/스테이징 환경에서 충분히 검증 후 적용
• 작업 전 전체 백업 또는 최소 테이블 레벨 백업 확보
• 작업 시간, 롤백 플랜, 영향 범위를 문서화

---

이 글에서 소개한 코드는 Oracle 환경에서 스키마 초기화 + DB 링크 기반 테이블/데이터 복제를 빠르게 처리할 수 있는 기본 골격입니다.

실무에서는 여기서 한 단계 더 나아가, 제외 테이블 관리, 로그 기록, 에러 핸들링, 인덱스/제약조건 복원 스크립트까지 함께 구성하면 안정적인 마이그레이션·동기화 스크립트로 발전시킬 수 있습니다.