You can translate the question and the replies:

Data getting removed from Materialized Table

Version 7.0.20190903 Oracle 12c as Cache DB. Hi We are using Materialized Table to store some project specific configurations. We are observing erratic behaviour- Materialized Table is getting truncated after 15-20 days. We understand that Cache Cleanup doesnt have any impact on the data. Also can not be deleted manually . We ensured that these materialized tables were not recreated. We checked with Oracle DBA as well , there is no change to Cache DB Please advise if you have encountered this behaviour?
Materialized Table
user
29-10-2021 01:46:40 -0400
code

3 Answers

Hi, As per [Materialized Tables documentation](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/materialized_tables/materialized_tables), Denodo Platform allows you to perform only insertion into a materialized table but it is not possible to update or delete them. Hence for your scenario, I would check the Oracle log files in order to get more information about the root cause. Hope this helps!
Denodo Team
29-10-2021 07:29:30 -0400
code
Hi Team, We analyzeed the Oracle Logs and we noticed following statement was executed - DELETE FROM C_CUSTOMERVDB_XXXXXXXXX WHERE rownum <= 100000 AND rowStatus = :1 OR ( expirationDate > :2 AND expirationDate < :3) No developer or admi has direct access on Oracle Cache Database to run this statement. Can you please advise
user
24-11-2021 00:15:53 -0500
Hi, I created a materialized table in Virtual DataPort, which allows me to perform insertion operations into it. But when I tried to perform deletion of the table’s data from VirtualDataPort, then it throws a “**Delete on materialized tables is not allowed**” error. This is the expected behavior because Materialized Tables allow performing only the insert operations. For your scenario, I would check the Oracle logs to identify the user who executed the Delete statement. Also, if I had the [**Denodo monitor**](https://community.denodo.com/docs/html/browse/latest/en/vdp/administration/monitoring_the_virtual_dataport_server/denodo_monitor/denodo_monitor) launched, I would check the Denodo Monitor log files to determine whether any query was triggered from the Virtual DataPort at that particular time. Hope this helps!
Denodo Team
24-11-2021 07:43:20 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here