Problem on owner change in EXP/IMP process, Urgent!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Problem on owner change in EXP/IMP process, Urgent!!

Hybrid View

  1. #1
    Join Date
    Aug 2003
    Location
    china
    Posts
    26

    Problem on owner change in EXP/IMP process, Urgent!!

    WINDOWS 2000 ORACLE9I

    I found the following problem when tranfering Schema A to B,

    EXP SYSTEM/AAA@DB1 OWNER=A FILE=A
    IMP SYSTEM/BBB@DB2 FROMUSER=A TOUSER=B FILE=A

    There is no object in USER B before import, and no error reported in EXP/IMP.

    But when checking the TRIGGERS in USER B, I found the TABEL/VIEW OWNER of some triggers are still A.

    What's the problem,pls advise.

    THANKS

  2. #2
    Join Date
    Sep 2003
    Posts
    2
    check out those triggers code,
    Could it be possible that inside the triggers, you are using the full schema name like .???

    Is that your question? I am not totally clear about your question

  3. #3
    Join Date
    Aug 2003
    Location
    china
    Posts
    26
    A code example: in User A

    CREATE OR REPLACE TRIGGER A.TRG1
    AFTER INSERT ON A.TABLE1
    FOR EACH ROW
    DECLARE
    ...
    BEGIN

    ...

    END ;
    /

    After import to user B, it should be:

    CREATE OR REPLACE TRIGGER B.TRG1
    AFTER INSERT ON B.TABLE1
    FOR EACH ROW
    DECLARE
    ...
    BEGIN

    ...

    END ;
    /

    The problem is , the code is below after import to B

    CREATE OR REPLACE TRIGGER B.TRG1
    AFTER INSERT ON A.TABLE1 --the owner is still B
    FOR EACH ROW
    DECLARE
    ...
    BEGIN

    ...

    END ;
    /

  4. #4
    Join Date
    Aug 2003
    Location
    china
    Posts
    26
    FOR CORRECTION.


    A code example: in User A

    CREATE OR REPLACE TRIGGER A.TRG1
    AFTER INSERT ON A.TABLE1
    FOR EACH ROW
    DECLARE
    ...
    BEGIN

    ...

    END ;
    /

    After import to user B, it should be:

    CREATE OR REPLACE TRIGGER B.TRG1
    AFTER INSERT ON B.TABLE1
    FOR EACH ROW
    DECLARE
    ...
    BEGIN

    ...

    END ;
    /

    The problem is , the code is below after import to B

    CREATE OR REPLACE TRIGGER B.TRG1
    AFTER INSERT ON A.TABLE1 --the owner is still A
    FOR EACH ROW
    DECLARE
    ...
    BEGIN

    ...

    END ;
    /

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    of course, that's not a problem, that's how it works, import DOES not modify your code

  6. #6
    Join Date
    Aug 2003
    Location
    china
    Posts
    26
    i don't mean change of code, BUT the ownership of the objects should be changed from A to B after ---
    EXP SYSTEM/AAA@DB1 OWNER=A FILE=A
    IMP SYSTEM/BBB@DB2 FROMUSER=A TOUSER=B FILE=A

    Right?

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by yanliu73

    After import to user B, it should be:

    CREATE OR REPLACE TRIGGER B.TRG1
    AFTER INSERT ON B.TABLE1

    CREATE OR REPLACE TRIGGER B.TRG1
    AFTER INSERT ON A.TABLE1

    that's code, you dont expect import to change that do you?!

  8. #8
    Join Date
    Aug 2003
    Location
    china
    Posts
    26
    pando,

    I got your point, I guess my problem is caused by using the full schema notation when define trigger's base table,as mentioned by seazodiac. the following code should not cause the problem.

    CREATE OR REPLACE TRIGGER A.TRG1
    AFTER INSERT ON TABLE1
    FOR EACH ROW
    DECLARE
    ...
    BEGIN

    ...

    END ;
    /

    I will do some test, and check the result.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width