InfoDie Select-Statements in diesem Blogpost habe ich von anderen Webseiten kopiert. Daher ist dieser Beitrag eher als Zusammenfassung unterschiedlicher Lösungsversuche zu sehen und dient mir als schnelle Hilfe bei der Verkleinerung eines zu großen Tablespaces. Schaut euch die Quellen an, die sehr viel detaillierter auf die jeweiligen Probleme eingehen.
Wer kennt nicht die Situation? Der DBA ruft an und meint der TEMP Tablespace verbraucht mehrere hundert Gigabyte an Speicher.
Was ist in solch einer (more...)
This week I was in an excellent presentation by Kerry Osborne about Outlines, SQL profiles, SQL patches and SQL Baselines. I’ve used three of those features in anger but when I looked at SQL Patches I just could not understand why you would use them – they looked to me like a very limited version of SQL Profiles.
There is a prize for spotting Kerry without a baseball cap
So I asked Kerry about it (more...)
Today I did my second presentation at DOAG2016. It was at 9:00 so I got to sleep in ;) The room was huge but there were enough people that I didn’t feel too lonely. The room and the technical help were top notch, and again there were questions at just the right time to remind me […]
DOAG2016 started today at 8:30, and I did too. There were so many great presentations at the same time as mine, I was surprised and pleased to get a nice audience.
If you are a user of the Oracle Data Miner tool (the workflow data mining tool that is part of SQL Developer), then you will have noticed that for many of the algorithms you can specify a Case Id attribute along with, say, the target attribute.
The idea is that you have one attribute that is a unique identifier for each case record. This may or may not be the case in your data model (more...)
A little while ago a member of the Danish Oracle User Group DOUG
asked if anyone had a bit of experience with use of R together with Oracle Database. I remembered an article I wrote for OTech Magazine on forecasting with R and Oracle and thought it would be a good primer for him. OTech Magazine is no longer active, sadly, but I thought I could send him a link anyway to my article. That (more...)
This year I get to speak about advanced SQL twice at two different conferences. My first presentation is about row pattern matching with MATCH_RECOGNIZE and my second deals with ranges - including but not limited to Temporal Validity ranges.
We have gone through a merger at our company where we are trying to merge the databases. The problem now is that we have duplicate records in our tables. We can of course go through all the records by hand and check if they exist twice. Another option is to build an application to do this. But using the Oracle Database there must be a better way to do this. Any ideas?
This is my contribution to the OTN Appreciation Day, which was initiated by Tim Hall.
One of my favorite features of the Oracle Database are Analytic Functions. They were introduced with Oracle Database 8.1.6 Enterprise Edition, and have been in the Standard Edition since version 9.
With analytic functions you can add inter-row calculations, aggegrate over multiple dimensions, rank assignments based on values. All this without a GROUP BY clause.
The syntax might (more...)
Oracle 12c introduced the ability to specify sequence.nextval as the default on a column, which is really nice – including the fact that it eliminates one of your excuses why you don’t decommission those old triggers.
Unfortunately it doesn’t work as you might expect if you use an
INSERT ALL statement; it evaluates the default expression once per statement, instead of once per row.
create sequence test_seq;
create table test_tab
( id number default (more...)
Recently I heard someone talk about overloading in Java. What is it, is it possible in PL/SQL and if so, how would I use it?
Overloading is a technique of creating multiple programs with the same name that can be called with different sets of parameters. It is definitely possible to apply this technique in PL/SQL, in fact, Oracle does this a lot of times in their own built-in (more...)
Because today is Batman Day
, it calls for a celebration of the SQL type:
SQL> select listagg (b)
2 within group (order by null) ||' Batman'
3 from (select 0f/0 b
4 from dual
5 connect by level <= 14
Oracle Database Release 12c (12.1) introduced a new operator that we can use in SQL queries. The MATCH_RECOGNIZE operator allows us to detect patterns in our relational data. Specifically: it allows us to identify records that mark the beginning of a set of records that together form a pattern. In this set, each record satisifies a certain condition. The fact that in a set of records these conditions are met – in a certain (more...)
Last year I did a presentation on table functions at KScope. One of the questions I got was: ‘If pipelined table functions provide their results faster, why would you want to use a normal table function?’ I couldn’t come up with the answer then, maybe you can help?
Erik van Roon
Let’s start with explaining a bit what table functions are. Table Functions are functions that return a collection of (more...)
One of those things SQL developers are frequently looking at is the generation of rows: having a query return records that do not really exist. For example to generate test data or to produce records for all days in a month. Tom Kyte usually selects from data dictionary views. Various tricks make the rounds, for example based on CONNECT BY or CUBE or UNPIVOT. This blog article by Natalka Roshak (2015) compares various row generation (more...)
What is an ANTI-JOIN? And what is the difference between the SEMI-JOIN and the ANTI-JOIN?
First of all, both SEMI-JOIN and ANTI-JOIN are not in the SQL syntax but they are more a pattern. You might expect to be able to write something like:
[PATRICK]SQL>SELECT d.deptno, d.dname, d.loc
FROM dept d
SEMI JOIN emp e ON (e.deptno = d.deptno)
to get all the (more...)
firstname.lastname@example.org > create table t( pk int primary key check(pk > 0));
email@example.com > insert /*+ignore_row_on_dupkey_index(t(pk)) */ into t
firstname.lastname@example.org > select trunc(dbms_random.value(1, 1e5)) from dual
email@example.com > connect by level <= 1e5
firstname.lastname@example.org > /
63187 rows created.
Finding the first n gaps
email@example.com > variable n number
firstname.lastname@example.org > exec :n := 1000
PL/SQL procedure successfully completed.
email@example.com > set autotr (more...)
Could you tell me what the difference is between NVL and COALESCE?
NVL returns the value of the first argument if it doesn’t evaluate to NULL, otherwise it will return the value of the second argument.
COALESCE returns the first argument that doesn’t evaluate to NULL. That can be any one of the arguments.
So they are definitely similar – but there are significant differences.
First of (more...)
I have got a website with a search form. I want to display a limited number of results to the user and have him/her navigate through different pages. Is this possible using plain SQL?
Of course this is possible. It might take some thinking, but that has never hurt anyone (yet). First we need a table with some randomly sorted data in it. In this example I (more...)