Home » SQL & PL/SQL » SQL & PL/SQL » script to delete all objets of schema (oracle 9i , solaris)
script to delete all objets of schema [message #334402] Wed, 16 July 2008 06:44 Go to next message
koff10
Messages: 58
Registered: December 2006
Location: france
Member
Hi all,
I'm a junior developper and I'm looking for sql script to delete all oracle objets in my own schema.

Thanks for help.
koff
Re: script to delete all objets of schema [message #334405 is a reply to message #334402] Wed, 16 July 2008 06:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I use this one:
set heading off
set pagesize 0
set feedback off
set timing off
set time off
set trimspool on
set trimout on
set linesize 100
set echo off
spool t
select 'drop '||object_type||' '||object_name||
       decode(object_type,'CLUSTER',' including tables cascade constraints',
                          'TABLE',' cascade constraints',
                          '')||';'
from user_objects
where object_type in ('CLUSTER','CONTEXT','DATABASE LINK','DIMENSION',
                      'DIRECTORY','FUNCTION','INDEX TYPE',
                      'JAVA','LIBRARY','MATERIALIZED VIEW','OPERATOR',
                      'OUTLINE','PACKAGE','PROCEDURE','SEQUENCE',
                      'SYNONYM','TABLE','TYPE','VIEW')
order by object_type, object_name
/
spool off
@t.lst

Regards
Michel
Re: script to delete all objets of schema [message #334413 is a reply to message #334405] Wed, 16 July 2008 07:21 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
And then purge the recyclebin afterward to get rid of the deleted tables.

purge recyclebin
Re: script to delete all objets of schema [message #334425 is a reply to message #334413] Wed, 16 July 2008 07:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not in 9i, not in 9i. Wink
But you're right I should (and will) add this to my old script.

Regards
Michel
Re: script to delete all objets of schema [message #334427 is a reply to message #334402] Wed, 16 July 2008 07:52 Go to previous messageGo to next message
koff10
Messages: 58
Registered: December 2006
Location: france
Member
thanks for all
koff
Re: script to delete all objets of schema [message #334431 is a reply to message #334425] Wed, 16 July 2008 07:57 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Ah correct, the old "burnt by the sub-subject being there in the topic listing, but disappearing in the topic" again, so I missed it Wink
Previous Topic: Removing ',' in values?
Next Topic: need help to create index to optimize query
Goto Forum:
  


Current Time: Sat Dec 03 16:02:20 CST 2016

Total time taken to generate the page: 0.07071 seconds