There was Twitter discussion about using context variables, accessed via SYS_CONTEXT within queries. It’s an easy means of passing parameters into a view. The question that got asked was – does a sys_context variable act exactly like a bind variable. Let’s take a look at a simple demo to explore that.
We’ll create a table with 1 row with a value of ‘a’, and 100,000 rows with a value of ‘b’. We’ll index that column, (more...)
When you are moving from another database to Oracle, in a perfect world, you’d set aside plenty of time to refactor all of your code and re-engineer your applications to get the maximum benefit out of the new database technology. But there are not many “perfect worlds” out there in the IT landscape So sometimes we have to “make do” with the time and budget constraints that are placed upon us. To (more...)
I created a new graph in my PythonDBAGraphs to show how a plan change affected execution time. The legend in the upper left is plan hash value numbers. Normally I run the equivalent as a sqlplus script and just look for plans with higher execution times. I used it today for the SQL statement with SQL_ID c6m8w0rxsa92v. It has been running slow since 10/11/2016.
Since I just split up my Python graphs into multiple smaller scripts (more...)
I recently had an issue where a database with 240GB SGA (1 huge shared memory segment) configured with hugepages on a system with 512G RAM was suddenly becoming instable and new logons were denied with these error message:
ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
Linux-x86_64 Error: 22: Invalid argument
Additional information: 2667
Additional information: 1736718
Additional information: 215016800256
This was strange because ipcs -a showed all shared memory segments (more...)
VirtualBox 5.1.8 has burst through the chest of it’s host organism.
Downloads and changelog are in the usual places. It’s a maintenance release, so there are a bunch of bug fixes in there.
I’ve installed it on macOS (Sierra), Windows 7 and Oracle Linux 6 with no problems.SO far no dramas.
was first posted on October 19, 2016 at 2:19 pm.
©2012 "The (more...)
I want you to imagine that you are about to run a race. You have your trainers on, your pre-race warm up is complete and you are at the start line. You look to your right… and see the guy next to you, the one with the bright orange trainers, is hopping up and down on one leg. He does have two legs – the other one is held up in the air – he’s just choosing to (more...)
The Critical Patch Update for October 2016 was released on October 18th, 2016. Oracle strongly recommends applying the patches as soon as possible.
Visit the URL below for more updates
Even in the new world of stateless web applications, from time to time we still come across the “lunch break locking” problem. That is, someone or something, commences a transaction which locks some rows, and then their session goes idle, without committing or rolling back the transaction. The metaphor commonly used was the “someone locks a row in a table and then goes out for lunch”, with them being totally oblivious to (more...)
It’s been over two years, but I’m finally going back to my second home. I’m presenting at BGOUG Autumn 2016 Conference.
I get back from the OTN APAC Tour, go to work for three days, then I’m off to Bulgaria. I figure if I confuse my body enough it won’t recognise how many time zones I’ve travelled through…
At the moment it looks like this is the last event I’m presenting at in 2016, (more...)
Oracle Enterprise Manager Cloud Control 13c Release 2 (13cR2) was released a couple of weeks ago. In a previous post I mentioned we were going to stop our rollout of 13cR1 agents to production and upgrade from 13cR1 to 13cR2 before we resumed.
I don’t like doing anything at work that I haven’t already tried at home, so the first step in that process was for me to do some clean installs and practice upgrades. After a (more...)
We have several posts on AskTom where it is described how to “carve up” a table into equi-sized chunks in order to (say) perform a task in parallel on that table. Here is an example of one.
Much of this has nowadays been obsoleted by the DBMS_PARALLEL_EXECUTE package, but in either instance, one key point remains: When you are generating rowids, there is no guarantee that the rowid you generate is either valid or (more...)
In my last post, I walked you through the first part of installing the Oracle Database 12c STIG compliance standards sample code. The next step to using these compliance standards is to associate your Oracle Database 12c databases with these standards. You may recall there are two separate standards in the sample code, one for multitenant databases and the other for conventional architecture databases. The process of associating the databases is the same in (more...)
If you’ve read my post about 12.2 new features and liked it, you will like this one as well. I’ve recently looked at presentations from OOW16, and found one by Keith Laker from Oracle. Keith, as principal PM for analytic SQL, talked about DWH features in 12.2, and they are great. I’d like to thanks … Continue reading More Cool 12.2 Features
I just published for free the work I’ve done for the past 1,5 years
We used to have a few problems with backups:
* Weekly full backup took 2+ days to run on the larger databases and it always took a lot of resources to run.
* To test the backups we needed to allocate the same amount of storage space and it took too long time to restore the database from backup.
* To (more...)
It’s my pleasure to be speaking at BGOUG and UKOUG again this year.
The coming Wednesday 19th Oct, I’ll be speaking at the UKOUG Systems SIG event here in London (agenda). I’ll talk about Exadata implementations I did last year and issues I encountered. Also, things you need to keep in mind when you plan to extend the system, attach it to ZFS Storage Appliance or Exalytics.
Next is all time my favorite (more...)
There are quite a lot of systems around the world that aren’t using the AWR (automatic workload repository) and ASH (active session history) tools to help them with trouble shooting because of the licensing requirement – so I’m still finding plenty of sites that are using Statspack and I recently came across a little oddity at one of these sites that I hadn’t noticed before: one of the Statspack snapshot statements was being appearing fairly (more...)
Although I normally use the job or scheduler facility to run database-centric style processes, most commonly PL/SQL programs, there is nothing to stop you from using the scheduler to gain control over tasks that might normally need to be done outside of the database. And of course, in the world of virtualization, cloud and other such innovations, the ability to initiate and perform tasks from within the database becomes increasingly useful, since often access (more...)
This is the fifth part of this installment, and before coming to comparisons to other cloud providers, in this part I show the results of the read-only test that I've already described in part three
of this series, but repeated at a later point in time. The test setup was identical and can be checked in the mentioned previous part.
The reason for running the test again was the fact that I was informed during (more...)
Enterprise Manager Cloud Control provides a centralized job scheduling system and it’s possible to control it through both web interface and EMCLI. On the other hand, it seems web interface provides more control over the past jobs, for example it’s not possible to delete a job run with EMCLI, while it’s possible to do it on web interface:
EMCLI verb “get_jobs” reports all runs of the jobs, and “delete job” can delete job and its (more...)