Tablespace Quota Question

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 12 Oct 2006 13:27:09 -0700
Message-ID: <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_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Oct 12 2006 - 15:27:09 CDT