windows, sysinternals, processes and disk space

If you work on both unix and windows, you may find yourself wishing for unix tools like du, ps, df, proc and fuser to use on windows systems. Such tools make it easier to investigate process activity and disk utilization.

Sysinternals and vbscript to the rescue!

Sysinternals is the website run by Mark Russinovich, now at Microsoft. The utility process explorer gives you everything ps does and more! With process explorer, you can quickly find if a particular process is locking a file, for example. There’s a windows version of du that works really well. With du, you can find out what directory is consuming space very quickly. And there’s many other tools that I have yet to explore.

Lastly, the unix utility df makes it easy to see space consumed by each mountpoint from the command line. In windows, you can do this via the GUI by going to My Computer and checking there. But there’s no easy way to do this from the command line with the built-in operating system commands. Here’s a vscript file to help. To run it, place the code in a file, say get_drive_utilization.vbs. Then invoke cscript from the command prompt like this:
C:\> cscript get_drive_utilization.vbs

' Windows scripting host script in VB Script
' to list Used, Free, Total and % Free on all fixed disk drives.

Function ShowDriveList

	Dim fso, d, dc, s, n
	Set fso = CreateObject("Scripting.FileSystemObject")
	Set dc = fso.Drives

	s = s & "DriveLetter | ShareName | DriveType | Used | Free | Total | % Free " & Chr(10) 

	For Each d in dc
		n = ""

		If d.DriveType = 3 Then
			n = d.ShareName
		ElseIf d.IsReady Then
			n = d.VolumeName
		Else
			n = "[Drive not ready]"
		End If

		Select Case d.DriveType
			Case 0: t = "Unknown"
			Case 1: t = "Removable"
			Case 2: t = "Fixed"
			Case 3: t = "Network"
			Case 4: t = "CD-ROM"
			Case 5: t = "RAM Disk"
		End Select

		If t = "Fixed" Then
			s = s & d.DriveLetter & " | "
			free_space = FormatNumber(d.AvailableSpace/(1024*1024*1024), 2)
			total_space = FormatNumber(d.totalsize/(1024*1024*1024), 2)
			used_space = formatnumber(((d.totalsize - d.availablespace)/(1024*1024*1024)), 2)
			pct_free = formatnumber(free_space / total_space, 2)
'			s = s & n & " | " & t & " | U: " & used_space & " GB | F: " & free_space & _
'				" GB | T: " & total_space & " GB | %Free: " & pct_free & Chr(10)
			s = s & n & " | " & t & " | " & used_space & " GB | " & free_space & _
				" GB | " & total_space & " GB | " & pct_free & Chr(10) 

		End If

	Next

	ShowDriveList = s

End Function

a = showdrivelist
wscript.stdout.write a

Flashback – Wow that was easy

I got a call from a developer today with a problem. Seems an end user had inadvertently updated records incorrectly using an admin screen that facilitates bulk updates. Could I make available the data in a particular table as it was 45 minutes ago? This was Oracle 9i.

Flashback query to the rescue! After reviewing the flashback feature, I was able to do the following:
1. Create a dummy user in the target database.
2. Run exp using the flashback_time parameter to export data from about 45 minutes ago.
3. Ran imp using the newly created export dump and targeted the new user schema.
4. Ran a grant statement so that the developer could do selects on both the real table and the dummy table with old data in order to do comparisons.

Easy! Kinda like a Rachel Ray recipe…

Alter that session for a work around!

This is ugly, but sometimes you need to get code working after a migration from 9i to 10g that somehow got overlooked or where you have run out of time to tune sql or statistics.

In the past 4 years, I’ve seen this deployed twice to fix small issues that went unnoticed during testing.

In one case, a query that had been tuned for the 9i optimizer did not work well with the 10g optimizer enabled. The rest of the queries did work fine. In another case, a seldom used delete trigger included a query against v$session that began using parallel query with the 10g optimizer. As a result, the select into clause of the query failed when the select into began fetching multiple rows.

The ugly workaround to get things working fast? Revert the optimizer only within the poorly performing block of code.

alter session set optimizer_features_enable=”9.2.0″

Use execute immediate for PL/SQL code.
execute immediate ‘alter session set optimizer_features_enable=”9.2.0″ ‘;

If you want to be extra careful, you can query the value of optimizer_features_enable, store it in a variable, and then run alter session again to reset the value back to the original setting immediately after the problem code completes.

Note that this is a temporary solution, and it would be better to identify the root cause of the problem and get things to work with 10g.

Automate Outlook Appointments w./ MS-Access

Do you keep birthdays in an MS Access database? Or any other database, for that matter? Have you wanted to get them into recurring Outlook appointments so you could remind yourself of the birthdays? Were you too lazy to enter them manually?

Here’s VBA code to automate a one-time addition of them into Outlook.

A few notes:
1. You’ll need to be familiar with the VBA programming environment. If you are in Outlook, alt-F11 gets you there fast.

2. Outlook VBA projects appear to be stored in a single file. Makes sense because Outlook does not fit the document based approach of Excel and Word. However, you can import and export projects, and I’m sure there is a way to control the location of the Outlook project if you really want to.

3. Be sure to have the DAO library references on in order for this to work. A default Outlook VBA project won’t have these turned on. You do this in the VBA IDE by choosing Tools – References and searching for the DAO library and checking it off.

Here’s the code.

Sub add_bdays()
Dim the_sql As String
Dim myDB
Dim ol
Dim myItem
Dim body_text As String

' Here's my SQL statement. Yours will vary, of course.
' Note the format command needed to order dates within the year.
the_sql = "SELECT Format([DOB],""mm/dd"") AS month_day, MainList.DOB, MainList.DOD, MainList.LName, MainList.FName, MainList.Street1, MainList.Street2, MainList.City, MainList.State, MainList.Zip, MainList.Country, MainList.HPhone, MainList.EMail "
the_sql = the_sql & "FROM MainList "
the_sql = the_sql & "WHERE (((MainList.DOB) Is Not Null)) "
the_sql = the_sql & "ORDER BY Format([DOB],""mm/dd"")"

Set ol = CreateObject("Outlook.Application")
Set myDB = DBEngine.OpenDatabase("D:\path\to_your\database.mdb")

Set rs = myDB.OpenRecordset(the_sql)
rs.MoveFirst

Do Until rs.EOF
Set myItem = ol.CreateItem(olAppointmentItem)
myItem.Subject = "BIRTHDAY: " & rs.fname & " " & rs.lname
myItem.Duration = 1
myItem.Start = CDate(rs.month_day & "/09 9:00:00 AM")
myItem.Location = "Ben's desk"
body_text = myItem.Subject

body_text = body_text & Chr(13) & rs.Street1
body_text = body_text & Chr(13) & rs.Street2
body_text = body_text & Chr(13) & rs.City & ", " & rs.State & " " & rs.Zip
body_text = body_text & Chr(13) & rs.Country
body_text = body_text & Chr(13) & rs.HPhone
body_text = body_text & Chr(13) & rs.Email

myItem.Body = body_text
' Code for setting appointment recurrence.
Set objrecurrence = myItem.GetRecurrencePattern
objrecurrence.RecurrenceType = olRecursYearly

' I found I had to specify when the recurrence start date was. Otherwise the start date got entered incorrectly.

objrecurrence.PatternStartDate = CDate(rs.month_day & "/09")
objrecurrence.PatternEndDate = #12/31/2015#

myItem.ReminderMinutesBeforeStart = 2880
myItem.ReminderSet = True

myItem.Save

Set objrecurrence = Nothing
Set myItem = Nothing
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set myItem = Nothing

End Sub

SSIS Cheatsheet

I infrequently use SSIS, but once in awhile I need to edit a package. This is harder than it was with SQL Server 2000, in which one could crank up Enterprise Manager, go to the target instance, drill down to a DTS package and start editing away, with changes saved directly to the database.

Here’s my newly created cheat sheet:

=================================================================
DISCLAIMER
=================================================================
SSIS has lots of functionality and this is just a very bare bones description of opening an existing package. Note that this does not discuss deploying multiple packages all at once (deployment wizard) or details on editing and debugging SSIS packages. Nor does it weigh the merits of
storing SSIS packages on the file system vs in the database vs SSIS store.

=================================================================
BIG PICTURE
=================================================================
SSIS Packages are eventually get stored and executed within SQL Server. They can be stored on the file system or within the SQL Server database, typically in MSDB.

SSIS Packages get edited in Visual Studio. To do that, you must get a copy of the SSIS package on to the file system. Several ways to do that. From within Visual Studio, you can create a new SSIS package, or you can choose Project – Add existing package and get one directly from SQL Server or a file system location. Or you can go to SQL Server Management Studio and export there first and then go into Visual Studio.

When done editing in Visual Studio, one must deploy to SQL Server. There are a few ways to do that. One can use the deployment wizard, or import the package using SQL Server Management Studio’s import package, or use the command line dtutil tool. There is also a dtexec for commandline execution of SSIS packages.

Once deployed, SSIS packages can then be run in SQL Server via jobs managed by SQL Server Agent.

===============================================================
SIMPLE WAY TO EDIT EXISTING PACKAGE
===============================================================
Step 1: Start Visual Studio and Make a New Project
File – New Project
Integration Services Project
Use Location to create directory structure meaningful for your project and team’s coding conventions.

Step 2: Add an Existing Package to the Project
Project – Add and Existing Package
In the Add Copy of Existing Package dialog:
Package Location is SQL Server
Server (local) if you are logged on to the SQL Server machine. If working from laptop, identify the SQL Server instance and include port if necessary
(e.g. USEOMAPD357,1113)
Authentication: Windows
Package Path: Click on . and locate package.
Then click OK.
Nothing will change on screen, but a file will be on the file system containing the package which you can open in the next step.

Step 3: Open the package for editing.
File – Open
locate the newly exported package.

Step 4: Edit the package.
Use Visual Studio as needed to edit.
You can test the package as needed.
You will find the SSIS programming metaphor is roughly similar to DTS and other Microsoft visual editing tools.
Save changes to package and they will update the file on the operating system.

Step 5: Deploy the package
Start SQL Server Management Studio.
Connect to Integration Services
Click on Stored Packages
Click on MSDB
Right Click and choose Import Package.
Package Location will be File System
Use Package Path to identify the file you edited in Step 4 above.
Click on Package Name and provide a name or accept the existing one.
Make protection level Encrypt Sensitive Data with User Key.

Suffering from Enlarged Oracle Homes?

Are you a diligent DBA who applies quarterly CPU patches whenever possible? Are you noticing how the $ORACLE_HOME directory gets larger and larger over time? That’s ’cause the opatch utility creates backup files needed for rolling back patches in a directory called $ORACLE_HOME/.patch_storage. Opatch will not eliminate old files here when you apply patches. However, starting in Oracle 10.2.0.x and higher, there is now a switch for opatch: opatch util cleanup. This will remove backup files from older patches that are no longer needed. Search on metalink for more details. This cleared up 2GB for me yesterday!

Application Express

I had time for fiddling around with Oracle Application Express (APEX) and could justify it as part of Oracle 11g product evaluation. Some notes follow.

When you use the database creation assistant with 11g, it generates a script called apex.sql, which in turn executes $ORACLE_HOME/apex/catapx.sql. That seemed to work OK, except there is a fair amount of configuration afterward. And Oracle Text really needs to be installed in order for apex help files to display correctly.

So here’s my list of stuff to do if you want to install Application Express.
1. Install Oracle Text first. $ORACLE_HOME/ctx/admin/catctx.sql. Check that all objects in dba_objects are valid and that components in DBA_REGISTRY are valid.
2. Run $ORACLE_HOME/apex/apexins.sql. Review the log file and make sure the entry in DBA_REGISTRY is valid. For older versions, you may need to run $ORACLE_HOME/spex/catapex.sql.
3. Review and then execute the steps in the Oracle Application Express documentation. You want the Application Express Installation Guide. It explains quite a bit, including how you can run either the Oracle http web server (Apache) or the web server that’s inside the database called embedded PL/SQL gateway. Pick which web server you want and follow the appropriate instructions. Be sure to remember the password you supply when running $ORACLE_HOME/apex/apxconf.sql. You’ll need it to do admin work with APEX.
4. Something not mentioned anywhere that I saw was the listener.ora change you need to ensure that embedded pl/sql gateway works. Adding this in the DESCRIPTION_LIST worked for me:
(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Thanks Tim Hall of www.oracle-base.com for pointing this out on your web site!
5. At point I was able to log in using http://[hostname]:8080/apex/apex_admin, using user ADMIN and the password supplied in step 3 above!

Transactional DDL

I found this thread on comp.databases.oracle.server interesting.

It’s a discussion of whether or not Oracle should support the ability to rollback a DDL statement. I never considered this to be a possibility or even something that I’d need. But I like the comparison amongst database products from different vendors. Such comparisons can help one understand better the general problems that RDBMS tools solve. One thing that came up in the thread was whether SQL Server supports transactional DDL. I had to verify for myself, and sure enough it does. Here’s a sample sqlcmd session:

1> begin transaction
2> create table foo (f1 varchar(10))
3> go
1> rollback
2> go
1> select * from foo
2> go
Msg 208, Level 16, State 1, Server USEOMAPD357, Line 1
Invalid object name ‘foo’.
1> begin transaction
2> create table foo (f1 varchar(10))
3> go
1> insert into foo values (‘harry’)
2> go

(1 rows affected)
1> select * from foo
2> go
f1
———-
harry

(1 rows affected)
1> rollback
2> go
1> select * from foo
2> go
Msg 208, Level 16, State 1, Server USEOMAPD357, Line 1
Invalid object name ‘foo’.
1> exit

Oracle VM Server, Interrupted

I’ve been wanting to get a virtual server running at home. But which vm server? I have two desktops at present. One is the general work machine for my wife and me and is running Windows Server 2003. I have a GB of RAM on this and can do SQL Server boondoggles when needed. My other machine now has 4GB.

I decided to try out Oracle VM Server. After looking around a bit, I learned that Oracle VM Server has two parts, the VM Server and the VM Manager. They need to get installed on separate machines. Apparently the VM Manager provides a web based GUI with which one can fiddle around with VMs running on the server. A little more digging and I found documentation which appeared to indicate that you could install a VM from the command line directly on the VM Server. Good for me, cause I don’t have a machine at home just now on which top VM Manager.

So…I installed Oracle VM Server. That went fine more or less, after creating a static ip for it. I downloaded a pre-packaged Oracle VM template with 11g on it from Oracle.com. I started unzipping it and….OUT OF DISK SPACE. My 40 GB drive was not sufficient for the unzipped VM. So I’ll need to purchase a larger drive for this machine before proceeding further.

Assessing Critical Patch Updates

Oracle releases a Critical Patch Update (CPU). How fast should you mobilize to apply it?

Don’t panic. Use the information available on Oracle’s web site to determine the degree to which your environment is affected.

4 times a year, Oracle releases a CPU. Here’s what the current notification looks like:

http://www.oracle.com/technology/deploy/security/critical-patch-updates/cpuoct2008.html

When reviewing the notification, take a look at the risk matrices. Here’s the one for database:

http://www.oracle.com/technology/deploy/security/critical-patch-updates/cpuoct2008.html#AppendixA

This matrix is using an industry standard security risk assessment rating system.

You will find that things are itemized according to products. If you are not using the products mentioned, your risk is lowered. Also, the matrix itemizes the significance of each vulnerability. The columns in the matrix are fully explained here:

http://www.first.org/cvss/cvss-guide.html

By analyzing the findings, you can make an informed choice about what threat the particular CPU poses to your Oracle environment.