SQL Queries - Common SQL Queries - How to remove all objects in an oracle schemas KB CB and MNGT which is used as CAST service

Purpose of Query

To remove all the objects (Functions, Tables, Procedures, Views etc.) in an oracle schema. 

Here is the valid script to be run by a user having the grant to remove objects permission in the specified schema.

Applicable CAST Version
Release
Yes/No
8.3.x(tick)
8.2.x(tick)
Applicable RDBMS
RDBMS
Yes/No
Oracle Server(tick)
Microsoft SQL Server(error) 
CSS(error) 
Query for Oracle
DECLARE
BEGIN
  FOR r1 IN ( SELECT 'DROP ' || object_type ||' '||OWNER||'.'|| object_name || DECODE ( object_type, 'TABLE', ' CASCADE CONSTRAINTS PURGE' ) AS v_sql
                FROM all_objects
               WHERE owner = 'CAST_SCHEMA_NAME'
               and OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'PACKAGE', 'TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE' )
               and not (OBJECT_TYPE = 'TABLE' and OBJECT_NAME like 'BIN$%')
               ORDER BY object_type,
                        object_name ) LOOP 
     EXECUTE IMMEDIATE r1.v_sql;
  END LOOP;
END;
COMMIT;

 Note: Replace "CAST_SCHEMA_NAME" in the query with the name of the schema in which you want to clean up the objects.

To run this query you can use any third party Oracle GUI tool (Example: Oracle SQL Developer, Aqua Data Studio etc.)

  1. Connect to the Oracle server.
  2. Run the above query (Make sure you provide the actual Schema Name in which you want to clean up the content).


Query result example
 As this is a DDL script, the result is just an indication that the objects have been dropped
Query result interpretation
 As this is a DDL script, the result is just an indication that the objects have been dropped

  

Notes/comments