This content has been marked as final.
Show 13 replies
-
1. Re: How can I delete all user objects in a schema????
The Human Fly Sep 14, 2005 9:32 AM (in response to 443882)Simply method would, if acceptable, drop the schema with cascade and re-create with same previleages.
Jaffar -
2. Re: How can I delete all user objects in a schema????
376714 Sep 14, 2005 9:35 AM (in response to 443882)Hi,
If you don't mind the grants, just drop the user (and recreate it).
If not, use something like:spool /tmp/purge.sql
Run it without actually running the spool first, and check it worked right.
conn / as sysdba
set head off feed off verify off
define owner=MYUSERNAME
SELECT 'DROP TABLE &owner..' || TABLE_NAME || ' CASCADE CONSTRAINTS;' FROM DBA_TABLES WHERE OWNER=UPPER('&owner');
-- idem for views
-- idem for procedures/functions/packages/...
spool off
@/tmp/purge.sql
Regards,
Yoann. -
3. Re: How can I delete all user objects in a schema????
Nicolas Gasparotto Sep 14, 2005 9:41 AM (in response to 443882)Hi,
Delete all objects from a user without drop user, if I well understand :conn test/test
Nicolas.
set head off
set pagesize 0
set verify off
set feedback off
select 'drop '||object_type||' '|| object_name||';'
from user_objects
spool drop_objects.sql
/
spool off
@drop_objects.sql
Message was edited by:
N. Gasparotto -
4. Re: How can I delete all user objects in a schema????
Jens Petersen Sep 14, 2005 9:50 AM (in response to Nicolas Gasparotto)If you have foreign key constraints defined you probably need to run these scripts several times until all tables are deleted. -
5. Re: How can I delete all user objects in a schema????
376714 Sep 14, 2005 10:00 AM (in response to Jens Petersen)That's why i included "CASCADE CONSTRAINTS".
Maybe Nicola's script modified like:conn test/test
would be better..
set head off
set pagesize 0
set verify off
set feedback off
select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from user_objects
spool drop_objects.sql
/
spool off
@drop_objects.sql
Regards,
Yoann. -
6. Re: How can I delete all user objects in a schema????
Nicolas Gasparotto Sep 14, 2005 10:03 AM (in response to 376714)Thank to correct me Yoann, like this the script will work very well...
Nicolas. -
7. Re: How can I delete all user objects in a schema????
376714 Sep 14, 2005 10:05 AM (in response to Nicolas Gasparotto)Your script was better than the one in my post, and I try to always use the most efficient way, hence the added DECODE.
Yoann. -
8. Re: How can I delete all user objects in a schema????
554899 Jan 10, 2008 11:21 AM (in response to 376714)hi,
I'm trying to import from a export dump file.
Here is what I did-
Created user A with table temp - 2 rows. Exported to a dump file with following command.
then, I imported the dump file using following script in SQL*PLUS-exp A/password@tns owner=A file=E:\Projects\IDC\Docs\exp_A.dump log=E:\Projects\IDC\Docs\A.log
but I see a new table when select from tab-set head off set pagesize 0 set verify off set feedback off select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';') from user_objects spool C:\drop_objects.sql / spool off @C:\drop_objects.sql host imp test111/test111@orcl fromuser=test111 touser=test111 file=E:\Projects\IDC\Docs\exp_test111.dump log=E:\Projects\IDC\Docs\imp_test111.log IGNORE=Y
SQL> select * from tab;
what is this BIN**** table???
BIN$foItORkqSzuCqjQe1jtbzg==$0 TABLE
TEMP TABLE
now if I run the script again then it throws error that sql statement is invalid---
and the script which generated (drop_objects.sql) it was as followsSQL> @E:\Projects\IDC\Performance_Issue\export_import\import.sql drop TABLE BIN$foItORkqSzuCqjQe1jtbzg==$0 CASCADE CONSTRAINTS; drop TABLE TEMP CASCADE CONSTRAINTS; drop TABLE BIN$foItORkqSzuCqjQe1jtbzg==$0 CASCADE CONSTRAINTS * ERROR at line 1: ORA-00933: SQL command not properly ended
Please help. My goal is to take schema export and then some other day import into the same schema from that export dump. User cant be dropped. Instead all objects in the schema to be dropped before import.drop TABLE BIN$foItORkqSzuCqjQe1jtbzg==$0 CASCADE CONSTRAINTS; drop TABLE TEMP CASCADE CONSTRAINTS;
Thanks. -
9. Re: How can I delete all user objects in a schema????
153119 Jan 10, 2008 12:44 PM (in response to 554899)You are using 10g.
You have the recycle bin on.
Your tables go to the recycle bin and are renamed to BIN$....
You need to use
drop table foo PURGE
etc.
Note: I'm hardly using 10g, but as I read docs, even I am aware of the recycle bin existence.
--
Sybrand Bakker
Senior Oracle DBA -
10. Re: How can I delete all user objects in a schema????
554899 Feb 11, 2008 2:24 PM (in response to 153119)Thanks Sybrand. -
11. Re: How can I delete all user objects in a schema????
618702 Feb 11, 2008 2:33 PM (in response to 443882)drop schema <schema_name> [cascade | restrict];
default is restrict, use cascade for dropping all without any constraint violations or child record errors. -
12. Re: How can I delete all user objects in a schema????
842286 Feb 23, 2011 9:42 PM (in response to 443882)The below noted anonymous PL/SQL block will drop all TABLEs, VIEWs, PACKAGEs, TYPEs, PROCEDUREs, FUNCTIONs, TRIGGERs and SEQUENCEs in the schema where it is executed.
------------------------------------------------------------------------------------------------------------------------------------------------------------------
declare
v_str1 varchar2(200) := null;
cursor get_sql is
select
'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS PURGE') v_str1
from user_objects
where object_type in ('TABLE','VIEW','PACKAGE','TYPE','PROCEDURE','FUNCTION','TRIGGER','SEQUENCE')
order by object_type,object_name;
begin
open get_sql;
loop
fetch get_sql into v_str1;
if get_sql%notfound
then exit;
end if;
execute immediate v_str1;
end loop;
close get_sql;
end;
/
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Edited by: user8250927 on Feb 23, 2011 1:41 PM -
13. Re: How can I delete all user objects in a schema????
540846 Apr 5, 2013 11:49 AM (in response to 842286)Or you could use an inline cursor....
No need for OPEN, FETCH, CLOSE, etc. and just as fast.
DECLARE BEGIN FOR r1 IN ( SELECT 'DROP ' || object_type || ' ' || object_name || DECODE ( object_type, 'TABLE', ' CASCADE CONSTRAINTS PURGE' ) AS v_sql FROM user_objects WHERE object_type IN ( 'TABLE', 'VIEW', 'PACKAGE', 'TYPE', 'PROCEDURE', 'FUNCTION', 'TRIGGER', 'SEQUENCE' ) ORDER BY object_type, object_name ) LOOP EXECUTE IMMEDIATE r1.v_sql; END LOOP; END; /