Home » SQL & PL/SQL » SQL & PL/SQL » After "Drop Materialized View" it's data continuing be shown (Oracle Database 12c EE 12.2.0.1.0 on Windows 64bit)
After "Drop Materialized View" it's data continuing be shown [message #683422] Tue, 12 January 2021 18:31 Go to next message
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 #683424 is a reply to message #683422] Wed, 13 January 2021 00:06 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, make sure that lines of code do not exceed 100 characters.

The error comes from what you did not posted: "AS SELECT ....."

Please post the complete definition (CREATE statements) of:
- mview
- views
- tables
- all relevant objects

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 Go to previous messageGo to next message
flyboy
Messages: 1899
Registered: November 2006
Senior Member
jrgOra wrote on Wed, 13 January 2021 01:31
For 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';
Re: After "Drop Materialized View" it's data continuing be shown [message #683432 is a reply to message #683422] Wed, 13 January 2021 06:10 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3045
Registered: January 2010
Location: Connecticut, USA
Senior Member
jrgOra wrote on Tue, 12 January 2021 19:31

CREATE MATERIALIZED VIEW MVW_CARTEIRAATIVOHIST

select * from vw_carteiraativohist;
Something doesn't add up.

SY.
Re: After "Drop Materialized View" it's data continuing be shown [message #683438 is a reply to message #683424] Wed, 13 January 2021 17:21 Go to previous message
jrgOra
Messages: 2
Registered: January 2021
Location: São Paulo - Brazil
Junior Member
Michel

thanks for the instructions.

Pleaes remove this thread , since I ended up discovering that I made a mistake during the select and there is no issue at all with my materialized views.

I apologize for this disturbance.

Thank you.
Previous Topic: Substring Query in Oracle SQL
Next Topic: Weird PL/SQL behavior with SQL statement containing a function in WITH clause
Goto Forum:
  


Current Time: Tue Apr 13 17:52:45 CDT 2021