Matthias Hoys schrieb:
> "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
>>
>
>
>
There are some situations where both approaches can fail
1) If you have tables with longs, you can not move
2) If you have tables consisting of more than 1 segment ( lobs,
partitions etc), you must habe those tables precreated
Then generate ddl scripts and edit them might be the most appropriated
way...
Regards
Maxim
Received on Tue May 10 2005 - 17:33:20 CDT
