Simulate data migration using music videos

Http://boylook.itpub.net/post/43144/520543

The biggest feature of MV data migration is its flexibility, which enables cross-platform and cross-database version migration and data restructuring and optimization. The implementation principle of this method requires that the source table object has a primary key for MV refresh. Create a music video log on the source table, create a table with the same structure in the target database, and use the prebuilt method to create the music video on the target database. The first time the music is fully refreshed, incremental refresh is always used, when switching, you only need to refresh the incremental log, delete the MV, and retain the target table.

-- Create a source table

SQL> create table from_table (id number, num number );

Table created.

-- Add a primary key

SQL> alter table from_table add constraint pk_from primary key (id );

Table altered.

-- Create a target table
. Then, create a primary key or a unique non-empty constraint on the table.

SQL> create table to_table (id number, num number );

Table created.

SQL> alter table to_table add constraint pk_to primary key (id );

Table altered.

SQL> insert into from_table select rownum, rownum * 100 from dba_objects where rownum <= 10;

10 rows created.

SQL> commit;

Commit complete.

-- Create music video logs in the source table

SQL> create materialized view log on from_table;

Materialized view log created.

-- Use prebuilt to create music videos on the target table

SQL> create materialized view to_table on prebuilt table refresh fast as select * from from_table;

Materialized view created.

SQL> select count (*) from to_table;

COUNT (*)
----------
0

-- Execute full refresh
SQL> exec dbms_mview.refresh ('to _ table', method => 'complete ');

PL/SQL procedure successfully completed.

SQL> select count (*) from to_table;

COUNT (*)
----------
10

-- Execute an incremental refresh. Before incremental refresh, make sure that both the source and target tables have primary keys. Otherwise, incremental refresh cannot be completed.

SQL> exec dbms_mview.refresh ('to _ table ');

PL/SQL procedure successfully completed.

SQL> select * from to_table;

ID NUM
--------------------
1 100
2 200
3 300
4 400
5 500
6 600
7 700
8 800
9 900
10 1000

10 rows selected.

-- Create an automatic refresh job to synchronize incremental logs every 30 seconds.

SQL> @ create_program
Appendix: cat create_program. SQL
Begin
Dbms_scheduler.create_program
(
Program_name => 'refresh_to_table ',
Program_type => 'plsql _ Block ',
Program_action => 'in in dbms_mview.refresh (''to _ table''); end ;',
Enabled => TRUE
);
End;
/

PL/SQL procedure successfully completed.

SQL> @ create_scheduler
Appendix: cat create_scheduler. SQL
Begin
Dbms_scheduler.create_schedule
(
Schedule_name => 'every _ 30_seconds ',
Start_date => policimestamp,
Repeat_interval => 'freq = SECONDLY; INTERVAL = 30'
);
End;
/

PL/SQL procedure successfully completed.

SQL> @ create_job
Appendix: cat create_job. SQL
Begin
Dbms_scheduler.create_job
(
Job_name => 'secondly _ refresh ',
Program_name => 'refresh_to_table ',
Schedule_name => 'every _ 30_seconds ',
Enabled => TRUE
);
End;
/

PL/SQL procedure successfully completed.

-- Run the job

SQL> exec dbms_scheduler.run_job ('secondly _ refresh ');

PL/SQL procedure successfully completed.

-- Continue operations on the source table

SQL> insert into from_table values (11,1 );

1 row created.

SQL> commit;

Commit complete.

SQL> select count (*) from to_table;

COUNT (*)
----------
11

SQL> insert into from_table select rownum + 11, rownum * 1000 from dba_objects where rownum <= 9;

9 rows created.

SQL> commit;

Commit complete.

SQL> select count (*) from to_table;

COUNT (*)
----------
20

SQL> select * from to_table;

ID NUM
--------------------
1 100
2 200
3 300
4 400
5 500
6 600
7 700
8 800
9 900
10 1000
11 1

ID NUM
--------------------
13 2000
14 3000
20 9000
17 6000
18 7000
12 1000
15 4000
16 5000
19 8000

20 rows selected.

SQL> update from_table set num = 1500 where id = 11;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from to_table;

ID NUM
--------------------
1 100
2 200
3 300
4 400
5 500
6 600
7 700
8 800
9 900
10 1000
11 1500

ID NUM
--------------------
13 2000
14 3000
20 9000
17 6000
18 7000
12 1000
15 4000
16 5000
19 8000

20 rows selected.

SQL> exec dbms_scheduler.drop_job ('secondly _ refresh ');

PL/SQL procedure successfully completed.

SQL> delete from to_table where rownum = 1;
Delete from to_table where rownum = 1
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

-- After refresh is completed, delete the MV log and MV. On the target database, after the MV is deleted, the table and data still exist.

SQL> drop materialized view to_table;

Materialized view dropped.

SQL> drop materialized view log on from_table;

Materialized view log dropped.

SQL> select * from to_table;

ID NUM
--------------------
1 100
2 200
3 300
4 400
5 500
6 600
7 700
8 800
9 900
10 1000
11 1500

ID NUM
--------------------
13 2000
14 3000
20 9000
17 6000
18 7000
12 1000
15 4000
16 5000
19 8000

20 rows selected.

-- Finally, create an object that is dependent on the table

This article is from "MIKE's old blog" blog, please be sure to keep this source http://boylook.blog.51cto.com/7934327/1298617