Oracle 11g “deferring” existing tables

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

Remark, i did this blog when 11.2.0.1 was available, as Lars mentionend in the comments with 11.2.0.2 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';

  COUNT(*)
----------
    172076

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

        MB
----------
130958.938

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';

  COUNT(*)
----------
     55585

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

        MB
----------
121581.875

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.