Oracle – Table Size Unaffected After Deleting Rows

oracle

When I ran the below query
select bytes/1024/1024,segment_name,owner from dba_segments where tablespace_name='DATA_L1' and segment_name='Step_Status';

I got the below result for a segment which is a table named 'Step_Status'

BYTES/1024/1024         SEGMENT_NAME                OWNER
-----------------------------------------------------------------
 3192                    STEP_STATUS                FLSTD04

It had about 18 million entries

select count(*) from flstd04.step_status;

  COUNT(*)
----------
  18178090

After deleting nearly 4 million enteries

select count(*) from flstd04.step_status;

  COUNT(*)
----------
  14114186

When I again checked the size of the table using dba_segments
I got the same result

select bytes/1024/1024,segment_name,owner from dba_segments where tablespace_name='DATA_L1' and segment_name='Step_Status';

  BYTES/1024/1024         SEGMENT_NAME                OWNER
    -----------------------------------------------------------------
   3192                    STEP_STATUS                FLSTD04

Why is it that even after deleting 4 million rows the size of the table is same?

Best Answer

Deleting data isn't expected to change the size of the table segment. It will create free space in blocks that are part of the table segment and it will probably create at least a few empty blocks. That free space will be made available for subsequent insert operations on the table (assuming you're doing conventional-path inserts) as well as update operations that cause the size of rows to increase.

If you want to actually shrink the size of the table segment (generally not a good idea if you're just going to insert more data into this table in the future), depending on the Oracle version, the type of tablespace, and whether you've enabled row movement you can probably shrink the segment

ALTER TABLE step_status
  SHRINK SPACE CASCADE;

Be aware, however, that this may be a very costly operation that may involve physically moving most or all of the rows in the table.

There is a chapter in the Database Administrator's Guide on reclaiming wasted space that discusses this and related issues in much more depth. It also walks through using the Enterprise Manager GUI and the various advisors to reclaim space either manually or automatically.

Related Solutions

Oracle 11g – Best Way to Delete Very Large Recordsets

The logic with 'A' and 'B' might be "hidden" behind a virtual column on which you could do the partitioning:

alter session set nls_date_format = 'yyyy-mm-dd';
drop   table tq84_partitioned_table;

create table tq84_partitioned_table (
  status varchar2(1)          not null check (status in ('A', 'B')),
  date_a          date        not null,
  date_b          date        not null,
  date_too_old    date as
                       (  case status
                                 when 'A' then add_months(date_a, -7*12)
                                 when 'B' then            date_b
                                 end
                        ) virtual,
  data            varchar2(100) 
)
partition   by range  (date_too_old) 
( 
  partition p_before_2000_10 values less than (date '2000-10-01'),
  partition p_before_2000_11 values less than (date '2000-11-01'),
  partition p_before_2000_12 values less than (date '2000-12-01'),
  --
  partition p_before_2001_01 values less than (date '2001-01-01'),
  partition p_before_2001_02 values less than (date '2001-02-01'),
  partition p_before_2001_03 values less than (date '2001-03-01'),
  partition p_before_2001_04 values less than (date '2001-04-01'),
  partition p_before_2001_05 values less than (date '2001-05-01'),
  partition p_before_2001_06 values less than (date '2001-06-01'),
  -- and so on and so forth..
  partition p_ values less than (maxvalue)
);

insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('B', date '2008-04-14', date '2000-05-17', 
 'B and 2000-05-17 is older than 10 yrs, must be deleted');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('B', date '1999-09-19', date '2004-02-12', 
 'B and 2004-02-12 is younger than 10 yrs, must be kept');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('A', date '2000-06-16', date '2010-01-01', 
 'A and 2000-06-16 is older than 3 yrs, must be deleted');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('A', date '2009-06-09', date '1999-08-28', 
 'A and 2009-06-09 is younger than 3 yrs, must be kept');

select * from tq84_partitioned_table order by date_too_old;

-- drop partitions older than 10 or 3 years, respectively:

alter table tq84_partitioned_table drop partition p_before_2000_10;
alter table tq84_partitioned_table drop partition p_before_2000_11;
alter table tq84_partitioned_table drop partition p2000_12;

select * from tq84_partitioned_table order by date_too_old;

Dodgy causes of ORA-12549

This is a resource issue.

The DB server cannot satisfy your queries due to the client host being configured incorrectly as-per the pre-requisites for an Oracle database client installation.

Ask your system administrators to verify that they have set the required number of open files, semaphores, shmax etc etc. Link to the Oracle documentation - I assume Linux, but Solaris docs are a link away from that.

Best Answer

Related Solutions

Oracle 11g – Best Way to Delete Very Large Recordsets

Dodgy causes of ORA-12549

Related Question