Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: ** mv log

Re: ** mv log

From: Vladimir Begun <Vladimir.Begun_at_oracle.com>
Date: Tue, 23 Nov 2004 16:48:23 -0800
Message-ID: <41A3DA57.9040603@oracle.com> 





A Joshi wrote:


 > Recently I incresed the column length in a table that has a materialized

 > view log on it. Update to the column with increased length is not working.

 > I am planning to recreate the mv log.  Before that I would like to extract

 > the mv log definition so I can use the same for re-creating it. I want to

 > be careful and follow the exact steps. Anything else I need to do? I did

 > find the table mlog$_<table_name>. I assume this gets created when the mv

 > log is created.

 >

 > When I delete the mv log then I assume all objects that depend on the mv

 > log have to be complete refreshed. There could be objects in other databases

 > that depend on it and I do not know how to find those. What would happen if

 > I delete the mv log and recreate it and after that one of the dependent

 > objects tries to do a refresh. Will it get an error or will it just changes

 > after the re-creation. Thanks for your help.




Hi there!



A materialized view is a compound object. For example (NB: this example
does not illustrate all possible cases; there is an assumption that you
have dbms_metadata package):



SQL> SELECT * FROM v$version WHERE ROWNUM = 1;



BANNER





Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production



SQL> DROP TABLE tbl;



Table dropped.



SQL> DROP MATERIALIZED VIEW mv_tbl;



Materialized view dropped.



SQL> CREATE TABLE tbl(p VARCHAR2(10));



Table created.



SQL> CREATE MATERIALIZED VIEW LOG ON tbl WITH ROWID (p) INCLUDING NEW VALUES;



Materialized view log created.



SQL> CREATE MATERIALIZED VIEW mv_tbl


   2  BUILD IMMEDIATE



   3  REFRESH FAST ON COMMIT



   4  AS


   5  	SELECT p, COUNT(p) FROM tbl GROUP BY p
   6  /



Materialized view created.



SQL> -- mview is a compound object:


SQL> SELECT table_name data_container FROM user_tables WHERE table_name = 'MV_TBL';



DATA_CONTAINER





MV_TBL


SQL> SELECT query "select statement" FROM user_mviews WHERE mview_name = 'MV_TBL';



select statement




SELECT p, COUNT(p) FROM tbl GROUP BY p



SQL> SELECT index_name, index_type, uniqueness FROM user_indexes WHERE table_name = 'MV_TBL';


INDEX_NAME                     INDEX_TYPE                  UNIQUENES
------------------------------ --------------------------- ---------
I_SNAP$_MV_TBL                 FUNCTION-BASED NORMAL       UNIQUE

SQL> -- the index is function based, let's check it's structure:


SQL> COLUMN column_expression FORMAT A25
SQL> SELECT column_expression, column_position FROM user_ind_expressions WHERE index_name = 'I_SNAP$_MV_TBL' AND table_name = 'MV_TBL';



COLUMN_EXPRESSION         COLUMN_POSITION


------------------------- ---------------
SYS_OP_MAP_NONNULL("P")                 1





SQL> INSERT INTO tbl VALUES('A');



1 row created.



SQL> COMMIT;


Commit complete.



SQL> SELECT * FROM mv_tbl;



P           COUNT(P)



---------- ---------

A                  1





So, as soon as you modify your base table you have to modify all
the objects (mview logs, mviews and fbi indexes) that consume the
data from the modified column. Below you can find an example that
illustrates the problem:



SQL> ALTER TABLE tbl MODIFY p VARCHAR2(20);



Table altered.



SQL> -- Bang! (ORA-12096/ORA-01401)


SQL> INSERT INTO tbl VALUES('ABCDFEFGHIJKL');
INSERT INTO tbl VALUES('ABCDFEFGHIJKL')


             *


ERROR at line 1:


ORA-12096: error in materialized view log on "TBL"
ORA-01401: inserted value too large for column



SQL> -- let's fix it...


SQL> ALTER MATERIALIZED VIEW LOG ON tbl MODIFY p VARCHAR2(20);



Materialized view log altered.



SQL> -- Inserted...


SQL> INSERT INTO tbl VALUES('ABCDFEFGHIJKL');



1 row created.



SQL> -- Bang! (ORA-12008/ORA-01401)


SQL> COMMIT;



COMMIT



*


ERROR at line 1:


ORA-12008: error in materialized view refresh path
ORA-01401: inserted value too large for column



SQL> -- Well... Bang again (ORA-30556)


SQL> ALTER MATERIALIZED VIEW mv_tbl MODIFY p VARCHAR2(20);
ALTER MATERIALIZED VIEW mv_tbl MODIFY p VARCHAR2(20)
*


ERROR at line 1:


ORA-30556: functional index is defined on the column to be modified



SQL> -- Let's drop the index
SQL> SET LONG 2000 ARRAYSIZE 1 LINE 200
SQL> SELECT dbms_metadata.get_ddl('INDEX', 'I_SNAP$_MV_TBL') indx FROM dual;





INDX





   CREATE UNIQUE INDEX "I_SNAP$_MV_TBL" ON "MV_TBL" (SYS_OP_MAP_NONNULL("P"))....


SQL> DROP INDEX i_snap$_mv_tbl;



Index dropped.



SQL> ALTER MATERIALIZED VIEW mv_tbl MODIFY p VARCHAR2(20);



Materialized view altered.



SQL>



SQL> CREATE UNIQUE INDEX "I_SNAP$_MV_TBL" ON "MV_TBL" (SYS_OP_MAP_NONNULL("P"));


Index created.



SQL> ALTER MATERIALIZED VIEW mv_tbl COMPILE;



Materialized view altered.



SQL>



SQL> SELECT * FROM tbl;



P




A



SQL> SELECT * FROM mv_tbl;


P                     COUNT(P)
-------------------- ---------
A                            1





SQL> INSERT INTO tbl VALUES('ABCDFEFGHIJKL');



1 row created.



SQL> COMMIT;


Commit complete.



SQL> SELECT * FROM mv_tbl;


P                     COUNT(P)
-------------------- ---------
A                            1
ABCDFEFGHIJKL                1






For local set of dependencies you can check:

- all_mview_detail_relations
- public_dependency/user_dependencies (all_,dba_)
- dbms_utility.get_dependency
- dbms_mview.get_mv_dependencies

SQL> COLUMN object_name FORMAT A30




SQL> COLUMN object_type FORMAT A30


SQL> SELECT object_id, object_name, object_type FROM user_objects WHERE object_name IN ('TBL', 'MV_TBL');


OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
--------- ------------------------------ ------------------------------
   1188027 MV_TBL                         TABLE
   1188029 MV_TBL                         MATERIALIZED VIEW
   1188025 TBL                            TABLE





SQL> SELECT *



   2    FROM public_dependency


   3   WHERE object_id = (

   4  	      SELECT object_id
   5  		FROM user_objects
   6  	       WHERE object_name = 'MV_TBL'
   7  		 AND object_type = 'MATERIALIZED VIEW'
   8  	    )




   9  /



OBJECT_ID REFERENCED_OBJECT_ID


--------- --------------------
   1188029              1188027
   1188029              1188025





SQL> SELECT mview_name FROM user_mview_detail_relations WHERE detailobj_owner = USER AND detailobj_name = 'TBL';



MVIEW_NAME





MV_TBL


SQL> SET SERVEROUTPUT ON



SQL> EXEC dbms_utility.get_dependency('MATERIALIZED VIEW', USER, 'MV_TBL');
-


DEPENDENCIES ON MV_TBL





*MATERIALIZED VIEW MV_TBL()


PL/SQL procedure successfully completed.



SQL> VAR c VARCHAR2(2000)


SQL> EXEC dbms_mview.get_mv_dependencies('TBL', :c);



PL/SQL procedure successfully completed.



SQL> PRINT c



C




"MV_TBL"


You can write a query that would check which particular mview does
use the modified column(s), it's a bit challenging but doable task,
probably with some assumptions and knowledge about the nature of
mviews' usage in your database(s).



To extract mview log definition you can use:



SQL> SELECT dbms_metadata.get_dependent_ddl('MATERIALIZED_VIEW_LOG', 'TBL', USER) mview_log FROM dual;



MVIEW_LOG





   CREATE MATERIALIZED VIEW LOG ON "TBL"

  PCTFREE 60 PCTUSED 30 INITRANS 1 MAXTRANS 255 LOGGING

   STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645

   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

   TABLESPACE "USERS"



   WITH ROWID ( "P" ) INCLUDING NEW VALUES


HTH,


-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
--
http://www.freelists.org/webpage/oracle-l


Received on Tue Nov 23 2004 - 21:12:25 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US