An unavoidable fact of database support life is webconferences with clients or users. Most of the time, we’re more interested in what’s going on onscreen than in each others’ faces. But every now and then we need to have a face-to-face. Skype is popular, but I recently had the chance to try out a FOSS alternative with better security: Jami.
Read more
Category: DBA
“What do you mean there’s line breaks in the address?” said SQLLDR
I had a large-ish CSV to load and a problem: line breaks inside some of the delimited fields.
Read more
Review: Oracle RAC Performance Tuning
Some time ago, I received a free review copy of Brian Peasland‘s recent book, Oracle RAC Performance Tuning.
Read more
Search plugins: Search Oracle docs from your browser search bar
Tired of navigating to the SQL documentation every time you need to look up syntax? I created a search plugin so that you can search the SQL documentation directly from your browser’s search bar:
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?
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.
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
My .bashrc
A RAC DBA needs to keep at least two sets of environment variables handy: one for the Grid/ASM home, and one for the database home. Here's how I set up my .bashrc to make that easy.