OraFAQ Forum: Server Administration » procedure to delete tables

Home » RDBMS Server » Server Administration » procedure to delete tables

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

procedure to delete tables [message #61344]

Thu, 15 April 2004 12:08

sandya srinivas
Messages: 3
Registered: April 2004

Junior Member

hi
i mistakenly imported tables into system tablespace it contains
around 200 plus tables .I want to delete it
how to do any procedure to delete these tables without going one by one
pls hlp
urgent
sandya srinivas

Report message to a moderator

Re: procedure to delete tables [message #61348 is a reply to message #61344]

Thu, 15 April 2004 23:00

Padmanabhan
Messages: 3
Registered: April 2004

Junior Member

Hi Use

Use Dynamic sql to do it.

u can use object_id in cursor to fetch the table

Report message to a moderator

Re: procedure to delete tables [message #61356 is a reply to message #61344]

Fri, 16 April 2004 02:18

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

every table is an individual entity.
I beleive you have drop one by one anyhow unless u drop the schema / user.
a simple script would do it.

mag@mutation_mutation > get del
1 Declare
2 cursor c1 is select table_name from user_tables;
3 begin
4 for mag in c1 loop
5 exit when c1%notfound;
6 dbms_output.put_line ('table to be dropped: '||mag.table_name);
7 execute immediate ('drop table '||mag.table_name||' cascade constraints');
8 end loop;
9* end;
mag@mutation_mutation > /
table to be dropped: A
table to be dropped: B
table to be dropped: BONUS
table to be dropped: DEPT
table to be dropped: E
table to be dropped: EMP
table to be dropped: EMP1
table to be dropped: FILLER
table to be dropped: MEMBER
table to be dropped: PLAN_TABLE
table to be dropped: REF_1MIN
table to be dropped: SALGRADE
table to be dropped: SEQS

Report message to a moderator

Re: procedure to delete tables [message #61359 is a reply to message #61356]

Fri, 16 April 2004 12:55

sandya srinivas
Messages: 3
Registered: April 2004

Junior Member

this one i created and ok i want delete a strech if there is any way if table_name like 'trx%' from user _tables with give a counter
wiht regards
sandya

Report message to a moderator

Re: procedure to delete tables [message #61360 is a reply to message #61348]

Fri, 16 April 2004 12:57

sandya srinivas
Messages: 3
Registered: April 2004

Junior Member

hi can u more specific

Report message to a moderator

Previous Topic:	commit
Next Topic:	SQL Recursive Error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 12:25:02 CDT 2024