Re: dropping tablespaces and datafiles

On Dec 10, 11:13 pm, Ben <benal..._at_yahoo.com> wrote:
> On Dec 7, 8:33 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
> > On Dec 7, 4:06 pm, Ben <benal..._at_yahoo.com> wrote:
>
> > > On Dec 7, 3:51 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > > Comments embedded.
> > > > On Dec 7, 2:32 pm, Ben <benal..._at_yahoo.com> wrote:
>
> > > > > 10.2.0.2 Ent Ed AIX5L 64bit
>
> > > > > What would be the best way to drop a dictionary managed tablespace and
> > > > > datafiles that has a few segments with somewhere around 1000 extents?
>
> > > > > drop all the objects first then drop tablespace & datafiles?
>
> > > > This appears to be the most logical of your suggested plans, in my
> > > > opinion. Unless you want/need to preserve the objects (as indicated
> > > > by your last listed 'option'). Which path is it going to be?
>
> > > > > drop objects, resize datafiles, drop tabspcs & datafiles?
>
> > > > This appears to be the least logical; why would you resize datafiles
> > > > if you're going to drop them?
>
> > > > > drop tablespace x including contents and datafiles?
>
> > > > Could take a while and could lock up any number of users. And it
> > > > doesn't preserve the tables if you need to do so.
>
> > > > > truncate objects, drop objects, drop tablespace?
>
> > > > This also makes sense, in some respects, however it may take as long,
> > > > overall, as the previous suggestion. And it also conflicts with your
> > > > next suggestion.
>
> > > > > alter table x move new_tbspc, then drop old tbspc?
>
> > > > Are you intending to drop a tablespace with its contents, or preserve
> > > > the contents and only rid yourself of the DMT? This appears to
> > > > conflict with the rest of your suggested plans of attack. Have you
> > > > decided what, exactly, you need the end result to be?
>
> > > > > I'm trying to get our database back up to par after using dmt's for
> > > > > the past 5 years and not bothering with extent sizes. This particular
> > > > > schema isn't as bad as others when it comes to number of extents. One
> > > > > of our schemas has around 7000 segments with 100 of those having over
> > > > > 1000 extents and the largest of which having 9000 extents. I found
> > > > > that when trying to drop some tables there is indeed a lock that
> > > > > blocks other space related ddl from being executed and thus causing
> > > > > some session to get hung.
>
> > > > > When using the 'alter table x move' statement, do you get the same
> > > > > locking related to uet$ and fet$ as when you drop objects?
>
> > > > Yes, as you are, in essence, dropping and recreating the table.
>
> > > > David Fitzjarrell
>
> > > my end result is to get the objects into a LMT. The tables in this
> > > particular schema have several long raw columns and I can't actually
> > > move them. So I'm exporting, dropping and recreating the tablespace(s)
> > > as LMTs, importing. Is there any way to get rid of tablespaces and
> > > objects without incurring the overhead on uet$ & fet$?- Hide quoted text -
>
> > > - Show quoted text -
>
> > I suggest some variation of
>
> > 1 - If you have the file space create the new locally managed
> > tablespace first
> > 2 - Then move those tables and rebuild the associated indexes for the
> > tables without long columns.
> > 3 -Export the tables with long columns
> > 4 - Drop the objects one at a time
> > 5 - Drop the tablespaces
> > 6 - Re-create any additional or remaining table
> > 7 - import the tables with long columns
>
> > HTH -- Mark D Powell --- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks for the info and suggestions. I wasn't necessarily limited by
> space but I was limited in that the new tablespace had to be called
> the same as the old. Which until this morning I was unaware that as of
> 10g you could rename a tablespace. This brings up another question
> though.
>
> Our ERP system requires all of our tables be in the same tablespace.
> We have 1500 or so tables in our production schema with over half of
> those being empty tables. How should I plan the new LMT for little
> waste of space. I would prefer to use uniform extent sizes but don't
> want to allocate large chunks of empty space for those empty tables.
> With LMT's is there any negative impact for having a large number of
> smaller extents on large tables as there is with DMT's? I was thinking
> of creating the new tablespaces with 164K uniform size extents, that
> would result in some objects having up to and beyond 200,000 extents.
> Sounds ridiculous to have that many.

In this special case, I think the benefit of using auto allocation outweighs that of uniform size.

What ERP system is it? If it's Oracle E-Business Suite, I remember there's a place where it stores the name of the tablespaces you use. So your first requirement that the tablespace name must keep the same can be worked around.

Yong Huang Received on Mon Dec 10 2007 - 21:53:58 CST