?
Solved

cannot drop and alter datafile to tablespace.

Posted on 2004-10-06
20
Medium Priority
?
2,107 Views
Last Modified: 2012-08-13
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.

0
Comment
Question by:bcpprn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 4
  • +2
20 Comments
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 12235794
Did you try dba_data_files view?
select * from dba_data_files;

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

select * from dba_tablespaces;
0
 
LVL 4

Expert Comment

by:mikejrobison
ID: 12235852
Try putting quotes around your tablespace name

 "my_TableSpace_Name"  

look at dba_tablespaces and then add quotes around it.

The quotes will make sure it stays case sensitive, if it is.
0
 
LVL 2

Expert Comment

by:sdrozd
ID: 12236990
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.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 12237436
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!
0
 

Author Comment

by:bcpprn
ID: 12246036
I try to test this statement and use quotes (")

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

it's does not work.
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 12246042
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 ?
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 12248203
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;

0
 

Author Comment

by:bcpprn
ID: 12283423
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  

                                                                               
TABLESPACE                                                                      
---------------                                                                
SYSTEM                                                                          
RBS                                                                            
TEMP                                                                            
BCR                                                                            
BCR_TTD                                                                        
ORDST2                                                                          
LOVE                                                                            
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 12283839
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 !!
0
 

Author Comment

by:bcpprn
ID: 12284101
Connected to:                                                                  
Oracle7 Server Release 7.1.6.2.0 - Production Release                          
With the distributed, replication and parallel query options                    
PL/SQL Release 2.1.6.2.0 - 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                                    
                                                                               
                                                                               
SQL>  
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 12284151
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;
0
 

Author Comment

by:bcpprn
ID: 12284943
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 7.1.6.2.0
      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.
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 12284979
then try creating a tablespace called LOVE using the same datafile and then try dropping it..
0
 

Author Comment

by:bcpprn
ID: 12285855
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.
0
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 300 total points
ID: 12285896
hmmmmm.......did it happen while creating the previous tablespaces as well?

Try killing the sessions and re-execute the query
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 12289278
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.
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 12294688
what happened?did it work or not?
0
 

Author Comment

by:bcpprn
ID: 12294721
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)

Thank.
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 12294734
do not attempt to modify the system objects (fet$ & file$)...your entire database may become corrupt !!!
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 12297708
I agree, you should never modify tables owned by SYS like: fet$, file$, etc. unless you are instructed to do so by Oracle support.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question