Re: Remove everything from a schema

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 27 Nov 2008 17:21:52 +0100
Message-ID: <492ec921$0$25687$426a74cc@news.free.fr>

"Charles Hooper" <hooperc2000_at_yahoo.com> a écrit dans le message de news: d952204e-7625-48c6-b80a-0a19549e6f07_at_w34g2000yqm.googlegroups.com... 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.

---

In this case, you will also need to regrant all privileges. I use the following script to drop all my objects:

set heading off
set pagesize 0
set feedback off
set timing off
set time off
set trimspool on
set trimout on
set linesize 100
set echo off
spool t
select 'drop '||object_type||' '||object_name||

       decode(object_type,'CLUSTER',' including tables cascade constraints',
                          'OPERATOR', ' force',
                          'TABLE',' cascade constraints',
                          'TYPE', ' force',
                          'VIEW',' cascade constraints',
                          '')||';'

from user_objects

where object_type in ('CLUSTER', 'CONTEXT', 'DATABASE LINK', 'DIMENSION',
                      'DIRECTORY', 'FUNCTION', 'INDEX TYPE', 'JAVA',
                      'LIBRARY', 'MATERIALIZED VIEW', 'OPERATOR',
                      'OUTLINE', 'PACKAGE', 'PROCEDURE', 'SEQUENCE',
                      'SYNONYM', 'TABLE', 'TYPE', 'VIEW')

order by object_type, object_name
/
spool off
@t.lst
purge recyclebin;

Regards
Michel Received on Thu Nov 27 2008 - 10:21:52 CST