The professionals at Proligence publish articles in several oracle related publications. Sometimes the published material may have some contents not very clearly described or the author felt that some additional content may help the reader even more. Since the published materials cannot be altered, the updates escape the readers. Often we receive feedback from the readers and some, we feel, should be of interest to the general audience too, for that article. This page is created to provide all such updates to an article or presentation after the date of publication or presentation.

Here is a list of all presentations and publications available for download.

New York Oracle User Group, December 2003 Meet: Fine Grained Access Control with Oracle 10g Enhancements.

Download the SQL scripts to create the users, tables, functions and policies to work on the examples here. Unzip this file to get the individual files. For the presentation and whitepaper see the downloads page.

Oracle Technology Network, Dec 1, 2003 Article "Fine-Grained Auditing in the Real World"

Thanks to Sean Hull (sean.hull@pobox.com) for this question:

Curious, if you add a RULE hint to a query, and traditional auditing is turned off, can you bypass this stuff? That would be a sort of funny anomaly.

Answer:

FGA auditing is not connected to the regular auditing - FGA can exist without the regular auditing and vice versa. In 10g, which will be covered in Part 3 of my article, both FGA and regular audit trails look the same and have the same information, a very useful feature.

Once FGA is defined, it's always on (if enabled), regardless of the optimizer mode. However, if you add a RULE hint, or your database is in RULE mode, or the rule mode is forced in the session, there will be more "false positives", i.e. FGA will be triggered even if your audit conditions are not satisfied and audit columns are not selected. Any user selecting from the table will generate a trail.

Consider this:

SQL> select /*+ RULE */ * from bank.accounts;

   ACCT_NO    CUST_ID    BALANCE 

---------- ---------- ----------

         1        123      10000

         2        123      15000

         3        456      10000

         4        456      15000

         5        456       9000

The FGA condition is where balance > 11000 (as per the article). The trail is written here.

SQL> select /*+ RULE */ * from bank.accounts where acct_no = 5;

ACCT_NO       CUST_ID    BALANCE

---------- ---------- ----------

         5        456       9000

Note, we are forcing the RULE optimizer. The row selected has balance < 11000, hence, the audit trail should not be generated, but looking at the audit trail, we can see that it is generated. The reason: the RULE hint does not allow the audit condition to be applied; the selection is audited regardless.

SQL> select * from bank.accounts where acct_no = 5;

 ACCT_NO       CUST_ID    BALANCE

 ---------- ---------- ----------

          5        456       9000

Here the rule hint is removed; and this statement does not produce a trail, since the audit condition is not satisfied.

Oracle Scene Issue 15, 2003 Article "Rediscovering SQL*Plus" Errata

In the article, in the section on Beautify your Output, the following line is missing a semicolon. It was probably introduced in the typesetting process.

SET MARKUP HTML SPOOL ON HEAD 'Employee Report' -

TABLE ‘BGCOLOR=GREEN STYLE= "FONT-FAMILY: ARIAL; COLOR=WHITE"'

                                               ^ Missing

Note the semicolon in red and indicated by a caret that was missing in the article.

Select Journal Q1, 2003 Article "Summary Table Management" Update

Many thanks to Kent Hinckley, President of Systems Plus, Salt Lake City, UT for bringing this potentially confusing words to my attention. On the article An Alternative Approach to Summary Table Management Using Advanced Queues on the 2003 Quarter 1 issue of Select Journal, on page 25 right column paragraph 6, I had written "A payload can be a simple VARCHAR2 string or an Oracle Abstract Datatype (ADT) object". While defining the payload in the procedures, the datatype of the payload must be ADT, it can't be VARCHAR2. However, to deliver a VARCHAR2 string, all you have to do is to declare a simple ADT with only one attribute of VARCHAR2. So, instance, create a type as follows.

CREATE TYPE MYPAYLOAD AS OBJECT (MYPAYLOAD VARCHAR2(2000));

Then this type can be easily be manipulated in the dbms_aq scripts. Since Oracle AQ interfaces with several object implementations like MQ Series, the payload must be of of an object datatype. Payload is stored in a physical table, so a simple overloading of procedures would not have been possible either. Rather than developing a complex solution to handle ADT as well as varchar2 datatypes for payload, Oracle decided to leave it just at that, the object type, as it's quite easy to handle the varchar2 within an object framework.

My intention of writing that was to ascertain that developing AQ based solution does not need an intricate knowledge of objects, a topic most developers and DBAs are unfamiliar with and can easily be discouraged to develop AQ processes at a slight mention of it.

If you use payloads of varchar2 type only, or mostly, here is a suggestion you might find useful. Develop a wrapper procedure that takes a varchar2 as an argument, encapsulates that to an object  and then calls dbms_aq.enequeue() with the new object value. This wrapper can then be extended for dequeue procedures as well where it returns a varchar2 value. 

TechJournal: New York Oracle User Group Journal Article on Partitioning Update

Range Partitioning on Character Fields

Space problems (in the printed article, not in the database!) prevented me from describing other types of partitioning examples. for instance, many readers emailed me whether they could create partitions based on fields that are character in nature, for instance, zip codes. the answer is a resounding yes. Here is an example table from the article, but with a char field.

create table sales

(

   trans_id        number(10),

   zip_code        char(5),

   sales_date      date,

   customer_id     number(10),

   item_id         number(10),

   business_unit   varchar2(20),

   sales_amount    number(10)

)

partition by range (zip_code)

(

   partition sales_10 values less than ('10%'),

   ...

   partition sales_99 values less than ('99%'),

   partition sales_max values less than (maxvalue)

)

Note how the value is specified with a percentage (%) sign. This is how you specify character based partition keys. If, at this juncture we decide to accept, say Canadian postal codes which are alphanumeric in nature, they will be sorted in a character based sorting mechanism, which may be affected by the national language settings. A value '1A3R9' will go to the default sales_max partition.

Segment Level Statistics Addendum

I would like to extend my thanks to Tim Gorman (www.evdbt.com) for pointing out some snippet of knowledge which complements the presentation and the paper on this topic. The statspack package in Oracle 9.2 has a undocumented snap level 7 where it collects the same segment level stats I mentioned in my presentation. Simply set it using

begin statspack.modify_statspack_parameter(i_snap_level=>7); end;  

 

However, this needs the presence of V$SEGSTAT view I discussed in the presentation. In order to populate the view, you need to set the initialization parameter STATISTICS_LEVEL to TYPICAL either in init.ora or via ALTER SYSTEM/SESSION. So, you have two choices in getting the segment level stats, using the V$SEGMENT_STATISTICS view or the STATSPACK report with snap_level = 7. I personally prefer to use the view, but it's your choice. Once again, thanks to Tim for this.

The New York Oracle User Group Presentation December 12th, 2002 New York, NY

I apologize for not being able to demonstrate the live case study as per expectation. The presentation room video projector cable decided to go to sleep at the very moment I was presenting and thus I couldn't use my laptop which also hosted the database I was supposed to use. I was forced to use the house computer and the powerpoint slides from the disk and could not demonstrate the concept due to lack of a database.

 

Also see the addendum to the presentation.

The Atlantic Oracle Training Conference (AOTC) Presentation Nov 1, 2002 at Washington, DC

I received the feedback on December 13th. The rating was mixed. I received a score of 4.63 in presentation skills and 4.91 is presentation material. Most of the audience felt that the material was new and refreshing. Thank you very much for that. However, almost all complained that the lack of sufficient number of handouts were very limiting. I understand that completely. However the handouts were distributed by AOTC, not me and they certainly did not anticipate the 100+ turnout. 

The VOUG Presentation October 10-11th 2002 Richmond, VA

Finally, I received the feedback sheets from the VOUG conference. Thank you so much for taking time to write feedback forms, especially for the participants who have made detailed comments. I appreciate your effort.

Most of the feedback is extremely complimentary in nature. Thank you for finding it useful. The average rating for the entire presentation was 4.51 and the rating for the presenter (that's yours truly) was 4.93 (Wow!). Although I am a bit disappointed with the 4.51 score in the topic, I am happy for the fact that the audience liked me as a presenter and that itself is gratifying.

I will use this page to cover a few of the feedback I received, actually only one. A participant wrote in the feedback form

"Incorrect information on ITL Waits provided. The information should have been checked before delivering"

The writer is anonymous so I can't contact him or her directly. I appreciate the honest feedback of the participant but would like to point out that the perceived incorrectness may be due to a lack on my part to communicate the concept of ITL Waits effectively, rather than the information itself being wrong. Here is a detailed explanation of ITL Waits based on my research. This is essentially what I conveyed in a 30 second deliverance on ITL waits. I hope I can dispel the participant's impression that the information was wrong during the presentation. If you still have additional questions or doubts, please do not hesitate to contact me at arup@proligence.com.

Also, please see the following addendum to the presentation.

© 2002, Proligence. Oracle is a registered trademark of Oracle Corporation