[DB Space] Actual Database Usage vs File Size

[DB Space] Actual Database Usage vs File Size

  
Forge Component
(3)
Published on 7 Mar by Carlos Alfaro
3 votes
Published on 7 Mar by Carlos Alfaro
Hi,

From what I undestand, oracle does not shrink the database file when data is deleted.

Does this module track actual database usage or file size of the data file?

Thanks.


Hello,

The data shown on Oracle is read from the DBA_EXTENTS system table, by summing the BYTES column per Table (SEGMENT_NAME).

Hope this answers your question. Anything else you need, feel free to ask.

Thank you.

CA
Thanks for your quick response.

Should it be querying DBA_FREE_SPACE instead to show the actual database usage instead of showing reserved space?

Another link to the issue.

Thanks.

Hello again,

The objective is to show the space occupied by each Table, that is why the DBA_EXTENTS system table is used.
The DBA_FREE_SPACE system table only has information on the free space of the whole tablespace.

This is a simple example of the data obtained from DBA_EXTENTS:
SELECT segment_name,
  sum(bytes)/1024 as kb
FROM sys.dba_extents
WHERE owner = sys_context('USERENV', 'CURRENT_SCHEMA')
  and segment_type = 'TABLE'
GROUP BY segment_name
ORDER BY 2 DESC

Thank you again for your interest in this tool, hope it fits your needs.

CA