One of my customers asked me to check performance on his production database server but could not allow any access to the server itself. He asked if I could generate the AWR reports from his client machine and since it’s not really trivial (or hard) I created this script.
I have a confession: I love Oracle User Groups. Here. I said it.
I think that the idea of a community gathered around common professional interests and expertise are great. It’s an opportunity to meet you colleague and learn a thing or two you don’t get to work with on a daily basis from your peers. For junior DBAs it’s even more important. They have the opportunity to look into their future and see where they want to be when they get more experienced. In my opinion, networking is SUPER important!
tl;dr: ilOUG is reinstating the quarterly SIG meetings, I’m one of the organizers, join us if you’re in Israel!
For the longer version, read bellow… 🙂
In some conditions, I need to find the top N queries for a specific user in the database.
Assuming my customer is running Enterprise Edition and have tuning pack licenses, it is easy enough to pull the data off the Automatic Workload Repository (AWR).
For some reason, a lot of DBAs are not aware that the AWR report is just a report – and you can query the base table yourself to extract more information if you need it.
This is a short script I sometime find very useful – finding the top N queries for a specific user.
Here is a common question I get from junior DBAs (and operating system who tries to help by installing the Oracle Home themselves). The question sometimes sounds like this: “After we installed the new database server and changed its hostname, we try to install the Oracle Home using the runInstaller but hitting the following error: PRVF-0002: Could not retrieve local nodename. How do we resolve it?”.
Indeed, a problem – but what can it be?
Starting Oracle 10g, the old export/import tools were replaced by a newer, more sophisticated tool: the data pump.
This new tool had some advantages over the older tool – it was able to run in parallel, it has a better interface and it can be ran from PL/SQL code easily. It can even be restarted or resumed if needed. On the con side, it uses the database DIRECTORY object which means the file we’re exporting to or importing from must reside on the server.
The problem start when we try to diagnose what is going on when there is a performance issue. Since the process we use for exporting might be forked into multiple processes, we can’t really know what is going on behind the scenes.
So, how can we resolve that?
While visiting a customer, we had a conversation about the correct way to stop users from writing to code tables. The customer described his ancient logic: when he wanted to move table to a read only state, he removed the write (insert/update/delete) permissions from all of his users. That was good enough for him for years – since he started using this method way back in Oracle 8. All was well, util this week he found out that the table owner’s privileges could not be revoked – and due to a bug, someone accidentally deleted the table content…
So I asked him why didn’t they change it to read only tables and he had no idea what I am talking about.
Well, I wrote about read only table like 6 years ago (in Hebrew), but I never thought I should translate it – since it’s a fairly “old” (11gR1) feature and everybody knows about it, right?
Well, this week proved me wrong. Here is what I wrote about it years ago.