how to identify cache table is active or not

Hi Team Suppose I have a View : Employee and it's having a cache Table in Oracle as : EMPTABLE, now I did some changes to the same Employee view and now Cache Table changed to : EMPTABLE1, so in this way there is a invalid table in Cache DB with name : EMPTABLE. So how do I identify the invalid tables like above scenario? are there any queries to identify and drop these tables? Kindly share the ideas or thoughts. Regards Pavan
user
14-03-2019 15:22:54 -0400

1 Answer

Hi, I can use a query below to allow for locating the cache tables corresponding to the cached base views : ``` select * from GET_CACHE_TABLE () g, get_views(<database name>,null,null,null,null,null,null,null,null,null,null,null) v where g.input_database_name =<database name> and g.input_view_name = v.name; ``` I would then go to the cache database directly and delete all other tables except those given by the above query manually, so that I have only the latest cache tables left. Alternatively, I would follow the steps given in the Knowledge Base article [cleanup of the cache database](https://community.denodo.com/kb/view/document/%22This%20connection%20is%20part%20of%20a%20global%20transaction%22%20error?category=Common+Errors). For information about the above stored procedures, please refer to the sections [GET_CACHE_TABLE](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/stored_procedures/predefined_stored_procedures/get_cache_table) and [GET_VIEWS](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/stored_procedures/predefined_stored_procedures/get_views) of the Virtual DataPort VQL Guide. Hope this helps!
Denodo Team
15-03-2019 08:40:14 -0400
You must sign in to add an answer. If you do not have an account, you can register here