ora-12096 [message #647042] |
Mon, 18 January 2016 11:49  |
 |
sant_new1
Messages: 46 Registered: June 2014
|
Member |
|
|
Hi friends,
We increasing the column length of one table from number(4) to (6). We get this error Ora-12096: error in materialized view log, ORA-01438: value larger than specified precision allowed for this column. DO we need to drop and re-create the materialized view log for this table after altering the column length?
THanks a lot
|
|
|
|
|
|
|
|
Re: ora-12096 [message #647059 is a reply to message #647053] |
Mon, 18 January 2016 14:01   |
Solomon Yakobson
Messages: 3305 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Error is about materialized view log, not materialized view. MV log resides locally. And error tells me MV icludes column being enlarged:
SQL> create table tbl(
2 id number,
3 val number(3)
4 )
5 /
Table created.
SQL> alter table tbl
2 add constraint tbl_pk
3 primary key(id)
4 /
Table altered.
SQL> create materialized view log on tbl with(val)
2 /
Materialized view log created.
SQL> alter table tbl
2 modify val number(5)
3 /
Table altered.
SQL> insert
2 into tbl
3 values(1,9999)
4 /
into tbl
*
ERROR at line 2:
ORA-12096: error in materialized view log on "SCOTT"."TBL"
ORA-01438: value larger than specified precision allowed for this column
SQL> desc mlog$_tbl
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
VAL NUMBER(3)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
XID$$ NUMBER
SQL>
As you can see, oracle didn't increase column precision in MV log when master table column precision increased.
SY.
|
|
|
|
|
|
|
Re: ora-12096 [message #647097 is a reply to message #647095] |
Tue, 19 January 2016 09:01   |
 |
sant_new1
Messages: 46 Registered: June 2014
|
Member |
|
|
I ran the below SQL, it returned one mview but it is not referencing the table that is causing problem..
select detailobj_owner, detailobj_name
from all_mview_detail_relations where
(owner, mview_name) in
(select referenced_owner, referenced_name
from all_dependencies where name like 'SF%'
)
Also in TOAD, when I click on the mview log for the problematic table and click on 'used by materialized view' the parameter is none. Whereas it shows the correct existing mview for the other table(which was the result of the above query as well).. IS it possible to create mview log without mview? Or is it possible that they have created mview for this table before and dropped it now?
Thanks
[Updated on: Tue, 19 January 2016 09:04] Report message to a moderator
|
|
|
Re: ora-12096 [message #647098 is a reply to message #647097] |
Tue, 19 January 2016 09:14   |
cookiemonster
Messages: 13967 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
It is completely possible to create MV logs without MVs since you need to create the log first and it can be used by multiple MVs.
There is of course no point having one if no MV is using it.
However - are there any other DBs that access the DB with the log via a DB link? If so, as Michel suggested earlier, the MV may well be there and the dependencies views wouldn't show it.
If there's not you can drop it.
|
|
|
|
Re: ora-12096 [message #647101 is a reply to message #647094] |
Tue, 19 January 2016 09:49   |
Solomon Yakobson
Messages: 3305 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
sant_new1 wrote on Tue, 19 January 2016 09:51
But, they are saying there is no mview for this table anywhere, they ran
Since the column in the table is already increased in length, can we go ahead and re-create mview log for this table? Will it impact anything in future?
Please give your suggestions.. Thanks a lot
Materialized view log is always local - resides in same database, same schema as master table. Materialized view can be local or remote and it can be owned by any user. So your customer should issue:
SQL> create table tbl(
2 id number,
3 val number(3)
4 )
5 /
Table created.
SQL> alter table tbl
2 add constraint tbl_pk
3 primary key(id)
4 /
Table altered.
SQL> create materialized view log on tbl with(val)
2 /
Materialized view log created.
SQL> create materialized view tbl_mv
2 as
3 select *
4 from tbl
5 /
Materialized view created.
SQL> select owner,
2 name
3 from dba_dependencies
4 where referenced_owner = 'SCOTT'
5 and referenced_name = 'TBL'
6 /
OWNER NAME
---------- ----------
SCOTT TBL_MV
SQL>
If it returns no rows then there are no local materialized views on the table, however there might be remote materialized views in other databases referencing the table. But the fact your customer is getting "Ora-12096: error in materialized view log, ORA-01438: value larger than specified precision allowed for this column" tells me MV resides on the database where the error is raised. And you looking in ALL_DEPENDENCIES doesn't show complete picture since your ID can have no grants on MV/MV log. Check DBA_DEPENDENCIES, as I showed. But main question is - you are supporting customer and have no clue about the database???
SY.
|
|
|
|
|
|
|
|
Re: ora-12096 [message #647108 is a reply to message #647107] |
Tue, 19 January 2016 11:33   |
John Watson
Messages: 8981 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
sant_new1 wrote on Tue, 19 January 2016 16:39Customer got back that there is Mview replication built and this table is one of them which is replicated. Please let me know if the below steps are good or if I'm missing something..
1. Drop mview log for the table
2. Increase column length
3. Re-create mview log for the table
4. Customer re-creates mview for this table
Thank you all so much If you do this you will invalidate all the dependent materialized views in the databases to which the table is being replicated. Your customer will not thank you for that. To do DDL in a replication environment you need to use the DBMS_REPCAT package. Better start reading the Advanced Replication manuals.
|
|
|
Re: ora-12096 [message #647109 is a reply to message #647107] |
Tue, 19 January 2016 11:39   |
Solomon Yakobson
Messages: 3305 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
1. Drop mview log for the table
2 .Increase column length
3. Re-create mview log for the table
4. Customer refreshes (not re-creates) mview for this table (must be complete refresh)
SY.
|
|
|
|