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
———- ———- ——— ———- ——— ———- ———- ———-
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!