Disk Space Used by Table 2

[Michael42]

Hello,

In an Oracle9i Database on Solaris 8 I have a table that spans multiple tablespaces (via partitions). Because of this it is not clear how much disk space it is using.

Can anyone please share SQL or a script that displays the disk space used by a table?

Thanks,

Michael42

 

[carp]

For the overall table,

SELECT bytes FROM dba_segments
 WHERE segment_type = 'TABLE'
   AND owner = 'your_table_owner_name'
   AND segment_name = 'your_table_name';

Note that the owner name and table name need to be in upper case.

You could query in dba_extents and get the amount of space that is being taken up in each data file, if desired.

 

[SantaMufasa]

Michael,

My "SchemaSegmentUsage.sql" script probably does what you want. In the version, below, the script lists all segments (e.g. tables and indexes) that exceed 10 million characters. You can certainly change the script to just query for a specific table by replacing "where bytes > 10000000" with "where segment_name = '<your table name>'. Here, then, is the script and some sample output. (It does take a few seconds to run, so be patient.):

set linesize 200
set pagesize 50
col a format a50 heading "Segment"
col b format a10 heading "Segment|Type"
col c format 999,999,999,999 heading "Storage|Usage"
spool SchemaSegmentUsage.txt
break on report
compute avg count sum of c on report
select segment_name a, segment_type b, bytes c
from user_segments
where bytes > 10000000
order by bytes
/
spool off
prompt
prompt "Wrote 'SchemaSegmentUsage.txt'
prompt

Segments Exceeding 10MB Storage Consumption:
                                                   Segment             Storage
Segment                                            Type                  Usage
-------------------------------------------------- ---------- ----------------
DD_DATA_IMPLEMENTATIONS                            TABLE            12,582,912
ER_LOG_EVENT                                       TABLE            12,582,912
...
TFAX_AX_RUN_OBJECT_N2                              INDEX           454,033,408
TFAX_AX_RESULT                                     TABLE           672,137,216
TFAX_AX_RUN_OBJECT                                 TABLE         1,610,612,736
                                                              ----------------
avg                                                                135,543,286
count                                                                       53
sum                                                              7,183,794,176
*******************************************************************************

Let us know your thoughts about this method.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SantaMufasa]

Sorry "Qwix-Draw Carp"...you are just too fast a shot.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[carp]

Only because I give shorter (and generally, less useful) responses! If I were to go to the pains and lengths that you do, I would not be nearly as fast!

 

[Michael42]

Guys,

Thanks for posting. This is exactly what I was looking for.

Thanks,

Michael42

 

[Michael42]

Some super cool options would be to:

1. Display how much data (bytes not records) goes into a table during a time period.

2. Display total size of partitioned tables.

Are determining these values possible?

Thanks,

Michael42

 

[SantaMufasa]

1. Display how much data (bytes not records) goes into a table during a time period.

Yes, possible, but it would require your building either some "triggering" or "periodic" infrastructure:

Trigger: Each time a record is INSERTed, UPDATEd, or DELETEd, tally the net change to a segments-summary table.

Periodic: Each day, run a "Net-Change" report against the size values of your tables from the volumetric information in DBA_SEGMENTS (following statistical re-gathering).

In each case, there is a "price to pay" for gathering/keeping this information, which you must justify against business needs/value: Specifically, gathering/keeping such information had better show a net reduction to business expenses or a net increase to business revenue. Is there such a justification in your case?

2. Display total size of partitioned tables.

It seems to me that there should be an easy way to do this...I'm simply not a "Partition Kinda Guy", so I'll leave such a solution/assertion to others more adept on this topic.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[carp]

Total size of partitioned tables is almost the same as a non-partitioned table:

SELECT sum(bytes) FROM dba_segments
 WHERE segment_name = 'MY_TABLE_NAME';

You can also easily determine more information about partitioned tables with a slight variation:

SELECT count(*) partitions, 
       min(bytes) smallest, 
       avg(bytes) average,
       max(bytes) largest,
       sum(bytes) total  
  FROM dba_segments
 WHERE segment_name = 'MY_TABLE_NAME';

As an example:

    SELECT count(*) partitions,
           min(bytes) smallest,
           avg(bytes) average,
           max(bytes) largest,
            sum(bytes) total
      FROM dba_segments
     WHERE segment_name = 'COSTS';

PARTITIONS   SMALLEST    AVERAGE    LARGEST      TOTAL
---------- ---------- ---------- ---------- ----------
        12    2097152 2271914.67    3145728   27262976