Re: Drop Database

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> schrieb im Newsbeitrag news:3c8e074b$0$8505$ed9e5944_at_reading.news.pipex.net...
> Perhaps you could explain the circumstances under which option 2 is better
> than option 1?

There is manly only one reason when using either option.

If you have the right to drop a user then you can use option 1.

If you do not have this right or you want to keep the schema owner then you can use option 2. Then you have to login as the schema owner and the owner must have the right to drop the objects (i.e: the owner has RESOURE rights)

> --
> 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 - 08:31:20 CST