ORDS Remove entire schema info

Business Case

While deploying more and more ORDS based solutions, we need to be able to clean ORDS completely for a certain schema.

Solution

We created our own procedure for this cleanup.  While it works in our cases, maybe it is not complete for your environment.  Testing is always crucial.

Follow the following steps to create the procedure:

1. Login with sys of your database or pluggable db
2. Execute the following script:

create or replace procedure ords_metadata.remove_schema_entries
authid definer
is
    v_user constant varchar2(200) := SYS_CONTEXT('USERENV', 'SESSION_USER');
begin
    dbms_output.put_line('start remove schema entries for '||v_user);
    delete from ords_handlers where created_by = v_user;
    delete from ords_modules where created_by = v_user;
    delete from ords_objects where created_by = v_user;
    delete from ords_parameters where created_by = v_user;
    delete from ords_templates where created_by = v_user;
    delete from ords_url_mappings where created_by = v_user;
    delete from sec_authenticators where created_by = v_user;
    delete from sec_keys where created_by = v_user;
    delete from sec_origins_allowed_modules where created_by = v_user;
    delete from sec_privilege_auths where created_by = v_user;
    delete from sec_privilege_mappings where created_by = v_user;
    delete from sec_privilege_modules where created_by = v_user;
    delete from sec_privilege_roles where created_by = v_user;
    delete from sec_privileges where created_by = v_user;
    delete from sec_role_mappings where created_by = v_user;
    delete from sec_roles where created_by = v_user;
    delete from ords_schemas where created_by = v_user;
    commit;
    dbms_output.put_line('end remove schema entries');
end remove_schema_entries;
/
grant execute on ords_metadata.remove_schema_entries to public; 

create public synonym ords_remove_schema_entries for ords_metadata.remove_schema_entries;

To use this procedure, execute it with the following syntax:

set serveroutput on

begin
      ords_remove_schema_entries;
end;
/

This solution has been developed with ORDS 18.3.