【Oracle】テーブルをコピーする方法完全ガイド｜CTAS・INSERT SELECT・構造のみ・制約の再作成・DB Link・高速化まで解説

-- employees テーブルを employees_copy としてコピー
CREATE TABLE employees_copy AS
SELECT * FROM employees;

-- コピー後の確認
SELECT COUNT(*) FROM employees_copy;

-- 必要な列だけコピー
CREATE TABLE emp_summary AS
SELECT employee_id, last_name, salary, department_id
FROM employees;

-- 列に別名を付ければコピー先のカラム名を変更できる
CREATE TABLE emp_renamed AS
SELECT employee_id AS id, last_name AS name, salary AS pay
FROM employees;

-- 部門 20 のデータのみコピー
CREATE TABLE dept20_employees AS
SELECT * FROM employees WHERE department_id = 20;

-- 2025年以降の注文のみコピー
CREATE TABLE orders_2025 AS
SELECT * FROM orders
WHERE order_date >= DATE '2025-01-01';

-- 方法①: WHERE 1=0（常に偽の条件でデータを 0 件にする）
CREATE TABLE employees_empty AS
SELECT * FROM employees WHERE 1 = 0;

-- 方法②: WHERE ROWNUM < 1（同じ効果）
CREATE TABLE employees_template AS
SELECT * FROM employees WHERE ROWNUM < 1;

-- どちらも同じ結果: カラム定義のみコピー、データは 0 件
SELECT COUNT(*) FROM employees_empty;  -- 0

-- 既存テーブルにデータをコピー
INSERT INTO employees_backup
SELECT * FROM employees;

-- 特定の列だけコピー（列名を合わせる）
INSERT INTO emp_summary (id, name, salary)
SELECT employee_id, last_name, salary
FROM employees;

-- 条件付きコピー
INSERT INTO active_employees
SELECT * FROM employees
WHERE status = 'ACTIVE';

COMMIT;

-- ① CTAS でデータをコピー
CREATE TABLE employees_copy AS SELECT * FROM employees;

-- ② PRIMARY KEY を追加
ALTER TABLE employees_copy ADD CONSTRAINT pk_emp_copy PRIMARY KEY (employee_id);

-- ③ インデックスを追加
CREATE INDEX idx_emp_copy_dept ON employees_copy (department_id);

-- ④ FOREIGN KEY を追加
ALTER TABLE employees_copy ADD CONSTRAINT fk_emp_copy_dept
    FOREIGN KEY (department_id) REFERENCES departments (dept_id);

-- 元テーブルの制約 DDL を取得
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', constraint_name, owner) AS ddl
FROM all_constraints
WHERE table_name = 'EMPLOYEES'
  AND owner = 'HR'
  AND constraint_type IN ('P', 'U', 'R', 'C');

-- 元テーブルのインデックス DDL を取得
SELECT DBMS_METADATA.GET_DDL('INDEX', index_name, owner) AS ddl
FROM all_indexes
WHERE table_name = 'EMPLOYEES'
  AND owner = 'HR';

-- 取得した DDL のテーブル名を置換して実行すれば制約・インデックスを再作成できる

-- デフォルトとは別の表領域にコピー
CREATE TABLE employees_copy
TABLESPACE users_data
AS SELECT * FROM employees;

-- 表領域を分けたい場面:
-- ・テスト用テーブルを別の表領域に隔離
-- ・SSD 上の表領域に高速テーブルをコピー
-- ・バックアップ用テーブルを大容量表領域に作成

-- DB Link の作成（事前準備）
CREATE DATABASE LINK remote_db
CONNECT TO remote_user IDENTIFIED BY password
USING 'remote_tns_name';

-- リモートテーブルの構造 + データをコピー（CTAS）
CREATE TABLE local_orders AS
SELECT * FROM orders@remote_db;

-- リモートから条件付きでコピー
CREATE TABLE recent_orders AS
SELECT * FROM orders@remote_db
WHERE order_date >= DATE '2025-01-01';

-- 既存テーブルにリモートデータを追加
INSERT INTO local_orders
SELECT * FROM orders@remote_db
WHERE order_id NOT IN (SELECT order_id FROM local_orders);
COMMIT;

-- NOLOGGING + PARALLEL で高速 CTAS
CREATE TABLE employees_copy
NOLOGGING
PARALLEL 4
AS SELECT * FROM employees;

-- コピー後に LOGGING に戻す（RMAN バックアップのため）
ALTER TABLE employees_copy LOGGING;
ALTER TABLE employees_copy NOPARALLEL;

-- コピー先テーブルを NOLOGGING に設定
ALTER TABLE employees_backup NOLOGGING;

-- APPEND ヒント（ダイレクトパスインサート）で高速コピー
INSERT /*+ APPEND PARALLEL(4) */ INTO employees_backup
SELECT * FROM employees;
COMMIT;

-- NOLOGGING を元に戻す
ALTER TABLE employees_backup LOGGING;

-- HR スキーマの employees を現在のスキーマにコピー
CREATE TABLE employees_copy AS
SELECT * FROM hr.employees;
-- 権限: hr.employees への SELECT 権限が必要

-- 別スキーマにコピーを作成する場合
CREATE TABLE test_schema.employees_copy AS
SELECT * FROM hr.employees;
-- 権限: test_schema での CREATE TABLE 権限 + hr.employees への SELECT 権限が必要

-- テスト用にテーブルをコピー（接尾辞 _test を付与）
CREATE TABLE orders_test AS SELECT * FROM orders;

-- テスト用なので制約は最小限（PK のみ追加）
ALTER TABLE orders_test ADD CONSTRAINT pk_orders_test PRIMARY KEY (order_id);

-- テスト後に削除
DROP TABLE orders_test PURGE;

-- PL/SQL で日付付きバックアップテーブルを動的作成
DECLARE
    v_table_name VARCHAR2(100);
BEGIN
    v_table_name := 'ORDERS_BK_' || TO_CHAR(SYSDATE, 'YYYYMMDD');
    EXECUTE IMMEDIATE
        'CREATE TABLE ' || v_table_name || ' AS SELECT * FROM orders';
    DBMS_OUTPUT.PUT_LINE('Created: ' || v_table_name);
END;
/
-- 結果: ORDERS_BK_20260328 テーブルが作成される

-- ① 現在のテーブルをバックアップ
CREATE TABLE employees_bk_20260328 AS SELECT * FROM employees;

-- ② ALTER TABLE で構造変更
ALTER TABLE employees ADD (middle_name VARCHAR2(50));

-- ③ 問題が発生した場合はバックアップから復元
-- TRUNCATE TABLE employees;
-- INSERT INTO employees SELECT emp.*, NULL FROM employees_bk_20260328 emp;
-- COMMIT;

-- 大量データを 10 万件ずつ分割してコピー（UNDO 溢れ防止）
BEGIN
    LOOP
        INSERT /*+ APPEND */ INTO orders_archive
        SELECT * FROM orders
        WHERE order_date < DATE '2024-01-01'
          AND ROWNUM <= 100000;

        EXIT WHEN SQL%ROWCOUNT = 0;
        COMMIT;
    END LOOP;
END;
/