Sunday, May 22, 2011

Find out MVIEWs stopping MVIEW LOG being purged

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