Cannot drop tablespace in oracle

2
votes

HI,

problem 1

When i try to drop the tablespace using drop tablespace command, it fails and error message is the tablespace not exist.

when i do select * from dba_tablespaces, i can see the tablespace name.

Problem 2 when i try to connect the oracle using aquadata, i get "connection failed:- no more data to read from socket"

please suggest

1
databaseoracle
Please ask one question per post. The SO mechanisms are designed to focus on a single topic. There is no charge for starting a new thread. - APC
Please post the precise error messages, including the ORA numbers or whatever identifier you get. The more information you provide the more likely you are to get a happy resolution. - APC
Could be a uppercase/lowercase issue. If the tablespace name (in DBA_TABLESPACES) is in lower or mized case, you may need to quote it in order to drop it. - Gary Myers
Sorry for adding two questions on same thread. @gary, when i execute query select * from dba_tablespaces, the tablespace name is shown in lower case. But i tried to drop it both the cases, but error thrown is database not found.. error code given is ORA-00959.. For problem 2 i will raise another thread - Abhiram Kulkarni

1 Answers

8
votes

"when i execute query select * from dba_tablespaces, the tablespace name is shown in lower case."

By default Oracle SQL is case insensitive. That is, both of these statements are equivalent:

drop tablespace TS1 including contents
/

drop tablespace ts1 including contents
/

However, when it comes to storing metadata Oracle uses UPPER CASE for object names. This doesn't matter unless when we created the database objects we wrapped their names in double-quotes. At that point Oracle becomes case sensitive.

It appears such is your situation. So you need to issue the command with the tablespace name in double quote:

drop tablespace "ts1" including contents
/