ITPub博客

首页 > Linux操作系统 > Linux操作系统 > TRUNCATING A SNAPSHOT LOG

TRUNCATING A SNAPSHOT LOG

原创 Linux操作系统 作者:syzxlyx_cu 时间:2009-09-28 09:47:55 0 删除 编辑

TRUNCATING A SNAPSHOT LOG.

If a snapshot log grows and allocates many extents, purging the log of rows
does not reduce the amount of space allocated for the log. To reduce the space
allocated for a snapshot log:

1. Acquire an exclusive lock on the master table to prevent updates from occurring
during the following process. For example, issue a statement similar to the following:

     LOCK TABLE scott.emp IN EXCLUSIVE MODE;

2. Using a second database session, copy the rows in the snapshot log (in other words,
the MLOG$ base table) to a temporary table. For example, issue a statement similar
to the following:

     CREATE TABLE scott.templog AS SELECT * FROM scott.mlog$_emp;

3. Using the second session, truncate the log using the SQL statement TRUNCATE.
For example, issue a statement similar to the following:

     TRUNCATE TABLE scott.mlog$_emp;

4. Using the second session, reinsert the old rows so that you do not have to perform.
a complete refresh of the dependent snapshots. For example, issue a statement similar
to the following:

     INSERT INTO scott.mlog$_emp SELECT * FROM scott.templog;
     DROP TABLE scott.templog;

5.Using the first session, release the exclusive lock on the master table by performing
a rollback:

     ROLLBACK;

Note: Any changes made to the master table 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. Then it’s better to truncate the snapshot log when it is empty.
Only the owner of a snapshot log or a user with the DELETE ANY TABLE system privilege can
truncate a snapshot log. 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20542911/viewspace-615682/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 优化方法论
请登录后发表评论 登录
全部评论

注册时间:2009-09-28

  • 博文量
    125
  • 访问量
    124467