Some people hate triggers, some people love triggers…
I am not that opinionated on them in either direction, but one thing I do hate, whether it be a trigger or not, is dumb code. And today’s post just happens to be about dumb code in a trigger.
Consider this simple trigger (you see these everywhere pre 12c):
CREATE OR REPLACE TRIGGER MY_TRG BEFORE INSERT ON MY_TABLE FOR EACH ROW BEGIN SELECT MY_SEQ.NEXTVAL INTO :NEW.MY_PK_COL FROM DUAL; END; /
Seems harmless enough…and I’m not talking about whether we should be using ":new.col := seq.nextval", because under the covers it will do a select-from-dual anyway.
The reason the code is really dumb…is you are totally screwed if you need to some data maintenance…
Consider the following scenario:
About year ago or more, Oracle came out with a way to create thin clone copies of a database in EM 12c called “Snap Clone”.
I was helping a customer debug some external table load problems. They are developing some code to do massive inserts via external tables. As the code was being tested, we saw a fair number of tests that were doing simple queries of an external table. I expected to see “external table read” wait events, but was surprised when we saw more “external table write” wait events than reads.
I thought this was due to writes to the “log” file and possible “bad” file, but I had to be sure. I searched the docs but could not find reference to this wait event. I specifically was seeing the following:
Performance testing requires full, fresh data
Many organizations don’t even attempt to test performance until very late in their development cycle because it is only in the UAT (or equivalent) environment that they have a full copy of their production data set. Errors and inefficiencies found at this stage are expensive to fix and are often promoted to production due to pressures from the business to meet release schedules.
Confio software is hosting a live discussion on twitter tomorrow Tuesday April 15 at 12pm PST on the subject of Oracle performance.
I’ll be online answering performance questions and have invited many other friends to participate. Some friends who’ve said they’ll be there are
Participation and tracking of the discussion can accomplished by either posting with and following along with the #datachat hashtag.
I found this hilarious
SQL> startup ORACLE instance started. Total System Global Area 1469792256 bytes Fixed Size 2402776 bytes Variable Size 536872488 bytes Database Buffers 922746880 bytes Redo Buffers 7770112 bytes Database mounted. ORA-19821: an intentionally corrupt log file was found
Really ? I intentionally corrupted my log file ? I dont think so !
Another big public username and password leak…
Some good reading on how it was done, and thus ensuring your code isn’t prone to SQL injection here:
Once you get into pluggable database territory, you might need to check your usage of "alter system enable restricted session", because unless you’ve patched, there’s a little bug which lets you enable restricted session, but wont let you get out of it ! :-)
SQL> alter session set container = pdb12; Session altered. SQL> alter system enable restricted session; System altered. SQL> select logins from v$instance; LOGINS ---------- RESTRICTED SQL> alter system disable restricted session; alter system disable restricted session * ERROR at line 1: ORA-65144: ALTER SYSTEM DISABLE RESTRICTED SESSION is not permitted
You can get out of the predicament, by force opening the pluggable database as shown below, but probably best to look at the latest 12c PSU, which contains a fix (unverified)
I had the pleasure of being a guest of the Norwegian User Group for the second year in a row on their famous cruise conference at the start of April. For a relatively ‘small’ conference (around 300-400 people), the array of quality speakers the group manage to get is always astounding. Martin Nash, Cary Millsap, Tim Hall, Bryn Llewellyn, Martin Bach, Jeff Smith, Doug Burns to name just a few. I gave a few talks which (seemed :-)) to be well received by the audience.
The conference runs like clockwork, and Oslo is a beautiful city to spend a day wandering around sampling the cuisine. The only real challenge is the 45min timeslot for papers, whereas as most places I’ve spoken at allow 50-60 mins, so you’re presented with the tough choice of cramming your existing content into 45 mins, or deciding what must be pared out.
Maximum manageable storage per VM by ESX version
Note that the 60TB limit for 5.0 and 5.1 requires Update 1. Without this the limit is 24TB.