Using Read Only Tables

,

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.

Read more →

/0 Comments/by /Estimated Reading Time: 2 Minutes

Fixing Dataguard Wrong File Location Problem

When we add a new datafile to the primary environment of a dataguard setup, we need to add the datafile to the standby environment as well if we want the manged recover to continue working. We can do that manually if we want – but fortunately for us Oracle comes with a built in feature for automatically creating the file in the standby as well. All we need to do to set it up is to set the parameter “standby_file_management” to AUTO and create a conversion map for the files location using DB_FILE_NAME_CONVERT parameter.

All of this should be running well, until some Junior DBA (hey D., you know I’m talking about you.. 🙂 ) decides to create a new datafile in the non-default location.
Once that happens, the dataguard is unable to create the new datafile and the managed recovery will stop working.

This is a short guide I created years ago for how to identify and solve the problem.

Read more →

/1 Comment/by /Estimated Reading Time: 3 Minutes

Oracle Week 2016: Advanced PL/SQL Optimizing for Better Performance (slides)

,

This is the presentation for Oracle Advanced PL/SQL session I did in Oracle Week 2016, a few weeks ago.

I wrote about the Oracle Week conference in the previous post so I won’t go over that again… 🙂

This presentation was all about PL/SQL. Most of it are things that are quite some old, but for some reason people seem to miss out on them. The presentation is divided into 3 parts.

The first one is development and it takes up most of the day. We talk about complex data types, cursors and things that are into PL/SQL should probably know. In this part I also try to add some new features and stuff that people will find interesting, or else it gets really boring. The second part is about the compilation processes and the last part is about tuning.

This year presentation was quite big – around 40 people in the class and got excellent feedback: 4.97/5 for overall satisfaction.

Here is the agenda:

  • Developing PL/SQL:
    • Composite datatypes, advanced cursors, dynamic SQL, tracing, and more…
  • Compiling PL/SQL:
    • Dependencies, optimization levels, and DBMS_WARNING
  • Tuning PL/SQL:
    • GTT, Result cache and Memory handling
  • Oracle 11g, 12cR1 and 12cR2 new useful features
  • SQL Developer Command Line tool

Read more →

/0 Comments/by /Estimated Reading Time: 1 Minute

Oracle Week 2016: Oracle Database Advanced Querying 2016 (slides)

,

This is the presentation for Oracle Advanced Querying session I did in Oracle Week few weeks ago.

Since most of my readers aren’t from Israel, let explain a bit about this event. Oracle Week is by far the biggest Oracle oriented conference/event in Israel. It’s a 5 days conference that is being organized by John Bryce Education (a technology schooling company) and has around 1500 participants each year. It’s an educational event – meaning, as less promotion/sell speeches as possible.

One of the main differences between this event and other conferences is the platform: instead of having five or six 45-60 minutes sessions, back to back – the event has 10-12 different day long sessions every day, for 5 days. Each session is from 9:00 until 16:30 (with breaks, of course) so there are enough time to go over many different aspects of the same topic.

This session is one of the crown jewels. For years it was considered to be the largest session in the conference (with 60-80 attendees) and couple of years ago it landed in my laps.

This year session was very good. The participant reviews was around 4.8/5 and course material were around 4.9/5 which was awesome.  We had around 45 people in the class what smaller than last year but still the biggest session of the day.

This year agenda included some of 12.2 new features (the ones I got to actually test, not all of them) which was a big improvment on last years session.

In the agenda:

  • Aggregative and advanced grouping options
  • Analytic functions, ranking and pagination
  • Hierarchical and recursive queries
  • Regular Expressions
  • Oracle 12c new rows pattern matching
  • XML and JSON handling with SQL
  • Oracle 12c (12.1 + 12.2) new features
  • SQL Developer Command Line tool

Read more →

/0 Comments/by /Estimated Reading Time: 2 Minutes

OOW2016: Exploring Advanced SQL Techniques Using Analytic Functions (slides)

,

This is the presentation I used for the Oracle Open World 2016 – it’s about SQL and more specifically about analytic functions and other advanced techniques.

Oracle Open World was one of my biggest aspirations for a very long time. Ever since I become a junior DBA I’ve heard of this conference but never thought I’d actually get to speak there.

For the last 3 or 4 years, I’ve been submitting my papers to the Open World CfP – and was never accepted.

Last year, I started to make my appearances at the different user groups meetup in the EMEA region. In June, I had the pleasure to sit down and talk to few user group leaders. From them, I learned what I can do better in submitting my papers and what kind of session I should aim for.  I followed their advice and indeed, I was accepted to present at the Open World (on the User Group Sunday – which, in my humble opinion, is the best day of the conference – with little to no marketing agenda).

So without feather ado, here it is. My first presentation at the Oracle Open World ever. The topic was Advanced SQL Techniques (analytic functions, grouping function and so on). This presentation also present couple of Oracle 12cR1 new features (since 12cR2 went out after OOW).  This is part of a bigger presentation I usually  give in a full day (I will post it as well soon) – that presentation also include newer features from 12cR2.

Read more →

/0 Comments/by /Estimated Reading Time: 2 Minutes

Presentations-Presentations-Presentations

, ,

For the last few months I’ve been presenting a lot in various venues. I presented in Oracle Open World, HROUG conference in Croatia, DOAG in Germany, Oracle Week in Israel (3 day long presentations!), MySQL User Group in Israel and in some more private conferences and private events. I was also accepted to IOUG collaborate in Las Vegas in April and I’m planning to give some Webinars soon.

I believe that educating and sharing is pretty important, and since most of my presentations are in English (the slides, but sometimes also the session itself) – I usually keep the habit of uploading it to my SlideShare for people to enjoy it.

I was talking to a customer-friend yesterday, and he told me that he’s been reading my blog for a very long time, but he’s kind of sad he can’t see all my decks there. So, I’m taking his advise and for the next few days, I’ll post all of my presentations here – a post for each of the presentations and will add the demo scripts, wherever applicable.

Feel free to ask questions and make requests – I’d love to hear you opinion and suggestions!

All of the presentation are available for download from Slideshare.
My SlideShare link: http://www.slideshare.net/zohare.

Zohar

/0 Comments/by /Estimated Reading Time: 1 Minute