cannot drop and alter datafile to tablespace.

Hi all
   My DB oracle V7.1
   i have one tablespace cannot drop tablespace or alter add datafile to tablespace, it's show error ora-00959 "tablespace xxx does not exists" error show is same i key tablespace name
mismatch but i check tablespace name in view dba_tablespace
and found.
   i not sure tablespace name it's keep upper case or lower case because i test in upper case and lower case it's not work.

   for create this tablespace with upper or lower , i not sure
because have another create and he is not remember.
   i want to known what is table keep actual tablespace name
, it's mean keep in lower case if have create in lower case.

   how can i do this tablespace.

Thank you in advance.

Who is Participating?
catchmeifuwantConnect With a Mentor Commented:
hmmmmm.......did it happen while creating the previous tablespaces as well?

Try killing the sessions and re-execute the query
Did you try dba_data_files view?
select * from dba_data_files;

See if the tablespace is offline..(status column)

select * from dba_tablespaces;
Try putting quotes around your tablespace name


look at dba_tablespaces and then add quotes around it.

The quotes will make sure it stays case sensitive, if it is.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Try this
select 'drop tablespace "'||tablespace_name||'";' from dba_tablespaces;

the query should return a bunch of drop tablespace statements. Carefully copy the one you want to drop and execute it as sys.
Mark GeerlingsDatabase AdministratorCommented:
Normally tablespace names in Oracle contain uppercase values only.  Since Oracle allows tables and columns to be created with mixed-case or lower-case names (if double quotes are used), it is possible that a tablespace name could also be in mixed-case or lower-case.  If that is true, you will have to use double quotes and match the case exactly.  

The suggestion from sdrozd should give you exactly the syntax you need.  Just be very careful with that and make sure that you copy and execute only the one line that you want to execute!
bcpprnAuthor Commented:
I try to test this statement and use quotes (")

select 'drop tablespace "'||tablespace_name||'";' from dba_tablespaces;

it's does not work.
what happens?what is the error?

why don't you paste the contents of dba_data_files & dba_tablespaces for the tablespace you are trying to delete ?
Mark GeerlingsDatabase AdministratorCommented:
Please run these two queries and post the results here:

select tablespace_name, substr(file_name,1,40) "File_name" from dba_data_files;

select tablespace_name from dba_tablespaces;

bcpprnAuthor Commented:
tablespace have problem is "LOVE" and "ORDST2"

TABLESPACE      File_name                                                      
--------------- ----------------------------------------                        
ORDST2          /BCR2/ORDST2.dbf                                                
ORDST2          /BCR2/ORDST2C.dbf                                              
BCR_TTD         /BCR/BCR_TTD01.dbf                                              
ORDST2          /BCR2/ORDST2B.dbf                                              
ORDST2          /data107/archive/BCR/ORDST2A.dbf                                
BCR             /data107/archive/BCR/BCR03.dbf                                  
BCR             /BCR2/BCR02.dbf                                                
LOVE            /BCR2/love.dbf                                                  
BCR             /BCR/BCR01.dbf                                                  
TEMP            /data107/archive/BCR/temp.dbf                                  
RBS             /BCR2/rbs.dbf                                                  
SYSTEM          /BCR/system.dbf  

execute the following commands and see if the TS are being dropped:

drop tablespace LOVE including contents and datafiles;
drop tablespace ORDST2 including contents and datafiles;

If any errors then paste the error message you get !!
bcpprnAuthor Commented:
Connected to:                                                                  
Oracle7 Server Release - Production Release                          
With the distributed, replication and parallel query options                    
PL/SQL Release - Production                                          
SQL> drop tablespace LOVE including contents and datafiles;                    
drop tablespace LOVE including contents and datafiles                          
ERROR at line 1:                                                                
ORA-02173: invalid option for DROP TABLESPACE                                  
SQL> drop tablespace LOVE including contents;                                  
drop tablespace LOVE including contents                                        
ERROR at line 1:                                                                
ORA-00959: tablespace 'LOVE' does not exist                                    
i do not have an idea why...

Try this from the same session & paste the results

sql>select tablespace_name,status,contents from dba_tablespaces;
sql>select tablespace_name,file_name,file_id from dba_data_files;

sql>alter tablespace LOVE offline;
sql>drop tablespace LOVE;
bcpprnAuthor Commented:
1  select tablespace_name,status                                              
  2* from dba_tablespaces                                                      
SQL> /                                                                          
TABLESPACE_NAME                STATUS                                          
------------------------------ ---------                                        
SYSTEM                         ONLINE                                          
RBS                            ONLINE                                          
TEMP                           ONLINE                                          
BCR                            ONLINE                                          
BCR_TTD                        ONLINE                                          
ORDST2                         ONLINE                                          
LOVE                           ONLINE                                          
7 rows selected.                      
  1* select tablespace_name,substr(file_name,1,30),file_id from dba_data_files  
SQL> /                                                                          
TABLESPACE_NAME                SUBSTR(FILE_NAME,1,30)            FILE_ID        
------------------------------ ------------------------------ ----------        
ORDST2                         /BCR2/ORDST2.dbf                       13        
ORDST2                         /BCR2/ORDST2C.dbf                      12        
BCR_TTD                        /BCR/BCR_TTD01.dbf                     10        
ORDST2                         /BCR2/ORDST2B.dbf                       9        
ORDST2                         /data107/archive/BCR/ORDST2A.d          8        
BCR                            /data107/archive/BCR/BCR03.dbf          7        
BCR                            /BCR2/BCR02.dbf                         6        
LOVE                           /BCR2/love.dbf                          5        
BCR                            /BCR/BCR01.dbf                          4        
TEMP                           /data107/archive/BCR/temp.dbf           3        
RBS                            /BCR2/rbs.dbf                           2        
TABLESPACE_NAME                SUBSTR(FILE_NAME,1,30)            FILE_ID        
------------------------------ ------------------------------ ----------        
SYSTEM                         /BCR/system.dbf                         1        
12 rows selected.                    

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

      my oracle db version
      tablespace BCR_TTD i found , i cannot drop TS if i use upper case, when i use lower case
it's ok can drop TS BCR_TTD. But for TS LOVE and ORDST2 I try for upper case and lower case
it's show same error.
then try creating a tablespace called LOVE using the same datafile and then try dropping it..
bcpprnAuthor Commented:
now i cannot create tablespace , screen  is hang.

sql> create tablespace test datafile '/BCR2/test.dbf' size 10M.

i check in file system found file test.dbf but it's not complete to generate tablespace.
Mark GeerlingsDatabase AdministratorCommented:
I'm guessing that the problem is the combination of upper and lower case characters in the path/file_names that is the problem.  Most of my experience has been with Oracle on Windows where the case of file and/or directory names does not matter.  In Unix, I would suggest that you consistently use just one case (probably lower case) for both directories and file names.

The syntax: "drop tablespace LOVE including contents and datafiles" was not supported in Oracle7, certainly not the "and datafiles" portion.  Maybe the "including contents" clause is valid in Oracle7 - I don't remember that for sure.
what happened?did it work or not?
bcpprnAuthor Commented:
hi all
   thank you everyone for help me.
   now i can drop tablespace and alter tablespace.

   i found , while create tablespace and hang , oracle doing insert to table 'fet$' and 'file$'.
   i try manual insert 1 record into table 'fet$' and delete it's , after i try create tablespace test,
it's ok, try drop tablespace and ok.
   i think , it's maybe some problem on table 'fet$'.(not sure)

do not attempt to modify the system objects (fet$ & file$)...your entire database may become corrupt !!!
Mark GeerlingsDatabase AdministratorCommented:
I agree, you should never modify tables owned by SYS like: fet$, file$, etc. unless you are instructed to do so by Oracle support.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.