Home » SQL & PL/SQL » SQL & PL/SQL » Deleting database
icon9.gif  Deleting database [message #194433] Fri, 22 September 2006 04:00 Go to next message
kwutzke
Messages: 2
Registered: September 2006
Location: Germany
Junior Member
Hello!

1. How do I completely drop a database in Oracle (10g R2)? I must get rid of everything associated with the DB, tables, data etc. It is just a (failed) test DB.

When using SQLPlus, I could use "drop database;" command, however, the console says it must be mounted exclusively, restricted and must be closed. How do I achieve this?

The Oracle docs for this simple stuff is not good/incomplete, or simply too big, I can't find the info I need.

Are there any additional steps I have to do?

2. In SQLPLus, which database is used when logging on as SYSDBA or another user? The one specified by the ORACLE_SID env var?

Is this env var simply the default DB?

3. How do I change the DB? Is there any command to show the databases running right now ("list databases")?

4. How do I show all tables/table names in a DB?

5. Is there any (web based) Oracle tool to delete and recreate a database? Which one is it? The enterprise manager is huge, I couldn't find anything there...

TIA
Karsten

PS: Oracle must create less complex tools AND better (easier to understand) documentation. If I wasn't forced to use Oracle, I would already be gone......
Re: Deleting database [message #194438 is a reply to message #194433] Fri, 22 September 2006 04:23 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that you are new in Oracle world. I suspect that you are, actually, talking about dropping a USER, not the whole database. If that is correct, log in as SYS and issue

SYS> DROP USER user_name CASCADE;

That should do it. If not, or if I was wrong about it, come back and we'll see what next.
Re: Deleting database [message #194445 is a reply to message #194438] Fri, 22 September 2006 04:50 Go to previous messageGo to next message
kwutzke
Messages: 2
Registered: September 2006
Location: Germany
Junior Member
I'm really new to Oracle. (Isn't this the newbie group?)

I'm not up to deleting a user. In fact, the (default) user that exists for the old/current database can "reacclaim" the rights to the new database if you ask me...

I want to delete the whole database and all traces of it, I need to recreate a blank one for a software that created some tables and entered some data into the old database of which I have no idea what it is/they are.

So the DROP DATABASE; command basically applies.

Karsten
Re: Deleting database [message #194457 is a reply to message #194445] Fri, 22 September 2006 05:26 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
did u even try to search for it?search in this forum.its available with the command itself
Re: Deleting database [message #194466 is a reply to message #194445] Fri, 22 September 2006 05:39 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I apologize; saying that Oracle is new to you doesn't mean anything bad; we were all newbies once. Perhaps I should have said that people used to other databases, different from Oracle, sometimes mix-up terms user and database.

I still think that you should drop a user, not the whole database. Why would you want to do that? If your database is healthy, up and running, simply drop that user (which "created some tables and entered some data into" it) and create another one for the same purpose.

To identify it (if you aren't sure which one is it), connect as SYS and issue

SYS> SELECT * FROM all_users;

If you can't connect to it (because user's password is unknown to you), change it using

SYS> ALTER USER user_name IDENTIFIED BY new_password;

Now you should be able to connect to it and see all objects and data. Once you correctly identify that user, drop it.

I admit, I might still be wrong about the whole thing, but my impression is that it is USER to be dropped here, not the database.
Re: Deleting database [message #194512 is a reply to message #194466] Fri, 22 September 2006 08:13 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Or if you really want to get rid of your database, you can shutdown abort it and then remove all dbf files, log files and controlfiles.

First, find these files by doing the following query as a user who has DBA rights.

select * from dba_db_files;
select * from v$logfile;
select * from v$controlfile;

Then login as the oracle owner, change to the ORACLE_HOME directory and do an rm -r to remove all the software, unless you plan on creating a new database, then you'd want to keep the software around.

Although, I think Littlefoot is correct here after seeing this:
Quote:

I'm not up to deleting a user. In fact, the (default) user that exists for the old/current database can "reacclaim" the rights to the new database if you ask me...
Previous Topic: What happens to data in case of improper shutdown if we have not committed it.
Next Topic: Using PL/SQL to display a web page - speed/functionality issues
Goto Forum:
  


Current Time: Fri Dec 02 20:34:04 CST 2016

Total time taken to generate the page: 0.05537 seconds