Re: How to re-org tablespace

"Douglas Cowles" <dcowles_at_us.ibm.com> wrote in message news:38E2FBBB.AC688611_at_us.ibm.com...
> 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?
>

No easy solutions. Try "select segment_name, owner from dba_segments where tablespace_name='X';" to start with. That will tell you what is in your messy tablespace, and who owns it.

Then you just have to do three User-mode exports.

Just make sure on subsequent import that you are selective about which tables you wish to import, since you don't want to import into the new tablespace objects of theirs which rightly belong elsewhere. Use the dba_segments report to tell you precisely what should be imported.

Remember that on import, objects are created in exactly the same tablespace they came from.... unless no tablespace of that name exists (eg, source=DATA01, destination = DATA1)... in which case, the tables are created in the default tablespace of the User doing the import.

So you might want to consider off-lining (or dropping) the original tablespace, then using the 'alter user XXX default tablespace YYY' command to make sure that when you do each import, the selected tables end up going into the correct, new tablespace.

And having said all that, I'm not clear in the first place why you'd be concerned about different users having different segments in the same tablespace. The tablespace is (or ought to be) a unit of administrative (backup) and qualitative (fast v. static data) organisation within the database. Unless each of your users has data which is qualitatively different from each other, I wouldn't worry about it.

Best regards
HJR
> Help greatly appreciated..
> Thanks,
> Dc
>
Received on Fri Mar 31 2000 - 00:00:00 CST