Re-partitioning – 18

In yesterday’s note on the options for converting a range-partioned table into a composite range/list parititioned table I mentioned that you could do this online with a single command in 18c, so here’s some demonstration code to demonstrate that claim:


rem
rem     Script:         pt_comp_from_pt_18.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2019
rem

create table pt_range (
        id              number(8,0)     not null,
        grp             varchar2(1)     not null,
        small_vc        varchar2(10),
        padding         varchar2(100)
)
partition by range(id) (
         (more...)

The problem with Googling for solutions

I started to write a post, then realised I’ve already written it several times before, with the most coherent of them here.

So instead I’m going to change it up a little and tell a story.

I’m a generalist, and as you will know it’s really hard to be good at everything, so clearly there are some things I’m “not so good at”. Like most people, I use Google (more...)

RMAN Full Backup & Demo

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Full Backup

A full backup is a backup of all the database’s data which excludes free space and stores in RMAN backup pieces.  For example, if you have a newly created database with datafiles added that initial size of 10GB, the 10GB from each datafiles is mainly just free space and hence RMAN does not backup the (more...)

Re-partitioning

I wrote a short note a little while ago demonstrating how flexible Oracle 12.2 can be about physically rebuilding a table online to introduce or change the partitioning while discarding data, and so on.  But what do you do (as a recent question on ODC asked) if you want to upgrade a customer’s database to meet the requirements of a new release of your application by changing a partitioned table into (more...)

MySQL Source Installs for each RDS version

I have been doing a lot of Oracle and PeopleSoft work this year, but I am trying to continue to develop my MySQL and Amazon Web Services (AWS) knowledge at the same time. My goal is to learn some new thing about MySQL and AWS each month and then document it either on this blog or on my company’s internal web site.

This month I decided to focus on building a Linux virtual machine on (more...)

Steps to recover the standby database’s datafile using backup of primary database’s datafile

Recently we have faced an issue for one of our dataguard environment, we will discuss those steps here which we followed to fix this issue. For our Physical standby database, one of the filesystem(/u01/data/) got corrupted, due to which when we added datafiles in primary, the changes not applied on the standby and same datafile not created on standby.Also MRP process get stopped automatically. Manually recreation of datafiles on standby database also we tried (more...)

Indexing Null Values – Part 2

In the previous post I've demonstrated that Oracle has some problems to make efficient use of B*Tree indexes if an IS NULL condition is followed by IN / OR predicates also covered by the same index - the predicates following are not used to navigate the index structure efficiently but are applied as filters on all index entries identified by the IS NULL.

In this part I'll show what results I got when repeating the (more...)

Examples of using Machine Learning on Video and Photo in Public

Over the past 18 months or so most of the examples of using machine learning have been on looking at images and identifying objects in them. There are the typical examples of examining pictures looking for a Cat or a Dog, or some famous person, etc. Most of these examples are very noddy, although they do illustrate important examples.

But what if this same technology was used to monitor people going about their daily lives. (more...)

Oracle Database File Placement Best Practice & Create Database Demo

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Introduction

In order for an Oracle Database to be resilient to physical storage failures, i.e. disk failure, it is imperative that the database files are placed according to Best Practice, so to avoid lost of database in case of a storage failure.  For example, not have your database and backups on the same disk, because if (more...)

RMAN Back to Basics Series

Back in October 2017, I presented “RMAN Back to Basics” at the UKOUG Database SIG.  The intention was to go over RMAN basics but ended up more like a master class 🙂

I was asked if I could share my demos from the presentation, thus the “RMAN Back to Basics Series” was born!  Albeit a year and a half later 🙂

Enjoy!

RMAN Back to Basics Series

  1. Oracle Database File Placement (more...)

Danger – Hints

It shouldn’t be possible to get the wrong results by using a hint – but hints are dangerous and the threat may be there if you don’t know eaxactly what a hint is supposed to do (and don’t check very carefully what has happened when you’ve used one that you’re not familiar with).

This post was inspired by a blog note from Connor McDonald titled “Being Generous to the Optimizer”. In his note Connor gives (more...)

How Select Statement Works

When Oracle receives a sql query, it requires to run some pre tasks before actually being able to really run the query.Combination of these tasks is called parsing.

During parsing the below operations used to perform.

Database validate the syntax of the statement whether the query is valid or not.For example, the following statement fails because the keyword FROM is missed:

Select employee where name=’RAM’;
Select employee where name=’RAM’
*
ERROR at line (more...)

Last Week in Stream Data Integration & Stream Analytics – 21.5.2019

This is the 158th edition of my blog series blog series around Stream Data Integration and Stream Analytics!

As every week I was also updating the following two lists with the presentations/videos of the current week:

As usual, find below the new blog articles, presentations, videos and software releases from last week:

News and Blog Posts

General

The DaAnalytics Blog has moved.

The DaAnalytics Blog will re-launch on the DaAnalytics website - http://daanalytics.nl/blog. This means that the old blog on http://blog.daanalytics.nl and / or http://obibb.wordpress.nl will remain online but won't be maintained anymore. As from now all new content will be posted on the DaAnalytics website blog. #blog #daanalytics #newstart

Oracle ACE Program : How you “align” matters!

Every so often I feel moved to write something about community programs. In the past it has resulted in these posts.

After some Tweets over the weekend I felt the need to write something more.

I’ve said in previous posts that you shouldn’t aim to become an Oracle ACE. If (more...)

Being generous to the optimizer

In a perfect world, the optimizer would reach out from the server room, say to us: “Hey, lets grab a coffee and have a chat about that query of yours”. Because ultimately, that is the task we are bestowing on the optimizer – to know what our intent was in terms of running a query in a way that meets the performance needs of our applications. It generally does a pretty good job even without (more...)

Find files with errors

My students wanted a quick solution on how to find the log files that contain errors. That’s a simple line of code in Linux if you want any Oracle errors that start with ORA-:

find . -type f | xargs grep -i ora\-

It takes only a moment more to look for errors starting with ORA- or PLS-, like:

find . -type f | xargs grep -i -e ora\- -e pls\-

The latter might return (more...)

BCOUG Tech Day 19 – It’s a Wrap

This year we organized the 2nd ever BCOUG Tech Day. Last year we (the BCOUG board) decided to try and go for a full day conference for the first time and it was a success. You can read my post about it here. So it was quite obvious that we would organize another one this … Continue reading "BCOUG Tech Day 19 – It’s a Wrap"

Getting t1001 in interactive grid PK

Have you been plagued with the issue of getting values like t1001 show up in columns you marked as primary key in your interactive grid? In case you do not feel it is a good interface for your end users you may have found yourself spending quality time with Google looking for clues. I have had a couple of people asking me about this in just the last week. Googling turns up very little and mostly (more...)

Misleading Execution Plan

A couple of weeks ago I published a note about an execution plan which showed the details of a scalar subquery in the wrong place (as far as the typical strategies for interpreting execution plans are concerned). In a footnote to the article I commented that Andy Sayer had produced a simple reproducible example of the anomaly based around the key features of the query supplied in the original posting and had emailed it to (more...)