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 ?
A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.
In an Oracle database, a materialized view log is a table associated with the master table of a materialized view. When master table data undergoes DML changes (such as INSERT, UPDATE, or DELETE), the Oracle database stores rows describing those changes in the materialized view log.
MLOG$ Table. An MLOG$ table is the Snapshot Log table created against a base table. A row is inserted into the table each time a change (insert/update/delete) is made to the base table.
You can use a materialized view in any SQL query by referencing the materialized view name as the data source, like a table or standard view. When a query accesses a materialized view, it sees only the data that is stored in the materialized view as of its most recent refresh.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With