Default Size of a CHAR Column

If you do not specify a size for a CHAR column, the default is 1. You can see what I mean in the example below, which I tested on Oracle 11.2:

SQL> create table t1
  2  (c1 char,
  3   c2 char(1))
  4  /
 
Table created.
 
SQL> desc t1
Name                       Null?    Type
-------------------------- -------- ------------------
C1                                  CHAR(1)
C2                                  CHAR(1)
 
SQL> 

However, if you rely on defaults like (more...)

VARCHAR and VARCHAR2

If you create a table with a VARCHAR column in Oracle 11.2, Oracle sets it up as a VARCHAR2:

SQL> l
  1  create table t1
  2  (c1 varchar(1),
  3*  c2 varchar2(1))
SQL> /
 
Table created.
 
SQL> desc t1
Name                       Null?    Type
-------------------------- -------- ------------------
C1                                  VARCHAR2(1)
C2                                  VARCHAR2(1)
 
SQL>

According to a book I am working through, this has been the case since Oracle 8. However, (more...)

MOS “log file parallel write” reference note updated

Last year, I spent some time researching redo log related performance problems, which resulted in a mini-series, including one post devoted specifically to one previously unknown scenario of excessive log file sync waits.

I am happy to announce that a service request opened on the back of this research resulted in the MOS note on “log file parallel write” wait event (Doc ID 34583.1) having been updated with a general description of this scenario (more...)

Understanding enhancements to block cleanouts in Exadata part 1

Travel time is writing time and I have the perfect setting for a techie post. Actually I got quite excited about the subject causing the article to get a bit longer than initially anticipated. In this part you can read about block cleanouts when using buffered I/O. The next part will show how this works using direct path reads and Smart Scans.

The article ultimately aims at describing the enhancements Exadata brings to the table (more...)

Return of the Disasters – OUG Ireland 2015

In just under a month I’ll be off to Dublin for the 2015 OUG Ireland conference. It takes place on Thursday the 19th of March. I’m doing my favorite presentation to present, on I.T. disasters I have witnessed and what you can learn from them (so now the title of this blog makes sense and maybe is not as exciting as it suggested).

not_the_best_thing_to_drop

I used to do this talk once or twice a year (more...)

Exadata 12c New Features RMOUG Slides

I've finally gotten around to post my RMOUG Slide Deck on Slideshare. Hopefully this is helpful to folks looking at new features in Exadata.

Running two oracle installations from the same terminal

Two posts from me on the same day. The other one about Datapatch is about a brand new utility in 12c and is probably new to most people. This post caused mixed reactions when I mentioned it at work last week. Some people laughed at my naivety in not knowing about it, others took the same view as me and were interested to hear about it as it may prove useful one day.

A colleague (more...)

Issue with Datapatch – AKA SQL Patching Tool after cloning a database

There have been a few changes in the way patches are managed and monitored in 12c and whilst looking at this I found a potential problem that might occur when you clone or copy databases around, or even build them from a template file.

Firstly when you apply a PSU and run an opatch lsinventory command you now see a description of the patch rather than just a patch number – here showing that PSU (more...)

RMOUG Training Days 2015

Yet again, it was a fantastic time at the RMOUG Training Days 2015 conference, as it has been every other year I have attended it. That is in no small measure due to the incredible work of the organizing committee, and in particular the Training Days Director, my colleague Kellyn Pot’Vin Gorman of dbakevlar.com fame. For me personally, the travel to get to Denver Colorado was somewhat more daunting than in previous years (see (more...)

Final version of RMOUG 2015 presentation and script files are here …

Wow I had a great time presenting at RMOUG 2015.  The room was absolutely packed and I felt presentation went pretty well ( so let's see what the evaluation numbers show ).

Here is a link (Final version presentation and supporting files )  to zip file containing the final version of the presentation as well the script and sql file shown at the end of the presentation.  Please let me know if (more...)

Oracle Manual Standby – Applying Log

If you are running Oracle EE, there are many books on Data Guard. However, for Oracle SE and manual standby, have fun searching.

I wanted to get a better understanding of registering logfile.

Depending on how the standby environment is monitored, not registering logfile may yield incorrect results when checking standby lag.

When logfile is not registered, v$archived_log is not updated.

STANDBY: check status

[oracle@armor:hawk:/home/oracle]
$ sqlplus / as sysdba @stby.sql

SQL*Plus: Release 11. (more...)

Data Guard Logical Standby – what does it mean?

With Data Guard, you have the choice between Physical and Logical Standby databases. Let’s see the differences! My demo starts with a Physical Standby, that is then converted into a Logical Standby (therefore the name of the database):

[oracle@uhesse1 ~]$ dgmgrl sys/oracle@prima
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration;

Configuration -  (more...)

The Database Protection Series- Vulnerability Assessments

This is the fourth article of a series that focuses on database security.  In my introduction, I provide an overview of the database protection process and what is to be discussed in future installments.   In last month’s article, we finished our discussion of the most common threats and vulnerabilities.  In this latest installment, we’ll review the database vulnerability assessment process.  We’ll begin by learning how to perform an initial database vulnerability (more...)

Learning for free – UK User Group Meetings Coming Up

There are a few user group meetings coming up in the UK over the next week or two.

Note, you need to register to attend any of these, follow the links.

First is Club Oracle London, which are evening sessions held in London with 3 talks plus free beer and pizza. The next meeting is Thursday Feb 26th at 103a Oxford Street, kicking off at 18:30. You can register for this free event here and (more...)

12c Parallel Execution New Features: Hybrid Hash Distribution – Part 2

In the second part of this post (go to part 1) I want to focus on the hybrid distribution for skewed join expressions.

2. Hybrid Distribution For Skewed Join Expressions

The HYBRID HASH distribution allows to some degree addressing data distribution skew in case of HASH distributions, which I've described in detail already in the past. A summary post that links to all other relevant articles regarding Parallel Execution Skew can be found here (more...)

Oracle "read by other session" Wait Event

When a session needs to read data from disk into the Oracle buffer cache, it may have to wait for another session to finish doing the same thing. Time spent doing this is recorded as a read by other session event. I decided to reproduce this in an Oracle 12 database. First, in session 1, in red, I set up a user called Fred to create a table:

SQL> conn / as sysdba
Connected.

How Many Values Can You Have in an IN List?

I have often wondered how many values you could have following an IN and I have just found out. I loaded some new data into a name and address table in an Oracle 11 database over the weekend. On Monday, a user sent me an Excel spreadsheet containing a list of almost 18000 meter point references to search for in the table. I exported them into a file, copied it to the server and used (more...)

Moving InnoDB Table Between Servers

This is a small article telling how to move a innoDB table from one MySQL server to another.

Moving MyISAM table from one server to another is very straight forward process. MyISAM table generates 3 files at file system level.

  • .frm file contains table structure/definition
  • .MYD file contains table data
  • .MYI file contains index data

We can simply copy these 3 files to another MySQL server installation under desired database and it will show up (more...)

opatch lsinventory gives “line 384: [: =: unary operator expected”

I noticed the error message when running lsinventory against a  12.1.0.2 Oracle_Home. As the command worked I didn’t think anymore of it until on the same server against an 11.2.0.1 home I got the same error message.

opatch lsinventory
 tr: extra operand `y'
 Try `tr --help' for more information.
 /app/oracle/product/11.2.0.1/dbhome_1/OPatch/opatch: line 384: [: =: unary operator expected

There is a Mos note which provides a solution (more...)

Test MySQL on AWS quickly

Using sysbench to performance test AWS RDS MySQL hardware is an easy three step  operation. 

Sysbench creates synthetic tests and they are done on a 1 mil row 'sbtest' table that sysbench creates in the MySQL database you indicate. The test doesn't intrude with your database schema, and it doesn't use your data, so it is quite safe. The test is an OLTP test trying to simulate event operations in the database as it (more...)