From your answer you said we have to create system TS as dictionary managed, if we want to use both locally managed and dictionary managed tablespaces in DB.
Does it mean can I create system TS with locally managed extent management? What I understood uptill 9iR2 is, system tablespace has to be created dictionary managed at all times!!

I get the ORA-12913 error when trying to create a dictionary extent managed tablespace.

I understand that Oracle does not recommend but I'd like to know if there is a way to modify my instance spfile in order to allow that management.

Tom, in 9i R2 database, can i create a temp tablespace with datafile clause using CREATE TABLESPACE datafile ....... TEMPORARY syntax? or should it be CREATE TEMPORARY syntax with tempfile clause? thanks.

Hi Tom,

I found a oracle tool (sql trace analyzer) at metalink 224270.1. I wanted to test it but when tried
to run

create tablespace TRCA datafile '<some_path>trca01.dbf'
size 100M autoextend on next 100M permanent
default storage ( initial 1M next 1M)
extent management dictionary;

I got

ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace

No surprise since my system tablespace is locally managed. The documentation to this tool clearly
says : 'you need to install this TRCA tool on a dictionary managed tablespace' ...

So i started to read about locally and dictonary managed Tabelspaces to find out why the documentation
tells my to create a dictonary managed tablespace - also asked my DBA. But I cant find any reason! I
searched this forum and you keep people telling, 'use locally managed tablespaces - faster, better' and
i found no reason why some application running in a dictonary managed tablespace should not run in a
locally managed one.

Am I missing something? Is there a reason why the trace analyzer (or any other appplication) MUST have
the dictonary managed tabelspace? Are they intercangeable in any situation or are there things where
you MUST keep them in the dictonary managed tablespace or things would fail?

Our DBA keeps creating the system tablespace as locally managed. He told me this is the default for
installing the database when I asked him why he does not use dictonary managed ones for system.

Sorry, when this questions sounds a little bit strange/silly. Matter of factly, it was 100% sure for me I could
use a locally managed tablespace for all my applications (I do so) until i found the trace-analyzer and did read
the documentation and I started to ask myself 'why does this docu from oracle say : use dictonary managed
tablespace? Is there someting I missed and I would mess up my database by running it on a locally managed
tablespace?'

1) Are locally and dictonary managed tablespaces 100% intercangeable?

2) What are the arguments for running system as Locally managed or dictonary managed?

thanks

Hannibal

It was very interesting and usefull.

Good and clear Questions and neat Explanations.

it was really great.
from this document only i came to know that in oracle 9i R2 we can not create dictionary managed tablespace unless we have system tablespace as dictionary managed .Really good.
But A SMALL QUESTION HOW TO CREATE DICTIONARY MANAGED SYSTEM TABLESPACE WHILE CREATING THE DATABASE?

Thanks Tom, once again.... for your explanation.

Hi Tom

My db version is Oracle 9iR2,but tablespaces are dictionary managed.
Is there any performance gain, if I migrate the tablespaces from dictionary managed to locally managed?

Tom,

I have recently upgraded my test environment from 8.1.7.4 to 10.2.0.4 . I have converted dictionary managed tablespaces to locally managed using the package. We are doing application testing and everything looks fine.
I am planning to create new locally managed tablespaces and selectively move the objects from original tablespace later on , probably in next 6 months ..This way I thought I can some benefit of LMT for now. Is this a good approach.

Tom ,

The explanation is very clear. You dont prefer using package. I understand it is kind of hacking.My problem is I dont have enough time before the production upgrade to go through the list of objects and move them to new tablespace.Also practically there wont be enough time in
production in moving all the objects to new tablespace
at once.

Would you suggest to keep the tablespace in dictionary managed after upgrade to 10.2.0.4 If I decide to move in parts keep some of the objects still in dictionary managed

Lastly I dont understand why the package has been provided
if it is not much useful.

Please let me know your inputs.

Shankar

Tom ,

I felt with this initially I can avoid the recursive operation of allocating and deallocating extents from uet$ and fet$ and avoid ST enqueues.
Later I was planning to create new tablespaces and move
all the objects to new locally managed tablespaces.

Regards,
Shankar

Tom,

The application team needs some tables to be truncated on daily basis and they load data. This is done during off hours.They have not informed us of any issues.
I believe this would update the dictionary tables.

Regards,
Shankar

IF you had an identified issue (which you didn't)
then
    assuming that issue was extent allocate/deallocate time on these 
    segments you truncate/load every day
    then
          alter those segments and increase their NEXT extent size (doubling 
          it would cut the extents in half, triple would ... and so on)
    end if
end if;

Tom,

Thanks for your input.Appreciate your time .

Regards,
Shankar