Search

Top 60 Oracle Blogs

Recent comments

February 2020

ROLLBACK TO SAVEPOINT;

By Franck Pachot

.
I love databases and, rather than trying to compare and rank them, I like to understand their difference. Sometimes, you make a mistake and encounter an error. Let’s take the following example:
create table DEMO (n int);
begin transaction;
insert into DEMO values (0);
select n "after insert" from DEMO;
update DEMO set n=1/n;
select n "after error" from DEMO;
commit;
select n "after commit" from DEMO;

The “begin transaction” is not valid syntax in all databases because transactions may be started implicitly, but the other statements are valid syntax in all the common SQL databases. They all raise an error in the update execution because there’s one row with N=0 and then we cannot calculate 1/N as it is a math error. But, what about the result of the last select?

ROLLBACK TO SAVEPOINT;

By Franck Pachot

.
I love databases and, rather than trying to compare and rank them, I like to understand their difference. Sometimes, you make a mistake and encounter an error. Let’s take the following example:
create table DEMO (n int);
begin transaction;
insert into DEMO values (0);
select n "after insert" from DEMO;
update DEMO set n=1/n;
select n "after error" from DEMO;
commit;
select n "after commit" from DEMO;

The “begin transaction” is not valid syntax in all databases because transactions may be started implicitly, but the other statements are valid syntax in all the common SQL databases. They all raise an error in the update execution because there’s one row with N=0 and then we cannot calculate 1/N as it is a math error. But, what about the result of the last select?

maxquerylen

The view v$undostat is a view holding summary information about undo activity that can be used by the automatic undo mechanism to deal with optimising the undo retention time (hence undo space allocation). The view holds one row for every ten minute interval in the last 4 days (96 hours) and includes two columns called maxquerylen and maxqueryid – which tell you something about the query that was considered to be the longest running query active in the interval.

In this note I want to explain why the contents of these two columns are sometimes (possibly often) completely irrelevant despite there being a few notes on the internet about how you should investigate them to help you decide on a suitable setting for the undo_retention.

Ansible tips’n’tricks: defining –extra-vars as JSON

While I’m continuing to learn more about Ansible I noticed a nifty little thing I wanted to share: it is possible to specify –extra-vars for an Ansible playbook in a JSON document in addition to the space-separated list of key=value pairs I have used so often. This can come in handy if you have many parameters in your play and want to test changing them without having to modify your defaults stored in group_vars/*.yml or wherever else you stored them. If you do change your global variables, you can almost be certain that your version control system notifies you about a change in the file and it wants to commit it next time. This might not be exactly what you had in mind.

For later reference, this article was composed using Ubuntu 18.04.4 LTS with all updates up to February 3rd, 2020.

Should You Go To Oracle OpenWorld Europe? Yes!… But…

Oracle Open World (Europe) is taking place in London in February. On Wednesday 12th & Thursday 13th Oracle will be giving lots of talks on Cloud, AI, Business Intelligence, Cloud Application Development, and anything else they see as modern and trendy. Oracle Partners will be there, demo booths by Oracle where you can talk to area experts, Safra Catz will be giving a keynote and, for entertainment, there are a couple of well known guest presenter. Go see who.

Video : Multitable Inserts

Today’s video takes a look at using Multitable Inserts, which allow an INSERT … SELECT to create rows in multiple destination tables. If you’ve not heard of them, don’t be surprised as they are really new, having only been introduced in Oracle 9i. </p />
</p></div>

    	  	<div class=

Video: Troubleshooting Complex Oracle Performance Problems (Jan 2020)

I have uploaded the 3-hour hacking session video into my YouTube channel in 2 parts.
Introduction and using ASH + wait chains for troubleshooting performance spikes A case study of a complex Oracle performance problem The slides are in SpeakerDeck:
https://speakerdeck.com/tanelpoder/troubleshooting-complex-oracle-perfor... Enjoy! :-)
Note that my blog has commenting enabled again (scroll down). You will need a GitHub account to comment.

Video: Troubleshooting Complex Oracle Performance Problems (Jan 2020)

I have uploaded the 3-hour hacking session video into my YouTube channel in 2 parts.
Introduction and using ASH + wait chains for troubleshooting performance spikes A case study of a complex Oracle performance problem The slides are in SpeakerDeck:
https://speakerdeck.com/tanelpoder/troubleshooting-complex-oracle-perfor... FYI there were a few questions about using such techniques without Oracle’s built-in ASH (due to licensing and Oracle Standard Edition). You can look into the Simulated Oracle ASH (OraSASH) package and there’s also my ashtop.

Video: Troubleshooting Complex Oracle Performance Problems (Jan 2020)

I have uploaded the 3-hour hacking session video into my YouTube channel in 2 parts.
Introduction and using ASH + wait chains for troubleshooting performance spikes A case study of a complex Oracle performance problem The slides are in SpeakerDeck:
https://speakerdeck.com/tanelpoder/troubleshooting-complex-oracle-perfor... FYI there were a few questions about using such techniques without Oracle’s built-in ASH (due to licensing and Oracle Standard Edition). You can look into the Simulated Oracle ASH (OraSASH) package and there’s also my ashtop.