script to drop all user objects
I remember seeing a script to recursively drop all user objects (for a specific schema), so if the user want to refresh their own schema, they can use this script to drop all objects they have first, and then using an dump file to refresh.
The script I was seeing uses a 'for cursor' loop to find all objects use owns and drop them on the fly (use 'execute immediate'). But I can't really it anymore, so I wonder anybody can help me on this?
You might want to try this:
You might want to play around with it a bit.
SET SERVEROUTPUT ON SIZE 1000000
FOR cur_rec IN (SELECT object_name, object_type
WHERE object_type IN ('TABLE', 'VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')) LOOP
IF cur_rec.object_type = 'TABLE' THEN
EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" CASCADE CONSTRAINTS';
EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"';
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('FAILED: DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"');
Also, remember to do a "purge recyclebin" if you are using 10g or you will fill the recyclebin with all the dropped tables.
Thanks Tim, it worked beautifully for me!
How about just DROP'ping and CREATE'ing the user::
DROP USER MySchema CASCADE;
CREATE USER MySchema ..etc...
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
It drops public synonyms as well so be careful
set trimspool on
set pagesize 0
set line 1000
set feed off
set verify off
select 'prompt Conectando como &&OWNER' || chr(10) || 'connect &&OWNER' DROP_OBJECTS
select 'spool dropall.log' DROP_OBJECTS
when object_type = 'TABLE' then 'drop ' || object_type
|| ' ' || owner || '.' || object_name ||' cascade constraints'
when object_type = 'PACKAGE BODY' then 'prompt PACKAGES BODY'
when object_type = 'INDEX' then 'prompt INDEXES'
when object_type = 'DATABASE LINK' then 'drop ' || object_type
|| ' ' || object_name
else 'drop ' || object_type || ' ' || owner || '.' || object_name
end || ';')
where owner = '&&OWNER'
and object_type not like '%PARTITION%'
select 'drop public synonym '||synonym_name||';'
where table_owner = '&&OWNER'
and owner = 'PUBLIC'
select 'spool off'
Would it be simplyer to do what LKBrwn said?
Yes, dropping the user with the CASCADE option is quicker, but there are a number of reasons why I might not pick that option:
1) I might want to only drop certain objects. If you see my script, I limit the object types that I reference.
2) If there are only a few objects I might clean up with a drop, rather than trash the whole user.
3) Some of my users have loads of privileges that I don't want to recreate, especially all the administrative ones granted via packages like the AQ and Java. For these I may decide to do a drop of the objects, rather than the user.
If of course you make a habit of regularly clearing out the contents of users, I guess doing a full drop and recreate of the user is a better option. Horses for courses!
Alot of this is just preference, not science, but it's not something you do all the time, so I don't get precious about it
When I drop a schema it takes like 20 mins or so to drop it..is there a way to increase that response time?
"High Salaries = Happiness = Project Success."
yes, have less objects in their schemas.
Seriously, how many object do you have to drop - what are you waiting on when it is happening
Click Here to Expand Forum to Full Width