why not just drop and re-create the schema?
Rajeshwaran, Jeyabal, February 13, 2017 - 8:03 am UTC
....
Intention is to only drop objects of Schema 'A'.
....
Since the intention is to drop all the objects for a schema, why not just drop and recreate that schema with all the required privileges (since they "said" - "we have DBA access" to the database), rather than dropping each and every objects.
drop user <user_name> CASCADE;
then re-create that schema again.
February 13, 2017 - 10:43 pm UTC
My general principle is I'd rather drop what I need to, rather than drop more and recreate what I really wanted to keep. (Because if I get that 2nd part wrong...I'm in a bad place).
check for schema name
Paul, February 14, 2017 - 7:43 pm UTC
I'm also not a fan of this kind of script; but have written scripts to do it anyway!
Here is how I mitigated the risk of running it in the wrong database or as the wrong schema. It relies on being run in SQL*Plus (it uses the WHENEVER SQLERROR sql*Plus setting)
WHENEVER SQLERROR EXIT
DECLARE
lv_dbname VARCHAR2(30);
lv_schemaname VARCHAR2(30) := USER;
BEGIN
/* You will have to change the below DB name and schema name to match your environment.
This is for safety. While you make those changes, you will have some time to think
about if you REALLY want to drop all tables, packagese, etc from that schema! */
SELECT global_name INTO lv_dbname FROM global_name;
IF lv_dbname <> 'PDB_1' -- change to the name of your database
THEN
raise_application_error(-20001, 'Only run this in your local DB. Dont run it in a shared database! (like prod!!!!!)');
END IF;
IF lv_schemaname <> 'ABC' -- change to the name of your schema
THEN
raise_application_error(-20001, 'Must only be run as abc schema.');
END IF;
END;
/
WHENEVER SQLERROR CONTINUE
<drop loop goes here>
Its still not completely failsafe; but it helps somewhat.
February 15, 2017 - 3:50 am UTC
Nice input.
A reader, May 16, 2017 - 6:46 pm UTC
define owner=<your_owner_name>
select 'drop '||object_type||' &owner..'|| object_name || ';' from dba_objects WHERE OWNER=UPPER('&owner') and object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX');
just a define owner fix that
jose antonio vega ruiz, May 16, 2017 - 6:47 pm UTC
select 'drop '||object_type||' &owner..'|| object_name || ';' from dba_objects WHERE OWNER=UPPER('&owner') and object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX');
A reader, February 18, 2022 - 1:42 pm UTC
For testing purposes I put it into a procedure and placed in a schema
Users can reset the schema themselfs.
create or replace procedure reset_schema
as
begin
for row in ( select 'drop '||object_type||' '|| object_name || '' as query from user_objects WHERE object_type in ('TABLE', 'VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX') and object_name != 'RESET_SCHEMA')
LOOP
execute immediate row.query;
END LOOP;
end;
February 24, 2022 - 7:25 am UTC
"reset" is an interesting choice of term :-)
I would go for "totally_destroy" so people know what's about to happen