Thanks to all of you that attended my PL/Scope session at Code One in San Fransisco!
You can find the scripts from my presentation about using PL/Scope to find your way through a PL/SQL code base in my github repository here: https://github.com/nicetheory/PL-Scope
Working on a routine for dropping old partitions from a log table, I realized that I don’t have to know the name of the partition I want to drop. I can just specify a value for the partition key and Oracle resolves the partition for me. Here’s how.
I was going through some of the Top SQL-reports in SQL Developer, running them against our production system.
One of the “culprits” that showed up was a procedure call, not a query. A quick investigation showed that this procedure was fairly large and consisted of quite a few queries.
I had a case where I had to match some names and needed to find a set of ways to clean and match strings. The solution I ended up with was a combination of regular expressions, the NLSSORT-function and the UTL_MATCH-package with the Jaro-Winkler algorithm.
Oracles function result cache (FRC) can in certain cases give a considerable performance boost. Application context is another useful feature. How does FRC work when the function result relies on context-settings? And how can we make them play well together?
Can we use PL/Scope to find dead and potentially dead PL/SQL code? Yes! And it takes less than a second!
PL/Scope records declarations and usages of procedures and functions, so it should be easy to do a quick comparison of what’s declared and what’s actually used.
I’ve always just used the TRIM-function to remove leading and trailing spaces. While going through Markus Winands presentation “Four* Major Database Release of 2017 in Review” on SlideShare.net, I realized that the TRIM function can do more than just remove spaces.
I read this blog-post by Connor McDonald the other day about the Advanced Network Compression and network transfer savings. It reminded me of a feature not many know of and comes without an extra cost option. In fact, it’s on by default.
In-database archiving is a neat, new 12c-functionality that lets you logically “delete” rows by changing a flag in a column: A “soft-delete”. How does this functionality work in relation to foreign keys?
I had a task to clean up some data and I decided to use in-database archiving, a new feature in 12c. As I was working I noticed some unexpected resultsets along the way. I was quite puzzled for a little time, so I set up a test-case and realized that I’ve struck on a bug. The bug seems related to the ANSI re-architecture-features of the optimizer.
- nice theory... 