Home » SQL & PL/SQL » SQL & PL/SQL » Delete all the tables within a schema
Delete all the tables within a schema [message #227767] Thu, 29 March 2007 06:18 Go to next message
libanori
Messages: 11
Registered: February 2007
Location: ireland
Junior Member



Hi,

What is the best way to delete all the tables within a schema?

For example if I want to delete all the content of the app schema,HZ schema etc.... without dropping the schema.
Thanks.
Re: Delete all the tables within a schema [message #227768 is a reply to message #227767] Thu, 29 March 2007 06:25 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'd probably drop the schema and recreate it. But if you want to drop all objects in a schema you can use SQL to generate the drop statements:
select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from user_objects


I stole the exact SQL from N. Gasparotto (I remembered this thread). Here's the original thread.

MHE
Re: Delete all the tables within a schema [message #227769 is a reply to message #227768] Thu, 29 March 2007 06:30 Go to previous messageGo to next message
fcatak
Messages: 16
Registered: March 2007
Location: turkey
Junior Member
declare
   stmt_    varchar2(200);
begin
   for rec_ in (select * from user_objects ) loop
      stmt_ := 'drop '||rec_.object_type||' '|| rec_.object_name; 
      if rec_.OBJECT_TYPE = 'TABLE'then
         stmt_ := stmt_ || ' CASCADE CONSTRAINTS';
      end if;
      dbmS_output.put_line(stmt_);
      execute immediate stmt_;
   end loop;
end;
Re: Delete all the tables within a schema [message #227777 is a reply to message #227767] Thu, 29 March 2007 07:08 Go to previous messageGo to next message
gkodakalla
Messages: 49
Registered: March 2005
Member
If we can modify the select clause as
select * from user_objects where object_type = 'TABLE',
we can avoid
1) fetching all the records from user_objects
2) if condition

Giridhar
Re: Delete all the tables within a schema [message #227778 is a reply to message #227777] Thu, 29 March 2007 07:11 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
gkodakalla wrote on Thu, 29 March 2007 14:08
If we can modify the select clause as
select * from user_objects where object_type = 'TABLE'
If you are interested in the tables only, why not use USER_TABLES instead of USER_OBJECTS?

MHE
Re: Delete all the tables within a schema [message #227806 is a reply to message #227778] Thu, 29 March 2007 08:03 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
You might also want to append a "purge" at the end of the drop table command unless you think you might want to bring any of those tables back.
Re: Delete all the tables within a schema [message #227809 is a reply to message #227806] Thu, 29 March 2007 08:14 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
joy_division wrote on Thu, 29 March 2007 15:03
You might also want to append a "purge" at the end of the drop table command unless you think you might want to bring any of those tables back.

Very good point, joy_division!
Re: Delete all the tables within a schema [message #227810 is a reply to message #227806] Thu, 29 March 2007 08:14 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
joy_division wrote on Thu, 29 March 2007 15:03
You might also want to append a "purge" at the end of the drop table command unless you think you might want to bring any of those tables back.

Very good point, joy_division!

MHE
Previous Topic: Identifying tablespaces
Next Topic: What does Set Pages do?
Goto Forum:
  


Current Time: Sat Dec 10 03:10:35 CST 2016

Total time taken to generate the page: 0.25940 seconds