Re: Drop Database

From: Clemens Hoffmann <choffmann_at_heeg.de>
Date: Tue, 12 Mar 2002 13:13:06 +0100
Message-ID: <a6krci$vla$1@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 - 06:13:06 CST