Cannot compile package DBMS_MVIEW

Printable View

06-15-2005, 04:17 PM
roadwarriorDBA

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 ...
06-15-2005, 04:31 PM
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';
06-15-2005, 04:35 PM
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
06-15-2005, 04:45 PM
roadwarriorDBA

Quote:

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
06-15-2005, 04:54 PM
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
06-15-2005, 04:57 PM
marist89

Ah ha, dbms_mview is not a package. What does it point to?
06-15-2005, 05:08 PM
roadwarriorDBA

Quote:

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
06-15-2005, 05:24 PM
Telco_DBA

Quote:

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');
06-15-2005, 05:38 PM
roadwarriorDBA

Quote:

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
06-15-2005, 05:48 PM
Telco_DBA

Quote:

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');
06-15-2005, 05:55 PM
roadwarriorDBA

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
06-15-2005, 06:11 PM
Telco_DBA

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 ?
06-15-2005, 06:17 PM
roadwarriorDBA

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.
06-15-2005, 06:20 PM
Telco_DBA

Quote:

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
06-15-2005, 07:23 PM
roadwarriorDBA

Quote:

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.
06-15-2005, 07:36 PM
marist89

Quote:

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.
06-15-2005, 07:49 PM
marist89

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.
06-15-2005, 09:56 PM
roadwarriorDBA

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.

:)
06-30-2005, 09:40 AM
roadwarriorDBA

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.
06-30-2005, 10:05 AM
Mr.Hanky

Ya shoulda seen what it was pointing to before you dropped it.
Oh well, long as it works now.
06-30-2005, 11:36 AM
roadwarriorDBA

I posted this on page 1.

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

It was pointing to DBMS_SNAPSHOT - it just was not working properly.

When I created a test synonym and it invoked the DBMS_SNAPSHOT I knew I had to recreate DBMS_MVIEW.

All times are GMT -4. The time now is 08:11 PM.