Wednesday, March 26, 2008

Objects Remain In Their Original Tablespaces After Run Oatm

Migrated to the new tablespaces using OATM but there are objects left behind in original tablespaces. There were no errors reported during tablespace migration.

SQL> select tablespace_name, count(1) from dba_Segments group by tablespace_name;
TABLESPACE_NAME COUNT(1)
------------------------------ ----------
APPLSYSD 1
APPLSYSX 1
COMD 26
COMX 47
CTXD 77
EDWREP 88
EDWREPX 31
PVD 1
PVX 1

SQL> select segment_name, segment_type from dba_segments
2* where tablespace_name='APPLSYSD'
SEGMENT_NA SEGMENT_TYPE
---------- ------------------
20.42 SPACE HEADER

Cause
*******

One of the circumstances under which a 'SPACE HEADER' segment gets created is if a 'dictionary managed' tablespace is migrated to 'locally managed' (see dbms_space_admin.tablespace_migrate_to_local()).

The space header segment contains the extent bitmap and is allocated during the migration of the tablespace. Since there is no reserved space after the file header (as with locally managed tablespaces) the bitmap segment will be allocated somewhere in the "data" area of the datafile. During its creation the segment will pick up some of the storage attributes (e.g. MAXEXTENTS) from the default storage clause of the tablespace. Once the segment has been created it can neither be dropped nor changed.

Fix
****

You can ignore these "left-over" objects. Please go ahead and drop old tablespaces