I’m going to OOW15 this year, my first Oracle Open World in 11 years I think. And despite the Prom Queen rejecting all my offerings, I will actually be sneaking in a presentation – which I am very happy about.
The European Oracle User Group (EOUG) get a few slots and two are being used on Sunday 25th, 13:30 – 15:15 for “More Than Another 12 on Oracle Database 12c” – 12 European experts (more...)
You’ve probably already heard about the Oracle Database Developer Choice Awards, Steven Feuerstein wrote about them here, but this is just a quick reminder to get your votes in.
This year there are five categories to vote for:
I am always a little nervous about “popularity contests”, but I would suggest you look through the finalists for each category and vote for someone if (more...)
(warning: this is a rather detailed technical post on the internal working of the Oracle database’s commit interactions between the committing foreground processes and the log writer)
After the Trivadis Performance days I was chatting to Jonathan Lewis. I presented my Profiling the log writer and database writer presentation, in which I state the foreground (user/server) process looks at the commit SCN in order to determine if its logbuffer contents are written to disk by (more...)
"Have you ever heard a blindfolded octopus unwrap a cellophane-covered bathtub?"
Norton Juster, The Phantom Tollbooth
Recently a thread in an Oracle forum I frequent asked the following question:
“… is there a way to restrict the login to database only from the application which we have made.”
That, of course, is not an easy question to answer as several possibilities emerge with none of them foolproof. Let’s look at those options.
Ever wonder why your SQL execution plan has changed? Wondering why your boss is shouting something about “traders can’t trade” or “shoppers can’t spend” or “well that’s the Airport shut!” ?
When you write SQL, Oracle runs it through the Optimiser to determine the fastest way to access the data. This access path sometimes changes without anyone noticing, sometimes with sub-optimal results. I’m giving a presentation at an OracleMidlands even (more...)
MoS has a number of “Master Note” documents which pop up from time to time while I’m checking for known problems or solutions; they get extended from time to time (and, conversely, link to some articles which are clearly no longer relevant to current versions). This is just a tidied up list of a few of the master notes that I’ve jotted down over time. The Data Dictionary overview appeared in my daily “Hot Topics” (more...)
A couple of blog post ago I announced that there is now a free version of Delphix. Here is a little more information about the installation of Delphix Express.
Delphix Express is a free version of Delphix limited to 25 GB of managed storage and 1 vCPU. Delphix Express is set up for small projects and not optimized for high throughput nor performance testing.
Landshark is a set of 2 virtual machines (more...)
Following on from the last post, I’ve brought my NFS RAC stuff up to date also.
I noticed I had not done a RAC install using NFS on Oracle Linux 6, so I threw that in for good measure too.
Just as a little (more...)
A colleague of mine asked about the several ways of saving XML content, I knew,…
Prior to 18.104.22.168 version, whenever container database was restarted,the pluggable databases within the container database remained in MOUNT state. Startup trigger were written to open the database in READ-WRITE/READ-ONLY mode.Starting from 22.214.171.124, this can be done with PDB save state feature
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ----------------- ---------- ----------
2 PDB$SEED READ ONLY NO
3 ALPDB READ WRITE NO
Lets create a new PDB
As you may know, not indexing foreign key can have a huge impact on lock behavior and hurt database scalability (Not only with Oracle).
But there is also some rules of thumbs (coming from Asktom) when non indexing the foreign key is considered as reasonable.
So, when do you NOT necessary need to index a foreign key ?
In general when the following conditions are met:
o you do NOT delete from the parent table. (especially with (more...)
Yep, its free, its cool and a lot of fun. During Oracle OpenWorld 2015, the…
Ever look at a screen’s output and get that puckered feeling in the pit of your stomach? If you have been working in this profession for any amount of time, you know the feeling I’m talking about. The feeling that makes you think you would rather be living in Montana making woodcarvings at a roadside stand than being a DBA. I’ll be taking a somewhat lighthearted look at the perils of our profession and discuss (more...)
I had an interesting encounter with latch: cbc contention early this week. During my oncall I received page for Load of 206.81 exceeded threshold of 150. After I logged into server , the server load average was continously increasing and all the top PIDs were of oracle processes. After logging into database, I saw multiple sessions waiting on latch: cache buffer chains wait event
load average: 258.52, 244.27, 226.15
select username,sql_id,event,count(*) (more...)
As part of ongoing security reviews I wanted to determine if all database links on production systems were in use. That is not very easy to do and this article is a listing of some of the options I have considered to get that information and how it is now possible from 11GR2 onwards.
The first option was to look and see if auditing can be used. The manual states “You can audit statements that (more...)
Following on from my earlier posting of problems with temporary table and the PL/SQL result cache (a combination which the manuals warn you against) here’s another problem – again, to a large degree, self-inflicted.
Imagine you have a complex report involving a large number of financial transactions with a need to include calculations about current exchange rates. Unfortunately the rules about calculating the appropriate exchange rate for any transaction are complex and you find you (more...)
When I am trying to drop the tablespace, I am getting below error
ORA-23515: materialized views and/or their indices exist in the tablespace
Step 1: Find the materialized views and/or their indices
SQL> set head off
SQL> set newpage none
SQL> set pagesize 9999
SQL> spool drop_materialized_view.sql
SQL>select ‘drop materialized view ‘||owner||’.’||name||’ PRESERVE TABLE;’ from dba_registered_snapshots where name in (select table_name from dba_tables where tablespace_name =’XXXXXXX’);
Step 2: Drop (more...)
Its new location
“A flashback operation does not relocate a moved data file to its previous location. If you move a data file online from one location to another and later flash back the database to a point in time before the move, then the data file remains in the new location, but the contents of the data file are changed to the contents at the time specified in the flashback.”
How cool is getting Linux. Hope soon to not look like a Windows. If they put a butterfly or shiny window in the “start menu” I’m out of the game. The “new” firewalld is cool and dynamic, but my servers are square, boring and static. Maybe if I’m using RHEL/OEL 7 in my laptop it […]
East Coast Oracle Conference was fun and filled with very good sessions and speakers. I had the opportunity to attend several wonderful sessions and present two sessions. Data Pump: Tricks, Tips and Techniques Getting Started with Oracle Multitenant Administration The presentations are uploaded to East Coast Oracle Conference site and you may download from there. […]