Search

Top 60 Oracle Blogs

Recent comments

Home

Oakies Blog Aggregator

Friday Philosophy – Is The Problem The Small Things?

Submitted by mwidlake on Fri, 2020-08-07 12:16

Something has been bothering me for a while. In fact, I’d go as far as to say it’s been depressing me. It’s you. Well, many of you.

What do I mean? Well I’ll give you an example. A week or so ago I went out in the car to get some shopping. A few minutes into the journey, as I go around a gentle bend, I see there is a car coming towards me – on my side of the road. I had to brake to give it space to get back over and I see it has swerved to avoid a branch in the road. As you can see in the picture, it’s not a huge branch, it covers less than one lane. I’m past it now so I go on to the shops and get my stuff.

30 minutes later I’m coming back. And I’m thinking to myself “I bet that branch is still there.” And it is. I can see it from maybe 300 meters back. The two cars in front of me barely slow down and they swerve past it. An oncoming vehicle that *I* can see coming, let alone the two cars in front of me, has to slow down for the swervers like I did. That slight bend means you get a much better warning of the obstacle from the side of the road it is on and as it is on your side, it’s really your responsibility so slow or even briefly stop, but the people in front of me just went for it. They did not care.

I did not swerve. I slowed down. And I put on my hazard lights, and stopped about 20 meters back from the branch. I double checked that no car has appeared behind me and I got out the car. In 20 seconds (including taking the snap), I’ve moved the branch off the road with no danger at all and I’m back to my car.

I know, you would have done the same.

Only no. No, you would not have.

Some of you would like to think you would have stopped and moved the obstacle.

I suspect most of you would claim, if asked, that you would have stopped and moved the branch.

And of course all of you would have slowed to avoid inconveniencing others.

But reality shows that nearly all of you would not.

As I left the scene, I was wondering how many people would have passed that branch in that 30 minutes I knew for sure this small branch had been an obstacle on the road. I’m going to let people going the other way off, as they would have to do a u-turn to come back to it, so how many people would have had to swerve past it?I know that road well, it would have been hmm, 4 or 5 cars a minute going past in one direction – certainly more than 3 cars, less than 10. So well over a hundred drivers would have seen that branch from a distance, most would have been able to safely slow and stop – and yet not one of them had. I have no idea how long the branch had been there, it was not too beaten up so maybe not long, but it could have been a couple of hours. It was easy to avoid – especially if you swerved with little concern for any on-coming traffic…

It turns out I’m the one in a hundred.

Are you thinking “well, it’s not my job to move branches of a road!”

So who’s job is it? And if you could label it as someone’s job (let’s go for someone in the “highways agency”) how do they get to know it needs doing? I don’t know about you but I see dozens of highways agency maintenance people on every journey I do, just cruising around looking for things that need doing. {sarcasm}.

When was the last time you saw something that needed doing in a public place and took the time to think about who should be told, try to contact them, get told to contact someone else, find out it’s not their job but are asked to ring Dave, who you do ring and he says thanks (before making a note to think about it, whilst probably muttering “this is not my job, I’ve got major roadworks to look after”). Hell, it’s easier to stop and move the branch.

Generally in life, in so many situations, I am constantly wondering why someone has not done X (or has done Y). Why don’t you reach for the jar in the shop the old lady can’t quite reach? Why don’t you hold the door? Why did you drop that litter when the bin is JUST THERE! That person  in front of you buying a parking ticket can’t find 10p in their purse to make the correct change? You have loads of 10p pieces… some in your hand already.

This is what is depressing me. Even though nearly everyone likes to think they are the nice person who will do a little for the common good, the reality is that most people won’t when it comes to it – but most people think we all should, and you tell yourselves you do the little things. You are telling yourself now, aren’t you? You are trying to think of the little things you have done for the common good. If you can think of a half dozen in the last month then you really are one of the good guys/gals. If you can only come up with a few…and actually most of them were ages ago… well, sorry but you are the problem.

The strange thing is that, having just insulted you all, as a group you lot are much more likely to be in the 1% than normal. Even though out of the general public not even 1 in 100 people would put in a little effort to move that branch, out of the people reading this, I’d say 10% would. Because I spend a lot of time in the Oracle user community, packed with people who give up their time, knowledge, even their holidays, to speak at conferences, help organise meetings, answer on forums, write blogs, answer questions on twitter, and all that stuff. Many of you reading this are active members of the User Community doing not just small things but often large things for the community. That’s why the community works.

To the rest of you, instead of liking to think you would move the branch or claiming you would (as everyone wants to be thought of as the nice guy/gal) just occasionally move the branch. Or pick that piece of litter up. Or do something small that cost you so little but it just would be nice if someone did it.

No one will thank you.

But you will know you did it. And you are becoming no longer part of the problem but part of the solution. I’m not asking you to give 10% of your salary to charity or give up an important part of your life, just do a bit of the small stuff.

If more of us do it, we will have a better world. If someone had moved that branch soon after it fell, I would not have had to  avoid some swerving dickhead, and the person I saw later would have not had to avoid people who could not even be bothered to slow down or stop briefly. And, in the worst case, that needless accident need not have happened. It really is as simple as spending 1 minute moving a branch.

Don’t be part of the problem, be part of the solution. It’s really, really, really easy.

 

AWS DynamoDB Local: running NoSQL on SQLite

Submitted by Franck Pachot on Fri, 2020-08-07 08:45

By Franck Pachot

.
DynamoDB is a cloud-native, managed, key-value proprietary database designed by AWS to handle massive throughput for large volume and high concurrency with a simple API.

  • simple API: Get, Put, Query, Scan on a table without joins, optimizer, transparent indexes,…
  • high concurrency: queries are directed to one shard with a hash function
  • massive throughput: you can just add partitions to increase the IOPS
  • large volume: it is a shared-nothing architecture where all tables are hash partitioned
  • key-value: you access to any data with a key that is hashed to go to the right partition and the right range within it
  • managed: you have zero administration to do. Just define the key and the throughput you desire and use it
  • cloud-native: it was designed from the beginning to run in the AWS cloud

One problem with cloud-native solution is that you need to access the service during the development of your application. This is not a major cost issue because DynamoDB is available on the Free Tier (with limited throughput, but that’s sufficient for development). But users may want to develop offline, on their laptop, without a reliable internet connection. And this is possible because Amazon provides a downloadable version of this database: DynamoDB Local.

Difference

The most important thing is that the API is the same as with the cloud version. For sure, all the partitioning stuff is missing in the local version. And I have no idea if the underlying data format is similar or not:

Modifying Scheduler Windows

Submitted by connor_mc_d on Fri, 2020-08-07 04:49

There are a few possible reasons why you might be one of those people why you might be using your database late in the evening. These include

  • you support a 24 by 7 system and so you often need to be online late at night,
  • you are a hopeless geek and love tinkering with the database when you should be in bed,
  • you now live in a virtual world and thus many of your meetings or seminars or presentations are being done in a unfriendly time zone

I tick some of these boxes and thus I am often using the database on my own home machine late in the evening. It is always frustrating when the clock ticks over to 10:00 PM and suddenly the fan on my machine goes nuts, the lights go dim in my house Smile and I can tell that the many many databases on my machine have suddenly decided it is time to get busy.

The reason for this is that the database has a number of tasks that get run at regular intervals in order to keep your database in tiptop condition. For example, the gathering of optimiser statistics and running the various advisors that come by default with your installation. Out of the box these tasks assume you are running a typical business day style of operation for your database, thus 10:00 PM is the default kick-off time for these tasks, and they’ll run for up to 4 hours.

Let’s take a look at the default scheduler windows that come in most recent versions of the Oracle database 



SQL> select window_name, repeat_interval, duration
  2  from dba_scheduler_windows;

WINDOW_NAME              REPEAT_INTERVAL                                                          DURATION
------------------------ ------------------------------------------------------------------------ ---------------
MONDAY_WINDOW            freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0                    +000 04:00:00
TUESDAY_WINDOW           freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0                    +000 04:00:00
WEDNESDAY_WINDOW         freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0                    +000 04:00:00
THURSDAY_WINDOW          freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0                    +000 04:00:00
FRIDAY_WINDOW            freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0                    +000 04:00:00
SATURDAY_WINDOW          freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0                     +000 20:00:00
SUNDAY_WINDOW            freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0                     +000 20:00:00
WEEKEND_WINDOW           freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                      +002 00:00:00
WEEKNIGHT_WINDOW         freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0    +000 08:00:00

If 10:00 PM is not a good time then it is easily to change these windows. I generally recommend people not to disable the windows because it is easy to get into a position where the nightly tasks you should be running are forgotten and never run again. Rest assured when you log a support call at some stage in the future and your database has not run any of these regular tasks for the past year, that is going to make the support technician’s job that much harder to help you, because you have diverted so far from default operations of the database.

If you need to change the windows I would suggest that you simply move the start time, and similarly you can shrink the duration to a smaller time scale if needed. For me, I run under the assumption that I will not be using the database at 2:00 AM (or that if I am I should not be and the busy machine will hopefully force me to go to bed!)

Here is a simple anonymous block that loops through the weekday windows to move the start time and duration: 



SQL> declare
  2    x sys.odcivarchar2list :=
  3          sys.odcivarchar2list('MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY');
  4  BEGIN
  5  for i in 1 .. x.count
  6  loop
  7    DBMS_SCHEDULER.disable(name => 'SYS.'||x(i)||'_WINDOW', force => TRUE);
  8
  9    DBMS_SCHEDULER.set_attribute(
 10      name      => 'SYS.'||x(i)||'_WINDOW',
 11      attribute => 'REPEAT_INTERVAL',
 12      value     => 'FREQ=WEEKLY;BYDAY='||substr(x(i),1,3)||';BYHOUR=02;BYMINUTE=0;BYSECOND=0');
 13
 14    DBMS_SCHEDULER.set_attribute(
 15      name      => 'SYS.'||x(i)||'_WINDOW',
 16      attribute => 'DURATION',
 17      value     =>  numtodsinterval(60, 'minute'));
 18
 19    DBMS_SCHEDULER.enable(name=>'SYS.'||x(i)||'_WINDOW');
 20  end loop;
 21  END;
 22  /

PL/SQL procedure successfully completed.

Remember that in a multi-tenant environment, these windows may be present in each of your pluggable as well as the root.

TL;DR: You can change the scheduler windows to a time that best suits your requirements but please leave them in place so that important database tasks do not get overlooked

Improve Your Remote Collaboration With P2

Submitted by harald on Thu, 2020-08-06 14:01

P2 powers internal collaboration at WordPress.com — and now it’s free for everyone.

As more collaboration is happening remotely and online — work yes, but increasingly also school and personal relationships — we’re all looking for better ways to work together online. Normally, teachers hand out homework to students in person, and project leaders gather colleagues around a conference table for a presentation. Suddenly all this is happening in email, and Slack, and Zoom, and Google docs, and a dozen other tools.

At WordPress.com, our 15 years as a fully distributed company with over 1,200 employees working from 77 countries relies on P2: an all-in-one team website, blog, database, and social network that consolidates communications and files in one accessible, searchable spot.

It powers work at WordPress.com, WooCommerce, and Tumblr. And today, a beta version is available for anyone — for your newly-remote work team, your homeschooling pod, your geographically scattered friends. P2 is the glue that gives your group an identity and coherence. 

https://en-blog.files.wordpress.com/2020/08/p2-1.png?w=2046 2046w, https://en-blog.files.wordpress.com/2020/08/p2-1.png?w=150 150w, https://en-blog.files.wordpress.com/2020/08/p2-1.png?w=300 300w, https://en-blog.files.wordpress.com/2020/08/p2-1.png?w=768 768w" sizes="(max-width: 1024px) 100vw, 1024px" />

What’s P2?

P2 moves your team or organization away from scattered communication and siloed email inboxes. Any member of your P2, working on any kind of project together, can post regular updates. Discussions happen via comments, posted right from the front page and updated in real time, so your team can brainstorm, plan, and come to a consensus. Upload photos or charts, take a poll, embed files, and share tidbits from your day’s activities. Tag teammates to get their attention. Your P2 members can see updates on the Web, via email, or in the WordPress mobile apps. 

Keep your P2 private for confidential collaboration. Or make it public to build a community. How you use it and who has access is up to you. And as folks come and go, all conversations and files remain available on the P2, and aren’t lost in anyone’s inbox.

The beta version of P2 is free for anyone, and you can create as many P2 sites as you need. (Premium versions are in the works.)  

What can I use P2 for?

Inside Automattic, we use P2 for:

  • Companywide blog posts from teams and leadership, where everyone can ask questions via comments.
  • Virtual “watercoolers” to help teammates connect — there are P2s for anything from music to Doctor Who to long-distance running.
  • Project planning updates.
  • Sharing expertise to our broader audience. We’ve got a P2 with guidance on how to manage remote work, and WooCommerce uses P2 to organize their global community.

P2 works as an asynchronous companion to live video like Zoom or live chat like Slack. It’s a perfect partner for live video and chat — you have those tools when a real-time conversation gets the job done, and P2 for reflection, discussion, and commemorating decisions.

How can you use your P2?

  • Plan a trip with friends and family — share links, ticket files, and travel details. (See an example on this P2!).
  • Create a P2 for your school or PTA to share homeschooling resources and organize virtual events.
  • Manage your sports team’s schedules and share photos from games.
  • Let kids track and submit homework assignments remotely, with a space for Q&A with your students.
https://en-blog.files.wordpress.com/2020/08/p2-3.png?w=2046 2046w, https://en-blog.files.wordpress.com/2020/08/p2-3.png?w=150 150w, https://en-blog.files.wordpress.com/2020/08/p2-3.png?w=300 300w, https://en-blog.files.wordpress.com/2020/08/p2-3.png?w=768 768w" sizes="(max-width: 1024px) 100vw, 1024px" />

How can I learn more?

Visit this demo P2 to learn the P2 ropes! Check out a range of example posts and comments to see how you can:

  • Post, read, comment, like, and follow conversations. 
  • @-mention individuals and groups to get their attention. 
  • Share video, audio, documents, polls, and more.  
  • Access in-depth stats and get notifications.

Ready for your own P2?

Visit WordPress.com/p2 and create your own P2.

Case and Aggregate bug

Submitted by Jonathan Lewis on Thu, 2020-08-06 12:43

The following description of a bug appeared on the Oracle Developer Community forum a little while ago – on an upgrade from 12c to 19c a query starting producing the wrong results on a simple call to the average() function. In fact it turned out to be a bug introduced in 12.2.0.1.

The owner of the thread posted a couple of zip files to build a test case – but I had to do a couple of edits, and change the nls_numeric_characters to ‘,.’ in order to get past a formatting error on a call to the to_timestamp() function. I’ve stripped the example to a minimum, and translated column name from German (which was presumably the source of the nls_numeric_characters issue) to make it easier to demonstrate and play with the bug.

First the basic data – you’ll notice that I’ve tested this on 12.1.0.2, 12.2.0.1 and 19.3.0.0 to find out when the bug appeared:

rem
rem     Script:         case_aggregate_bug.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Aug 2020
rem     Purpose:        
rem
rem     Last tested
rem             19.3.0.0
rem             12.2.0.1
rem             12.1.0.2
rem

create table test(
        case_col        varchar2(11), 
        duration        number(*,0), 
        quarter         varchar2(6), 
        q2h_knum_b      varchar2(10)
   )
/

insert into test values('OK',22,'1.2020','AB1234');
insert into test values('OK',39,'1.2020','AB1234');
insert into test values('OK',30,'1.2020','AB1234');
insert into test values('OK',48,'1.2020','AB1234');
commit;

execute dbms_stats.gather_table_stats(user,'test')

create or replace force view v_test
as 
select 
        q2h_knum_b,
        case 
                when b.case_col not like 'err%'
                        then b.duration 
        end     duration,
        case 
                when b.case_col not like 'err%' 
                        then 1 
                        else 0 
        end     status_ok
from
        test b
where
        substr(b.quarter, -4) = 2020
;


break on report
compute avg of duration on report
select * from v_test;

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

Q2H_KNUM_B   DURATION  STATUS_OK
---------- ---------- ----------
AB1234             22          1
AB1234             39          1
AB1234             30          1
AB1234             48          1
           ----------
avg             34.75

I’ve created a table, loaded some data, gathered stats, then created a view over the table. The view includes a couple of columns that use a simple case expression, and both expressions are based in the same way on the same base column (this may, or may not, be significant in what’s coming). I’ve then run off a simple query with a couple of SQL*Plus commands to report the actual content of the view with the average of the duration column – which is 34.75.

So now we run a couple of queries against the view which aggregate the data down to a single row – including the avg() of the duration – using the coalesce() function – rather than the older nvl() function – to convert any nulls to zero.


select
        coalesce(count(duration), 0)    duration_count,
        coalesce(median(duration), 0)   duration_med,
        coalesce(avg(duration), 0)      duration_avg,
        coalesce(sum(status_ok), 0)     ok_count
from
        v_test  v1
where
        instr('AB1234', q2h_knum_b) > 0
/

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

DURATION_COUNT DURATION_MED DURATION_AVG   OK_COUNT
-------------- ------------ ------------ ----------
             4         34.5            0          4

You’ll notice that the duration_avg is reported as zero (this would be the same if I used nvl(), and would be a null if I omitted the coalesce(). This is clearly incorrect. This was the output from 19.3; 12.2 gives the same result, 12.1.0.2 reports the average correctly as 34.75.

There are several way in which you can modify this query to get the right average – here’s one, just put the ok_count column first in the select list:


select
        coalesce(sum(status_ok), 0)     ok_count,
        coalesce(count(duration), 0)    duration_count,
        coalesce(median(duration), 0)   duration_med,
        coalesce(avg(duration), 0)      duration_avg
from
        v_test  v1
where
        instr('AB1234', q2h_knum_b) > 0
/

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

  OK_COUNT DURATION_COUNT DURATION_MED DURATION_AVG
---------- -------------- ------------ ------------
         4              4         34.5        34.75

There’s no obvious reason why the error should occur, but there’s a little hint about what may be happening in the Column projection information from the execution plan. The basic plan is the same in both cases, so I’m only show it once; but it’s followed by two versions of the projection information (restricted to operation 1) which I’ve formatted to improve:

Plan hash value: 2603667166

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |
|   1 |  SORT GROUP BY     |      |     1 |    20 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST |     1 |    20 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((INSTR('AB1234',"Q2H_KNUM_B")>0 AND
              TO_NUMBER(SUBSTR("B"."QUARTER",(-4)))=2020))

Column Projection Information (Operation 1 only):  (Wrong result)
-----------------------------------------------------------------
PERCENTILE_CONT(.5) WITHIN GROUP ( ORDER BY CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END)[22],
COUNT(CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END)[22], 
SUM  (CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN 1 ELSE 0 END)[22], 
SUM  (CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END)[22]



Column Projection Information (Operation 1 only):  (Right result)
-----------------------------------------------------------------
PERCENTILE_CONT(.5) WITHIN GROUP ( ORDER BY CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END)[22],
COUNT(CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END)[22], 
SUM  (CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END)[22], 
SUM  (CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN 1 ELSE 0 END)[22]

As you can see, to report avg() Oracle has projected sum() and count().

When we get the right result the sum() for duration appears immediately after the count().

When we get the wrong result the sum() for ok_count comes between the count() and sum() for duration.

This makes me wonder whether Oracle is somehow just losing track of the sum() for duration and therefore dividing null by the count().

This is purely conjecture, of course, and may simply be a coincidence – particularly since 12.1.0.2 gets the right result and shows exactly the same projection information.

Readers are left to experiment with other variations to see if they can spot other suggestive patterns.

 

Oracle 19c Automatic Indexing: Poor Data Clustering With Autonomous Databases Part I (Don’t Look Down)

Submitted by Richard Foote on Thu, 2020-08-06 04:39

I’ve discussed many times the importance of data clustering in relation to the efficiency of indexes. With respect to the efficiency of Automatic Indexes including their usage within Oracle’s Autonomous Database environments, data clustering is just as important. The following demo was run on an Oracle 19c database within the Oracle Autonomous Database Transaction Processing […]

Hosting Live (Virtual!) Events: Lessons from Planning the WordPress.com Growth Summit

Submitted by harald on Wed, 2020-08-05 15:17

Back in January, my team at WordPress.com was busy planning another year of exciting in-person events — community meetups, conference keynotes, booths, and in-person demos — at large exhibit halls and hotels around the world.

Then the world changed overnight, and because of a global pandemic, our Events team — just like many of you running your own businesses — had to rethink everything about how we connect with people. 

So we went back to work. We’ve learned so much in just five months, and it culminates in the upcoming WordPress.com Growth Summit — our first-ever multi-day virtual conference. It’s going to be a jam-packed program full of expert advice from business leaders and entrepreneurs. We’ll also have breakout sessions with our own WordPress experts, the Automattic Happiness Engineers, taking you through everything you need to know about building a powerful, fast website that works great for SEO, eCommerce, and growing your business. 

In the lead-up to the Summit, we wanted to share everything we’ve learned so far about running virtual events, from YouTube to webinars to Facebook Live and ticket sales. There are dozens of great solutions for staying connected to and supporting your audience — here’s what’s been working for us: 

Live webinars 

In April, we launched a series of daily webinars, 30-minute live demos and Q&As direct from our Happiness Engineers, five days a week. These webinars walk people through the basics of getting started with WordPress.com. We also launched a few topical webinars — deeper dives into specific topics: eCommerce 101, growing an audience, using the WordPress app, and podcasting, to name a few.

We chose Zoom to host these because it’s a popular platform that allows for key webinar elements like pre-registration/signups, screen sharing, and Q&A. We pulled these together quickly, so going with a familiar solution was key for us and our audience. 

To expand our reach, we also streamed to our Facebook, Instagram, and YouTube channels. This was a simple integration that Zoom offers already, and we saw our viewership grow exponentially. 

Pre-recorded vs. live instruction 

At virtual events, one question always comes up: pre-recorded or live? We prefer a combination! Live is great when possible; it gives attendees an opportunity to interact with speakers, speakers can personalize the content based on questions being asked, and attendees can interact with one another, forming connections with like-minded content creators and entrepreneurs. 

Live content also has challenges: internet connections can cut out, computers can shut down unexpectedly, and there are more opportunities for interruption (does anyone else’s dog bark the minute you get on a live video?). It also requires all participants to be online at the same time, which can be logistically challenging.

Our advice: Test, test, test! If a speaker isn’t comfortable presenting live, there is the option to do a combination — a pre-recorded session with a live Q&A in the chat. We’ve found it to work really well, and it gives attendees the same access to the presenter.

The Growth Summit 

We helped folks to get online quickly with our daily webinars and dove into deeper topics each month, and now we want to help you grow your site. Enter The Official WordPress.com Growth Summit, happening next week, August 11-13.

We gathered frequently asked questions over the past few months, listened to your requests for live sessions across more time zones, and found inspiration from users that we felt needed to be shared widely.  

The Growth Summit takes a deeper dive into topics and offers hands-on WordPress training for anyone looking to get online. You’ll have the opportunity to ask questions live, connect with speakers, visit our virtual Happiness Bar for 1:1 support, and connect with other attendees during the networking breaks. 

Some key highlights from the agenda: 

  • Using the block editor
  • Customizing your site
  • Growing your audience
  • Improving your content ranking (SEO)
  • Creating a marketing plan 
  • Expanding from blogging to podcasting 
  • Making money with your site
  • And so much more… 

We wanted a platform for this event that would act as an immersive event experience. There are many great platforms for this, including Accelevents and Hopin. We’ll be experimenting with many of them in the coming months (Remember: test!). A few key features we looked for: 

  • Ease of self-production
  • Ability for simultaneous sessions
  • Overall user experience
  • Flow of the event — central location for agenda, speaker bios, networking, and more
  • Networking features
  • Audience engagement — polling, live chat, and more
  • Analytics
  • Registration within the platform
  • Accessibility
  • Customization
  • Speaker (virtual) green rooms

The best part? This event is being offered twice a day so that we cover all time zones. And if you can’t join us live, attendees will have access to all content from all time zones, after the event.

Register for the WordPress.com Growth Summit Today!

The Problem With Oracle : If a developer/user can’t do it, it doesn’t exist.

Submitted by oraclebase on Tue, 2020-08-04 09:10

This post was inspired by two separate events that happened back to back.

Event 1

One of my colleagues sent me a list of cool features that were available in a cloud database service we may be purchasing. The vast majority of those features have been available in Oracle for over a decade, and we are currently licensed to use them. I would suggest the Oracle features were in fact the inspiration for their inclusion in this cloud database product.

I got a little on the defensive and explained this situation, and their reply was along the lines of, “Most of those features are DBA-focused, not user-focused”. That was also not 100% true, but I can understand where that assumption came from.

Now you could raise several arguments, including these.

  • The person in question should have known about this functionality.
  • I should have done a better job about promoting this functionality.
  • I could have done a better job about making those features that were DBA-focused available to a wider audience.

Regardless of all that, we are left in the position where Oracle and several other database engines are seen as DBA-focused tools, and not really inclusive for developers and users.

FYI: I think this cloud database product is probably the right choice for this specific use case, so this is not {only} about me being an Oracle fan-boy. It is my frustration at the reinvention of the wheel being touted as new. Happens all the time… </p /> </p></div> <div class=

Level up your audit trigger game

Submitted by connor_mc_d on Tue, 2020-08-04 05:09

A weekend audit

Often if you see the words “weekend audit” in a non-technical sense, it means that awkward moment where you look at the state of your house/bedroom/bathroom/bookshelf/shed etc and say to yourself “Yup, it is time we had a weekend audit of all this crap” Smile

But I had a different kind of weekend audit on the weekend just passed. I used some weekend time to write a generic audit facility for capturing changes to tables in your Oracle database. I was motivated to do this from a recent review on a very very old AskTOM question from way back in 2000.  Tom wrote a facility that wrote out column changes, but I was never really enamoured with the solution, in particular, the multiplier effect on the number of calls you had to do, and the fact that the changes were all stored in generic VARCHAR2 columns. I recently updated the post due to a user request to handle timestamps and other data types, but I’ve never been a true fan of the code.

But as my parents always taught me – you can’t criticise unless you have a better proposal…so I built one! I think it is a better option for anyone wanting to capture changes via triggers.

Note – I’m also a fan of using Flashback Data Archive as a means of capturing audit changes (by combining it with the VERSIONS BETWEEN syntax). There’s a video at the bottom of this post showing you details on that. But I built my own using triggers because I wanted the flexibility to also satisfy the following (common) audit requirements.

  • A dedicated audit table for each table to be audited
  • A consolidated view of all audit actions undertaken
  • Not having to write a lot of code to implement auditing – it should be generated for me
  • API level control over whether to capture inserts, and whether to capture both OLD/NEW updates or just OLD (because the current table values always contain the NEW)
  • The ability to selectively disable/enable the trigger for a session (for example, for data maintenance) without impacting other sessions
  • Handle things like new columns to the source table etc.

Here is my implementation, and you can get the code from github.

The common metadata for all captured changes goes into a table called AUDIT_HEADER. I won’t describe what each column is here, because they are pretty much self-explanatory, but also…in perhaps what is the last example you’ll see anywhere of someone using the database dictionary for what it is designed for, I have added COMMENT commands for all the tables and columns in the github repo.  I always lament the sad state of affairs when people say “We don’t have a place to document our database schema…”.  Er, um, yes you do .. it’s called (drum roll for impact…..) the database!


SQL> desc AUD_UTIL.AUDIT_HEADER

 Name                          Null?    Type
 ----------------------------- -------- ------------------
 AUD$TSTAMP                    NOT NULL TIMESTAMP(6)
 AUD$ID                        NOT NULL NUMBER(18)
 TABLE_NAME                    NOT NULL VARCHAR2(30)
 DML                           NOT NULL VARCHAR2(1)
 DESCR                                  VARCHAR2(100)
 ACTION                                 VARCHAR2(32)
 CLIENT_ID                              VARCHAR2(64)
 HOST                                   VARCHAR2(64)
 MODULE                                 VARCHAR2(48)
 OS_USER                                VARCHAR2(32)

Every table for which you activate auditing for when then have its own table which is a logical child of the AUDIT_HEADER table. I say logical child because I am not explicitly nominating a foreign key here. Feel free to add it if you like, but by default, I don’t do it because no-one should be doing DML against these tables except via the API that is automatically generated. It might be useful for the optimizer if you are heavily mining these tables.

Each audit table is the table name suffixed with the schema (because multiple schemas with potentially the same table names will have their audit captured into a single auditing schema, which is AUD_UTIL by default, but you can change this simply by editing the ‘schema’ substitution variable at the top of each script.


SQL> desc AUD_UTIL.EMP_SCOTT

 Name                          Null?    Type
 ----------------------------- -------- -----------------
 AUD$TSTAMP                    NOT NULL TIMESTAMP(6)
 AUD$ID                        NOT NULL NUMBER(18)
 AUD$IMAGE                     NOT NULL VARCHAR2(3)
 EMPNO                                  NUMBER(4)
 ENAME                                  VARCHAR2(10)
 JOB                                    VARCHAR2(9)
 MGR                                    NUMBER(4)
 HIREDATE                               DATE
 SAL                                    NUMBER(7,2)
 COMM                                   NUMBER(7,2)
 DEPTNO                                 NUMBER(2)

The child audit tables will contain the same columns as the source table name, but with three additional columns

  • AUD$TSTAMP, AUD$ID which are logical link back to the parent AUDIT_HEADER record
  • AUD$IMAGE which is “OLD” or “NEW” aligning to the triggering values

Sample Usage

Any API call that is “destructive”, namely, it could run DDL has an “action” parameter that is OUTPUT or EXECUTE. “OUTPUT” lets you do things is a safe manner.

Every API call to modify the auditing is captured in a MAINT_LOG table so you can blame the appropriate people have a history of the auditing API calls you have made. But first, we need to let the code know which schemas we will allow to be audited. Only the schemas nominated in the table SCHEMA_LIST can have auditing enabled, and by default it is empty, so you’ll get an error on any attempt to use the API


SQL> exec  aud_util.audit_util.generate_audit_table('SCOTT','EMP',p_action=>'OUTPUT');

Call to generate audit table for SCOTT.EMP

BEGIN aud_util.audit_util.generate_audit_table('SCOTT','EMP',p_action=>'OUTPUT'); END;

*
ERROR at line 1:
ORA-20378: You can only manage audit facilities for schemas listed in SCHEMA_LIST
ORA-06512: at "AUD_UTIL.AUDIT_UTIL", line 111
ORA-06512: at "AUD_UTIL.AUDIT_UTIL", line 480
ORA-06512: at "AUD_UTIL.AUDIT_UTIL", line 496
ORA-06512: at line 1


SQL> insert into aud_util.schema_list values ('SCOTT');

1 row created.

SQL> commit;

Commit complete.

Now that this is done, we can dig into the API a little more

Creating an Audit Table

Note: This example does not actually do any work, because we have set p_action to OUTPUT. But we can see what work would have been performed by the call.


SQL> exec  aud_util.audit_util.generate_audit_table('SCOTT','EMP',p_action=>'OUTPUT');

Call to generate audit table for SCOTT.EMP

create table AUD_UTIL.EMP_SCOTT (
 aud$tstamp     timestamp   not null,
 aud$id         number(18)  not null,
 aud$image      varchar2(3) not null )
 partition by range ( aud$tstamp  )
 interval (numtoyminterval(1,'MONTH'))
 ( partition EMP_p202009 values less than ( to_timestamp('20201001','yyyymmdd') )
 ) pctfree 1 tablespace users

alter table AUD_UTIL.EMP_SCOTT
  add constraint EMP_SCOTT_PK primary key ( aud$tstamp, aud$id, aud$image)
  using index
    (create unique index AUD_UTIL.EMP_SCOTT_PK
     on AUD_UTIL.EMP_SCOTT ( aud$tstamp, aud$id, aud$image)
     local tablespace users)

alter table AUD_UTIL.EMP_SCOTT add EMPNO NUMBER(4,0)
alter table AUD_UTIL.EMP_SCOTT add ENAME VARCHAR2(10)
alter table AUD_UTIL.EMP_SCOTT add JOB VARCHAR2(9)
alter table AUD_UTIL.EMP_SCOTT add MGR NUMBER(4,0)
alter table AUD_UTIL.EMP_SCOTT add HIREDATE DATE
alter table AUD_UTIL.EMP_SCOTT add SAL NUMBER(7,2)
alter table AUD_UTIL.EMP_SCOTT add COMM NUMBER(7,2)
alter table AUD_UTIL.EMP_SCOTT add DEPTNO NUMBER(2,0)

PL/SQL procedure successfully completed.

We create an audit table which is partitioned by month (see later for details about partitioning), with an appropriate locally partitioned primary key. No global indexes are used because the expectation here is that with partitioning you may ultimately want to purge at the partition level in future.

Creating an Audit Package

I’m “old school” and have always considered that any lengthy code that would go into a trigger should be placed into a package. So our insertion DML is wrapped up in a database package that ultimately our  audit trigger will call.


SQL> exec  aud_util.audit_util.generate_audit_package('SCOTT','EMP',p_action=>'OUTPUT');

Call to generate audit package for SCOTT.EMP

create or replace
package AUD_UTIL.PKG_EMP_SCOTT is

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

  procedure audit_row(
     p_aud$tstamp                     timestamp
    ,p_aud$id                         number
    ,p_aud$image                      varchar2
    ,p_empno     number
    ,p_ename     varchar2
    ,p_job       varchar2
    ,p_mgr       number
    ,p_hiredate  date
    ,p_sal       number
    ,p_comm      number
    ,p_deptno    number
  );
end;
create or replace
package body AUD_UTIL.PKG_EMP_SCOTT is

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

  procedure audit_row(
     p_aud$tstamp                    timestamp
    ,p_aud$id                        number
    ,p_aud$image                     varchar2
    ,p_empno     number
    ,p_ename     varchar2
    ,p_job       varchar2
    ,p_mgr       number
    ,p_hiredate  date
    ,p_sal       number
    ,p_comm      number
    ,p_deptno    number
  ) is
begin

  insert into AUD_UTIL.EMP_SCOTT (
     aud$tstamp
    ,aud$id
    ,aud$image
    ,empno
    ,ename
    ,job
    ,mgr
    ,hiredate
    ,sal
    ,comm
    ,deptno
  ) values (
     p_aud$tstamp
    ,p_aud$id
    ,p_aud$image
    ,p_empno
    ,p_ename
    ,p_job
    ,p_mgr
    ,p_hiredate
    ,p_sal
    ,p_comm
    ,p_deptno
    );
end;

end;
grant execute on AUD_UTIL.PKG_EMP_SCOTT to SCOTT

PL/SQL procedure successfully completed.

You can see that the package is created in our audit schema not in the table owing schema.  This is to improve security, and the use of a package is aimed to keep the code in the subsequent audit trigger nice and compact.

 

Creating an Audit Trigger

Finally we need a trigger to call our package to capture information.


SQL> exec  aud_util.audit_util.generate_audit_trigger('SCOTT','EMP',p_action=>'OUTPUT');

Call to generate audit trigger for SCOTT.EMP

create or replace
trigger AUD_UTIL.AUD$EMP
after insert or update or delete on SCOTT.EMP
for each row
disable
declare
 l_dml       varchar2(1) := case when updating then 'U' when inserting then 'I' else 'D' end;
 l_tstamp    timestamp;
 l_id        number;
 l_descr     varchar2(100);
begin

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

 if aud_util.trigger_ctl.enabled('AUD$EMP') then
  l_descr :=
    case
      when updating
        then 'UPDATE'
      when inserting
        then 'INSERT'
      else
        'DELETE'
    end;

  aud_util.log_header('EMP',l_dml,l_descr,l_tstamp,l_id);

  if updating or
     deleting then
     aud_util.pkg_emp_scott.audit_row(
         p_aud$tstamp =>l_tstamp
        ,p_aud$id     =>l_id
        ,p_aud$image  =>'OLD'
        ,p_empno     =>:old.empno
        ,p_ename     =>:old.ename
        ,p_job       =>:old.job
        ,p_mgr       =>:old.mgr
        ,p_hiredate  =>:old.hiredate
        ,p_sal       =>:old.sal
        ,p_comm      =>:old.comm
        ,p_deptno    =>:old.deptno
        );
  end if;
  if inserting then
     aud_util.pkg_emp_scott.audit_row(
         p_aud$tstamp=>l_tstamp
        ,p_aud$id    =>l_id
        ,p_aud$image =>'NEW'
        ,p_empno     =>:new.empno
        ,p_ename     =>:new.ename
        ,p_job       =>:new.job
        ,p_mgr       =>:new.mgr
        ,p_hiredate  =>:new.hiredate
        ,p_sal       =>:new.sal
        ,p_comm      =>:new.comm
        ,p_deptno    =>:new.deptno
        );
  end if;
 end if;
end;
alter trigger AUD_UTIL.AUD$EMP enable

By default, even the trigger is created in the audit schema not the owning schema. Some people prefer this, some people hate it. You can choose your preference by adjusting the ‘g_trigger_in_audit_schema’ global variable in the package body. See the tail of this post for other settings.

You can see that we are capturing inserts. I typically think this is overkill, because an inserted row is readily available in the table itself. It is only when someone updates or deletes the row that typically you want to capture an audit. The ‘g_inserts_audited’ (true|false) in the package body gives you control over this.

Also, people have differing opinions on whether audit should capture both OLD and NEW values during an update, or just the OLD (because the new values are readily available in the table). Thus similarly, there is a setting ‘g_capture_new_updates’ (true|false) to give you control over this.

The trigger is always created in DISABLED mode to ensure that if it does not compile, then it will not cause any damage. It is enabled afterwards, but you can control this with the p_enable_trigger parameter which defaults to true.

Bringing it Altogether

All of the above is really just to help with explaining what is going on under the covers. In normal operation, you only need a single call to generate all the audit infrastructure for a table.


SQL> exec  aud_util.audit_util.generate_audit_support('SCOTT','EMP',p_action=>'EXECUTE');

Call to generate audit table for SCOTT.EMP

create table AUD_UTIL.EMP_SCOTT (
 aud$tstamp     timestamp   not null,
 aud$id         number(18)  not null,
 aud$image      varchar2(3) not null )
 partition by range ( aud$tstamp  )
 interval (numtoyminterval(1,'MONTH'))
 ( partition EMP_p202009 values less than ( to_timestamp('20201001','yyyymmdd') )
 ) pctfree 1 tablespace users
alter table AUD_UTIL.EMP_SCOTT
  add constraint EMP_SCOTT_PK primary key ( aud$tstamp, aud$id, aud$image)
  using index
    (create unique index AUD_UTIL.EMP_SCOTT_PK
     on AUD_UTIL.EMP_SCOTT ( aud$tstamp, aud$id, aud$image)
     local tablespace users)
alter table AUD_UTIL.EMP_SCOTT add EMPNO NUMBER(4,0)
alter table AUD_UTIL.EMP_SCOTT add ENAME VARCHAR2(10)
alter table AUD_UTIL.EMP_SCOTT add JOB VARCHAR2(9)
alter table AUD_UTIL.EMP_SCOTT add MGR NUMBER(4,0)
alter table AUD_UTIL.EMP_SCOTT add HIREDATE DATE
alter table AUD_UTIL.EMP_SCOTT add SAL NUMBER(7,2)
alter table AUD_UTIL.EMP_SCOTT add COMM NUMBER(7,2)
alter table AUD_UTIL.EMP_SCOTT add DEPTNO NUMBER(2,0)

Call to generate audit package for SCOTT.EMP

create or replace
package AUD_UTIL.PKG_EMP_SCOTT is

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

  procedure audit_row(
     p_aud$tstamp                     timestamp
    ,p_aud$id                         number
    ,p_aud$image                      varchar2
    ,p_empno     number
    ,p_ename     varchar2
    ,p_job       varchar2
    ,p_mgr       number
    ,p_hiredate  date
    ,p_sal       number
    ,p_comm      number
    ,p_deptno    number
  );
end;
create or replace
package body AUD_UTIL.PKG_EMP_SCOTT is

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

  procedure audit_row(
     p_aud$tstamp                    timestamp
    ,p_aud$id                        number
    ,p_aud$image                     varchar2
    ,p_empno     number
    ,p_ename     varchar2
    ,p_job       varchar2
    ,p_mgr       number
    ,p_hiredate  date
    ,p_sal       number
    ,p_comm      number
    ,p_deptno    number
  ) is
begin

  insert into AUD_UTIL.EMP_SCOTT (
     aud$tstamp
    ,aud$id
    ,aud$image
    ,empno
    ,ename
    ,job
    ,mgr
    ,hiredate
    ,sal
    ,comm
    ,deptno
  ) values (
     p_aud$tstamp
    ,p_aud$id
    ,p_aud$image
    ,p_empno
    ,p_ename
    ,p_job
    ,p_mgr
    ,p_hiredate
    ,p_sal
    ,p_comm
    ,p_deptno
    );
end;

end;
grant execute on AUD_UTIL.PKG_EMP_SCOTT to SCOTT

Call to generate audit trigger for SCOTT.EMP

create or replace
trigger AUD_UTIL.AUD$EMP
after insert or update or delete on SCOTT.EMP
for each row
disable
declare
 l_dml       varchar2(1) := case when updating then 'U' when inserting then 'I' else 'D' end;
 l_tstamp    timestamp;
 l_id        number;
 l_descr     varchar2(100);
begin

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

 if aud_util.trigger_ctl.enabled('AUD$EMP') then
  l_descr :=
    case
      when updating
        then 'UPDATE'
      when inserting
        then 'INSERT'
      else
        'DELETE'
    end;

  aud_util.log_header('EMP',l_dml,l_descr,l_tstamp,l_id);

  if updating or
     deleting then
     aud_util.pkg_emp_scott.audit_row(
         p_aud$tstamp =>l_tstamp
        ,p_aud$id     =>l_id
        ,p_aud$image  =>'OLD'
        ,p_empno     =>:old.empno
        ,p_ename     =>:old.ename
        ,p_job       =>:old.job
        ,p_mgr       =>:old.mgr
        ,p_hiredate  =>:old.hiredate
        ,p_sal       =>:old.sal
        ,p_comm      =>:old.comm
        ,p_deptno    =>:old.deptno
        );
  end if;
  if inserting then
     aud_util.pkg_emp_scott.audit_row(
         p_aud$tstamp=>l_tstamp
        ,p_aud$id    =>l_id
        ,p_aud$image =>'NEW'
        ,p_empno     =>:new.empno
        ,p_ename     =>:new.ename
        ,p_job       =>:new.job
        ,p_mgr       =>:new.mgr
        ,p_hiredate  =>:new.hiredate
        ,p_sal       =>:new.sal
        ,p_comm      =>:new.comm
        ,p_deptno    =>:new.deptno
        );
  end if;
 end if;
end;
alter trigger AUD_UTIL.AUD$EMP enable

PL/SQL procedure successfully completed.

SQL>

Remember this call, because as you’ll see below, it should be the only call you ever need.

Schema Evolution

Contrary to popular opinion, it is pretty easy to change the structure of a database table in a relational database. So what happens to our auditing when you add a column to the SCOTT.EMP table? By default, the auditing will continue on without any issue but will not capture that new column. But all you need to do is re-run the same audit API. It will work out what you have done and make the necessary adjustment.


SQL> alter table scott.emp add new_col number(10,2);

Table altered.

SQL> exec  aud_util.audit_util.generate_audit_support('SCOTT','EMP',p_action=>'EXECUTE');

Call to generate audit table for SCOTT.EMP

alter table AUD_UTIL.EMP_SCOTT add NEW_COL NUMBER(10,2)

Call to generate audit package for SCOTT.EMP
create or replace
package AUD_UTIL.PKG_EMP_SCOTT is

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

  procedure audit_row(
     p_aud$tstamp                     timestamp
    ,p_aud$id                         number
    ,p_aud$image                      varchar2
    ,p_empno     number
    ,p_ename     varchar2
    ,p_job       varchar2
    ,p_mgr       number
    ,p_hiredate  date
    ,p_sal       number
    ,p_comm      number
    ,p_deptno    number
    ,p_new_col   number
  );
end;
create or replace
package body AUD_UTIL.PKG_EMP_SCOTT is

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

  procedure audit_row(
     p_aud$tstamp                    timestamp
    ,p_aud$id                        number
    ,p_aud$image                     varchar2
    ,p_empno     number
    ,p_ename     varchar2
    ,p_job       varchar2
    ,p_mgr       number
    ,p_hiredate  date
    ,p_sal       number
    ,p_comm      number
    ,p_deptno    number
    ,p_new_col   number
  ) is
begin

  insert into AUD_UTIL.EMP_SCOTT (
     aud$tstamp
    ,aud$id
    ,aud$image
    ,empno
    ,ename
    ,job
    ,mgr
    ,hiredate
    ,sal
    ,comm
    ,deptno
    ,new_col
  ) values (
     p_aud$tstamp
    ,p_aud$id
    ,p_aud$image
    ,p_empno
    ,p_ename
    ,p_job
    ,p_mgr
    ,p_hiredate
    ,p_sal
    ,p_comm
    ,p_deptno
    ,p_new_col
    );
end;

end;
grant execute on AUD_UTIL.PKG_EMP_SCOTT to SCOTT

Call to generate audit trigger for SCOTT.EMP

create or replace
trigger AUD_UTIL.AUD$EMP
after insert or update or delete on SCOTT.EMP
for each row
disable
declare
 l_dml       varchar2(1) := case when updating then 'U' when inserting then 'I' else 'D' end;
 l_tstamp    timestamp;
 l_id        number;
 l_descr     varchar2(100);
begin

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

 if aud_util.trigger_ctl.enabled('AUD$EMP') then
  l_descr :=
    case
      when updating
        then 'UPDATE'
      when inserting
        then 'INSERT'
      else
        'DELETE'
    end;

  aud_util.log_header('EMP',l_dml,l_descr,l_tstamp,l_id);

  if updating or
     deleting then
     aud_util.pkg_emp_scott.audit_row(
         p_aud$tstamp =>l_tstamp
        ,p_aud$id     =>l_id
        ,p_aud$image  =>'OLD'
        ,p_empno     =>:old.empno
        ,p_ename     =>:old.ename
        ,p_job       =>:old.job
        ,p_mgr       =>:old.mgr
        ,p_hiredate  =>:old.hiredate
        ,p_sal       =>:old.sal
        ,p_comm      =>:old.comm
        ,p_deptno    =>:old.deptno
        ,p_new_col   =>:old.new_col
        );
  end if;
  if inserting then
     aud_util.pkg_emp_scott.audit_row(
         p_aud$tstamp=>l_tstamp
        ,p_aud$id    =>l_id
        ,p_aud$image =>'NEW'
        ,p_empno     =>:new.empno
        ,p_ename     =>:new.ename
        ,p_job       =>:new.job
        ,p_mgr       =>:new.mgr
        ,p_hiredate  =>:new.hiredate
        ,p_sal       =>:new.sal
        ,p_comm      =>:new.comm
        ,p_deptno    =>:new.deptno
        ,p_new_col   =>:new.new_col
        );
  end if;
 end if;
end;
alter trigger AUD_UTIL.AUD$EMP enable

PL/SQL procedure successfully completed.

If you drop a column, we don’t change the audit table because presumably you still want a record of the changes that occurred whilst that column existed. Regenerating the audit with the same call again will adjust the package and trigger to no longer reference the dropped column and leave the table untouched.


SQL> alter table scott.emp drop column new_col;

Table altered.

SQL> exec  aud_util.audit_util.generate_audit_support('SCOTT','EMP',p_action=>'EXECUTE');

Call to generate audit table for SCOTT.EMP

Call to generate audit package for SCOTT.EMP

create or replace
package AUD_UTIL.PKG_EMP_SCOTT is

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

  procedure audit_row(
     p_aud$tstamp                     timestamp
    ,p_aud$id                         number
    ,p_aud$image                      varchar2
    ,p_empno     number
    ,p_ename     varchar2
    ,p_job       varchar2
    ,p_mgr       number
    ,p_hiredate  date
    ,p_sal       number
    ,p_comm      number
    ,p_deptno    number
  );
end;
create or replace
package body AUD_UTIL.PKG_EMP_SCOTT is

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

  procedure audit_row(
     p_aud$tstamp                    timestamp
    ,p_aud$id                        number
    ,p_aud$image                     varchar2
    ,p_empno     number
    ,p_ename     varchar2
    ,p_job       varchar2
    ,p_mgr       number
    ,p_hiredate  date
    ,p_sal       number
    ,p_comm      number
    ,p_deptno    number
  ) is
begin

  insert into AUD_UTIL.EMP_SCOTT (
     aud$tstamp
    ,aud$id
    ,aud$image
    ,empno
    ,ename
    ,job
    ,mgr
    ,hiredate
    ,sal
    ,comm
    ,deptno
  ) values (
     p_aud$tstamp
    ,p_aud$id
    ,p_aud$image
    ,p_empno
    ,p_ename
    ,p_job
    ,p_mgr
    ,p_hiredate
    ,p_sal
    ,p_comm
    ,p_deptno
    );
end;

end;
grant execute on AUD_UTIL.PKG_EMP_SCOTT to SCOTT

Call to generate audit trigger for SCOTT.EMP

create or replace
trigger AUD_UTIL.AUD$EMP
after insert or update or delete on SCOTT.EMP
for each row
disable
declare
 l_dml       varchar2(1) := case when updating then 'U' when inserting then 'I' else 'D' end;
 l_tstamp    timestamp;
 l_id        number;
 l_descr     varchar2(100);
begin

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

 if aud_util.trigger_ctl.enabled('AUD$EMP') then
  l_descr :=
    case
      when updating
        then 'UPDATE'
      when inserting
        then 'INSERT'
      else
        'DELETE'
    end;

  aud_util.log_header('EMP',l_dml,l_descr,l_tstamp,l_id);

  if updating or
     deleting then
     aud_util.pkg_emp_scott.audit_row(
         p_aud$tstamp =>l_tstamp
        ,p_aud$id     =>l_id
        ,p_aud$image  =>'OLD'
        ,p_empno     =>:old.empno
        ,p_ename     =>:old.ename
        ,p_job       =>:old.job
        ,p_mgr       =>:old.mgr
        ,p_hiredate  =>:old.hiredate
        ,p_sal       =>:old.sal
        ,p_comm      =>:old.comm
        ,p_deptno    =>:old.deptno
        );
  end if;
  if inserting then
     aud_util.pkg_emp_scott.audit_row(
         p_aud$tstamp=>l_tstamp
        ,p_aud$id    =>l_id
        ,p_aud$image =>'NEW'
        ,p_empno     =>:new.empno
        ,p_ename     =>:new.ename
        ,p_job       =>:new.job
        ,p_mgr       =>:new.mgr
        ,p_hiredate  =>:new.hiredate
        ,p_sal       =>:new.sal
        ,p_comm      =>:new.comm
        ,p_deptno    =>:new.deptno
        );
  end if;
 end if;
end;
alter trigger AUD_UTIL.AUD$EMP enable

PL/SQL procedure successfully completed.

Similarly, the routine will “do its best” to see if it can align columns if you change them in the source table.  For example, if the ENAME column got extended:


SQL> alter table scott.emp modify ename varchar2(40);

Table altered.

then we will try to make a similar change in the audit setup

SQL> exec  aud_util.audit_util.generate_audit_support('SCOTT','EMP',p_action=>'EXECUTE');

Call to generate audit table for SCOTT.EMP

alter table AUD_UTIL.EMP_SCOTT modify ENAME VARCHAR2(40)

Call to generate audit package for SCOTT.EMP

create or replace
package AUD_UTIL.PKG_EMP_SCOTT is

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

  procedure audit_row(
     p_aud$tstamp                     timestamp
    ,p_aud$id                         number
    ,p_aud$image                      varchar2
    ,p_empno     number
    ,p_ename     varchar2
    ,p_job       varchar2
    ,p_mgr       number
    ,p_hiredate  date
    ,p_sal       number
    ,p_comm      number
    ,p_deptno    number
  );
end;
create or replace
package body AUD_UTIL.PKG_EMP_SCOTT is

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

  procedure audit_row(
     p_aud$tstamp                    timestamp
    ,p_aud$id                        number
    ,p_aud$image                     varchar2
    ,p_empno     number
    ,p_ename     varchar2
    ,p_job       varchar2
    ,p_mgr       number
    ,p_hiredate  date
    ,p_sal       number
    ,p_comm      number
    ,p_deptno    number
  ) is
begin

  insert into AUD_UTIL.EMP_SCOTT (
     aud$tstamp
    ,aud$id
    ,aud$image
    ,empno
    ,ename
    ,job
    ,mgr
    ,hiredate
    ,sal
    ,comm
    ,deptno
  ) values (
     p_aud$tstamp
    ,p_aud$id
    ,p_aud$image
    ,p_empno
    ,p_ename
    ,p_job
    ,p_mgr
    ,p_hiredate
    ,p_sal
    ,p_comm
    ,p_deptno
    );
end;

end;
grant execute on AUD_UTIL.PKG_EMP_SCOTT to SCOTT

Call to generate audit trigger for SCOTT.EMP

create or replace
trigger AUD_UTIL.AUD$EMP
after insert or update or delete on SCOTT.EMP
for each row
disable
declare
 l_dml       varchar2(1) := case when updating then 'U' when inserting then 'I' else 'D' end;
 l_tstamp    timestamp;
 l_id        number;
 l_descr     varchar2(100);
begin

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

 if aud_util.trigger_ctl.enabled('AUD$EMP') then
  l_descr :=
    case
      when updating
        then 'UPDATE'
      when inserting
        then 'INSERT'
      else
        'DELETE'
    end;

  aud_util.log_header('EMP',l_dml,l_descr,l_tstamp,l_id);

  if updating or
     deleting then
     aud_util.pkg_emp_scott.audit_row(
         p_aud$tstamp =>l_tstamp
        ,p_aud$id     =>l_id
        ,p_aud$image  =>'OLD'
        ,p_empno     =>:old.empno
        ,p_ename     =>:old.ename
        ,p_job       =>:old.job
        ,p_mgr       =>:old.mgr
        ,p_hiredate  =>:old.hiredate
        ,p_sal       =>:old.sal
        ,p_comm      =>:old.comm
        ,p_deptno    =>:old.deptno
        );
  end if;
  if inserting then
     aud_util.pkg_emp_scott.audit_row(
         p_aud$tstamp=>l_tstamp
        ,p_aud$id    =>l_id
        ,p_aud$image =>'NEW'
        ,p_empno     =>:new.empno
        ,p_ename     =>:new.ename
        ,p_job       =>:new.job
        ,p_mgr       =>:new.mgr
        ,p_hiredate  =>:new.hiredate
        ,p_sal       =>:new.sal
        ,p_comm      =>:new.comm
        ,p_deptno    =>:new.deptno
        );
  end if;
 end if;
end;
alter trigger AUD_UTIL.AUD$EMP enable

PL/SQL procedure successfully completed.

Some things we obviously can’t really know what your intent was, for example, if you rename a column, but there is an API provided to let you do that.


PROCEDURE rename_column(p_owner varchar2
                       ,p_table_name varchar2
                       ,p_old_columns varchar2
                       ,p_new_columns varchar2
                       ,p_action varchar2) IS

You pass in a comma separated list of the old column names and the new column names, and the audit table columns will be renamed and the audit package and audit triggers regenerated.

Dropping Auditing for a table

If you want to remove the auditing facilities for a table, simply call DROP_AUDIT_SUPPORT for the table.


SQL>  exec  aud_util.audit_util.drop_audit_support('SCOTT','EMP',p_action=>'EXECUTE');

Call to drop audit trigger for SCOTT.EMP
drop trigger AUD_UTIL.AUD$EMP

Call to drop audit package for SCOTT.EMP
drop package AUD_UTIL.PKG_EMP_SCOTT

Call to drop audit table for SCOTT.EMP
drop table AUD_UTIL.EMP_SCOTT

PL/SQL procedure successfully completed.

Clearly, if you added auditing for a table, then dropping is not a thing that should be taken lightly. For this reason, we check to see if there are any rows in the audit table for this object. If there is any data, then by default, we will drop the trigger and the package, but the table will be preserved.


SQL> exec  aud_util.audit_util.drop_audit_support('SCOTT','EMP',p_action=>'EXECUTE');

Call to drop audit trigger for SCOTT.EMP
drop trigger AUD_UTIL.AUD$EMP

Call to drop audit package for SCOTT.EMP
drop package AUD_UTIL.PKG_EMP_SCOTT

Call to drop audit table for SCOTT.EMP
BEGIN aud_util.audit_util.drop_audit_support('SCOTT','EMP',p_action=>'EXECUTE'); END;

*
ERROR at line 1:
ORA-20378: Rows found in AUD_UTIL.EMP_SCOTT.  Use FORCE option if you really want to drop this
ORA-06512: at "AUD_UTIL.AUDIT_UTIL", line 111
ORA-06512: at "AUD_UTIL.AUDIT_UTIL", line 909
ORA-06512: at "AUD_UTIL.AUDIT_UTIL", line 975
ORA-06512: at line 1

As the error message suggests, if you really want to erase that audit history, then add the P_FORCE parameter.


SQL> exec  aud_util.audit_util.drop_audit_support('SCOTT','EMP',p_action=>'EXECUTE',p_force=>true);
Call to drop audit trigger for SCOTT.EMP
drop trigger AUD_UTIL.AUD$EMP
Call to drop audit package for SCOTT.EMP
drop package AUD_UTIL.PKG_EMP_SCOTT
Call to drop audit table for SCOTT.EMP
drop table AUD_UTIL.EMP_SCOTT

PL/SQL procedure successfully completed.

But wait..there’s more!

The examples above are simplified to make comprehension easier. As many of us know, one of the concerns about having auditing triggers is that they turn a batch operation (ie, a single DML that modifies lots of rows) into a row-by-row operation in terms of performance, because for every row touched, we are “jumping out” to the audit code to log the changed row.

In reality, the audit generator does not do this. We take advantage of bulk binding to ensure that we minimise the performance overhead of the audit triggers. This is controlled by the setting g_bulk_bind which defaults to true. Of course, if you know in advance that every operation your application performs is always a single row modification, then

  • that’s a very sad application Smile ,
  • you should consider setting the bulk binding to false because there are also some small overheads with bulk binding when you don’t need to.

Here is how the package and trigger really look when generated with the bulk binding enabled



SQL> exec  aud_util.audit_util.generate_audit_package('SCOTT','EMP',p_action=>'EXECUTE');

Call to generate audit package for SCOTT.EMP

create or replace
package AUD_UTIL.PKG_EMP_SCOTT is

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

  procedure bulk_init;
  procedure bulk_process;

  procedure audit_row(
     p_aud$tstamp                     timestamp
    ,p_aud$id                         number
    ,p_aud$image                      varchar2
    ,p_empno     number
    ,p_ename     varchar2
    ,p_job       varchar2
    ,p_mgr       number
    ,p_hiredate  date
    ,p_sal       number
    ,p_comm      number
    ,p_deptno    number
  );
end;

create or replace
package body AUD_UTIL.PKG_EMP_SCOTT is

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

    type t_audit_rows is table of AUD_UTIL.EMP_SCOTT%rowtype
      index by pls_integer;

    l_audrows t_audit_rows;

  procedure bulk_init is
  begin
    l_audrows.delete;
  end;

  procedure bulk_process is
  begin
    if l_audrows.count = 1 then
      insert into AUD_UTIL.EMP_SCOTT values l_audrows(1);
    else
      forall i in 1 .. l_audrows.count
        insert into AUD_UTIL.EMP_SCOTT values l_audrows(i);
    end if;
  end;

  procedure audit_row(
     p_aud$tstamp                    timestamp
    ,p_aud$id                        number
    ,p_aud$image                     varchar2
    ,p_empno     number
    ,p_ename     varchar2
    ,p_job       varchar2
    ,p_mgr       number
    ,p_hiredate  date
    ,p_sal       number
    ,p_comm      number
    ,p_deptno    number
  ) is
    l_idx pls_integer := l_audrows.count+1;
  begin

    l_audrows(l_idx).aud$tstamp := p_aud$tstamp;
    l_audrows(l_idx).aud$id     := p_aud$id;
    l_audrows(l_idx).aud$image  := p_aud$image;
    l_audrows(l_idx).empno      := p_empno;
    l_audrows(l_idx).ename      := p_ename;
    l_audrows(l_idx).job        := p_job;
    l_audrows(l_idx).mgr        := p_mgr;
    l_audrows(l_idx).hiredate   := p_hiredate;
    l_audrows(l_idx).sal        := p_sal;
    l_audrows(l_idx).comm       := p_comm;
    l_audrows(l_idx).deptno     := p_deptno;
  end;

end;

You can see that the package just retains audit rows in an associative array, and the “bulk_process” routine which will be called by the trigger to process them all once the statement completes. Thus our trigger now becomes a compound one.



SQL> exec  aud_util.audit_util.generate_audit_trigger('SCOTT','EMP',p_action=>'EXECUTE');

Call to generate audit trigger for SCOTT.EMP

create or replace
trigger AUD_UTIL.AUD$EMP
for insert or update or delete on SCOTT.EMP
disable
compound trigger
 l_dml       varchar2(1) := case when updating then 'U' when inserting then 'I' else 'D' end;
 l_tstamp    timestamp;
 l_id        number;
 l_descr     varchar2(100);

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

before statement is
begin
 if aud_util.trigger_ctl.enabled('AUD$EMP') then
   aud_util.pkg_emp_scott.bulk_init;
 end if;
end before statement;

after each row is
begin
 if aud_util.trigger_ctl.enabled('AUD$EMP') then
  l_descr :=
    case
      when updating
        then 'UPDATE'
      when inserting
        then 'INSERT'
      else
        'DELETE'
    end;

  aud_util.audit_pkg.log_header_bulk('EMP',l_dml,l_descr,l_tstamp,l_id);

  if updating('EMPNO')    or
     updating('DEPTNO')   or
     updating('HIREDATE') or
     deleting then
     aud_util.pkg_emp_scott.audit_row(
         p_aud$tstamp =>l_tstamp
        ,p_aud$id     =>l_id
        ,p_aud$image  =>'OLD'
        ,p_empno     =>:old.empno
        ,p_ename     =>:old.ename
        ,p_job       =>:old.job
        ,p_mgr       =>:old.mgr
        ,p_hiredate  =>:old.hiredate
        ,p_sal       =>:old.sal
        ,p_comm      =>:old.comm
        ,p_deptno    =>:old.deptno
        );
  end if;
  if updating('EMPNO')    or
     updating('DEPTNO')   or
     updating('HIREDATE') or
     inserting then
     aud_util.pkg_emp_scott.audit_row(
         p_aud$tstamp=>l_tstamp
        ,p_aud$id    =>l_id
        ,p_aud$image =>'NEW'
        ,p_empno     =>:new.empno
        ,p_ename     =>:new.ename
        ,p_job       =>:new.job
        ,p_mgr       =>:new.mgr
        ,p_hiredate  =>:new.hiredate
        ,p_sal       =>:new.sal
        ,p_comm      =>:new.comm
        ,p_deptno    =>:new.deptno
        );
  end if;
 end if;
end after each row;

after statement is
begin
 if aud_util.trigger_ctl.enabled('AUD$EMP') then
   aud_util.pkg_emp_scott.bulk_process;
   aud_util.audit_pkg.bulk_process;
 end if;
end after statement;
end;
alter trigger AUD_UTIL.AUD$EMP enable

Miscellaneous

Interval Partitioning

Also provided is a routine


PROCEDURE partition_name_tidy_up(p_operation varchar2 default 'DEFAULT',
                                 p_action varchar2);

which only is relevant if you are using partitioning for your audit tables. Interval partition is being used, and as such, automatically generated partition names will be observed. This routine looks through the audit schema and renames the partitions to have the form  _P

The “p_operation” parameter allows this routine to be activated as a scheduler job.  The values for this parameter are:

  • DEFAULT – do the partition renaming work
  • DISABLE – disable the existing scheduler job
  • ENABLE – enable the existing scheduler job
  • UNSCHEDULE – drop the scheduler job
  • SCHEDULE – create a new scheduler job for 9am each day, which simply calls the same routine with the DEFAULT operation
  • CHECK – see if there is a job and create one if not there.

Selective Column Updates

There may be some columns in your source table for which an update does not constitute that change being audit worthy. For example, you might have a table of users, and you don’t care when people upload a new profile photo. You can also nominate which columns are the ones “of interest” when generating audit support for the table



SQL> exec  aud_util.audit_util.generate_audit_trigger('SCOTT','EMP',p_update_cols=>'EMPNO,DEPTNO,HIREDATE',p_action=>'OUTPUT');

Call to generate audit trigger for SCOTT.EMP

create or replace
trigger AUD_UTIL.AUD$EMP
after insert or update or delete on SCOTT.EMP
for each row
disable
declare
 l_dml       varchar2(1) := case when updating then 'U' when inserting then 'I' else 'D' end;
 l_tstamp    timestamp;
 l_id        number;
 l_descr     varchar2(100);
begin

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

 if aud_util.trigger_ctl.enabled('AUD$EMP') then
  l_descr :=
    case
      when updating
        then 'UPDATE'
      when inserting
        then 'INSERT'
      else
        'DELETE'
    end;

  aud_util.log_header_bulk('EMP',l_dml,l_descr,l_tstamp,l_id);

  if updating('EMPNO')    or
     updating('DEPTNO')   or
     updating('HIREDATE') or
     deleting then
     aud_util.pkg_emp_scott.audit_row(
         p_aud$tstamp =>l_tstamp
        ,p_aud$id     =>l_id
        ,p_aud$image  =>'OLD'
        ,p_empno     =>:old.empno
        ,p_ename     =>:old.ename
        ,p_job       =>:old.job
        ,p_mgr       =>:old.mgr
        ,p_hiredate  =>:old.hiredate
        ,p_sal       =>:old.sal
        ,p_comm      =>:old.comm
        ,p_deptno    =>:old.deptno
        );
  end if;
  if inserting then
     aud_util.pkg_emp_scott.audit_row(
         p_aud$tstamp=>l_tstamp
        ,p_aud$id    =>l_id
        ,p_aud$image =>'NEW'
        ,p_empno     =>:new.empno
        ,p_ename     =>:new.ename
        ,p_job       =>:new.job
        ,p_mgr       =>:new.mgr
        ,p_hiredate  =>:new.hiredate
        ,p_sal       =>:new.sal
        ,p_comm      =>:new.comm
        ,p_deptno    =>:new.deptno
        );
  end if;
 end if;
end;
alter trigger AUD_UTIL.AUD$EMP enable

When you create a trigger in this way, the nominated columns are stored in the AUDIT_UTIL_UPDATE_TRIG table. In this way, if you generate the trigger again, the same column specification is retained. If you need to reset the columns that are captured, you pass the string ‘NULL’ for the p_update_cols parameter (because a true null just means use the existing column specification.

Selective Row Audit

Along similar lines, you can nominate a WHEN clause for the trigger when generating the audit.



SQL> exec  aud_util.audit_util.generate_audit_trigger('SCOTT','EMP',p_when_clause=>'new.empno > 0',p_action=>'OUTPUT');
Call to generate audit trigger for SCOTT.EMP
create or replace
trigger AUD_UTIL.AUD$EMP
after insert or update or delete on SCOTT.EMP
for each row
disable
when (new.empno > 0)
declare
 l_dml       varchar2(1) := case when updating then 'U' when inserting then 'I' else 'D' end;
 l_tstamp    timestamp;
 l_id        number;
 l_descr     varchar2(100);
begin

 /***************************************************************/
 /* ATTENTION                                                   */
 /*                                                             */
 /* This package is automatically generated by audit generator  */
 /* utility.  Do not edit this package by hand as your changes  */
 /* will be lost if the package are re-generated.               */
 /***************************************************************/

 if aud_util.trigger_ctl.enabled('AUD$EMP') then
  l_descr :=
    case
      when updating
        then 'UPDATE'
      when inserting
        then 'INSERT'
      else
        'DELETE'
    end;

  aud_util.log_header('EMP',l_dml,l_descr,l_tstamp,l_id);

  if updating('EMPNO')    or
     updating('DEPTNO')   or
     deleting then
     aud_util.pkg_emp_scott.audit_row(
         p_aud$tstamp =>l_tstamp
        ,p_aud$id     =>l_id
        ,p_aud$image  =>'OLD'
        ,p_empno     =>:old.empno
        ,p_ename     =>:old.ename
        ,p_job       =>:old.job
        ,p_mgr       =>:old.mgr
        ,p_hiredate  =>:old.hiredate
        ,p_sal       =>:old.sal
        ,p_comm      =>:old.comm
        ,p_deptno    =>:old.deptno
        );
  end if;
  if inserting then
     aud_util.pkg_emp_scott.audit_row(
         p_aud$tstamp=>l_tstamp
        ,p_aud$id    =>l_id
        ,p_aud$image =>'NEW'
        ,p_empno     =>:new.empno
        ,p_ename     =>:new.ename
        ,p_job       =>:new.job
        ,p_mgr       =>:new.mgr
        ,p_hiredate  =>:new.hiredate
        ,p_sal       =>:new.sal
        ,p_comm      =>:new.comm
        ,p_deptno    =>:new.deptno
        );
  end if;
 end if;
end;
alter trigger AUD_UTIL.AUD$EMP enable

PL/SQL procedure successfully completed.

Like the update columns clause, the WHEN clause is stored in the AUDIT_UTIL_UPDATE_TRIG table. In this way, if you generate the trigger again, the same WHEN clause is retained. If you need to reset it, you pass the string ‘NULL’ for the p_when_clause parameter (because a true null just means use the existing specification .

Post Installation

Just done a large application deployment? If you are worried that you might have missed something pertaining to auditing, you can call POST_INSTALL which will cycle though all tables for those schemas listed in the SCHEMA_LIST table and look for differences between the source table and its audit partner. In this one routine, as well as the standard OUTPUT and EXECUTE options for the p_action parameter, there is also an option of REPORT will be present a report of what differences were found. This can be very useful as a validation check.

Accidents Happen

As I mentioned in the video below, some times you need to turn off a trigger temporarily in a session. Disabling a trigger is a drastic way to achieve this because it breaks the application for any other session that is current using that table. So the audit routines respect that need, and you can control audit trigger facilities on a session by session basis using the TRIGGER_CTL package which will be loaded into the audit schema. Clearly, you might want to look at either not using this (if you want to force audit ALL the time) or perhaps adding some sort of authentication etc to ensure people don’t go around selectively turning off the audit! But by default, it is not granted to anyone so you’re pretty safe with the default.

Complete list of Settings

g_aud_prefix
common prefix for all audit table names. Defaults to null

g_capture_new_updates
whether to log :NEW records as well as :OLD records. Defaults to false.

g_inserts_audited
whether to log inserts as well as updates and deletes. Defaults to false.

g_job_name
the scheduler job name for partition name tidy up. Defaults to AUDIT_PARTITION_NAME_TIDY_UP

g_log_level
The amount of logging we do. 1= dbms_output, 2=maint table, 3=both. Defaults to 3

g_logical_del_col
Some times an update is a delete from an application perspective, namely, we set a column called (say) DELETED to Y. If you have that, you can nominate that column here and we will log an update as a “logical delete” in the audit table metadata. Defaults to DELETED_IND

g_table_grantees
If you want to allow access to the audit tables, set the list of schemas/roles to be granted that privilege in this associative array. Defaults to none.

g_trigger_in_audit_schema
Whether the trigger should be created in the table owning schema or the audit schema. Defaults to true (the audit schema).

g_bulk_bind
Whether the audit processing uses bulk bind or row-by-row processing for audit capture.

Alternatives

My video on flashback data archive as an audit trail option

Photo by Harley-Davidson on Unsplash

Why Good Intentions + WIT + D&I = FAIL

Submitted by dbakevlar on Mon, 2020-08-03 23:02

In two hours I am honored to be the guest of the CSME Women’s Book Club event at Microsoft to discuss my book, “Crushing the IT Gender Bias.”  In celebration of this, I want to discuss the importance of Women in Technology, (WIT) initiatives and the mistake many groups continue to make these days consolidating them into Diversity and Inclusion, (D&I) groups.

A few years ago, when Women in Technology, (WIT) initiatives were the newest, coolest thing for business to be a part of, I was awarded an annual WIT award from the state’s tech organization.  I was quite humbled to receive this award, anxious to attend the awards ceremony with fellow recipients specializing in other recognized areas.  The event was a well -attended gala by over 1000 people.

The Change

The state organization’s WIT group was quite large at this time and scheduled activities were well received, involving all tiers of technical abilities.  After I’d received the award, it was announced some months later that it would be the last year for the WIT award, as focus had evolved to have diversity and inclusion initiatives.  The change was made with incredibly good intentions.  Many of us were thrilled by the prospect of needed representation and priority for D&I to finally receive some much needed attention.  We also were highly concerned that instead of creating its own group, as they’d done for so many other areas, it was being folded into the WIT group.

In the next two months, I noticed that the dynamic of the WIT activities changed.  As the group started to “fold in” diversity and inclusion, no longer did the deeper technical women attend.  I reached out to those women who were close to me in the WIT groups and discovered that most of them had left the original WIT group once it became a D&I group to join Women Who Code, Girl’s Develop It and Girl Geek Dinners.  When I asked why, they highlighted the loss of focus on WIT challenges and began to refer to the state lead WIT group as the “happy/shiny people.”  This was due to a perception around a need to check off boxes on a checklist, the lack of deeper technical folks being involved and a lack of discussion around solving the challenges groups were facing in the industry.  As I had experienced similar since the update in the state organization group, I ended up investing in the local Girl Geek Dinners as the organizer and partnered up to cosponsor more technical events and discussions centered around WIT issues between us, Women who Code and Girls Develop It.

2020 Numbers

As of 2020, women in technology make up less than 25% of the workforce and a considerable challenge around that percentage as it includes roles such as recruiters and project managers that may not actually perform technical duties.  Currently for GAFAM, (Google, Apple, Facebook, Amazon and Microsoft) the WIT percentage sits at 25%.  The numbers for Microsoft for women in their workforce is at 20%, which always surprises me with the amount of focus we have on ensuring women in tech have an evolved working experience.  I’ve never felt more accepted in any position in my 25 years in technology at any other company.  What this tells us, if a company like Microsoft is having a challenge with all the work they’ve done, then this tells us women in technology is not self-sustaining.  If we think we don’t still need a focused effort in this area, we’re setting ourselves up for failure.

Data shows that even though over half of college students are women, only 20% are headed for CS degrees.  Forty years ago, this number was 40% and the loss of role models and the way we view technical careers is part of the reason.

The cultural challenges still exist.  Girls are still raised with huge limitations between them and leading into a technical career.  71% of grown women in the industry still state they’ve worked in tech companies with a strong “bro culture”.   I’m in a group of deeply technical individuals and we, just as with other engineering groups, on average, consist of a 5:1 ratio of men to women.  As some are trying to “coat” the numbers by including less technical roles to soothe us is not the answer.  Giving the focus that each specialized group deserves is important to its success, otherwise your group or company only appears to be “checking the box”.

The Fix

In the years since the state’s organization new initiative for D&I was announced, it didn’t become the large win they’d hoped for and they chose to learn from their mistake.  In the end, this group re-embraced WIT as an individual group within their organization.  They discovered that consolidating specialized groups with unique struggles isn’t a formula for success and each deserves focus.  When they have consolidated, this results in members less likely to invest when they don’t receive the investment in their interest shown.  Now that they have re-engaged their WIT group, the events, conferences and such are once more well attended. I’m thrilled to see this renewed success into the WIT community and the response to reversing the change appears to be quite successful.

I’m now seeing more organizations disbanding very successful WIT groups with strong involvement, only to roll them up into D&I groups.  As in the experience I’ve just shared, consolidating specialized groups into one, becomes akin to choosing to do a “Black, Women and LBGTQ Lives Matter” protest.  Most won’t be happy and no one will be able to address the varied and important topics each group requires a priority on.

In regards to the D&I groups I’ve been part of, there was limited success in making the most of limited resources and it alienates many important members of originally focused groups. The ending “consolidated” topics resulted priorities unresolved in exchange for a broader range of coverage.  The organizations no longer appeared to be addressing the problems which were a priority to each focus group and those members of the group who really drive change or increase involvement by others simply left.

My hope is, by sharing this lesson learned by the state organization, it will help guide others to not make the same mistake down the road with other WIT groups.  Create new D&I groups- create them with passion for diversity and lead them to take on the challenges uniquely faced by passionate folks who are focused on D&I.  Yes, there will be some overlap with WIT, but these two groups should not be consolidated.  Both are the future of technology and consolidation will only deprive us all of success.

Have a great Monday, everyone!



Tags:  ,


Del.icio.us



 		Facebook

 TweetThis

 Digg

 StumbleUpon



Copyright ©  [Why Good Intentions + WIT + D&I = FAIL], All Right Reserved. 2020.