Re: Quota on nonexistent tablespace...

You would need to first create a tablespace with the same name as the one dropped, set the quota of the user to "0" for the tablespace, and then drop the tablespace (again):    

SQL> select username, tablespace_name, bytes from dba_ts_quotas;  

USERNAME TABLESPACE_NAME MAX_BYTES

------------ ------------------ ---------
ODM          ODM                       -1
ODM_MTR      ODM                       -1
OLAPSYS      CWMLITE                   -1
JHUNTER      TEMP_TBS              516096
 

SQL> DROP TABLESPACE temp_tbs INCLUDING CONTENTS AND DATAFILES;  

SQL> select username, tablespace_name, bytes from dba_ts_quotas; -- Still there????  

USERNAME TABLESPACE_NAME MAX_BYTES

------------ ------------------ ---------
ODM          ODM                       -1
ODM_MTR      ODM                       -1
OLAPSYS      CWMLITE                   -1
JHUNTER      TEMP_TBS              516096
 

SQL> alter user jhunter quota 0 on temp_tbs; -- Ouch!!! alter user jhunter quota 0 on temp_tbs
*
ERROR at line 1:
ORA-00959: tablespace 'TEMP_TBS' does not exist  

SQL> create tablespace temp_tbs datafile '/u10/app/oradata/TRUESRC/temp_tbs01.dbf' size 1M;  

SQL> alter user jhunter quota 0 on temp_tbs; -- Yes!!!  

SQL> select username, tablespace_name, max_bytes from dba_ts_quotas; -- It's Miller time  

USERNAME                       TABLESPACE_NAME                 MAX_BYTES
------------------------------ ------------------------------ ----------
ODM                            ODM                                    -1
ODM_MTR                        ODM                                    -1
OLAPSYS                        CWMLITE                                -1
 

SQL> DROP TABLESPACE temp_tbs INCLUDING CONTENTS AND DATAFILES;  

 Hope this helps...  

Kindest Regards,
-- jeff

Jeffrey M. Hunter
Sr. Database Administrator
<mailto:jhunter_at_idevelopment.info> jhunter_at_idevelopment.info
<http://www.idevelopment.info> www.idevelopment.info

"Volker Hetzer" < <mailto:volker.hetzer_at_ieee.org> volker.hetzer_at_ieee.org> wrote in message <news:b7gvh5$p36$1_at_dackel.pdb.sbs.de>
news:b7gvh5$p36$1_at_dackel.pdb.sbs.de...

> Hi!
> I just deleted a tablespace but an user had a quota on it.
> How do I get rid of the quota?
> I hesitate to delete the row from dba_ts_quotas.
> 
> Lots of thanks!
> Volker
> --
> While it is a known fact that programmers
> never make mistakes, it is still a good idea
> to humor the users by checking for errors at
> critical points in your program.
> -Robert D. Schneider, "Optimizing INFORMIX
>  Applications"
> 
>