Home » SQL & PL/SQL » SQL & PL/SQL » related to drop command
related to drop command [message #217468] Fri, 02 February 2007 06:24 Go to next message
pooja_09
Messages: 28
Registered: June 2005
Location: Delhi
Junior Member

how to Drop all tables for a particular user..??
I m using oracle 9i.
Re: related to drop command [message #217469 is a reply to message #217468] Fri, 02 February 2007 06:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
untested, but you'll get the idea.
BEGIN
  FOR con in (SELECT constraint_name,table_name FROM user_constraints WHERE constraint_type = 'R') LOOP
    execute immmediate 'ALTER TABLE '||cons.table_name||' DISABLE CONSTRAINT '||cons.constraint_name;
  END LOOP;

  FOR rec in (SELECT table_name FROM user_tables) LOOP
    execute immediate 'DROP TABLE '||rec.table_name;
  END LOOP;
END;


Re: related to drop command [message #217471 is a reply to message #217469] Fri, 02 February 2007 06:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Just to add,
drop table table_name cascade constraints;
Re: related to drop command [message #217474 is a reply to message #217471] Fri, 02 February 2007 06:54 Go to previous messageGo to next message
karismapanda
Messages: 58
Registered: January 2007
Member
can we use it.........

select 'drop table '||table_name||'cascade costraints;'
from all_tables
where owner='user';

then fire all the generated statements.

OR is there any issue
Re: related to drop command [message #217604 is a reply to message #217474] Sat, 03 February 2007 00:03 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
No issue.
you will drop all tables.

consider
SQL> create table one ( no number, name varchar2(20));

Table created.

SQL> create table two ( no number, no1 number);

Table created.

SQL>
SQL> create table three as select * from all_objects where rownum <= 10;

Table created.

SQL> alter table one add constraint pkno primary key(no);

Table altered.

SQL> alter table two add constraint fkno foreign key (no) references one (no);

Table altered.

SQL> set heading off
SQL> set echo off
SQL> set feedback off
SQL> spool d:\drop.sql
SQL> select 'drop table ' || table_name ||' cascade constraints;'
  2  from user_tables;

drop table THREE cascade constraints;
drop table TWO cascade constraints;
drop table ONE cascade constraints;
SQL> spool off
SQL> set heading on
SQL> set echo on
SQL> set feedback on
SQL> @d:\drop.sql
SQL> SQL> select 'drop table ' || table_name ||' cascade constraints;'
SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
SQL>   2  from user_tables;
SQL>
SQL> drop table THREE cascade constraints;

Table dropped.

SQL> drop table TWO cascade constraints;

Table dropped.

SQL> drop table ONE cascade constraints;

Table dropped.

SQL>






[Updated on: Sat, 03 February 2007 01:50]

Report message to a moderator

Re: related to drop command [message #217610 is a reply to message #217469] Sat, 03 February 2007 01:33 Go to previous messageGo to next message
pooja_09
Messages: 28
Registered: June 2005
Location: Delhi
Junior Member

thanks buddy...
i m runnig the following code but it is giving error...

BEGIN
FOR con in (SELECT constraint_name,table_name FROM user_constraints WHERE constraint_type = 'R') LOOP
execute immediate 'ALTER TABLE '||con.table_name||' DISABLE CONSTRAINT '||con.constraint_name;
END LOOP;
FOR rec in (SELECT table_name FROM user_tables) LOOP
execute immediate 'DROP TABLE '||rec.table_name;
END LOOP;
End;

SQL> /
BEGIN
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
ORA-06512: at line 6

what should i do???


Re: related to drop command [message #217612 is a reply to message #217610] Sat, 03 February 2007 01:45 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
becuase of CONSTRAINT.
you have to two option
1.DISABLE ALL CONSTRAINT
2.use CASCADE CONSTRAINTS with drop table statement.
consider.
SQL> drop table one purge;
drop table one purge
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys


SQL> alter table one disable constraint pkno;
alter table one disable constraint pkno
*
ERROR at line 1:
ORA-02297: cannot disable constraint (TEST.PKNO) - dependencies exist


SQL> alter table two disable constraint fkno;

Table altered.

SQL> alter table one disable constraint pkno;

Table altered.

SQL>


regards
Taj
Re: related to drop command [message #217709 is a reply to message #217612] Mon, 05 February 2007 00:13 Go to previous message
pooja_09
Messages: 28
Registered: June 2005
Location: Delhi
Junior Member

thanks a lot....my problem is solved now.
Previous Topic: on insert trigger
Next Topic: Update Query
Goto Forum:
  


Current Time: Wed Dec 07 22:08:07 CST 2016

Total time taken to generate the page: 0.13916 seconds