SQLcl Alias Example For Dropping Multiple Objects.

SQLcl allows you to define "named" scripts or statements using the ALIAS command. 
Jeff has a neat example on his blog.
I created the following alias to make it really easy to destroy a database! drop objects.

RM Alias

Dropping tables, views, procedures is a daily occurrence for database developers.

The following SQLcl ALIAS may come in handy when you want to drop lots of objects at the same time. Just specify the

  • object_type : TABLE, VIEW, PROCEDURE, ....
  • object_name: Using a  LIKE expression.  Example: MY% 
  • created: Number of days. Example: 1

A script is written with all the objects listed. The list of objects can be reviewed and an option to execute the script or cancel without dropping anything is provided.

Needless to say you can do a lot of damage dropping database objects and this makes it very easy to do so. So use at your own risk! Best to grab the alias code below and see if you can tweak it to suit yourself.

Test Env
create table app1_emp(col1 int);
create table app1_users(col1 int);
create table app1_books(col1 int);
create view app1_sales_view as select 1 as col1 from dual;
create procedure app1_proc_orders as begin null; end;
/

Examples

drop all the tables created in the last ~ 2 hours (0.1 of a day)
rm TABLE % 0.1

drop all tables starting with APP1_E created in the last day
rm TABLE APP1_E% 1

drop all views that end in SALES_VIEW created in the last 1000 days
rm VIEW %SALES_VIEW 1000

drop all objects that have E in their name created in the last ~ 2 hours
rm ALL %E% 0.1

 drop all the tables created in the last day
rm TABLE % 1




MOD Alias

I expanded on this idea to make a more generic alias.
The mod alias allows you to specify the what kind command is defined around the chosen objects.

Example
CTAS on a bunch of tables you created in the last hour?
No problem

mod TABLE % 0.1 'CTAS :object_name :object_name_new;\n/'


Note

The ALIAS rm automatically creates a script with the DROP statements called sqlcl_rm_script.sql.
The ALIAS mod automatically creates a script with the defined commands called sqlcl_mod_script.sql
Its reasonable to invoke the alias, then not run commands directly. Instead cancel and edit the sqlcl_rm_script.sql or sqlcl_mod_script.sql.

Install

alias load rm.xml
rm.xml 

alias load mod.xml
mod.xml