On Fri, 28 May 1999, AmyTse wrote:
> Hi all:
>
> Is the following procedure for recreating table safe?
>
> 1. export table.
> 2. import table use SHOW=Y to have a create table script.
> 3. Drop existing table.
> 4. Run the create table script from step 2.
> 5. import data back to the newly created table.
>
> Will I loose any constraints of the table by using this procedure?
> The purpose of recreating table is to change the tables to other
> tablespace. Please advise.
>
> Thanks,
>
>
> Amy
>
> Regards,
>
>
>
> Amy
>
Amy,
Your method is not always safe. For example, any procedures, packages, or
triggers, FK constraints, and so on that point to the table will become
invalid.
When you do the SHOW=Y option in the import, you will also see SQL for
creating GRANTs, TRIGGERs, CONSTRAINTs, and so on. You will need to run
these SQL statements as well.
Regardless, after using your method, you will need to revalidate any
objects that become invalid and recreate the foreign key constraints (if
any) that had been pointing to the table.
Sounds like fun, doesn't it?
Best regards,
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 295+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page