USER_TS_QUOTAS's new column in Oracle 10gR2 and XE

I just noticed that view USER_TS_QUOTAS, giving the list of tablespace quotas on the granted tablespaces, in version 10GR2 comes with an additional column called "DROPPED", containing a YES/NO flag.

I guess this is meant for our convenience, because these tablespace quotas are rather special beasts as you can see below:
for instance, once you have granted a tablespace quota to a user, through the CREATE or ALTER USER statement, the only way to revoke the quota is to lower it to zero:

ALTER USER xyz QUOTA 0 ON yocoya;

This means that any previously created objects will remain in the tablespace and they cannot grow any more from that moment on.

Of course what stated above doesn't apply to users granted UNLIMITED TABLESPACE privilege, as they can create objects everywhere regardless of quotas.

If no objects were created and your quota has been set to zero, you may see a line like the following in the corresponding tablespace entry (on a 10gR1 server):

TABLESPACE_NAME	BYTES	MAX_BYTES	BLOCKS	MAX_BLOCKS
YOCOYA	0	0	0	0

On a XE database, the same view returns also the column DROPPED, mentioned above:

TABLESPACE_NAME	BYTES	MAX_BYTES	BLOCKS	MAX_BLOCKS	DROPPED
YOCOYA	0	0	0	0	YES

So, tablespace quotas are very persistent, indeed they persist *after* dropping the relevant tablespace, but on 10gR2 you can quickly check whether the relevant tablespace is still present or if it has been dropped and this may be useful information to know, i guess.

Interestingly enough, view DBA_TS_QUOTAS will not display empty quotas like that above regardless of the tablespace existence. If you look at the code behind DBA_TS_QUOTAS, you'll see that the view if filtering out rows with MAX_BLOCKS equal to zero.