Solved

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

Posted on 2004-04-28
4
4,366 Views
Last Modified: 2008-03-10
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
Comment
Question by:shelbyinfotech
  • 2
4 Comments
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
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
 
LVL 23

Accepted Solution

by:
seazodiac earned 500 total points
Comment Utility
also a reminder, don't forget to close your question if people do provide you a solution.

show some courtesy...
0
 
LVL 8

Expert Comment

by:baonguyen1
Comment Utility
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
 
LVL 8

Expert Comment

by:Danielzt
Comment Utility

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now