Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Locally Managed Tablespaces

Locally Managed Tablespaces

From: <Joel.Patterson_at_crowley.com>
Date: Tue, 24 Apr 2007 09:43:08 -0400
Message-ID: <02C2FA1C9961934BB6D16DE35707B27B02D97D1E@JAX-MBH-01.crowley.com>


All my tablespaces in this particular database are marked LOCALly mananged except SYSTEM. I changed SYSTEM using dbms_space_admin.tablespace_migrate_to_local('SYSTEM').

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;

Quandry.
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
joel.patterson_at_crowley.com
x72546
904 727-2546

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 24 2007 - 08:43:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US