• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4382
  • Last Modified:

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
0
shelbyinfotech
Asked:
shelbyinfotech
  • 2
1 Solution
 
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
 
seazodiacCommented:
also a reminder, don't forget to close your question if people do provide you a solution.

show some courtesy...
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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now