Is there any way to drop all schema objects at once?
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...
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,
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
A Wise Man Knows How much he doesn't know !!!
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
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');
set heading on
set feedback on
set termout on
set echo on
You can if you think you can.
Click Here to Expand Forum to Full Width