CODEWITHSUNDEEP
oracle11gmaterialized-views
user1860447
user1860447

Reputation: 1434

Get a list of materialized view log in oracle'

I want to get a list of materialized view logs in Oracle. How can I do that? I thought that this will give me a list: 

 select * from USER_BASE_TABLE_MVIEWS;

When a materialized view log was dropped and recreated by using the script similar to this :

 CREATE MATERIALIZED VIEW LOG ON "XXX"."STATUSES_AUD"
 PCTFREE 10 PCTUSED 30 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 ....

It does not show up when this query is run :

select * from USER_BASE_TABLE_MVIEWS;

Any ideas ?

Upvotes: 6

Views: 30848

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

Use [dba_|all_|user_]mview_logs. dba_mview_logs will show all the materialized view logs in the database but not everyone will have access to the dba_ views. You probably need create any dictionary. all_mview_logs will show you all the materialized view logs that you have access to. user_mview_logs will show you all the materialized view logs that you own.

Upvotes: 12

Related Questions