Thinking Out Loud

June 16, 2010

Remove User Quota On a Dropped Tablespace

Filed under: Uncategorized — mdinh @ 4:48 pm

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;
/

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: