Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

Add Code w/ Syntax Highlighting to PowerPoint

Posted by Tyler Muth on February 6, 2013

…or any media that supports rich text or HTML. I often include code-snippets or queries in technical presentations. I find them much more visually appealing and easier to read if they include syntax highlighting. If you’re using Notepad++ on Windows, it’s trivial. Simply go to the “Plugins” menu > “NppExport” > “Copy all formats to clipboard”. Check out the screenshot below as well as an example slide from my talk last year at E4. On a related note, ZoomIt is a nice tool for presentations involving code or other things that are hard to read so you can zoom in on them.

Notepad++ Syntax Highlighting Export




Posted in Oracle | 8 Comments »

“Refresh” a CSV File

Posted by Tyler Muth on February 5, 2013

In a previous post I discussed a PDF reader that allows you to refresh the PDF after changes are made. This is the same concept except it’s for CSV files. I’ve been using Perl to convert sqlplus spool output to CSV which is much easier to read into R. As I’m updating my code and regenerating sample CSV files, it’s really annoying that Excel gets an exclusive lock on the file. So, I would open it in Notepad++ or vim, but it’s much harder in those tools to pick out the columns and rows to find errors.

CSVed to the rescue!!! Here’s the website for CSVed. It’s a great, simple CSV viewer / editor and it does not lock the file. You simply press the “reload” button or ctrl+r and it reloads the file. Again, so simple but so useful for my use-case.

Posted in Oracle, R | Tagged: , , , | 1 Comment »

“Refresh” a PDF

Posted by Tyler Muth on February 5, 2013

I’ve been working in R (wikipedia entry) a lot lately to graph performance data about Oracle systems. In general I output PDF files. The problem with most PDF readers (on Windows) is that they either get an exclusive lock on the file when it’s open (yes you, Adobe Acrobat), or you have to close and re-open the PDF to see any changes. Enter Sumatra PDF (wikipedia entry, home page). I’ve used on and off for a few years. It’s free, very lightweight & fast to start.

The key feature for this post is that when you open a PDF, it doesn’t get an exclusive lock on the file. So, when I re-run my R code to regenerate the PDF file, Sumatra changes the title bar to [Changes detected; refreshing]… and reloads the PDF when it no longer detects changes. Brilliant! Absolutely brilliant!

Here’s a screenshot:


Here’s a follow-up post on how to “Refresh a CSV File

Posted in R | Tagged: , , | 6 Comments »

Scripted Collection of OS Watcher Files on Exadata

Posted by Tyler Muth on November 2, 2012

I’ve been working a lot with graphing DB and OS metrics in R. I find it especially useful in Exadata POVs (proof of value) to gather and graph the oswatcher vmstat files for the compute nodes and iostat for the cells. For an example, take a look at this graph (PDF, 168 KB) of what happens when you have a connection pooling issue and keep creating connections throughout the test (14,930 to be precise). It’s a nice picture of what happened to the OS as it ran out of memory, started swapping, then fell over. To quote the Real World Performance Team: “When Databases swap, Databases stop”.

Anyway, back to the point of this post. OS Watcher files are automatically collected on each db node and storage cell. It’s not too bad to get them manually on a 1/4 rack as that’s only 5 components, but what about a full X3-2 with 22 components? Or the previous POV I did with 3 X2-8’s which had 42 storage cells and 6 DB nodes? Note that support will often ask for these files when you have an issue and log an SR.

Here’s a script I wrote to collect the files with various find options in the comments at the top. It also optionally takes in 1 parameter of a name to include in the tar.gz files so you can identify them easier. It will create 1 tar.gz file for each component, so in the case of a 1/4 rack it will create 5 files.

if [ $# -gt 0 ]

# A few find examples
# \( -name "*.*" \) \
# \( -name "*vmstat*.*" -o -name "*iostat*.*" \) \
# \( -name "*vmstat*.*" -o -name "*iostat*.*" \) -mmin -60 \
# \( -name "*vmstat*.*" -o -name "*iostat*.*" -o -name "*netstat*.*" \) -mtime -8 \

while read line; do
	(ssh -n -q root@$line 'find /opt/oracle.oswatcher/osw/archive  \
	\( -name "*vmstat*.*" -o -name "*iostat*.*" \) \
	-prune -print0 2>/dev/null | \
	xargs -0 tar --no-recursion -czf - 2>/dev/null ' | cat >  osw${file_name}_${line}.tar.gz
done < /home/oracle/tyler/osw/allnodes

Note the file “allnodes” on the last line is just a file with the names of the db nodes and cells, each on a new line. It’s the same format you use for dcli. This is of course a lot easier if you’ve setup root equivalencies for each component…

Posted in Exadata, Oracle | 10 Comments »

Speaking at Enkitec Extreme Exadata Expo

Posted by Tyler Muth on August 9, 2012

I’ll be speaking at the Enkitec Extreme Exadata Expo (E4), August 13-14 in Dallas Texas (you can also attend virtually). They’ve recruited some of the top names from community including keynote speaker Andrew Mendelsohn, Arup Nanda, Cary Millsap, Jonathan Lewis, Karen Morton, Maria Colgan, Kerry Osborne and Tanel Põder. I left a lot of names off the list, many of which you probably know so take a look at the full list of speakers here. Having implemented over 80 Exadata environments, the Enkitec speakers will clearly have a lot to offer as well.

Many of the speakers are not employed by Oracle so I expect the tone and perspective to be significantly different than an Oracle hosted event.

I realize I never published this draft until this morning and this is probably pretty late notice if you want to attend in person. However, you can attend virtually and the price is exceptionally low for that option…

Posted in Exadata, Oracle | 3 Comments »

Convert sqlplus spool output to CSV

Posted by Tyler Muth on April 11, 2012

I’ve been doing some work lately with sizing / capacity planning where the source are a few of Karl Arao’s excellent AWR Mining scripts. Had I been reading his blog more I would have known he was going down a parallel path using Tableau as I was moving in the direction of R.  Anyway, I wanted to get the output of these sqlplus scripts which spool to a file into CSV format to import into R as data frames. Yes, R can deal with fixed-width data, but not nearly as well as CSV. If it were just a few files, wrangling the data into CSV isn’t that hard. However, I had ~40 files and I plan on doing this a lot more so it had to be easy and fast. I looked at a couple of existing solutions such as the sqlplus colsep directive combined with sed or XMLAGG. But each had it’s drawbacks.

For me, perl seemed like the best fit as it’s easy, cross platform, and made for text files. I only used one module and it’s one that’s installed by default on most platforms. Anyway, what started as about 10 lines grew to about a 100 lines as I had to handle edge cases. I could be a lot less, but it’s intentionally verbose as I’ll trade compactness for readability every time. By default it automatically uses the column headings as the headings in the CSV output.

OK, enough rambling. Here’s the script on dropbox. I’ll post the whole script at the end of this post for viewing and for people that can’t get to dropbox hosted files. Here are my test case files used in the following examples.

If you don’t supply parameters it returns the usage instructions:

$ perl sqlplus_spool_to_csv.pl
usage: program [--infiles "file.txt" | "*.out"] [--headers [AUTO] | FILE | NONE] [--header_file header.txt] [--help|-?]

This will generate a csv file for every file like “a-*.out”:

$ perl sqlplus_spool_to_csv.pl --infiles "a-*.out"

$ ls *.csv
a-countries.out.csv  a-departments.out.csv  a-employees.out.csv  a-employees_repeat_headers.out.csv  a-locations.out.csv

$ head -7 a-countries.out
hr@orcl> select * from countries;

CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
AR Argentina                                         2
AU Australia                                         3
BE Belgium                                           1

$ head -4 a-countries.out.csv

This example will glue on the headers from a custom text file:

$ perl sqlplus_spool_to_csv.pl --infiles "b-*.out" --headers FILE --header_file employees_headers.txt

On my laptop it only took 25 seconds to convert a 100M file to CSV (a directory of 40 smaller files took under 5 seconds). Here’s the 100M filecompressed to 17K in case you want it.:

$ ls -lh employees_big.out
-rw-r--r--+ 1 tmuth None 100M Apr 11 15:31 employees_big.out

$ wc -l employees_big.out
556200 employees_big.out

$ time perl sqlplus_spool_to_csv.pl --infiles employees_big.out

real    0m25.047s
user    0m24.289s
sys     0m0.467s

OK, here’s the perl source in case you just want to take a peak or can’t download from dropbox:

#! /usr/bin/perl
# Author: Tyler D Muth - https://tylermuth.wordpress.com
# Version: 1.1

use warnings;
use strict;
use Getopt::Long;
my (@infiles, $headers, $header_file);
usage() if ( @ARGV < 1 or           ! GetOptions('infiles=s@' => \@infiles, 'headers=s' => \$headers, 'header_file=s' => \$header_file));

$headers = defined($headers) ? $headers : 'AUTO'; $headers=uc($headers);


sub usage
  print "Unknown option: @_\n" if ( @_ );
  print qq/usage: program [--infiles "file.txt" | "*.out"] [--headers [AUTO] | FILE | NONE] [--header_file header.txt] [--help|-?]\n/;

sub file_loop{
	for my $in_file ( map {glob($_)} @infiles ) {
		print $in_file, "\n";
		my $unpack_layout =''; my $header_titles=''; my $headers_mapped='';my $dashes_line='';
		my $header_line_new='';
		open ( INFILE, "$in_file.csv" ) or die "$!";
		chomp $header_line_new;

		if($headers eq 'AUTO'){
		print OUTFILE "$headers_mapped\n" if $headers ne "NONE";
		while( my $line =  ){
			chomp $line;
			last if ($line =~ /^[[:digit:]]+[[:space:]]rows[[:space:]]selected.*/);	# this will be an issue for non-english sqlplus
			if($line ne $dashes_line && $line ne $header_line_new && length($line) > 2){
				print OUTFILE text_line_to_csv($line ,$unpack_layout) . "\n";
		close INFILE;
		close ( OUTFILE );

sub get_unpack_layout{
	my $infile_handle=$_[0];my $outfile_handle=$_[1];my $unpack_layout_int='';
	my $header_line='';
	while( my $line =  ){
		if ($line =~ /-{3,}.*/) { #if the line starts with 3 or more dashes (may cause problems for < 3 character leading columns
			chomp $line;
			$_[3]=$line; #return the line of dashes so we can skip them if repeated
			my @columns = split(' ', $line);
			foreach my $col (@columns) {
				my $col_length = length($col)+1;
				$unpack_layout_int .= "A$col_length ";
sub get_header_titles{
	my $header_titles_in=$_[0]; my $header_titles_return='';my $dummy_line='';
	$header_titles_return=$header_titles_in if $headers eq 'AUTO';
	if($headers eq 'FILE'){
		open ( HEADERS_FILE, "		$header_titles_return = ;
		close ( HEADERS_FILE);
	chomp $header_titles_return; $_[1]=$header_titles_return;
sub text_line_to_csv{
	my $in_line=$_[0]; my $in_unpack_layout=$_[1];

	my @f = unpack("$in_unpack_layout", $in_line);
	my $out_text = join ',', map {
		s/^\s+//;  		 # strip leading spaces
			  s/\s+$//;  # strip trailing spaces
			  s/#+/ /g;  # replace any pound signs (#) in the line
		qq/"$_"/ } @f;

	print "$out_text \n" if $out_text =~ m/\#+/g;
	return $out_text;

Posted in Oracle | Tagged: , , | 14 Comments »

Create Bigfile Tablespace – Oracle Managed Files (OMF)

Posted by Tyler Muth on February 10, 2012

Working with Exadata a lot (almost exclusively) I create almost all my tablespaces as bigfile using OMF. For some reason I can never find the right syntax for this when I need it. So, that’s the main reason for this post.


On a related note, wow I use “column mode” in Notepad++ (link) a lot for this stuff. Many other editors support column mode, including an old favorite on Windows, UltraEdit (link).


Posted in Oracle | 4 Comments »

I Need More Hardware!

Posted by Tyler Muth on February 8, 2012

Actually, this is one of the few times I can remember in my career when I can honestly say I have enough horsepower at my disposal.

Want to guess what I’m running right now?

> select count(*) from v$asm_disk where name like 'DATA_XXX%';


> select sum(total_mb)/1024/1024 tb from v$asm_disk;


> select count(*) from gv$instance;

[oracle@somehost: ~]$ cat /proc/meminfo | grep MemTotal
MemTotal:       1058429820 kB

[oracle@somehost: ~]$ cat /proc/cpuinfo | grep -m 1 Xeon
model name      : Intel(R) Xeon(R) CPU           X7560  @ 2.27GHz

[oracle@somehost: ~]$ cat /proc/cpuinfo | grep -c Xeon

[root@somehost ~]# find /sys/class/infiniband/ -name 'rate' -exec cat {} \;
40 Gb/sec (4X QDR)
40 Gb/sec (4X QDR)
40 Gb/sec (4X QDR)
40 Gb/sec (4X QDR)
40 Gb/sec (4X QDR)
40 Gb/sec (4X QDR)
40 Gb/sec (4X QDR)
40 Gb/sec (4X QDR)

This POV will probably generate a number of follow-up posts. One concept: there are always bottlenecks. When you remove the hardware bottlenecks, bottlenecks in your code quickly surface.

Posted in Oracle | 8 Comments »

Oracle Database 10.2 De-Supported

Posted by Tyler Muth on January 11, 2012

OK, that’s an alarmist title and is not really true. However, I feel this topic is important enough to warrant the title. I’ve talked to a lot of people lately that were not aware of the actual support status of Database 10.2 and below (it changed on July 2010 and the 1 year grace period ended July 2011). My goal of this post is certainly not to be critical of anyone, just to spread the word and allow people to make informed decisions.

The document “Oracle Lifetime Support Policy” (PDF, 474KB) discusses most of the details. There’s also a FAQ on MOS entitled “Oracle Database 10.2 End of Premier Support – Frequently Asked Questions [ID 1130327.1]” (HTML Version or Full Flash Version (for now…(details on that here)).

Here’s my quick summary of some key points:

  • 10.2 general availability was July 2005
  • “Premiere Support” is what you probably think of as “supported”.
    • It extends for 5 years from the date of general availability.
    • It ended for 10.2 in July of 2010.
    • Oracle offered a 1 year, no-cost grace period for 10.2 premiere support customers which ended in July of 2011. This was basically a year of free Extended Support.
  • “Extended Support” is a for-cost option that allows you to extend Premiere Support for an additional 3 years.
  • “Sustaining Support” is the level of support after Premiere Support ends, assuming you are not paying for Extended Support.
    • This is probably where a large majority of the 10.2 and earlier customers are today, but may not be aware of this.
    • This does NOT include a number of key services such as “New updates, fixes, security alerts, data fixes, and critical patch updates”.

A number of customers that I’ve talked to realized this situation when they went to download the latest Critical Patch Update and could not as they were on Sustaining Support.

The PDF also contains the following chart which is a nice overview of the dates for planning purposes:

Database Support Dates Chart

Posted in Oracle | Tagged: | 10 Comments »

Logger Project Moved Temporarilly

Posted by Tyler Muth on November 9, 2011

The site samplecode.oracle.com was decommissioned recently. I was hosting a number of projects there including “Logger”, my PL/SQL instrumentation package. Until I find a new home, here’s a temporary link to the latest release (1.4) or if you just want to view the readme use this link. I’ll update this post when I decide on a new home.

This project is still very much alive and I will continue to update it based on your suggestions. I know there are a number of them in my “queue”, I just haven’t had time. My plan is to find a public place to host it such as github where everyone can contribute.

Posted in APEX, Oracle, PLSQL | 26 Comments »