Re: Possible 10.2.0.2 Bug - Can someone confirm this?

"DA Morgan" <damorgan_at_psoug.org> a écrit dans le message de news: 452FB13E.8030207_at_psoug.org...
| 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

Maybe I missed something but I did note you changed the default tablespace (data_old) after you dropped it. You said:

<quote>
| The default tablespace name changes to data_old which is correct.
<snip>
| 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.

</quote>

Nothing about changing back default tablespace to data_new.

What is strange to me is that Oracle let you drop the default tablespace not that it still tries to create the table in it. Can you post a test case in SQL form abd not verbal form. It will be easier to test and we are then sure to execute the same thing.

Regards
Michel Cadot Received on Fri Oct 13 2006 - 10:46:04 CDT