Home » SQL & PL/SQL » SQL & PL/SQL » Removing multiple tables in one go [merged]
Removing multiple tables in one go [merged] [message #377614] Tue, 23 December 2008 23:07 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello friends,

I have to remove all the tables from one schema.
But there are lots of tables available in that schema.
Is there any way, so that I can remove those tables by using single command?

regards,
Delna
Re: Removing all tables of one schema at a time [message #377615 is a reply to message #377614] Tue, 23 December 2008 23:10 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
It is not possible until you make a script for DROP TABLE <TABLE_NAME>.

like

SELECT 'DROP TABLE '||table_name||';' FROM USER_TABLES;


and run this script on sqlplus.

Thanks
Trivendra
Re: Removing all tables of one schema at a time [message #377639 is a reply to message #377615] Wed, 24 December 2008 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A correction:
SELECT 'DROP TABLE '||table_name||' CASCADE CONSTRAINTS;' FROM USER_TABLES;

Regards
Michel
Re: Removing all tables of one schema at a time [message #377641 is a reply to message #377614] Wed, 24 December 2008 01:00 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Thanks Michel.
Re: Removing all tables of one schema at a time [message #377658 is a reply to message #377641] Wed, 24 December 2008 02:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you can run something like this:
BEGIN
  FOR rec IN (SELECT table_name FROM user_tables) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE '||rec.table_name||' CASCADE CONSTRAINTS';
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;
  END LOOP;
END;
/

If you run that a couple of times, that will clear out the tables.
Re: Removing all tables of one schema at a time [message #377706 is a reply to message #377658] Wed, 24 December 2008 07:27 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thanks to all.

Finally I used SQL approach using script.

regards,
Delna
ORA-00903: invalid table name [message #377797 is a reply to message #377614] Thu, 25 December 2008 03:32 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi all,

I am having one more question today.
I have around 100 tables containing name staring with ‘TABLE’ as shown below.

SQL> select table_name from user_tables where table_name like 'TABLE%'
  2  ;

TABLE1
TABLE10
TABLE11
TABLE12
TABLE13
TABLE14
TABLE15
TABLE16
TABLE17
TABLE18
TABLE19
TABLE2
TABLE20
TABLE21
…
TABLE96
TABLE97
TABLE98

98 rows selected.


And to DROP all those tables, I have created procedure as,

SQL> create or replace procedure remove_all_tables
  2  as
  3  begin
  4   for cur in (select table_name from user_tables where table_name like 'TABLE%')
  5   loop
  6    if (cur.table_name is not null) then
  7     execute immediate 'drop table :x purge' using cur.table_name;
  8    end if;
  9   end loop;
 10  end;
 11  /

Procedure created.


But when I execute that procedure, it gives error. Please help me to solve

SQL> execute remove_all_tables;
BEGIN remove_all_tables; END;

*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at "NARESH.REMOVE_ALL_TABLES", line 7
ORA-06512: at line 1


regards,
Delna
Re: ORA-00903: invalid table name [message #377798 is a reply to message #377797] Thu, 25 December 2008 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't use a bind variable for an object name only for values.

Regards
Michel
Re: ORA-00903: invalid table name [message #377799 is a reply to message #377797] Thu, 25 December 2008 03:40 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
so I should use that statement as

execute immediate 'drop table ' || cur.table_name || ' purge';


is it so?

thanks Michel sir.

regards,
Delna
Re: ORA-00903: invalid table name [message #377804 is a reply to message #377799] Thu, 25 December 2008 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, but have a look at DBMS_ASSERT to prevent from SQL injection (only documented in 11g but already exists in 10g).

Regards
Michel
Re: ORA-00903: invalid table name [message #377808 is a reply to message #377797] Thu, 25 December 2008 07:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No need to create a new topic for this; merged the two.
Re: ORA-00903: invalid table name [message #377813 is a reply to message #377804] Thu, 25 December 2008 08:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DBMS_ASSERT doc: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_assert.htm#CHDIFDJE

Regards
Michel
Re: Removing multiple tables in one go [merged] [message #377817 is a reply to message #377614] Thu, 25 December 2008 13:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
delna.sexy wrote on Tue, 23 December 2008 21:07

I have to remove all the tables from one schema.



If there is nothing else in the schema but the tables that you plan to drop, then it might be simpler and faster to drop and recreate the schema.
Re: Removing multiple tables in one go [merged] [message #377819 is a reply to message #377817] Thu, 25 December 2008 13:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The main drawback of dropping the schema is that you loose the privileges it has on other schema objects.

Regards
Michel
Re: Removing multiple tables in one go [merged] [message #377884 is a reply to message #377819] Fri, 26 December 2008 01:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Thu, 25 December 2008 20:31
The main drawback of dropping the schema is that you loose the privileges it has on other schema objects.

Regards
Michel


True, but any self-respecting system has scripts to recreate the user, including all its privs.
Re: Removing multiple tables in one go [merged] [message #377953 is a reply to message #377884] Fri, 26 December 2008 20:33 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi Frank,

Quote:

True, but any self-respecting system has scripts to recreate the user, including all its privs.



I didn't get this.
and please specify, how to do it?

regards,
Delna
Re: Removing multiple tables in one go [merged] [message #377957 is a reply to message #377953] Fri, 26 December 2008 21:34 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi delna,
it means IF you drop the schema and recreate it. Do you want to do that?
Bye
Re: Removing multiple tables in one go [merged] [message #377963 is a reply to message #377953] Sat, 27 December 2008 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It means that in a well managed system, you should have a script for each task:
create tables
create PL/SQL procedures and other codes
create application roles
create end user account and its privileges
...

Regards
Michel
Re: Removing multiple tables in one go [merged] [message #378101 is a reply to message #377963] Sun, 28 December 2008 21:48 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Ohhh K,

Thanks to all for their kind reply and suggestion.

regard,
Delna
Previous Topic: UTL_HTTP,UTL.DBWS or Table Functions?
Next Topic: Oracle equivalent of SQL Server table-valued functions
Goto Forum:
  


Current Time: Sun Dec 04 16:50:29 CST 2016

Total time taken to generate the page: 0.07537 seconds