Home » RDBMS Server » Performance Tuning » Dictionary Managed to Locally Managed Tablespace
Dictionary Managed to Locally Managed Tablespace [message #187842] Tue, 15 August 2006 23:58 Go to next message
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 #187859 is a reply to message #187842] Wed, 16 August 2006 01:00 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');
PL/SQL procedure successfully completed.


Also see http://www.orafaq.com/node/3
Re: Dictionary Managed to Locally Managed Tablespace [message #187890 is a reply to message #187859] Wed, 16 August 2006 04:28 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
@Frank,
Would it not be better to:

1. Create a new LMT tablespace
2. ALTER TABLE ... MOVE TABLESPACE ....

TK provides a script to do this here
http://asktom.oracle.com/pls/ask/f?p=4950:8:16859693577628166685::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:47812348053

rather than migrate a DMT to an LMT using DBMS_SPACE_ADMIN as this method will not resolve existing fragmentation in the tbs (providing of course that there is such) whereas the MOVE method will resolve the fragmentation issue.

I could of course be wrong (It's not a rare occurrence Smile )
Regards
Jim

[Updated on: Wed, 16 August 2006 04:28]

Report message to a moderator

Re: Dictionary Managed to Locally Managed Tablespace [message #187895 is a reply to message #187890] Wed, 16 August 2006 05:14 Go to previous messageGo to next message
cherryven
Messages: 13
Registered: August 2006
Junior Member
Hi Jim,
If i do a re-org after migrating from DMT to an LMT using DBMS_SPACE_ADMIN,is it resolve the fragmentation issue which you have specified?

Regards,
Cherry
Re: Dictionary Managed to Locally Managed Tablespace [message #187896 is a reply to message #187895] Wed, 16 August 2006 05:16 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Not as far as I know. I've never tested. I'd use the method I described.

Jim
Re: Dictionary Managed to Locally Managed Tablespace [message #187897 is a reply to message #187896] Wed, 16 August 2006 05:20 Go to previous messageGo to next message
cherryven
Messages: 13
Registered: August 2006
Junior Member
Jim,
I am concerned b'cause i have a DB of size 3TB all in DMT.

Regards,
Cherry
Re: Dictionary Managed to Locally Managed Tablespace [message #187898 is a reply to message #187897] Wed, 16 August 2006 05:21 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Why are you concerned?
Re: Dictionary Managed to Locally Managed Tablespace [message #187900 is a reply to message #187898] Wed, 16 August 2006 05:25 Go to previous messageGo to next message
cherryven
Messages: 13
Registered: August 2006
Junior Member
B'cause if I want to create a new TB with LMT,i should have that much space available espically in case of £TB of data...
Re: Dictionary Managed to Locally Managed Tablespace [message #187903 is a reply to message #187900] Wed, 16 August 2006 05:33 Go to previous messageGo to next message
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 #187905 is a reply to message #187903] Wed, 16 August 2006 05:41 Go to previous messageGo to next message
cherryven
Messages: 13
Registered: August 2006
Junior Member
In that case if I move few tables to the newely created TB,whether i have to wait until i move all the objects from the old TB(ie DMT)to get the space back?

Regards,
Cherry
Re: Dictionary Managed to Locally Managed Tablespace [message #187906 is a reply to message #187905] Wed, 16 August 2006 05:42 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
You can perform a coalesce on the dmt to improve space utilsation (you should be doing that anyway)
Jim
Re: Dictionary Managed to Locally Managed Tablespace [message #187907 is a reply to message #187906] Wed, 16 August 2006 05:54 Go to previous messageGo to next message
cherryven
Messages: 13
Registered: August 2006
Junior Member
But still i will come across with fragmentation issues,isn't?
what is the best solution in this case as per you?

Regards,
Cherry
Re: Dictionary Managed to Locally Managed Tablespace [message #187911 is a reply to message #187907] Wed, 16 August 2006 06:09 Go to previous messageGo to next message
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 #187914 is a reply to message #187911] Wed, 16 August 2006 06:16 Go to previous messageGo to next message
cherryven
Messages: 13
Registered: August 2006
Junior Member
Jim,
If i am moving to LMT then why should i do a coalesce?
Can you please answer "If I move few tables to the newely created TB,whether i have to wait until i move all the objects from the old TB(ie DMT)to get the space back?"

Regards,
Cherry
Re: Dictionary Managed to Locally Managed Tablespace [message #187924 is a reply to message #187914] Wed, 16 August 2006 06:32 Go to previous message
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.

Previous Topic: Analyze before insert - does it help
Next Topic: Staging table from partitioned table
Goto Forum:
  


Current Time: Sun Dec 04 14:32:14 CST 2016

Total time taken to generate the page: 0.10345 seconds