ORA-12954: The request exceeds the maximum allowed database size of 12 GB

Today, I was gathering statistics on a table when I received ORA-12954: The request exceeds the maximum allowed database size of 12 GB. This threw me for a complete loop, because of a few things:

1. I had a USERS tablespace, which had plenty of free space available in it–I was only occupying about 2 GB of this tablespace.
2. I was only gathering stat on a table. This wasn’t anything that I anticipated taking up space in the USERS tablespace.

I Eventually Figured Out The Issue

The SYSAUX tablespace was 99.9% full, and when I was gathering stats on the table, Oracle was trying to grow the SYSAUX tablespace. Well, the USERS tablespace was big (slightly over 12gb) but it was unoccupied space.

Apparently, when Oracle figures up the 12GB limit, it takes into account the sum total of all of your tablespaces. (SYSTEM, SYSAUX, USER, etc). So the sum total of all tablespace sizes exceeded some threshold that Oracle wasn’t happy about.

The solution was just to take the file behind the USERS tablespace and shrink it down some. (USERS tablespace was around 12.335 gigabytes in size). I issued the following command to shrink it back down some:

alter database datafile '/opt/oracle/oradata/XE/XEPDB1/users01.dbf' resize 12g;

And then I was able to gather stats.

So bear in mind a few things if you get ORA-12954:

• Oracle apparently considers the “sum total” of all your tablespaces–SYSTEM, SYSAUX, USER, etc.
• Even if a tablespace is empty, if the data file behind the tablespace is big enough, that can cause you to exceed the 12GB threshold.
• Oracle actually gives you a little bit more than 12GB before it starts complaining.

Useful queries:

-- Guery to tell you how your 
-- tablespaces are being allocated
select nvl(b.tablespace_name
     , nvl(a.tablespace_name, 'UNKOWN')) name
     , kbytes_alloc / 1024 / 1024 gbytes_allocated
     , (kbytes_alloc-nvl(kbytes_free,0)) / 1024 / 1024 gbytes_used
     , nvl(kbytes_free,0) / 1024 / 1024 gbytes_free
     , to_char(((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100, 'FM990.0') || '%' pct_used
from (select sum(bytes)/1024 Kbytes_free
           , tablespace_name
      from sys.dba_free_space
      group by tablespace_name ) a
   , (select sum(bytes)/1024 Kbytes_alloc
           , sum(maxbytes)/1024 Kbytes_max
           , tablespace_name
      from sys.dba_data_files
      group by tablespace_name
      -----------
      union all
      -----------
      select sum(bytes)/1024 Kbytes_alloc
           , sum(maxbytes)/1024 Kbytes_max
           , tablespace_name
      from sys.dba_temp_files
      group by tablespace_name) b
where a.tablespace_name (+) = b.tablespace_name;

-- See what is taking up space in your SYSAUX tablespace
select *
from V$SYSAUX_OCCUPANTS;

-- See how big each of the datafiles you've allocated to each tablespace are
select name
     , to_char(bytes / 1024/1024/1024, '99,990.0') || 'GB' datafile_size
from v$datafile;

Hopefully this more fully explains the ORA-12954: The request exceeds the maximum allowed database size of 12 GB error.

Free Oracle SQL Tuning Guide

Check out my FREE guide! 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets