Re: How to re-org tablespace

You could try exporting the users objects without the data and then grep'ing the CREATE xxx TABLESPACE xxx statements out of the export. That way, you can control the objects to be recreated. Do an export with data for those users objects. Make an import parameter file that only takes the objects in the tablespace you need to move/reorganize.

Then, write a SQL to drop those objects. Now, run your CREATE script to make the new definitions of those objects in the tablespace you want.

Run the import with the parameter file and tell it to ignore object errors. Now things are in the right order.

HTH
Andrew

<rspeaker_at_my-deja.com> wrote in message news:8bvu78$33l$1_at_nnrp1.deja.com...
> 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 Mon Apr 03 2000 - 00:00:00 CDT