RUN as DBA User
RUN as SOURCE_USER
RUN as TARGET_USER
donghua@rh6:~$ sqlplus donghua/donghua@orcl
SQL*Plus: Release 11.2.0.2.0 Production on Sun May 22 11:27:15 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user source_user identified by source_user;
User created.
SQL> grant create session,create table to source_user;
Grant succeeded.
SQL> alter user source_user quota unlimited on users;
User altered.
SQL> create user target_user identified by target_user;
User created.
SQL> grant create session, create table, create materialized view to target_user;
Grant succeeded.
SQL> alter user target_user quota unlimited on users;
User altered.
SQL> grant create database link to target_user;
Grant succeeded.
SQL> create user snapshot_user identified by snapshot_user;
User created.
SQL> grant create session to snapshot_user;
Grant succeeded.
SQL> conn source_user/source_user@orcl
Connected.
SQL> create table tbl_large_table
2 (id number,
3 name char(100));
Table created.
SQL> alter table tbl_large_table
2 add constraint pk_tbl_large_table
3 primary key (id);
Table altered.
SQL> create materialized view log on tbl_large_table
2 tablespace users
3 with rowid, primary key
4 including new values;
Materialized view log created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
MLOG$_TBL_LARGE_TABLE TABLE
RUPD$_TBL_LARGE_TABLE TABLE
TBL_LARGE_TABLE TABLE
SQL> grant select on tbl_large_table to snapshot_user;
Grant succeeded.
SQL> grant select on mlog$_tbl_large_table to snapshot_user;
Grant succeeded.
SQL> grant select on rupd$_tbl_large_table to snapshot_user;
Grant succeeded.
SQL> desc user_registered_mviews;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
NAME NOT NULL VARCHAR2(30)
MVIEW_SITE NOT NULL VARCHAR2(128)
CAN_USE_LOG VARCHAR2(3)
UPDATABLE VARCHAR2(3)
REFRESH_METHOD VARCHAR2(11)
MVIEW_ID NUMBER(38)
VERSION VARCHAR2(26)
QUERY_TXT LONG
SQL> select count(*) from user_registered_mviews;
COUNT(*)
----------
0
SQL> set pages 999
SQL> select * from user_mview_logs;
LOG_OWNER MASTER
------------------------------ ------------------------------
LOG_TABLE LOG_TRIGGER ROW PRI OBJ FIL
------------------------------ ------------------------------ --- --- --- ---
SEQ INC PUR PUR PURGE_STA
--- --- --- --- ---------
PURGE_INTERVAL
--------------------------------------------------------------------------------
LAST_PURG LAST_PURGE_STATUS NUM_ROWS_PURGED COM
--------- ----------------- --------------- ---
SOURCE_USER TBL_LARGE_TABLE
MLOG$_TBL_LARGE_TABLE YES YES NO NO
NO YES NO NO
NO
donghua@rh6:~$ sqlplus target_user/target_user@orcl
SQL*Plus: Release 11.2.0.2.0 Production on Sun May 22 12:05:26 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create database link orcl.local connect to snapshot_user identified by snapshot_user using 'orcl';
Database link created.
SQL> select systimestamp from dual@orcl.local;
SYSTIMESTAMP
---------------------------------------------------------------------------
22-MAY-11 12.11.15.187693 PM +08:00
SQL>
SQL> create materialized view tbl_large_table_sum
2 refresh fast on demand
3 as
4 select sum(id) from source_user.tbl_large_table@orcl.local;
Materialized view created.
SQL> select * from user_registered_mviews;
OWNER NAME
------------------------------ ------------------------------
MVIEW_SITE
--------------------------------------------------------------------------------
CAN UPD REFRESH_MET MVIEW_ID VERSION
--- --- ----------- ---------- --------------------------
QUERY_TXT
--------------------------------------------------------------------------------
TARGET_USER TBL_LARGE_TABLE_SUM
ORCL
YES NO ROWID 23 ORACLE 8 MATERIALIZED VIEW
select sum(id) from source_user.tbl_large_table@orcl.local
SQL> create materialized view tbl_large_table
2 refresh fast on demand
3 as
4 select * from source_user.tbl_large_table@orcl.local;
Materialized view created.
SQL> select * from user_registered_mviews;
OWNER NAME
------------------------------ ------------------------------
MVIEW_SITE
--------------------------------------------------------------------------------
CAN UPD REFRESH_MET MVIEW_ID VERSION
--- --- ----------- ---------- --------------------------
QUERY_TXT
--------------------------------------------------------------------------------
TARGET_USER TBL_LARGE_TABLE
ORCL
YES NO PRIMARY KEY 24 ORACLE 8 MATERIALIZED VIEW
SELECT "TBL_LARGE_TABLE"."ID" "ID","TBL_LARGE_TABLE"."NAME" "NAME" FROM "SOURCE_
TARGET_USER TBL_LARGE_TABLE_SUM
ORCL
YES NO ROWID 23 ORACLE 8 MATERIALIZED VIEW
select sum(id) from source_user.tbl_large_table@orcl.local
SQL> exec dbms_mview.refresh('tbl_large_table');
PL/SQL procedure successfully completed.
SQL> select * from tbl_large_table;
no rows selected
SQL> exec dbms_mview.refresh('tbl_large_table_sum');
PL/SQL procedure successfully completed.
SQL> select * from tbl_large_table_sum;
SUM(ID)
----------
SQL> select * from user_mview_refresh_times;
OWNER NAME
------------------------------ ------------------------------
MASTER_OWNER MASTER LAST_REFRESH
------------------------------ ------------------------------ ------------------
TARGET_USER TBL_LARGE_TABLE_SUM
SOURCE_USER TBL_LARGE_TABLE 22-MAY-11 12:30:18
TARGET_USER TBL_LARGE_TABLE
SOURCE_USER TBL_LARGE_TABLE 22-MAY-11 12:29:59
SQL> desc user_base_table_mviews
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
MASTER NOT NULL VARCHAR2(30)
MVIEW_LAST_REFRESH_TIME NOT NULL DATE
MVIEW_ID NUMBER(38)
SQL> select * from user_base_table_mviews;
OWNER MASTER MVIEW_LAST_REFRESH
------------------------------ ------------------------------ ------------------
MVIEW_ID
----------
SOURCE_USER TBL_LARGE_TABLE 22-MAY-11 12:30:18
23
SOURCE_USER TBL_LARGE_TABLE 22-MAY-11 12:29:59
24
SQL> desc user_mview_logs
Name Null? Type
----------------------------------------- -------- ----------------------------
LOG_OWNER VARCHAR2(30)
MASTER VARCHAR2(30)
LOG_TABLE VARCHAR2(30)
LOG_TRIGGER VARCHAR2(30)
ROWIDS VARCHAR2(3)
PRIMARY_KEY VARCHAR2(3)
OBJECT_ID VARCHAR2(3)
FILTER_COLUMNS VARCHAR2(3)
SEQUENCE VARCHAR2(3)
INCLUDE_NEW_VALUES VARCHAR2(3)
PURGE_ASYNCHRONOUS VARCHAR2(3)
PURGE_DEFERRED VARCHAR2(3)
PURGE_START DATE
PURGE_INTERVAL VARCHAR2(200)
LAST_PURGE_DATE DATE
LAST_PURGE_STATUS NUMBER
NUM_ROWS_PURGED NUMBER
COMMIT_SCN_BASED VARCHAR2(3)
SQL> select * from user_mview_logs;
LOG_OWNER MASTER
------------------------------ ------------------------------
LOG_TABLE LOG_TRIGGER ROW PRI OBJ FIL
------------------------------ ------------------------------ --- --- --- ---
SEQ INC PUR PUR PURGE_START
--- --- --- --- ------------------
PURGE_INTERVAL
--------------------------------------------------------------------------------
LAST_PURGE_DATE LAST_PURGE_STATUS NUM_ROWS_PURGED COM
------------------ ----------------- --------------- ---
SOURCE_USER TBL_LARGE_TABLE
MLOG$_TBL_LARGE_TABLE YES YES NO NO
NO YES NO NO
22-MAY-11 12:30:18 0 0 NO
-- Generate Data in Source (source_user)
SQL> insert into tbl_large_table
2 select rownum, 'x' from dual connect by rownum < 100000;
99999 rows created.
SQL> commit;
Commit complete.
SQL> delete from tbl_large_table where mod(id,4)=0;
24999 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from tbl_large_table;
COUNT(*)
----------
75000
SQL> select last_purge_date,num_rows_purged from user_mview_logs;
LAST_PURGE_DATE NUM_ROWS_PURGED
------------------ ---------------
22-MAY-11 12:30:18 0
SQL> select mview_id,mview_last_refresh_time from user_base_table_mviews;
MVIEW_ID MVIEW_LAST_REFRESH
---------- ------------------
23 22-MAY-11 12:30:18
24 22-MAY-11 12:29:59
SQL> col segment_name for a30
SQL> select segment_name, blocks from user_segments;
SEGMENT_NAME BLOCKS
------------------------------ ----------
TBL_LARGE_TABLE 1664
MLOG$_TBL_LARGE_TABLE 1024
PK_TBL_LARGE_TABLE 256
SQL> select count(*) from mlog$_tbl_large_table;
COUNT(*)
----------
124998
SQL> exec dbms_mview.refresh('tbl_large_table_sum');
BEGIN dbms_mview.refresh('tbl_large_table_sum'); END;
*
ERROR at line 1:
ORA-32314: REFRESH FAST of "TARGET_USER"."TBL_LARGE_TABLE_SUM" unsupported
after deletes/updates
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2566
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2779
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2748
ORA-06512: at line 1
SQL> exec dbms_mview.refresh('tbl_large_table_sum',method=>'complete');
PL/SQL procedure successfully completed.
SQL> select * from user_mview_refresh_times;
OWNER NAME
------------------------------ ------------------------------
MASTER_OWNER MASTER LAST_REFRESH
------------------------------ ------------------------------ ------------------
TARGET_USER TBL_LARGE_TABLE_SUM
SOURCE_USER TBL_LARGE_TABLE 22-MAY-11 12:49:20
TARGET_USER TBL_LARGE_TABLE
SOURCE_USER TBL_LARGE_TABLE 22-MAY-11 12:29:59
SQL> select mview_id,mview_last_refresh_time from user_base_table_mviews;
MVIEW_ID MVIEW_LAST_REFRESH
---------- ------------------
23 22-MAY-11 12:49:20
24 22-MAY-11 12:29:59
SQL> select last_purge_date,num_rows_purged from user_mview_logs;
LAST_PURGE_DATE NUM_ROWS_PURGED
------------------ ---------------
22-MAY-11 12:49:20 0
SQL> select count(*) from mlog$_tbl_large_table;
COUNT(*)
----------
124998
SQL> select segment_name, blocks from user_segments;
SEGMENT_NAME BLOCKS
------------------------------ ----------
TBL_LARGE_TABLE 1664
MLOG$_TBL_LARGE_TABLE 1024
PK_TBL_LARGE_TABLE 256
SQL> exec dbms_mview.refresh('tbl_large_table');
PL/SQL procedure successfully completed.
SQL> select * from user_mview_refresh_times;
OWNER NAME
------------------------------ ------------------------------
MASTER_OWNER MASTER LAST_REFRESH
------------------------------ ------------------------------ ------------------
TARGET_USER TBL_LARGE_TABLE_SUM
SOURCE_USER TBL_LARGE_TABLE 22-MAY-11 12:49:20
TARGET_USER TBL_LARGE_TABLE
SOURCE_USER TBL_LARGE_TABLE 22-MAY-11 12:53:09
SQL> select mview_id,mview_last_refresh_time from user_base_table_mviews;
MVIEW_ID MVIEW_LAST_REFRESH
---------- ------------------
23 22-MAY-11 12:49:20
24 22-MAY-11 12:53:09
SQL> select last_purge_date,num_rows_purged from user_mview_logs;
LAST_PURGE_DATE NUM_ROWS_PURGED
------------------ ---------------
22-MAY-11 12:53:15 124998
SQL> select count(*) from mlog$_tbl_large_table;
COUNT(*)
----------
0
SQL> select segment_name, blocks from user_segments;
SEGMENT_NAME BLOCKS
------------------------------ ----------
TBL_LARGE_TABLE 1664
MLOG$_TBL_LARGE_TABLE 1024
PK_TBL_LARGE_TABLE 256
SQL> alter table MLOG$_TBL_LARGE_TABLE move;
Table altered.
SQL> select segment_name, blocks from user_segments;
SEGMENT_NAME BLOCKS
------------------------------ ----------
TBL_LARGE_TABLE 1664
MLOG$_TBL_LARGE_TABLE 8
PK_TBL_LARGE_TABLE 256
it's possible to truncate table "MLOG$_TBL_LARGE_TABLE" followed by performing complete refresh for all depending mviews.
ReplyDelete