Re: Remove everything from a schema

On Nov 27, 7:33 am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA..._at_demogracia.com> wrote:
> I want to remove all the contents of a schema (tables, indexes,
> sequences...) in my development box so I can checkout a fresh working
> copy of the web site I'm working on and generate all objects from
> scratch. The server runs version 9.2.0.1.0 in a separate network machine
> and I'd rather not ask the DBA to do it for me every time.
>
> What's the easiest way to do it?
>
> --
> --http://alvaro.es- Álvaro G. Vicario - Burgos, Spain

One method is to drop the user that owns the schema using the CASCADE option. You would then need to recreate the schema owner, which will be done automatically if you import the user schema using the imp utility. In the following, replace schema with the owner of the schema:
DROP USER schema CASCADE;

If you are not using imp to recreate the user, you will need to take additional steps to recreate user. I suggest querying DBA_USERS for the information related to the schema user. Make note of the password hash, default tablespace, etc. The password hash may be used to recreate the user with the same password "CREATE USER ______ IDENTIFIED BY VALUES ____"... Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Nov 27 2008 - 07:24:22 CST