主题: ORA-12091 Attempting Online Reorganization of Table with Materialized Views
文档 ID: 注释:177408.1 类型: PROBLEM
上次修订日期: 20-JUN-2007 状态: PUBLISHED
Checked for relevance on 20-Jun-2007
Problem Description:
====================
You attempt to re-organize a table ONLINE. The table contains materialized
views. You use the DBMS_REDEFINITION.START_REDEF_TABLE procedure to do this,
and you receive an ORA-12091 "cannot online redefine table %s . %s with
materialized views" error.
For example:
SQL> execute DBMS_REDEFINITION.START_REDEF_TABLE('TEST', -
> 'EMP', -
> 'INT_EMP', -
> 'empno empno, name last_name, deptno deptno');
BEGIN DBMS_REDEFINITION.START_REDEF_TABLE('TEST', ;
*
ERROR at line 1:
ORA-12091: cannot online redefine table "TEST"."EMP" with materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 146
ORA-06512: at line 1
Solution Description:
=====================
Remove the snapshot log and materialized view created during the unsuccessfull
first execution of the DBMS_REDEFINITION.START_REDEF_TABLE procedure:
SQL> select log_table from user_snapshot_logs;
LOG_TABLE
-------------------------
MLOG$_EMP
SQL> drop snapshot log on emp;
Materialized view log dropped.
SQL> select TABLE_NAME , status from user_snapshots;
TABLE_NAME STATUS
------------------------------ -------
INT_EMP INVALID
SQL> select mview_name, compile_state from user_mviews;
MVIEW_NAME COMPILE_STATE
------------------------------ -------------------
INT_EMP ERROR
SQL> drop materialized view int_emp;
Materialized view dropped.
SQL> execute DBMS_REDEFINITION.START_REDEF_TABLE('TEST', -
> 'EMP', -
> 'INT_EMP', -
> 'empno empno, name last_name, deptno deptno');
PL/SQL procedure successfully completed.
Note:
If you do not drop the materialized view, you will receive the following error:
SQL> execute DBMS_REDEFINITION.START_REDEF_TABLE('TEST', -
> 'EMP', -
> 'INT_EMP', -
> 'empno empno, name last_name, deptno deptno');
BEGIN DBMS_REDEFINITION.START_REDEF_TABLE('TEST', ;
*
ERROR at line 1:
ORA-12006: a materialized view with the same user.name already exists
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 146
ORA-06512: at line 1
Explanation:
============
You unsuccessfully launched the DBMS_REDEFINITION.START_REDEF_TABLE procedure
once and the materialized view could be created on the interim table.
SQL> execute DBMS_REDEFINITION.START_REDEF_TABLE('TEST', -
> 'EMP', -
> 'INT_EMP', -
> 'empno empno, name last_name, deptno deptno');
BEGIN DBMS_REDEFINITION.START_REDEF_TABLE('TEST', ;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01688: unable to extend table TEST.INT_EMP partition P10 by 8 in
tablespace USERS
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 146
ORA-06512: at line 1
Since there is a space error on the interim table while loading the data from
the source table into the interim table, the procedure could not successfully
complete.
You extended the tablespace:
SQL> alter database datafile '/filer/9.0.2/djeunot/DB1/users01.dbf'
autoextend on;
Database altered.
However, the materialized view still exists after the unsuccessfull execution:
SQL> select TABLE_NAME , status from user_snapshots;
TABLE_NAME STATUS
------------------------------ -------------
INT_EMP INVALID
SQL> select mview_name, compile_state from user_mviews;
MVIEW_NAME COMPILE_STATE
------------------------------ -------------------
INT_EMP ERROR
References:
===========
Note 177407.1 How to Re-Organize a Table Online
文档 ID: 注释:177408.1 类型: PROBLEM
上次修订日期: 20-JUN-2007 状态: PUBLISHED
Checked for relevance on 20-Jun-2007
Problem Description:
====================
You attempt to re-organize a table ONLINE. The table contains materialized
views. You use the DBMS_REDEFINITION.START_REDEF_TABLE procedure to do this,
and you receive an ORA-12091 "cannot online redefine table %s . %s with
materialized views" error.
For example:
SQL> execute DBMS_REDEFINITION.START_REDEF_TABLE('TEST', -
> 'EMP', -
> 'INT_EMP', -
> 'empno empno, name last_name, deptno deptno');
BEGIN DBMS_REDEFINITION.START_REDEF_TABLE('TEST', ;
*
ERROR at line 1:
ORA-12091: cannot online redefine table "TEST"."EMP" with materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 146
ORA-06512: at line 1
Solution Description:
=====================
Remove the snapshot log and materialized view created during the unsuccessfull
first execution of the DBMS_REDEFINITION.START_REDEF_TABLE procedure:
SQL> select log_table from user_snapshot_logs;
LOG_TABLE
-------------------------
MLOG$_EMP
SQL> drop snapshot log on emp;
Materialized view log dropped.
SQL> select TABLE_NAME , status from user_snapshots;
TABLE_NAME STATUS
------------------------------ -------
INT_EMP INVALID
SQL> select mview_name, compile_state from user_mviews;
MVIEW_NAME COMPILE_STATE
------------------------------ -------------------
INT_EMP ERROR
SQL> drop materialized view int_emp;
Materialized view dropped.
SQL> execute DBMS_REDEFINITION.START_REDEF_TABLE('TEST', -
> 'EMP', -
> 'INT_EMP', -
> 'empno empno, name last_name, deptno deptno');
PL/SQL procedure successfully completed.
Note:
If you do not drop the materialized view, you will receive the following error:
SQL> execute DBMS_REDEFINITION.START_REDEF_TABLE('TEST', -
> 'EMP', -
> 'INT_EMP', -
> 'empno empno, name last_name, deptno deptno');
BEGIN DBMS_REDEFINITION.START_REDEF_TABLE('TEST', ;
*
ERROR at line 1:
ORA-12006: a materialized view with the same user.name already exists
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 146
ORA-06512: at line 1
Explanation:
============
You unsuccessfully launched the DBMS_REDEFINITION.START_REDEF_TABLE procedure
once and the materialized view could be created on the interim table.
SQL> execute DBMS_REDEFINITION.START_REDEF_TABLE('TEST', -
> 'EMP', -
> 'INT_EMP', -
> 'empno empno, name last_name, deptno deptno');
BEGIN DBMS_REDEFINITION.START_REDEF_TABLE('TEST', ;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01688: unable to extend table TEST.INT_EMP partition P10 by 8 in
tablespace USERS
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 146
ORA-06512: at line 1
Since there is a space error on the interim table while loading the data from
the source table into the interim table, the procedure could not successfully
complete.
You extended the tablespace:
SQL> alter database datafile '/filer/9.0.2/djeunot/DB1/users01.dbf'
autoextend on;
Database altered.
However, the materialized view still exists after the unsuccessfull execution:
SQL> select TABLE_NAME , status from user_snapshots;
TABLE_NAME STATUS
------------------------------ -------------
INT_EMP INVALID
SQL> select mview_name, compile_state from user_mviews;
MVIEW_NAME COMPILE_STATE
------------------------------ -------------------
INT_EMP ERROR
References:
===========
Note 177407.1 How to Re-Organize a Table Online
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22198259/viewspace-676420/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22198259/viewspace-676420/