Q
Problem solve Get help with specific problems with your technologies, process and projects.

Can the Oracle database schema be cleared without dropping?

An Oracle user asks if the Oracle database schema can be cleared without dropping. Read Brian Peasland's answer here.

How do you clear the Oracle database schema (in a single shot) without dropping?
In short, you cannot. There is no ALTER USER DROP SCHEMA command. You can drop the user and thus drop the schema objects. If you want to drop all objects in the schema, you can do a simple operation as follows:

SPOOL drop.sql
SELECT 'DROP '||object_type||' '||object_name||';'
FROM dba_objects WHERE owner='SCOTT';
SPOOL OFF
@drop
@drop

The above quickly creates a script to drop all of the user's objects. It then runs that script. The script is run twice to handle objects with Foreign Key constraints. But the above is not a single-shot as requested.

This was last published in August 2008

Dig Deeper on Oracle database administration

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close