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:
substr(dirname,instr(dirname,'/',-1)+1)
(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 bookmarks.html $ basename /home/seiler/bookmarks.html .html bookmarks
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:
CREATE OR REPLACE FUNCTION basename (v_full_path IN VARCHAR2,
v_suffix IN VARCHAR2 DEFAULT NULL,
v_separator IN CHAR DEFAULT '/')
RETURN VARCHAR2
IS
v_basename VARCHAR2(256);
BEGIN
v_basename := SUBSTR(v_full_path, INSTR(v_full_path,v_separator,-1)+1);
IF v_suffix IS NOT NULL THEN
v_basename := SUBSTR(v_basename, 1, INSTR(v_basename, v_suffix, -1)-1);
END IF;
RETURN v_basename;
END;
/
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 10.2.0.1 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):
4.4.4.4 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!
FLASHBACK TABLE vs. DBA_OBJECTS . LAST_DDL_TIME
NOTE: This post originally appeared on The Pythian Group blog on 6 June 2008, and is reposted here with permission.
A little over a week ago, a teammate and I were trying to use Oracle’s FLASHBACK TABLE to undo an “oops” UPDATE statement that a client’s developers had run on one of their test databases, clearing data from two columns in all rows of the table. The statement was actually part of a script that also contained ALTER TABLE statements to add columns. This is important to note because FLASHBACK TABLE will only let you go back as far as the most recent DDL against that table. To quote the SQL reference, “Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.”
A Traveller’s Woe
Having spent 3 weeks in Ottawa, I was very much looking forward to returning home to my family last Friday evening. The itinerary was simple: fly out of Ottawa at 7:30 PM EDT, land in Chicago at 8:30 CDT, fly out of Chicago at 9:30 and land in Green Bay at 10:30 PM CDT where my lovely wife would pick me up and bring me home.
Upon arriving at the airport in Ottawa, I discovered that my flight was delayed due to bad weather in Chicago. I literally threw my head back in despair. My flight from Green Bay to Chicago 3 weeks prior had also been delayed 3 hours due to bad weather in Chicago. Fortunately the flight from Chicago to Ottawa was also delayed in departing so I just made it before they closed the doors. This fortune would not repeat itself.
die Updates
Just dropping a line to let everyone know that the large part of my tech blogging will now be at the Pythian Group blog. I’ll keep this blog space open and post links to my new posts over there. I’m still searching for the balance of what will be strictly over there vs. what content will be just here, so please bear with me.
Moving On Up
Today is my last day at my current employer. It has been an incredible learning experience, and I’ll be forever grateful to the man who gave me the opportunity almost 7 years ago having zero knowledge of either Oracle or database administration.
I have accepted an offer from noted consulting firm The Pythian Group to join their staff of DBAs doing remote administration. This new position should give me exposure to a diverse set of client environments and configurations and a chance to grow in this career path and learn from some of the best in the business. I will be flying out on Sunday for a 3-week stint at the worldwide headquarters in Ottawa, Ontario, Canada to immerse myself in the Pythian way. If I have free time with my nights I hope to finish porting LeagueSite to drupal.
I’ll also try to get more regular with the blogging. Things have been a bit hectic as of late.
Helpful Hints for RMAN Recovery
When you copy your backup files onto disk for RMAN to use for practicing cold-metal restore/recovery, make sure that they are at least visible to the oracle OS user. It doesn’t help to have them owned by root with perm 640.
Thank me later.
Bind Variables and Parallel Queries Do Not Mix
This post was promised long ago, and I apologize for the tardiness. Some of you may recall my whining about seemingly unexplainable instance hanging since migrating our database to 64-bit hardware in September. Well, after some back and forth and hand-offs from one rep to another, we finally were given a possible explanation: Bug 4367986. The summary of the bug is “bind peeked parallel cursors do not share.” This basically means that parallel queries that use bind variables won’t share cursors. Not only does this defeat the purpose of using the bind variables, but it creates a new cursor for each parallel process. After a while, your cursor count will go up, just as mine did:
select sql_id, count(*) from v$sql_shared_cursor where bind_peeked_pq_mismatch='Y' group by sql_id; SQL_ID COUNT(*) ------------- ---------- f3u64ru922snx 520 ckha07wkfaf8v 5 9g26upcqjh8kp 1 gdnga6d26vf4g 15
While I wasn’t able to choke and hang the instance in development, I was able to drive the count up as we saw in production.
We probably didn’t see this before our x86_64 migration because on our 32-bit instance, our parallel_max_servers was only set to 16. After migrating to the new hardware it was raised to 80 based on “the formula”. Dropping it to 0 obviously prevented the problem from coming up as well.
The bug is reportedly fixed in 10.2.0.4, which wasn’t released at the time we were finishing up the SR. There was only a one-off patch for 10.2.0.3, meaning we had to upgrade from 10.2.0.2. Well we did this, applied the patch, and haven’t had a reoccurrence of the problem since. That query listed above now happily returns no rows.
Of course there was also the issue of using bind variables in queries against partitioned tables. Greg Rahn had this to say:
Using PQ with binds can have other adverse effects, specifically if
the partition key is not provided as a literal. When the partition
key is a bind, the resulting plan will be a KEY-KEY plan (for
pstart/pstop) because w/o a literal value the optimizer can not tell
if there is any partition elimination since the literal value is not
provided at parse time. This often times results in a “wost case”
assumption, thus is it possible to have different plans even when the
bind and literal statements use the same values.I would speculate that the overhead of parsing literals when using PQ
is minimal compared to the side effects it is causing (due to the bug)
and the potential of suboptimal plans. I personally would never mix
the two.
Don: 1 vs. 1Z0-043: 0
Passed the 1Z0-043 exam today, otherwise known as the OCP exam. Just need some other jazz like my hands-on course requirement form to be processed and then I should get my badge and gun and key to the executive washroom.
Thanks to Bradd Piontek for reminding me of how silly it all is, inspiring me to pass it even more so that I wouldn’t look even more foolish failing a silly exam.
