Re: Import / Export

spam_at_spam.com wrote in message news:<400f9b34.260377933_at_news.demon.co.uk>...
> Hi
>
> We use Oracle 8.1.7.4 with Sparc Soalris 8
> I want to export and Import a table to defrag it. The problem is
> tables has procedures and synoname depended on it. After I export and
> drop the table what happens to the synonym and procedures.
>
> How do I defrag the table without destroying the dependencies
>
> Thanks
>
> R

R, under the assumption that you have determined that defragging the table has value then instead of export/drop/import try exprot/truncate/imp ignore=y. This will preserve the grants. If the table has any size you may want to drop the indexes prior to export and recreate them manually after the import. If the table is very large you may need to use commit=y on the import due to rollback segment size limitations.

Synonyms do not drop when the objects they reference are dropped; however, all dependend code: functions, procedures, packages, and views will go invalid. It is best to revalidate (recompile) the code manually. The utlrp script provided by Oracle, $ORACLE_HOME/rdbms/admin, is very handly for revalidating stored code.

Also remember if you have FK references to the target table the export solution will be impacted and you will likely have to drop and recreate the FK references.

It might just be easier to use the alter table move statement if you have enough space available. This bypasses most of the problems associated with export/import of the table.

HTH -- Mark D Powell -- Received on Thu Jan 22 2004 - 11:31:33 CST