Re: exp/imp - Problems with procedures and trigger

On Feb 12, 1:37 am, "Alexander Peters" <apet..._at_ap-data.de> wrote:
> Hello NG!
> I have a problem with the tools imp and exp. When i export a user with
> exp and reimport the dump into an other user or server all procedures
> and trigger compiled with warnings/errors. But when i recreate the
> procedures (with SQL), they are compiled without any error. How can i
> export and import a database user without destroying the procedures and
> trigger?
>
> Export:
> my Parfile:
> STATISTICS=NONE userid=%1/%2
> file=%3
> log=%3.log
> exp parfile=parfile.txt
>
> Import:
> imp %1/%2 fromuser=%3 touser=%1 file=%4 log=%4.log
>
> Thanks in advance
> A. Peters

You should always remember to post the version of Oracle, the operating system, and any error message.

What is likely happening is exp is putting in references to the schema name to keep the object ownership straight, and you are expecting imp to straighten it out with a different user.

So, for example, if you look at what imp to LARRY is trying to do with a trigger exported from user SCOTT, you might see something like

create or replace trigger LARRY.AP_TRANSACTIONS BEFORE INSERT OR UPDATE
ON SCOTT.AP_TRANSACTIONS FOR EACH ROW
BEGIN :new.TOT_TAX_VAL := (:new.SYS_TOT_BAT_E_TAX_VAL +:new.SYS_TOT_BAT_N_TAX_VAL
); END ; If there is no SCOTT, then you would get a ORA-942, table does not exist error, and if there is a SCOTT, you might wind up with errors about no privilege for updating SCOTT, or worse, update SCOTT when you expect to update LARRY.

The exact fix depends on what you are doing, but you might consider having separate procedures for migrating code, security and data.

jg

--
@home.com is bogus.
http://riosec.com/solaris-telnet-0-day.html?q=solaris-telnet-0-day