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.