DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

Thread: Cannot compile package DBMS_MVIEW

  1. #11
    Join Date
    May 2005
    Posts
    129
    I did it with and without above.

    Here it is again.

    SQL> exec dbms_mview.refresh('TBL_SIC');
    BEGIN dbms_mview.refresh('TBL_SIC'); END;

    *
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00201: identifier 'DBMS_MVIEW.REFRESH' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored


    Now when I created a new test synonym it invokes the underlying package and fails for a user error.

    This tells me the synonym is messed up and should be re-created no ?

    CREATE public SYNONYM test FOR sys.DBMS_SNAPSHOT;

    SQL> exec test.refresh('TBL_SIC');
    BEGIN test.refresh('TBL_SIC'); END;

    *
    ERROR at line 1:
    ORA-23401: snapshot "SYS"."TBL_SIC" does not exist
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 617
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 674
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 654
    ORA-06512: at line 1

  2. #12
    Join Date
    Jun 2005
    Posts
    31
    What Oracle Version ?

    Are materialized view and base table in same database or is there a database link between ? - If yes, what's the Oracle Version on the other side of the database link ?

    Do you use in "create materialized view ..." a synonym for the table accessed ?

  3. #13
    Join Date
    May 2005
    Posts
    129
    8.1.7.0.0

    I am not worried about the table name, it simply does not exist in sys, its in a use schema. I dont know the user password. The good news is the test synonym correctly invoked DBMS_SNAPSHOT.

    This is a clients production database I dont know how they built this stuff. Just need to get the synonym to work and move on. I think if I drop the synonym and recreate it will work ( call DBMS_SNAPSHOT ) as the test synonym did.

    Do you agree?

    Once it does that the customer can be told they are ok to use the it either way. DBMS_SNAPSHOT or the DBMS_MVIEW synonym.

  4. #14
    Join Date
    Jun 2005
    Posts
    31
    Originally posted by roadwarriorDBA

    CREATE public SYNONYM test FOR sys.DBMS_SNAPSHOT;

    SQL> exec test.refresh('TBL_SIC');
    BEGIN test.refresh('TBL_SIC'); END;

    *
    ERROR at line 1:
    ORA-23401: snapshot "SYS"."TBL_SIC" does not exist
    Are you sure that the snapshot "TBL_SIC" is owned by SYS ?

    You are testing now as SYS, or ?

    Try to run that as that user which owns TBL_SIC or add the owner-name

  5. #15
    Join Date
    May 2005
    Posts
    129
    Originally posted by Telco_DBA
    [B]Are you sure that the snapshot "TBL_SIC" is owned by SYS ?
    I am telling you that I know it is not owned by sys. I dont want to actually run the refresh, I want get the synonym to fuction by invoking DMBS_SNAPSHOT.refresh which my test synonym does - DBMS_MVIEW does not.

    The fact that once DMBS_SNAPSHOT.refresh was actually invoked failed because I gave it a bogus table name is trivial.

    I just need the synonym to work, right now when you call its refresh method it claims the synonym does not exist. IOW its corrupt and does not point to DBMS_SNAPSHOT.

    I am going to discuss with the DBA who knows the client better that I want to drop and recreate the DBMS_MVIEW synonym to fix it. Then the client can call it all they want. She has concerns about touching it, thinks its related to backups which if it is they sure are not working.

    Drop synonym and recreate synonym I say.

  6. #16
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by roadwarriorDBA

    Drop synonym and recreate synonym I say.
    A synonym is only an alias. If it is pointing to the right thing, it will work.
    Jeff Hunter

  7. #17
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    user JEFFH owns a materialized view:

    Code:
    SQL> select * from xyz_mview;
    
             X Y         Z
    ---------- --------- --------------------
             1 16-JUN-05 1
             2 17-JUN-05 2
             3 18-JUN-05 3
             4 19-JUN-05 4
             5 20-JUN-05 5
             6 21-JUN-05 6
             7 22-JUN-05 7
             8 23-JUN-05 8
             9 24-JUN-05 9
            10 25-JUN-05 10
    
    10 rows selected.
    
    SQL> exec dbms_mview.refresh('xyz_mview');
    
    PL/SQL procedure successfully completed.
    
    SQL>  select USER from dual;
    
    USER
    ------------------------------
    JEFFH
    SYSTEM tries to refresh it:
    Code:
    SQL> exec dbms_mview.refresh('xyz_mview');
    BEGIN dbms_mview.refresh('xyz_mview'); END;
    
    *
    ERROR at line 1:
    ORA-23401: materialized view "SYSTEM"."XYZ_MVIEW" does not exist
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 1883
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2089
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2058
    ORA-06512: at line 1
    but can't because the error says system doesn't own an mview with that name. Refresh with the owner.mview_name, and it's ok:
    Code:
    SQL> exec dbms_mview.refresh('jeffh.xyz_mview');
    
    PL/SQL procedure successfully completed.
    Jeff Hunter

  8. #18
    Join Date
    May 2005
    Posts
    129
    OK I have managed to confuse everyone.

    The synonym is corrupt somehow.

    I have proven a test synonym actually calls the correct package.

    I am not interested in the outcome of the package its easy enough to give it the right table/owner. I dont intend to refresh the production table or I would not have intentionally created a user error.

    Its of no consequence, as long as the synonym calls the right package with a good or bad outcome I dont care.

    The problem is the DBMS_MVIEW synonym cannot even call the package it is suppose d to point to. Its messed up somehow. Client says it happened before.

    I have proven I can create a new synonym and it calls the right package.

    DBMS_MVIEW fails to invoke the right package - it does not work, it is somehow corrupt.

    If my goal was to refresh the table I would just call DBMS_SNAPSHOT with the correct tablename. That is not my goal.

    Thank you for trying to help me.


  9. #19
    Join Date
    May 2005
    Posts
    129
    I finally got around to dropping synonym DBMS_MVIEW and recreated a public synonym DBMS_MVIEW to point to DBMS_SNAPSHOT and it works now. Must have been corrupt somehow. Calling it with a real or imagined tablename results in a call to DBMS_MVIEW.

  10. #20
    Join Date
    Jan 2001
    Posts
    3,134
    Ya shoulda seen what it was pointing to before you dropped it.
    Oh well, long as it works now.
    I remember when this place was cool.

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