Saturday, April 12, 2014

adding NOT NULL columns to an existing table ... implications make me grumpy

This is DBA basics 101 in the oracle world but well also something that we grumpy DBA types forget from time to time.  We have an existing table in a schema that is populated with data.  Something like this say:

create table dbaperf.has_data ( column_one varchar2(10) not null, column_two number(10) not null);

insert into dbaperf.has_data(column_one, column_two) values('First row',13);
insert into dbaperf.has_data(column_one, column_two) values('Another',42); commit;

Now you need to add another column that is also NOT NULL.  Chris Date not happy the vendor implementations of the relational model allow null columns.  Be aware of any potential NULL columns in rows and handle them carefully ( IS null / IS not null ) to avoid messing up results.

But anyhow we are going to add in a new column that is NOT NULL.

How easy that is to do against an Oracle table depends on whether one is also supplying a DEFAULT value for the new column.  If you do not supply DEFAULT value what happens here?

 alter table dbaperf.has_data add ( column_three char(1) NOT NULL );

You get: ORA-01758: table must be empty to add mandatory (NOT NULL) column

To get around that you have to do this in three steps:
  • Add in the new column
  • Populate all the new columns with a value ( data migration )
  • Make the column NOT NULL
alter table dbaperf.has_data add ( column_three char(1) );

update dbaperf.has_data set column_three = 'X' where column_one = 'First row';
update dbaperf.has_data set column_three = 'X' where column_one = 'Another';

alter table dbaperf.has_data modify ( column_three NOT NULL );

Things get easier if you do this with a DEFAULT clause on the new column.  The problem is of course some columns have a reasonable default value others may not get any agreement for a default value.  A min and a max type column probably can have an easy default others not so much.

alter table dbaperf.has_data add ( column_four number(21,2) default 0 NOT NULL );

All of this discussion side steps the implications of adding a new column to a large existing table or partitioned table and fragging up the blocks ... that is a little beyond 101 for now.

Friday, March 28, 2014

another month almost gone ... another presentation done at NEOOUG

Geez this month really just flew by it started with my presentation at Hotsos 2014.  Pretty well attended waiting for final evaluation information.  Then work work work ...

This Friday ( today 3/28/2014 ) I did my Hotsos presentation again but this time at my local user group.  I added "just three more slides" to try to give some additional information on PGA and program connections to people aka developers coming in cold to this area. 

Somehow the three additional slides caused me to take an additional twenty minutes to deliver this information.  Lucky I was not given the hook ( sometimes it helps to be president ).  It was kind of funny even though I had just delivered the presentation at the beginning of the month I found myself looking at some of the slides that came up thinking "oh that is out of order here ( no it was not )" or even worse "oh geez what am I trying to connect with on this slide" ... yikes!

Registrations are starting to roll in for GLOC 2014 but the month of April is the critical one.  We would like to increase attendance by 33% percent ... time will tell!

Tuesday, March 18, 2014

some days it is harder to be grumpy than others ...

The official notification is out and the nomination that was submitted to recognize me as an Oracle Ace has been approved.  This is certainly an honor and along with my continued role at NEOOUG I am hoping to leverage this status to continue to help grow our user group and our yearly conference Great Lakes Oracle Conference ( GLOC )

Speaking of that our abstract selection has been finalized and you can see all of our great presentations and workshops from the link above.  We will have a session schedule out very soon.

Essentially we have four tracks DBA : Developer : Applicationss : BI/DW and each track has at least 1 session for each time slot and the DBA track having 2 sessions concurrently.

This conference is a great value with top presenters.  We have worked hard this year to give a stronger/deeper set of Applications and BI/DW offerings.  I could start rambling about our plans for next year but probably best to focus on 2014 for now!

The Grumpy Old DBA hopes to see everyone in Cleveland!

Geez oh man I read through briefly the pages in Oracle Ace agreement on how to display this logo hope I did not mess up my first attempt!  I can just see already "Grumpy Old DBA please report to the principals office immediately" ( hope not keep your fingers crossed for me ha ha )!

Friday, March 14, 2014

is this old school ... using dbms_shared_pool keep to pin plsql code in shared pool?

These days with the shared pool area getting so large many people ( including me ) tend to think of reserving/pinning plsql code into the shared pool as an old school "probably no longer necessary" approach.  Of course many 11g systems are now running with 4 gb shared pools ( and much more sometimes ).

In my recent hotsos session I polled the attendee's and ( kind of as expected ) confirmed that most people are not doing this any more.

However after I finished someone came up and recommended that I add in as material to my presentation taking a look at an oracle provided toolkit ( PIND ) that accomplishes this for you.  He noted that oracle ebiz suite deployments are perhaps particularly significant in maybe still needing this kind of setup.  ( So this information has been added to my presentation ).

Of course dbms_shared_pool.keep is the way to do it yourself and no shortage of web posts about doing this stuff yourself.

If you want to look at this oracle toolkit look at oracle doc ids 301171.1 and 311689.1

Saturday, March 8, 2014

final version of my Hotsos 2014 presentation

This is the "really really" final version because it was updated and some additional references added even after the presentation was delivered.

Hoping to do it at Open World 2014 so ... I guess this is the early version of that presentation eh?

You can find it here: Three approaches to shared pool monitoring Hotsos 2014

So you think you have disabled ASMM/AMM oracle automatic SGA sizing behavior?

I polled my session attendee's at Hotsos 2014 ( Three approaches to shared pool monitoring ) and asked them who was using automatic stuff versus who was doing it all manually?

My guess is that there were about 100 people in the room and it looked like automatic "won" but not by much.  Maybe 55 percent automatic and 45 manual ... so there are still a whole bunch of people nervous about using that functionality.

My presentation was updated ( after delivering it ) to add in some additional doc id's and warnings ( both pro and con ) about manual memory settings.

Specifically worth noting is the un obvious fact that EVEN IF you have disabled oracle from doing SGA resizing operations automatically ... well it may do them anyhow.  Makes me a little grumpy eh?

So you can set SGA_TARGET to zero ( and/or MEMORY_TARGET to zero ) and still have oracle make decisions for you?  It turns out that after some point you have to set yet another hidden underscore parameter to tell the oracle software "yeah thanks but I really really mean it" ...


Oracle doc id 1269139.1 ... a pretty good writeup here ... really turn off ammasmm

Monday, March 3, 2014

managed to make it to Dallas sunday morning ... Hotsos 2014 is rolling

It was a little dicey sunday morning with weather in Ohio but I caught very early flight and made it down to Dallas well before noon.  Lots of turbulence in the airplane ride and bounced around some but all in all not bad.  Lots of people traveling ran into hitches one way or another but by the end of the day Monday most everyone seems to be here now.

Even better I had pretty good turnout for my session maybe even close to 1/2 the attendees dunno exactly but room was pretty full.  Presentation went pretty well only blanked out once and then jumped into next bullet point.  Actually all in all for as many times as I had practiced this thing and thought I was going to fail at various points I think it went over pretty well.

Managed one really cheesy comparison of the shared pool to a picture that I will probably get in trouble for eventually.

Besides some of the people I already knew I have met several other nice speakers and now much more relaxed with my presentation over with.  Even managed to update my presentation slightly with some material from this morning Kerry Osborne and Tim Gorman.