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 and how to fix it.

1) User has quotas on two tablespaces:

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

2) Drop tablespace “ESB_TRACKER_OLD”:

SQL> drop tablespace ESB_TRACKER_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='ESB_TRACKER';
TABLESPACE_NAME USERNAME MAX_BYTES DRO
------------------------------ ------------------------------ ---------- ---
ESB_TRACKER_OLD ESB_TRACKER -1 YES
ESB_TRACKER ESB_TRACKER -1 NO

4) Revoke quota on tablespace “ESB_TRACKER_OLD”

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

5) Create a dummy tablespace with  same name

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

6) Revoke quota on tablespace ESB_TRACKER_OLD

SQL> alter user ESB_TRACKER quota 0 on ESB_TRACKER_OLD;
User altered.

7) To confirm quota on tablespace “ESB_TRACKER_OLD” revoked

SQL> select tablespace_name,username,max_bytes,dropped from dba_ts_quotas where username='ESB_TRACKER';

TABLESPACE_NAME USERNAME MAX_BYTES DROPPED
------------------------------ ------------------------------

ESB_TRACKER ESB_TRACKER -1 NO

8) Drop tablespace in last step

SQL> drop tablespace ESB_TRACKER_OLD including contents and datafiles;
Tablespace dropped.
Advertisements
This entry was posted in User Privileges and Roles and tagged , , , , . Bookmark the permalink.

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

Leave a 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s