13 Replies Latest reply on Apr 5, 2013 11:49 AM by 540846

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

    443882
      How can I delete all user objects in a schema???? (Tables, views, procedures, dblinks, etc….)

      Thanks in advance!
        • 1. Re: How can I delete all user objects in a schema????
          The Human Fly
          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
            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.
            • 3. Re: How can I delete all user objects in a schema????
              Nicolas.Gasparotto
              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
              • 4. Re: How can I delete all user objects in a schema????
                Jens Petersen
                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
                  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.
                  • 6. Re: How can I delete all user objects in a schema????
                    Nicolas.Gasparotto
                    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
                      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
                        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.
                        • 9. Re: How can I delete all user objects in a schema????
                          153119
                          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
                          • 11. Re: How can I delete all user objects in a schema????
                            618702
                            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
                              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
                                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;
                                /