This past week the topic of archiving came up - the thing that everyone talks about but no-one does....
One of the topics that came up was that old partitions could be exchanged out of the main table and stored away from the main table to help in certain performance cases - we would still like access to them and the ability to switch it back in if required - we do not want it (more...)
If you don’t want to read the story, the summary for this article is:
If you create bitmap join indexes on a partitioned table and you use partition exchanges to load data into the table then make sure you create the bitmap join indexes on the loading tables in exactly the same order as you created them on the partitioned table or the exchange will fail with the (truthful not quite complete) error: ORA-14098: index (more...)
and whilst most people would take care of this with a source code control system, its also true that people might do several compilations / tests with their PLSQL source before checking it in officially to their source repository.
So the idea has some merit… and maybe we can roll our own without too much fuss. I make no claims that this is a ‘complete’ solution, (more...)
This Log Buffer Edition covers various useful tips and tricks from blogs for Oracle, SQL Server and MySQL.
pstack(or thread stack) for Windows to diagnose Firefox high CPU usage
With the ever-changing browser landscape, we needed to make some tough decisions as to which browsers and versions are going to be deemed “supported” for Oracle Application Express. There isn’t enough time and money to support all browsers and all versions, each with (more...)
Today marks the 10th anniversary of Lex de Haan passing away. Although 10 years is a long time, I think about my dear friend Lex at least once a week. Lex assisted me adapting to new teaching skills when I progressed to blindness. It was Lex his idea to use colored magnets on my classroom […]
In the previous part of this mini-series we looked at differences in multiblock read behavior for different nested loop optimization mechanisms depending on degree of ordering of the data. In this post I’ll continue to explore the subject, but this time we’ll focus on decision-making process: what factors (other than the obvious ones — like optimizer hints and/or parameters) affect the specific choice of a mechanism?
It’s interesting that we still see lots of code out “in the wild” that leans itself to taking forever. Now I’m not talking here about poorly performing SQL etc…because (at least theoretically) no matter how bad the SQL is, presumably it will finish one day, even if that day is years into the future. What I’m referring to here is the default mechanism for locking in Oracle.
I was contacted by Dev team to look into Development database where Oracle queue size kept on increasing. As per them messages were not getting cleared.They were running count on Queue table and the count kept increasing. To find out exact cause I included the msg_state in the query and found out that there were…
YesSQL Summit 2016 sponsored by O'Reilly and Axxana was held by the Northern California Oracle Users Group on January 26–28 at the Oracle conference center in Redwood City, California in conjunction with BIWA Summit 2016 and Spatial Summit 2016. The grand raffle prize sponsored by O'Reilly was a full pass to Strata + Hadoop World on March 28–31 in San Jose, California. Save 20% on Strata + Hadoop World conference passes with discount code UGNOCOUG. (more...)
Oracle has released a nicely packaged, preconfigured OEM 13c environment for us to play with inside Oracle Virtual Box. The VM comes with an installed repository database, a management server, and a local agent. Great starting place.
I know what you are thinking. Not another standby monitoring script, right?
There’s plenty of them out there already and what makes this one any different?
You are probably correct and there’s probably nothing different about this.
Having to work with environments that are inconsistent such as manual standby, dataguard without broker, dataguard with broker,
I have been obsessed to create standby SQL which can accommodate any environment and can be run from primary or (more...)
Like many of you, I started out my working life in IT as a computer programmer. Like some of you, I still am a computer programmer from time to time. I don’t think I’ve had a year of my working life when I did not do either some application development programming or some database infrastructure programming. I am constantly writing small (or not (more...)
It’s amazing how easy it is to interpret a number incorrectly until the point comes where you have to look at it closely – and then you realise that there was a lot more to the number than your initial casual assumption, and you would have realised it all along if you’d ever needed to think about it before.
Here’s a little case in point. I have a simple (i.e. non-partitioned) heap table t1(more...)
Sometimes things just happen which makes you realise that stuff you thought all experienced DBA’s knew about isn’t common knowledge. I suppose it’s a side effect of working with the same evolving product for over 25 years at dozens of clients, when the colleague you are talking to has a mere decade of experience at fewer companies (and therefore less diversity of challenge).
Today I noticed that a release was creating a table, then an (more...)
Most people already know about the very cool GATHER_PLAN_STATISTICS hint. If not, you can see an example here
But here’s a common cause of confusion when using it in SQL Plus:
SQL> select /*+ gather_plan_statistics*/ count(p) from t where x > sysdate - 30;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'))
SQL_ID 9babjv8yq8ru3, child number 0
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
NOTE: cannot fetch plan for SQL_ID: (more...)
To assist users as they plan out their upgrades and new Enterprise Manager environments, I wanted to go over a few subtle, but important changes from EM12c, 188.8.131.52 to the newest release, the much anticipated EM13c, 184.108.40.206.
One of the things you’ll notice when starting an EM12c from the command line is WHAT is started.
Typically, it’s not a good idea to have “crosscheck archivelog all;” in backup scripts and
even worse to have “delete expired archivelog all;” since any evidence will be eradicated.
$ rman target /
Recovery Manager: Release 220.127.116.11.0 - Production on Thu Jan 28 14:17:35 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: HAWK (DBID=3130551611)
We use VictorOps for our paging/notification system, and we're pretty happy with it so far. On the DBA team, we've just been using a simple email gateway to send notifications from Oracle Enterprise Manager (EM) to VictorOps. Even then, we can only send the initial notification and not really send an automated recovery without more hacking than its worth. Not a big deal, but would be nice to have some more functionality.