On Fri, 5 Feb 1999, Brian Gallagher wrote:
> Hi Ari,
> Firstly i think your resource is really great - thanks.
> i have a question:
> i have to move a table from SYSTEM tablespace to DATA tablespace.
> 1. export TABLE
> 2. drop table
> 3. Change user's account to
>    - remove their quota on SYSTEM
>    - give them a quota on DATA
> 4. import table
> Does this have any affect on grants ..... or anything else ? any
> help would be greatly appreciated
> thanks a lot and regards
> brian

Excellent question, which no one has asked me before. Your steps are
almost perfect. Just add a step between 3 and 4 to change the default
tablespace of the user to DATA.

What export/import does is recreate all constraints, indexes, grants, and
statistics for the table.

What it does NOT do is recompile all procedures/views/packages that become
invalid when you drop the table. It also does not recreate all
FK constraints on other tables that were pointing to the dropped table.

To find all invalid objects:

select owner, segment_name, segment_type
from all_segments
where status = 'INVALID'

To recompile the invalidated objects:

alter procedure XXX compile;
alter view XXX compile;
alter package XXX compile;

To find all constraints pointing to the table (this must be done before
the table is dropped):

select * from all_constraints
where r_constraint_name in
     (select constraint_name from all_constraints
      where table_name = '***table_name***');

You will have to recreate all constraints by what is returned from the
above query.

Best regards,

-Ari Kaplan
Independent Oracle DBA Consultant

<-> For 250+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->

Back to Ari Kaplan's Home Page "18"> Back to Ari Kaplan's Home Page