| Dictionary Managed to Locally Managed Tablespace [message #187842] |
Tue, 15 August 2006 23:58  |
cherryven
Messages: 13
Registered: August 2006
|
Junior Member |
|
|
Hi,
I am having an Oracle 9.2.0.6.0 Database running on AIX5.2,all the tablespaces are Dictionary Managed,how can I convert it into a LOCALLY Managed?.I am planning to do a re-org on this database.How should i proceed.
Regards,
Sam
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Dictionary Managed to Locally Managed Tablespace [message #187903 is a reply to message #187900] |
Wed, 16 August 2006 05:33  |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
It is a major job, treat it as such and don't look for the 'quick fix'.
Do it incrementally. You don't have to move all the tables at once/ in a short period of time.
Plan it out. look at the areas with the worst fragmentation. Work on them first. Even set up test environments to assess the performance hits that will be incurred during the migration.
|
|
|
|
|
|
|
|
|
|
| Re: Dictionary Managed to Locally Managed Tablespace [message #187911 is a reply to message #187907] |
Wed, 16 August 2006 06:09 |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
| Quote: |
But still i will come across with fragmentation issues
|
exactly what do you mean? Do you mean that moving your tables to a LMT will cause fragmentation? No. Will the DMTs remain fragmented? Yes.
Do you have ALL your segments in a single Tablespace?
I'm pretty sure I've told you what I think is the best approach. USE ALTER TABLE MOVE. do it incrementally. rebuild indexes as required. coalesce dmts while you're doingit. But most importantly, go and read the documentation on the subject, surf the web for opinions other than mine (here is one exmple of an article on the subject
http://www.dbasupport.com/oracle/ora9i/managed_tablespace.shtml
). I repeat, this is a big job and not one that should be taken lightly.
Jim
|
|
|
|
|
|
| Re: Dictionary Managed to Locally Managed Tablespace [message #187924 is a reply to message #187914] |
Wed, 16 August 2006 06:32 |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
C'mon, think about the context in which I said it.
Your concern was that you will have fragmnentation in your DMT when you move the segments to the LMTs. A valid concern. i told you that you could coalece the DMTs to reduce somewhat the fragmentation. Once all of the objects from the DMT have been moved, you can then get rid of the DMT.
Define what you mean by getting the space back? do you mean taht the space will be available for further extent allocation in the DMT or do you mean you want the DISK space back.
If the former, then coalescing will make (hopefully) more space available for extent allocation. if you mean the latter, you could try reszing the datafiles.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
)