TweetShareSharePin0 SharesSometimes the results of a SQL is not at all what you expected. Most of the time it is a simple error you’ve made. Very rarely is it an issue that after hours of looking at it still looks like SQL does something wrong, But it still happens. This post is about one such situation that occurred last week. If you read the last blog post, you’ll recognize some of this. It turned up when I looked at transforming […]
Recent Posts
LUHNs algoritm – Three ways with SQL and PL/SQL
TweetShareSharePin0 SharesHave you encountered LUHNs algorithm? I can almost guarantee it even if you’ve never heard the name before. It is part of all of our lives every single day. It is used to check that various numbers are correctly entered. From ID numbers for persons in Sweden, Greace, and Israel to credit card numbers and IMEI numbers and misc other things. It is a very simple checksum function not intended to be cryptographically secure hash function. Due to the […]
Waiting for direct write temp – but what file?
TweetShareSharePin0 SharesThis is essentially a not for myself for the next time I forget. It seems to be like once every other year I figure this out. It is not too easy to google for so it usually takes a bit too long. So you have identified the event “direct path read temp” or “Direct path write temp” as your main culprit in a SQL. Great, now let’s just see what temp file it is. That is how I’ve started […]
BTB – Aggregation
TweetShareSharePin0 SharesIt is time for aggregating data, using the GROUP BY keyword. Hint: I will be using the emp/dept tables used by Oracle for demo in all demonstrations. I recommend Live SQL for playing, it is a great place to test SQL and learn from others. You can also sign up for a database for free (for a short time) on Oracle’s cloud. I recommend beginning with Live SQL and moving to paid services when you are ready, Hint II: You can run the code […]
Back to basics
TweetShareSharePin0 SharesLet’s start with baby steps We’re all very excited to read a new blog with a neat trick or a cool and complex feature or even a very useful undocumented function. However that is preaching to the choir. We do that and then we get together and complain that there is no new fresh talent in the database business. Well, it may be a bit hard to get into it if all we do are talking new features and really […]
Getting t1001 in interactive grid PK
TweetShareSharePin0 SharesHave you been plagued with the issue of getting values like t1001 show up in columns you marked as primary key in your interactive grid? In case you do not feel it is a good interface for your end users you may have found yourself spending quality time with Google looking for clues. I have had a couple of people asking me about this in just the last week. Googling turns up very little and mostly about the T1000 processor […]
Quicktip: Don’t grant access to a synonym
TweetShareSharePin0 SharesI’ve seen this in a few places lately. When I ask the database guys there about it, they think this DDL would never work or “it does nothing but does not return an error”. Here is an example, All of the following is performed by a DBA user. We create a table, let’s call it TB. It is owned by user A. User B is granted select privilege to A.TB. A synonym is created for user B pointing to A.TB, let’s call […]
When views causes havoc
TweetShareSharePin0 SharesViews are great. They simplify design, makes code look more elegant and hides complexity. They also enables reuse by putting complex code in just one place instead of in every accessing piece of code. But… There once was a large project that has been churning away for a long time. The performance of having more than one user on the system was horrendous. But it was chalked up to misconfiguration. I am asked to spend a day giving said […]
Quick note on MT and applications II
TweetShareSharePin0 SharesTalking about Multi Tenant and applications, one has to talk about the different options for sharing. It is one of the great features of multi tenant. It is a way to let one PDB – the application root – have data that can be used from other PDBs. There are three kinds that allow you to get different features of the sharing. Metadata Data Extended Data Lets review each kind and why and how you’d use it. Metadata This allows […]
Quick note on MT and applications
TweetShareSharePin0 SharesAre you using multi tenant with multiple containers and you’re not familiar with application containers? It allows you to set up PDBs that belong to a CDB-like PDB that “owns” them. With that you can install applications (database objects) in the container, and then decide into which PDB to install, upgrade or patch that particular application, So you could have an application that is a set of schemas with objects that belong together in a logical sense. Once you have […]