| After "Drop Materialized View" it's data continuing be shown [message #683422] |
Tue, 12 January 2021 18:31  |
 |
jrgOra
Messages: 2
Registered: January 2021
Location: São Paulo - Brazil
|
Junior Member |
|
|
Hi everyone
it sounds quite strange issue but it's been hard for me to understand why it is happening and how to solve it. (my environment is Oracle DB 12c 12.2.0.1.0 on Win 64)
1) I created a MV
CREATE MATERIALIZED VIEW MVW_CARTEIRAATIVOHIST
LOGGING NOCOMPRESS INMEMORY MEMCOMPRESS FOR QUERY LOW DISTRIBUTE AUTO NO DUPLICATE PRIORITY NONE NOCACHE NOPARALLEL USING INDEX REFRESH ON DEMAND FORCE USING DEFAULT LOCAL ROLLBACK SEGMENT ENABLE QUERY REWRITE
AS SELECT .....
Materialized view MVW_CARTEIRAATIVOHIST created.
2) Select data to check it
select * from vw_carteiraativohist; then got an
ORA-01476: divisor is equal to zero
01476. 00000 - "divisor is equal to zero"
*Cause:
*Action:
however , for certain rows the select works
select * from vw_carteiraativohist where cart_id = 14; it brings me the corresponding rows .
3) I dropped the MV in order to solve the division by zero issue and try to recreate it later
drop materialized view "<schema_name>"."MVW_CARTEIRAATIVOHIST";
Materialized view "<schema_name>"."MVW_CARTEIRAATIVOHIST" dropped.
4) Check to see if it was drooped :
select object_name, object_type, created,status from user_objects where object_name = 'MVW_CARTEIRAATIVOHIST'
==> no rows returned
5) Query again , just to guarantee there is no MV anymore
select * from vw_carteiraativohist where cart_id = 14
==> it brings the corresponding rows
For my surprise, there are still data in it ! IT'S STRANGE !! How is it possible ??
I appreciate any help .
|
|
|
|
|
|
| Re: After "Drop Materialized View" it's data continuing be shown [message #683426 is a reply to message #683422] |
Wed, 13 January 2021 00:41  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
jrgOra wrote on Wed, 13 January 2021 01:31For my surprise, there are still data in it ! IT'S STRANGE !! How is it possible ??
I appreciate any help .
Hi,
At the first sight, materialized view is named MVW_CARTEIRAATIVOHIST, however the query is on VW_CARTEIRAATIVOHIST (note the missing first letter).
So, you are querying different object, probably the underlying "normal" view. You may checking it similarly:
select object_name, object_type, created, status
from user_objects
where object_name = 'VW_CARTEIRAATIVOHIST'; |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
