| Convert dictionary managed tablespace to Locally managed tablespace [message #538552] |
Fri, 06 January 2012 23:27  |
 |
himabija
Messages: 33
Registered: December 2011
Location: San Francisco
|
Member |
|
|
I have one database which is recently upgraded from oracle 8.1.5 to oracle 10.2.0.4.The database is having around 300 tablespace and total size of the database is 1.5 TB.
The database was created in oracle 8i and all the teblespace were
DMT(Dictionary Managed Tablespace) .Usually after up gradation all the tablespace are in DMT mode. Now my requirement is to convert all the tablespace into LMT (Locally Managed Tablespace) so that I can AVAIL ALL THE FEATURES OF LMT.
This database is a mission critical database and very less downtime can be allowed.So I'm looking for a solution which takes very less time with less impact on performance. Please help me with
some good ideas.
|
|
|
|
|
|
|
|
| Re: Convert dictionary managed tablespace to Locally managed tablespace [message #538581 is a reply to message #538569] |
Sat, 07 January 2012 06:49  |
|
himabija
Messages: 33
Registered: December 2011
Location: San Francisco
|
Member |
|
|
John: Thanks for your input but as I mentioned we want to AVAIL ALL THE FEATURES OF LMT ,so this option will not work for me.
Michel: I have read many forums, they also suggest the same option provided by you.But I have few queries
Just after tables are moved to new tablespace all the index associated with it will become invalid .And I guess any query to the moved table with a where clause to the indexed column will fail until and unless associate index are rebuilt. Is there any option available to avoid this error during the rebuilt process of indexes?
We have few table/partition is having size of 30GB .I have never moved a huge table like this.Any idea how much time it might take to move ?(I know its quite difficult to say but I wanted to know the time taken from practical experience if any one had moved a huge table like this )
[Updated on: Sat, 07 January 2012 06:50] Report message to a moderator |
|
|
|
| Re: Convert dictionary managed tablespace to Locally managed tablespace [message #538608 is a reply to message #538581] |
Sat, 07 January 2012 10:00 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:And I guess any query to the moved table with a where clause to the indexed column will fail until and unless associate index are rebuilt. Is there any option available to avoid this error during the rebuilt process of indexes?
Right, use DBMS_REDEFINITION then but this requires more work.
Quote:We have few table/partition is having size of 30GB .I have never moved a huge table like this.Any idea how much time it might take to move ?
No one can say, it depends on too many things, server, OS, io subsystem, cpu, concurrent workload...
You have to first make a benchmark on one smaller table (say 1GB).
Regards
Michel
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
