Just a quick post today to play around with some analytic functions.
Read more
How to SSH into your VirtualBox VM from another machine, OS X edtion
By far the easiest way to get Oracle up and running on OS X is to install VirtualBox and run a developer VM. The VirtualBox and VMs are free downloads from Oracle. You don't have to install the database and the developer VM comes with labs to get you started.
So what could make it even better? Well, the VM needs memory and CPU, and my laptop is old and feeble (the problem with "laptop RAC" alas). What if I could install the VM on a desktop and just ssh into it from my older, low-powered low-memory laptop?
What you can’t do with NEXTVAL
I wrote a quiz for the PL/SQL challenge on sequence pseudocolumns, and I noticed that a couple of the answers tricked a lot of people who took the quiz. Time for a blog post on what you can’t do with NEXTVAL!
If you want to try the quiz, do it now before reading on:
Pseudocolumns: Sequence Pseudocolumns from “Weekly SQL Quiz: 31 January – 6 February 2015”.
Read more
Getting oriented in a CDB when all you have is SQL*Plus access…
SQL> col PDB_NAME FOR A15 SQL> SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID; PDB_ID PDB_NAME STATUS ---------- --------------- --------- 2 PDB$SEED NORMAL 3 ORCL NORMAL SQL> ALTER SESSION SET container=orcl; SESSION altered.
A little fun with global temporary tables
Here’s a little bit of fun I thought up as part of a SQL game I was working on at Oracle. It’s a simple use of a trigger and a global temporary table to create a “combination lock” that any logged-in session can try to “open”.
To use it:
SQL> INSERT INTO combinationlock (combination) VALUES ('Test'); 1 ROW created. SQL> SELECT * FROM combinationlock; COMBI LOCK_STATE ----- -------------------------------------------------- Test Closed SQL> UPDATE combinationlock SET combination='99999'; 1 ROW updated. SQL> SELECT * FROM combinationlock; COMBI LOCK_STATE ----- -------------------------------------------------- 99999 Closed SQL> SELECT * FROM combinationlock; COMBI LOCK_STATE ----- -------------------------------------------------- 12345 OPEN Sesame
To create:
DROP TABLE combinationlock; CREATE global TEMPORARY TABLE combinationlock (combination varchar2(5), lock_state varchar2(50)) ON commit preserve ROWS; DROP TRIGGER combinationlock_trig ; CREATE TRIGGER combinationlock_trig BEFORE INSERT OR UPDATE ON combinationlock FOR each ROW BEGIN IF :NEW.combination = '12345' THEN :NEW.lock_state := 'Open Sesame'; ELSE :NEW.lock_state := 'Closed'; END IF; exception WHEN others THEN :NEW.lock_state := 'Closed'; raise; END; /
The nice thing about this is that, since it’s using a global temporary table, any user can try their guesses at the combination and it affects only their session.
Floating down the infinite river
NVL vs NULLIF
What’s the difference between NVL and NULLIF?
These two similarly named functions do very different things. Here’s a comparison.
RMAN: Delete older archivelogs to free space
A development database with a small FRA was filling it up with archived logs, hanging the database on log file switch (needs archiving).
Read more
Troubleshooting walkthrough
Got a message from a user of a not-much-used dev database that their SQL Developer connection was timing out. Here’s a walkthrough of how I troubleshot (troubleshooted?) the issue to find that the root cause was a kernel issue.
Read more
Quick start guide to 10046 tracing
Here’s a quick start guide to taking and interpreting 10046 trace, with bind variables.