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 permissionin the specified schema.

Applicable CAST Version

Applicable RDBMS

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