Oracle Point, Oracle Life.

One of our developers reported that she experienced the following errors while she was running a simple update statement to update an oracle table.

ORA-12096: error in materialized view log on “SATURN”.”SSBSECT”
ORA-01536: space quota exceeded for tablespace ‘DATADDS’

The update statement issued is as simple as followings.

update ssbsect set ssbsect_ssts_code=’A’
where ssbsect_user_id=’WSTUMGR’;

In order to figure out this problem, the following steps were conducted in specific order.

Step 1: check if the user by which that update statement run has Unlimited Tablespace privilege

Answer: yes, it has and also tablespace of target table has considerable free space

Step 2: find any dependent objects over target table.

select NAME from dba_dependencies where REFERENCED_NAME=’SSBSECT’

Answer: yes, there are some dependent objects.

Step 3: check the owner of those dependent objects

select OWNER, object_name, object_type from dba_objects where OBJECT_NAME in (select NAME from dba_dependencies where REFERENCED_NAME=’SSBSECT’);

Step 4: check if above owners have Unlimited Tablespace privilege on target tablespace

Answer: All above owners have Unlimited Tablespace privilege except for PUBLIC

Step 5: Grant Unlimited Tablespace privilege to PUBLIC

Grant unlimited tablespace to public;

The problem is solved. (Once done, I revoked Unlimited Tablespace privilege from Public and re-ran the update statement, it worked very well without error. It’s clear that  the quota has been reset.)

Note: What’s Public? A user, a role, a privilege? Refer to Oracle Metalink Note:234551.1

Further discussion: PUBLIC acts as a default role granted to every user in an Oracle database. Any authenticated database user can exercise privileges that are granted to PUBLIC. Therefore, granting unlimited tablespace privilege to public is kind of risky. (Reversely, revoking Unlimited Tablespace is also risky somehow.) The above granting is conducted based on the following two facts.

1. It’s done on development system.

2. We have tough space resource and are unable to extend target tablespace considerably.

Popularity: 6% [?]