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.
I’m currently working on a project that involves a system that is basically unknown to me. We also have to make changes (additions) to the system as part of our project. The system is a PL/SQL hard-shell application with all business logic and queries in the Oracle database and has about 1 million lines of code. Being a PL/SQL application, it has the great advantage that the source code is easily available for quick searches through views like all_source. We also know that all queries are in the database.
My requirement was to intercept and change the logic around the update of a specific column in a specific table. This means that I will have to find and examine all statements that inserts into or updates this table.Read More »
During a session on Oracle OpenWorld I had a pleasant time watching Keith Laker use LiveSQL.oracle.com extensively to walk through a tutorial on SQL pattern matching. Very powerful indeed! Both the pattern matching and the way the tutorial was built up.
So, having used it a little bit before, I thought I’d share an example of the in-database archiving functionality in 12c with my team, using Live SQL.
I first jotted down an example on my own, since I like to work through stuff on my own to explore caveats which sometimes are not obvious in the examples. I was surprised when my example didn’t work and spent a bit of time going through documentation.
I also tested an example from the fabulous library of Tim Hall‘s blog posts – and that didn’t work on Live SQL either.
However – both examples worked flawlessly in my VirtualBox database.
Going through the Help-pages of Live SQL i realized that this functionality probably isn’t supported due to session privileges. In fact – I found an example, added by Oracle, a couple of weeks ago demonstrating in-database archiving, where I found the following comment:
Note that the WHERE clause has been added for demonstration purposes in Live SQL. Outside the Live SQL environment the WHERE clause would not be needed because the ROW ARCHIVAL VISIBILITY session setting can be determined.
So – bottom lines:
I’m honoured to be selected to present twice on OUG Scotland 2017 in Glasgow in june:
Happily I get to deliver my favourite topic “Scalability, Maintainability, Correctness? Easier with #ThickDB“. In this session I’ll look at the Thick DB-paradigm and explain the major negative effects of not adhering to it it will have in a data-lifecycle perspective. We’ll take a red pill and look into The Matrix and see what really happens.
Sadly I’ll be doing that at the same time as Jonathan Lewis talks about “Reading Parallel Execution Plans”. I would really have liked to get a refresh on that topic.
In the afternoon, I’ll talk about a proof of concept (PoC) that we did in the last year: “Consolidating large critical medical databases with minimum downtime – a story from Norway“. The goal was to find a way to migrate 9 medical databases into one of about 100+ TB with a downtime of less that 4 hours for every migration. That includes replacing billions of keys in the process. This was a fun project where we could really test and evaluate different techniques of moving data from several sources into on large one. By applying better techiques and automation we reduced the data-loading downtime window from 15+ hours to 1-2.
I held both of these on OUGN’s spring seminar this year, and this time I assume that the latter session will be unobstructed by (the ferry’s) safety-announcements. 🙂
