Locally Managed Tablespaces

  • From: <Joel.Patterson@xxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 24 Apr 2007 09:43:08 -0400

All my tablespaces in this particular database are marked LOCALly
mananged except SYSTEM.   I changed SYSTEM using

Uet$, and fet$ now return 0 rows:
select ts#, File#, count(*) from uet$ group by ts#, File#;

System tablespace is datafile 1, another is datafile 3.   3 creates a
"BitMap" in the trace file, but 1 does not.  Is this because the bitmap
was put at the first available block - probably at the end?
alter system dump datafile 1 block 3; 
alter system dump datafile 3 block 3;

1)  I am under the impression that it is better to create a new datafile
because the bitmap is put in front - although not sure I would detect a
performance boost.   
2)  using tablespace_migrate_to_local creates a hybrid, meaning existing
objects continue to be dictionary managed, and new objects are locally
managed.  If this is the case why does UET$, and FET$ return 0 rows?
3)  Thus, moving/rebuilding all the objects in new tablespace.    So How
can I tell if I need to build/move all the objects in my existing
tablespaces that are managed locally - Since I did not witness how they
were created, ie was tablespace_migrate_to_local used on them?
Essentially 3 is the crux of the issue.

4)  segment space management is MANUAL on all previous Locally managed
tablespace, and AUTO on the couple I have created recently.
Personally, another reason to create a new tablespace as I would like to
see AUTO.   Any comments?
5)  I also have a 50 - 100gb databases without two many objects being
created, but am leaning towards uniform size, but autoallocate may make
more sense.  This particular database is financial with about 3000
tables.  Any comment?

Best Regards,

Joel Patterson
Database Administrator
904  727-2546

Other related posts: