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

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.