DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Cannot compile package DBMS_MVIEW

  1. #1
    Join Date
    May 2005
    Posts
    129

    Cannot compile package DBMS_MVIEW

    I can describe the package just fine.

    I cannot compile it.

    Any ideas ?

    PHP Code:

    SQL
    show user
    USER is 
    "SYS"
    SQLalter package sys.dbms_mview compile package;
    alter package sys.dbms_mview compile package
    *
    ERROR at line 1:
    ORA-04043object DBMS_MVIEW does not exist


    SQL
    show user
    USER is 
    "SYS"
    SQL>  ALTER PACKAGE DBMS_MVIEW  COMPILE PACKAGE;
     
    ALTER PACKAGE DBMS_MVIEW  COMPILE PACKAGE
    *
    ERROR at line 1:
    ORA-04043object DBMS_MVIEW does not exist


    SQL
    desc DBMS_MVIEW

    SQL
    desc DBMS_MVIEW
    PROCEDURE BEGIN_TABLE_REORGANIZATION
     Argument Name                  Type                    In
    /Out Default?
     ------------------------------ ----------------------- ------ --------
     
    TABOWNER                       VARCHAR2                IN
     TABNAME                        VARCHAR2                IN
    PROCEDURE DROP_SNAPSHOT
     Argument Name                  Type                    In
    /Out Default?
     ------------------------------ ----------------------- ------ --------
     
    MOWNER                         VARCHAR2                IN
     MASTER                         VARCHAR2                IN
     SNAPSHOT                       DATE                    IN

    ... 
    Last edited by marist89; 06-15-2005 at 04:29 PM.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    When describing, you are describing the spec. Show me the output from:
    Code:
    select owner, object_name, object_type, status
    from dba_objects
    where object_name = 'DBMS_MVIEW';
    Jeff Hunter

  3. #3
    Join Date
    Jan 2001
    Posts
    3,134
    SQL> select * from dba_synonyms
    2 where synonym_name='DBMS_MVIEW';

    OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
    ------------ ------------------------------ ------------------------------ --------------
    PUBLIC DBMS_MVIEW SYS DBMS_SNAPSHOT
    I remember when this place was cool.

  4. #4
    Join Date
    May 2005
    Posts
    129
    Originally posted by marist89
    When describing, you are describing the spec. Show me the output from:
    Code:
    select owner, object_name, object_type, status
    from dba_objects
    where object_name = 'DBMS_MVIEW';
    OWNER
    ------------------------------
    OBJECT_NAME
    --------------------------------------------------------------------------------
    OBJECT_TYPE STATUS
    ------------------ -------
    PUBLIC
    DBMS_MVIEW
    SYNONYM VALID

  5. #5
    Join Date
    May 2005
    Posts
    129
    Holy synonym batman - I think I get it.

    You cannot compile a synonym.

    OWNER SYNONYM_NAME
    ------------------------------ ------------------------------
    TABLE_OWNER TABLE_NAME
    ------------------------------ ------------------------------
    DB_LINK
    --------------------------------------------------------------------------------
    PUBLIC DBMS_MVIEW
    SYS DBMS_SNAPSHOT


    DMBS_SNAPSHOT works...time to test a call with the synonym. If that fails then perhaps I need to recreate it.

    Cannot use it. You dont need a synonym for every procedure within the package so something is still broken.

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

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


    SQL> select * from dba_synonyms
    where synonym_name='DBMS_MVIEW.REFRESH'; 2

    no rows selected
    Last edited by roadwarriorDBA; 06-15-2005 at 04:59 PM.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Ah ha, dbms_mview is not a package. What does it point to?
    Jeff Hunter

  7. #7
    Join Date
    May 2005
    Posts
    129
    Originally posted by marist89
    Ah ha, dbms_mview is not a package. What does it point to?
    DBMS_SNAPSHOT

    Weird the synonym is unusable but the base package is ok.

    OWNER SYNONYM_NAME
    ------------------------------ ------------------------------
    TABLE_OWNER TABLE_NAME
    ------------------------------ ------------------------------
    DB_LINK
    --------------------------------------------------------------------------------
    PUBLIC DBMS_MVIEW
    SYS DBMS_SNAPSHOT

  8. #8
    Join Date
    Jun 2005
    Posts
    31
    Originally posted by roadwarriorDBA
    Holy synonym batman - I think I get it.

    SQL> select * from dba_synonyms
    where synonym_name='DBMS_MVIEW.REFRESH'; 2

    no rows selected
    You can create a synonym only for the package, but not for an individual procedure in the package.

    select * from dba_synonyms
    where synonym_name='DBMS_MVIEW';

    And try

    select owner, object_name, object_type, status from dba_objects where object_name in ('DBMS_MVIEW', 'DBMS_SNAPSHOT');

  9. #9
    Join Date
    May 2005
    Posts
    129
    Originally posted by Telco_DBA
    You can create a synonym only for the package, but not for an individual procedure in the package.

    Why does the call to synonym.REFRESH fail ?

    PHP Code:
    OWNER      OBJECT_NAME          OBJECT_TYPE          STATUS
    ---------- -------------------- -------------------- -------
    SYS        DBMS_SNAPSHOT        PACKAGE              VALID
    SYS        DBMS_SNAPSHOT        PACKAGE BODY         VALID
    PUBLIC     DBMS_MVIEW           SYNONYM              VALID
    PUBLIC     DBMS_SNAPSHOT        SYNONYM              VALID 

  10. #10
    Join Date
    Jun 2005
    Posts
    31
    Originally posted by roadwarriorDBA
    SQL> exec sys.dbms_mview.refresh('TBL_SIC');
    BEGIN sys.dbms_mview.refresh('TBL_SIC'); END;

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

    In your last post you see that SYS does not own a object called "DBMS_MVIEW", the owner is "public"

    Try it without "SYS." - as it's a public synonym you should not need to reference owner...

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

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