This discussion is archived
5 Replies Latest reply: Jun 1, 2009 10:28 PM by 688463 RSS

ORA-00959 tablespace test does not exist

user454189 Newbie
Currently Being Moderated
hi all,
here i hav a question to you,actaually i try to drop the tablespace 'test' before dropping i tried to take the tablespace offline. i gave
sql>alter tablespace test offline;
but it returns
error:ORA-00959 tablespace test does not exist

now i queried "select * from v$tablespace" to check is that tablespace exist or not. there it shows the tablespace exists.
actually there are no users alloted to this tablespace.so i try to create seperate user and try to allot this tablespace for that user but i cud't, again it shows same error.

i hav also tested by quering
SELECT tablespace_name FROM dba_tablespaces WHERE lower(tablespace_name) = 'test';
-------------------------------------------------------------------------------------------------
TABLESPACE_NAME
test

select file_name from dba_data_files where lower(tablespace_name)='test';
---------------------------------------------------------------------------------------------------------------
FILE_NAME
D:\ORACLE\ORADATA\KESD\TEST.ORA

i dont know watz the wrong i did? can any one help me to solve this issue.
  • 1. Re: ORA-00959 tablespace test does not exist
    352719 Newbie
    Currently Being Moderated
    Because you need to drop the tablespace just used the following command:

    drop tablespace test including contents and datafiles;

    Michael
  • 2. Re: ORA-00959 tablespace test does not exist
    user454189 Newbie
    Currently Being Moderated
    hi,
    i hav also used this command but still its not dropped returning same error
  • 3. Re: ORA-00959 tablespace test does not exist
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    Hi,
    select file_name from dba_data_files where lower(tablespace_name)='test';
    Maybe your tablespace name was create explicitly in lower case. In this case, you need to use " when you invoke this tablespace :
    SQL> create tablespace "test" datafile '/data/ora/H89UCBAC/u01/test.dbf' size 10M;

    Tablespace created.

    SQL> select file_name from dba_data_files where lower(tablespace_name)='test';

    FILE_NAME
    --------------------------------------------------------------------------------
    /data/ora/H89UCBAC/u01/test.dbf

    SQL> alter tablespace test offline;
    alter tablespace test offline
    *
    ERROR at line 1:
    ORA-00959: tablespace 'TEST' does not exist


    SQL> c/test/TEST
      1* alter tablespace TEST offline
    SQL> /
    alter tablespace TEST offline
    *
    ERROR at line 1:
    ORA-00959: tablespace 'TEST' does not exist


    SQL> c/TEST/"test"
      1* alter tablespace "test" offline
    SQL> /

    Tablespace altered.

    SQL> drop tablespace "test" including contents and datafiles;

    Tablespace dropped.

    SQL>
    Nicolas.
  • 4. Re: ORA-00959 tablespace test does not exist
    454585 Newbie
    Currently Being Moderated
    Dear user454189
    The complete answer to your problem is described in the thread "ORA-00959 tablespace test does not exist" you [user454189] initiated on 26-Mar-2006 22:54.

    I do not think there is a need to create duplicate discussion thread but OK. Given the fact, that you created the tablespace as "test" (i.e. in lowercase letters, as shown by the query result) you always have to reference the tablespace name explicitly as "test" (i.e. with double-quotes). I believe that is "watz wrong".

    If you created the tablespace in lower/mixed case ("Test", "test", "TeST") using double-quotes then you can expect troubles. You have to reference such a tablespace explicitely in the same way. Best practice - never create any objects in lower/mixed case by using double-quotes.

    1)
    CREATE TABLESPACE "TeST"
    DATAFILE '/oracle/..../TeST.dbf'
    SIZE 10M

    2)
    SELECT tablespace_name FROM dba_tablespaces WHERE LOWER(tablespace_name) = 'test'
    --
    TeST

    3)
    CREATE TABLE xxx (id INTEGER) TABLESPACE "TeST"
    -- OK

    CREATE TABLE yyy (id INTEGER) TABLESPACE test
    -- ORA-00959: tablespace 'TEST' does not exist

    Regards,
    Pavel
  • 5. Re: ORA-00959 tablespace test does not exist
    688463 Newbie
    Currently Being Moderated
    hi,

    i was facing the same problem of tablespace doesn't exist. you might have more than one databases and they have different database names. when you will connect as sys as sysdba, you have to include the database name (@dbname). this would might be helpful for you.