mjbommar’s gists

mjbommar / kill_sessions_by_module_machine.sql

Created June 15, 2013 12:59

Generate a script to kill (IMMEDIATE) all sqlplus sessions from a given host.

	SELECT 'ALTER SYSTEM KILL SESSION ''' \|\| sid \|\| ',' \|\| serial# \|\| ',@' \|\| inst_id \|\| ''' IMMEDIATE;'
	FROM gv$session
	WHERE module = 'SQL*Plus'
	AND machine = 'host.company.com';

mjbommar / recreate_em_repos.sh

Created June 14, 2013 15:51

Recreate repositories for Oracle Enterprise Manager (OEM) via the Enterprise Manager Configuration Assistant.

	# Set parameters; these should be dynamically supplied by provisioning, with the possible exception of the two $_HOME paths.
	DB_SID="ORCL"
	LISTENER_PORT="1521"
	ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1"
	LISTENER_HOME="/u01/app/oracle/product/11.2.0/dbhome_1"
	SYS_PASSWORD="tiger"
	DBSNMP_PASSWORD="tiger"
	SYSMAN_PASSWORD="tiger"

	# Run emca with parameters

mjbommar / get_table_row_counts_by_field_name.sql

Created June 14, 2013 12:35

For an Oracle database, select the (possibly stale) table row counts from all_tab_statistics for all tables with a given field name and owner. This example is based on the Oracle Transportation Management (OTM) schema and shows all table row counts for tables with a domain_name field.

	SELECT table_name, num_rows, last_analyzed
	FROM all_tab_statistics
	WHERE table_name IN (SELECT table_name
	FROM all_tab_cols
	WHERE column_name = 'DOMAIN_NAME'
	AND owner IN ('GLOGOWNER'))
	ORDER BY num_rows DESC;

mjbommar / plot_asm_average_operation_size.R

Last active December 18, 2015 09:59

Plot the average KB per read or write by Oracle ASM disk using ggplot2 and RJDBC: http://bommaritollc.com/2013/06/12/plotting-average-read-and-write-operation-size-by-asm-disk

	# Java and JDBC path configuration
	jvmHome <- 'C:\\Program Files (x86)\\Java\\jre7\\'
	ojdbcPath <- 'lib/ojdbc.jar'

	# Load rJava library
	Sys.setenv(JAVA_HOME=jvmHome)
	options(java.parameters="-Xmx1g")
	library(rJava)

	# Output Java version

mjbommar / asm_average_operation_size.sql

Created June 12, 2013 13:38

Calculate the average KB per read or write for an Oracle ASM disk: http://bommaritollc.com/2013/06/12/plotting-average-read-and-write-operation-size-by-asm-disk

	SELECT path, label, inst_id,
	(bytes_read / DECODE(reads, 0, 1, reads)) / 1024. AS kb_per_read,
	(bytes_written / DECODE(writes, 0, 1, writes)) / 1024. AS kb_per_write
	FROM gv$asm_disk_stat
	ORDER BY path, inst_id;

mjbommar / plot_rman_backup_duration.R

Last active December 18, 2015 01:19

Plot the backup duration for RMAN jobs using R, ggplot2, rJava, RJDBC, and the Oracle JDBC thin client.

	# Load rJava library
	Sys.setenv(JAVA_HOME='C:\\Program Files (x86)\\Java\\jre7\\')
	options(java.parameters="-Xmx1g")
	library(rJava)

	# Output Java version
	.jinit()
	print(.jcall("java/lang/System", "S", "getProperty", "java.version"))

	# Load RJDBC library

mjbommar / clean_orphaned_snapshots.py

Created March 2, 2013 13:10

Cleans orphaned snapshots from an EC2 account. Requires boto config.

	'''
	@author Bommarito Consulting, LLC
	@date 20120622

	Identify and, if requested, remove orphaned snapshots from an EC2 account.
	'''


	# Imports
	import boto

mjbommar / generate_aws_ssh_config.py

Last active April 20, 2020 13:16

Generate .ssh/config lines from EC2 instance information using boto.

	'''
	@author Bommarito Consulting, LLC
	@date 2012-12-23

	Generate .ssh/config lines from EC2 instance information.
	'''

	# Imports
	import boto
	import os

mjbommar / check-mysql.py

Created December 20, 2012 18:00

This simple python script checks to see if MySQL is alive based on mysqladmin output. Tested on 5.5.28-0ubuntu0.12.04.3 under ubuntu user. (c) Bommarito Consulting, LLC; http://bommaritollc.com You might want to run in cron every minute: $ EDITOR=emacs crontab -e */1 * * * * /path/to/check-mysql.py

	#!/usr/bin/python
	#@author Bommarito Consulting, LLC; http://bommaritollc.com
	#@date 2012-12-20

	import subprocess
	import smtplib
	from email.mime.text import MIMEText

	fromAddress = '[email protected]'
	adminEmail = '[email protected]'

mjbommar / r_oracle_jdbc_example1.R

Created November 22, 2012 15:36

Example of connecting to an Oracle database using R and RJDBC

	# Set JAVA_HOME, set max. memory, and load rJava library
	Sys.setenv(JAVA_HOME='/path/to/java_home')
	options(java.parameters="-Xmx2g")
	library(rJava)

	# Output Java version
	.jinit()
	print(.jcall("java/lang/System", "S", "getProperty", "java.version"))

	# Load RJDBC library

Michael Bommarito mjbommar