Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: dba
Cool stuff with partition elimination
Sometimes in the IT world, the term “surprise” is not a good one. “I woke up this morning and got a surprise…my database was down.” “I ran a SELECT COUNT(*) on my most important table, and got a surprise result of zero rows.” and…
Choose your names wisely
It all seems simple enough. You pick a name for an object…and that is the only object that have than name right ? For example: SQL> create table MY_UNIQUE_NAME ( x int ); Table created. SQL> SQL> create synonym MY_UNIQUE_NAME for MY_UNIQUE_NAME; create synonym…
NULL’s vs NOT NULL’s and Performance
When it comes to giving the cost based optimiser the best possible chance to make the “right” decisions, many DBA’s are diligent in keeping statistics up to date, using histograms where appropriate, creating more indexes (or removing surplus indexes). However one often neglected area…
Getting started…adding an account to use
If you’ve read my previous post about getting started with the Oracle database, then hopefully you now have your very own database installed and running, and you have a explored a little with the sample schemas using SQL Developer. Perhaps now you want to…
Clone a table
Sometimes doing a CREATE TABLE AS SELECT is all we need to copy the data from an existing table. But what if we want more than that ? What if we really want to clone that table to match the original as closely as…
Licensed for Advanced Compression? Don’t forget the network
We often think of Advanced Compression being exclusively about compressing data “at rest”, ie, on some sort of storage device. And don’t get me wrong, if we consider just that part of Advanced Compression, that still covers a myriad of opportunities that could yield…
iASH–my “infinite ASH” routine
I love Active Session History (ASH) data because a lot of the work I’ve done in my consulting life was “after the fact” diagnosis. By this I mean that many of us have been in a similar circumstance where the customer will contact you…
Parsing freeform data in flat files
SQL loader is a very cool utility that has existed for a long time within Oracle to load flat files into the database. However sometimes people find the control file syntax quite cryptic, and when it comes to passing very complicated structures, this can…
Idle banter
When your car gets a flat tyre, it’s always handy to have a spare. We do the same with the database SQL> select 2 x.ksppinm name 3 from 4 sys.x$ksppi x, 5 sys.x$ksppcv2 y 6 where 7 x.inst_id = userenv(‘Instance’) and 8 y.inst_id =…
Updating indexes with partition maintenance
An index is basically a structure that maps keys (values) in columns to the physical location of their corresponding rows in a table. So if you move the rows (ie, change the physical location of a row) then the index entries for those rows…
LOBs and tiny typos
This one caught me out – I was racing around looking for bugs, or parameter files, or hidden settings that stopped SECUREFILE lobs from being created. Here was my incredibly simple test case – create a securefile LOB, and then make sure it’s a…
Partitioning Guide updated
Just a quick note to say I’ve updated our Getting Started With Partitioning Guide! 9 easy to follow demonstrations to get developers up to speed with handling large databases. Check it out here
