general oracle 9i

[yls177]

oracle 9i also has this LMT which are a definites must to have for tablespace for those who keeps growing.. but question, how can i identify which tablespace is LMT or DMT?

[davey23uk]

LMT's were in 8i

look in dba_tablespaces, extent_management

[marist89]

dba_tablespaces.extent_management

[yls177]

i am surprised... i am on oracle 9i with datafiles of over 30gbs each.. and my tablespace management is dictionary....

will i have lots of problem if i switched them all over to LMT during the process

[davey23uk]

nope, but if you convert them you are probably gonna miss out on one of the main benefits - uniform extent sizes to stop fragmentation.

Best best is to create new tablespaces and move your objects.

But if you want to convert them using dbms_space_admin you won't have any problems

[OracleDoc]

Originally posted by yls177
i am surprised... i am on oracle 9i with datafiles of over 30gbs each.. and my tablespace management is dictionary....

will i have lots of problem if i switched them all over to LMT during the process

More than likely the reason you have dictionary managed tablespaces in 9i is because when you installed the appication that sits on top of Oracle, created the tablespaces for you. 9i defaults to LMT and you have to explictly say dictionary managed in the tablespace create.
Davey23uk is right with moving your objects off to the side, then creating a new tablespace. But, heck you were probably due for a re-org anyway *cha cha chain rows*

[yls177]

creating LMT tablespaces , will have a need to export/import data from existing DMT and then dropping them after this is done.

if i do a change of tablespace, wont it be less effort compared to the initial efforts?

[davey23uk]

use the move command, much quicker

[OracleDoc]

Originally posted by davey23uk
use the move command, much quicker

Yes, much quicker but if he's hurting for space, he may have to export,create tablespace,import.

that was more or less just an FYI incase someone didn't think about it