Search

Top 60 Oracle Blogs

Recent comments

April 2020

Will a BLOB eat all my memory?

Probably the most common usage for large objects (CLOBs and BLOBs) is to store them in a database table. In this circumstance, it feels intuitive that you won’t have a lot of concerns about memory, because the database will simply store those objects in datafiles like it would any other kind of data.

But BLOBs and CLOBs can also be declared as local variables in a PL/SQL block. We typically expect local variables to be housed within the memory for that session (the PGA). There are explicit calls in the DBMS_LOB package to create a temporary large object, but what if we do not use that API? What if we just start bludgeoning a local variable with more and more data? Is this a threat to the session memory and potentially the database server ?

Time for a test!

To see what happens when we keep growing a BLOB, I’m going to monitor two attributes for a session:

Oracle 19c Automatic Indexing: Adding Columns To Existing Automatic Indexes (2+2=5)

  In my previous post, I discussed how when the following query is run: select * from major_tom3 where code3=4 and code2=42; the Automatic Indexing process will create an index on (CODE2, CODE3) but ultimately not use the index as the CBO considers the corresponding index based execution plan too expensive. I’m going to expand […]

PL/SQL, AST, DIANA, Attributes and IDL

I have been wanting to write a detailed post about this subject for a very long time and indeed I have had some notes and screen dumps for some of this for more than 15 years for some parts of....[Read More]

Posted by Pete On 06/04/20 At 08:57 PM

Video : Online Table Move Operations in Oracle 12.2 Onward

In today’s video we demonstrate how to move, or rebuild, a table as an online operation.

This video was done as a response to some questions about the previous video on shrink operations. As usual, the video is based on some stuff I’ve written previously.

Oracle library cache cursor child generation

This post is about library cache SQL cursors, and how these are managed by the database instance.

Oracle multi-tenant and library cache isolation

This post is the result of a question that I got after presenting a session about Oracle database mutexes organised by ITOUG, as a response to the conference cancellations because of COVID-19. Thank Gianni Ceresa for asking me!

The library cache provides shared cursors and execution plans. Because they are shared, sessions can take advantage of the work of previous sessions of creating these. However, by having these shared, access needs to be regulated not to have sessions overwrite each other’s work. This is done by mutexes.

The question I got was (this is a paraphrased from my memory): ‘when using pluggable databases, could a session in one pluggable database influence performance of a session in another pluggable database’?

Friday Philosophy – Concentrating and Keeping Calm.

I was talking with a friend this week (via a webcam of course) about how he had been looking & looking at some misbehaving code for days. His team mates had looked too. It was not working and logically it should work. None of them could work it out. The problem turned out to be a small but obvious mistake.

Creating a new disk group for use with ASM Filter Driver on the command line in Oracle 19c

In my previous post I shared my surprise when I learned that calling gridSetup.sh 19c for use with Oracle ASM Filter Driver (ASMFD) required me to specify the names of the native block devices. This is definitely different from installing ASM with ASMLib where you pass ASM disks as “ORCL:diskname” to the installer.

Um, that’s great, but why did I write this post? Well, once the installation/configuration steps are completed you most likely need to create at least a second disk group. In my case that’s going to be RECO, for use with the Fast Recovery Area (FRA). This post details the necessary steps to get there, as they are different compared to the initial call to gridSetup.sh.

Make Your Business More Accessible with New Blocks

From our support sessions with customers each month, we know that growing your brand or business is a top website goal. And in this unprecedented time in which more people around the world are staying at home, it’s important to promote your products and services online to reach a wider audience and connect with more people.

Our team has been hard at work improving the block editor experience. We’ve launched six new blocks that integrate WordPress.com and Jetpack-enabled sites with popular services — Eventbrite, Calendly, Pinterest, Mapbox, Google Calendar, and OpenTable — enabling you to embed rich content and provide booking and scheduling options right on your blog or website.

Active Data Guard – limitations on ROWTYPE

I had an AskTOM question come in with an issue trying to PL/SQL on an Active Data Guard (ADG) database (which of course is running in read-only mode). The PL/SQL block seems innocuous; it does not DML and yet refuses to run:



SQL> DECLARE
  2    tst_row  dual%ROWTYPE;
  3  BEGIN
  4    null;
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access

I did a trace on the code, and made an interesting discovery, and hence the cause of the error. When we reference a ROWTYPE definition, we need a mechanism to refer to and compile against that definition of that type. Hence we temporarily “create” a type definition to handle that. Tracing the above anonymous block, you’ll see the following entries in the trace file