Oracle Extension collects all data but the Database size

[ccapellanvil]

Hello

I have installed the Oracle Database Extension in my MaaS environment to monitor several Oracle DBs.
Each of the Oracle Database Entities show the data correctly (Datafile status, Tablespaces, Query performance, longest running querys, etc).
However, Database size parameters and Database size charts return 0 B on each field. It's not a problem as "Data not available", but a problem with the return value being 0 B. 
Is there any extension configuration/parameter I'm missing?

Many thanks in advance, best regards.

[AntonPineiro]

Hi,

Can you show some screenshot?

Are you using latest Oracle extension version?

Best regards

[ccapellanvil]

Hello,

Thanks for your response. This is what I currently see on the Database summary:

I'm currently using the latest Oracle Database extension version available, 3.7.5.

[AntonPineiro]

Hi,

I see same behaviour. Maybe is a bug, or something in relation Dynatrace user created in database side. I would say double check if Dynatrace user has these privileges.

Anyway, I would raise open a support ticket.

Best regards

[AntonPineiro]

Hi @TomásSeroteRoos ,
I was checking hundred databases and "max size" is always 0B. This is happening with version 3.7.5, 3.6.2 and 2.5.1 about Oracle extension version.

Do you think that is a bug?

Thank you!

Best regards

[TomásSeroteRoos · Accepted solution]

Hi @AntonPineiro,

Thanks for pinging me! That particular metric (com.dynatrace.extension.sql-oracle.pdb-max_size), is captured from the MAX SIZE column of V$CONTAINERS view. From Oracle's documentation:

MAX_SIZE

NUMBER

Shows the maximum amount of disk space (in bytes) that can be used by data and temp files in the container

As far as I understand it, MAX_SIZE is a parameter that can be used to limit the size of a certain PDB. My guess is that when this parameter is not set (which it isn't by default afaik), then it shows 0, to indicate that there is no MAX_SIZE limitation. I expect that this is why you are seeing 0B reported by the metric on so many databases.

[AntonPineiro]

Hi,

Thank you so much for your fast answer!

Best regards

[mn_24]

Hi Tomas,

but it will be good if we can see actually the Database Size. Up until now we can calculate this manually but why not to be visible with one glance in a Dashboard and in the Oracle Database page.

The Oracle database size is actually the total allocated(autoextend included) value of all datafiles and by looking at the Used space we can simply check/see where we are. Something like TotalSpace is 2.4TB but UsedSpace is 1.9TB so we know that currently the database is 1.9TB big in size. You have already this data but I can not figure out how to SUM from each row into one single value...:

So what I am suggesting is to add one new metric which we could use in the dashboard: "Database size" with the following SQL query behind:

col "Database Size" format a20

col "Free space" format a20

col "Used space" format a20

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"

, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -

round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"

, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"

from (select bytes

from v$datafile

union all

select bytes

from v$tempfile

union all

select bytes

from v$log) used

, (select sum(bytes) as p

from dba_free_space) free

group by free.p

/

which will give us the following result:

Database Size         Used space            Free space

-------------------- -------------------- --------------------

2388 GB                  1928 GB                  460 GB

I was about to open an Idea for this but saw the topic is already around the community.

[TomásSeroteRoos · Accepted solution]

Hi @mn_24,

If it's just a matter of summing those columns, wouldn't this work?

com.dynatrace.extension.sql-oracle.tablespaces.usedSpace:filter(<your-filter>):splitBy(tablespace):sum:splitBy()

And you could do something similar for the allocated and free space metrics, to get the total free space and total database size.

[mn_24]

Hi,

"Last value" was missing in my trying to see the final result as expected - thank you! It is still quite not visually cool but I could live with it as it is: