sjpunshi

• Sep 25th, 2007

 

Query dba_segments, dba_free_space for the tablespace and table, if too many extents are avaiable, yet space availability is a problem, implies that the table is fragmented. Solution, export the segment. recreate and import, fragmentation is eliminated.

subha gunasekaran

 Profile Answers by subha gunasekaran 

• Jun 5th, 2008

 

To see fragmentation:

select sum(bytes)/1024 from  dba_data_files where tablespace_name='';
output: 10
select sum(bytes)/1024 from  dba_segments where tablespace_name='';
ouput :7
Select sum(bytes)/1024 from  dba_free_space where tablespace_name='';
ouput:5

subtract 10-7 = 3 and compare the result with dba_free_space(which is 5)
thus we can determine the fragmentation.

To solve this problem:
1.exp/Imp
2. recreate the table using create table table_name as select * from table_name;
3. alter table tablename move tablespace tablespace_name
4.alter tablespace tablespace coalesce.