Wednesday, December 16, 2009

Drop All User's Objects in a Single Query

Recently, I needed to find a single command to drop all the tables from an Oracle database without explicitly naming each table and without dropping the entire schema.

One possible solution would be to login as SYS and drop the schema itself,
e.g.
------------------------------
sqlplus sys/syspwd

Drop user username cascade
------------------------------

However, I don't want to drop the whole schema, and I just want to drop all the tables, constaints, triggers, sequence, procedures, database links, packages, so I come out with the following sql:
----------------------------------------------------------------------------------
set heading off;
set feedback off;
spool c:\dropobj.sql;
prompt --Drop constraint
select 'alter table '||table_name||' drop constraint '||constraint_name||' ;' from user_constraints where constraint_type='R';
prompt --Drop tables
select 'drop table '||table_name ||';' from user_tables;

prompt --Drop view
select 'drop view ' ||view_name||';' from user_views;

prompt --Drop sequence
select 'drop sequence ' ||sequence_name||';' from user_sequences;

prompt --Drop function
select 'drop function ' ||object_name||';' from user_objects where object_type='FUNCTION';

prompt --Drop procedure
select 'drop procedure '||object_name||';' from user_objects where object_type='PROCEDURE';

prompt --Drop package
prompt --Drop package body
select 'drop package '|| object_name||';' from user_objects where object_type='PACKAGE';

prompt --Drop database link
select 'drop database link '|| object_name||';' from user_objects where object_type='DATABASE LINK';

spool off;
set heading on;
set feedback on;

@@c:\dropobj.sql;
host del c:\dropobj.sql;
----------------------------------------------------------------------------------

I save it as an SQL file in the name of "ClearUserAllObjects.sql", then I login as the user that I want to delete all objects from, then execute the sql file. e.g.

sqlplus targetUser/targetUserPwd
@c:\ClearUserAllObjects.sql

then a clearing objects process begins, wait until it is done.

No comments:

Post a Comment