sybrand_b wrote:Hi Sybrand
Provided you are using Locally Managed Tablespaces:
Why are you still subscribing to Ye Olde Fragmentation Myth?
Using LMT, objects won't get fragmented, EVER.
Ye Olde Fragmentation Myth has been invented by consultants to keep their income high: they come over to de-fragment your database every few months, and this doesn't resolve anything.
Senior Oracle DBA
moslee wrote:this, in itself, makes no sense. What defines a database not being 'in sync' with another? Exactly what situation is it that triggers this alert? The term 'in sync' could mean anything at all, but it usually has something to do with data. I've never seen it to mean anything that would be remotely related to fragmentation.
Thanks for your prompt reply.
Here's abit of my current situation which I have just taken over:
Up to now my company is still using 8.1.6 for 3 identical DBs (DMT) and they are supposed to be in sync. For about every 50-60days, they will not be in sync and I will receive an alert. Past DBAs (those my senior) always reckons that the issue is due to fragmentation in any of the DBs. And they will always do a physical copy and paste of the system, datafiles to resolve the issue. Thus, the idea of 'defragmenting databases' still exist.
I want to change them to LMT but they are all top critcal production DBs. But I have only 1 such DB for development, not 3. One way that I could prove that LMT reduces or eliminates fragmentation is to execute the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure in those 3 top critical production DBs and hope that I will not receive an alert in the future, but this would be very very risky.You are not going to be able to "prove" anything until you have a firm definition of "fragmentation" and whatever it is that you refer to as "being in sync". In the broader sense, nothing can ever be "proven" until the terms are defined and the definitions accepted by all interested parties.
This is the reason why I need to prove and test that LMT actually works before implementing in my production. With the proof and testing that I have done, I can then confidently claim that LMT reduces or eliminates fragmentation.
Thank you, for your help.
You're right I have LMT objects in Oracle 9i, but I have others databases in olders version, like....Oracle 7.
In Oracle 7 I think there is fragmentation.
Do you know any script or query to check the level of fragmentation???