01 October 2008

GNU basename in PL/SQL

Reposted from The Pythian Group blog.

In the process of scripting a database migration, I was in need of something akin to the GNU basename utility that I know and love on Linux. basename is most famous for taking a full file path string and stripping away the leading path component, returning just the name of the file. This can be emulated in PL/SQL with calls to SUBSTR and INSTR, like this:


(Thanks to Ian Cary, who shared this logic on oracle-l)

As you can see, this simply finds the last occurence of /, which is our directory separator on *nix and Solaris operating systems. On Windows, it would be \. It then returns a substring beginning one character after that last separator until the end of the string. Voila, a basic basename routine!

Upon reading the basename man page again, I found that basename also takes an optional parameter, a suffix string. If this suffix string is provided, basename will also truncate that string from the end. For example:

$ basename /home/seiler/bookmarks.html
$ basename /home/seiler/bookmarks.html .html

I decided that this would be handy to have, and set out to create a compatible basename function in PL/SQL. Here is what I came up with:

v_separator IN CHAR DEFAULT '/')
v_basename VARCHAR2(256);
v_basename := SUBSTR(v_full_path, INSTR(v_full_path,v_separator,-1)+1);
v_basename := SUBSTR(v_basename, 1, INSTR(v_basename, v_suffix, -1)-1);

RETURN v_basename;

I’ve also added an optional third parameter to specify a directory separator other than the default. It would probably be rarely useful, but not hard to remove if you don’t like it. As you can see, I’ve used similar SUBSTR/INSTR logic to identify the suffix index and prune it out.

Here it is in action:

SQL> COLUMN file_name FORMAT a45;
SQL> COLUMN basename FORMAT a15;
SQL> COLUMN no_suffix FORMAT a12;
SQL> SELECT file_name
2 , basename(file_name) as basename
3 , basename(file_name, '.dbf') as no_suffix
4 FROM dba_data_files;

FILE_NAME                                     BASENAME        NO_SUFFIX
--------------------------------------------- --------------- ------------
/u01/app/oracle/oradata/orcl/users01.dbf      users01.dbf     users01
/u01/app/oracle/oradata/orcl/sysaux01.dbf     sysaux01.dbf    sysaux01
/u01/app/oracle/oradata/orcl/undotbs01.dbf    undotbs01.dbf   undotbs01
/u01/app/oracle/oradata/orcl/system01.dbf     system01.dbf    system01
/u01/app/oracle/oradata/orcl/example01.dbf    example01.dbf   example01

I hope this makes your work just a little bit easier, as it has mine.

Does Oracle’s Block Change Tracking File Shrink?

Reposted from The Pythian Group blog.

Just a quick post to get myself back into blogging mode. Recently in IRC (#oracle on freenode, to be precise), a fresh face asked if the Block Change Tracking file ever shrinks. She had been worrying about the file in her instance continuing to grow. A number of us speculated (non-BAAG!) that perhaps taking an RMAN backup would somehow purge the file of what it was keeping track of, and then the magical Oracle fairies would promptly resize it for us. Needless to say, I was hesitant to take this theory forward with Alex Gorbachev aware of my home address.

After setting up Oracle on a nice VirtualBox image (more on that in another post) running CentOS 5, I began to do some reading. For some reason, actually reading the official tahiti docs was last on my list. A search of the 10gR2 docs quickly yielded this (from RMAN Incremental Backups): Estimating Size of the Change Tracking File on Disk

The size of the change tracking file is proportional to the size of the database and the number of enabled threads of redo. The size is not related to the frequency of updates to the database. Typically, the space required for block change tracking is approximately 1/30,000 the size of the data blocks to be tracked. Note, however, the following two factors that may cause the file to be larger than this estimate suggests:
  • To avoid overhead of allocating space as your database grows, the change tracking file size starts at 10MB, and new space is allocated in 10MB incremenents [sic]. Thus, for any database up to approximately 300GB the file size is no smaller than 10MB, for up to approximately 600GB the file size is no smaller than 20MB, and so on.
  • For each datafile, a minimum of 320K of space is allocated in the change tracking file, regardless of the size of the file. Thus, if you have a large number of relatively small datafiles, the change tracking file is larger than for databases with a smaller number of larger datafiles containing the same data.
So (if the docs are to be trusted), it would seem that whether or not a backup is taken has no effect on the size of the file, or at least wouldn’t cause it to be shrunk. The size is tied to the amount of data in the database itself, not necessarily the changes in the database waiting to be included in the next incremental RMAN backup.

The documentation does suggest, however, that file size might be affected if (for example) a tablespace and its datafiles were dropped from the database. I’ll save this test for another day!