1

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?

6

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.

| improve this answer | |
  • Thanks Justin for your answer .I wanted to ask ,if the activity of shrinking the table critical.I was going to follow these steps :1) Enabling the row movement for the table 2)Shrinking the table using the query you have provided 3) Disabling the row movement.Do I have to take care of anything else need to be done – user1 Jun 17 '15 at 6:32
  • @user1 - That's hard to answer-- you, presumably, know your system and you, presumably, have tested the change in your lower environment. You're in a much better position to know if you need to do anything else. You know, for example, what sort of tablespace the table is in. You know whether your applications depend on the rowid being stable which wouldn't be the case during the shrink operation. You know whether the physical reorganization is going to impact application performance and whether that is an acceptable tradeoff. – Justin Cave Jun 17 '15 at 6:40
  • ALTER TABLE XXX SHRINK SPACE CASCADE ERROR at line 1: ORA-10636: ROW MOVEMENT is not enabled – Toolkit Apr 26 '19 at 9:51
  • @Toolkit - That's why I said it depends on things like whether you've enabled row movement ALTER TABLE xxx ENABLE ROW MOVEMENT. If you haven't, you can't do a SHRINK SPACE. Be aware that enabling row movement has other impacts that you should read up on in the documentation. Most applications won't have an application but some store the rowid for rows and will have major problems. – Justin Cave Apr 26 '19 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.