All geek questions in one place

Monitoring Tablespace Usage in Oracle XE

As the Oracle XE review page says:

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

Now, if I want to control the database to find out how much user data is used or how much memory is used in the database, how would I do it? You can track these values ​​from Oracle Application Express, but I want to control the database from a central monitoring system. The Oracle XE documentation is a query that returns using the Flash Recovery Area, so I assume there is a similar query for using user data.

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 exceeds the limit?

+4
source share
2 answers

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

 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 table space.

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

  • When you try to insert data, you get some error outside space (in any case, any Oracle error message always leaves space ;-)
  • You can do the biggest test of your life ...
+5
source

A closely related question: using a tablespace by a user (to find out where the space goes):

 select owner,tablespace_name, sum(bytes)/1024/1024 as mbytes from dba_segments group by owner,tablespace_name order by mbytes desc;
+1
source

Source: https://habr.com/ru/post/1345403/

More articles:


All Articles