Search

Top 60 Oracle Blogs

Recent comments

February 2018

255 Columns

It’s the gift that keeps on giving – no matter how many problems you find there are always more waiting to be found. It’s been some time since I last wrote about tables with more than 255 columns, and I said then that there was more to come. In the last article I described how adding a few columns to a table, or updating a trailing column in a way that made the table’s used column count exceed 255, could result in some strange row-splitting behaviour – in this article I’m going to look at a critical side effect of that behaviour.

ROWNUM and ORDER BY

Just a quick revisit on an old topic so I could link to this post in a Stack Overflow discussion.

18c dbms_xplan note about failed SQL Plan Baseline

SQL Plan Baselines is a great feature for plan stability: you capture the plans that you accept. However, if the data model changes and the accepted plans cannot reproduce, the optimizer will come with a new plan. In 18c we have a note from DBMS_XPLAN when the optimization ‘failed to use SQL plan baseline for this statement’.

I create a table, with an index, and run a query on it using this index:

SQL> create table DEMO as select rownum n from xmltable('1 to 1000');
Table DEMO created.
 
SQL> create index DEMO_N on DEMO(n);
Index DEMO_N created.
 
SQL> select * from DEMO where n=1;
 
N
-
1

Scripts to Download Oracle Database 18c Documentation

In the past I already shared with you the scripts I use to download the Oracle Database documentation. The aim of this short post is to reference the scripts I just wrote for 18c.

Happy downloading as well as happy reading!

PS: I also refreshed the scripts for 12.1 and 12.2.

Can You DIG It?

As I dig deeper into Linux for the SQL Server DBAs and for Oracle HotSos, I’m digging into DIG.  Yeah, I went there with that first sentence….

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:

A look into Oracle redo, part 5: the log writer writing

This the the fifth blog in a series of blogposts about Oracle database redo. The previous blog looked into the ‘null write’ (kcrfw_do_null_write actually) function inside kcrfw_redo_write_driver, which does housekeeping like updating SCNs and posting processes if needed, this blog looks into what happens when the log writer is actually posted by a process or if public redo strand buffers have been written into. In part 3 of this blog series (the log writer working cycle) it can be seen that when a session posts the log writer, it returns from the semaphore related functions, and calls ‘kcrfw_redo_write_driver’ directly, which otherwise is called inside ksbcti.

Inside the kcrfw_redo_write_driver function, the first thing of interest is executed only when the logwriter is posted, and the kcrfw_redo_write_driver function is called directly after returning from ksarcv and ksl_exit_main_loop_wait:

18c merge partition online

One of the cool things in 18c is the ability to merge partitions without causing a service interruption.  Here’s a video demonstration of that in action:

This is just an accompanying blog post to let you grab the scripts for the demo so that you can try this yourself on livesql.oracle.com, or any of the Oracle Cloud services that will be running 18c in the near future.

Oracle Database 18c Indexing Related New Features (New Angels of Promise)

Although the recently released Oracle 18c Database is really just 12.2.0.2 under the covers, there are a few little features and enhancements that are of interest from an indexing perspective. These include: Memory Optimized Rowstore Scalable Sequences Oracle Text indexing enhancements, such as automatic background index maintenance and new optimize index options JSON Search Index […]

Duplicating a Database on ODA

Introduction

Every so often, we get a request to duplicate a database for one of our customers using an Oracle Database Appliance (ODA). The process for doing that is relatively straightforward, but there are a couple of nuances along the way so I thought I’d write it up as a blog post in case it’s of use to others. Obviously, I have obfuscated any customer-specific information to protect their identity.

Configuration

The first nuance is to understand what database is being used as the source for the clone. Generally, a request for cloning will be something like this:

“We are creating a new environment which needs a new database to be set up. Please copy P1_SRV_T and restore as P1_SRV_F”.