Home » SQL & PL/SQL » SQL & PL/SQL » drop all views and sequences form a schema
drop all views and sequences form a schema [message #261962] Fri, 24 August 2007 03:36 Go to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I need a script to drop all views and sequences from a given schema.
for droping all tables I use

select 'drop table ', table_name, 'cascade constraints \;' from user_tables;

can I get something similar to drop all views and sequences ?
also can I get breif explanation on this script
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET MARKUP HTML OFF
SET ESCAPE \
SPOOL DELETEME.SQL
select 'drop table ', table_name, 'cascade constraints \;' from user_tables;

SPOOL OFF
@DELETEME

what is all SET statements for why we need them ?

Re: drop all views and sequences form a schema [message #261964 is a reply to message #261962] Fri, 24 August 2007 03:44 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
set 1
set 2
If you don't put them, you will get some characters like heading, etc, which you don't want to be there while getting the script DELETEME.
You can check this yourself by removing them (you can check the file DELETEME. Better not to run @DELETEME).

By
Vamsi
Re: drop all views and sequences form a schema [message #261966 is a reply to message #261962] Fri, 24 August 2007 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:57926815441661#66700686444171

Regards
Michel
Re: drop all views and sequences form a schema [message #261970 is a reply to message #261966] Fri, 24 August 2007 04:06 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Seems I missed it out in my previous post. Cool
Quote:
can I get something similar to drop all views and sequences ?
By
Vamsi

[Updated on: Fri, 24 August 2007 04:07]

Report message to a moderator

Re: drop all views and sequences form a schema [message #261973 is a reply to message #261970] Fri, 24 August 2007 04:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just select from USER_VIEWS and USER SEQUENCES instead of USER_TABLES, and use the syntax 'DROP SEQUENCE' and 'DROP VIEW'.


You could use the Execute Immediate command to avoid the whole spooling to file and executing aproach.
(Untested code)
BEGIN
  FOR rec in (select 'drop view '||view_name cmd 
              from user_views
              union all
              select 'drop sequence '||sequence_name
              from user_sequences) LOOP
    execute immediate rec.cmd;
  END LOOP;
END;
/


Re: drop all views and sequences form a schema [message #261982 is a reply to message #261962] Fri, 24 August 2007 04:50 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I am trying the approach posted by Michel Cadot and JRowbottom.
With Michel Cadot suggestion
the file generates
drop TABLE BIN$OGQMogJ+1czgQAEK/AFMew==$0 cascade constraints;
drop TABLE BIN$OGQMogJ11czgQAEK/AFMew==$0 cascade constraints;
drop TABLE BIN$OGQMogJ51czgQAEK/AFMew==$0 cascade constraints;
drop TABLE BIN$OGQMogJY1czgQAEK/AFMew==$0 cascade constraints;

and more such tables. I donot know about those tables and i donot want to delete them i want to delete only my application related tables ? moreover
I get
ORA-00933: SQL command not properly ended for all table listed above , is there a way I select only tables related to my application ?

Re: drop all views and sequences form a schema [message #261990 is a reply to message #261982] Fri, 24 August 2007 05:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's not my suggestion.

Is there a naming convention that you have stuck rigidly to that identifies tables for your application.
If there is, then add that into the WHERE clause in the USER_TABLES select to get a list of your application tables.
Re: drop all views and sequences form a schema [message #262000 is a reply to message #261962] Fri, 24 August 2007 05:50 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
JRowbottom I also followed your suggestion .I modified your script
to include tables as well here is script


BEGIN

FOR rec in (select 'drop view '||view_name cmd
from user_views
union all
select 'drop table '||table_name || ' cascade constraints'
from user_tables
union all
select 'drop sequence '||sequence_name
from user_sequences) LOOP
execute immediate rec.cmd;
END LOOP;

END;
/
I tested It worked .
Thnaks
Miro
Re: drop all views and sequences form a schema [message #262074 is a reply to message #262000] Fri, 24 August 2007 07:54 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Unless you want to have a growing recyclebin, you'd be best to put a keyword PURGE on the drops for the tables.
Previous Topic: co-related sub queries
Next Topic: if in where or conditional where
Goto Forum:
  


Current Time: Fri Dec 02 22:59:20 CST 2016

Total time taken to generate the page: 0.06344 seconds