Peoplesoft load without segments

As discussed few months ago here, Oracle 11gR2 offers the possibility to create table without segment. Particularly interesting on Peoplesoft database to drop down the size of the occupied size in the database, as I explained it is difficult to get rid off segment on existing table, but what happens on a new database load for a first Peopletools installation ?
Everything works as expected, after doing a database creation, just be sure to set the parameter deferred_segment_creation to TRUE on system level before the very first DataMover load :
SQL> show parameter deferred_segment_creation

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL>
Then, after load, a quick check shows a lot of tables are empty (HCM9.1/Peopletools 8.50) :
SQL> select count(*) from user_tables where num_rows = 0;

  COUNT(*)
----------
     12101
But how many of them have a segment ?
SQL> select count(*)
  2  from   user_tables,dba_segments
  3  where  num_rows = 0
  4  and    segment_type='TABLE'
  5  and    table_name=segment_name;

  COUNT(*)
----------
         0
Yeah, actually none of the empty tables have segment !

Now to get rid of segment on existing table, what about export and import through DataMover ?
Just found a table with few rows, and truncate it (just for my test) :
SQL> truncate table PS_AA_COST_RT_JPN;

Table truncated.

SQL> select count(*) from dba_segments where segment_name='PS_AA_COST_RT_JPN' and segment_type='TABLE';

  COUNT(*)
----------
         1

Then with DMS, connect as PS and export the table :

Finally, import with REPLACE_ALL option :

Let’s check what happened on database side :
SQL> select count(*) from user_tables where table_name='PS_AA_COST_RT_JPN';

  COUNT(*)
----------
         1

SQL> select count(*) from dba_segments where segment_name='PS_AA_COST_RT_JPN' and segment_type='TABLE';

  COUNT(*)
----------
         0

Yeap, no more segment. So, finally, if you have an existing Peoplesoft database, and think about an upgrade to 11gR2, maybe a DataMover export and re-import of all the empty tables has to be considered (maybe that’s easier than “rebuild” everything through AppDesigner).

Nicolas.