Solved

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

Posted on 2004-04-28
4
4,369 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 500 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

733 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