Tablespace is going to waste

Hi all,

I have an issue with a schema which appears to be taking up much more 
tablespace than it really needs.
I have done a few queries in SQLPLUS and these are the results :


SQL> select SUM(num_rows*avg_row_len) "Bytes Used" from dba_tables where 
OWNER='MY_REP';

Bytes Used
----------
  19452933


SQL> SELECT SUM(BYTES) "BYTES ALLOCATED" FROM DBA_SEGMENTS WHERE 
OWNER='MY_REP';

BYTES ALLOCATED
---------------
     4471848960


SQL> select num_rows*avg_row_len "Bytes Used" from dba_tables where 
table_name='OR_BINARYSTORE';

Bytes Used
----------
   4595448
      4320

SQL>

As you can see the schema MY_REP has got over 4 GB of tablespace allocated to 
it but is using only close to 20 MB.
The largest table in this schema is the OR_BINARYSTORE and this one is using  
up about 4.5 MB.

If I am not mistaken most of the tablespace here is going to waste.
Allocated to schema MY_REP : 4471848960
Used by schema MY_REP: 19452933

Any ideas how I could organize the usage of the tablespace so that there is 
less waste ?

Thanks,

A.

Check this writeup -- it deals with exactly the same issue you are facing:

   how_to_identify_tables_that_waste_too_much_space

And in regards to why this is happening?  One possibility is that the table is 
getting loaded and then all rows are deleted leaving the extents behind.  This 
is NOT necessarily a "bad" thing especially if the data-loading takes place on 
regular basis.  On the other hand, if the data-loading procedure uses:

   insert /*+ APPEND */ into table_X ...
   delete table_X ...

then it will constantly "waste" space in table_X because none of the "old" 
pre-allocated extents will be used, thus it will waste space with each data 
load.

Bottom line: you need to investigate what and how the development/application 
is using this table, but the procedure to reclaim the space is outlined in the 
link I provided above.

-HTH
- VItaliy

> Check this writeup -- it deals with exactly the same issue you are facing:
> 
>    how_to_identify_tables_that_waste_too_much_space
> 
> And in regards to why this is happening?  One possibility is that the table 
> is getting loaded and then all rows are deleted leaving the extents behind. 
>  This is NOT necessarily a "bad" thing especially if the data-loading takes 
> place on regular basis.  On the other hand, if the data-loading procedure 
> uses:
> 
>    insert /*+ APPEND */ into table_X ...
>    delete table_X ...
> 
> then it will constantly "waste" space in table_X because none of the "old" 
> pre-allocated extents will be used, thus it will waste space with each data 
> load.
> 
> Bottom line: you need to investigate what and how the 
> development/application is using this table, but the procedure to reclaim 
> the space is outlined in the link I provided above.
> 
> -HTH
> - VItaliy


Hi Vitaliy,

Thanks for the quick reply.
I logged in as the user who owns the schema with lot of waste,tried the script 
you suggested ( I did not modify it it any way) and got the following back :

SQL> select
  2     t.owner
  3  ,  t.table_name
  4  ,  t.num_rows
  5  ,  t.avg_row_len
  6  ,  round((t.num_rows*t.avg_row_len/1024)) est_could_be_kb
  7  ,  s.bytes/1024 curr_kb
  8  ,  round((s.bytes-(t.num_rows*t.avg_row_len))/1024) est_wasted_kb
  9  ,  s.tablespace_name
 10  from dba_tables t
 11  ,    dba_segments_dump s
 12  where t.table_name=s.segment_name
 13  and t.owner = s.owner
 14  and s.owner!='SYS'
 15  and (s.bytes-(t.num_rows*t.avg_row_len)) >= 100*1024*1024
 16  order by (s.bytes-(t.num_rows*t.avg_row_len)) desc;

OWNER           TABLE_NAME                       NUM_ROWS AVG_ROW_LEN 
EST_COULD_BE_KB    CURR_KB EST_WASTED_KB TABLESPACE_NAME
--------------- ------------------------------ ---------- ----------- 
--------------- ---------- ------------- --------------------
                                                                                
                 -------------
sum

no rows selected

SQL>
SQL>
SQL> set echo off
SQL>


Any ideas ?

Thanks,

A.

The script is looking for TABLES that waste at least 100mb (100*1024*1024) in 
your case there aren't.

I reread your OP and I am wondering if maybe there are other types of segments 
owned by MY_REP that are taking up all that space.

Try this:

select count(*), sum(bytes)/1024 kbytes, segment_type
from dba_segments
where owner = 'MY_REP'
group by segment_type;

- Vitaliy

> The script is looking for TABLES that waste at least 100mb (100*1024*1024) 
> in your case there aren't.
> 
> I reread your OP and I am wondering if maybe there are other types of 
> segments owned by MY_REP that are taking up all that space.
> 
> Try this:
> 
> select count(*), sum(bytes)/1024 kbytes, segment_type
> from dba_segments
> where owner = 'MY_REP'
> group by segment_type;
> 
> - Vitaliy


Hi Vitaliy,


Here is the output you asked for :


SQL> select count(*), sum(bytes)/1024 kbytes, segment_type from dba_segments 
where owner = 'MY_REP' group by segment_type;

  COUNT(*)     KBYTES SEGMENT_TYPE
---------- ---------- ------------------
        14       4928 LOBINDEX
        14    4271872 LOBSEGMENT
       121      39104 TABLE
       199      51136 INDEX


Looks lie tables are not wasting too much space.
Rather it is the LOBSEGMENT that is the culprit.
Can this be examined for possible wastage and then reorganized accordingly ?

Thanks,

A.

Take a look at this post -- 
ASKTOM: reclaim space of lob partition

Generally I wouldn't recommend storing large LOBs in Oracle 10g and below.  
Apparently 11g has improved the mechanism of dealing with LOB storage but I 
have not done much work in this area to speak of.

Instead of storing large LOBs in Oracle, store a pointer to a file in the 
table:

PK  Lob Desc    Lob
--- ----------- -----------------
1   myfile1.ext /path/myfile1.ext
2   myfile2.ext /path/myfile2.ext
....
....

And then write a routine to pull it to the client application.  For smaller 
lobs it's OK to stuff them into the table but store the LOB in it's own 
tablespace.

-HTH
- Vitaliy