【Oracle】表領域にユーザーを割り当てる方法｜DEFAULT TABLESPACE・QUOTA・一時表領域・確認 SQL まで解説

-- デフォルト表領域と一時表領域を指定
CREATE USER app_user IDENTIFIED BY password
    DEFAULT TABLESPACE app_data      -- オブジェクトの格納先
    TEMPORARY TABLESPACE temp        -- ソート等の一時領域
    QUOTA 500M ON app_data;          -- app_data に 500MB まで使用可能

-- QUOTA UNLIMITED: 表領域のサイズ上限まで無制限に使用
CREATE USER dev_user IDENTIFIED BY password
    DEFAULT TABLESPACE dev_data
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON dev_data;

-- 最小限の権限を付与
GRANT CREATE SESSION, CREATE TABLE TO app_user;

-- デフォルト表領域を変更
ALTER USER app_user DEFAULT TABLESPACE new_app_data;

-- 一時表領域を変更
ALTER USER app_user TEMPORARY TABLESPACE temp2;

-- QUOTA を変更
ALTER USER app_user QUOTA 1G ON new_app_data;
ALTER USER app_user QUOTA UNLIMITED ON new_app_data;

-- QUOTA を取り消し（0 に設定: 新規書き込み不可）
ALTER USER app_user QUOTA 0 ON old_data;
-- ※ 既存オブジェクトは残るが、新規作成や拡張ができなくなる

-- デフォルト表領域 + インデックス用表領域に個別に QUOTA
ALTER USER app_user QUOTA 1G ON app_data;
ALTER USER app_user QUOTA 500M ON app_index;

-- SYSTEM 表領域には QUOTA を設定しないのが原則
-- （SYSTEM にユーザーオブジェクトを作成すべきではない）

-- 全表領域に無制限 QUOTA（開発環境向け）
GRANT UNLIMITED TABLESPACE TO dev_user;

-- 取り消し
REVOKE UNLIMITED TABLESPACE FROM dev_user;

-- 注意: REVOKE すると個別の QUOTA 設定も 0 にリセットされる
-- → REVOKE 後に必要な表領域の QUOTA を再設定する
ALTER USER dev_user QUOTA 1G ON dev_data;

-- ユーザーごとのデフォルト表領域と一時表領域
SELECT username, default_tablespace, temporary_tablespace, account_status
FROM dba_users
WHERE username NOT IN ('SYS','SYSTEM','ANONYMOUS','XDB')
ORDER BY username;

-- ユーザーごとの QUOTA 設定と使用量
SELECT username, tablespace_name,
       CASE WHEN max_bytes = -1 THEN 'UNLIMITED'
            ELSE TO_CHAR(ROUND(max_bytes/1024/1024)) || ' MB'
       END AS quota,
       ROUND(bytes/1024/1024, 1) AS used_mb
FROM dba_ts_quotas
ORDER BY username, tablespace_name;

-- UNLIMITED TABLESPACE 権限の確認
SELECT grantee, privilege FROM dba_sys_privs
WHERE privilege = 'UNLIMITED TABLESPACE'
ORDER BY grantee;

-- ユーザー APP_USER の表領域関連情報
SELECT 'DEFAULT_TS' AS info, default_tablespace AS value FROM dba_users WHERE username = 'APP_USER'
UNION ALL
SELECT 'TEMP_TS', temporary_tablespace FROM dba_users WHERE username = 'APP_USER'
UNION ALL
SELECT 'QUOTA: ' || tablespace_name,
       CASE WHEN max_bytes = -1 THEN 'UNLIMITED'
            ELSE ROUND(max_bytes/1024/1024) || 'MB (used: ' || ROUND(bytes/1024/1024,1) || 'MB)'
       END
FROM dba_ts_quotas WHERE username = 'APP_USER';

-- 現在の DB デフォルト表領域を確認
SELECT property_name, property_value
FROM database_properties
WHERE property_name IN ('DEFAULT_PERMANENT_TABLESPACE', 'DEFAULT_TEMP_TABLESPACE');

-- DB デフォルト表領域を変更（今後作成するユーザーに影響）
ALTER DATABASE DEFAULT TABLESPACE app_data;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

-- 既存ユーザーのデフォルト表領域は変わらない（新規ユーザーのみ）

ORA-01950: no privileges on tablespace 'USERS'

-- (1) ユーザーの QUOTA を確認
SELECT tablespace_name, max_bytes, bytes
FROM dba_ts_quotas WHERE username = 'APP_USER';
-- max_bytes = 0 なら QUOTA なし → ORA-01950 の原因

-- (2) QUOTA を付与
ALTER USER app_user QUOTA UNLIMITED ON users;

-- (3) 再度 CREATE TABLE を実行して確認

-- テーブルを別の表領域に移動
ALTER TABLE employees MOVE TABLESPACE new_data;

-- インデックスの再構築（MOVE 後は UNUSABLE になるため必須）
ALTER INDEX idx_emp_dept REBUILD TABLESPACE new_index;

-- テーブルの全インデックスを一括再構築
BEGIN
    FOR rec IN (
        SELECT index_name FROM user_indexes WHERE table_name = 'EMPLOYEES'
    ) LOOP
        EXECUTE IMMEDIATE 'ALTER INDEX ' || rec.index_name || ' REBUILD';
    END LOOP;
END;
/

-- 表領域の作成
CREATE TABLESPACE app_data DATAFILE '/oracle/oradata/app_data01.dbf' SIZE 2G AUTOEXTEND ON;
CREATE TABLESPACE app_index DATAFILE '/oracle/oradata/app_idx01.dbf' SIZE 1G AUTOEXTEND ON;

-- ユーザー作成
CREATE USER app_user IDENTIFIED BY secure_pass
    DEFAULT TABLESPACE app_data
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON app_data
    QUOTA UNLIMITED ON app_index;

GRANT CREATE SESSION, CREATE TABLE, CREATE INDEX TO app_user;

CREATE USER dev_tanaka IDENTIFIED BY dev_pass
    DEFAULT TABLESPACE dev_data
    TEMPORARY TABLESPACE temp
    QUOTA 500M ON dev_data;

-- 500MB を超えると CREATE TABLE / INSERT でエラー
-- → 開発者がディスクを使い過ぎるのを防止

-- (1) 新しい表領域を作成
CREATE TABLESPACE new_data DATAFILE '/oracle/new_data01.dbf' SIZE 5G AUTOEXTEND ON;

-- (2) ユーザーのデフォルト表領域を変更
ALTER USER app_user DEFAULT TABLESPACE new_data;
ALTER USER app_user QUOTA UNLIMITED ON new_data;

-- (3) 既存テーブルを移動
ALTER TABLE app_user.orders MOVE TABLESPACE new_data;
-- テーブルごとに MOVE + インデックス REBUILD が必要

-- QUOTA 設定の全一覧（UNLIMITED / 具体的なサイズ / 使用量）
SELECT u.username, u.default_tablespace,
       NVL(q.tablespace_name, '(no quota)') AS quota_ts,
       CASE WHEN q.max_bytes = -1 THEN 'UNLIMITED'
            WHEN q.max_bytes IS NULL THEN '-'
            ELSE ROUND(q.max_bytes/1024/1024) || 'MB'
       END AS quota,
       ROUND(NVL(q.bytes,0)/1024/1024, 1) AS used_mb
FROM dba_users u
LEFT JOIN dba_ts_quotas q ON u.username = q.username
WHERE u.username NOT IN ('SYS','SYSTEM','ANONYMOUS','XDB','DBSNMP','AUDSYS')
ORDER BY u.username, q.tablespace_name;