1

I have a requirement to check the space occupied by a specific table per day per system, just a short back ground i have some 10 systems from each system we process the daily etl loads and the counts can be observed based in the date field.

Database oracle 11g

size per GB

example

SYSTEM PROCESS_DATE count(*)

RETAIL 26.02.2021   100
PHARMACY 26.02.2021 200
BANKING  26.02.2021 300

query 1 - to check the daily counts per system

select distinct system,count(*) from AUDIT_SCH.DWH_ADT_TBL
where trunc(process_date)=trunc(sysdate)
group by system
order by count(*) desc;

but what i want is how to capture the space of daily loads consumed per system from this table ? , is this possible

it's confusing checking various suggestions below is the reference

How do I calculate tables size in Oracle

any suggestions with query ?

Data2explore
  • 452
  • 6
  • 16
  • 1
    Can you write a query against a table when you don't know the structure of the table? https://stackoverflow.com/help/minimal-reproducible-example – EdStevens Feb 26 '21 at 14:19

1 Answers1

0

Use the function VSIZE to sum the number of bytes used in each column, per system:

select
    system, count(*),
    round(sum
    (
        nvl(vsize(system), 0) +
        nvl(vsize(process_date), 0) +
        nvl(vsize(column1), 0) +
        nvl(vsize(column2), 0)
        --Add all other columns here
    )/1024/1024/1024) gb
from DWH_ADT_TBL
where trunc(process_date)=trunc(sysdate)
group by system
order by count(*) desc;

Unfortunately, calculating the size of things in a database can be ridiculously complicated. You may need to worry about:

  1. Overhead. The VSIZE function does not account for row overhead, block overhead, segment overhead, and unused space in files/ASM diskgroups/volumes, etc.
  2. Compression. If the table, tablespace, or LOBs are compressed or encrypted, the VSIZE will incorrectly return the uncompressed size.
  3. Indexes. VSIZE does not include index sizes. But if you're only interested in comparing systems, then the percentage of data will still be the same even if the absolute sizes are off. (Unless you have indexed columns that are only used by one system.)
  4. LOBs. You may need to use DBMS_LOB.GETLENGTH to calculate the size of LOBs. For CLOBs you may need to multiple the result by 2 depending on the characterset - for UCS2, each character uses 2 bytes.

But in practice the above query is still good enough to give you a decent understanding of where the space is used.

If you have multiple tables with many columns you could generate the queries using the data dictionary, by querying from DBA_TAB_COLUMNS.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • fantastic way to check , how to identify whether the results are in GB/MB/TB usually we put some thing like example - ROUND(BYTES) / 1024 / 1024 / 1024 AS GB but when i am trying to test it it's not working ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected" *Cause: *Action: Error at Line: 10 Column: 12 – Data2explore Feb 28 '21 at 20:22
  • after using the updated logic i am getting 0 rows – Data2explore Feb 28 '21 at 22:12
  • @rakesh That may be because of `trunc(process_date)=trunc(sysdate)` - maybe data isn't loaded on the weekend so there are no rows today? Try with `trunc(process_date) = date '2021-02-26'` instead. – Jon Heller Mar 01 '21 at 02:30
  • i have tried that as well i have also switched the past days i can see the counts and if i apply newly updated condition under the GB section 0 – Data2explore Mar 01 '21 at 07:57
  • @rakesh Maybe the data isn't large enough - try using `1024/1024` for MB instead of `1024/1024/1024` for GB. – Jon Heller Mar 01 '21 at 19:19