【Oracle】開発環境を一括初期化するスクリプトの作成例｜DROP+再作成・Data Pump・シーケンスリセット・安全策まで解説

-- PL/SQL で全テーブルを自動 DROP
BEGIN
    -- 外部キー制約を先に無効化（DROP 順序の問題を回避）
    FOR rec IN (
        SELECT constraint_name, table_name
        FROM user_constraints
        WHERE constraint_type = 'R'  -- 外部キーのみ
    ) LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name
            || ' DROP CONSTRAINT ' || rec.constraint_name;
    END LOOP;

    -- 全テーブルを DROP
    FOR rec IN (
        SELECT table_name FROM user_tables
    ) LOOP
        EXECUTE IMMEDIATE 'DROP TABLE ' || rec.table_name || ' CASCADE CONSTRAINTS PURGE';
    END LOOP;
END;
/

-- テーブル作成スクリプト（DDL）
CREATE TABLE departments (
    dept_id      NUMBER PRIMARY KEY,
    dept_name    VARCHAR2(100) NOT NULL
);

CREATE TABLE employees (
    emp_id       NUMBER PRIMARY KEY,
    emp_name     VARCHAR2(100) NOT NULL,
    dept_id      NUMBER REFERENCES departments(dept_id),
    salary       NUMBER,
    hire_date    DATE DEFAULT SYSDATE
);

CREATE INDEX idx_emp_dept ON employees(dept_id);

-- PL/SQL で全シーケンスを DROP
BEGIN
    FOR rec IN (
        SELECT sequence_name FROM user_sequences
    ) LOOP
        EXECUTE IMMEDIATE 'DROP SEQUENCE ' || rec.sequence_name;
    END LOOP;
END;
/

-- シーケンスを再作成
CREATE SEQUENCE seq_dept_id START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_emp_id START WITH 1 INCREMENT BY 1;

-- シーケンスを 1 にリセットする方法（DROP 不要）
DECLARE
    v_val NUMBER;
BEGIN
    -- 現在値を取得
    SELECT seq_emp_id.NEXTVAL INTO v_val FROM DUAL;
    -- INCREMENT を -(現在値-1) に変更して NEXTVAL で 1 に戻す
    EXECUTE IMMEDIATE 'ALTER SEQUENCE seq_emp_id INCREMENT BY -' || (v_val - 1);
    SELECT seq_emp_id.NEXTVAL INTO v_val FROM DUAL;
    -- INCREMENT を元に戻す
    EXECUTE IMMEDIATE 'ALTER SEQUENCE seq_emp_id INCREMENT BY 1';
END;
/

-- テストデータ投入
INSERT INTO departments VALUES (seq_dept_id.NEXTVAL, '営業部');
INSERT INTO departments VALUES (seq_dept_id.NEXTVAL, '開発部');
INSERT INTO departments VALUES (seq_dept_id.NEXTVAL, '総務部');

INSERT INTO employees VALUES (seq_emp_id.NEXTVAL, '田中太郎', 1, 5000, DATE '2020-04-01');
INSERT INTO employees VALUES (seq_emp_id.NEXTVAL, '鈴木花子', 2, 6000, DATE '2021-04-01');
INSERT INTO employees VALUES (seq_emp_id.NEXTVAL, '佐藤一郎', 1, 4500, DATE '2022-04-01');

COMMIT;

-- reset_dev.sql: 開発環境の一括初期化
SET SERVEROUTPUT ON
PROMPT ===== 開発環境の初期化を開始 =====

-- (1) 全テーブル DROP
@drop_all_tables.sql

-- (2) テーブル作成
@create_tables.sql

-- (3) シーケンス作成
@create_sequences.sql

-- (4) テストデータ投入
@insert_test_data.sql

-- (5) 統計情報の収集
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(USER);

PROMPT ===== 初期化完了 =====

# テスト環境を初期状態にした後にダンプを取得
expdp dev_user/password \
    directory=DP_DIR \
    dumpfile=dev_initial_state.dmp \
    logfile=dev_initial_exp.log \
    schemas=DEV_USER \
    compression=ALL

# このダンプを「初期状態マスター」として保管

# 全オブジェクトを削除してからダンプで復元
impdp dev_user/password \
    directory=DP_DIR \
    dumpfile=dev_initial_state.dmp \
    logfile=dev_reset.log \
    schemas=DEV_USER \
    table_exists_action=REPLACE

-- (1) テスト開始前: リストアポイントを作成
CREATE RESTORE POINT dev_initial GUARANTEE FLASHBACK DATABASE;

-- (2) テスト実施...

-- (3) テスト完了: リストアポイントに巻き戻し
-- SYSDBA で接続
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT dev_initial;
ALTER DATABASE OPEN RESETLOGS;

-- (4) 不要になったらリストアポイントを削除
DROP RESTORE POINT dev_initial;

#!/bin/bash
# reset_dev_env.sh: 開発環境の一括初期化
set -e

export ORACLE_HOME=/oracle/product/19c/dbhome_1
export ORACLE_SID=DEVDB

SCRIPT_DIR=$(cd $(dirname $0) && pwd)
LOG_DIR=$SCRIPT_DIR/logs
mkdir -p $LOG_DIR

echo "[$(date)] 開発環境の初期化を開始" | tee -a $LOG_DIR/reset.log

# (1) 安全チェック: 本番 DB でないことを確認
DB_NAME=$(sqlplus -s / as sysdba <<EOF
SET HEADING OFF FEEDBACK OFF
SELECT name FROM v\$database;
EOF
)
if [ "$DB_NAME" != "DEVDB" ]; then
    echo "ERROR: 本番 DB ($DB_NAME) では実行できません" >&2
    exit 1
fi

# (2) Data Pump で初期状態を復元
impdp dev_user/password \
    directory=DP_DIR \
    dumpfile=dev_initial_state.dmp \
    schemas=DEV_USER \
    table_exists_action=REPLACE \
    logfile=reset_import.log 2>&1 | tee -a $LOG_DIR/reset.log

# (3) 統計情報の収集
sqlplus dev_user/password <<EOF
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(USER);
EOF

echo "[$(date)] 初期化完了" | tee -a $LOG_DIR/reset.log

-- reset_dev.sql の先頭に安全チェックを追加
WHENEVER SQLERROR EXIT SQL.SQLCODE

DECLARE
    v_db_name VARCHAR2(100);
BEGIN
    SELECT name INTO v_db_name FROM v$database;
    IF v_db_name NOT IN ('DEVDB', 'TESTDB') THEN
        RAISE_APPLICATION_ERROR(-20999,
            '本番DB(' || v_db_name || ')では実行できません');
    END IF;
    IF USER NOT IN ('DEV_USER', 'TEST_USER') THEN
        RAISE_APPLICATION_ERROR(-20998,
            '本番ユーザー(' || USER || ')では実行できません');
    END IF;
END;
/

PROMPT 安全チェック OK: 初期化を続行します
-- ここから DROP / CREATE / INSERT 処理

-- テーブル構造（制約 / インデックス）を維持してデータだけ削除
BEGIN
    -- 外部キー制約を一時無効化
    FOR rec IN (
        SELECT constraint_name, table_name FROM user_constraints
        WHERE constraint_type = 'R'
    ) LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name
            || ' DISABLE CONSTRAINT ' || rec.constraint_name;
    END LOOP;

    -- 全テーブルを TRUNCATE
    FOR rec IN (SELECT table_name FROM user_tables) LOOP
        EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || rec.table_name;
    END LOOP;

    -- 外部キー制約を再有効化
    FOR rec IN (
        SELECT constraint_name, table_name FROM user_constraints
        WHERE constraint_type = 'R'
    ) LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name
            || ' ENABLE CONSTRAINT ' || rec.constraint_name;
    END LOOP;
END;
/

-- その後テストデータを投入
@insert_test_data.sql

-- ビューの再作成
@create_views.sql

-- プロシージャ / ファンクション / パッケージの再作成
@create_packages.sql

-- トリガーの再作成
@create_triggers.sql

-- INVALID オブジェクトの再コンパイル
EXEC DBMS_UTILITY.COMPILE_SCHEMA(USER);

-- 全ビューを DROP
BEGIN
    FOR rec IN (SELECT view_name FROM user_views) LOOP
        EXECUTE IMMEDIATE 'DROP VIEW ' || rec.view_name;
    END LOOP;
END;
/

-- 全プロシージャを DROP
BEGIN
    FOR rec IN (
        SELECT object_name, object_type FROM user_objects
        WHERE object_type IN ('PROCEDURE','FUNCTION','PACKAGE')
    ) LOOP
        EXECUTE IMMEDIATE 'DROP ' || rec.object_type || ' ' || rec.object_name;
    END LOOP;
END;
/