Ulfat`s DataBase Blog

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

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,NU...