5

As it says on the Oracle XE overview page:

Oracle Database XE can be installed on any size host machine with any number of CPUs (one database per machine), but XE will store up to 4GB of user data, use up to 1GB of memory, and use one CPU on the host machine.

Now if I want to monitor the database to see how much user data is in use or how much memory is the database using, how would I do that? It is possible to monitor these values from Oracle Application Express, but I want to monitor the database from a centralized monitoring system. Oracle XE documentation presents a query which returns Flash Recovery Area usage, so I'm guessing there's a similar query for user data usage also.

SELECT
NAME,
TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE,'999,999,999,999')
   AS SPACE_AVAILABLE,
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
   AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;

Also, what happens when user data is above the limit?

ivan_pozdeev
  • 33,874
  • 19
  • 107
  • 152
thevilledev
  • 2,367
  • 1
  • 15
  • 19

2 Answers2

6

Shamelessly taken from the Oracle FAQ website, here is a query that checks used space by tablespace:

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

By default, user data is the space used in the USERS tablespace.

As for what happens when you are at the limit, I can only guess that:

  • You will get some out of space error when trying to insert data (some Oracle error message always seem to come from out of space anyway ;-)
  • You may be in for the biggest check in your life...
Mac
  • 8,191
  • 4
  • 40
  • 51
  • Thanks, that seems to do the trick. It seems that Oracle XE gives an error saying "ORA-12952: The request exceeds the maximum allowed database size of 4 GB." I hope 4 GB is enough. :-) – thevilledev Mar 27 '11 at 11:48
  • The 11g Express Edition (yet to be released) has been described as going up to 10GB. – Gary Myers Mar 27 '11 at 23:05
  • @GaryMyers, 11g XE [allows 11GB of user data](http://www.oracle.com/technetwork/database/express-edition/overview/index.html). – bwDraco Jan 31 '12 at 04:09
  • `You may be in for the biggest check in your life...`: If you need to store more data, and [Oracle Database](http://www.oracle.com/database) is too expensive, try [MySQL](http://www.mysql.com) or [PostgreSQL](http://www.postgresql.org) instead. – bwDraco Jan 31 '12 at 04:13
1

A closely-related matter: tablespace usage by user (to find out where the space is going):

select owner,tablespace_name,
sum(bytes)/1024/1024 as mbytes
from dba_segments
group by owner,tablespace_name
order by mbytes desc;
ivan_pozdeev
  • 33,874
  • 19
  • 107
  • 152