Re: Import Fromuser/Touser has problems with Views

From: Bill Thorsteinson <billthor_at_yahoo.com>
Date: Tue, 25 Jun 2002 22:54:35 -0400
Message-ID: <ffaihu4hbu2h3r7f2dui9g9rvlrn234nn4_at_4ax.com>


On Tue, 25 Jun 2002 22:05:23 GMT, Daniel Morgan <dmorgan_at_exesolutions.com> wrote:

>Josh Sale wrote:
>
>> I'm importing a database and need to change the schema (e.g., fromuser=foo
>> touser=bar).
>>
>> All of the tables import OK. However, the export file includes a view
>> definitions like:
>>
>> create view stuff as select col from foo.tab

Modify the views to not include foo. in the table references. If foo owns the table and the view then I don't think you need the schema qualifier.
>>
>> and import doesn't change foo.tab to bar.tab in the view definition. This
>> means the view can't be created because the referenced table doesn't exist.
>>
This is generally desirable behavior.

>> Does anybody know a way around this?
>>
If the table definitions are stable, truncate the tables and import away. Only drop tables when the table definition has changed.

You may want to disable constraints during the import, and reenable them after the import.

>> Manually editing the view definition after the fact isn't very satisfactory
>> because I need to reimport the schema periodically.
>>

If you are dropping the tables, etc. don't drop the views. The views should change to invalid until the views are replaced.

>> TIA,
>>
>> josh
>
>Get the DDL definition of the view and recreate it.
>
Get a tool to extract the DDL from the export, if you don't have the DDL kept somewhere.

>Or
>
>Before the import starts create synonyms that will solve the problem.
>
Won't work if foo schema was exported from or imported into the same database.

>Daniel Morgan
Received on Wed Jun 26 2002 - 04:54:35 CEST

Original text of this message