| Materialized view error [message #181841] |
Tue, 11 July 2006 20:31  |
ehegagoka
Messages: 493
Registered: July 2005
|
Senior Member |
|
|
hi!
we have procedure named "cts_update_snapshot" it inserts on a table "cts_snapshot_tbl", this has a trigger named "CTS_SNAPSHOT_TBL_AIS", which has a procedure being called named "UDMAINLOGBOOKING", the procedure "UDMAINLOGBOOKING" updates on a table "MAINLOG". the procedure is:
CREATE OR REPLACE PROCEDURE "UDMAINLOGBOOKING"
(
THISCONTAINERNO VARCHAR2,
FIRSTTIME DATE
)
as
begin
UPDATE MAINLOG SET BOOKINGNO = GETBOOKINGBYTIME( CONTAINERNO, LOGTIME)
WHERE CONTAINERNO = THISCONTAINERNO AND LOGTIME >= FIRSTTIME;
end;
now when i execute the procedure cts_update_snapshot i get the following error:
SQL> exec cts_update_snapshot(to_date('01-FEB-2006', 'DD-MON-RRRR'),to_date('28-FEB-2006', 'DD-MON-RRRR'));
begin cts_update_snapshot(to_date('01-FEB-2006', 'DD-MON-RRRR'),to_date('28-FEB-2006', 'DD-MON-RRRR')); end;
ORA-12096: error in materialized view log on "CTS"."MAINLOG"
ORA-00942: table or view does not exist
ORA-06512: at "CTS.UDMAINLOGBOOKING", line 8
ORA-06512: at "CTS.CHANGEMAINLOGBOOKING", line 10
ORA-06512: at "CTS.CTS_SNAPSHOT_TBL_AIS", line 2
ORA-04088: error during execution of trigger 'CTS.CTS_SNAPSHOT_TBL_AIS'
ORA-06512: at "CTS.CTS_UPDATE_SNAPSHOT", line 45
ORA-06512: at line 1
"MAINLOG" table exist, the user CTS is the owner of all the objects, what seems to be the problem? i dont know much of materialized views, if i need to recreate the "materialized view log" how would it be?
thank you so much sir/mam =)
|
|
|
|
| Re: Materialized view error [message #181844 is a reply to message #181841] |
Tue, 11 July 2006 21:38  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Someone may have dropped the Materialized View Log. Try this:
SELECT *
FROM user_tables
WHERE table_name = 'MAINLOG'
SELECT *
FROM user_mveiw_logs
WHERE master = 'MAINLOG'
SELECT *
FROM user_tables
WHERE table_name = (
SELECT log_table
FROM user_mveiw_logs
WHERE master = 'MAINLOG'
)
Do all of these return a row? If just the third one fails, then you need to DROP and recreate the materialized view log on MAINLOG.
Ross Leishman
|
|
|
|
| Re: Materialized view error [message #181848 is a reply to message #181844] |
Tue, 11 July 2006 22:12 |
ehegagoka
Messages: 493
Registered: July 2005
|
Senior Member |
|
|
hi!
thanks for your reply, i got the script for those view logs, and then i execute this
SQL> drop materialized view log on mainlog;
Materialized view log dropped
SQL>
SQL> CREATE MATERIALIZED VIEW LOG
2 ON "CTS"."MAINLOG"
3 TABLESPACE "CTS_DATA" PARALLEL ( DEGREE DEFAULT ) CACHE
4 WITH ROWID
5 INCLUDING NEW VALUES
6 /
Materialized view log created
and then i rerun it
SQL> exec cts_update_snapshot(to_date('01-FEB-2006', 'DD-MON-RRRR'),to_date('28-FEB-2006', 'DD-MON-RRRR'));
begin cts_update_snapshot(to_date('01-FEB-2006', 'DD-MON-RRRR'),to_date('28-FEB-2006', 'DD-MON-RRRR')); end;
ORA-12096: error in materialized view log on "CTS"."LASTLOG"
ORA-00942: table or view does not exist
ORA-06512: at "CTS.LLUPDATE", line 39
ORA-06512: at "CTS.MAINLOG_AUR", line 2
ORA-04088: error during execution of trigger 'CTS.MAINLOG_AUR'
ORA-06512: at "CTS.UDMAINLOGBOOKING", line 8
ORA-06512: at "CTS.CHANGEMAINLOGBOOKING", line 10
ORA-06512: at "CTS.CTS_SNAPSHOT_TBL_AIS", line 2
ORA-04088: error during execution of trigger 'CTS.CTS_SNAPSHOT_TBL_AIS'
ORA-06512: at "CTS.CTS_UPDATE_SNAPSHOT", line 45
ORA-06512: at line 1
SQL> drop materialized view log on lastlog
2 /
Materialized view log dropped
SQL>
SQL> CREATE MATERIALIZED VIEW LOG
2 ON "CTS"."LASTLOG"
3 TABLESPACE "CTS_DATA" PARALLEL ( DEGREE DEFAULT ) CACHE
4 WITH ROWID
5 INCLUDING NEW VALUES
6 /
Materialized view log created
i'm just thinking why when i dropped it, it shows the view log exists but has an error like that? i'm still running the script, hopefully that would be the last view log error, thanks again =)
|
|
|
|
| Re: Materialized view error [message #181895 is a reply to message #181848] |
Wed, 12 July 2006 02:17 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
To find out the name of the table that does not exist, trace the session and then run the trace file through TK*Prof. The TK*Prof output will list all of the SQL's executed either by you or internally by Oracle, with the errored ones up the top - that's where you will find which table is missing.
See the Oracle Performance Tuning guide for instructions on SQL*Trace and TK*Prof.
Ross Leishman
|
|
|
|
| Re: Materialized view error [message #181899 is a reply to message #181895] |
Wed, 12 July 2006 02:29 |
ehegagoka
Messages: 493
Registered: July 2005
|
Senior Member |
|
|
hi!
thanks for the reply, i dont have direct access to the database, im just connecting from plsqldev on my pc, ill check if one of the dba's here will have time for it, thanks again sir, also another question on my cts_update_snapshot procedure..
the code structure is
proc a
cursor c...
begin
for c in...
select into statements..
delete from statements..
etc..
end loop;
end;
would it be faster if i change all those select & delete statements to execute immediate? i mean
execute immediate 'select..'
into...
using..
and
execute immediate 'delete from...'
using...
thanks again =)
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
