Perhaps you could explain the circumstances under which option 2 is better
than option 1?
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Clemens Hoffmann" <choffmann_at_heeg.de> wrote in message
news:a6krci$vla$1_at_charly.heeg.de...
>
> "Kram" <myoung_at_optushome.com.au> schrieb im Newsbeitrag
> news:w2bj8.13$an4.2442_at_nsw.nnrp.telstra.net...
> > I am relatively new to Oracle and was wondering how to drop a
> > database/schema as you can in Sybase?
> >
>
> Oracle schema differ from the concept of schema in Sybase.
> In Oracle a schema normally is defined by the database objects
> (tables, views, indexes, ...) a user owns.
>
> There are two options to drop a shema.
>
> 1) you drop the user
>
> SQL> drop user <user_name> cascade
>
> 2) you remove all database object that are owned by a user.
>
> with some few statements you can easily create scripts to drop
> all database objects of a user.
>
> SQL> set pagesize 999
> SQL> spool 'c:\temp\drop-constraints.sql'
> SQL> select 'alter table ' || table_name || ' drop constraint ' ||
> constraint_name || ';' from user_constraints;
> SQL> spool off
> SQL> spool 'c:\temp\drop-objects.sql'
> SQL> select 'drop ' || object_type || ' ' || object_name || ';' from
> user_objects where object_type in ('TABLE', 'VIEW', 'INDEX', 'SEQUENCE',
> 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'TYPE') order by object_type;
> SQL> spool off
>
> SQL> @c:\temp\drop-constraints.sql
> SQL> @c:\temp\drop-objects.sql
>
> There are some statments in the files that cause error messages in
SQLPlus.
> This are the headers ond footer of the queries that created the files.
> This messages can be ignored. if you do not like them then you can edit
> the files and remove the lines.
>
> hope this helps
>
> Clemens Hoffmann
>
>
>
>
Received on Tue Mar 12 2002 - 07:48:49 CST
