Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to delete all objects of a schema or db?

Posted on 2004-09-28
3
Medium Priority
?
9,973 Views
Last Modified: 2009-07-29
With what sql statement(s) can I delete all objects of a db in oracle 9i? (Without deleting and recreating the database)

Regards,
pb
0
Comment
Question by:psychicblue
3 Comments
 
LVL 11

Assisted Solution

by:cjjclifford
cjjclifford earned 75 total points
ID: 12170436
you could recreate all the users/schemas.

or write a PL/SQL procedure to loop through USER_OBJECTS dropping everything (take care if you have Stored Java deployed...) Also, take care to DBMS_JOB.REMOVE() all the jobs for the user...
0
 
LVL 12

Accepted Solution

by:
geotiger earned 75 total points
ID: 12170448
You just need to drop all the users that you created such as

drop user xxx cascade;

0
 
LVL 4

Expert Comment

by:boriskalavsky
ID: 12170839
It is a lot easier to recreate the database. For that you can use DBCA (database create assistant). You can create a template from existing database and use it to rebuild.
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

971 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