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)
----------
16SCOTT 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/1440SCOTT 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)
----------
2ANAND 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)
----------
0ANAND 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)
----------
16The “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
———- ———- ——— ———- ——— ———- ———- ———-
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
2 thoughts on “Alter Materialized View Move”
Thank You!