Re: How to re-org tablespace

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. Received on Thu Mar 30 2000 - 00:00:00 CST