Re: Reorganizing the DB.. the tricky way

Rick Denoire wrote:

> "Ana C. Dent" <anacedent_at_hotmail.com> wrote:
>
> >1) Oracle really does NOT care about OS files per se and
> >neither should you. Oracle primarily cares about tablespaces.
>
> Whether I should care: See my answer to 2)
>
> >2) Why does it matter the name of the tablespace where the tables
> >and indexes reside? If it does not matter then simply create a
> >new tablespace & move the objects into it. I see no requirement to
> >move the object a second time "then back again"
>
> When the developers create an index, they were asked to put them in
> the corresponding tablespace for indexes. If its name change, they
> will get an error. There is no such thing like a "default tablespace
> for indexes".
>
> Besides that, tablespace names were put in a number of sql statements
> that developers have prepared to recreate objects if needed. OK, if
> only the default tablespace is going to be used, that is a bad
> practice.
>
> >3) If you add a new file to the existing tablepsace, stop worrying
> >about what object is in which file. Simply "reorg" the objects.
> >Oracle will make them contiguous as long as contiguous free space
> >exists within the tablespace.
>
> Yes, but free space in the files won't be contiguous, so fragmentation
> at the tablespace level will remain. That is what I am trying to
> avoid.
>
> Bye
> Rick Denoire

Ouch. You need to get someone in there to teach your developers how to work with Oracle. Tablespace names in SQL statements? Sounds like a bunch of SQL Serve retreads that never learned Oracle concepts and architecture and are still building temp tables.

The entire concept of developers choosing tablespaces, as they have no knowledge of I/O is wrong. The blind placement of indexes in tablespaces with names related to where the tables are without knowledge of I/O on te server is wrong.

I would urge you to take a big step back away from the table and review the postings here at c.d.o. over the last year with respect to both of these issues before proceeding.

It is easy now to see why you have such a huge problem with fragmentation, chaining, etc. Someone is going to need to take control of this or the problems will grow bigger and bigger or you should consider polishing your resume and getting out before it all collapses around you.

Daniel Morgan Received on Sun Apr 06 2003 - 13:14:57 CDT