Skip to Content
Author's profile photo Former Member

Oracle 11g “deferring” existing tables

This blog is about the new 11g feature Deferred Segment Creation

Remark, i did this blog when was available, as Lars mentionend in the comments with the new DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS produre can be better than my brspace-method.

After you upgrade an existing database to 11.2.0 all segments are already created and are eating up space even it they contain no data. To get rid of them you can use brspace and do an online table reorg.

The system before the reorg:

SQL> select count(*) from dba_segments where owner = 'SAPSR3';


SQL> select sum(bytes)/1024/1024 "MB" from dba_segments where owner = 'SAPSR3';


We have a total of 172076 of segments owned by SAP. Let us compile a list of probably empty tables for the reorg:

SQL> select table_name from dba_tables where num_rows = 0 and owner = 'SAPSR3';

The select returns a total 50049 tables. With this list we create a bunch of brspace commands with 1000 tables each. Here is an example:

brspace -c force -f tbreorg -a reorg -p 8 -t "WDY_FIELDGROUP,VRS_AR_IDX,VBWRK,VBMOD,VBLOG..."

Surprisingly the whole run took a bit over 12 hours, I expected a shorter runtime. First I thought I accidentally hit a few large tables with no statistics, but i couldn’t find any. 


A quick check for for errors (grep ‘terminated with errors’ *tbr) showed two weird deadlocks on the oracle dictionary. The tables were CRWB_BADI_INITWF and UGMD2156, closer investigation showed remains of a failed online reorg from six months ago. A brspace -a cleanup solved the problem.


After the all reorgs done,  we end up with around 55’000 segments left:

SQL> select count(*) from dba_segments where owner = 'SAPSR3';
SQL> select sum(bytes)/1024/1024 "MB" from dba_segments where owner = 'SAPSR3';


We have deleted about 120’000 Segments, we have also saved 9gb of database space. This is within expectations, because 100’000 times 64kb -> ~6gb.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Lars Breddemann
      Lars Breddemann
      ... but with there's even a more elegant way to "dematerialize" empty tables:

      can be started with just a schema name and it will automatically work on all tables in that schema.

      Of course your reorganization strategy allows to move segments to another tablespace and thereby freeing up data files which in turn means that filesystem space can be freed up.
      This is obviously not possible with the procedure call.

      Thanks for sharing your 11g experience!
      It's always interesting to hear what others make of the new features.


      Author's profile photo Former Member
      Former Member
      Blog Post Author
      Thanks, you are right. In most cases the procedure should be the way to go. I will add a remark at the start of the blog, as soon as is out for SAP.