Skip to Main Content
  • Questions
  • Tablespace Issue : ORA-01653: unable to extend table X for materialized view log

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Mansi.

Asked: March 06, 2017 - 1:02 pm UTC

Last updated: March 06, 2017 - 1:41 pm UTC

Version: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit

Viewed 10K+ times! This question is

You Asked

Hi Team,

Please suggest on below issue.

I am facing ORA-01653: unable to extend table X(Here X is MLOG$view) by Y in tablespace Z while updating multiple rows in one of the table. Below is the detail scenario.

1.There is a tablespace with below used and free space.

USedMB FREEMB TotalMB
TablespaceA 6200 5800 12000

2. This tablespace has 4 datafile with below freespace

FileID MB
1 8
2 40
3 1700.9375
4 2983


3. We have table X with size 5788MB

Now,when I am updating table X which has associated mlog$view with it,that view is also getting updating. I am updating each and every row from table,there are around 15Cr rows in a table. While updating this I am facing ORA-01653: unable to extend table X.

but if we see,there is free space of 5800 on table space. and if we assume that entire table is getting updated the mlog$ would be equal or less than in size of table which is 5788 MB. I am not able to understand why I am getting this error.




and Chris said...

When you insert/update/delete rows in a table with a MV log on it, Oracle Database writes the changes to the log. But it's not just the column values. There's a whole bunch of other metadata captures:

create table plch_invoices (
  invoice_id int not null primary key,
  customer_id int not null,
  invoice_datetime date not null
);
--
create materialized view log on plch_invoices 
  with rowid, sequence (customer_id, invoice_datetime);

create materialized view plch_invoice_mv
enable query rewrite as
  select count(*)
  from   plch_invoices i;
  
insert into plch_invoices
  select rownum, rownum, sysdate+(rownum/100) from dual
  connect by level <= 10;
  
commit;

select * from mlog$_plch_invoices;

CUSTOMER_ID  INVOICE_DATETIME      M_ROW$$             SEQUENCE$$  SNAPTIME$$            DMLTYPE$$  OLD_NEW$$  CHANGE_VECTOR$$  XID$$                
1            06-MAR-2017 05:41:55  AAAfmdAAdAAAARXAAA  11,001      01-JAN-4000 00:00:00  I          N          FE               281,474,976,718,223  
2            06-MAR-2017 05:56:19  AAAfmdAAdAAAARXAAB  11,002      01-JAN-4000 00:00:00  I          N          FE               281,474,976,718,223  
3            06-MAR-2017 06:10:43  AAAfmdAAdAAAARXAAC  11,003      01-JAN-4000 00:00:00  I          N          FE               281,474,976,718,223  
4            06-MAR-2017 06:25:07  AAAfmdAAdAAAARXAAD  11,004      01-JAN-4000 00:00:00  I          N          FE               281,474,976,718,223  
5            06-MAR-2017 06:39:31  AAAfmdAAdAAAARXAAE  11,005      01-JAN-4000 00:00:00  I          N          FE               281,474,976,718,223  
6            06-MAR-2017 06:53:55  AAAfmdAAdAAAARXAAF  11,006      01-JAN-4000 00:00:00  I          N          FE               281,474,976,718,223  
7            06-MAR-2017 07:08:19  AAAfmdAAdAAAARXAAG  11,007      01-JAN-4000 00:00:00  I          N          FE               281,474,976,718,223  
8            06-MAR-2017 07:22:43  AAAfmdAAdAAAARXAAH  11,008      01-JAN-4000 00:00:00  I          N          FE               281,474,976,718,223  
9            06-MAR-2017 07:37:07  AAAfmdAAdAAAARXAAI  11,009      01-JAN-4000 00:00:00  I          N          FE               281,474,976,718,223  
10           06-MAR-2017 07:51:31  AAAfmdAAdAAAARXAAJ  11,010      01-JAN-4000 00:00:00  I          N          FE               281,474,976,718,223  



If you have 5788Mb free, that's (5788 * 1024 * 1024) 6,069,157,888 bytes. I'm assuming that by 15Cr you mean 150,000,000. If so, that means the max size of each row in the MV log can be:

6,069,157,888 / 150,000,000 ~ 40 bytes


That's not much to store all the above. You're going to be close to that with just the rowid, XID, sequence and snaptime, never mind the table columns! So it's no surprise to me you're getting this error...

If you're updating all the rows in the table, you effectively have to do a complete refresh of the MV after. So do you really need the MV log for this operation? Can you drop it and re-create afterwards?

Or allocate more space to your tablespace? ;)

Is this answer out of date? If it is, please let us know via a Comment