Home » SQL & PL/SQL » SQL & PL/SQL » ora-12096 (Oracle 11.2.0.4, Windows 2008 R2)
ora-12096 [message #647042] Mon, 18 January 2016 11:49 Go to next message
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 #647044 is a reply to message #647042] Mon, 18 January 2016 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Yes, before, and you have to complete refresh the mview.

Re: ora-12096 [message #647048 is a reply to message #647044] Mon, 18 January 2016 12:10 Go to previous messageGo to next message
sant_new1
Messages: 46
Registered: June 2014
Member
Thanks. I don't see any materialized view for that table, only materialized view log. Also, the column length is already changed in the table.. Is it still OK to drop and re-create the materialized view log?

Thanks a lot again
Re: ora-12096 [message #647049 is a reply to message #647048] Mon, 18 January 2016 12:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Yes but you have a mview somewhere that needs to be changed otherwise what is the need of the mview log?

Re: ora-12096 [message #647051 is a reply to message #647049] Mon, 18 January 2016 13:07 Go to previous messageGo to next message
sant_new1
Messages: 46
Registered: June 2014
Member
THis is strange.. I don't see mview anywhere in the database for this table. So, is it not possible to have a mview log without mview?

Thanks
Re: ora-12096 [message #647053 is a reply to message #647051] Mon, 18 January 2016 13:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

It may be in another database, this is most often the case.

Re: ora-12096 [message #647059 is a reply to message #647053] Mon, 18 January 2016 14:01 Go to previous messageGo to next message
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 #647061 is a reply to message #647059] Mon, 18 January 2016 14:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

I agree but the mview will be impacted if you increase the size of the source table and not the one in the mview.

Re: ora-12096 [message #647065 is a reply to message #647061] Mon, 18 January 2016 14:11 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
Absolutely. That's the next step OP or someone else on remote side will face.

SY.
Re: ora-12096 [message #647094 is a reply to message #647065] Tue, 19 January 2016 08:51 Go to previous messageGo to next message
sant_new1
Messages: 46
Registered: June 2014
Member
Thank you for your responses.. This is a problem at the customer's database and you are right the column length is not altered in the mlog$ table.
But, they are saying there is no mview for this table anywhere, they ran 'select owner,name,table_name from all_snapshots where owner='USER1' and it didn't return any mviews in their database..

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
Re: ora-12096 [message #647095 is a reply to message #647094] Tue, 19 January 2016 08:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
what clues exist in ALL_DEPENDENCIES?
Re: ora-12096 [message #647097 is a reply to message #647095] Tue, 19 January 2016 09:01 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #647099 is a reply to message #647098] Tue, 19 January 2016 09:20 Go to previous messageGo to next message
sant_new1
Messages: 46
Registered: June 2014
Member
Thank you so much for all the help. I will discuss this with the customer. Is there a SQL query to list all the mview logs in the database?

Thanks
Re: ora-12096 [message #647101 is a reply to message #647094] Tue, 19 January 2016 09:49 Go to previous messageGo to next message
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 #647102 is a reply to message #647101] Tue, 19 January 2016 09:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
the Clue Locker is empty. Sad
Re: ora-12096 [message #647104 is a reply to message #647102] Tue, 19 January 2016 10:22 Go to previous messageGo to next message
sant_new1
Messages: 46
Registered: June 2014
Member
We do have a recent copy of their database in-house but I'm not sure what they added/created in the last week or so. We do not manage their database, only provide application related support.. I ran the DBA_DEPENDENCIES query, it returned procedures dependent on that table but no mviews on both in-house and customer database.. There are a lot of mview logs in this database without using mviews, is there a query to list all the mview logs?

Thank you
Re: ora-12096 [message #647105 is a reply to message #647104] Tue, 19 January 2016 10:27 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
DBA_MVIEW_LOGS
Re: ora-12096 [message #647106 is a reply to message #647105] Tue, 19 January 2016 10:30 Go to previous messageGo to next message
sant_new1
Messages: 46
Registered: June 2014
Member
Thanks
Re: ora-12096 [message #647107 is a reply to message #647106] Tue, 19 January 2016 10:39 Go to previous messageGo to next message
sant_new1
Messages: 46
Registered: June 2014
Member
Customer 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
Re: ora-12096 [message #647108 is a reply to message #647107] Tue, 19 January 2016 11:33 Go to previous messageGo to next message
John Watson
Messages: 8981
Registered: January 2010
Location: Global Village
Senior Member
sant_new1 wrote on Tue, 19 January 2016 16:39
Customer 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 Go to previous messageGo to next message
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.

Re: ora-12096 [message #647110 is a reply to message #647042] Tue, 19 January 2016 13:32 Go to previous message
sant_new1
Messages: 46
Registered: June 2014
Member
Thank you for all the help.
Previous Topic: How do I find a value anywhere in a Oracle schema ?
Next Topic: Use cursor output in the IN CLAUSE
Goto Forum:
  


Current Time: Wed Aug 06 19:00:06 CDT 2025