A common criticism of PLSQL is that the "original" array datatype, now called associative arrays are perfect for passing stuff back and forth to 3GL environments (for example .Net), but canno be used within SQL natively, for example:
SQL> create or replace
2 package BLAH is
3 type num_list is table of number index by pls_integer;
4 type str_list is table of varchar2(30) index by pls_integer;
6 procedure ODP_PROC(n out num_list, s out str_list);
Sadly there seems to be no concept of the Hakan factor for an IOT.
I have an application which merges into an IOT, the merge incrementally populating a swag of initially null columns, hence growing the rows in size. Some simple benchmarking shows the overhead of this versus merging into a table with pre-populated values:
SQL> create table T1
2 ( x int primary key,
3 y1 number(10),
4 y2 number(10),
5 y3 number(10),
We have a fairly common query process, where we run a MERGE command to compare a remote table to a local copy of it, as "poor mans" Golden Gate to bring that table up to date on a regular basis. [Editors note: Writing MERGE's is more complicated but a lot cheaper than Golden Gate :-)]
After an upgrade to 12c, the performance of some of the MERGE’s went very bad…and you can see what (more...)
I played a lot of volleyball in a bygone life :-) and subsequently ruined my knees to the extent that I needed surgery. I got a shock when the surgeon (after a series of x-rays and checks) said to me: "Of course, we’ll only know once we’re in there".
So here’s a body part (a knee) that’s had hundreds of thousands of years to evolve, so you’d expect that knees are pretty much the same (more...)
We did a "real" upgrade to 12c this weekend, where "real" means a production system, as opposed to my laptop, a play VM etc etc :-)
It all went relatively smoothly except for one interesting thing, that I can’t 100% say was caused by the upgrade, but it would appear to be the case.
After the upgrade, our scheduler external jobs started failing. A quick look in the alert log revealed:
Sun Jun 29 (more...)
In the previous post, I pontificated about triggers that "lock you in" to having them fire, which can create dramas when it comes to doing data patching.
Maybe you can design your application around this, but if you cant, the last thing you want to be doing is having to take an outage so that you can disable the trigger whilst you do your data maintenance. Ideally you want the trigger to fire (more...)
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
SELECT MY_SEQ.NEXTVAL INTO (more...)
I found this hilarious
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
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;
SQL> alter system enable restricted session;
SQL> select logins from v$instance;
SQL> alter system disable restricted (more...)