tablespace does not exist
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: tablespace does not exist

  1. #1
    Join Date
    Oct 2001
    Location
    Stockholm, Sweden
    Posts
    33

    tablespace does not exist

    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?

  2. #2
    Join Date
    Oct 2001
    Location
    Stockholm, Sweden
    Posts
    33
    Seem to be a upper/lower-case problem....

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

  3. #3
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Yes, case sensitive in 'Quotes'
    Code:
    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!
    Last edited by Thomasps; 07-06-2004 at 11:35 AM.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  4. #4
    Join Date
    Jan 2001
    Location
    Vancouver, Canada
    Posts
    87

    tablespace does not exist.

    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.
    Mohammad Zahid
    Software Developer
    Database Management Applications.
    Vancouver, Canada
    E-mail: mzahid@shaw.ca

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width