tablespace does not exist

[johan]

Running Oracle 9.2.0.1.0
I created 2 tablespaces with corresponding datafiles with this script:
-------------------------------------------
ORACLE_HOME='/opt/oracle/product/9.2/' ; export ORACLE_HOME
ORACLE_SID=TESTDB ; export ORACLE_SID
NLS_LANG=swedish_sweden.we8iso8859p1; export NLS_LANG

$ORACLE_HOME/bin/sqlplus system/manager@TESTDB< CREATE TABLESPACE "system_tbs" LOGGING DATAFILE '/local/db/oracle/oradata/SYSTEM_DATA.dbf' SIZE 600M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEM
ENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
exit;
EOF

$ORACLE_HOME/bin/sqlplus system/manager@TESTDB< CREATE TEMPORARY TABLESPACE "system_temp" TEMPFILE '/local/db/oracle/oradata/SYSTEM_TEMP.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAG
EMENT LOCAL UNIFORM SIZE 1M;
exit;
EOF
-------------------------------------------
The datafiles are created BUT when I try to create my user:
-------------------------------------------
$ORACLE_HOME/bin/sqlplus system/manager@TESTDB< drop user myuser cascade;
create user myuser identified by mypassword
default tablespace system_tbs
quota unlimited on system_tbs;
exit;
EOF
-------------------------------------------

I got an error message telling me that system_tbs does not exist!!

I can't DROP the tablespace either because it does not exist!..

..still it shows up in v$tablespace!!

Why?

[johan]

Seem to be a upper/lower-case problem....

After I quoted the tablespace name it all worked...

[Thomasps]

Yes, case sensitive in 'Quotes'

SQL> create tablespace "Test1" datafile 'D:\ORACLE\ORADATA\TEST10G\test1.dbf' size 20m 
  2  extent management local uniform size 1m segment space management auto;

Tablespace created.

SQL> create user roy identified by roy 
  2  default tablespace test1;
create user roy identified by roy
*
ERROR at line 1:
ORA-00959: tablespace 'TEST1' does not exist


SQL> create user roy1 identified by roy default tablespace "test1" ;
create user roy1 identified by roy default tablespace "test1"
*
ERROR at line 1:
ORA-00959: tablespace 'test1' does not exist



SQL> create user roy identified by roy default tablespace "Test1";

User created.

Why you are using quotes while creating tablespaces? Anyway something new for me too!

[mzahid]

This appears to be related to the way Oracle stores tablespace name in the database.

Is there a reason you have defined tablespace name in quotes?
Remove quotes from the tablespace name and run the script again.