How can I delete all user objects in a schema????

Simply method would, if acceptable, drop the schema with cascade and re-create with same previleages.

Jaffar

Hi,

If you don't mind the grants, just drop the user (and recreate it).

If not, use something like:

spool /tmp/purge.sql

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

Run it without actually running the spool first, and check it worked right.

Regards,

Yoann.

Hi,

Delete all objects from a user without drop user, if I well understand :

conn test/test

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

Nicolas.

Message was edited by:
N. Gasparotto

If you have foreign key constraints defined you probably need to run these scripts several times until all tables are deleted.

That's why i included "CASCADE CONSTRAINTS".
Maybe Nicola's script modified like:

conn test/test

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

would be better..

Regards,

Yoann.

Thank to correct me Yoann, like this the script will work very well...

Nicolas.

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.

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.

exp A/password@tns owner=A file=E:\Projects\IDC\Docs\exp_A.dump log=E:\Projects\IDC\Docs\A.log

then, I imported the dump file using following script in SQL*PLUS-

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

but I see a new table when select from tab-

SQL> select * from tab;

BIN$foItORkqSzuCqjQe1jtbzg==$0 TABLE

TEMP                           TABLE

what is this BIN**** table???

now if I run the script again then it throws error that sql statement is invalid---

SQL> @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

and the script which generated (drop_objects.sql) it was as follows

drop TABLE BIN$foItORkqSzuCqjQe1jtbzg==$0 CASCADE CONSTRAINTS;                  
drop TABLE TEMP CASCADE CONSTRAINTS;                                            

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.

Thanks.

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

Thanks Sybrand.

drop schema <schema_name> [cascade | restrict];

default is restrict, use cascade for dropping all without any constraint violations or child record errors.

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

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;
/