-
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"
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
...
Last edited by marist89; 06-15-2005 at 04:29 PM.
-
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
-
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.
-
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
-
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.
-
Ah ha, dbms_mview is not a package. What does it point to?
Jeff Hunter
-
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
-
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');
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|