Re: How to re-org tablespace

Doug,

I think miaemp is on the right track, but your situation with having multiple users in the tablespace (and those users elsewhere also) complicates things for you a bit. I would suggest using the DBA_SEGMENTS view to grab the owner, segment_type, and segment_name of all objects in given tablespace. Then you could write a small script to export each object, or manually export the objects grouping them by user. Then you can reimport them to reload the data where you want it and you won't affect the objects owned by these 3 users that reside on other tablespaces.

You may also want to check out a product called Space Manager from Quest Software (www.quests.com). They offer a trial download of the product. It has some very nice features, such as automatic creation of rebuild/reorg scripts, tablespace maps, etc. I have reorg'd tables on a production system mid-day with minimal impact to users. The drawback is that you need to have enough freespace available in the tablespace to hold 2 copies of the object being rebuilt. You also will sometimes encounter some pretty hefty freespace fragmentation, but not always. But it gives you the option to simply script the rebuild process, save the file off, then edit and run it manually later. I have done that several times, use the product to generate my rebuild scripts based on the extent sizings I want, then manually drop the objects, run the rebuild script (you can control placement inside the datafile that way), and reload the data. Works out pretty well.

Hope this helps you out, if you need any additional info let me know.

-Roy

In article <38E36F6E.91B961B7_at_us.ibm.com>, Douglas Cowles <dcowles_at_us.ibm.com> wrote:
> Thanks.. that helps a little.. but I am still confused because
 although the
> tablespace contains 3 owners of objects, those owners are not
 exclusively
> in the tablespace.. they have objects in many places.. so I think I
 exported
>
> the owner and jammed them back in the new tablespace, I get a lot of
> objects that are suppossed to be somewhere else.. I hope this makes
 sense
> to someone...
> More feedback appreciated...
>
> Thanks,
> Dc.
>
> miaemp_at_my-deja.com wrote:
>
> > I performed something similar, but, the users did not have the
 amount
> > of data you have. What I did was, exported each user on their own.
> > Created a new tablespace for the import. I then made sure to revoke
> > the system privilege 'UNLIMITED TABLESPACE' from each user. Then I
> > revoked all of their quotas on all tablespaces except for the new
> > tablespace I created for them to go into. I then imported each user
> > and each users tables went into the new tablespace. Now, I don't
 know
> > what you are going to do about the indexes if they have any. You may
> > want to consider not importing them, just recreating them after the
> > users data is re-imported, which I did. One little tip, if you
 perform
> > a separate export of each user where rows=N and indexes=Y, then dump
> > file from export will contain the ddl in a readable format, which I
> > copied and pasted into a sql script, reformated the sql statements
 and
> > ran them. Just be sure you do not make changes to the dump file and
> > save them, if you do, the dmp file will become unreadable to Oracle.
> >
> > HTH,
> > Eric Peterson, DBA
> > Maurices Inc.
> > eric_NOpeterson_at_maurices.inSPAMMERSALLOWEDrg.com
> >
> > Note that the views I may have expressed here are solely my own and
 not
> > that of my employer. To email me directly, remove the NO and the
> > SPAMMERSALLOWED from the above email address.
> >
> > Oh, after all of that, they had unlimited tablespace before I
 started,
> > I granted them that privilege back to them.
> >
> > In article <38E2FBBB.AC688611_at_us.ibm.com>,
> > Douglas Cowles <dcowles_at_us.ibm.com> wrote:
> > > Can someone point me in the right direction as to how to re-org a
> > > tablespace?
> > > The tablespace is 12GB in size, and has objects from 3 different
 users
> > > in it.
> > > If it was just one user in their very own tablespace , it would
 seem
> > > like a simple matter of exporting the user, re-creating the
 tablespace,
> > > and re-importing the user, however, I am not sure how to handle 3
> > > users. There must be a few tricks people
> > > know to do this.. any thoughts?
> > >
> > > Help greatly appreciated..
> > > Thanks,
> > > Dc
> > >
> > >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Mar 30 2000 - 00:00:00 CST