清理物化视图日志-凯发app官方网站

always try to keep a materialized view log as small as possible to minimize the database space that it uses. to remove rows from a materialized view log and make space for newer log records, you can perform one of the following actions:

refresh the materialized views associated with the log so that oracle can purge rows from the materialized view log.
manually purge records in the log by deleting rows required only by the nth least recently refreshed materialized views.

to manually purge rows from a materialized view log, execute the purge_log procedure of the dbms_mview package at the database that contains the log. for example, to purge entries from the materialized view log of the customers table that are necessary only for the least recently refreshed materialized view, execute the following procedure:

begin

master => 'hr.employees',

num => 1,

flag => 'delete');

end;

only the owner of a materialized view log or a user with the execute privilege for the dbms_mview package can purge rows from the materialized view log by executing the purge_log procedure.

truncating a materialized view log

if a materialized view log grows and allocates many extents, then purging the log of rows does not reduce the amount of space allocated for the log. in such cases, you should truncate the materialized view log. only the owner of a materialized view log or a user with the delete any table system privilege can truncate a materialized view log.

to reduce the space allocated for a materialized view log by truncating it, complete the following steps:

step 1 acquire an exclusive lock on the master table or master materialized view to prevent updates from occurring during the following process. for example, issue a statement similar to the following:

lock table hr.employees in exclusive mode;

step 2 using a second database session, copy the rows in the materialized view log (in other words, the mlog$ base table) to a temporary table. for example, issue a statement similar to the following:

create table hr.templog as select * from hr.mlog$_employees;

step 3 using the second session, truncate the log using the sql statement truncate. for example, issue a statement similar to the following:

truncate hr.mlog$_employees;

step 4 using the second session, reinsert the old rows so that you do not have to perform a complete refresh of the dependent materialized views. for example, issue statements similar to the following:

insert into hr.mlog$_employees select * from hr.templog;

drop table hr.templog;

step 5 using the first session, release the exclusive lock on the master table or master materialized view by performing a rollback:

rollback;

note:

any changes made to the master table or master materialized view between the time you copy the rows to a new location and when you truncate the log do not appear until after you perform a complete refresh.

我的示例：

select'数据量 ' || count(0) from t3 union

select'需更新量 ' || count(0) from mlog$_t3;

locktable t3 in exclusivemode;

createtable templog as select * from mlog$_t3;

truncatetable mlog$_t3;

insertinto mlog$_t3 select * from templog;

droptable templog;

rollback;

阅读(4741) | 评论(0) | 转发(0) |

上一篇：删除ocr中注册的监听--zyq

下一篇：练习物化视图日志异常的处理

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们