Free space in Index Tablespace

[oxya_oxya2]

Dear Support,

According to DB02 u201Ctablespace global viewu201D, the size of an Index tablespace is 60 GB and the free space is 10 GB. The u201Cdba_segmentu201D view of this tablespace (in DB02) shows us that the sum of the index sizes is less than 10 GB.

This means that we have: 60 GB allocated u2013 10 GB free u2013 10 GB used for the indexes = 40 GB

What has happened with de 40 GB ?

We rebuilt the indexes on a regular basis using the u201Calter index rebuild parallel 5u201D followed by a u201Calter index noparallel u201C statement. After the rebuild the Index Tablespace is free from temporary segments.

Thank you for your help

SEDINSKI Willy

[oxya_oxya2]

Dear Support,

As requested here are the details out of sql request :

SQL> select round(sum(bytes)/1024) KB, count(*) nro, status from dba_data_files where tablespace_name = 'PSAPB3TABI' group by status;

KB NRO STATUS

-

---

-

---

-

---

61605520 27 AVAILABLE

SQL> select round(sum(bytes)/1024) KB, count(*) from dba_free_space where tablespace_name = 'PSAPB3TABI';

KB COUNT(*)

-

---

-

---

10158080 10

SQL> select round(sum(bytes)/1024) KB, count(*), sum(extents) from dba_segments where tablespace_name = 'PSAPB3TABI';

KB COUNT(*) SUM(EXTENTS)

-

---

-

---

-

---

12779520 6 144

SQL> select round(sum(bytes)/1024) KB, count(*), segment_type from dba_extents where tablespace_name = 'PSAPB3TABI'

group by segment_type;

2

KB COUNT(*) SEGMENT_TYPE

-

---

-

---

-

---

47185920 144 INDEX

Best regards,

Willy

[fidel_vales]

hi,

good

what is your exact database version and what patches do you have installed?

you have an inconsistency between dba_segments and dba_extents.

I think that is caused by an oracle bug, but I cannot check it right now.

it is mentioned in one SAP note, you can also check.

OK, found it.

Take a look at SAP Note 1001558 DBA_SEGMENTS.BLOCKS wrong after parallel create index

even when the title is "blocks", "bytes" are also wrong.

Check if the patch mentioned in the note is installed

Edited by: Fidel Vales on Mar 30, 2009 9:53 PM