Possible 10.2.0.2 Bug - Can someone confirm this?

Using:
ALTER DATABASE DEFAULT TABLESPACE data_new;

Data new was created with ASSM. I then create a schema and some tables.

Then I renamed the tablespace to data_old.

ALTER TABLESPACE data_new RENAME TO data_old;

The default tablespace name changes to data_old which is correct.

SELECT datats#
FROM user$
WHERE name = 'UWCLASS';

Then I create a new tablespace named data_new without ASSM. And grant quota to my user to use the new tablespace. Then I move the tables from data_old to data_new. Then I drop the data_old tablespace.

DROP TABLESPACE data_old INCLUDING CONTENTS AND DATAFILES;

One would assume all would be well but it is not.

SQL> CREATE TABLE newtab (

   2 newcol NUMBER);
CREATE TABLE newtab (
*
ERROR at line 1:
ORA-00959: tablespace 'DATA_OLD' does not exist

But it is not. Even though the data_old tablespace does not exist Oracle wants to use it. And that is true even though there is no quota.

SQL> SELECT tablespace_name, bytes

   2 FROM user_ts_quotas;

TABLESPACE_NAME                     BYTES
------------------------------ ----------
COMPRESSED                              0
PART1                                   0
SYSAUX                                  0
DATA_NEW                         12058624
USERS                                   0
PART3                                   0
DATA_OLD                                0
PART2                                   0
SYSTEM                                  0
EXAMPLE                                 0

10 rows selected.

SQL> A quick examination of the data dictionary reveals that this artifact remains uncorrected.

SQL> SELECT t.ts#, t.name TSNAME

   2 FROM user$ u, ts$ t
   3 WHERE u.datats# = t.ts#
   4 AND u.name = 'UWCLASS'
   5 /

        TS# TSNAME

---------- ---------------------
          7 DATA_OLD

SQL> The value of datats# in user$ is uncorrected.

Even the following:

SQL> ALTER DATABASE DEFAULT TABLESPACE data_sml;

Database altered.

SQL> SELECT t.ts#, t.name TSNAME

   2 FROM user$ u, ts$ t
   3 WHERE u.datats# = t.ts#
   4 AND u.name = 'UWCLASS'
   5 /

        TS# TSNAME

---------- ------------------------------
          7 DATA_OLD

SQL> Does not correct it.

If anyone can replicate this it would be appreciated. Especially in other versions.

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