Dump Oracle data into a delimited ascii file with PL/SQL

This is how I dump data from an Oracle Database (tested on 8i,9i,10g,11g,12c) to a delimited ascii file:

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 24 13:55:47 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL> set timing on
SQL> select Dump_Delimited('select * from all_objects', 'all_objects.csv') nr_rows from dual;

   NR_ROWS
----------
     97116

Elapsed:  (more...)

DIY Parallelization with Oracle DBMS_DATAPUMP

Oracle dbms_datapump provides a parallel option for exports and imports, but some objects cannot be processed in this mode. In a migration project from AIX 11gR2 to ODA X5-2 ( OL 5.9 ) 12c that included an initial load for Golden Gate, I had to deal with one of those objects, a 600G table with LOB fields, stored in the database as Basic Files ( = traditional LOB storage ).

By applying some DIY (more...)

Wading Through Database Platforms

As a heterogenous company, I get a lot of questions on what database platforms are important, why and if we should invest in them.  When looking at some of the lists and some of the marketing, it can be a bit overwhelming.

Now I’m not going to promise to take all of the confusion out of the myriad of database platforms out there, but I’m hoping to help (more...)

The Flash Insider: To POC or Not To POC?

Proof of Concept?

Guest Post

I’m excited announce another guest blog written by my good friend and funny-talking American cousin Nathan Fuzi. Like me, Nate comes from a database background but joined the all-flash storage revolution back in its infancy. Which means, like me, Nate how has a little tombstone on his résumé marked Violin Memory. But even though he has since moved up to working in THE CLOUD, Nate’s experience working for an AFA vendor is invaluable. Over six years, he worked (more...)

Coming Soon: Database Star Academy Membership

The Database Star Academy membership will be launching very soon. Read on to find out more. What Is The Database Star Academy Membership? Over the last couple of years, I’ve developed several video courses and PDF guides to help database developers improve their SQL skills and their career. Up until now, I’ve made these available […]

Multitenant Enhancements in Oracle Database 12.2 (Some More Articles)

The Oracle Database 12c Release 2 (12.2) learning process continues. I’m determined to get to the bottom of all this new multitenant stuff. 🙂

Here’s the latest batch of articles.

#Oracle cutting in inspiration and new business?

Over the many years Oracle has been leading the database world, I guess they are now taking something of a wrong turn.
Let me briefly fill you in on my thoughts.

Basically I see two “minor” shifts that are significantly indicative of this:

  1. Oracle Standard Edition 2
  2. Oracle ACE Program

Okay, so you might think I am crazy, but let me try to explain.

Oracle Standard Edition 2

Sometime last year, the long expected, (more...)

Creating a RAC cluster using Ansible (part 2)

In my previous 2 blogposts i explained how to setup a Vagrantfile that deploys 2 virtualbox machines that can be used as a basis for a RAC cluster and how you could use Ansible to deploy RAC on those VM’s. In this post i want to dive a bit deeper into how i setup Ansible and why, keep in mind that this just one way of doing this.

The Github repository containing all the files (more...)

12.2 DBaaS (Extreme Edition) possible bug/issue with the DB install/setup

A few weeks ago the 12.2 Oracle Database was released on the cloud. I immediately set an account and got my 12.2 DBaaS setup. This was a relatively painless process and quick.

For me I wanted to test out all the new Oracle Advanced Analytics new features and the new features in SQL Developer 4.2 that only become visible when you are using the 12.2 Database.

When you are go to (more...)

#DOAG2016, definitely a crazy week.

#DOAG2016, the largest Oracle Community gathering in Europe. Taking place in Nuremberg, at the Nuremberg Convention Center NCC, one of the more impressive places to hold such a conference, towering 4 stories high, with a big central atrium!!
It is a huge effort to get all of this together!

In this blog-post I want to highlight some of the crazy things I experienced this week… And… I did try to follow my own schedule (more...)

Associating Oracle Database 12c Databases with Compliance Standards

In my last post, I walked you through the first part of installing the Oracle Database 12c STIG compliance standards sample code. The next step to using these compliance standards is to associate your Oracle Database 12c databases with these standards. You may recall there are two separate standards in the sample code, one for multitenant databases and the other for conventional architecture databases. The process of associating the databases is the same in (more...)

OTN Appreciation Day: PL/SQL

We are posting these blog posts today as part of the OTN Appreciation Day, a celebration for the Oracle Technology Network as suggested by Tim Hall, inspired by Debra Lilley.

The mission was not too hard: write about your favorite bit of Oracle Technology.

As a developer and a core-tech DBA and APEX enthusiast… the choice was easy! PL/SQL (apart from how it is pronounced or even written ;-)!

Why? Easy!

Not just because (more...)

#OOW16, San Francisco, looking back

In this post I just wanted to highlight a few things that have lingered with me since the 2016 Oracle Open World Experience.

Persistent DRAM
Now, here, being at home, I must admit that I cannot find very much documentation about this, but it got me thinking… A little paradigm-shift, where computers actually wouldn’t need moving parts anymore (ie. disks of any kind). Create devices that use these memory structures, quite possibly combined with flash-disks, (more...)

#doag2016 my picks and suggestions

As many conferences evolve over the years, the number of sessions on offer can easily be overwhelming. I have overheard many conference attendees wrestling with their choices for what to see and which sessions to attend.

For DOAG 2016 I have a short overview with my picks and with one or two tip-sessions. I hope this helps, though it is just my personal preference of course…

Please note that this post is based on the (more...)

Oracle Database 12c STIG Compliance Standard sample code is here!

One of the most requested features in the compliance management area has been a compliance standard for Oracle Database 12c. As most of you would know, Oracle Database 12c has been out for quite some time, and indeed Larry Ellison has just announced at OOW2016 availability of Oracle Database 12cR2 in Oracle’s public cloud offering. However, as I discussed in another post earlier this year, creating a new STIG compliance standard is not a simple (more...)

Virtual Development Server: Provide Docker images in docker registry for Oracle XE Database and others

For later creation of containers as needed in the deployment process we have first to build docker images whenever we do not find suitable one in docker hub.

For most of my development I need a oracle database. On the internet you can find a lot of Dockerfile’s for this, but no ready image. This is because for running a database you have to accept a license and this happens when you download the installation (more...)

#OOW16, San Francisco

This year, 2016, is turning out to be an amazing year again, with #OOW16 being once again on of the apices!

Looking back

After the discovery of the Oracle community in 2012, as a result of a very first trip to downtown San Francisco in 2010 for #OOW10, an amazing chain of events was set in motion. This very first introduction in the Oracle World was as ‘a mere participant’ in this awe-inspring, large (more...)

SYSAUX LOB segment for auditing bug not released in Standard Edition

Last week we were struck by an issue, which turned out to be a bite from a bug!
SYSAUX table-space had quickly filled up to the “my data-file is full”-limit, which in the end was fixed by adding a data-file.

Strange thing though, that for a very small footprint database, we now have a very big SYSAUX table-space.

Some investigation brought me to the Unified Auditing being standard active in database 12c (you can read (more...)

Oracle Database INTERVAL Partitioning and Virtual Columns

I bumped into this issue recently which prevents you INSERTING a date of 31-12-9999 into an INTERVAL partitioned table due to the fact that the LESS THAN partition value would be larger than an Oracle Date. After speaking with Jeff Moss he mentioned that we might be able to get round the problem by using a virtual

Oracle Database Interval Partition Set Up Script

This script is used to set up the test table for the following blog posts: Oracle Database INTERVAL Partitioning Date Error With 31-12-9999 Oracle Database INTERVAL Partitioning and Virtual Columns [crayon-578913d2a047f830082062/]