Re: exporting multiple tablespaces then importing to single tablespace

"Dave" <x_at_x.com> wrote in message
news:SrSfe.28890$G8.20500_at_text.news.blueyonder.co.uk...
>
> "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

+ recollect the table/index statistics

>
> or do as Sybrand said and before the import revok all privleges and use
> ignore=y on the import
>
Received on Tue May 10 2005 - 03:40:53 CDT