Solved

cannot drop and alter datafile to tablespace.

Posted on 2004-10-06
20
1,960 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
  • 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
 
LVL 34

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 34

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 100 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 34

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now