What happens when you can’t get a PL/SQL Web Toolkit to work because it only prints to a web page? That’s more tedious because any dbms_output.put_line command you embed only prints to a SQL*Plus session. The answer is quite simple, you create a test case and test it inside a SQL*Plus environment.
Here’s a sample web page that fails to run successfully …
My previous post that showed a real life troubleshooting was a hit. I guess people like to hear these kind of things, so I decided to write another post, this time a real life SQL tuning. This happened a while ago, probably about 8-9 years, but it’s an excellent example and it contains many aspects […]
I wanted to go to Hotsos Symposium for quite some time, having heard so many great topics from there for years. And every time I was a bit lazy to think about what I can talk about. Apparently I thought that everything I know is well covered elsewhere, so why would I be accepted. Plus all these complexities of budget, getting a visa, travel arrangements, jet lag, and personal matters in between. Last year, when (more...)
Here’s a possible bug (though maybe “not a bug”) that came up over the weekend on the OTN database forum. An application generating lots of “literal string” SQL was tested with cursor_sharing set to force. This successfully forced the use of bind variable substitution, but a particular type of simple insert statement started generating very large numbers of child cursors – introducing a lot of mutex waits and library cache contention. Here’s a (substituted) statement (more...)
I’ll be presenting my brand new presentation “Using image copies for Oracle database backups” at ilOUG Tech Days on 30. May in Israel.
More information about the event can be found here
Abstract of my presentation:
When databases get ever larger and larger, backing them up using traditional RMAN backupsets will quickly get unfeasible. Completing a backup requires too much time and resources, but more importantly the same also applies to restores. RMAN has always (more...)
While SQL Developer installs with a dbms_output view, some organizations close it before they distribute images or virtual machine (VM) instances. This post shows you how to re-enable the Dbms Output view for SQL Developer.
SQL Developer DBMS_OUTPUT Configuration
You need to open SQL Developer, which may look like this when the DBMS_OUTPUT view isn’t visible.
You need to click on the View menu option in SQL Developer and choose the Dbms Output dropdown menu (more...)
The Oracle AVG function is a common function in Oracle SQL. I’ll explain how to use it in this article and show you some examples. Purpose of the Oracle AVG Function The AVG function calculates the average of a series of values that you provide to it. Most of the time, this will be a […]
One of my customer is using Oracle RAC (22.214.171.124) on Windows 2012. This is might not be the most ideal setup I’ve ever seen, but it works and we’ll leave it by that.
One of the side effects of using Oracle RAC on Windows is that some of the basic things I am used to do when using RAC on Linux (for example) is behaving differently when it comes to windows. Here (more...)
My favourite format options for dbms_xplan.display_cursor().
This is another of those posts where I tell you about something that I’ve frequently mentioned but never documented explicitly as a good (or, at least, convenient) idea. It also another example of how easy it is to tell half the story most of the time when someone asks a “simple” question.
You’re probably familiar with the idea of “tuning by cardinality feedback” – comparing the predicted data (more...)
A lot of my ideas for blog posts come from questions emailed to me or asked via Twitter. Today’s blog is no different, as I was asked by someone in the community what the best method of comparing databases using features within AWR when migrating from one host and OS to another.
There is a lot of planning that must go into a project to migrate a database (more...)
RI = Referential Integrity: also known informally as parent/child integrity, and primary (or unique) key/foreign key checking.
I’m on a bit of a roll with things that I must have explained dozens or even hundreds of times in different environments without ever formally explaining them on my blog. Here’s a blog item I could have done with to repsone to a question that came up on the OTN database forum over the weekend.
A few weeks ago, I was asked to give a private session about In-Memory database vs. traditional persistent databases. I created an hour-long session explaining the basics of database systems, how in-memory systems work, and when to use each of the systems.
One of the questions I got (and answered) was about persistent (regular) database cache mechanism – and I felt this is a good opportunity to write about Oracle 12c new feature – the (more...)
Using PL/SQL to run the SQL tuning Advisor in 11gR2
In the article we will have a look at the steps to invoke and run the SQL Tuning Advisor using the interface of the PL/SQL packages provided by Oracle. The SQL Tuning Advisor can be run in an alternative way using the OEM 12c Cloud Control interface. We will not cover the OEM 12c CC interface.
The approach is simple we run a single SQL (more...)
Oracle Data Visualisation Desktop has the feature of being able to include some advanced analytics. In a previous blog post I showed you how to go about installing Oracle R Distribution on your desktop/client machine. This will allow you to make use of some of the advanced analytics features of Oracle Data Visualization Desktop.
The best way to get started with using the advanced analytics features of Oracle Data Visualization Desktop, is to ignore that (more...)