Cannot compile package DBMS_MVIEW

[roadwarriorDBA]

I can describe the package just fine.

I cannot compile it.

Any ideas ?

SQL> show user
USER is "SYS"
SQL> alter package sys.dbms_mview compile package;
alter package sys.dbms_mview compile package
*
ERROR at line 1:
ORA-04043: object 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-04043: object 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

... 

[marist89]

When describing, you are describing the spec. Show me the output from:

select owner, object_name, object_type, status
from dba_objects
where object_name = 'DBMS_MVIEW';

[Mr.Hanky]

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

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

[roadwarriorDBA]

Originally posted by marist89
When describing, you are describing the spec. Show me the output from:

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

[roadwarriorDBA]

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

[marist89]

Ah ha, dbms_mview is not a package. What does it point to?

[roadwarriorDBA]

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

[Telco_DBA]

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');

[roadwarriorDBA]

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 ?

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 

[Telco_DBA]

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');