分布式数据库的备份，历史数据的移出问题。

cgh_chen

快照的建立主要是环境的配置。
如果环境配置好，建立快照就是一句话的事情了
CREATE SNAPSHOT SNAP_NAME AS SELECT COL_NAME.....  FROM USER_NAME.TABLE_NAME@REMOTE_SITE;
好像是这样吧，
关于你的问题，好像在一个快照里面好像不能实现的，不知道可不可以在快照上建立触发器，
然后建立另外一个表，更新另外一个表中的字段值就是了。

ydwangcn

我也是这样想：建一个快照将远程表复制过来，在快照上建个触发器，更新目标表。我目前想到的办法也只有这一个。

yangbright

我们也有这种情况。我们叫历史数据迁移。
我们的做法是建立一个独立的INSTANCE做为历史数据库。
当我们认为数据不会再被改时就迁移到库，只供用户查询。
通常是晚上用程序来做，一边INSERT，一边DELETE。

cgh_chen

yangbright
你的方法在单个数据库下可行，现在是我在表上建立的快照，快照记录也不知道如何删除。
上面有的同志提到的方法，我还没有来得及试验。

yangbright

多个数据库的我没人试过.
按正常来说,快照记录会随原来的数据删除自动删除的.
你可以试试. 很久以前我们有这样做过啊

cgh_chen

快照记录会随原来的数据删除自动删除的。
这个我是知道的，
就是如果我要把去年的记录全部删除，那个量是非常大的。
因为网络条件很差，还有其他数据要传输，这样实现的可能性几乎为零。
还是系统原来设计就不好，不过现在也不知道，究竟设计就算 好了

john_zhu

ydwangcn：
可以使用Metaview来进行增量

CGH_CHEN
不但是删除，一般在建立复制环境的时候也同样有这个问题，我上面说的方法肯定是可以的

cgh_chen

找到了删除基础表的删除方法
How to Disable a Replication Trigger in Oracle 8.x:
===================================================

Prior to Oracle8, snapshot triggers: $RT, USTRG$ and TLOG$ were PL/SQL
procedures that can be seen and modified (although unsupported) by
users. With the release of Oracle8, the INTERNALIZED trigger was
introduced.

The Internal triggers provide the same functionality as embedded
PL/SQL triggers, but they cannot be seen or modified by users.

Prior to Oracle8, users can easily disable these triggers by issuing
an ALTER TRIGGER command.  Since these triggers are now internal in
Oracle8, an ALTER TRIGGER command is not possible.

To disable the $RT triggers in Oracle8, you can execute the
DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION() procedure to destroy the
triggers before performing activity on the replicated table, and
execute the DBMS_SNAPSHOT.END_TABLE_REORGANIZATION() procedure to
recreate them.  These procedures are documented in the Replication Manual
to help reorganize your master tables preserving the ability to fast refresh.  
However, these procedures will only work with PRIMARY KEY snapshots because
only master tables whose snapshot is refreshed with PRIMARY KEY can be
reorganized and still have the ability to fast refresh.

Example:

Master Site
============
> execute DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION ('SCOTT','T1');
PL/SQL procedure successfully completed.

> INSERT INTO T1 VALUES (5,'THIS IS 5 ');
1 row created.

> COMMIT;
Commit complete.

> SELECT * FROM MLOG$_T1;
no rows selected

> EXECUTE DBMS_SNAPSHOT.END_TABLE_REORGANIZATION ('SCOTT','T1');
PL/SQL procedure successfully completed.

Snapshot Site
==============
> EXEC DBMS_SNAPSHOT.REFRESH('S_T1','F');
PL/SQL procedure successfully completed.
    NOTE:  This step is necessary to guarantee that a fast refresh will
           continue to work AFTER you enable the triggers.  
           If you skip this step, you will receive an ORA-12034
                "snapshot log on \"%s\".\"%s\" younger than last refresh"
           and will have to perform a complete refresh.
           ALL SNAPSHOTS refreshing off this master table must be refreshed

           You will also receive this error if your snapshot is NOT based
           on a PRIMARY KEY, but instead, ROWID.
        
Master Site
============
> INSERT INTO T1 VALUES (6,'I6 NO5?');
1 row created.

> COMMIT;
Commit complete.

> SELECT * FROM MLOG$_T1;
no rows selected

Snapshot Site
==============

> EXEC DBMS_SNAPSHOT.REFRESH('S_T1','F');
PL/SQL procedure successfully completed.


Master Site
============
> select * From t1;

      COL1 COL2
---------- ----------
         1 test1
         6 I6 NO5?
         5 THIS IS 5

Snapshot Site
==============
> select * From s_t1;

      COL1 COL2
---------- ----------
         1 test1
         6 I6 NO5

cgh_chen

不知道只读快照记录如何删除

cgh_chen

Oracle 说不能直接删除只读快照，我真的无路可走了吗？