Oracle Databaseのスキーマ毎（ユーザ毎）表領域使用量の制限と確認

SQL ツール

対応バージョン

Oracle Database 10.2 - 19.6

2020-12-01記事公開

ここではスキーマに割り当てるデータ容量の最大サイズを変更するSQLについて記載します。UNLIMITED TABLESPACEシステム権限がスキーマに付与されている場合はそちらが優先されます。システム表領域または他システム用の表領域などを使われるような懸念があり、制限をかけたい場合はQUOTA句による指定で対応する必要があります。

スキーマの割り当てサイズの変更

以下の文でスキーマの表領域ごとの使用量を変更できます。

alter user [スキーマ名] quota [最大割当量(例:100M)] on [表領域名];

1	alter user [スキーマ名] quota [最大割当量(例:100M)] on [表領域名];

無制限に割り当てる場合はunlimitedを指定します。

alter user [スキーマ名] quota unlimited on [表領域名];

1	alter user [スキーマ名] quota unlimited on [表領域名];

スキーマの割り当てサイズの確認

以下の文で割り当てサイズの確認ができます。 Max_MBytesが-1は無制限を示します。

set linesize 120 pagesize 1000 
col USERNAME format a30
col TABLESPACE_NAME format a30
select USERNAME
     , TABLESPACE_NAME
     , BYTES / 1024 / 1024 as MBytes
     , decode(MAX_BYTES
         , -1 , -1
         , MAX_BYTES / 1024 / 1024) as Max_MBytes
     , ROUND((BYTES/MAX_BYTES)*100, 1) as "TS_Usage(%)"
  from DBA_TS_QUOTAS
 order by USERNAME, TABLESPACE_NAME;

set linesize 120 pagesize 1000

col USERNAME format a30

col TABLESPACE_NAME format a30

select USERNAME

, TABLESPACE_NAME

, BYTES / 1024 / 1024 as MBytes

, decode(MAX_BYTES

, -1 , -1

, MAX_BYTES / 1024 / 1024) as Max_MBytes

, ROUND((BYTES/MAX_BYTES)*100, 1) as "TS_Usage(%)"

from DBA_TS_QUOTAS

order by USERNAME, TABLESPACE_NAME;

自身のスキーマの割り当てサイズが見たい場合は、DBA_TS_QUOTASをUSER_TS_QUOTASに変更します。USERNAMEは存在しないので、SQLから省く必要があります。

set linesize 120 pagesize 1000 
col TABLESPACE_NAME format a30
select TABLESPACE_NAME
     , BYTES / 1024 / 1024 as MBytes
     , decode(MAX_BYTES
         , -1 , -1
         , MAX_BYTES / 1024 / 1024) as Max_MBytes
     , ROUND((BYTES/MAX_BYTES)*100, 1) as "TS_Usage(%)"
  from USER_TS_QUOTAS
 order by TABLESPACE_NAME;