?
Solved

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

Posted on 2004-04-28
4
Medium Priority
?
4,371 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 10942281
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 2000 total points
ID: 10942290
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
ID: 10942350
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
ID: 10942461

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

777 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