A quickie

B4 i forget a certain someone screwed me ova a certain thing.....those of u out there know what i speak off....

Oracle bug

Thia should be my 1st official post

I found this bug when i was doing my DBA1 course....

1st i created 3 temporary tablespaces

I assigned all users to use default temporary tablespace 1, temp.

then i assigned user bob to temp tablespace 2, temp2

immediately after that i altered all the users to use default temp tablespace 3,temp3
(theoretically all users will be using temp3 except bob who is assigned to temp2)

now i proceeded to drop temp2

is user bob still using temp2? yes but temp2 is already dropped. The datafile also has been manually deleted and instance has been restarted

This is the result i got from querying the data dictionary

USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS TEMP3
SYSTEM TEMP3
DBSNMP TEMP3
BOB TEMP2
WMSYS TEMP3
OUTLN TEMP3
EMI TEMP3
ALAN TEMP3



SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
TEMP
INDX
TOOLS
USERS
USERDATA
DATA01
INDX01
INDEX01
DATA02
UNDOBS2

TABLESPACE_NAME
------------------------------
TEMP3

12 rows selected.

Anybody want to explain this to me or should it classified as an X-File?