Oracle tablespaces can be set to auto-extend, but not to auto-contract. As a result, one can end up with a 10GB datafile that only has 10MB of stuff in it. Enterprise Manager Console and various other tools make shrinking a datafile a point-and-click operation, but remember what I said about tools? Here is how to look at your datafiles in SQL*Plus to see which ones are shrinkable:

SET LINESIZE 120
COLUMN file_name FORMAT a45
COLUMN percent_free FORMAT 99.00
CREATE VIEW free_space AS (
     SELECT SUM(bytes) free_bytes, tablespace_name
     FROM dba_free_space
     GROUP BY tablespace_name
);
SELECT d.tablespace_name,
           d.bytes overall_size,
           f.free_bytes/d.bytes*100 percent_free,
           d.file_name
     FROM free_space f, dba_data_files d
     WHERE f.tablespace_name = d.tablespace_name;
DROP VIEW free_space;

I’m creating a view and dropping it here for the simple reason that I’m too lazy to bother with subqueries right now and performing some quick and dirty DDL isn’t a big deal. Anyway, this will show you each tablespace, how big it is, and how much of it is being used. High % free + high byte count = tablespace that can be shrunk. I’m pretty sure this query will break if a tablespace uses more than one datafile, but as they say this is good enough for government work.

Once you have the path to the datafile you want to shrink, it’s just an ALTER DATABASE command to resize it:

ALTER DATABASE
     DATAFILE 'd:\oracle\oradata\dev1\mydatafile.ora' RESIZE 500M;

Oracle throws a nice error if you try to make it smaller than its contents.

I love tools. Anything that’s easy-to-use and streamlines the process is welcome. The problem is that many people rely so much on tools that they lose (or fail to learn) the skills that are required when those tools aren’t present. The most prevalent example that I’ve seen is SQL.

When I’m interviewing job candidates, I consider SQL skills to be fairly important, given all of the database work we do. So when I see SQL on a resume, I consider it fair game for technical questions. I believe if you put something on a resume, you better be damned ready to answer questions about it.

I start it out easily, giving them an opt-out: “Rate your SQL skills on a scale of 1 to 10.”

Just getting the number is interesting, because of the thought process involved. “What’s the highest number I can pick and not get called on this.” Anyone above a four gets the join question.

Now, I don’t even ask for the syntax of a join, because the ANSI standard is a pain the ass and I always forget where the (+) signs go in Oracle, but someone who claims to have SQL skills should be able to explain what a join is without stammering. Problem is, when I ask the join question, I very rarely like the answer I get.

“Well, I don’t usually write queries myself. We used TOAD at my last job.”

“Oh, uh, the last job I had we didn’t do a lot of SQL, because the system had a bunch of pre-built queries”

“We had this tool…”

Note that none of these responses actually answer the question, just provide a reason why the answer isn’t forthcoming. Most of the time, it’s because of tools. Tools give people the illusion that they understand a technology. After all, if the tool harnesses the technology, the tool is easy to use, and the tool gives them everything they need, that’s all there is, right? Wrong.

It’s great if you can use TOAD to find the column values you’re looking for, and if you can find them faster with TOAD, so much the better. It’s no substitute for being able to query the database yourself to get those column values. It’s an even better idea to stretch your abilities so that you can do things like joins and subqueries, to go poking around in the database figuring out how to do things, instead of poking around with TOAD’s preferences to see if you can make it stop croaking audibly when it launches.

When working on a new functional area, there is usually (hopefully?) a period of time during development where the functional specification is in a usable form, even if the software is not in testable state. The spec might have a high level description of the functionality to be implemented, and some sample screenshots of what the finished UI will look like, but it can be a bit of pain to weed through what a developer has written to find what will eventually become test cases, particularly if your methodology focuses on black box testing and the developer has included a lot of implementation details.

One of the methods I use to get a quick outline of potential test cases is the same method that SAT prep courses use for the reading comprehension section of the test. By quickly skimming through the text, looking for specific trigger words, one can quickly glean what’s needed. In the case of the SAT, the triggers are words like because and after. In the case of a functional spec, the trigger words are words like if, when, and, or, and otherwise. These are words that, not surprisingly, have a direct correlation to codepaths. Take a look at this short example:

If an object has been published in a library, it will appear when the user searches the database.

You find the if, you find yourself two test cases. One, that published objects appear in a search. Two, that unpublished objects do not. Here’s another one:

If an object has been published in a library, and the user has rights to search that particular library, it will appear when the user searches the database.

Now you have three test cases: the original two, and a third where the object is published, but the user does not have the rights to search that library.
It doesn’t matter what the mechanism is for searching or for publishing… those details can be filled in when those first usable builds are available.

Bullet points can also be useful - they often provide a list of testing prerequisites or verification points. Here’s an example:

When a user logs onto the system, they are greeted with a welcome page containing the following:

• The current date and time, with timezone
  
• A list of objects that have been published since last logon
• A list of tasks assigned to them
• An inbox with messages from other users

Now you have a list of verification points to check upon logon. Again, it doesn’t matter what the UI looks like… the details can be filled in later. But you’re building the skeleton of a test suite rather quickly without the software, and without even digging too deeply into the functional spec.