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

[oxya_oxya2]

Hello,

As requested here are the details out of DB02

Sample :

<h4>

Tablespace Size (kb) Free (kb) Used (%) Tab/ind Extents AutoExt (kb) Used (%) Status

PSAPB3TABI 61.605.520 10.485.760 82 6 143 Off 82 ONLINE

list of index tables in tablespace PSAPB3TABI :

Owner Object Type Tablespace KBytes Blocks Extents MaxExtents Next (K)

SAPR3 EKBE~0 INDEX PSAPB3TABI 327.680 40.960 5 1- 327.680

SAPR3 EKBE~A INDEX PSAPB3TABI 327.680 40.960 5 1- 327.680

SAPR3 EKBE~B INDEX PSAPB3TABI 327.680 40.960 5 1- 327.680

SAPR3 EKBE~Z1 INDEX PSAPB3TABI 1.966.080 245.760 6 1- 327.680

SAPR3 ZARIXMM2~0 INDEX PSAPB3TABI 4.259.840 532.480 64 1- 327.680

SAPR3 ZARIXMM2~REF INDEX PSAPB3TABI 3.932.160 491.520 58 1- 327.680

Total 11.141.120 1.392.640 143

Hereunder the difference we found:

Diff : 61 605 520 - 11 141 120 = 50 464 400

</h4>

Best regards,

Willy

[fidel_vales]

hi,

could you provide the output of the following queries to try to find out where the space is:

select round(sum(bytes)/1024) KB, count(*) nro, status 
from dba_data_files 
where tablespace_name = 'PSAPB3TABI' group by status;
select round(sum(bytes)/1024) KB, count(*) from 
dba_free_space 
where tablespace_name = 'PSAPB3TABI';
select round(sum(bytes)/1024) KB, count(*), sum(extents) 
from dba_segments 
where tablespace_name = 'PSAPB3TABI';
select round(sum(bytes)/1024) KB, count(*), segment_type 
from dba_extents 
where tablespace_name = 'PSAPB3TABI'
group by segment_type;

[former_member204746]

try this:

sqlplus "/ as sysdba"

alter tablespace PSAPB3TABI coalesce;

you can also consider a full tablespace reorg. check SAP note 646681. this should be quick and can even be done online with no downtime as you will only be reorging indexes.

[Former Member]

Hi,

Please post details about DB? If oracle check tablespace autoextend and maximum file size.

Manoj