Re: Migrate from DMT to LMT when fet$ and uet$ counts > 700,000

"Charles Davis" <cdavis10717_at_comcast.net> wrote in message news:<OMednYY-r_ouOeTcRVn-uQ_at_comcast.com>...
> "Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
> news:417911E5.13BA_at_yahoo.com...
> > Charles wrote:
> > >
> > > All,
> > >
> > > I have a 1.8TB database with all Dictionary Managed Tablespaces.
> > >
> > > It's a very active SAP Business Warehouse application.
> > >
> > > We've sort of reached 'critical mass' with the fragmentation of it,
> > > and now SMON coalescing is so slow that "Disk Space Transaction" locks
> > > are hanging up the entire db.
> > >
> > > I've added events to disable SMON coalescing while we deal with this.
> > >
> > > Oracle is telling me I must run tablespace coalescing at 'quiet' times
> > > prior to migrating the tablespace to Locally Managed Tablespaces.
> > >
> > > I want to migrate them now to eliminate the contention on the fet$ and
> > > uet$ tables in the SYSTEM tablespace.
> > >
> > > I await Oracle's technical reasons why I cannot migrate now.
> > >
> > > I have done the migration in all non-production instances of this
> > > warehouse application; they ran seemingly ok and those databases are
> > > performing well, according to the warehouse users.
> > >
> > > What is your experience with this? What's the problem or the bad
> > > results in the LMT if I migrate these tablespaces now?
> > >
> > > Am looking for the quick fix now, to be following to reorgs later.
> > >
> > > Thank you.
> > >
> > > Charles
> >
> > Basically you're damned if you do, damned if you don't.
> >
> > Whilst migrating to LMT as is will leave you with a soup of extent
> > sizes, it should still be better than where you are now.
> >
> > You may want to consider
> >
> > a) move to LMT
> > b) piecemeal moving to "nice" LMT's as HJR suggests.
> >
> > Convincing management to do (b) once (a) is done might be a challenge...
> >
> > C
> >
> > --
> > Connor McDonald
> > Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
> > ISBN: 1590592174
> >
> > web: http://www.oracledba.co.uk
> > web: http://www.oaktable.net
> > email: connor_mcdonald_at_yahoo.com
> >
> > Coming Soon! "Oracle Insight - Tales of the OakTable"
> >
> > "GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
> > and...he will sit in a boat and drink beer all day"
> >
> > ------------------------------------------------------------
>
> Thank you.
>
> I will be reorganizing this database starting next month when new SAN disk
> drives become available.
>
> In the meantime I am looking for the band-aid. Thanks for the reply.
>
> c

I should have added that if you are going to convert, produce a simulation of that many extents in a test database and convert that - because converting 700k entries in fet$/uet$ might

1. stress your undo out so the whole thing might collapse and die
2. take a l-o-n-g time to run and thus impact your decision to do so

hth
connor Received on Mon Oct 25 2004 - 11:32:29 CDT