Re: dropping tablespaces and datafiles

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 -- Received on Fri Dec 07 2007 - 19:33:53 CST