Is there any way to drop all schema objects at once?
Thanks
Printable View
Is there any way to drop all schema objects at once?
Thanks
Hi,
If you trully mean all schema objects (including sys schema)
then just delete the database by shutting down and deleting all relevant files(datafiles,control files, redo logs etc)
I want to drop tables and indexes overall...
Hi,
Do you want to do it for a particular user then the code is:
select ' DROP '||object_type ,object_name||';'|| from user_objects ;
Hope it works,
Take Care.
There isn't a command that will drop all schema objects (not one that i've come across anyway).
You could just drop the tablespaces containing the indexes and tables assuming that you have different tablespaces for each type of object.
dear sir ,
Simply drop the all users with command
drop user username cascade ;
and next time create the users
While creating tablespaces again use "reuse" option
virajvk
Execute the following script first . This will generate a spooled output which you can execute to drop all the objects for a particular schema.
set linesize 200
set termout off
set heading off
set feedback off
set echo off
spool dropobj.sql
Select 'drop ' || OBJECT_TYPE , OBJECT_NAME || ' CASCADE CONSTRAINTS;' FROM USER_OBJECTS WHERE OBJECT_TYPE LIKE 'TABLE';
Select 'drop ' || OBJECT_TYPE , OBJECT_NAME || ';' FROM USER_OBJECTS WHERE
OBJECT_TYPE LIKE 'SEQUENCE';
Select 'drop ' || OBJECT_TYPE , OBJECT_NAME ||';' FROM USER_OBJECTS where object_type not in ('TABLE' ,'INDEX','SEQUENCE','PACKAGE BODY');
spool off
set heading on
set feedback on
set termout on
set echo on