Kill DB sessions the easy way with SQLcl

Seems I can not tweet these animated GIFs anymore. So this is another very short blog post to show real case for adding a new command into SQLcl which can be downloaded here.

It's hard annoying tedious to find SID and SESSION then alter to kill that when needed.  What if there was a simple kill command.

Here a sample one that takes in the 2 options:
  kill sid serial#
  Example :   kill 1 2
OR
  kill USERNAME
  Example: kill KLRICE

SQLcl and Query Change Notification

The database has had Query Change Notification for a while but to use it required a custom program. Such as Tim outlines on his blog https://oracle-base.com/articles/10g/dbms_change_notification_10gR2


Since SQLcl has Nashorn, now it can be integrated with a few lines of javascript to get notified when changes happen.  The script is below and posted.  The catch is QCN only works on Varchars and Numbers.

	var DatabaseChangeListener = Java.type("oracle.jdbc.dcn.DatabaseChangeListener");
	var Properties = Java.type("java.util.Properties");
	var OracleConnection = Java.type("oracle.jdbc.OracleConnection");
	var OracleStatement = Java.type("oracle.jdbc.OracleStatement");
	var cmd = {};
	cmd.onChange = function(e){
	// print it out but could go query the change instead
	ctx.write("\n Data Changed:" + e.toString() + "\n");
	out.flush();
	}
	var MyChangeListener = Java.extend(DatabaseChangeListener, {
	onDatabaseChangeNotification : cmd.onChange
	});
	var prop = new Properties();
	prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS,"true");
	prop.setProperty(OracleConnection.DCN_QUERY_CHANGE_NOTIFICATION,"true");
	var dcr = conn.registerDatabaseChangeNotification(prop);
	var changeListener = new MyChangeListener();
	dcr.addListener(changeListener);
	var stmt = conn.createStatement();
	// associate the statement with the registration:
	stmt.setDatabaseChangeRegistration(dcr);
	// build up the sql again
	var sql="";
	for(var i=1;i<args.length;i++){
	sql = sql + " " + args[i];
	}
	ctx.write(sql + "\n\n");
	var rs = stmt.executeQuery(sql);
	ctx.write("\nRegistered\n")
	var tableNames = dcr.getTables();
	for(var i=0;i<tableNames.length;i++)
	ctx.write(tableNames[i]+" is part of the registration.\n");
	stmt.close();

view raw monitor.js hosted with ❤ by GitHub