Re: script to delete miltiple tables

Jeanneli wrote:
>
> Hi,
>
> Need someone's help here. I have a speadsheet that have 300 table names. I want
> a SQL script to read the file and delete those tables in a single database.
>
> Any one has the similar codes, or at least point me to the right direction? I
> am new to oracle :(.
>
> Thanks so much.
> Jeanne

Jeanne,

I'm going to base this upon the Oracle online documentation. Its available at technet - http://technet.oracle.com - but that's not currently functioning.
You can even download the entire documentation set - around 550 MB.

Have you ever used sql*loader?
if yes, this will seem easy:

1. export the spreadsheet as a text file - the file named myfile.txt will be used here.

        You will only need the table_name column, but sql*loader can skip the other columns.

2. create a controlfile for the load.

        This could be accomplished by including a header in the text file, and renaming it with the

        extension .ctl.

LOAD DATA
INFILE *
APPEND
INTO TABLE my_kill_list
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' (table_name)
BEGINDATA 3. connect with sqlplus and create the table

	CREATE TABLE my_kill_list (
	  table_name	VARCHAR2(30)
        );

4. execute the control file with sqlldr.exe (path assumed to be on C:\ for simplicity)

        C:\> sqlldr username/password_at_alias control=myfile.ctl

5. create the drop table script (did you mean truncate the tables, or drop the tables?)

        (please fill in the <owner> with your schema_name)

        in sqlplus:

	set heading off verify off feedback off termout off 
	set trimspool on linesize 132 pagesize 0
	spool exec_my_kill_list.sql
	select 'spool exec_my_kill_list.lst' from dual;
	select 'set deading on verify on feedback on serveroutput on size
1000000
	select 'truncate table <owner>.'||table_name||';'
          from my_kill_list
         order by table_name;
	select 'drop table <owner>.'||table_name||';'
          from my_kill_list
         order by table_name;
	select 'spool off' from dual;
	spool off

6. execute the delete script

        in sqlplus

        @exec_my_kill_list.sql;

7. examine the list file in notepad.

that should be it.

Paul Received on Sun May 06 2001 - 14:56:59 CDT