Oracle Midlands : Event #13

Tomorrow is Oracle Midlands Event #13.

om13

Franck is a super-smart guy, so please show your support and start the year as you mean to go on!

Cheers

Tim…


Oracle Midlands : Event #13 was first posted on January 25, 2016 at 11:41 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty (more...)

PL/SQL context switch, part 2

This is the second blogpost on using PL/SQL inside SQL. If you landed on this page and have not read the first part, click this link and read that first. I gotten some reactions on the first article, of which one was: how does this look like with ‘pragma udf’ in the function?

Pragma udf is a way to speed up using PL/SQL functions in (user defined function), starting from version 12. If you want (more...)

APEX 5.0 Beyond basics

iAdvise presented the APEX 5.0 roadshows in the first half of 2015. Besides the presentation about the new features, we provided 3 tutorials which helped the visitors to explore Oracle Application Express 5.0. Now we offer these tutorials through our blog. In this article the second tutorial is presented.

This tutorial helps you step by step through the process of migrating an application to the Universal Theme of Oracle Application Express 5.0. In this tutorial we talk about (more...)

When did I update that row?

I had a requirement the other day to understand when some data had been changed, but there was no column on the table which showed this.

So how do I find out? Well I could go off mining redo and lots of other time consuming and exotic things, but you can use the Oracle Pseudocolumn ORA_ROWSCN. This gives the SCN assocaited with each row. Well, actually it usually doesn’t. It does not show when the (more...)

(via https://www.youtube.com/watch?v=ozrbIskgyDY)



(via https://www.youtube.com/watch?v=ozrbIskgyDY)

Semijoin_driver

Here’s one of those odd little tricks that (a) may help in a couple of very special cases and (b) may show up at some future date – or maybe it already does – in the optimizer if it is recognised as a solution to a more popular problem. It’s about an apparent restriction on how the optimizer uses the BITMAP MERGE operation, and to demonstrate a very simple case I’ll start with a data (more...)

PL/SQL context switch

Whenever you use PL/SQL in SQL statements, the Oracle engine needs to switch from doing SQL to doing PL/SQL, and switch back after it is done. Generally, this is called a “context switch”. This is an example of that:

-- A function that uses PL/SQL 
create or replace function add_one( value number ) return number is
        l_value number(10):= value;
begin
        return l_value+1;
end;
/
-- A SQL statement that uses the PL/SQL function
select sum(add_one(id))  (more...)

Video: Oracle Linux Virtual Machine (VM) on Amazon Web Services (AWS)

Continuing the cloud theme, here is a quick run through of the process of creating an Oracle Linux virtual machine on Amazon Web Services (AWS).

A few months ago I wrote an article about installing an Oracle database on AWS.

I updated the images in that article last night to bring them in line with this video.

The cameo today by Joel Pérez, who was a bit (more...)

Determining What Management Packs Are Needed

In Enterprise Manager 12c, there was a little known functionality where you could check what management packs you needed to be licensed for to use a particular page in Enterprise Manager. I referred to it many times at conferences I was presenting at, and almost always people did not realize the functionality existed. Let’s see how this works.

To start with, I’m going to go to the Performance Home page for a particular database (accessed (more...)

I’ll be back!

Tons to say, zero time to write. But… I’ll be back ;)

Drop Column

I published a note on AllthingsOracle a few days ago discussing the options for dropping a column from an existing table. In a little teaser to a future article I pointed out that dropping columns DOESN’T reclaim space; or rather, probably doesn’t, and even if it did you probably won’t like the way it does it.

I will  be writing about “massive deletes” for AllthingsOracle in the near future, but I thought I’d expand on (more...)

Validate DG Broker Config for Switchover

Primary and Standby databases are running on the same server using OMF with listening on port 1530/1531

Note I have – TraceLevel = ‘SUPPORT’

+++ Check listener for DGMGRL service from PRIMARY and STANDBY.

oracle@arrow:hawksan:/media/sf_working/dataguard
$ lsnrctl status listener_las|grep DG -A 1
Service "hawklas_DGB" has 1 instance(s).
  Instance "hawklas", status READY, has 1 handler(s) for this service...
Service "hawklas_DGMGRL" has 1 instance(s).
  Instance "hawklas", status UNKNOWN, has 1 handler(s) for this service...

oracle@arrow:hawksan:/media/sf_working/dataguard
$ lsnrctl  (more...)

Issues with Oracle Secure External Password Stores

Background

In the previous article, I covered the basics of how to remove database passwords (credentials) from Oracle monitoring or backup scripts and how to instead secure them using a “Secure External Password Store” (SEPS) and Oracle Wallet.

While this mechanism is far better than putting a plain text credential in a script file, one of the more advanced options, specifically tying the files to the local host with the “-auto_login_local(more...)

Securing Oracle Monitoring and Backup Scripts

Background

Almost every DBA writes and uses various custom scripts to monitor and backup their Oracle databases. However, finding the optimal and most secure way to connect to the database is often not prioritized.

The short summary is that having your script put the username/password (credentials) in any sort of variables or command arguments is the “bad way”. Using an “Oracle External Password Store” (SEPS) or Oracle Wallet is the “better way”. Yet this technology (more...)

Log Buffer #457: A Carnival of the Vanities for DBAs

This Log Buffer Edition begins with some great blog posts from Oracle, goes through SQL Server and then ends with MySQL.

Oracle:

  • Ruby-oci8 is a ruby interface for an Oracle Database.
  • Another python graph – one wait event.
  • This article compares FBL and HDL – two of the commonly used data loading tools in Fusion HCM to highlight key differences and similarities.
  • Better Data Modeling: Customizing Oracle Sql Developer Data Modeler (#SQLDevModeler) to Support Custom Data (more...)

Instance parameters derived from cpu_count – 12.1.0.2

About 6 years ago I wanted to know which instance parameters are derived from cpu_count. So it tested a 11.1.0.7 DB - in that version there 21 parameters changed based on the value of cpu_count.
Some DB versions passed by so I decided it's time for another check. This time it's 12.1.0.2 without any PSUs/patches. The machine is the same class as previous, so it took some time.

the (more...)

Presentation: Making MySQL highly available using Oracle Grid Infrastructure

I published my 2014 presentation “Making MySQL highly available using Oracle Grid Infrastructure” in Slideshare. You can view it here:

Sample code: Oracle Grid Infrastructure action script for Windows

I’m currently involved in a project where we are replacing one company’s entire hardware platform. They also have Oracle database 10.2.0.5 (that we cannot upgrade right now) and what is really unusual for me, is that this Oracle database runs under Windows (and we cannot migrate to another platform). We also decided to use Oracle Grid Infrastructure (aka Oracle Clusterware) 11.2.0.4 to implement active-passive standby server for this database. (more...)

New Features of Enterprise Manager Cloud Control 13c

For those of you that have been wondering why I’ve been so quiet of recent times, it’s because my wife and I took advantage of a trip to England for UKOUG in December to spend nearly 5 weeks touring around Europe (2 weeks of which was spent river cruising from Amsterdam to Budapest with the fabulous APT river cruises, something I can HIGHLY recommend if you want to see Europe!). But while I (more...)

Which Management Packs are Required for Using the Notifications Feature from Cloud Control?

Sending notification emails shouldn’t require any licenses right? Unfortunately it might be more complicated than it seems

As you know Enterprise Manager Cloud Control provides a web interface called web console, and (naturally) it consists of web pages that enables you to access the features of Enterprise Manager Framework. Some of these features (mostly the basic ones) are free but some of them require you to buy management pack licenses. When you’re not sure if (more...)