Today I came across a strange behavior of tablespace quotas. I’ve had to create new tablespaces in a test and a development database. So I’ve created a tablespace in each of the databases and informed the developer. His answer: “Great, in development I can create tables, but in test I do not have quota on the tablespace” Ok, I did not set a quota on the newly create tablespaces nor does the user has the privilege UNLIMITED TABLESPACE. But what happened? It seems that tablespace quotas remain even if a tablespace has been deleted. Ok, lets have a closer look.
The initial situation
We assume, that somebody has created a tablespace TEST and granted unlimited quota on this to user SCOTT.
CREATE tablespace TEST datafile '/u01/oradata/PT1120/test01PT1120.dbf' SIZE 5M;
ALTER USER SCOTT quota unlimited ON TEST;
SELECT TABLESPACE_NAME,USERNAME,MAX_BYTES,DROPPED
FROM DBA_TS_QUOTAS WHERE username='SCOTT';
TABLESPACE_NAME USERNAME MAX_BYTES DROPPED
-------------------- ---------- ---------- ----------
TEST SCOTT -1 NO
Let’s drop the tablespace TEST and see what happens to the tablespace and the quota.
DROP tablespace TEST including contents AND datafiles;
Tablespace dropped.
SELECT TS#,NAME,ONLINE$ FROM TS$;
TS# NAME ONLINE$
---------- ------------------------------ ----------
0 SYSTEM 1
1 SYSAUX 1
2 UNDOTBS1 1
3 TEMP 1
4 USERS 1
5 EXAMPLE 1
6 TEST 3
7 ROWS selected.
SELECT TABLESPACE_NAME,USERNAME,MAX_BYTES,DROPPED
FROM DBA_TS_QUOTAS WHERE username='SCOTT';
TABLESPACE_NAME USERNAME MAX_BYTES DROPPED
-------------------- ---------- ---------- ----------
TEST SCOTT -1 YES
As you can see the tablespace status in TS$ has chanted from 1 to 3 (dropped). Querying DBA_TS_QUOTAS is now showing in the column DROPPED that the tablespace for which this quota is valid has been dropped. The information in DBA_TS_QUOTA is taken from TS$ and TSQ$.
Somewhen later
Lets create again a tablespace TEST and verify SCOTT’s quota on this new tablespace. In real world this could be after a going live. Or just after quite some time when nobody remember that there use to be a tablespace TEST.
CREATE tablespace test datafile '/u01/oradata/PT1120/test01PT1120.dbf' SIZE 10M;
Tablespace created.
SELECT TABLESPACE_NAME,USERNAME,MAX_BYTES,DROPPED
FROM dba_ts_quotas WHERE username='SCOTT';
TABLESPACE_NAME USERNAME MAX_BYTES DROPPED
-------------------- ---------- ---------- ----------
TEST SCOTT -1 NO
É voila, SCOTT has again access to the newly created tablespace.
Conclusion
From my understanding it looks like an expected behavior. But is it good, bad or ugly? As usual it depends. If a test somewhen becomes a productive database, it its worth to have a look if there are still some quotas hanging around. Just to make sure, that a low privilege user can not suddenly fill up a tablespace. On the other hand it may be handy when the quota remains and we do not have to grant again quota to x users on a newly created tablespace. I tend more towards the safe side. When a quota is not needed or makes no sense, it should be deleted. What are your opinion and experience on this topic?
By the way, the post has started with a movie title as well ended with one. Do you know which two?
