Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2250
  • Last Modified:

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.

0
bcpprn
Asked:
bcpprn
  • 8
  • 6
  • 4
  • +2
1 Solution
 
catchmeifuwantCommented:
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
 
mikejrobisonCommented:
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
 
sdrozdCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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!
0
 
bcpprnAuthor Commented:
I try to test this statement and use quotes (")

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

it's does not work.
0
 
catchmeifuwantCommented:
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
 
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;

0
 
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  

                                                                               
TABLESPACE                                                                      
---------------                                                                
SYSTEM                                                                          
RBS                                                                            
TEMP                                                                            
BCR                                                                            
BCR_TTD                                                                        
ORDST2                                                                          
LOVE                                                                            
0
 
catchmeifuwantCommented:
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
 
bcpprnAuthor Commented:
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
 
catchmeifuwantCommented:
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
 
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 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
 
catchmeifuwantCommented:
then try creating a tablespace called LOVE using the same datafile and then try dropping it..
0
 
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.
0
 
catchmeifuwantCommented:
hmmmmm.......did it happen while creating the previous tablespaces as well?

Try killing the sessions and re-execute the query
0
 
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.
0
 
catchmeifuwantCommented:
what happened?did it work or not?
0
 
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)

Thank.
0
 
catchmeifuwantCommented:
do not attempt to modify the system objects (fet$ & file$)...your entire database may become corrupt !!!
0
 
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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now