-
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
-
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 ?
-
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.
-
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
-
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.
-
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
-
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
-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|