Oracle Express Space Utilization 3

[RobertT687]

I have a question regarding how the Oracle 10g Express Edition calculates the 'Space Allocation' amount that is displayed on the Administration > Storage page. For instance on my desktop installation the Space Allocated figure is given as 770mb. That figure is 105mb Lower than the figure I get if I run:

Select 'Allocated Perm (MB) : '|| round(sum(bytes)/1024/1024/1024,2) mb from dba_data_files

which in my case is 875mb.

Select 'Consumed Total (MB) : '|| round(sum(bytes)/1024/1024/1024,2) mb from dba_extents

gives a total of 669.13mb.

Is there some other total I need to add or subtract from either of these amounts to get the 770mb figure?

My experience is primarily with MSSQL so I'm not sure what I need to look for in Oracle.

Thanks in advance.

 

[SantaMufasa]

Yes, Robert, there is more to the calculation. First of all, your "...dba_extents" calculation totals up actual consumption by objects, whereas "...bytes...dba_data_files" calculation totals up total disk-space currently allocated to data files. Both are important pieces of information to a DBA, but the difference equals free space in currently allocated data files, that is available for expansion/creation of new database objects.

Let us know if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[RobertT687]

Still leaves my original question.
Given the two figures of 875mb and 669.13mb, what is ORACLE XE omitting from the total of the Allocated SELECT statement to get 770mb and what am I failing to include in the Consumed SELECT statement that adds up to 770mb?

If I just need to assume about 100mb overhead, that's OK, but I would like to know what Oracle is looking at.

Thanks again!!

 

[SantaMufasa]

My apologies, Robert, for my not reading your original post more carefully. I do not have Oracle Express 10g installed anywhere for me to be able to reconcile what you are seeing, so I must defer to another one of our Oracle gurus here.

I will, however, look forward to seeing the resolution to your issue.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[RobertT687]

For SantaMufasa:
No problem. This is one I'm trying to run down for a medium priority task.
So far just about everything I've seen on Google or the Oracle sites regarding space utilization for XE refers me to the DB Home Page.
The reason I want to find a way to compute total utilization like XE is that my company maintains over 100 remote PCs with XE as the database for several custom applications. We want to find a way to accurately monitor the database growth without user intervention as part of our periodic automated application updates. If we need to assume a fixed 100mb overhead that's OK. On the other hand if that missing chunk of space isn't fixed overhead we could be running into maximum space trouble sooner than anticipated.

Thanks again for looking at this!!

 

[jaxtell]

Well I'm curious now. Here are the same stats from my XE database:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


Tablespace Percent Used Allocated (MB) Used (MB) Datafiles
MSS 1.12% 1,024.00 11.44 1
MSSX 0.79% 500.00 3.94 1
SYSAUX 96.63% 330.00 318.88 1
SYSTEM 98.41% 390.00 383.81 1
UNDO 5.86% 210.00 12.31 1
USERS 1.63% 100.00 1.63 1
report total: 2,554.00 732.00 6
1 - 6


Space Allocated: 2,344 MB
Available: 2,776 MB
Physical Limit: 5,120 MB
Percent Used: 46%

Allocated Perm (MB) : 2.49

Consumed Total (MB) : .71


Yeah, its still pretty tiny. Hope this info helps you.

 

[RobertT687]

I think I've found the answer.

The disclaimer on the db Home Page display says clearly the value shown excludes the UNDO space. In my case the Allocated total of 875mb less the UNDO space of 105mb = 770mb as shown. The same is true for the stats shown by jaxtell (2554 - 210 = 2344).

This tells me the right thing to do is gather stats for both figures and let the boss decide which he wants to track.

Thanks jaxtell and SantaMufasa.
Have a star each on me!

 

[SantaMufasa]

Actually, Robert, I didn't do much but acknowledge my lack of attention to your original post.

If anyone deserves a

, it is you, for properly answering your own question. So, please accept a

.

For future readers of this thread, the proper answer lies in Robert's excellent detective work.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[RobertT687]

SantaMufasa

You were a great help by verifying that I on the right track with my queries.

Thanks for Star!