Update statement runs for longtime and throws ORA-01654 table space error

All we need is an easy explanation of the problem, so here it is.

I have a simple update statement which runs for a long time (>15mins) to update approximately around 120K records in oracle19c,but it also fails atlast with ORA-01654 table space error.

Is there way to reduce the execution time of this update statement and how to overcome this table space error.

UPDATE TABLE1 F 
SET F.SYSTEM_ID = NVL(
(select TC.NEW_SYSTEM_ID  from TABLE2 TC where TC.OLD_SYSTEM_ID=F.SYSTEM_ID),F.SYSTEM_ID);

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

То overcome the error "ORA-01654: unable to extend index …" in most cases you have to extend the corresponding tablespace. There are three different ways to do that, depending on the case and your preferences:

  • add a new datafile to the tablespace, example: ALTER TABLESPACE users ADD DATAFILE '/u02/oracle/data/users02.dbf' SIZE 200M;
  • increase the size of existing datafile of the tablespace, example: ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users01.dbf' RESIZE 200M;
  • allow atomatic extension for existing datafile of the tablespace, example: ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users01.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE 200M.

For more information see Oracle 21c Database Documentation, Database Administrator’s Guide, Part II Oracle Database Structure and Storage, 11 Managing Tablespaces, 11.8 Altering and Maintaining Tablespaces, 11.8.1 Increasing the Size of a Tablespace. For specific syntax and options of the mentioned SQL commands see Oracle 21c Database Documentation, SQL Language Reference.

To reduce the execution time of the update you can do some (or all) of the following:

  • If you have any indexes on the column TABLE1.SYSTEM_ID try removing them before the update and re-creating them after the update.
  • Create an index on the TABLE2.OLD_SYSTEM_ID column, if you don’t have one. Judging from the existing update statement, the values in this column are unique. If that’s true then it’s better to set primary key or unique key constraint on the column – it will use an existing index or will create a new one if there is no suitable index.
  • Reformulate the update statement. This should make the difference. Here I see are two alternatives: use updateable inline view or use merge statement.

So, to make an updateable view you must have primary key or unique key constraint on column TABLE2.OLD_SYSTEM_ID (see previous recommendations). The new update statement will look like this:

update
(
  select f.SYSTEM_ID, tc.NEW_SYSTEM_ID
  from TABLE1 f join TABLE2 tc on f.SYSTEM_ID = tc.OLD_SYSTEM_ID
)
set SYSTEM_ID = NEW_SYSTEM_ID

IMO this is the best way to "fix" this statement.

Otherwise, to use merge statement you must have primary key constraint on TABLE1 which does not include SYSTEM_ID column. The merge statement will look like this:

merge into TABLE1 t
using
(
  select f.PKCOL1, tc.NEW_SYSTEM_ID
  from TABLE1 f join TABLE2 tc on f.SYSTEM_ID = tc.OLD_SYSTEM_ID
) s
on (t.PKCOL1 = s.PKCOL1)
when matched then update set t.SYSTEM_ID = s.NEW_SYSTEM_ID

Here PKCOL1 is the hypotethical primary key column in TABLE1.

As a last resort, if you can’t make/use primary key of TABLE1, you can replace the primary key column in the merge statement with rowid. That is not safe if there are other "writers" to the table at the same time – it will work, but some of the rows may not receive the update.

Method 2

Such updates should be done with MERGE:

MERGE INTO TABLE1 F
USING TABLE2 TC
ON (TC.OLD_SYSTEM_ID = F.SYSTEM_ID)
WHEN MATCHED THEN UPDATE F.SYSTEM_ID = TC.NEW_SYSTEM_ID);

This should work faster, but your tablespace error may be unrelated.

Method 3

One option might be to use a procedure.

CREATE OR REPLACE PROCEDURE update_ids
AS

BEGIN

    FOR r IN (
        SELECT new_system_id,
                old_system_id
            FROM table2 ) LOOP

        UPDATE table1
            SET system_id = r.new_system_id
            WHERE system_id = r.old_system_id ;

    END LOOP ;

END ;
/

and then execute the procedure

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply