【Oracle】スキーマとは何か・作成から活用まで完全ガイド｜他RDBMS比較・クロススキーマアクセス・SYNONYM・CURRENT_SCHEMAまで解説

-- ユーザーを作成すると同名のスキーマが自動生成される
CREATE USER app_schema IDENTIFIED BY "SecurePass#2024"
    DEFAULT TABLESPACE app_data      -- オブジェクトを格納する表領域
    TEMPORARY TABLESPACE temp        -- ソート作業領域
    QUOTA UNLIMITED ON app_data;     -- 表領域の使用上限（UNLIMITED = 無制限）

-- 接続するには CREATE SESSION 権限が必要
GRANT CREATE SESSION TO app_schema;

-- アプリのオブジェクトを作る権限を付与
GRANT CREATE TABLE, CREATE INDEX, CREATE VIEW, CREATE SEQUENCE,
      CREATE PROCEDURE, CREATE TRIGGER TO app_schema;

-- CREATE SCHEMA AUTHORIZATION は「新スキーマ作成」ではない
-- 自分のスキーマ（HR）内にテーブル・ビューを一括で作成し、権限も付与する
-- HR ユーザーとして実行する例
CREATE SCHEMA AUTHORIZATION hr
    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),
        dept_id  NUMBER REFERENCES departments(dept_id)
    )
    CREATE VIEW emp_dept_view AS
        SELECT e.emp_name, d.dept_name
        FROM   employees e JOIN departments d ON e.dept_id = d.dept_id
    GRANT SELECT ON departments TO scott;

-- HR スキーマの employees テーブルを SCOTT スキーマから参照する例

-- Step 1: HR ユーザーが SCOTT に SELECT 権限を付与
GRANT SELECT ON hr.employees TO scott;

-- Step 2: SCOTT ユーザーからスキーマ名付きで参照
SELECT emp_id, emp_name
FROM   hr.employees   -- スキーマ名.テーブル名 で他スキーマを参照
WHERE  dept_id = 10;

-- あるスキーマのすべてのテーブルへの SELECT を一括付与（DBA が実行）
BEGIN
    FOR t IN (SELECT table_name FROM dba_tables WHERE owner = 'HR') LOOP
        EXECUTE IMMEDIATE 'GRANT SELECT ON hr.' || t.table_name || ' TO scott';
    END LOOP;
END;
/

-- SCOTT が受け取った権限をさらに別のユーザーに付与できるようにする
GRANT SELECT ON hr.employees TO scott WITH GRANT OPTION;

-- SCOTT は受け取った権限を JONES にも付与できる
-- （SCOTT として実行）
GRANT SELECT ON hr.employees TO jones;

-- プライベートシノニム：SCOTT が HR.EMPLOYEES を「EMPLOYEES」として参照
CREATE SYNONYM employees FOR hr.employees;

-- シノニムを使ってスキーマ名なしでアクセス
SELECT emp_id, emp_name FROM employees;   -- hr.employees と同じ

-- パブリックシノニム（DBA が作成、すべてのユーザーが参照可能）
CREATE PUBLIC SYNONYM pub_employees FOR hr.employees;

-- シノニムの削除
DROP SYNONYM employees;
DROP PUBLIC SYNONYM pub_employees;

-- 存在するシノニムを確認
SELECT synonym_name, table_owner, table_name FROM user_synonyms ORDER BY synonym_name;
SELECT synonym_name, table_owner, table_name FROM all_synonyms WHERE owner = 'PUBLIC' AND table_owner = 'HR';

-- 現在のスキーマを確認
SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM dual;

-- CURRENT_SCHEMA を HR スキーマに切り替える
ALTER SESSION SET CURRENT_SCHEMA = hr;

-- これ以降、スキーマ名を省略すると HR スキーマが参照される
SELECT * FROM employees;    -- hr.employees として解釈される
SELECT * FROM departments;  -- hr.departments として解釈される

-- 元のスキーマに戻す（自分のユーザー名を指定）
ALTER SESSION SET CURRENT_SCHEMA = scott;

-- 自分のスキーマのオブジェクト一覧（種別・状態含む）
SELECT object_name,
       object_type,
       status,         -- VALID / INVALID
       created,
       last_ddl_time
FROM   user_objects
ORDER  BY object_type, object_name;

-- 種別ごとのオブジェクト数を集計
SELECT object_type, COUNT(*) AS cnt
FROM   user_objects
GROUP  BY object_type
ORDER  BY cnt DESC;

-- HR スキーマのすべてのオブジェクトを確認
SELECT object_name, object_type, status, last_ddl_time
FROM   dba_objects
WHERE  owner = 'HR'
ORDER  BY object_type, object_name;

-- INVALID になっているオブジェクトを全スキーマから抽出
SELECT owner, object_name, object_type, last_ddl_time
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER  BY owner, object_type;

-- スキーマごとの合計使用容量（MB）
SELECT owner,
       COUNT(*)                              AS segment_count,
       ROUND(SUM(bytes) / 1024 / 1024, 1)   AS total_mb
FROM   dba_segments
GROUP  BY owner
ORDER  BY total_mb DESC
FETCH  FIRST 20 ROWS ONLY;

-- 特定スキーマの種別ごとの使用容量
SELECT segment_type, ROUND(SUM(bytes) / 1024 / 1024, 1) AS mb
FROM   dba_segments
WHERE  owner = 'HR'
GROUP  BY segment_type
ORDER  BY mb DESC;

-- パターン①: スキーマにオブジェクトがない場合
DROP USER app_schema;
-- → スキーマにテーブル等が残っているとエラー（ORA-01922: CASCADE must be specified）

-- パターン②: スキーマとその中のすべてのオブジェクトを削除（CASCADE）
DROP USER app_schema CASCADE;
-- → テーブル・インデックス・ビュー・プロシージャなどをすべて削除
-- → 他スキーマから参照している外部キー制約も自動的に削除される

-- 削除前に格納オブジェクト数を確認
SELECT COUNT(*) FROM dba_objects WHERE owner = 'APP_SCHEMA';

-- Step 1: オブジェクト所有用スキーマを作成（接続不可）
CREATE USER app_owner IDENTIFIED BY "OwnerPass#2024"
    DEFAULT TABLESPACE app_data
    QUOTA UNLIMITED ON app_data;
-- CREATE SESSION を付与しない → 直接接続不可にする

-- Step 2: アプリ接続用スキーマを作成（接続のみ、オブジェクト所有なし）
CREATE USER app_user IDENTIFIED BY "UserPass#2024"
    DEFAULT TABLESPACE app_data;
GRANT CREATE SESSION TO app_user;

-- Step 3: app_owner のオブジェクトへの権限を app_user に付与
GRANT SELECT, INSERT, UPDATE, DELETE ON app_owner.orders TO app_user;
GRANT SELECT, INSERT ON app_owner.customers TO app_user;

-- Step 4: ログオントリガーで CURRENT_SCHEMA を自動切り替え（省略記述が可能になる）
CREATE OR REPLACE TRIGGER trg_set_schema
    AFTER LOGON ON app_user.SCHEMA
BEGIN
    EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = app_owner';
END;
/

-- これ以降 app_user は "SELECT * FROM orders" で app_owner.orders を参照できる

-- HR スキーマ全体をエクスポート
expdp system/password SCHEMAS=hr DIRECTORY=DATA_PUMP_DIR DUMPFILE=hr_schema.dmp LOGFILE=hr_exp.log

-- 別スキーマ名（HR_NEW）でインポート（REMAP_SCHEMA）
impdp system/password SCHEMAS=hr REMAP_SCHEMA=hr:hr_new DIRECTORY=DATA_PUMP_DIR DUMPFILE=hr_schema.dmp LOGFILE=hr_imp.log