How do i drop all objects of a user but not the user ?

How do i drop all objects of a user but not the user ?

Drop user xxxx cascade ; will delete the user and all objects, but in this case I need the user and schema info to remain
shelbyinfotechAsked:
Who is Participating?
 
seazodiacCommented:
also a reminder, don't forget to close your question if people do provide you a solution.

show some courtesy...
0
 
seazodiacCommented:
try this:

IN sqlplus, log in as sysdba (you can also log in as that user but you will query a different database object view)...

--------------------------------------------------cut here---------------------------------------------------
set termout off
set feedback off
set pagesize 0
set linesize 200
col object_name format a30
col object_type format a30
col owner format a30
set trimspool on
spool dropall.sql
select 'DROP ' || object_type || ' '|| owner||'.'||object_name|| ';' from all_objects where owner='&username';
spool off
@dropall.sql

-----------------------------------------------------------cut here ----------------------------------------------------------------------


you can copy and paste the contents of my script into a file , say "test.sql"

now run this at the sqlplus

SQL>@test.sql --you need to provide the full path to this file.


you will be prompted enter the username the you are all set....
0
 
baonguyen1Commented:
Not sure which info you want to keep but all type of object listed here:

SQL> select distinct object_type from dba_objects;

OBJECT_TYPE
------------------
CLUSTER
CONSUMER GROUP
DATABASE LINK
FUNCTION
INDEX
LIBRARY
LOB
PACKAGE
PACKAGE BODY
PROCEDURE
QUEUE

OBJECT_TYPE
------------------
RESOURCE PLAN
SEQUENCE
SYNONYM
TABLE
TRIGGER
TYPE
VIEW

You if you want to drop, write the scripts like:

select 'drop table '||object_name||' cascade' from dba_objects
where object_type = 'TABLE' and owner='SCHEMA'
/
....
spool the file the run.
0
 
DanielztCommented:

when you delete the objects of the user, the schema info changes at the same time.

you can use the follwing way to delete what you want:

sql>spool drop_table.sql;
sql>select 'DROP '||OWNER||'.'||OBJECT_NAME ||' CASCADE;' from dba_OBJECTS
where OWNER='user_name' AND OBJECT_TYPE= 'TABLE';
sql>spool off;
sql>start drop_table.sql

you can do a little change for deleting views, procedures.....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.