Fix ORA-08104

While rebuilding index my toad session was hanged and I decided to kill session but it also hanged

SQL> alter index xxx.xxxxx rebuild online parallel 8;

To fix it I connected db with new sesssion and got object_id of mentioned index

 SQL> select object_id obj#, i.status from dba_indexes i, dba_objects o  
 where o.object_name=i.index_name and  
 i.index_name='LOYAL_RES_IDX1';

--Note flags

SQL> select flags from ind$ where obj#=356001; 

Using DBMS_REPAIR package clean index

SQL> declare  
 isclean boolean;  
 begin  
 isclean :=false;  
 while isclean=false loop  
 isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN  
 (dbms_repair.all_index_id,dbms_repair.lock_wait);  
 dbms_lock.sleep(10);  
 end loop;  
 end;  
 /  

--Note flags

 SQL> select flags from ind$ where obj#=356001; 

As you see it was decreased and normal

Brief practice about shrink extents

Today after analyzing segments I found out some tables have 0 rows but segment size more than 2 GB. For practice purpose I simulated some actions on test environment. To do that I created a simple user, grant him essential grants, created table, load some data etc.

 create user ulfet identified by ulfet;  
 grant connect, resource to ulfet; 

--create table with 1 mln rows

 create table ulfet.btt nologging as  
 SELECT   
        LEVEL id,      
        dbms_random.string ('U', 10) text1,   
        reverse(dbms_random.string ('U', 10)) text2  
 FROM   dual  
 CONNECT BY   LEVEL   <= 1000000;  

--Check segment, block size

 SQL> select  
           bytes,   
           bytes/1024/1024 SIZEMB,   
           extents,   
           s.blocks,  
           t.num_rows,   
           t.last_analyzed,   
           t.blocks  
 from dba_segments s, dba_tables t  
 where s.segment_name = t.table_name   
 and segment_name='BTT'; 

 BYTES,SIZEMB,EXTENTS,BLOCKS,NUM_ROWS,LAST_ANALYZED,BLOCKS_1  
 37748736,36,51,4608,,,  

--compute statistics

 analyze table ulfet.btt compute statistics; 

--Check again segment, block size

SQL> select  
           bytes,   
           bytes/1024/1024 SIZEMB,   
           extents,   
           s.blocks,  
           t.num_rows,   
           t.last_analyzed,   
           t.blocks  
 from dba_segments s, dba_tables t  
 where s.segment_name = t.table_name and segment_name='BTT';  

 BYTES,SIZEMB,EXTENTS,BLOCKS,NUM_ROWS,LAST_ANALYZED,BLOCKS_1  
 37748736,36,51,4608,1000000,14.10.2015 10:03:04,4504 

--inserted 15 000 000 rows

 insert into ulfet.btt  
 select * from ulfet.btt;  

 insert into ulfet.btt  
 select * from ulfet.btt;  

 insert into ulfet.btt  
 select * from ulfet.btt;  

 insert into ulfet.btt  
 select * from ulfet.btt;  

 select count(1) from ulfet.btt;  
 --16000000  

commit;

Now check segment size

 SQL> select  
           bytes,   
           bytes/1024/1024 SIZEMB,   
           extents,   
           s.blocks,  
           t.num_rows,   
           t.last_analyzed,   
           t.blocks  
 from dba_segments s, dba_tables t  
 where s.segment_name = t.table_name and segment_name='BTT';  

 BYTES,SIZEMB,EXTENTS,BLOCKS,NUM_ROWS,LAST_ANALYZED,BLOCKS_1  
 587202560,560,141,71680,1000000,14.10.2015 10:03:04,4504 

--gather table`s stat again for fresh statistics

 EXEC DBMS_STATS.gather_table_stats('ULFET', 'BTT');

--Check segment and block size

 SQL> select  
           bytes,   
           bytes/1024/1024 SIZEMB,   
           extents,   
           s.blocks,  
           t.num_rows,   
           t.last_analyzed,   
           t.blocks  
 from dba_segments s, dba_tables t  
 where s.segment_name = t.table_name and segment_name='BTT';  

 BYTES,SIZEMB,EXTENTS,BLOCKS,NUM_ROWS,LAST_ANALYZED,BLOCKS_1  
 587202560,560,141,71680,16000000,14.10.2015 10:10:15,71377

Now delete rows

 delete from ulfet.btt;  
 commit; 

--Again take statistics

 EXEC DBMS_STATS.gather_table_stats('ULFET', 'BTT');

--Check again

 SQL> select  
           bytes,   
           bytes/1024/1024 SIZEMB,   
           extents,   
           s.blocks,  
           t.num_rows,   
           t.last_analyzed,   
           t.blocks  
 from dba_segments s, dba_tables t  
 where s.segment_name = t.table_name and segment_name='BTT';  

 BYTES,SIZEMB,EXTENTS,BLOCKS,NUM_ROWS,LAST_ANALYZED,BLOCKS_1  
 587202560,560,141,71680,0,14.10.2015 10:17:30,71377 

As you see extent not changed

Ulfet user has quota on two tablespaces

SQL> select   
     tablespace_name,   
     bytes,   
     bytes/1024/1024 SIZEMB,   
     blocks   
 from dba_ts_quotas   
 where username='ULFET';

 TABLESPACE_NAME,BYTES,SIZEMB,BLOCKS  
 DATA01,0,0,0  
 DATA001,624951296,596,76288  

--Move segment to another tablespace for shrink purpose

SQL> alter table ulfet.btt move tablespace DATA01;

Now check again

 SQL> select   
     tablespace_name,   
     bytes,   
     bytes/1024/1024 SIZEMB,   
     blocks   
 from dba_ts_quotas   
 where username='ULFET'; 

 TABLESPACE_NAME,BYTES,SIZEMB,BLOCKS  
 DATA01,65536,0,0625,8  
 DATA001,37748736,36,4608  

--Check segment size and extents

SQL> select  
           bytes,   
           bytes/1024/1024 SIZEMB,   
           extents,   
           s.blocks,  
           t.num_rows,   
           t.last_analyzed,   
           t.blocks  
 from dba_segments s, dba_tables t  
 where s.segment_name = t.table_name and segment_name='BTT'; 

 BYTES,SIZEMB,EXTENTS,BLOCKS,NUM_ROWS,LAST_ANALYZED,BLOCKS_1  
 65536,0,0625,1,8,0,14.10.2015 10:17:30,71377