This is turning out to be a bit harder to write than I orginially thought. I generally land in the laissez-faire camp, and I know what you’re thinking: “Jeff, we didn’t come here to read French!” Sorry about that, bad habits picked up from 4 years of public education instruction. In terms of American politics, think of me as the Ron Paul of database security folks.

Set a user up to be able to do what they need to do, then leave them alone.

Don’t throw roadblocks in their path. Don’t make things harder than they need to be. It’s a pretty simple concept. I ranted earlier this year on a similar and kind-of-related topic, Database Security Should be Handled by the Database.

BUT

And that’s a big ‘but’ (hehehehehehe), but do we need to help push our users towards disaster? Not getting in there way is a bit different than encouraging them to step on themselves. And so my maturity as a person has led me to become less conservative and more liberal in my philosophy. Because we know better, maybe we SHOULD use that power and influence to keep the users out of trouble. At what point does this become babysitting?

And this is the crux of why this post is giving me pause. I’m being forced to take a stand and get off the fence. If I’m going to be a Product Manager, I can’t sit back and just play both sides of every argument.

Let’s look at a sample case:

User: Let me drop multiple tables in a single click.

Our tool makes it relatively easy to drop an individual table now. It’s a click-click kind of thing. You get a ‘Are you sure’ confirmation dialog, and the ability to PURGE it from the database as well. So someone isn’t likely to fat finger themselves into the unemployment line.

Let’s say you are in development and you’ve built 30 or so staging tables, and you need to drop all 30 of them. Yes, you could build a script to generate the series of DROP commands, or you could ask your IDE to do that for you. My libertarian tendencies tell me to make that available to the user. But the pragmatist side of me says, ‘Hey these are the same users that can not remember if they are in DEV or PROD half the time.’

So who wins out?

Hopefully the user does. I think we can find a middle ground. Discussion is on now to let the user do this, but have it generate a DDL script that the user must manually run on their own. I think this is a fine compromise.

There are limits to this live-and-let-live philosophy

Don’t ask me to give you a TRUNCATE button. No way and no how. Some things shouldn’t be easy, like getting married. But you can undo a marriage. TRUNCATE is unrecoverable outside of a database recovery. So I’m babysitting the user here, because I know better, and I want to see them prosper. And I take solace in knowing that I’m not a hypocrite regarding my database security rant as TRUNCATE is an ALTER command and there is no way to revoke a ‘TRUNCATE’ privilege.

So which camp to you subscribe to here? Do you say ‘give me privs or give me death!’, or do you take things on a case-by-case basis? More importantly, do you find this aspect of software development interesting enough to read about it here?

Are you sick and tired of playing with the same sample data? Do you shudder at even the mention of the name, ‘Scott?’ First of all, if you’re still using SCOTT, you should try out HR instead. And if you want some beefier tables and need to experiment with partitioning, then SH is the way to go.

But eventually you will get to the point where if you see another SALARY or DEPTNO in a query, you’ll just scream.

So what’s a data geek to do?

Build your OWN demo tables!

Too lazy to do that? Ok, jump to the end of this document and I’ll share my BEER table. It’s hash partitioned, has about 12,000 rows in it, and come in very handy when you’re travelling and want to know what’s available locally.

So the problem with building your own demo tables is that you need some data to pull from. Thanks to the Apple machine, just about everyone has a copy of iTunes. Did you know you can export your iTunes library to a text file? Just mouse-right-click in iTunes on the left where it says ‘Music’. It will create a tab delimited text file.

I recommend converting it to spreadsheet before continuing…let Excel or OpenOffice auto-magically use TABs as a column separator.

You can now import that data to Oracle as a new table!

You have lots and lots of options on how to proceed. You could build a SQL*Loader control file, you could do the 10/11g EXTERNAL TABLE stuff, or you can cheat and use your IDE to auto-import the data for you. Thankfully SQL Developer supports this.

Import Table Data using SQL Developer

• Connect to the database
• Mouse-right-click on the Tables tree node lable
• Point to your file, and answer some questions!

You can get away with the INSERT method, I doubt anyone has a million+ iTunes library. When it’s finished churning, you’ll have a decent sized un-normalized table to play with. For bonus credit, create an ARTIST, COMPOSER, ALBUM, etc set of tables, and replace the text with IDs pointing to the parent records. It’s good SQL practice

Beer here!

Ok, I made you suffer long enough. Here’s your BEER table. I’ve built the following script using my new favorite SQL Developer feature, the Cart. After you download the file, you’ll just want to login to Oracle as the user who want to own the data and run the ‘Generated-20111117140806.sql’ file. Before running the file, please edit the BEER.sql file and insert appropriate tablespace names. You’ll notice the table is partitioned. If you don’t own the partitioning license, then feel free to create it as a straight up ordinary table instead.

You can also add a
‘CONNECT SCOTT/TIGER’
to the top of my script so it’s loaded to the user you want to own the objects.

After you’ve run the script, you SHOULD have something that looks like this

We are surrounded by data

Did you know you can have your Facebook account exported and archived? That could make for an interesting table or two!

What’s in your archive?

“Any photos or videos you’ve shared on Facebook
Your Wall posts, messages and chat conversations
Your friends’ names and some of their email addresses”

Publicly Available Datasets

Kris reminded me about this, some really cool stuff here including the Human Genome Project.

Since I announced my new job at Oracle, I’ve had more than a few friends and associates approach me about databases. They’ve always wondered just what it is that I talk about all the time, or perhaps they want to get their feet wet and want to know more about data in general.

I think this is fairly normal and expected, especially as just about everyone has some sort of interaction with a database. As an end-user or consumer, this is almost always a transparent relationship. You put in your ATM card, and money comes out. You don’t need to understand the database that stores your financial records and transactions – but it’s there. If you fall into this category, it’s enough to understand that data lives in ‘really big inter-connected spreadsheets.’ If you don’t want to install stuff or get your hands dirty, it’s safe to stop reading this now. If you just want to learn more about database and related technologies in general, then Wikipedia is probably a good place to start. Otherwise, read on!

If you are in a business or support role in your organization however, your exposure to the actual database may increase. You might hear someone say ‘Oh yeah, we build those pie charts for you from the database.’

Or maybe you have ‘people’ that run reports or answer questions on your behalf. So while you never directly touch the database, you’re aware that there is thing that stores all the data you rely on to run your business.

There are also many people out there that are running an application that employs a database – WordPress admins and MySQL anyone? – who may never actually touch said database directly – that’s not a bad thing actually.

No need to get drunk first to experiment with data

Even if you’re not a techie, it’s not so hard to get your feet wet with a database. Kids are practically born with an iPad in their hands today – so I don’t think it’s too much to expect someone with the will to lack the skill needed to get started.

And here’s the best part – it can all be done for free!

Back in the old days, us techies got our DB geek on with something called LAMP. Linux + Apache + MySQL + Perl (or PHP) = everything you needed to build a web driven application for free, assuming you already had a computer. I built a web interface for developers to track their new build features using this application stack (substituting Oracle for MySQL) about 8 years ago, and its still in use today!

MySQL is still around, and is more prevalent than many people realize. If you have installed a bunch of applications on your PC, check your task manager – odds are you might have MySQL installed and running today.

And of course let’s not forget the ubiquitous ‘MSFT Access’ application. It’s desktop database provided with Office. If you have a Windows PC, you probably have Office. So if you want to play around with a database, why not give it a look-see.

I think the need to build your own custom apps today is becoming less fierce. There are so many free or low-cost apps available for iOS and Android that allow you to manage your music or photo collection, who needs to build their own system?

But maybe you WANT to build stuff, learn stuff, and grow your IT skills. Have you noticed that database professionals are experiencing some of the lowest levels of unemployment and the highest levels of compensation and career growth? A little bit of database know-how could be enough to really separate yourself from your peers in the Marketing or Social Media industry.

Learn the basics before you ask for access to your ‘production’ database

Just because Oracle is running in your company, doesn’t mean you get to touch it. Access to the database is generally restricted to those that have a business need. So, if your needs are currently met by a series of reports or ‘that smart gal that runs queries for you’ – you may need to fall back on setting up your own playground.

If you want to eventually get into that Oracle database though, why not run your own first? Oracle makes this VERY easy with their pre-built VirtualBox image that’s running Oracle database 11gR2 on it. It also has their application framework known as APEX if you want to get fancy and start building interactive websites based on your rotisserie league baseball database. Pre-Built Developer VMs for Oracle can be downloaded here.

If you really have that entrepreneurial spirit, you can also mess around with MySQL. You can download both the database software and a free GUI client to run on Windows and be up in running in a few minutes.

Once you get the gist of tables and rows – and the language used to query them, SQL – then perhaps can start building your business case to be granted access to your REAL database.

Still Curious?

Some things to consider…

• Do you have a computer? Is it beefy? You’ll need plenty of RAM to run Oracle. If you’re a bit short, then MySQL is the way to go
• You’ll need some data to play with. Oracle has the SCOTT, HR, and SH demo data you can mess around with. I recommend going for SH. The nice thing about the VirtualBox image from Oracle? It’s preloaded with all the data you’ll ever need
• STEEP learning curve. Just getting started with Oracle can be daunting. If you’re ready to jump in, try to find a local resource you can rely on. Just having someone tell you WHAT to ‘Google’ can make all the difference. Also, use and lean on the database community. Notice I didn’t say ‘abuse’ the community.
• Does your company have any sort of training or higher education reimbursement programs? Many community colleges have database classes you can take
• Buy this book – SQL in a Nutshell. My good friend Kevin Kline wrote it, and he has many small childrens to clothe and feed
• And last but not least – don’t forget Oracle has an excellent free graphical interface to the data called SQL Developer. It makes connecting, querying, and reporting the data much easier. You still have the steep learning curve, but you’ll enjoy your experience ten-fold over using the old-school command-line interface (this thing that old dudes like me use to make others feel stupid)

A Toad user posed this question today:

Is there a way to move a (marked) TABLE mytable123 from Tablespace AAA and Schema BBB to another Tablespace USERS and Schema MYSCH?

Let’s tackle the 2nd and easiest part first!

Moving a table or partition to a new tablespace is pretty straightforward:

ALTER TABLE table_name MOVE
TABLESPACE tablespace_name;

The ALTER TABLE MOVE clause is documented and diagrammed in the Oracle Docs.

However, the user ALSO wants to change the owner of the table.

Now how do we go about doing that?

ALTER TABLE CHANGE OWNER…no, can’t do that. Darn! For the morbidly curious, DB2 DOES make that pretty easy with TRANSFER OWNERSHIP. However, my user isn’t willing to port his application to DB2 probably, so we’re back to where we started.

Just brainstorming for a few minutes produced these ideas:

Create Table As Select

Create a copy of the table fresh in the ‘new’ SCHEMA. You can specify storage in this command, so the tablespace piece can be addressed here too. This is one of my favorite ‘tricks’, but it does have its drawbacks. It won’t grab the triggers, synonyms, and other objects associated with the table. It leaves the old table in its place. The data could change while it’s being ‘copied.’

Export + Import (Datapump)

Get a logical backup of the data and then import it into the new schema. Again, the data could change after your copy is made, and then you’ll need to drop the ‘old’ object(s) afterwards.

In my original post, I recommended a third method – using DBMS_REDEFINITION to move the table painlessly in production. Turns out that I was W-R-O-N-G.

Here’s a technical blogging tip: try testing the stuff you advocate as a solution first! I saw a colleague make the same mistake as me – he assumed that DBMS_REDEF was ‘flexible’ enough to allow this ‘moving’ of tables between schemas. If you actually read the docs, you can see they take great pains to mention that these scenarios are ALWAYS “in the same schema.” Doh!

Whatever You Do, Don’t Do it this Way!

Just so we’re clear on the matter, here is what the good folks at AskTom have to say on the subject:

that is the worst thing you can do.

You lose, game over player one.

That is the best way to totally corrupt your database.

Never, not under any circumstances — without support telling you “do this update” — will you EVER
update the data dictionary.

It is the worst thing you can do (besides not using bind variables).

Don’t do that, EVER. Period. Anytime you are tempted to update a SYS. table — stop, don’t.

It could be fun to try in a test environment though. Ha! Seriously though, don’t do it.

Now if you want to move a picnic table, that’s something that is much easier!

“Now One Person Can Move Heavy Picnic Tables!”

I stumbled upon an interesting discussion in the ODTUG group on LinkedIn this weekend. A vendor is doing some research I suppose and wanted to know who enjoyed using what IDE and why. Here’s the original question:

In your opinion which is the best IDE for PL/SQL programming and why? e.g. PL/SQL Developer, Toad, Oracle SQL Developer. To participate in the conversation, you’ll need to join the group on LinkedIn.

My only input was to suggest that maybe the question could be better worded. Instead of saying what’s ‘best’, maybe share your favorite IDE or your favorite feature in an IDE. Real quick – IDE – is an Integrated Development Environment.

I’m always getting this question from prospective clients, although asked in a slightly different manner. It’s usually posed as…

‘Why is your product better than X?’

I really despise this question, maybe I should find a new job? It’s not that I can’t defend the technology I am selling – I’m really quite decent at it, and the tool usually sells itself. I think what really ‘gets my goat’ is that we are not addressing the REAL question. Every developer already has a favorite tool – most likely the one they found first. Those hours invested in learning the ins and outs of a technology are not easily forgotten. What someone wants to know is, ‘Why should I ditch my tool and use yours?’ I hate that the sales model often puts us in a confrontational or all-or-nothing mindset. In my head I’m just selling you a new cool pipe wrench to play with. No one is asking you to throw out the rest of your plumbing tools. Of course, let’s not be too naive and completely ignore the fact that new shiny tools usually cost a few shiny coins, so the cost must always be justified.

Someone once said that a good developer should never get religious about the tools they use. The word ‘religious’ I think is very apt and spot-on for this conversation. I think I would be more successful attempting to convert a Southern Baptist to a Presbyterian than trying to convince someone that the programming environment they’ve used for the past ten years is ‘wrong.’

I don’t want to convert anyone. I just want folks to be productive. I like my tools because they enable me to be super-productive without sacrificing the years I have spent learning the ins and outs of Oracle. One of my favorite questions posed in the Oracle Magazine Q&A goes something like this – ‘If you could only bring one Oracle book to the International Space Station, what book would it be?’ Imagine instead of ‘book’ they were asking about tools. If you have to think more than a few seconds on this one, I doubt you’re not a true oraclenerd.

Yes, I realized I’ve had this conversation before.

I Only Use SQLPlus, a Desperate Cry for Help – can you believe that some people refuse to use no tools at all?

In yesterday’s post, I posed a question: What Hint Do You See Over or Misused?

Gary (Twitter) responded with

With APPEND the rows are written directly to the datafiles and are not logged. If the DBA doesn’t know that unlogged changes are being made then whatever recovery plans they have may be useless when it comes to restoring from a backup.

In theory they can set FORCE LOGGING on individual tablespaces so they can’t be sabotaged by a rogue developer. That’s not the default so it relies on the DBA being prepared.

Ok, so this freaked me out a little bit. Mostly because I was upset at myself for not being more familiar with the Direct-Path INSERT technology that Oracle has offered for YEARS. As a matter of fact, I used to load data frequently for customers using SQL*Loader and IMP, so I had a pretty good idea how Direct-Path data loads work. Here’s a quick summary of how it works for INSERTs:

• Data is inserted at the end of the table
• Data bypasses the buffer cache
• Referential Integrity is ignored

I got this from reading the Oracle Docs – good ole Oracle Docs

Now, if you scroll down a bit, you can read about how the user can also disable ‘LOGGING’ for the data load. And surprise, surprise – this can also aid performance. If Oracle doesn’t have to track the REDO information for its logs, then sure it’s going to run faster. Of course if your system crashes before your next backup, you’re going to need to insert this data again – assuming you can.

Ok, now let’s get back to the APPEND hint. Yeah, developers may be using it to make application inserts run faster. They don’t care about the storage consequences, that’s a DBA’s job. Their application doesn’t even HAVE foreign keys in the database, so they don’t care about that either. So they read about this HINT online somewhere and decide to implement it to speed up their application.

Months later something bad happens, and a ton of customer data is lost.

The DBA is incensed because they come to find out the application was loading data without being logged.

Ok, I have to admit, there are some pretty big holes here. One, let’s hope the application DBA is INTIMATELY familiar with their application code, especially when it comes to how the database transactions work. And B, no self-respecting DBA would create a table or tablespace with NOLOGGING as an attribute, right?

So what happens when someone runs an INSERT with the /* +APPEND */ hint?

Let’s find out!

Create a tablespace with LOGGING
Create a tablespace with NOLOGGING.
Create a table – copy of SH.CUSTOMERS to LOGGING tablespace
Create a table – copy of SH.CUSTOMERS to NOLOGGING tablespace

Take a few records and INSERT with the /*+ APPEND*/ hint to both tables.

Check the redo logs – we should find records for the first table but not the second.

You can set the LOGGING property at the Segment (fancy name for Table or Partition), but if you don’t, it inherits from the TABLESPACE it resides in.

I Freaked Out for No Reason

If you were following me on Twitter this morning you may have noticed a few tweets where I was freaking out. It turns out I was seeing unpected results because I was runnign inserts against the NOLOGING tables and the records weren’t’ getting logged as expected. I just thought I was writing to the LOGGING tables. I didn’t notice the mistake until I pasted the code into WordPress and noticed the code was wrong. My Replace All in my IDE didn’t replace all what I wanted it to

Jump To the Results

Here’s my code:

--tablespace with LOGGING
CREATE TABLESPACE LOGGING_Y DATAFILE
  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOGGING_Y01.DBF' SIZE 256M AUTOEXTEND ON NEXT 128M MAXSIZE 1024M
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

--tablespace with NOLOGGING
CREATE TABLESPACE LOGGING_NO DATAFILE
  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOGGING_N01.DBF' SIZE 256M AUTOEXTEND ON NEXT 128M MAXSIZE 1024M
NOLOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

--TABLE IN LOGGING TABLESPACE
create table sh.customers_no_logging
tablespace logging_y
as select * from sh.customers;

--Table in NOLOGGING TABLESPACE
create table sh.customers_no_logging_double
tablespace logging_no
as select * from sh.customers;

-- run some additional inserts to the logging table

Insert /*+ Append*/ INTO SH.CUSTOMERS_LOGGING
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
    CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
    CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
    CUST_VALID)
 Values
   (49671, 'Abigail', 'Ruddy', 'M', 1976,
    'married', '27 North Sagadahoc Boulevard', '60332', 'Ede', 51442,
    'Gelderland', 52610, 52770, '519-236-6123', 'G: 130,000 - 149,999',
    1500, 'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'I');

Insert/*+ APPEND */ into SH.CUSTOMERS_LOGGING
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID, CUST_STATE_PROVINCE,
    CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL, CUST_CREDIT_LIMIT,
    CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM, CUST_VALID)
 Values
   (3228, 'Abigail', 'Ruddy', 'M', 1964,
    '37 West Geneva Street', '55406', 'Hoofddorp', 51669, 'Noord-Holland',
    52683, 52770, '327-194-5008', 'G: 130,000 - 149,999', 7000,
    'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'A');
Insert /*+ APPEND */ into SH.CUSTOMERS_LOGGING
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
    CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
    CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
    CUST_VALID)
 Values
   (6783, 'Abigail', 'Ruddy', 'M', 1942,
    'single', '47 Toa Alta Road', '34077', 'Schimmert', 52297,
    'Limburg', 52646, 52770, '288-613-9676', 'G: 130,000 - 149,999',
    11000, 'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'I');
Insert /*+ APPEND */ into SH.CUSTOMERS_LOGGING
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
    CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
    CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
    CUST_VALID)
 Values
   (10338, 'Abigail', 'Ruddy', 'M', 1977,
    'married', '47 South Kanabec Road', '72996', 'Scheveningen', 52296,
    'Zuid-Holland', 52771, 52770, '222-269-1259', 'G: 130,000 - 149,999',
    1500, 'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'I');
Insert /*+ APPEND */ into SH.CUSTOMERS_LOGGING
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID, CUST_STATE_PROVINCE,
    CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL, CUST_CREDIT_LIMIT,
    CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM, CUST_VALID)
 Values
   (13894, 'Abigail', 'Ruddy', 'M', 1949,
    '57 North 3rd Drive', '67644', 'Joinville', 51698, 'Santa Catarina',
    52734, 52775, '675-133-2226', 'G: 130,000 - 149,999', 9000,
    'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'I');
Insert /*+ APPEND */ into SH.CUSTOMERS_LOGGING
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
    CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
    CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
    CUST_VALID)
 Values
   (17449, 'Abigail', 'Ruddy', 'M', 1950,
    'single', '67 East Mcintosh Avenue', '83786', 'Nagoya', 51971,
    'Aichi', 52543, 52782, '183-207-2933', 'G: 130,000 - 149,999',
    9000, 'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'A');
Insert /*+ APPEND */ into SH.CUSTOMERS_LOGGING
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
    CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
    CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
    CUST_VALID)
 Values
   (21005, 'Abigail', 'Ruddy', 'M', 1946,
    'married', '77 Bradford Avenue', '52773', 'Santos', 52293,
    'Sao Paulo', 52735, 52775, '129-379-7148', 'G: 130,000 - 149,999',
    3000, 'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'I');
Insert /*+ APPEND */ into SH.CUSTOMERS_LOGGING
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID, CUST_STATE_PROVINCE,
    CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL, CUST_CREDIT_LIMIT,
    CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM, CUST_VALID)
 Values
   (24561, 'Abigail', 'Ruddy', 'M', 1978,
    '77 North Packard Avenue', '37400', 'Yokohama', 52526, 'Kanagawa',
    52634, 52782, '689-236-7611', 'G: 130,000 - 149,999', 7000,
    'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'A');
Insert /*+ APPEND */ into SH.CUSTOMERS_LOGGING
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
    CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
    CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
    CUST_VALID)
 Values
   (28116, 'Abigail', 'Ruddy', 'M', 1949,
    'single', '87 West Coshocton Avenue', '71349', 'Haarlem', 51644,
    'Noord-Holland', 52683, 52770, '208-194-6025', 'G: 130,000 - 149,999',
    11000, 'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'I');
Insert /*+ APPEND */ into SH.CUSTOMERS_LOGGING
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
    CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
    CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
    CUST_VALID)
 Values
   (31671, 'Abigail', 'Ruddy', 'M', 1951,
    'married', '97 Sagadahoc Avenue', '55056', 'Bolton', 51205,
    'England - Greater Manchester', 52590, 52789, '391-613-8000', 'G: 130,000 - 149,999',
    1500, 'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'I');
Insert /*+ APPEND */ into SH.CUSTOMERS_LOGGING
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID, CUST_STATE_PROVINCE,
    CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL, CUST_CREDIT_LIMIT,
    CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM, CUST_VALID)
 Values
   (35227, 'Abigail', 'Ruddy', 'M', 1948,
    '97 South Geneva Avenue', '81402', 'Lelystad', 51793, 'Flevopolder',
    52599, 52770, '587-269-1680', 'G: 130,000 - 149,999', 9000,
    'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'I');
COMMIT;

--now run some inserts to the nonlogging table
Insert /*+ Append*/ INTO SH.CUSTOMERS_NO_LOGGING_DOUBLE
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
    CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
    CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
    CUST_VALID)
 Values
   (49671, 'Abigail', 'Ruddy', 'M', 1976,
    'married', '27 North Sagadahoc Boulevard', '60332', 'Ede', 51442,
    'Gelderland', 52610, 52770, '519-236-6123', 'G: 130,000 - 149,999',
    1500, 'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'I');

Insert/*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID, CUST_STATE_PROVINCE,
    CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL, CUST_CREDIT_LIMIT,
    CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM, CUST_VALID)
 Values
   (3228, 'Abigail', 'Ruddy', 'M', 1964,
    '37 West Geneva Street', '55406', 'Hoofddorp', 51669, 'Noord-Holland',
    52683, 52770, '327-194-5008', 'G: 130,000 - 149,999', 7000,
    'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'A');
Insert /*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
    CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
    CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
    CUST_VALID)
 Values
   (6783, 'Abigail', 'Ruddy', 'M', 1942,
    'single', '47 Toa Alta Road', '34077', 'Schimmert', 52297,
    'Limburg', 52646, 52770, '288-613-9676', 'G: 130,000 - 149,999',
    11000, 'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'I');
Insert /*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
    CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
    CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
    CUST_VALID)
 Values
   (10338, 'Abigail', 'Ruddy', 'M', 1977,
    'married', '47 South Kanabec Road', '72996', 'Scheveningen', 52296,
    'Zuid-Holland', 52771, 52770, '222-269-1259', 'G: 130,000 - 149,999',
    1500, 'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'I');
Insert /*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID, CUST_STATE_PROVINCE,
    CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL, CUST_CREDIT_LIMIT,
    CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM, CUST_VALID)
 Values
   (13894, 'Abigail', 'Ruddy', 'M', 1949,
    '57 North 3rd Drive', '67644', 'Joinville', 51698, 'Santa Catarina',
    52734, 52775, '675-133-2226', 'G: 130,000 - 149,999', 9000,
    'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'I');
Insert /*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
    CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
    CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
    CUST_VALID)
 Values
   (17449, 'Abigail', 'Ruddy', 'M', 1950,
    'single', '67 East Mcintosh Avenue', '83786', 'Nagoya', 51971,
    'Aichi', 52543, 52782, '183-207-2933', 'G: 130,000 - 149,999',
    9000, 'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'A');
Insert /*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
    CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
    CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
    CUST_VALID)
 Values
   (21005, 'Abigail', 'Ruddy', 'M', 1946,
    'married', '77 Bradford Avenue', '52773', 'Santos', 52293,
    'Sao Paulo', 52735, 52775, '129-379-7148', 'G: 130,000 - 149,999',
    3000, 'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'I');
Insert /*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID, CUST_STATE_PROVINCE,
    CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL, CUST_CREDIT_LIMIT,
    CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM, CUST_VALID)
 Values
   (24561, 'Abigail', 'Ruddy', 'M', 1978,
    '77 North Packard Avenue', '37400', 'Yokohama', 52526, 'Kanagawa',
    52634, 52782, '689-236-7611', 'G: 130,000 - 149,999', 7000,
    'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'A');
Insert /*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
    CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
    CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
    CUST_VALID)
 Values
   (28116, 'Abigail', 'Ruddy', 'M', 1949,
    'single', '87 West Coshocton Avenue', '71349', 'Haarlem', 51644,
    'Noord-Holland', 52683, 52770, '208-194-6025', 'G: 130,000 - 149,999',
    11000, 'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'I');
Insert /*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID,
    CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
    CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM,
    CUST_VALID)
 Values
   (31671, 'Abigail', 'Ruddy', 'M', 1951,
    'married', '97 Sagadahoc Avenue', '55056', 'Bolton', 51205,
    'England - Greater Manchester', 52590, 52789, '391-613-8000', 'G: 130,000 - 149,999',
    1500, 'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'I');
Insert /*+ APPEND */ into SH.CUSTOMERS_NO_LOGGING_DOUBLE
   (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,
    CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID, CUST_STATE_PROVINCE,
    CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL, CUST_CREDIT_LIMIT,
    CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_EFF_FROM, CUST_VALID)
 Values
   (35227, 'Abigail', 'Ruddy', 'M', 1948,
    '97 South Geneva Avenue', '81402', 'Lelystad', 51793, 'Flevopolder',
    52599, 52770, '587-269-1680', 'G: 130,000 - 149,999', 9000,
    'Ruddy@company.com', 'Customer total', 52772, TO_DATE('01/01/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'I');
COMMIT;

At this point I had enough going on to at least start checking what was being logged. I fully expect to see the first batch of inserts and non of the second bath in the logs.

If you haven’t used DBMS_LOGMINER before, please go read this first.

I mined all 3 REDO LOGS – I’m lazy.

I now query the v$V$LOGMNR_CONTENTS view to see what’s in the logs.

  SELECT Operation, Table_name, Scn
,        Timestamp, Seg_owner, Table_space
,        Username, Session_info, Rbablk
,        Data_objd#, Sql_redo
    FROM V$logmnr_contents
   WHERE Table_name LIKE 'CUSTOMERS%'
ORDER BY 1

And the results!

I only have one record in the logs for the _NO_LOGGING_DOUBLE table, and that was a test I ran without the APPEND hint to use as a control. It’s interesting to note that Oracle doesn’t include the APPEND hint in the SQL_REDO portion of the record. Well, kind of interesting I suppose. This data would get reconstructed as desired based on our scenario, so it goes back in the database and recovery runs outside of INSERT or SQL*Loader so direct load method really has no context here.

Summary – Control Logging At the Object or Tablespace Level!

If you have LOGGING set at the object level, the user has to try really, really hard to add data to your database that wouldn’t be recoverable. Of course you may still have problems with orphaned records where foreign key constraints were bypassed, but that’s a different story.

Also, don’t freak out on Twitter before you check your code for mistakes. Just because it runs without error doesn’t meant it’s free of mistakes.

I’ve overheard the SQL Server folks talking about the challenges of support applications where the NOWAIT (or is it NO_WAIT?) hint has been deployed to make transactions and queries go faster.

This probably causes more problems than it solves, and rather trying to rehash what I’ve heard second hand, I suggest you go listen to a MSFT MCM talk about it. Brent Ozar PLF co-founder Kenda Little does an excellent overview on it here.

That got me thinking, what’s the equivalent ‘abused and misused hint’ in the Oracle world? The first thing that came to mind was /*Parallel*/ (Oracle Docs). The documentation is fairly straightforward, with the first line saying

The PARALLEL hint lets you specify the desired number of concurrent servers that can be used for a parallel operation.

So what does a developer think when they read this?

Wait a second here. My query is taking X minutes now. If I use a parallel degree Y hint, I can diving X by Y! The math here being, well if takes one process X minutes, then surely Y processes working together in concert can divide up the work and complete the task much, much faster.

Unfortunately, this is not always the case. That’s really not how it works at all to be honest. Application architects and DBAs take great care to determine what tables should be divided and conquered with parallelism. You’ll see this when a table is created with the PARALLEL option. This means you can write your queries and let the system best decide when to split the work up to multiple worker processes. One could argue then that a business user may NEVER want to use the parallel hint in their ad-hoc queries.

Parallel Can Be Bad

I’ll quote one Oracle master quoting another – in this case Tom Kyte quoting Jonathon Lewis (AskTom)

You might want to get Jonathan Lewis’s book “practical Oracle8i — building efficient databases”. I think he put it beautifully when he described parallel query as a “non scalable operation”. It does not scale up with users (more users, less scalability). It gives you the ability to consume the ENTIRE machine with your one query. It is designed to use EVERYTHING, it is very selfish.

Parallel Can Be Awesome

Greg Rahn (Twitter), a data performance geek for Oracle Corp, gives us an excellent scenario where parallel degree execution makes a lot of sense – data warehouses. (blog). One of my favorite lines, Greg confirms what many of us already know “PX is not, however, “go fast” magic pixi dust for any old operation.” With that out of the way, he goes on to assure folks that we shouldn’t be afraid to use it either though. Greg advocates using this when you’re working with really large datasets in a data warehouse, especially on SELECTs and Create Table As Select (CTAS).

As a matter of fact, Oracle gets very explicit on using parallelism when running CTAS statements.

If you think this is a lame post because I’m just quoting others and sharing web links, then I can’t argue with that. I’ve heard people talk about parallel being a pain to support when folks don’t understand it, but I’ve never had to be that guy supporting it. If you’re a Oracle developer though, please don’t add hints to your query without fully investigating what that hint does. Just reading the first page of the Oracle Docs might get you in trouble.

What Hint Do You See Over or Misused?

/*+ FIRST_ROWS*/ anyone? Or maybe /*+ INDEX*/? That’s right, sometimes Oracle doesn’t want to use an index, and it doesn’t make sense. Also, there are things more important than getting your data base as quickly as possible.

And I know that some of you will chime in that ANY hint is a bad hint. I am happy to have hints available when the occasion calls for one, but I don’t try to solve every problem with a hint either.

My employer has a freeware utility you can run on your network to find and document instances of SQL Server. One of SQL Server’s primary advantages is also one of its primary disadvantages – you can spin one up in a manner of minutes.

To grow deals at a company buying our SQL Server software, we’d offer to document their servers for them. They would claim they only had 10-15 instances of SQL running. We’d run our utility and return with closer to 100-150 instances. Oops!

I was recently asked if we had a similar tool for Oracle. I was a bit taken aback, and my gut reaction wasn’t a very good one.

Wait a sec, most orgs know EXACTLY where they have Oracle running

To answer the question though, “No, we do not have such a tool. This is not a primary concern for our Oracle customers.” But, I was curious and really wanted to find out if there was such a tool, or if DBAs worried about such matters.

A quick Google proves mostly unfruitful. I found one promising lead via a blog post in 2005 from Oracle security expert, Pete Finnigan. Unfortunately going to the download page prompts me for a login, although to be fair it is over 6 years old.

So, am I naive to think that it’s much harder to sneak an instance of Oracle database onto a network? A quick Twitter conversation sprouted up, and apparently it does happen. Or at least, production DBAs are rudely awakened to the presence of unknown databases popping up. Oh, and people normally find these databases when the owner sticks their neck out and asks for help.

What do you do after you find them?

To the victor go the spoils? Do you really want ownership of a database that probably hasn’t been well maintained? In terms of job security I would think having more resources in your domain would be a good thing. On the flip side, most Oracle DBAs aren’t exactly short on work.

A lot of sites have an Enterprise agreement with Oracle that allows them to deploy an unlimited number of databases. Users hear this, and then decide to build a local application using Oracle, and the word never makes it to the IT Support team. The company starts to rely on the service this new app is supplying, and all of a sudden IT is now responsible for another database it never even knew about!

Again, this is a scenario I hear over and over in the SQL Server world. Usually it involves 3rd party vendor apps that use SQL Server. But is this problem just as rampant in the Oracle world? Share your horror stories, and better yet, let us know how you audit your Oracle installs. Have a script, use a tool? Don’t be greedy now!

It’s one of the most iconic scenes in American film – Roy Schneider’s character is chumming the water and sees the shark they are hunting. The massive size of the fish causes him to comment, “We’re gonna need a bigger boat!” It’s shark week, and I was struggling with inspiration for blog posts, so apologies if this metaphor is stretched beyond your imagination.

When Oracle (and SQL Server) need to do some expensive math operations, primarily SORTS, it needs a lot of disk space. When you want to join or order data, the database engine gets to do all the low-level yucky math stuff that you ignored in college (or at least I did). Instead of going to the OS for disk space to use this, the database has its own TEMP tablespace. In Oracle you can even have multiple tablespaces set aside for TEMP operations. But my questions are, ‘How much is enough?’ and ‘How much is too much?’

I had never really considered having too much of anything as a problem before, but an offhand tweet last week by @erinstellato gave me pause. My current database has a 5+GB TEMP tablespace. The image it runs on only has 30GB of space and runs pretty full most of the time. So maybe my boat is really way too big? Or, maybe I can get greedy and steal back some of the space for my OS.

Instead of researching and finding out the best way to determine TEMP size, I’m just going to re-size this to 2GB and see what happens. I’ll wait and watch for

ORA-01652: unable to extend temp segment by string in tablespace string

I’ve additionally enabled auto-extend with 512MB increments.

How Do I Properly Size My TEMP Tablespace?

Is there a magic rule or ratio based on the size of your database or the number of transactions or users running amok in your system? Back in the day when I was managing customer databases, we had a script to build the database — which included TEMP — and then going forward we would just add space as necessary. Is this really the best way? I’m guessing not. So let’s jump to the innerwebs to see what the experts have to say!

• Creation and Maintenance of TEMP (Oracle Docs) – good stuff, but nothing about sizing guidelines

And that’s all I could really find. Multiple Google’s on ‘sizing recommendations’ and ‘how to size’ really didn’t give me much. So I’m waiting for real production DBAs and other people smarter than me to share their thoughts here.

I’m also happy to learn more about sizing TEMPDB too if you’re a SQL Server type!

I’ll be happy to report back on the ‘magic number’ I end up with on my own personal Oracle 11gR2 database. Hopefully I come up with that number based on trial and error or science – and not on guessing. But I’ll be happy with a number that doesn’t starve the OS or the database. I think a lot of folks really only care about the end result and not how they got there. Is that bad when it comes to managing TEMP?

I found a few good links for stories and advice around being sure to tune your queries and having proper stats on your objects so you don’t slam TEMP. But how do you know when you’ve reached the limit of database tuning? Or

Is Your Server So Big You Can Afford to Throw Tons of Space at TEMP and Not Worry About It?

Someone asked me yesterday where do I get my blog topics and ideas from. Usually it’s from watching too much TV, but often it’s an actual question or topic that is raised by a co-worker or customer. Yesterday I was asked by my boss about how to find out when TRUNCATEs have been issued because

I know that truncates cannot be rollbacked from so they wouldn’t be in the redo logs

Now, in my boss’ defense, this is mostly true.

• You cannot ROLLBACK a TRUNCATE.
• TRUNCATEs will be in the Redo Log, but there is no ‘SQL Undo’ entry for that record.

So, just because you cannot ROLLLBACK something, doesn’t mean it does not get logged.

Let’s say that your database crashes, and you need to recover it from backup. So, you take your last known ‘good’ backup and restore it. Then you ask RMAN to plow through the logs to apply all of the transactions that occurred from the backup to the point right before the crash.

Do you think it would be important to be able to ‘replay’ any TRUNCATEs that occurred in that timeframe?

So, just because Oracle doesn’t let you ROLLBACK a TRUNCATE doesn’t mean that Oracle doesn’t LOG the TRUNCATEs.

Now, also in my boss’ defense (just in case he stumbles across this blog) – I have heard this claim uttered many, many times. So, there are lot of people out there who have heard this before and have probably passed it on.

This happens in the SQL Server world too – although you can ROLLBACK TRUNCATEs over there – so it’s not just us Oracle folks who suffer from this. Go read myth #19 from the geniuses over there at SQLSkills,
‘Myth #19: A TRUNCATE TABLE operation is non-logged.’

Ok, so now that we know all database user transactions are logged, how do we go find them?

Introducing LogMiner

Oracle gives us an interface to ‘mine’ the online and archived transaction logs. You can read ALL about it here (Oracle Docs). I will say this – please be very, very careful with this utility. I’ve had a co-worker play with it and she corrupted her test database. So, once you have the basics down, now we can go digging for our TRUNCATEs.

First, let’s do a TRUNCATE

--create table
create table scott_emp_trunc as select * from scott.EMP;
--truncate table
truncate TABLE SCOTT.EMP_TEST;

Now I am going to dive into my ONLINE redo logs using LogMiner and query V$LOGMNR_CONTENTS. This view gets populated once you’ve mined your log(s).

For example:

Select
 SCN,
 timestamp "Time Stamp",
 session# "Session #",
 sql_redo "SQL Redo"
From V$LOGMNR_CONTENTS
Order by 1

Instead of querying it ‘manually’, I’m using my favorite IDE which has interface to LogMiner. I just give it a start and stop time or SCN and then I can start running my queries. I like it because the data grids allow me to do grouping around things like ‘Segment Owner.’

I have 1 record logged for ‘SCOTT’, and it is indeed our TRUNCATE!

You will notice that our UNDO SQL entry is a very boring and useless ‘NULL’

You want to know why TRUNCATE is super-duper fast? It’s partially because Oracle isn’t logging all the records that are dropped. Logging is expensive. That’s why Oracle allows us to turn logging off at the object and tablespace level (LOGGING clause – don’t do it!) You can also turn off logging for your IMPORT sessions.

The Real Question Was About Auditing

The question originated from a customer who was really wanting a method for auditing their Oracle tables. Yes, you can find TRUNCATEs that have been issued. But, it assumes you have the logs handy, and that you know how to use LogMiner. Instead, I recommend you just use Oracle’s built-in Auditing feature. It will capture everything you want it to, including TRUNCATEs. Then it’s a simple matter of querying the audit trail tables.

At least, I THINK Oracle tracks this via auditing. So I promise to blog soon proving that.

Nobody’s Going to Help You

I read an excellent blog this morning from The Coding Horror blog. The general gist is, you can’t rely on a self-help book to do your work for you. You need to work and do things for yourself. Excellent, excellent read! Anyways, I wanted to get some help yesterday and I Googled and Googled and even tweeted a few times, but I came up dry.

In less than 15 minutes this morning, I was able to answer my own question by just ‘DOING IT’ for myself. Thanks for the great read Jeff (Atwood, not me). Go follow codinghorror now.