| Remove all schema objects [message #20225] |
Tue, 07 May 2002 23:28  |
Rajee
Messages: 17
Registered: June 2001
|
Junior Member |
|
|
Hi,
Is it possible to delete all schema objects without dropping the user.
Thanks and Regards
Rajeswari R
|
|
|
|
| Re: Remove all schema objects [message #20228 is a reply to message #20225] |
Wed, 08 May 2002 05:36  |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
rem =========================================================================
rem
rem drop_user_objects.sql
rem
rem Copyright (C) Oriole Software, 1999
rem
rem Downloaded from http://www.oriolecorp.com
rem
rem This script for Oracle database administration is free software; you
rem can redistribute it and/or modify it under the terms of the GNU General
rem Public License as published by the Free Software Foundation; either
rem version 2 of the License, or any later version.
rem
rem This script is distributed in the hope that it will be useful,
rem but WITHOUT ANY WARRANTY; without even the implied warranty of
rem MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
rem GNU General Public License for more details.
rem
rem You should have received a copy of the GNU General Public License
rem along with this program; if not, write to the Free Software
rem Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
rem
rem =========================================================================
rem
rem This script, to be run by a DBA, generates a script to drop
rem all of a user's objects - while retaining the Oracle account with
rem all its privileges. This is especially useful before reimporting
rem data on a per-user basis, for instance to update the contents of
rem a test database.
rem
rem Usage : @drop_user_objects <username>
rem
rem It generates drop_<username>_objects.sql
rem
rem Note that it take cares of dropping only objects which do not
rem depend on other objects (indexes and triggers for instance are
rem dropped with the associated tables, as are package bodies with
rem the associated packages) in order to avoid harmless but irritating
rem error messages. However, no in-depth checking of dependencies is
rem performed.
rem This may lead to a number of misleading error messages :
rem - if you are using clusters (God forbid!) the generated script will
rem try to drop the clustered tables after the cluster is gone.
rem - with Oracle8, 'hidden' tables such as overflow tables for index
rem organized tables will be explicitly dropped after the main
rem table has been dropped.
rem - etc.
rem
rem As a rule, do not worry about '... does not exist' messages
rem
rem Note that, when run, the script which actually drops the objects
rem will generate a spool file.
rem
set pause off
set echo off
set scan on
set verify off
set pagesize 0
set feedback off
set recsep off
set termout off
spool drop_&1._objects.sql
select 'spool drop_&1._objects.log'
from dual
/
select 'drop ' || object_type || ' ' || owner || '.' || object_name
|| decode(object_type,
'CLUSTER', ' including tables cascade constraints;',
'TABLE', ' cascade constraints;',
';')
from dba_objects
where owner = upper('&1')
and object_type in ('CLUSTER', 'TABLE', 'VIEW', 'SEQUENCE', 'SYNONYM',
'FUNCTION',
'PROCEDURE', 'PACKAGE')
/
select 'spool off'
from dual
/
spool off
set feedback on
set pagesize 24
set termout on
prompt Run @drop_&1._objects to drop &1's objects ...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
