Re: exporting multiple tablespaces then importing to single tablespace

"Kee" <keedae_at_hotmail.com> wrote in message news:wlSfe.59789$tg1.29204_at_edtnps84...
>>>The documentation indicates I must revoke Umlimited Tablespace and set
>>>the
>>>Quota to 0 (or none) then export file.
>>
>> The documentation indicates I must revoke Umlimited Tablespace and set
>> the Quota to 0 (or none) then *IMPORT* file.
>> --
>> Sybrand Bakker, Senior Oracle DBA
> Allow me to clear up my problem more,
>
> Excerpted from Oracle 9i Database Utilities [section: Reorganizing
> Tablespaces]
> //
> For example, you need to move joe's tables from tablespace A to tablespace
> B after
> a full database export. Follow these steps:
>
> 1. If joe has the UNLIMITED TABLESPACE privilege, revoke it. Set joe's
> quota on
> tablespace A to zero. Also revoke all roles that might have such
> privileges or
> quotas.
> Role revokes do not cascade. Therefore, users who were granted other roles
> by
> joe will be unaffected.
> 2. Export joe's tables.
> 3. Drop joe's tables from tablespace A.
> 4. Give joe a quota on tablespace B and make it the default tablespace for
> joe.
> 5. Import joe's tables. (By default, Import puts joe's tables into
> tablespace B.)
>
> //
>
> I am facing a problem where the user joe has more than one table spaces.
> Above case as joe's tablespace set to A only. What if joe has tablespace
> A, B, C, D and E and wants to move all data to tablespace Z? Everytime I
> do that, I get this Tablespace A, B, C, D, E are not available. Also for
> following the following steps
> http://www.oracle.com/technology/oramag/oracle/02-jul/o42support.html to
> use SQL script doesn't work nicely due to constraints on tables and Index
> file reports some errors on syntaxes created by import utility.
>
>
>
>
>

then dont use export and use alter table xx move tablespace z;

then rebuild all the indexes

or do as Sybrand said and before the import revok all privleges and use ignore=y on the import Received on Mon May 09 2005 - 18:34:10 CDT