1 2 Previous Next 18 Replies Latest reply: Oct 27, 2010 3:56 AM by CKPT RSS

    Delete all tables

    585321
      How to delete all the tables of a user?
        • 1. Re: Delete all tables
          JustinCave
          1) Do you mean delete? Drop? Truncate? Or something else?
          2) Do you want to drop the user as well?
          3) Are there foreign keys in the system?

          Assuming you mean "drop", the easiest option is often to drop and re-create the user.

          Justin
          • 2. Re: Delete all tables
            Madrid
            If you want to drop the schema, you may want to issue the command:

            drop user username cascade;

            Be aware that this is a syntactically easy method to purge, not only the tables, but the user itself.

            If you only want to get rid only of the tables, here is not a single command to achieve this task, but you could make an S.O.S. sql out of sql, a managed query to produce the script.


            ~ Madrid
            • 3. Re: Delete all tables
              615305
              To drop all the tables(only tables) of a user you can use the following query.

              select 'drop table '||table_name||' cascade constraints;' from user_tables;

              Spool the result of this query and execute it.
              • 4. Re: Delete all tables
                682564
                Thank you. That was very usefull.
                • 5. Re: Delete all tables
                  690721
                  Begin
                  for c in (select table_name from user_tables) loop
                  execute immediate ('drop table '||c.table_name||' cascade constraints);
                  end loop;
                  End;

                  Best regard,
                  [Fellow Developer|http://www.fellowdeveloper.blogspot.com]
                  ----------------------------------------------------------------------------------------------------
                  http://www.fellowdeveloper.blogspot.com
                  • 6. Re: Delete all tables
                    418213
                    I agree that it's the same thing by dropping that user directly
                    • 7. Re: Delete all tables
                      Srini Chavali-Oracle
                      Not really ;-) The scripts described above drop user tables only - other objects such as procedures, packages, sequences, triggers etc etc will remain. Dropping the schema will drop all user objects, obviously.

                      HTH
                      Srini
                      • 8. Re: Delete all tables
                        704017
                        Ramoorthy wrote:
                        To drop all the tables(only tables) of a user you can use the following query.

                        select 'drop table '||table_name||' cascade constraints;' from user_tables;

                        Spool the result of this query and execute it.
                        Thanks. I used the following as the ; gave me fits:

                        select 'drop table '|| TABLESPACE_NAME|| '.' ||table_name || ' cascade constraints' || CHR(59) from user_tables where TABLESPACE_NAME = 'schema' order by table_name;
                        • 9. Re: Delete all tables
                          EdStevens
                          user1191303 wrote:
                          Ramoorthy wrote:
                          To drop all the tables(only tables) of a user you can use the following query.

                          select 'drop table '||table_name||' cascade constraints;' from user_tables;

                          Spool the result of this query and execute it.
                          Thanks. I used the following as the ; gave me fits:

                          select 'drop table '|| TABLESPACE_NAME|| '.' ||table_name || ' cascade constraints' || CHR(59) from user_tables where TABLESPACE_NAME = 'schema' order by table_name;
                          as well it should ... where did the reference to TABLESPACE_NAME come in? Table names are qualified with owner, not tablspace. What's with the CHR(59)? And your WHERE clause??? TABLESPACE_NAME = 'SCHEMA' ???? Of course it gave you fits, there's nothing remotely correct about it.

                          The statement you were given was correct -- EXCEPT -- there was no qualification on which table names were selected from user_tables. Suppose you were logged on as SYSTEM but wanted to delete tables belonging to SCOTT????
                          • 10. Re: Delete all tables
                            EdStevens
                            Ramoorthy wrote:
                            To drop all the tables(only tables) of a user you can use the following query.

                            select 'drop table '||table_name||' cascade constraints;' from user_tables;

                            Spool the result of this query and execute it.
                            Let's hope he doesn't execute that while logged on as SYSTEM, expecting to drop tables owned by SCOTT.
                            • 11. Re: Delete all tables
                              760428
                              Very nice, thanks :)
                              • 12. Re: Delete all tables
                                LinoPisto
                                this is what i use to delete all the objects in the schema before importing *.dmp files


                                declare
                                stringa varchar2(100);

                                cursor cur is
                                select *
                                from user_objects;

                                begin
                                for c in cur loop
                                begin
                                stringa := '';

                                if c.object_type = 'VIEW' then

                                stringa := 'drop view ' || c.object_name;
                                EXECUTE immediate stringa;

                                elsif c.object_type = 'TABLE' then

                                stringa := 'drop table ' || c.object_name || ' cascade constraints';
                                EXECUTE immediate stringa;
                                     
                                elsif c.object_type = 'SEQUENCE' then

                                stringa := 'drop sequence ' || c.object_name;
                                EXECUTE immediate stringa;
                                elsif c.object_type = 'PACKAGE' then

                                stringa := 'drop package ' || c.object_name;
                                EXECUTE immediate stringa;      

                                elsif c.object_type = 'TRIGGER' then

                                stringa := 'drop trigger ' || c.object_name;
                                EXECUTE immediate stringa;      

                                elsif c.object_type = 'PROCEDURE' then

                                stringa := 'drop procedure ' || c.object_name;
                                EXECUTE immediate stringa;

                                elsif c.object_type = 'FUNCTION' then

                                stringa := 'drop function ' || c.object_name;
                                EXECUTE immediate stringa;      
                                elsif c.object_type = 'SYNONYM' then

                                stringa := 'drop synonym ' || c.object_name;
                                EXECUTE immediate stringa;
                                elsif c.object_type = 'INDEX' then

                                stringa := 'drop index ' || c.object_name;
                                EXECUTE immediate stringa;
                                elsif c.object_type = 'PACKAGE BODY' then

                                stringa := 'drop PACKAGE BODY ' || c.object_name;
                                EXECUTE immediate stringa;      
                                elsif c.object_type = 'DATABASE LINK' then

                                stringa := 'drop database link ' || c.object_name;
                                EXECUTE immediate stringa;      
                                end if;
                                     
                                     exception
                                when others then
                                null;
                                end;
                                end loop;
                                -- PURGE recyclebin

                                end;
                                /


                                PURGE recyclebin to clean the recycle bin

                                bye,
                                Massimo
                                • 13. Re: Delete all tables
                                  Nicolas.Gasparotto
                                  Why not include object_type in stringa instead of doing all your tests, object type by object type ?
                                  Apart from that, this thread is 3 months old.

                                  Nicolas.
                                  • 14. Re: Delete all tables
                                    Maran Viswarayar
                                    Apart from that, this thread is 3 months old.
                                    Infact it is 3 yrs old :)
                                    1 2 Previous Next