Script to Drop a user's objects.

This script will produce the alter/drop commands that we can use to drop all the user objects from a database.

REM Script to drop all user objects

SET LINES 132 PAGES 59 FEEDBACK OFF ECHO OFF VERIFY OFF

BREAK ON username

TTITLE left _date center 'SQL To Drop A Users Objects' skip 2

PROMPT When prompted, enter the name of the user who''s objects you wish to drop

PROMPT Then cut and paste the commands presented to drop those objects.

SELECT 'alter table '

|| owner

|| '.'

|| table_name

|| ' drop constraint '

|| constraint_name

|| ' cascade;' code

FROM dba_constraints

WHERE (constraint_type = 'R') AND (owner = UPPER ('&&enter_user_name'))

UNION

SELECT DISTINCT 'DROP '

|| object_type

|| ' '

|| owner

|| '.'

|| object_name

|| ';' code

FROM dba_objects

WHERE (object_type NOT IN ('INDEX'))

AND (owner = UPPER ('&&enter_user_name'))

ORDER BY code DESC;

Sample Output

SQL> When prompted, enter the name of the user who’s objects you wish to drop

SQL> Then cut and paste the commands presented to drop those objects.

18-JUL-08 SQL To Drop A Users Objects

CODE

--------------------------------------------------------------------------------------

DROP TABLE SCOTT.UMCATEGORYCONVFACTOR;

DROP TABLE SCOTT.UOMCATEGORYCONVFACTOR;

DROP TABLE SCOTT.UDT_SOURCING;

DROP TABLE SCOTT.WEBSPRELOPTS;

DROP TABLE SCOTT.WEBSKUOPTS;

DROP TABLE SCOTT.WDDDATA;

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!