Re: dropping tablespaces and datafiles

On Dec 11, 6:52 am, Ben <benal..._at_yahoo.com> wrote:
> On Dec 10, 10:53 pm, Yong Huang <yong..._at_yahoo.com> wrote:
>
>
>
> > 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- Hide quoted text -
>
> > - Show quoted text -
>
> It is JDE Edwards. I'm pretty sure that there is a place to store the
> tablespace name, but it is a system level setting. One size fits all.
>
> I remember reading about bugs with auto allocation but I think it was
> related to assm in 9.2. Are you aware of any of those bugs still
> lurking in 10.2?

Which bug is it? I'm sure there're bugs with either allocation type. It's hard for me to find all. I would open a Tar if needed. Sorry I can't help.

Yong Huang Received on Tue Dec 11 2007 - 10:32:50 CST