模拟PREBUILD MV迁移数据

最新推荐文章于 2024-11-16 17:02:51 发布

csubx4572

最新推荐文章于 2024-11-16 17:02:51 发布

阅读量56

点赞数

文章标签：数据库

主站点
创建用户：
SQL> create user test identified by test
2 ;

User created.

SQL> grant connect,resource,unlimited tablespace to test;

Grant succeeded.

SQL> grant dba to test;

Grant succeeded.

SQL> conn test/test
Connected.
创建需要迁移的表：
SQL> create table big_t1 as select * from all_objects;

Table created.

SQL> select count(1) from big_t1;

COUNT(1)
----------
49331

创建VIEW LOG，这里出现问题，应该该表没有主键，需要创建主键。

SQL> create materialized view log on big_t1;
create materialized view log on big_t1
*
ERROR at line 1:
ORA-12014: table 'BIG_T1' does not contain a primary key constraint

SQL> desc big_t1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OWNER                                     NOT NULL VARCHAR2(30)
OBJECT_NAME                               NOT NULL VARCHAR2(30)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                 NOT NULL NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(19)
CREATED                                   NOT NULL DATE
LAST_DDL_TIME                             NOT NULL DATE
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)

创建主键：

SQL> alter table big_t1 add primary key (object_id);

Table altered.

SQL> create materialized view log on big_t1;

Materialized view log created.

SQL> show user
USER is "TEST"

复制端：

1、创建DBLINK

create public database link asm using 'MDCL';

2、生成需要复制的表，创建主键，并在该表上建立PREBUILT MV

SQL> create table big_t1 as select * from big_t1@asm where 1=2;

表已创建。