Remove User Quota On a Dropped Tablespace

I have dropped a good number of tablespaces and forgot to remove the user quota before drop.

Once the tablespace has been dropped, the user quota can not be removed.

You will get error – ORA-00959 tablespace ‘%s’ does not exist.

One solution is to recreate the tablespace, remove user quota, and drop tablespace.

This can be very cumbersome.  Luckily, 10gR2 provides option to rename tablespace.

Here’s the SQL I used to do this.


DECLARE
c_ts VARCHAR2 (30) := 'STAGING_DATA';
l_sql VARCHAR2 (1000);
BEGIN
FOR x IN (SELECT username, tablespace_name FROM dba_ts_quotas WHERE dropped = 'YES' ORDER BY 1,2)
LOOP
l_sql := 'ALTER TABLESPACE ' || c_ts || ' RENAME TO ' || x.tablespace_name;
EXECUTE IMMEDIATE l_sql;
DBMS_OUTPUT.put_line (l_sql);
l_sql := 'ALTER USER ' || x.username || ' QUOTA 0 ON ' || x.tablespace_name;
EXECUTE IMMEDIATE l_sql;
DBMS_OUTPUT.put_line (l_sql);
c_ts := x.tablespace_name;
END LOOP;
l_sql := 'ALTER TABLESPACE ' || c_ts || ' RENAME TO STAGING_DATA';
EXECUTE IMMEDIATE l_sql;
DBMS_OUTPUT.put_line (l_sql);
END;
/


Advertisement