Blogroll event

Tom Day 3 : read consistency, transparent data encryption, dbms_advanced_rewrite

Day 3 concludes the Tom workshop in Switzerland. We started with read consistency and write consistency, with an interesting example were a single row update could make a big job run thrice slower, because of the write consistency.

Once again, Tom insist on saying that you must understand Oracle to write applications.

Tom demonstrates a few 10g features. Even if I already attended his 10gR2 new features in OpenWorld, I learnt a few interesting stuff about 10gR1, and the demo on his notebook were impressing.

Ok, let’s reveal some of those 😉

You have surely heard of flashback database, but did you ever heard of flashforward ??? Well, if you flashback, open readonly, than you can flashback to the future afterwards !

Did you know about Case Insensitive? Did you try it? By writing the correct index, setting the correct parameters (NLS_COMP=ANSI, NLS_SORT=binary_ci), you will be using a regular index where doing where ENAME=’Scott’, the execution will actually reveal an index RANGE scan.

A life-saving package I have never heard of is DBMS_ADVANCED_REWRITE. This is simply a great way of rewriting a call you cannot rewrite in the application. Each call to the server could be rewritting by the dba. A great tuning capability. Maybe I will never use that, sounds a bit too much frightening to me, but I am so glad to know about it !

Finally, the TDE (transparent data encryption) demo was educative. On the one hand, if you do not have the wallet password, even if you are the DBA, than you cannot select an encrypted column!

Does it mean that you can hide data from the dba? Tom says NO WAY. The dba can always look your bind variable, look your package functions, and catch all your datas. What he think (and I do think that too), is that you can have a non-powerfull dba, which can reset your password, create a new user, drop a datafile, but which does not need to have the DBA role. Kind of pseudo-dba.

I believe that some systems contain data that no one is able to access. Not even the database administrator. In bank, it is not the case, the DBA is trusted, he signed some contracts to not reveal datas, etc… But in justice for example, I could imagine that the dba does not have the power to select any table (so will not be granted the dba role). I have been postings on forums quite a lot of time about it. For example thread 499144.995 in metalink in 2003. But I have no real experience about “secret” data which are hidded from the dba. Just thoughts.

One more great feature I learnt and will try on monday is datapump compression. We have always been compression regular export dumps with named pipe and “compress” in unix, there is finally a way to compress datapump exports.

Tom said: “I learn something about Oracle every single day”. Well, in Tom workshop, I learnt 42 new things in 3 days.

By Laurent Schneider

Oracle Certified Master

2 replies on “Tom Day 3 : read consistency, transparent data encryption, dbms_advanced_rewrite”

thanks for comment. I appreciate.

I also notice that it is the default to compress metadata in 10.2.

I made a few tests with a fairly empty database

1728K datapumpcompressed.dmp
3064K datapumpnotcompressed.dmp
468K expcompressed.dmp
2432K expnotcompressed.dmp

still sounds the disk requirement for a full export are much higher than the old “named pipe” solution (mknod p p; compress <p >expcompressed.dmp; exp file=p)

Leave a Reply

Your email address will not be published.