Drop tables 1

[Annelize]

Hi guys

I'm relatively new to Oracle, and want to know syntax for dropping all the tables, indexes and constraints that were created at once!

I don't want to drop the entire schema.

Thanks in advance
A

 

[KenCunningham]

Hi. Take a look at the CASCADE option to drop, ie drop table <table> cascade, removes anything else associated with the table, including constraints and views.

 

[Annelize]

Hi

I don't have a problem with dropping a single table and all its constraints etc. I want to know how to do ALL of them at once.

Thanks
A

 

[sem]

Drop user

Regards, Dima

http://seminihin.narod.ru

 

[Annelize]

Could you please provide me with some syntax...

 

[bogman]

The following command should do what you want as long as your account has the system privilege to drop another user:

DROP USER username CASCADE

 

[SantaMufasa]

Annelize,

A contradiction exists in your request:

I don't want to drop the entire schema.

If you

DROP USER username CASCADE

...then you have just dropped the entire schema.

How do you reconcile this ambiguity?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[Annelize]

Hi Dave

What I meant was I don't want to drop and recreate the schema as that would be an easy way of getting rid of everything. I want to explicitly drop tables and everything that reference it.

Thanks Bogman for you the syntax!

A

 

[Turkbear]

Hi,
Odd, you say you do not want to drop the schema, but yet you thank Bogman for giving you the syntax for dropping the schema...






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Annelize,

I promise that Turkbear and I are not trying to gang up on you...we're just puzzled:

I don't want to drop and recreate the schema as that would be an easy way of getting rid of everything.

I was not aware that "the easy way" is ruled out of the competition. If we are looking for "the hardest way", I'm sure I can contrive some very time-consuming, labour-intensive methods for draining a schema if that is the goal. <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[KenCunningham]

I want to explicitly drop tables and everything that reference it.

Confused? I am too.

 

[Annelize]

Hi guys

Sorry about the confusion. This is the way I understand it, which might be wrong, so any help would be gladly appreciated.

When I create a schema, I create a username and password as well as certain permission to that specific schema. Now after I created tables, Indexes and constraints in that schema I could delete everything by just dropping the whole schema right? That would take away everything that I've created including my permissions and I'll have to set up everything from the start.

All I wanted to do, was delete the newly created tables, indexes and constraints.

I seem to have things wrong though as I've caused quite a bit of confusion. My apologies

A

 

[KenCunningham]

Annelize, no problem with the confusion, i have at least one episode a day!

You could presumably write a query to get all table_names from user_tables (select table_name from user_tables), write these to a spool file, then edit that file to include the drop table <table_name> cascade command for each. Then simply run the file as a sql script to do the necessary. Does that make sense?

 

[Annelize]

Ken,

Yes it does thanks.
Basically I'll do the following select :

select 'DROP TABLE ' || object_name || ';'
   from user_objects
 where object_type = 'TABLE'

and then to the same for indexes, sequences or anything else I want to drop in the future.

A

 

[KenCunningham]

Glad to help. I'd not considered your construct, but it's certainly easier than mine in terms of editing etc! Good luck.

 

[SJSFoxPro]

Annelize,

You've probably already finished, but I wanted to give you one more suggestion to drop the tables and associated objects (indexes, sequences, etc.)...

Login as the schema owner
Create script:
select 'DROP TABLE ' || table_name || ' CASCADE;'
from user_tables;

Might save you a couple of steps in the future.

 

[SantaMufasa]

SJS,

Actually, your script will get rid of your tables, constraints, indexes, and triggers, but it will not (as you suggested) get rid of sequences nor views, synonyms, functions, packages, procedures, et cetera.

To empty out a schema of all objects, you can use the following script:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor "Dasages, LLC" makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM 
REM Please contact the author via email (dave@dasages.com) when 
REM you have comments, suggestions, and/or difficulties with this
REM package or its functions.
REM
REM [Please keep the above disclaimer and the embedded electronic 
REM  documentation with this script.]
REM **************************************************************
set feedback off
set pagesize 0
spool temp.sql
select 'DROP TABLE '||table_name||' cascade constraints;'
from user_tables
order by table_name
/
select 'DROP '||object_type||' '||object_name||';'
from user_objects 
where object_type not in ('TABLE','INDEX','TRIGGER')
order by object_type, object_name
/
spool off
set pagesize 35
set feedback on
prompt
prompt To actually drop objects from this schema, '@temp.sql' at the SQL> prompt
prompt

DROP TABLE TIMES cascade constraints;
DROP TABLE X cascade constraints;
DROP DATABASE LINK YADA.WORLD;
DROP FUNCTION GET_DATE;
DROP FUNCTION GET_DISTANCE;
DROP FUNCTION TO_MILLISECS;
DROP PACKAGE VAC;
DROP PACKAGE VACATION_LIABILITY;
DROP PACKAGE BODY VAC;
DROP PACKAGE BODY VACATION_LIABILITY;
DROP PROCEDURE PREP_USER_CONSTRAINTS;
DROP PROCEDURE PRT;
DROP PROCEDURE SK;
DROP SEQUENCE S_CUSTOMER_ID;
DROP SEQUENCE S_DEPT_ID;
DROP SEQUENCE S_EMP_ID;

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[SJSFoxPro]

Sorry. Thanks for catching that about sequences.

My intent was to answer Annelize's original "simple" question, which was "want to know syntax for dropping all the tables, indexes and constraints"... "I don't want to drop the entire schema."

Thank you for your solution which is very thorough!

 

[Annelize]

Thanks for your feedback guys. I have already succesfully dropped everything I wanted to, but next time I will keep this approach in mind.

A

 

[sem]

I think that where object_type not in ('TABLE','INDEX','TRIGGER') is redundant as if everything goes right there's no more tables at that time.
Since it's 9i rather than 5,6,7 forum, it would be easier to make it in one pl/sql block:

begin
  for f in (select 'DROP TABLE '||table_name||' cascade constraints' cmd 
   from user_tables) loop
    execute immediate f.cmd;
  end loop;

  for f in (select 'DROP '||object_type||' '||object_name cmd 
   from user_objects) loop
    execute immediate f.cmd;
  end loop;


end ;

Regards, Dima

http://seminihin.narod.ru