Michel Cadot wrote:
> "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
Actually there are a number of things I identified as anomalous.
And the only solution, even after manually editing user$ was a restart.
Just read Brian's response and agree there too. Something is strange
so I am going to try to duplicate it again and will post a test case
if I can.
--
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 Fri Oct 13 2006 - 12:00:58 CDT
