Alter Materialized View Move

Few days back a question was asked on OTN forum on materialized views.The question was

“can we use move command against MV & MV log to place them in another tablespace?something like alter materialized view move…”

Truly speaking i was not very sure to this question,so i replied no,but  it can be dropped and re-created back.But then Robert Geier (Oracle ACE member) answered it “YES”.So lets,check it out now ,is it really that we can do it??

In this example below,emp_mv is created in default tablespace ANDY of the user ANAND and then moved to MV_TEST tablespace.The materialized view emp_mv is created on scott.emp table.

SCOTT SESSION:-
SCOTT @ oracle >grant select on emp to anand;
Grant succeeded.
ANAND SESSION:-
ANAND @ oracle >select username,default_tablespace from dba_users where username in ('ANAND','SCOTT');

USERNAME                       DEFAULT_TABLESPACE                                                                                                                                                       
------------------------------ ------------------------------                                                                                                                                           
SCOTT                          USERS                                                                                                                                                                    
ANAND                          ANDY                                                                                                                                                                     

ANAND @ oracle >select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO                                                                                                                          
------------------------------ ---------- ---------- ---------- ---------- ---                                                                                                                          
MV_TEST                         292552704          0      35712          0 NO                                                                                                                           
TEST                            292618240         -1      35720         -1 NO                                                                                                                           
ANDY                             17432576         -1       2128         -1 NO                                                                                                                           

ANAND @ oracle >alter user scott quota unlimited on mv_test;

User altered.

ANAND @ oracle >select count(1) from scott.emp;

 COUNT(1)                                                                                                                                                                                                                                                
----------                                                                                                                                                                                                                                                
 16
SCOTT SESSION:-
SCOTT @ oracle >select count(1) from tab;

 COUNT(1)                                                                                                                                                                                              
----------                                                                                                                                                                                              
 4 

SCOTT @ oracle >create materialized view log on emp tablespace users;

Materialized view log created.

SCOTT @ oracle >select count(1) from tab;

 COUNT(1)                                                                                                                                                                                              
----------                                                                                                                                                                                              
 6 

SCOTT @ oracle >SELECT master, log_table, rowids, primary_key FROM user_snapshot_logs;

MASTER                         LOG_TABLE                      ROW PRI                                                                                                                                   
------------------------------ ------------------------------ --- ---                                                                                                                                   
EMP                            MLOG$_EMP                      NO  YES 
SCOTT @ oracle >desc RUPD$_EMP
 Name                  Null?         Type
 --------------------  --------- -------------------------------
 EMPNO                            NUMBER(4)
 DMLTYPE$$                        VARCHAR2(1)
 SNAPID                           NUMBER(38)
 CHANGE_VECTOR$$                  RAW(255)

SCOTT @ oracle >
SCOTT @ oracle >
SCOTT @ oracle >desc MLOG$_EMP
 Name                   Null?      Type
 --------------------- --------  --------------------------------
 EMPNO                           NUMBER(4)
 SNAPTIME$$                      DATE
 DMLTYPE$$                       VARCHAR2(1)
 OLD_NEW$$                       VARCHAR2(1)
 CHANGE_VECTOR$$                 RAW(255)

SCOTT @ oracle >select count(1) from RUPD$_EMP;

 COUNT(1)                                                                                                                                                                                              
----------                                                                                                                                                                                              
 0                                                                                                                                                                                              

Elapsed: 00:00:00.03
SCOTT @ oracle >select count(1) from MLOG$_EMP;

 COUNT(1)                                                                                                                                                                                              
----------                                                                                                                                                                                              
 0
ANAND SESSION:-
ANAND @ oracle >create materialized view emp_mv
 2  tablespace andy
 3  build immediate
 4  refresh complete
 5  start with sysdate next sysdate +10/1440
 6  as select * from scott.emp;

Materialized view created.
ANAND @ oracle >select * from emp_mv;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7935 JACK       MANAGER         7839 02-JAN-85       2500                    20
      7936 JILL       MANAGER         7839 01-JAN-84       2500                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10                                                                                               

16 rows selected.

ANAND @ oracle >SELECT name, table_name, updatable, refresh_method FROM user_snapshots;

NAME                           TABLE_NAME                     UPD REFRESH_MET
------------------------------ ------------------------------ --- -----------
EMP_MV                         EMP_MV                         NO  PRIMARY KEY                                                                                                       

ANAND @ oracle >SELECT name, table_name,refresh_method FROM user_snapshots;

NAME                           TABLE_NAME                     REFRESH_MET
------------------------------ ------------------------------ -----------
EMP_MV                         EMP_MV                         PRIMARY KEY                                                                                                           

ANAND @ oracle >SELECT name, type, next, start_with, refresh_group FROM user_snapshots;

NAME                           TYPE     NEXT                           START_WIT REFRESH_GROUP
------------------------------ -------- ------------------------------ --------- -------------
EMP_MV                         COMPLETE sysdate +10/1440               11-DEC-09             1

ANAND @ oracle >SELECT name, query, status FROM user_snapshots;

NAME                           QUERY                                              STATUS
------------------------------ -------------------------------------------------- -------
EMP_MV                         SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME" VALID
                               ,"EMP"."JOB" "JOB","EMP"."MGR"
ANAND @ oracle >@mview_info
Enter value for mview_name: emp_mv
old   4: WHERE b.name=UPPER('&mview_name') and a.rname=b.name ORDER BY TO_CHAR(last_refresh,'dd-mm-yyyy hh24:mi')
new   4: WHERE b.name=UPPER('emp_mv') and a.rname=b.name ORDER BY TO_CHAR(last_refresh,'dd-mm-yyyy hh24:mi')

NAME                           LASTREFRESH                              NEXTREFRESH          JOB B INTERVAL
------------------------------ ---------------------------------------- ---------------- ------- - ---------------
EMP_MV                         11-12-2009 01:34                         11-12-2009 01:44      21 N sysdate +10/1440

ANAND @ oracle >@segment_info
Enter value for segment_name: emp_mv
old   1: select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,(BYTES/1024/1024)MB from dba_segments where SEGMENT_NAME=UPPER('&segment_name')
new   1: select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,(BYTES/1024/1024)MB from dba_segments where SEGMENT_NAME=UPPER('emp_mv')

OWNER      SEGMENT_NAME              SEGMENT_TYPE       TABLESPACE_NAME                        MB
---------- ------------------------- ------------------ ------------------------------ ----------
ANAND      EMP_MV                    TABLE              ANDY                                .0625 

ANAND @ oracle >@index
Enter value for owner: anand
Enter value for table_name: emp_mv

TABLE_OWNER          TABLE_NAME                     INDEX_OWNER          INDEX_NAME                     TABLESPACE_NAME        NUM_ROWS STATUS   INDEX_TYPE
-------------------- ------------------------------ -------------------- ------------------------------ -------------------- ---------- -------- ------------
ANAND                EMP_MV                         ANAND                PK_EMP                         ANDY                         16 VALID    NORMAL

ANAND @ oracle >select count(1) from emp_mv;

  COUNT(1)
----------
        16                                                                                                                                                                                              

ANAND @ oracle >alter materialized view emp_mv move tablespace mv_test;

Materialized view altered.

ANAND @ oracle >@index
Enter value for owner: anand
Enter value for table_name: emp_mv

TABLE_OWNER          TABLE_NAME                     INDEX_OWNER          INDEX_NAME                     TABLESPACE_NAME        NUM_ROWS STATUS   INDEX_TYPE
-------------------- ------------------------------ -------------------- ------------------------------ -------------------- ---------- -------- ------------
ANAND                EMP_MV                         ANAND                PK_EMP                         ANDY                         16 UNUSABLE NORMAL                                                 

ANAND @ oracle >alter index  PK_EMP rebuild tablespace MV_TEST;

Index altered.

ANAND @ oracle >@index
Enter value for owner: anand
Enter value for table_name: emp_mv

TABLE_OWNER          TABLE_NAME                     INDEX_OWNER          INDEX_NAME                     TABLESPACE_NAME        NUM_ROWS STATUS   INDEX_TYPE
-------------------- ------------------------------ -------------------- ------------------------------ -------------------- ---------- -------- ------------
ANAND                EMP_MV                         ANAND                PK_EMP                          MV_TEST                     16 VALID    NORMAL                                                 

ANAND @ oracle >select count(1) from emp_mv;

  COUNT(1)
----------
        16
ANAND @ oracle >@segment_info
Enter value for segment_name: emp_mv

OWNER      SEGMENT_NAME              SEGMENT_TYPE       TABLESPACE_NAME              MB
---------- ------------------------- ------------------ -------------------- ----------
ANAND      EMP_MV                    TABLE              MV_TEST                   .0625                                                                                                                 

ANAND @ oracle >@mview_info
Enter value for mview_name: emp_mv

NAME                           LASTREFRESH                              NEXTREFRESH          JOB B INTERVAL
------------------------------ ---------------------------------------- ---------------- ------- - ---------------
EMP_MV                         11-12-2009 01:34                         11-12-2009 01:44      21 N sysdate +10/1440
SCOTT SESSION:-
SCOTT @ oracle >delete from emp where empno=7935;

1 row deleted.

SCOTT @ oracle >delete from emp where empno=7936;

1 row deleted.

SCOTT @ oracle >commit;

Commit complete.

SCOTT @ oracle >select count(*) from emp;

  COUNT(*)
----------
        14                                                                                                                                                                                              

SCOTT @ oracle >select count(1) from RUPD$_EMP;

  COUNT(1)
----------
         0                                                                                                                                                                                              

SCOTT @ oracle >select count(1) from MLOG$_EMP;

  COUNT(1)
----------
         2                                                                                                                                                                                              

SCOTT @ oracle >alter materialized view log on emp move tablespace mv_test;

Materialized view log altered.

SCOTT @ oracle >SELECT master, log_table, rowids, primary_key FROM user_snapshot_logs;

MASTER                         LOG_TABLE                      ROW PRI
------------------------------ ------------------------------ --- ---
EMP                            MLOG$_EMP                      NO  YES 

SCOTT @ oracle >select count(1) from MLOG$_EMP;

  COUNT(1)
----------
         2
ANAND SESSION:-
ANAND @ oracle >@mview_info
Enter value for mview_name: emp_mv

NAME                           LASTREFRESH                              NEXTREFRESH          JOB B INTERVAL                                                                                            
------------------------------ ---------------------------------------- ---------------- ------- - ---------------                                                                                     
EMP_MV                         11-12-2009 01:34                         11-12-2009 01:44      21 N sysdate +10/1440                                                                                     

ANAND @ oracle >@mview_info
Enter value for mview_name: emp_mv

NAME                           LASTREFRESH                              NEXTREFRESH          JOB B INTERVAL                                                                                            
------------------------------ ---------------------------------------- ---------------- ------- - ---------------                                                                                     
EMP_MV                         11-12-2009 01:44                         11-12-2009 01:54      21 N sysdate +10/1440                                                                                      

ANAND @ oracle >select count(1) from emp_mv;

  COUNT(1) 
----------                                                                                                                                                                                             
        14

ANAND @ oracle >@mview_info
Enter value for mview_name: emp_mv

NAME                           LASTREFRESH                              NEXTREFRESH          JOB B INTERVAL
------------------------------ ---------------------------------------- ---------------- ------- - ---------------
EMP_MV                         11-12-2009 01:44                         11-12-2009 01:54      21 N sysdate +10/1440
SCOTT SESSION:-
SCOTT @ oracle >select count(1) from MLOG$_EMP;

  COUNT(1)
----------
         0

SCOTT @ oracle >select count(1) from RUPD$_EMP;

  COUNT(1)
----------
         0
SCOTT @ oracle >insert into emp values (7377,'JACK','VP','7839','26-FEB-86',4500,'',10);

1 row created.

SCOTT @ oracle >insert into emp values (7344,'JILL','AVP','7698','02-FEB-84',2000,'',20);

1 row created.

SCOTT @ oracle >commit;

Commit complete.
SCOTT @ oracle >select count(1) from MLOG$_EMP;

  COUNT(1)
----------
         2                                                               

Elapsed: 00:00:00.03
SCOTT @ oracle >select count(1) from RUPD$_EMP;

  COUNT(1)
----------
         0
ANAND SESSION:-
ANAND @ oracle >@mview_info
Enter value for mview_name: emp_mv

NAME                           LASTREFRESH                              NEXTREFRESH          JOB B INTERVAL
------------------------------ ---------------------------------------- ---------------- ------- - ---------------
EMP_MV                         11-12-2009 01:44                         11-12-2009 01:54      21 N sysdate +10/1440                                                                                     

ANAND @ oracle >@mview_info
Enter value for mview_name: emp_mv

NAME                           LASTREFRESH                              NEXTREFRESH          JOB B INTERVAL
------------------------------ ---------------------------------------- ---------------- ------- - ---------------
EMP_MV                         11-12-2009 01:54                         11-12-2009 02:04      21 N sysdate +10/1440                                                                                      

ANAND @ oracle >select count(1) from emp_mv;

  COUNT(1)
----------
     16

The “Alter Materialized View <name> Move Tablespace <name>” runs successfully on 9i and above versions.For 8i use “Alter Snapshot <name> Move Tablespace <name>” and do not forget the rebuilds the index.

Some other useful links:-

http://download.oracle.com/docs/cd/B10500_01/server.920/a96567/repmview.htm

http://diznix.com/2009/12/07/broken-materialized-views-and-ora-12034/

http://hemantoracledba.blogspot.com/2009/03/materialized-views-and-tables.html

http://surachartopun.com/2008/06/rupd-tables.html


ANAND @ oracle >select * from emp_mv;EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7935 JACK       MANAGER         7839 02-JAN-85       2500                    20
7936 JILL       MANAGER         7839 01-JAN-84       2500                    20
7369 SMITH      CLERK           7902 17-DEC-80        800                    20
7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
7566 JONES      MANAGER         7839 02-APR-81       2975                    20
7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
7839 KING       PRESIDENT            17-NOV-81       5000                    10
7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
7900 JAMES      CLERK           7698 03-DEC-81        950                    30
7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
7934 MILLER     CLERK           7782 23-JAN-82       1300                    1016 rows selected.

Elapsed: 00:00:00.14
ANAND @ oracle >SELECT name, table_name, updatable, refresh_method
2  FROM user_snapshots;

NAME                           TABLE_NAME                     UPD REFRESH_MET
—————————— —————————— — ———–
EMP_MV                         EMP_MV                         NO  PRIMARY KEY

Elapsed: 00:00:00.07
ANAND @ oracle >
ANAND @ oracle >
ANAND @ oracle >SELECT name, table_name,
2  refresh_method
3  FROM user_snapshots;

NAME                           TABLE_NAME                     REFRESH_MET
—————————— —————————— ———–
EMP_MV                         EMP_MV                         PRIMARY KEY

Elapsed: 00:00:00.07

About these ads
    • Mausumi Moharar
    • February 8th, 2012

    Thank You!

  1. December 23rd, 2009

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 463 other followers

%d bloggers like this: