今天遇到一个怪问题,修改了用户的默认表空间后,创建全局的临时表对象会报错:ORA-00959: tablespace 'xxxx' does not exist。查找metalink上的相关帖子,竟然是bug 5988029,在11G中已经修复。
相关的测试脚本如下:
create tablespace test2 datafile '/u01/app/oradata/randy10g/test02.dbf' size 5m;
Tablespace created.
SQL> create user test identified by test
2 default tablespace test2;
User created.
SQL> alter tablespace test2 rename to test3;
Tablespace altered.
SQL> create tablespace test2 datafile '/u01/app/oradata/randy10g/test002.dbf' size 5m;
Tablespace created.
SQL> drop tablespace test3;
Tablespace dropped.
SQL> grant connect, create session to test;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> create global temporary table today_tables
2 on commit preserve rows
3 as
4 select * from user_objects;
select * from user_objects
*
ERROR at line 4:
ORA-00959: tablespace 'TEST3' does not exist