Home » SQL & PL/SQL » SQL & PL/SQL » How to clean entire Oracle db/attain empty db
How to clean entire Oracle db/attain empty db [message #257267] Tue, 07 August 2007 22:54 Go to next message
nagkumar
Messages: 6
Registered: August 2007
Junior Member
Hi,

How do I clean all the db objects which include tables, triggers, functions, procedures, views and any other db objects with sql.

I am hoping that drop db would drop entire data along with dabase. However I only would like see empty database .

Any SQL which helps to do this on Oracle 9i+ would help.

Regards,
Raja Nagendra Kumar,
C.T.O
www.tejasoft.com

Re: How to clean entire Oracle db/attain empty db [message #257268 is a reply to message #257267] Tue, 07 August 2007 23:05 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>However I only would like see empty database .
DROP USER SYS CASCADE;

I suspect you don't realize what you'd have if you got what you asked for.
Since "Oracle" resides inside the database, if there were ZERO objects inside the datafiles; you'd have the functional equivalent of an empty 55 gallon drum.
A 100% empty database would NOT be an RDBMS.
Be careful what you wish for.

[Updated on: Tue, 07 August 2007 23:06] by Moderator

Report message to a moderator

Re: How to clean entire Oracle db/attain empty db [message #257271 is a reply to message #257268] Tue, 07 August 2007 23:35 Go to previous messageGo to next message
nagkumar
Messages: 6
Registered: August 2007
Junior Member
Thank you for note.

If this is the case, is there way to clean all the user created objects.

Regards,
Nagendra
Re: How to clean entire Oracle db/attain empty db [message #257275 is a reply to message #257267] Tue, 07 August 2007 23:43 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>If this is the case, is there way to clean all the user created objects.
It depends upon who/what you mean/classify as "user".

DROP USER <schema_name> CASCADE;

You can drop any schema you deem expendable.
Re: How to clean entire Oracle db/attain empty db [message #257390 is a reply to message #257275] Wed, 08 August 2007 04:29 Go to previous messageGo to next message
nagkumar
Messages: 6
Registered: August 2007
Junior Member
O.K, let me make it more clear..

I shall login to my oracle dabase instance with admin a/c and create a new user 'newuser'. There on I shall provide creation, deletion, modification all the needed rights to this user.

There on I reconnect to the same db with 'newuser' a/c. Now at this login I use any sql's to create, upload <functions, procedure, views or any other operations/objects creation>.

Now that I have done all operations with newuser a/c, is there a way to get back to original empty state using the sql as u said.

Some thing like

Drop USER newuser CASCADE;

by doing this would all the user done operations on the datbased removed too.

Thank you for the quick support

Regards,
Nagendra
Re: How to clean entire Oracle db/attain empty db [message #257392 is a reply to message #257390] Wed, 08 August 2007 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
by doing this would all the user done operations on the datbased removed too.

No, only objects created by him.

Regards
Michel
Re: How to clean entire Oracle db/attain empty db [message #257394 is a reply to message #257390] Wed, 08 August 2007 04:33 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
DROP user CASCADE will remove the entire schema, including its objects, yes. After that you recreate the user and you'll have a brand new empty schema at your disposal.

Edit: Michel has a point. If the user has done DML on another schema, those changes will NOT be rolled back. They will remain.

MHE

[Updated on: Wed, 08 August 2007 04:34]

Report message to a moderator

Re: How to clean entire Oracle db/attain empty db [message #257461 is a reply to message #257390] Wed, 08 August 2007 07:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
nagkumar wrote on Wed, 08 August 2007 11:29
Now that I have done all operations with newuser a/c, is there a way to get back to original empty state using the sql as u said.

Restore a backup, created before creating the user.
Re: How to clean entire Oracle db/attain empty db [message #257470 is a reply to message #257461] Wed, 08 August 2007 07:40 Go to previous messageGo to next message
nagkumar
Messages: 6
Registered: August 2007
Junior Member
Opps.. I would have loved to see some thing like DROP Database and Create Database support as in MySQL to avoid all such issues. I do understand the security related issues.. however all these could be controled by connected users previlages

I am not sure if Oracle is thinking of some thing like this in future..

Regards,
Nagendra
Re: How to clean entire Oracle db/attain empty db [message #257471 is a reply to message #257267] Wed, 08 August 2007 07:43 Go to previous messageGo to next message
nagkumar
Messages: 6
Registered: August 2007
Junior Member
Exact Steps in MySQL are

drop database `tezzd`

for dropping the database

create database `tezzd`
for creating the datbase

Regards,
Nagendra
Re: How to clean entire Oracle db/attain empty db [message #257490 is a reply to message #257471] Wed, 08 August 2007 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This will never happen in Oracle (although you have create database and drop database).
Oracle is for terabytes and complex applications and is not a toy.

If you don't use/modify others' objects you can do the same with create user/drop user cascade.

Regards
Michel
Re: How to clean entire Oracle db/attain empty db [message #257558 is a reply to message #257490] Wed, 08 August 2007 11:31 Go to previous message
nagkumar
Messages: 6
Registered: August 2007
Junior Member
Thank you Michel and others. I appricate all your timely help.

Regards,
Raja Nagendra Kumar,
C.T.O
www.tejasoft.com
Previous Topic: Database link
Next Topic: Versioning of PL/SQL codes
Goto Forum:
  


Current Time: Fri Dec 02 16:57:14 CST 2016

Total time taken to generate the page: 0.34523 seconds