ORA-00959 tablespace does not exist by revoking user quota on a dropped tablespace

It is a bug? Tablespace has been dropped, but this tablespace information still exists in database data dictionary.

User has quotas on two tablespaces. One tablespace needs to be dropped. After dropping this tablespace, the quota on this tablespace can not be manually revoked from this user.

To fix this, we have to create and add a dummy tablespace with same name back again, then revoke user quota on this tablespace, drop the tablespace finally.

Here are the steps to show this issue plus how to fix it most importantly.

1) User has quotas on two tablespaces.

SQL>select tablespace_name,username,max_bytes,dropped 
from dba_ts_quotas
where username='TESTUSER';
TABLESPACE_NAME USERNAME MAX_BYTES DRO ---------------- ---------- ---------- --- USERS_OLD TESTUSER -1 NO USERS TESTUSER -1 NO

2) Drop tablespace “USERS_OLD”.

SQL> drop tablespace USERS_OLD including contents and datafiles;
Tablespace dropped.

3) Quota on tablespace still exists.

SQL> select tablespace_name,username,max_bytes,dropped 
from dba_ts_quotas
where username='TESTUSER';
TABLESPACE_NAME USERNAME MAX_BYTES DRO ---------------- --------- ---------- --- USERS_OLD TESTUSER -1 YES USERS TESTUSER -1 NO

4) Revoke quota on tablespace “USERS_OLD”

SQL> alter user TESTUSER quota 0 on USERS_OLD;
alter user TESTUSER quota 0 on USERS_OLD
*
ERROR at line 1:
ORA-00959: tablespace 'USERS_OLD' does not exist

5) Create a dummy tablespace with  same name.

SQL> create tablespace USERS_OLD datafile '+dg1' size 10m;
Tablespace created.

6) Revoke quota on tablespace USERS_OLD.

SQL> alter user TESTUSER quota 0 on USERS_OLD;
User altered.

7) To confirm quota on tablespace “USERS_OLD” revoked.

SQL> select tablespace_name,username,max_bytes,dropped 
from dba_ts_quotas
where username='TESTUSER'; TABLESPACE_NAME USERNAME MAX_BYTES DROPPED ---------------- ---------- ---------- ------ USERS TESTUSER -1 NO

8) Drop the dummy tablespace. 

SQL> drop tablespace USERS_OLD including contents and datafiles;
Tablespace dropped.

One thought on “ORA-00959 tablespace does not exist by revoking user quota on a dropped tablespace”

Leave a Reply to Howard Hackworth Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.