I need to make a query in Oracle SQL that allows me to know the volume of data used by a table within the database. The query I have for it is the following
select segment_name table_name,
SUM (BYTES) AS "[Bytes]",
SUM (BYTES) / 1024 AS "[Kb]",
SUM (BYTES) / (1024*1024) AS "[Mb]",
SUM (BYTES) / (1024*1024*1024) AS "[Gb]"
from user_extents
where segment_type='TABLE'
and segment_name = 'nombre_tabla'
group by segment_name
which returns me perfectly the weight of the table in bytes, kilos, megabytes and gigas. but this table inside has a date column in which I need to filter because I need to know the weight of the last data entered into the table. A normal query that I use to filter by date is the following.
SELECT * FROM nombre_tabla where fecha > '01/02/20' and fecha < '02/02/20'
What I can't do is join these two queries, I tried several ways but not one of them works. Does anyone know how I could get the volume of the data that exists in the date range?
Calculating the exact size of a given set of records is not simple.
Oracle has a very flexible and therefore complex storage structure: Tablespace < Segment < Extent < Oracle Data Block. If you imagine it as small boxes within large boxes, you can know the size of the boxes but not the size of the object that can be disassembled and distributed in several small boxes.
On the other hand, Oracle will try to optimize in such a way that a record takes up as little space as possible; these optimizations will depend on the data type and the value itself.
You could get a good approximation of the size with the function
vsize()
, which returns the number of bytes in a value, and it would be something like:Another cheaper approach would be using the statistics : from the table
user_tables
multiply the value of the columnavg_row_size
(average row size) by the number of records inserted.