Re: Tablespace Quota Question

"DA Morgan" <damorgan_at_psoug.org> a écrit dans le message de news: 1160684825.292595_at_bubbleator.drizzle.com...
| 10.2.0.2 on XP
|
| SQL> conn uwclass/uwclass
| Connected.
|
| SQL> SELECT tablespace_name
| 2 FROM user_tablespaces;
|
| TABLESPACE_NAME
| ------------------------------
| SYSTEM
| UNDOTBS1
| SYSAUX
| TEMP
| USERS
| EXAMPLE
| DATA_SML
|
| SQL> SELECT tablespace_name, bytes
| 2 FROM user_ts_quotas;
|
| TABLESPACE_NAME BYTES
| ------------------------------ ----------
| COMPRESSED 0
| PART1 0
| SYSAUX 0
| DATA_SML 13107200
| USERS 0
| PART3 0
| DATA_OLD 0
| PART2 0
| SYSTEM 0
|
| 9 rows selected.
|
| SQL> create table xxx (
| 2 testcol char(1))
| 3 tablespace users;
|
| Table created.
|
| SQL>
|
| The tablespaces COMPRESSED, PART1, PART2, PART3, and DATA_OLD
| do not exist as is correctly shown in the first query. They
| were dropped but yet a record is retained in TS$ which shows
| in DBA_TS_QUOTAS even after a restart. Further DBA_TS_QUOTAS
| does not reflect an accurate quota on the USERS tablespace for
| the user.
|
| Thanks.
| --
| Daniel A. Morgan
| University of Washington
| damorgan_at_x.washington.edu
| (replace x with u to respond)
| Puget Sound Oracle Users Group
| www.psoug.org

Yes, this is, imho, a bug in dba_ts_quotas that exists in all versions. In its definition "ts.online$ != 3" is missing. I remember having raised a TAR many and many years ago but this is still there.

But in 10g, they added the column DROPPED to indicate that the tablespace no more exists.

Why they keep these records in sys.tsq$, sys.ts$ and why they don't restrict in dba_ts_quotas as they do in dba_tablespaces, I don't know. Maybe someelse can answer to this question.

Regards
Michel Cadot Received on Thu Oct 12 2006 - 15:55:18 CDT