Skip to content

Instantly share code, notes, and snippets.

View mjbommar's full-sized avatar

Michael Bommarito mjbommar

151 followers · 26 following
View GitHub Profile
@mjbommar
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
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
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
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
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
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
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
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
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 = 'root@bommaritollc.com'
adminEmail = 'michael@bommaritollc.com'
@mjbommar
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