Search

Top 60 Oracle Blogs

Recent comments

Uncategorized

UTL_FILE_DIR and 18c revisited

A couple of years back (wow…time flies!) I made a video and a post about the de-support of UTL_FILE_DIR in 18c. This was good thing because the number of people opting for “utl_file_dir=*” in their init.ora file, and hence opening themselves up to all sorts of risks seemed to be large! (The post elaborates on this more with an example of erasing your database with a short UTL_FILE script Smile)

MySQL – manually updating table and index optimizer statistics

Goal was to create a empty table and poplulate it with optimizer table and index stats from a poplulated table to try and get the optimizer to give the same explain plan and costs on the empty table as the populated table. The purpose of this was to be able to modify the empty table by adding indexes quickly and cheaply and still be able to get correct optimizer costs. This worked in the case of table access but unfortunately didn’t work when bring in an index.

Create procedure to populate test table

Philosophy 23

It’s a long time since I wrote a note with the “philosophy” theme, but a recent complaint about Oracle prompted me to suggest the following thought:

“If you write SQL that is technically incorrect it’s not Oracle’s fault if sometimes the SQL completes without an error.”

Consider the following fragment of code:

Has my scheduler been turned off?

When the scheduler came into existence in Oracle 10g, there was a cool API call that could be used to temporarily turn the entire scheduler off.  That command was:


SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE')

PL/SQL procedure successfully completed.

However, a quick check of the documentation in later releases, no longer makes any reference to this attribute:

image

Monitor your lockdown profiles

Most administrators are now aware of the lockdown profile facility in the Oracle database (available since 12c). They allow a more granular level of privilege control of components and functionality within the database. I’m not going to spend more time than that on what they do – this post is just to highlight a small “gotcha” that you need to be aware of when farming out lockdown profiles to pluggable databases.

For example, perhaps one your pluggable databases should not be using UTL_HTTP and you’ve created a lockdown profile called “P1” to handle that. You would then set that profile at the PDB level via something such as:

Getting the most out of in-memory – part 2

In the previous post, I described the importance of checking V$IM_SEGMENTS to ensure that the in-memory store is populated to have confidence that you are indeed getting the benefit of the feature. But even if the store has been populated, when it comes to virtual columns, additional care is needed to make sure you can take advantage of the feature. Here is an example of how you might not be getting the in-memory benefits when you were expecting to (and how to solve it).

I’ll start by creating a table T as 20 copies of DBA_OBJECTS, and I’ll add a virtual column called OBJECT_DETAILS. The definition for the virtual column is somewhat nonsensical, being many nested SQRT calls, but this also makes it easy to check for in-memory usage (as I’ll explain shortly).

Getting the most out of in-memory

First of all … Happy New Year! This is my first post for 2020. Last year, I fell just short of 100 blog posts for the year – so this year, I’m starting early and hopefully I can crack the 100 mark! Anyway..onto the post.

The in-memory option in the Oracle database can yield some ridiculously good performance results. As someone who regularly gets to visit customers, it is always a feel good moment when you can take their data warehouse sample data and queries, which could be running in minutes or hours, slap on some in-memory parameters and watch the amazement when those queries might drop from hours to minutes, or minutes to seconds.

VirtualBox 6.1 : No compatible version of Vagrant yet! (or is there?)

VirtualBox 6.1 was released on the 11th of December and I totally missed it.

The downloads and changelog are in the usual places.

I spotted it this morning, downloaded it and installed in straight away. I had no installation dramas on Windows 10, macoS Catalina and Oracle Linux 7 hosts.

Unique all the things … including your pluggables

A quick tip just in time for Christmas Smile

I logged on to my database this morning, and things just didn’t look right. In fact, they looked down right alarming. All my objects were gone, my user account had the wrong password..It was almost as if I was connecting to a totally different database!

That’s because I was! Smile Here is how it happened:

Listener log data mining with SQL

If you take a look at the log files created by the listener, there is obviously a nice wealth of information in there. We get service updates, connections etc, all of which might be useful particularly in terms of auditing security

However, it also is in a fairly loose text format, which means ideally I’d like to utilise the power of SQL to mine the data.