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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

"DA Morgan" <damorgan@psoug.org> a écrit dans le message de news: 1160684825.292595@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@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