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