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 | |
8.2.x |
Applicable RDBMS
RDBMS | Yes/No |
---|---|
Oracle Server | |
Microsoft SQL Server | |
CSS |
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.)
- Connect to the Oracle server.
- 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